03Mysql 04 mysql 命令

mysql的命令

1.mysql连接服务端的快捷命令

#常用命令
\c	中断当前的命令
\G	垂直显示结果
\q	退出数据库  等于 exit quit
\h	显示帮助  等于 \? 或 help
\T	将执行和输出的内容打印到指定的文件中
\t	取消写入文件
\e	进入编辑状态，可一次编辑多条 mysql 命令，命令后面要加 ; 表示一条命令语句结束
\.	导入指定的sql文件  等于 source
\s	查看状态  等于 \status

#不常用命令
\r	进入客户端后切换mysql服务器连接,只能是切换到端口，用户名，密码都是一样的另外一个mysql服务器
\d	更改命令结束符
\g	和命令结束符 ; 一样的作用
\n	禁用外部设备，打印到标准输出
\P	连接外部设备，打印输出结果到外部设备
\p	打印出命令和命令的结果到屏幕	show databases \p;
\R	更改命令提示符如 mysql> 
\u	切换数据库和 use mysql； 一样如 \u mysql；
\C	切换字符集
\W	每条语句后面显示警告
\w	每条语句后面不显示警告

#范例
\G: 垂直显示结果
#例子：mysql> select * from mysql.user \G

\T: 将执行和输出的内容打印到服务器文件
\t: 取消写入文件
#例子：
mysql> \T /tmp/a.txt
Logging to file '/tmp/a.txt'
mysql> show grants for root@'%';
+--------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                |
+--------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF04B6A265E0AA257' |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \t
Outfile disabled.
#查看 /tmp/a.txt 文件写入的内容
[root@db03 ~]# cat /tmp/a.txt 
mysql> show grants for root@'%';
+--------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                |
+--------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0F5E05AA257' |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \t

\R：设置 mysql 的 命令提示符
#例子：
mysql> \R mysql- 
PROMPT set to 'mysql- '
mysql- 

\r：重新连接到服务器
#例子1：
mysql> \r mysql- 
Connection id:    29
Current database: *** NONE ***
#例子2：进入客户端后切换服务器连接,只能是切换到和另外一个SERVER端口，用户名，密码都是一样的
mysql> \r test 192.168.0.232
Connection id: 23
Current database: test
#参考文件：https://yq.aliyun.com/articles/402399

\d：更改命令结束符
#例子：
mysql> \d ]
mysql> show databases]
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

\g：和命令结束符 ; 一样的作用
#例子：
mysql> show databases\g
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

\s：查看当前MySQL状态
#例子：
mysql> show slave \s
--------------
mysql  Ver 14.14 Distrib 5.6.46, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:		7
Current database:	
Current user:		root@db01
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.46 MySQL Community Server (GPL)
Protocol version:	10
Connection:		172.16.1.51 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			21 hours 28 min 52 sec

Threads: 1  Questions: 68  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.000
--------------

    -> \c
mysql> 

2.help命令

mysql> help create database
mysql> help
mysql> help contents
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show

3.客户端mysqladmin的命令

1）设置、修改密码

#设置密码【刚刚安装好mysql还没有密码的时候设置方法】
[root@db01 ~]# mysqladmin -uroot password '123456'
#修改密码
[root@db01 ~]# mysqladmin -uroot -p123456 password '123'

2）关闭服务

[root@db01 ~]# mysqladmin -S /service/3307/mysql.sock shutdown
[root@db01 ~]# mysqladmin -uroot -p123 shutdown

3）库外创建数据库

[root@db01 ~]# mysqladmin -uroot -p123 create linux7

4）库外删除数据库

[root@db01 ~]# mysqladmin -uroot -p123 drop linux7
Do you really want to drop the 'linux7' database [y/N] y
Database "linux7" dropped

5）查看数据库配置

[root@db01 ~]# mysqladmin -uroot -p123 variables
[root@db01 ~]# mysqladmin -uroot -p123 variables | grep server_id

#登陆数据库后查看
mysql> show variables;

6）监测数据库是否启动

[root@db01 ~]# mysqladmin -uroot -p123 ping
Warning: Using a password on the command line interface can be insecure.
mysqld is alive
[root@db01 ~]# mysqladmin -uroot -p ping
Enter password: 
mysqld is alive

#如果没启动，则连接报错

