溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“MySQL的知識點有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL的知識點有哪些”吧!
1.在CentOS6 上 MySQL重新部署
1.1 先刪除 日志文件和數據文件
rm -rf arch/* data/*
1.2 再進行安裝
scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2. 空密碼修改
show databases; -- 查看數據庫列表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
use mysql; -- 使用mysql數據庫
mysql> use mysql;
Database changed
show tables; -- 查看mysql數據庫里面的table列表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
desc user; -- 查看user表的表結構
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-
show create table user; -- 查看user表的表結構，包含user表的創建腳本
mysql> show create table user \G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
-- 查詢 user表的允許訪問的地址，登錄名和登錄密碼
mysql> select Host,user,password from mysql.user;
+-----------+------+----------+
| Host | user | password |
+-----------+------+----------+
| localhost | root | |
| hadoop000 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| hadoop000 | | |
+-----------+------+----------+
6 rows in set (0.00 sec)
-- 修改root賬號的密碼
mysql> update mysql.user set password=password('123456') where user='root';
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
-- 查詢修改后的信息
mysql> select Host,user,password from mysql.user;
+-----------+------+-------------------------------------------+
| Host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| hadoop000 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | | |
| hadoop000 | | |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)
-- 使修改生效
mysql> flush privileges;
mysql用戶的權限操作，最后加 flush privileges;
3. 修改% 任意機器可訪問
-- 將 hadoop000 修改為'%'后，表示任意機器都可訪問
mysql> update mysql.user set Host='%' where Host='hadoop000';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 使修改生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4. mysql命令參數
mysql -uroot -p123456 -- 可以正常登錄
mysql -u root -p123456 -- 可以正常登錄
mysql -u root -p 123456 -- 不可以正常登錄 -p后面有空格
mysql -u root -p123456 -h292.168.137.251 -- 可以正常登錄 帶IP登錄
mysql -u root -p123456 -hlocalhost mysql -- 可以正登錄 指定登錄的數據庫
5. 創建新DB和用戶
-- 創建數據庫
mysql> create database testdb;
Query OK, 1 row affected (0.09 sec)
-- 授權登錄數據庫的用戶名和密碼
mysql> grant all privileges on testdb to root@'%' identified by '123456';
Query OK, 0 rows affected (0.16 sec)
-- 生效命令
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
6. 查看當前mysql鏈接數 （在工作中，可以適當kill連接，注意：慎用！！！）
mysql> show processlist;
+----+------+----------------------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+-------+---------+------+-------+------------------+
| 7 | root | localhost | mysql | Query | 0 | init | show processlist |
| 11 | root | 192.168.90.157:63149 | NULL | Sleep | 13 | | NULL |
+----+------+----------------------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
-- 如果哪個process的卡，夯住，鎖死，可以相關技術人員或相關責任人溝通好后kill掉
kill Id;
7. MySQL字符類型 char 和 varchar 的對比
相同點： char 和 varchar都是存儲字符串的數據類型
差異點：
1 存數據時的區別
char定義的是固定長度，長度范圍為0-255，存儲時，如果字符數沒有達到定義的位數，會在后面用空格補全存入數據庫中。
varchar是變長長度，長度范圍為0-65535，存儲時，如果字符沒有達到定義的位數，也不會在后面補空格。
2 占用字節差別
以latin編碼為便，一個字符占用一個字節。
可以用上表來表示，當定義char時，不管你存入多少字符，都會占用到你定義的字符數，而用varchar時，則和你輸入的字符數有關，會多一到兩個字節來記錄字節長度，當數據位占用的字節數小于255時，用1個字節來記錄長度，數據位占用字節數大于255時，用2個字節來記錄長度，還有一位來記錄是否為nul值。
8. MySQL 庫名，表名和表數據的大小寫問題
8.1 庫名和表名 默認是區分大小寫的，列名不區分大小寫：
-- 列出數據庫列表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
-- 更改數據庫，數據庫名大小寫，和數據庫里面的數據庫名大小寫不一致
mysql> use TestDB;
ERROR 1049 (42000): Unknown database 'TestDB'
-- -- 更改數據庫，數據庫小寫，和數據庫里面的數據庫名大小寫一致
mysql> use testdb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
-- 查詢大寫的表名，和數據庫里面的表名大小寫不一致
mysql> select * from USER;
ERROR 1146 (42S02): Table 'testdb.USER' doesn't exist
-- 查詢小寫的表名，和數據庫里面的表名大小寫一致
mysql> select * from user;
+------+----------+
| id | name |
+------+----------+
| 1 | ZHANGSAN |
| 2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
-- 查詢user表結構
mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(200) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
-- 查詢user表數據，列名大寫，不影響查詢，說明表列名不區分大小寫。 mysql> select ID,NAME from user;
+------+----------+
| ID | NAME |
+------+----------+
| 1 | ZHANGSAN |
| 2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
解決庫名和表名 區分大小寫的方法：在 /etc/my.cnf 文件里面添加 lower_case_table_names=1 配置。
8.2 表數據默認是不區分大小寫的。可以通過binary關鍵字加以解決區分大小寫。
8.2.1. 在查詢的sql語句后面加上 binary關鍵字。
-- 不加關鍵字查詢
mysql> select * from user where name='zhangsan';
+------+----------+
| id | name |
+------+----------+
| 1 | ZHANGSAN |
| 2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
-- 加關鍵字查詢
mysql> select * from user where binary name='zhangsan';
+------+----------+
| id | name |
+------+----------+
| 2 | zhangsan |
+------+----------+
1 row in set (0.03 sec)
8.2.2. 在建表時上 binary關鍵字
-- 將user表刪除后再重建
mysql> drop table user;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `user` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(200) binary DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> ;
Query OK, 0 rows affected (0.04 sec)
-- 查詢 user表的數據
mysql> select * from user;
+------+----------+
| id | name |
+------+----------+
| 1 | ZHANGSAN |
| 2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
-- 不帶 binary關鍵字的條件查詢
mysql> select * from user where name='zhangsan';
+------+----------+
| id | name |
+------+----------+
| 2 | zhangsan |
+------+----------+
1 row in set (0.02 sec)
9. 修改mysql客戶端及服務器的字符集
9.1 修改數據庫客戶端字符集
修改/etc/my.cnf配置文件如下：
[client]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
default-character-set=utf8
9.2 查看參數文件
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
9.3 修改服務器參數文件
[mysqld]
port=3306
character-set-server=utf8 -- 設置MySQL服務器編碼
init-connect='SET NAMES utf8' -- 設置connectioin 的編碼
socket = /usr/local/mysql/data/mysql.sock
9.4 重啟mysql服務
hadoop001:mysqladmin:/usr/local/mysql:>service mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
9.5 查看字符集參數
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
注：字符集都已經變成utf-8了
9.6 查看數據庫信息
-- 查看mysql數據庫的信息
mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
-- 查看testdb數據庫的信息
mysql> show create database testdb;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
注：此時數據庫的字符集也變成utf-8了
到此，相信大家對“MySQL的知識點有哪些”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
