客户端的命令

MySQL

MySQL：
-u: 指定用户
-p：指定密码
-h：指定主机域
-S：指定socket
-P：指定端口
-e：指定SQL语句
status | \s 查看MySQL服务端状态
prompt = \u@\h [\d] > 开启命令提示符，写入，my.cnf配置文件重启服务即可
#mysql5.6退出
quit|exit|\q|Ctrl+c
# mysql5.7退出
quit|exit|\q
#查看帮助信息
help|?
# mysql5.6中止SQL语句
clear|\c|Ctrl+c
# mysql5.7中止SQL语句
clear|\c

mysqladmin

想使用mysqladmin前提：服务端必须开启！！！

# 1.修改密码
[root@db03 ~]# mysqladmin -uroot -p123 password '123'
# 2.检测MySQL是否存活
[root@db03 ~]# mysqladmin -uroot -p123 ping
# 3.库外创建数据库
[root@db03 ~]# mysqladmin -uroot -p123 create 库名
# 4.删除数据库
[root@db03 ~]# mysqladmin -uroot -p123 drop 库名
# 5.查看MySQL默认配置
[root@db03 ~]# mysqladmin -uroot -p123 variables
# 6.库外刷新授权表
[root@db03 ~]# mysqladmin -uroot -p123 flush-host
[root@db03 ~]# mysqladmin -uroot -p123 reload
# 7.刷新日志
[root@db03 ~]# mysqladmin -uroot -p123 flush-log
# 8.停库
[root@db03 ~]# mysqladmin -uroot -p123 shutdown 

mysqldump

#导出数据
[root@db03 ~]# mysqldump -u[用户] -p[密码] -B [库名] >/存放路径
#导入数据
[root@db03 ~]# mysqldump -u[用户] -p[密码]  >/存放路径

SQL语句

DDL（Database Definition Language）数据定义语言

DDL用于定义和管理数据库的结构，包括表、索引、视图和其他数据库对象。它包含以下类型的SQL语句：

CREATE：用于创建数据库对象，如表、视图、索引等

#数据库的增：
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...
create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name 
    | COLLATE [=] collation_name
}
CREATE DATABASE语句用于创建新的数据库或模式（schema），你可以选择性地指定字符集和排序规则作为选项。
如果已经存在同名的数据库（当加上IF NOT EXISTS选项时），则不会创建新的数据库，避免重复。
root@localhost [(none)] >create schema jiujiu;
root@localhost [(none)] >create database test;
数据库已存在情况下让其不报错：
root@localhost [(none)] >create schema if not exists jiujiu;
指定字符集和校验规则：
root@localhost [(none)] >create database if not exists test charset utf8 collate
utf8_bin;
修改默认字符集：
vim /etc/my.cnf
[mysqld]
character_set_server=utf8

DROP：用于删除数据库对象，如表、视图、索引等。

#删
root@localhost [mysql] >drop database jiujiu1;
root@localhost [mysql] >drop database if exists jiujiu1;

ALTER：用于修改数据库对象的结构，如添加、修改、删除列或约束等。

#改
alter database 库名 修改内容;
root@localhost [(none)] >alter database jiujiu charset utf8;
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)] >show create database jiujiu;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| jiujiu | CREATE DATABASE `jiujiu` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [(none)] >alter database jiujiu collate utf8_bin;
Query OK, 1 row affected (0.01 sec)
root@localhost [(none)] >alter database jiujiu charset latin1 collate latin1_general_ci; //该ALTER DATABASE命令用于修改名为jiujiu的数据库的字符集为latin1，并且修改排序规则为latin1_general_ci，从而影响数据库中的字符串存储和排序方式。
注意，该命令只会影响之后在该数据库中创建的表，已经存在的表的字符集和排序规则不会受到影响。

针对表的操作