7）查看数据库信息

[root@db01 ~]# mysqladmin -uroot -p123 status
Warning: Using a password on the command line interface can be insecure.
Uptime: 49  Threads: 1  Questions: 10  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.204

Uptime: 49		服务器工作时长49秒
Threads: 1		服务器当前连接数量1
Questions: 10	发往服务器的查询的数量
Slow queries: 0	要花超过long_query_time（长查询时间）的查询数量
Opens: 70		
Flush tables: 1 刷新表的数量
Open tables: 63	打开表的数量
Queries per second avg: 0.204	每秒钟平均查询次数

8）刷新授权表

[root@db01 ~]# mysqladmin -uroot -p123 reload
#相当于进到数据库里面执行 flush privileges
#忘记密码使用跳过授权登陆后创建新用户，就需要刷新授权表【不刷新不可以执行授权命令 grant】

9）刷新binlog日志

[root@db01 ~]# mysqladmin -uroot -p123 flush-log

# 查看binlog信息
mysql> show master status;
# 库内刷新
mysql> flush logs;

SQL语句

一、SQL语句语义种类

DDL：Data Definition Language 	数据定义语言（CREATE）
DCL：Data control Language 		数据控制语言（GRANT，ROLLBACK，COMMIT）
DML：Data Manipulation Language  数据操作语言（INSERT，UPDATE，DELETE）
DQL：Data Query Language 		数据查询语言（SELECT）

二、DDL语句—数据定义语言（CREATE）

1.CREATE针对库的操作

1）查看语法

mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name

2）创建库

#方法一
mysql> create database mcy1;
Query OK, 1 row affected (0.00 sec)
#方法二
mysql> create schema mcy2;
Query OK, 1 row affected (0.00 sec)

3）创建数据库已存在会报错，使用 IF NOT EXISTS 可以忽略错误

mysql> create database qiudao;
ERROR 1007 (HY000): Can't create database 'qiudao'; database exists

mysql> create database if not exists qiudao;
Query OK, 1 row affected, 1 warning (0.00 sec)

4）查看建库语句

mysql> show create database qiudao;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| qiudao   | CREATE DATABASE `qiudao` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

5）指定字符集和校验规则创建数据库

mysql> create database qiudao charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database qiudao;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| qiudao   | CREATE DATABASE `qiudao` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

#查看所有可用的字符集
show character set;
#查看支持的所有检验规则
show collation;
#查看GBK字符集所有的校对规则
show collation like 'gbk%';

#查看数据库所有变量和值
show variables;
#查看 MySQL 数据库服务器和数据库字符集
show variables like '%char%';
#当前服务器字符集
show variables like 'character_set_server';
#当前服务器字符集对应的校验规则
show variables like 'collation_server';
#当前数据库字符集
show variables like 'character_set_database';
#当前数据库字符集对应的校对规则
show variables like 'collation_database';

#查看 countries 数据表的列（column）信息
show full columns from countries;
#查看当前数据库有哪些线程正在运行
show full processlist;
show processlist;

(1) 最简单的修改方法，就是修改mysql的my.cnf文件中的字符集键值，
	character_set_server =  utf8
     修改完后，重启mysql的服务
(2) 还有一种修改字符集和效验规则的方法，就是使用 mysql 的 set 命令
     mysql> SET character_set_client = utf8 ;
     mysql> SET character_set_connection = utf8 ;
     mysql> SET character_set_database = utf8 ;
     mysql> SET character_set_results = utf8 ;
     mysql> SET character_set_server = utf8 ;
     mysql> SET collation_connection = utf8 ;
     mysql> SET collation_database = utf8 ;
     mysql> SET collation_server = utf8 ;

6）删库

mysql> drop database qiudao;
Query OK, 0 rows affected (0.00 sec)

7）修改库的 字符集和效验规则

mysql> alter database qiudao charset utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database qiudao;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| qiudao   | CREATE DATABASE `qiudao` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

8）修改库名

1、创建新库，修改旧表名称到新库中【速度快，建议使用】
mysql> create database linux7 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.28 sec)
	#将旧库的表移动到新库
mysql> rename table lnh7q.student to linux7.student;
Query OK, 0 rows affected (0.04 sec)

