MySQL的知识点有哪些

本篇内容主要讲解"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的知识点有哪些"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！