#查看创建表语句帮助
mysql> help create table
#创建表  //必须指定列和数据类型
mysql> create table student(
sid INT,
sname VARCHAR(20),
sage TINYINT,
sgender ENUM('m','f'),
cometime DATETIME);
#数据类型
## 数字类型
int：整形 -2^31 ~ 2^31-1
bigint: 最大整型
tinyint：最小整形 -128 ~ 127
## 字符串类型
name
varchar(10) // 可变长类型 写入几个字符就占几个
char(10) // 定长类型 不管输入几个字符都是占用全部内
## 枚举类型  输入只能是A或者B
enum('f','m')
male female
enum('A','B','C','D')
## 浮点型
float 单精度
double 双精度
## 时间戳类型
timestamp 1970-1-1
datetime 1000-1-1
# 字段属性（约束）
not null：非空
primary key：主键 唯一且不能为空（一张表中只能创建一个主键）
auto_increment：自增
unique key：唯一键 唯一可以为空
default：默认值
unsigned：无符号（非负数）
comment：注释
主键=唯一键+not null
#创建命令
create table 库名 表名;
root@localhost [(none)] >create table jiujiu.stu10(name varchar(10),age tinyint);
#删除  
drop table 表名;
root@localhost [(none)] >drop table jiujiu.zls;
# 改
alter table
alter table 表名 add 字段名 数据类型 属性约束;
alter table 表名 add 字段名 数据类型 属性约束 first;
alter table 表名 add 字段名 数据类型 属性约束 after 字段名;
# 修改表名
语法：alter table [原表名] raname [新表名]
alter table test rename test1;
# 添加字段
示例：添加一个sakura的字段
alter table test add sakura bigint(10) not null;
## 添加字段（将字段放在最后一列）
mysql> alter table test1 add abc varchar(1) not null;
## 添加字段（放到指定字段的后面）
mysql> alter table test1 add cbd int after abc;
# 添加字段（将字段放到最前面）
mysql> alter table test1 add def int first; 
# 一次性添加多个字段 //添加aaa和bbb字段
mysql> alter table test1 add aaa int,add bbb int;
## 删除字段
mysql> alter table [表名] drop [字段名];
# 字段修改
 - change
 - modify
语法：alter table 表名 modify 字段名 数据类型 约束属性;
mysql> alter table test1 modify aaa varchar(10) comment 'xxx';
语法：alter table 表名 change 旧字段名 新字段名 数据类型 约束属性;
mysql> alter table test1 change bbb ccc char(10) comment 'yyy';

练习

建库：

库名：linux50 字符集：utf8 校验规则：utf8_general_ci

create database linux50  charset utf8 collate utf8_general_ci;
插入内容：
create table linux50.student(
sno bigint(20) not null primary key auto_increment comment '学号（主键）',
sname varchar(10) not null comment '学生姓名',
sage tinyint unsigned not null comment '学生年龄',
ssex enum('0','1') not null default '1' comment '学生性别（1是男，0是女）默认为男）',
sbirthday datetime null comment '学生生日',
class varchar(5) not null comment '学生班级');

course