2、导出旧数据库后再导入到新的数据库中
#导出数据库
	-A		#备份所有数据库，并在备份文件中生成创建库的语句及use语句。这样方便恢复时不用创建指定数据库。
	-B		#备份几个数据库，并在备份文件中生成创建库的语句及use语句。这样方便恢复时不用创建指定数据库。
mysqldump -uroot -p -A >all_db.sql
mysqldump -uroot -p -B wp ww > /tmp/two.sql
	#不加选项，则不会生成创建库的语句
mysqldump -uroot -p123456 bbp >bbp.sql

#导出数据库中的一个表
mysqldump -uroot -p 数据库名 数据表名 > /tmp/wordpress.sql

#导入数据库
 	#备份文件中 有 创建库的语句及use语句
mysql -uroot -p < /tmp/wp.sql
	#备份文件中 无 创建库的语句及use语句
mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /tmp/wp.sql

9）进入库

#进入qiudao库，可以忽略掉命令结束符 ;
mysql> use qiudao
Database changed

10）查看当前库

#查看所在库
mysql> select database();
+------------+
| database() |
+------------+
| qiudao     |
+------------+
1 row in set (0.00 sec)

2.CREATE 针对表的操作

1）查看建表语法

mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

2）建表

#进入qiudao库，可以忽略掉命令结束符 ;
mysql> use qiudao
Database changed

#查看所在库
mysql> select database();
+------------+
| database() |
+------------+
| qiudao     |
+------------+
1 row in set (0.00 sec)

#创建表，最少有一列
mysql> create table qiudao;
ERROR 1113 (42000): A table must have at least 1 column

mysql> create table qiudao(id int);
Query OK, 0 rows affected (0.02 sec)

#查看表
mysql> show tables;
+------------------+
| Tables_in_qiudao |
+------------------+
| qiudao           |
+------------------+
1 row in set (0.00 sec)

3）建表题：

表名:student
sid
sname
sage
sgender
scometime

#1.建表
mysql> create table student(
    -> sid int,
    -> sname varchar(10),
    -> sage tinyint,
    -> sgender enum('man','woman'),
    -> scometime datetime);
Query OK, 0 rows affected (0.03 sec)

#2.插入数据
mysql> insert into student values(1,'邱导','-10','woman',now());

#3.查看数据
mysql> select * from student;
+------+--------+------+---------+---------------------+
| sid  | sname  | sage | sgender | scometime           |
+------+--------+------+---------+---------------------+
|    1 | 邱导   |  -10 | woman   | 2020-04-17 09:38:46 |
+------+--------+------+---------+---------------------+
1 row in set (0.00 sec)

4）数据类型

int：			整数 -2^31 ~ 2^31-1    （-2147483648  ~  2147483647）
tinyint：		最小整数 -128 ~ 127		#一般年龄使用
varchar：		字符类型（变长）		 #身份证 34050619880908216X
char：			字符类型（定长）
enum：			枚举类型			  	#填写性别，指定多个选项，选择其中一个
datetime：		时间类型				#年月日时分秒 如：20200419102538

5）建表数据属性

not null： 		非空
primary key： 	主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 	单独的唯一的
default： 		默认值
unsigned： 		无符号，非负数		#添加unsigned属性，会加到数据属性中，所以把这个属性写到数据属性后面
comment： 		注释

primary key = unique key + not null

6）加上建表属性创建学生表

#1.创建表
create table student(
sid int unsigned not null primary key auto_increment comment '学号',
sname varchar(10) not null comment '学生姓名',
sage tinyint unsigned not null comment '学生年龄',
sgender enum('m','f') not null default 'm' comment '学生性别',
scometime datetime default now() comment '入学时间',
sbirthday datetime comment '学生生日',
sclass varchar(20) comment '学生班级');