CREATE TABLE `course` (
`cno` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '课程号（主键）',
`cname` varchar(10) NOT NULL COMMENT '课程名称',
`tno` varchar(10) NOT NULL COMMENT '教师编号',
PRIMARY KEY (`cno`,`cname`,`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

score

CREATE TABLE score(
sno BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '学号（主键）',
cno BIGINT(20) NOT NULL COMMENT '课程号（主键）',
mark FLOAT(4,1) NOT NULL COMMENT '成绩',
PRIMARY KEY(sno,cno));

teacher

CREATE TABLE `teacher` (
`tno` bigint(3) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '教师编号（主键）',
`tname` varchar(10) NOT NULL COMMENT '教师姓名',
`tage` tinyint(3) unsigned NOT NULL COMMENT '教师年龄',
`tsex` enum('1','0') NOT NULL DEFAULT '1' COMMENT '教师性别（1是男，0是女）默认为
男）',
`prof` varchar(10) NOT NULL COMMENT '教师职称',
`depart` varchar(10) NOT NULL COMMENT '教师部门',
PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DML

# 对表中数据进行操作
# 对数据的增删改
## 增
insert
insert into 表名('字段1','字段2') 值('值1','值2');
mysql> insert into student(sname,sage,sbirthday,class)values('yuanli',3,NOW(),'Linux5');
mysql> insert student(sname,sage,sbirthday,class) values('yuanli',3,NOW(),'L5');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+-----+--------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+--------+------+------+---------------------+-------+
| 1 | yuanli | 3 | 1 | 2023-07-25 10:10:54 | L5 |
| 2 | yuanli | 3 | 1 | 2023-07-25 10:11:51 | L5 |
+-----+--------+------+------+---------------------+-------+
mysql> insert student(sname,sage,sbirthday,class) values('yuanli',3,NOW(),'L5'),
('wyd',2,'2021-11-12 00:00:00','L6');
mysql> select * from student;
+-----+--------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+--------+------+------+---------------------+-------+
| 1 | yuanli | 3 | 1 | 2023-07-25 10:10:54 | L5 |
| 2 | yuanli | 3 | 1 | 2023-07-25 10:11:51 | L5 |
| 3 | yuanli | 3 | 1 | 2023-07-25 10:13:19 | L5 |
| 4 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 |
+-----+--------+------+------+---------------------+-------+
mysql> insert student values(5,'yuanli',3,'1',NOW(),'L5'),(6,'wyd',2,'0','2021-11-12
00:00:00','L6');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-----+--------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+--------+------+------+---------------------+-------+
| 1 | yuanli | 3 | 1 | 2023-07-25 10:10:54 | L5 |
| 2 | yuanli | 3 | 1 | 2023-07-25 10:11:51 | L5 |
| 3 | yuanli | 3 | 1 | 2023-07-25 10:13:19 | L5 |
| 4 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 |
| 5 | yuanli | 3 | 1 | 2023-07-25 10:15:07 | L5 |
| 6 | wyd | 2 | 0 | 2021-11-12 00:00:00 | L6 |
+-----+--------+------+------+---------------------+-------+
## 删   //在使用delete的时候，后面必须跟上where判断条件！！！！
delete
delete from 库名.表名;
delete from 库名.表名 where;
mysql> delete from linux50.student where sno=7 or sno=10;
mysql> delete from linux50.student where sname='wyd';
## 删除所有内容  //where条件只要为真即可
mysql> delete from linux50.student where 1=1;
#删除用户  //指定用户和主机域
mysql> delete from mysql.user where user='test1' and host='%';
Query OK, 1 row affected (0.00 sec)
# 改
update
update 库.表 set 字段='值';
 更新表student中ssex字段所有值为0：
mysql> update student set ssex='0';
 更新表student中sno等于10的行ssex字段所有值为1：
mysql> update student set ssex='1' where sno=10;
 更新"student"表中所有行的"ssex"列，将其值设置为'1'。条件"where 1=1"实际上是一种始终为真的条件，因此它会将"ssex"列的值统一设置为'1
mysql> update student set ssex='1' where 1=1;

#使用update代替delete做伪删除
# 1.给表中添加状态列
mysql> alter table student add status enum('0','1') default '1';
# 2.使用update代替delete删除
mysql> update student set status='0' where sno=5;
# 3.使用select查询时，加上条件
mysql> select * from student where status='1';

DCL

# 授权（可以创建用户）
grant
语法：grant 权限 on 库.表 to 用户@'主机域' identified by '密码';
grant 权限 on 库.表 to 用户@'主机域' identified by '密码' with grant option;
#授权(dev1)在所有数据库中的所有表上进行SELECT、UPDATE和INSERT操作的权限，一个小时内最多只能执行2次查询操作
grant select,update,insert on *.* to dev1@'%' identified by '123' with max_queries_per_hour 2;
#授权(de3)在所有数据库中的所有表上进行SELECT、UPDATE和INSERT操作的权限，一个小时之内最多三次查询和一次修改
grant select,update,insert on *.* to dev3@'%' identified by '123' with
 max_queries_per_hour 3 
 max_updates_per_hour 1;
#授权(dev4)在所有数据库中的所有表上进行SELECT、UPDATE和INSERT操作的权限 ，一个小时之内最多三次查询、一次修改和同时连接最大允许一人
grant select,update,insert on *.* to dev4@'%' identified by '123'
 with max_queries_per_hour 3
 max_updates_per_hour 1
 max_connections_per_hour 1;
#授权(dev5)在所有数据库中的所有表上进行SELECT、UPDATE和INSERT操作的权限，同时连接最大允许一人
grant select,update,insert on *.* to dev5@'%' identified by '123' with max_user_connections 1;

max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量
grant option：授权权限
## 回收权限
revoke
mysql> grant all on *.* to test1@'%' identified by '123';
回收用户test1的select权限
mysql> revoke select on *.* from test1@'%';

DQL

## 查看数据库
root@localhost [(none)] >show databases;
## 查看表
root@localhost [(none)] >show tables;   //此方式需要切换到指定的数据库
root@localhost [mysql] >show tables from jiujiu;
## 查看建库语句相关信息
root@localhost [(none)] >show create database jiujiu;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| jiujiu   | CREATE DATABASE `jiujiu` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
## 查看表结构
root@localhost [(none)] >desc test;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| abc       | int(11)             | YES  |     | NULL    |                |
| sno       | bigint(20)          | NO   | PRI | NULL    | auto_increment |
| sname     | varchar(10)         | NO   |     | NULL    |                |
| sage      | tinyint(3) unsigned | NO   |     | NULL    |                |
| ssex      | enum('0','1')       | NO   |     | 1       |                |
| sbirthday | datetime            | YES  |     | NULL    |                |
| class     | varchar(5)          | NO   |     | NULL    |                |
| sakura    | bigint(10)          | NO   |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
## 查看数据库
root@localhost [(none)] >show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux50            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
## 查看表   //使用show tables时需进到要查看的库
root@localhost [(none)] >show tables;
语法： show tables from [库名]
root@localhost [mysql] >show tables from linux50;
+-------------------+
| Tables_in_linux50 |
+-------------------+
| test              |
+-------------------+
1 row in set (0.00 sec)
## 查看建库语句相关信息
root@localhost [(none)] >show create database linux50;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| linux50  | CREATE DATABASE `linux50` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
## 查看表结构
root@localhost [(none)] >desc linux50.test;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| abc       | int(11)             | YES  |     | NULL    |                |
| sno       | bigint(20)          | NO   | PRI | NULL    | auto_increment |
| sname     | varchar(10)         | NO   |     | NULL    |                |
| sage      | tinyint(3) unsigned | NO   |     | NULL    |                |
| ssex      | enum('0','1')       | NO   |     | 1       |                |
| sbirthday | datetime            | YES  |     | NULL    |                |
| class     | varchar(5)          | NO   |     | NULL    |                |
| sakura    | bigint(10)          | NO   |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
## 查看建表语句（注释信息）
mysql> show create table linux50.test;
| test  | CREATE TABLE `test` (
  `abc` int(11) DEFAULT NULL,
  `sno` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '学号（主键）',
  `sname` varchar(10) NOT NULL COMMENT '学生姓名',
  `sage` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
  `ssex` enum('0','1') NOT NULL DEFAULT '1' COMMENT '学生性别（1是男，0是女）默认为男）',
  `sbirthday` datetime DEFAULT NULL COMMENT '学生生日',
  `class` varchar(5) NOT NULL COMMENT '学生班级',
  `sakura` bigint(10) NOT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
## 查看创建用户语句 
mysql> show grants for test1@'%';
INSERT, SELECT,UPDATE, DELETE, CREATE, DROP, 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 TABLESPACE
## 查看当前所在数据
mysql> select database();
+------------+
| database() |
+------------+
| linux50    |
+------------+
1 row in set (0.00 sec)
## 查看字符集
mysql> show charset;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)
## 查看校验规则
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
| koi8r_bin                | koi8r    |  74 |         | Yes      |       1 |
| latin1_german1_ci        | latin1   |   5 |         | Yes      |       1 |
| latin1_swedish_ci        | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci         | latin1   |  15 |         | Yes      |       1 |
| latin1_german2_ci        | latin1   |  31 |         | Yes      |       2 |
| latin1_bin               | latin1   |  47 |         | Yes      |       1 |
.......
## 查看存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
#查看sql语句进程
root@localhost [linux50] > show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host      | db      | Command | Time | State | Info             |
+----+------+-----------+---------+---------+------+-------+------------------+
| 17 | root | localhost | linux50 | Query   |    0 | init  | show processlist |
+----+------+-----------+---------+---------+------+-------+------------------+
1 row in set (0.00 sec)
查看所有：
root@localhost [linux50] > show full processlist;
# 查看变量
mysql> show variables like '%log%';
+-----------------------------------------+-------------------------------+
| Variable_name                           | Value                         |
+-----------------------------------------+-------------------------------+
| back_log                                | 80                            |
| binlog_cache_size                       | 32768                         |
| binlog_checksum                         | CRC32                         |
| binlog_direct_non_transactional_updates | OFF                           |
| binlog_error_action                     | IGNORE_ERROR                  |
| binlog_format                           | STATEMENT                     |
| binlog_gtid_simple_recovery             | OFF                           |
| binlog_max_flush_queue_time             | 0                             |
| binlog_order_commits                    | ON                            |
| binlog_row_image                        | FULL                          |
| binlog_rows_query_log_events            | OFF                           |
| binlog_stmt_cache_size                  | 32768                         |
| binlogging_impossible_mode              | IGNORE_ERROR                  |
| expire_logs_days                        | 0                             |
........

select

# 查询所有数据
root@localhost [linux50] >select * from linux50.test;
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   1 | 张三   |   20 | 1    | 2003-01-01 00:00:00 | 5     |      0 |
| NULL |   2 | 李四   |   22 | 0    | 2001-03-15 12:34:56 | 6     |      0 |
| NULL |   3 | 王五   |   22 | 0    | 2001-03-15 12:34:56 | 7     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
3 rows in set (0.00 sec)
#查看单段数据
select sname,ssex,class from student;
# 使用where条件查询数据
root@localhost [linux50] >select * from linux50.test where sno=2;
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   2 | 李四   |   22 | 0    | 2001-03-15 12:34:56 | 6     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
1 row in set (0.00 sec)
## 多条件查询
root@localhost [linux50] >select * from linux50.test where sno=1 or sno=3;
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   1 | 张三   |   20 | 1    | 2003-01-01 00:00:00 | 5     |      0 |
| NULL |   3 | 王五   |   22 | 0    | 2001-03-15 12:34:56 | 7     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
2 rows in set (0.00 sec)
# 范围查询 //满足sno大于1的行
root@localhost [linux50] >select * from linux50.test where sno>1;
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   2 | 李四   |   22 | 0    | 2001-03-15 12:34:56 | 6     |      0 |
| NULL |   3 | 王五   |   22 | 0    | 2001-03-15 12:34:56 | 7     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
2 rows in set (0.00 sec)
# 模糊查询 like
root@localhost [linux50] >select * from linux50.test where sname like '%五';
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   3 | 王五   |   22 | 0    | 2001-03-15 12:34:56 | 7     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
1 row in set (0.00 sec)
# 多条件查询 or and
root@localhost [linux50] >select * from test where sno='1' or sno='3';
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   1 | 张三   |   20 | 1    | 2003-01-01 00:00:00 | 5     |      0 |
| NULL |   3 | 王五   |   22 | 0    | 2001-03-15 12:34:56 | 7     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
2 rows in set (0.00 sec)
root@localhost [linux50] >select * from test where sno in ('1','3');
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   1 | 张三   |   20 | 1    | 2003-01-01 00:00:00 | 5     |      0 |
| NULL |   3 | 王五   |   22 | 0    | 2001-03-15 12:34:56 | 7     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
2 rows in set (0.00 sec)
# 联合查询 union all
root@localhost [linux50] >select * from test where sno='1' union all select * from test where sno='2'; 
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   1 | 张三   |   20 | 1    | 2003-01-01 00:00:00 | 5     |      0 |
| NULL |   2 | 李四   |   22 | 0    | 2001-03-15 12:34:56 | 6     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
2 rows in set (0.00 sec)
# 分页查询 limit
查看前两行：
root@localhost [linux50] >select * from test limit 2;
+------+-----+--------+------+------+---------------------+-------+--------+
| abc  | sno | sname  | sage | ssex | sbirthday           | class | sakura |
+------+-----+--------+------+------+---------------------+-------+--------+
| NULL |   1 | 张三   |   20 | 1    | 2003-01-01 00:00:00 | 5     |      0 |
| NULL |   2 | 李四   |   22 | 0    | 2001-03-15 12:34:56 | 6     |      0 |
+------+-----+--------+------+------+---------------------+-------+--------+
2 rows in set (0.00 sec)
# 联合查询 union all
查询语句的意思是从"city"表中选择所有列，并且只返回满足"countrycode='CHN' or countrycode='USA'"条件的行，
然后按照城市的人口数量（population）进行升序排序。这将返回来自中国（CHN）或美国（USA）的城市数据，
并按照人口数量从小到大排列。
select * from city where countrycode='CHN' or countrycode='USA' order by population;
# 排序查询 order by
order by :升序
order by [列段名] desc ：降序
root@localhost [world] >select * from city where countrycode='CHN' or countrycode='USA' order by population;
root@localhost [world] >select * from city where countrycode='CHN' or countrycode='USA' order by population desc;
#函数
distinct() // 去重函数
count() // 统计（用于，查询出来的结果，统计有多少行）
sum() // 求和
avg() // 求平均值函数
max() // 求最大值函数
min() // 求最小值函数
#使用技巧
1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加
# 统计世界上每个国家的总人口数
select  字段名      函数（需要处理的字段） from 表名 group by 字段名
select countrycode,sum(population) from city group by countrycode;
#统计中国各个省的人口数量(练习)
select district,sum(population) from city where countrycode='CHN' group by district;
#统每个国家的城市数量(练习)
select countrycode,count(name) from city group by countrycode;

高级用法，连表查询

传统连接：

#语法：
>select [列段名] from [表名] where [表一相同字段]=[表二相同字段] and [条件];
#示例
世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population from city,country where city.countrycode=country.code and city.population < 100
select |-----------------列段名--------------|     |-----表名----|     |--------相同字段------------|     |-------条件--------|
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language             |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese         |
+----------+-----------+------------+-------------+

内连接：
语法：join on

#语法
A join B on 1 join C on 2
#以上面的语句为例子
写法一：
select country.name,city.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
写法二：
select country.name,city.name,city.population,countrylanguage.language
from city join country
join countrylanguage
on city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
where city.population < 100;

自连接
自动找到等价条件 natural join，前提条件：
1.等价条件的列名
2.数据值必须一致

select city.name,countrylanguage.language from city natural join countrylanguage where city.population<100;
select  表一字段，      表二字段            from   表一    natural join  表二                条件

外连接：
左连接：left join
左连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则返回NULL值。
左连接以左表为基础，并将右表中符合条件的行与左表进行关联

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

有连接：right join
左连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则返回NULL值。
左连接以左表为基础，并将右表中符合条件的行与左表进行关联。

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