#2.查看建表语句
mysql> show create table student;
| student | CREATE TABLE `student` (
  `sid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
  `sname` varchar(10) NOT NULL COMMENT '学生姓名',
  `sage` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
  `sgender` enum('m','f') NOT NULL DEFAULT 'm' COMMENT '学生性别',
  `scometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
  `sbirthday` datetime DEFAULT NULL COMMENT '学生生日',
  `sclass` varchar(20) DEFAULT NULL COMMENT '学生班级',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                           |
1 row in set (0.00 sec)

#3.插入语句
mysql> insert into student values(1,'邱导',88,'f',now(),now(),'初三1班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(2,'邱导',88,'f',NULL,NULL,'初三1班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values('4','林',18,'m',NULL,NULL,'初四2班');
Query OK, 1 row affected (0.00 sec)

mysql> insert student(sname,sage) values('哈',18);

#4.查看语句
mysql> select * from student;
+-----+--------+------+---------+---------------------+---------------------+------------+
| sid | sname  | sage | sgender | scometime           | sbirthday           | sclass     |
+-----+--------+------+---------+---------------------+---------------------+------------+
|   1 | 邱导   |   88 | f       | 2020-04-17 10:28:19 | 2020-04-17 10:28:19 | 初三1班    |
|   2 | 邱导   |   88 | f       | NULL                | NULL                | 初三1班    |
|   3 | 邱     |   74 | m       | NULL                | NULL                | 初三1班    |
|   4 | 林     |   18 | m       | NULL                | NULL                | 初四2班    |
+-----+--------+------+---------+---------------------+---------------------+------------+
4 rows in set (0.00 sec)

7）删除表

mysql> drop table student;

8）修改表

#1.修改表名
	方法一
mysql> alter table linux7 rename linux7qi;
	方法二			 原表名          新表名
mysql> rename table linux6.class_table to linux6.kechengbiao;
					 库名		旧表名		   库名		新表名
#2.在最后添加列
mysql> alter table linux7qi add zy varchar(10);

#3.添加多个列
mysql> alter table linux7qi add qiudao varchar(10),add dongge varchar(10);

#4.添加列到表前面
mysql> alter table linux7qi add xiaohong varchar(10) first;

#5.指定位置添加列
mysql> alter table linux7qi add wupeng varchar(10) after zy;

#6.删除指定列
mysql> alter table linux7qi drop gcc;

#7.修改列的数据类型
mysql> alter table linux7qi modify zy int;

#8.修改列及属性
mysql> alter table linux7qi change dongge gcc int;

三、DCL语句 数据控制语言（GRANT，REVOKE）

1.GRANT 授权

#1.授权语句
mysql> grant all on *.* to root@'%' identified by '123';

#2.查看用户权限
mysql> select * from mysql.user\G
mysql> show grants for root@'%';

max_queries_per_hour：一个用户每小时可发出的查询数量
mysql> grant all on *.* to test@'localhost' identified by '123' with max_queries_per_hour 2;
Query OK, 0 rows affected (0.00 sec)

max_updates_per_hour：一个用户每小时可发出的更新数量

max_connections_per_hour：一个用户每小时可连接到服务器的次数
mysql> grant all on *.* to test1@'localhost' identified by '123' with max_connections_per_hour 1;
Query OK, 0 rows affected (0.00 sec)

max_user_connections：允许同时连接数量
mysql> grant all on *.* to test2@'localhost' identified by '123' with max_user_connections 1;
Query OK, 0 rows affected (0.00 sec)

2.回收权限 revoke

#1.回收权限
mysql> revoke drop on *.* from root@'%';
Query OK, 0 rows affected (0.00 sec)

#2.查看权限，可以知道所有的权限都包含什么
mysql> show grants for root@'%';

#3.所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE, DROP

3.授权一个超级管理员

mysql> grant all on *.* to lhd@'%' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)

四、DML 数据操作语言 （INSERT，DELETE，UPDATE）

1.INSERT命令

#1.插入数据之前，一定先看表结构和建表规则
mysql> desc student;
mysql> show create table student;

#2.插入数据（不规范的写法）
mysql> insert into student values(6,'林',18,'m',NULL,NULL,NULL);

#3.插入一条数据（规范写法）
mysql> insert student(sname,sage) values('达',18);
mysql> insert student(sname,sage,sgender) values('丽',18,'f');

#4.插入多条数据（规范写法）
mysql> insert student(sname,sage,sgender) values('丽',18,'f'),('艺',18,'f'),('张音',18,'f');

2.update命令

#1.修改数据之前一点先查看数据
mysql> select * from student;
mysql> select * from qiudao.student;

#2.修改数据，错误的方法，这样会将整列都修改
mysql> update student set sgender'm';

#3.使用update一定要加where条件
mysql> update qiudao.student set sgender='f' where sid=11;
mysql> update qiudao.student set sgender='m' where sname='邱导' and sage='88';  #没有主键指点多个值

#4.如果要求整列修改
mysql> update student set sgender'm' where 1=1;

3.delete命令

#1.删除数据之前一定先查看数据
mysql> select * from student;
mysql> select * from qiudao.student;

#2.删除数据，错误的方法，会清空整个表
mysql> delete from qiudao.student;

#3.使用delete一定要加where条件
mysql> delete from qiudao.student where sid=1;
mysql> delete from qiudao.student where sname='林' and sage='18';

#4.清空表
truncate table student;

4.使用update代替delete

1）添加一列状态列

mysql> alter table student add status enum('1','0') default 1;

2）使用update修改数据状态

mysql> update student set status='0' where id=14;

3）查询有效的数据

mysql> select * from student where status=1;

五、DQL 数据查询语言（SELECT,SHOW,DESC）

show

#查看所有库
show databases;
#查看所有表
show tables;

#查看所有可用的字符集
show character set;
#查看支持的所有检验规则
show collation;
#查看GBK字符集所有的校对规则
show collation like  'gbk%';

#查看数据库所有变量和值
show variables;
#查看 MySQL 数据库服务器和数据库字符集
show variables like '%char%';
#当前服务器字符集
show variables like 'character_set_server';
#当前服务器字符集对应的校验规则
show variables like 'collation_server';
#当前数据库字符集
show variables like 'character_set_database';
#当前数据库字符集对应的校对规则
show variables like 'collation_database';

#查看 countries 数据表的列（column）信息
show full columns from countries;

#查看当前数据库有哪些线程正在运行 参考： https://blog.csdn.net/u013419838/article/details/100145286
#如果不使用FULL关键字，则只在Info字段中显示每个语句的前100个字符
show full processlist;
show processlist;
+----+------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host      | db    | Command | Time | State | Info             |
+----+------+-----------+-------+---------+------+-------+------------------+
|  1 | root | localhost | mysql | Query   |    0 | init  | show processlist |
+----+------+-----------+-------+---------+------+-------+------------------+
1 row in set (0.00 sec)
Id 		线程id，标识符，当线程卡死或者严重影响系统性能时，可以kill Id 
User 	当前用户名 
Host 	当前线程语句发出的客户端Ip地址和端口号，用来追踪是那台服务器执行的语句 
db 		当前线程连接的数据库名称 
Command 显示当前线程执行的命令，休眠（sleep）、查询（query）、连接（connect）为主要的命令的类型 
Time 	显示线程当前状态的执行时间，单位是秒 
State 	显示当前线程中sql语句的执行状态，详细信息下面列出 
Info 	显示当前线程执行的sql语句 

#查看建库建表语句
show create database mcy;
show create table t1;

# 总结：
1、查看所有库、某个库下所有表
show databases;
show tables;
2、查看所有字符集和效验规则
show character set;
show collation;
3、查看系统变量和其值
show variables;
show variables like '%char%';
4、查看当前数据库正在运行的线程
show processlist;
show full processlist;
5、查看建库、建表语句
show create database mcy;
show create table t1;
6、查看数据库状态信息
show status;

desc

information_schema.character_sets 表 用于查看字符集的详细信息
1、# desc 查看表结构的详细信息（此表由哪些列组成，每一列的字符类型是什么，每一列的属性有哪些）
mysql> desc information_schema.character_sets;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME   | varchar(32) | NO   |     |         |       |
| DEFAULT_COLLATE_NAME | varchar(32) | NO   |     |         |       |
| DESCRIPTION          | varchar(60) | NO   |     |         |       |
| MAXLEN               | bigint(3)   | NO   |     | 0       |       |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

character_set_name		字符集名
default_collate_name	默认排序规则　　
description				描述信息
maxlen					单个字符最大占用字节数

select
    character_set_name		字符集名
    default_collate_name	默认排序规则名
    description				描述信息
    maxlen					单个字符最大占用字节数
from
    information_schema.character_sets;

2、# desc 按降序排列，查看 information_schema 库中 character_sets 表，并按 maxlen 列的值降序排列
	   查询	所有 来自                 库.表			   排列依据		列	降序
mysql> select * from information_schema.character_sets order by maxlen desc;

select

1.查询数据

#1.查看表中所有数据，如果数据量太大，会导致内存撑爆
mysql> select * from qiudao.student;

#2.查看表中所有数据之前先查看下数据量
mysql> select count(*) from qiudao.student;

#3.查看指定列的数据
mysql> select user,host from mysql.user;

#4.按条件查询
mysql> select * from information_schema.character_sets where maxlen=4;
mysql> select * from information_schema.character_sets where maxlen>1 and maxlen<4;
mysql> select * from information_schema.character_sets where maxlen=4 or maxlen=3;
mysql> select sname,sage from qiudao.student where sage=18 and sgender='f';

2.查询数据测试

#1.导入数据
mysql> \. /tmp/world.sql
或
mysql> source /tmp/world.sql

#2.查看库和表
	#进入库
mysql> use world
Database changed
	#查看所在库
mysql> select database();
+------------+
| database() |
+------------+
| world      |
+------------+
1 row in set (0.00 sec)
	#查看当前库中所有表的列表
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

#3.查看city表结构
mysql> desc city;

#4.查询city表数据
mysql> select * from city;

#5.查询指定列数据
mysql> select name,population from city;

#6.按照人口数量排序
#升序
mysql> select name,population from city order by population;
#降序
mysql> select name,population from city order by population desc;
#复合排序，先按列n1升序排列，再按n2降序排列
mysql> select * from city order by CountryCode asc,population desc limit 10;
select * from m1 order by n1 asc,n2 desc;

#7.只看前十条
mysql> select name,population from city limit 10;
mysql> select name,population from city order by population limit 10;

#8.按照步长查询数据
mysql> select id,name,population from city limit 10,10;
#前面的10代表开始，后面的10代表再次展示个数称为步长
#商品也翻页原理：
mysql> select id,name,population from city limit 0,60;
mysql> select id,name,population from city limit 60,60;
mysql> select id,name,population from city limit 120,60;

3.按条件查询

#1.条件查询where可以接的符号
where接条件符号：= < > >= <= != <>
where接条件：or and like
= : 精确查询
< > >= <= != <> : 范围查询
like : 模糊查询

#2.精确查询：中国城市人口
mysql> select name,population from city where CountryCode='CHN';

#3.精确查询：黑龙江省的人口
mysql> select name,population from city where District='heilongjiang';

#4.范围查询：中国人口数量小于100000的城市
mysql> select name,population from city where population < 100000 and countrycode='CHN';

#5.模糊查询
#国家代码以H结尾的
mysql> select * from city where countrycode like '%H';
#国家代码以H开头的
mysql> select * from city where countrycode like 'H%';
#国家代码包含H的
mysql> select * from city where countrycode like '%H%';

#6.查询中国或美国的城市人口 or in
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');
#排除
mysql> select * from city where countrycode != 'CHN' and countrycode != 'USA';
mysql> select * from city where countrycode not in ('CHN','USA');

#7.联合查询 union all（速度最快）
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

select 高级用法

1.传统连接（连表查询，多表联查）

#集合
[qiudao,zengdao,qiandao]
[80,90,100]

id:[1,2,3]
name:[qiudao,zengdao,qiandao]

id:[1,2,3]
mark:[80,90,100]

#1.创建student表
mysql> create table student(id int,name varchar(10));

#2.创建一个成绩表
mysql> create table score(id int,mark int);

#3.插入数据
mysql> insert into student values(1,'qiudao'),(2,'zengdao'),(3,'qiandao');
mysql> insert into score values(1,80),(2,90),(3,100);

#4.查看数据
mysql> select * from student;
+------+---------+
| id   | name    |
+------+---------+
|    1 | qiudao  |
|    2 | zengdao |
|    3 | qiandao |
+------+---------+
3 rows in set (0.00 sec)

mysql> select * from score;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   90 |
|    3 |  100 |
+------+------+
3 rows in set (0.00 sec)

#5.连表查询数据
1.只查询邱导成绩
mysql> select student.name,score.mark from student,score where student.id=1 and score.id=1;
mysql> select student.name,score.mark from student,score where student.id=score.id and name='qiudao';
2.查询所有学生成绩
mysql> select student.name,score.mark from student,score where student.id=score.id;

1）连表查询练习1：

# 世界上小于100人的城市是哪个国家的？请列出城市名字，人口数量，国家名字

#1.看看查看的内容都有哪些？
城市人口数量  城市名字  国家名

#2.找出要输出的列
城市名字	 城市人口			国家名
city.name	city.population	    country.name

#3.找出两个表相关联的字段
city.countrycode
country.code

#4.编写语句
mysql> select city.name,city.population,country.name from city,country where city.countrycode=country.code and city.population < 100;
+-----------+------------+----------+
| name      | population | name     |
+-----------+------------+----------+
| Adamstown |         42 | Pitcairn |
+-----------+------------+----------+
1 row in set (0.00 sec)

2）连表查询练习2：

# 世界上小于100人的城市是哪个国家的，使用什么语言？请列出城市名字，人口数量，国家名字，什么语言？

#1.看看查看的内容都有哪些？找出要输出的列
城市名字	 城市人口			国家名			 语言
city.name	city.population	    country.name	countrylanguage.language

#2.找出三个表相关联的字段
city.countrycode
country.code
countrylanguage.countrycode

#3.编写语句
mysql> select city.name,city.population,country.name,countrylanguage.language from city,country,countrylanguage where city.countrycode=country.code and country.code=countrylanguage.countrycode and city.population < 100;
+-----------+------------+----------+-------------+
| name      | population | name     | language    |
+-----------+------------+----------+-------------+
| Adamstown |         42 | Pitcairn | Pitcairnese |
+-----------+------------+----------+-------------+
1 row in set (0.02 sec)

2.自连接

自连接的表必须有共同的列名和数据

#自连接格式：
select * from 表1 NATURAL JOIN 表2 where 条件
select 表1.列2名,表1.列5名,表2.列2名 from 表1 NATURAL JOIN 表2 where 条件 order by 排序的列名

#查询人口数量大于100万的城市名字，国家代码，国家语言，切升序排序
SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage
WHERE city.population > 1000000
ORDER BY population;

#修改查询语句测试
SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city,countrylanguage
WHERE city.countrycode=countrylanguage.countrycode and city.population > 10000000
ORDER BY population;

#查不出来，因为没有对应字段有相同值
select city.name,city.population,country.name from city NATURAL JOIN country where city.population < 100;

#注意：
	1.自连接会自己去两个表查询相同字段，相同值
	2.两个表中的字段和数据必须一模一样

3.内连接

#1.内连接格式：
select * from 表1 join 表2 on 相关联条件 where 条件

#2.例子1：查询人口数量小于100的城市是哪个国家的，国家代码是什么？
select city.name,city.countrycode,country.name
from country join city on city.countrycode=country.code
where city.population < 100;

#3.例子2：查询人口数量小于100的城市是哪个国家的，人口数量是多少？
select city.name,city.population,country.name
from country join city on city.countrycode=country.code
where city.population < 100;

#4.注意：
	表1 （小表）
	表2 （大表）
先处理小表数据再处理大表数据，速度会快一些，不过计算机速度很快，所以没多大影响

4.外连接

1）左外链接

select city.name,city.countrycode,country.name 
from city left join country 
on city.countrycode=country.code 
and city.population<100;

2）右外连接

select city.name,city.countrycode,country.name 
from city right join country 
on city.countrycode=country.code 
and city.population<100;

5.UNION（合并查询）

#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';
#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');
替换为：
mysql> select * from city where countrycode='CHN' 
union all
select * from city where countrycode='USA' limit 10

去重？？？

#Sql题目：
年级表grade有字段 grade_id（年级ID），teachers(教师数量)
学生表student有字段 grade_id（年级ID），student_id(学生id)，student_name (学生姓名)

求所有含”王” 学生中对应的教师总数量是多少，注意需要去重。

#建教师表
create table grade(
grade_id int unsigned not null primary key auto_increment comment '年级ID',
teachers int unsigned not null comment '教师数量');

#插入教师表数据
insert grade(grade_id,teachers) values(1,17),(2,18),(3,20);

#建学生表
create table student(
grade_id int unsigned not null comment '年级ID',
student_id int unsigned not null primary key auto_increment comment '学生ID',
student_name varchar(10) not null comment '学生姓名');

#插入学生表数据
insert student(grade_id,student_id,student_name) values(1,1,'张三'),(2,2,'王大'),(3,3,'王二'),(3,4,'王三');

#单表去重 ？？？


#数据条数查看  类似于 wc -l
mysql> select count(grade_id) from grade;

#数据去重	类似于 uniq -c
mysql> select distinct(grade_id) from student;

字符集

1.什么是字符集

字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

最早的字符集：ASCII码

中国字符集：GBK GB2312 utf8 utf8mb4
日本：shift-jis
韩国：EUC-kr

万国编码：Unicode

常用字符集： gbk：汉字占2个字节
		   utf8：汉字占3个字节
		   utf8mb4：汉字占4个字节
		   
#数据库查看字符集
mysql> show charset;

2.校验规则

#查看校验规则
mysql> show collation;
| latin1_bin               | 
| latin1_general_ci        | 
| latin1_general_cs        |

#校验规则区别
1.ci：不区分大小写
2.cs或bin：区分大小写

#已存在的字段不能再添加
mysql> alter table city add nAME varchar(10);
ERROR 1060 (42S21): Duplicate column name 'nAME'

3.统一字符集

#1.xshell字符集

#2.linux系统字符集
临时修改：
[root@db02 ~]# LANG=zh_CN.UTF-8
永久修改：
[root@db02 ~]# vim /etc/locale.conf
LANG="zh_CN.UTF-8"

#3.配置数据库字符集
1.cmake指定字符集
-DEFAULT_CHARSET=utf8
-DEFAULT_COLLATION=UTF8_GENERAL_CI

#2.配置数据库设置字符集
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
character-set-server=utf8

#3.创建库或者表的收直接指定字符集
创建库的字符集
mysql> create database code charset gbk collate gbk_chinese_ci;
创建表的字符集
mysql> create table code(id int) charset utf8mb4 collate utf8mb4_general_ci;

#4.修改字符集
修改库的字符集
mysql> alter database linux7 charset utf8 collate utf8_general_ci;
修改表的字符集
mysql> alter table test charset utf8 collate utf8_general_ci;

#5.修改字符集条件
1.字符集范围必须为包含关系，范围小的转换为范围大的

#6.批量修改字符集
1.导出数据库
mysqldump -u root -p -A > /tmp/123.sql			#导出所有sql
mysqldump -B user1 user2 > /tmp/123.sql			#导出指定库的sql

2.修改sql语句中的字符集
vim /tmp/123.sql
:s#utf8#utf8mb4#g

3.导入数据库
mysql -u root -p < /tmp/123.sql

set

mysqld服务器维护两种变量
全局变量 影响 服务器的全局操作
会话变量 影响 具体客户端连接相关操作

服务器启动时，将所有全局变量初始化为默认值。可以在选项文件或命令行中指定的选项来更改这些默认值。
服务器启动后，通过连接服务器并执行 SET GLOBAL var_name 语句可以更改动态全局变量。
要想更改全局变量，必须具有SUPER权限。

服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。
客户可以通过 SET SESSION var_name 语句来更改动态会话变量。
设置会话变量不需要特殊权限，但客户可以只更改自己的会话变量，而不更改其它客户的会话变量。

任何访问全局变量的客户端都可以看见对全局变量的更改。
只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。
不会影响已经连接上的客户端的会话变量(甚至是执行SET GLOBAL语句的客户端)。

当使用启动选项设置变量时，变量值可以使用后缀K、M或G分别表示千字节、兆字节或gigabytes。

例如，下面的命令启动服务器时的键值缓冲区大小为16 megabytes 后缀的大小写没关系;16M和16m是同样的。
mysqld --key_buffer_size=16M

运行时，使用SET语句来设置系统变量。此时，不能使用后缀，但值可以采取下列表达式：

mysql> SET sort_buffer_size = 10 * 1024 * 1024;

要想显式指定是否设置全局或会话变量，使用GLOBAL或SESSION选项：

mysql> SET GLOBAL sort_buffer_size = 10 * 1024 * 1024;

mysql> SET SESSION sort_buffer_size = 10 * 1024 * 1024;

两个选项均没有，则语句设置会话变量。