Mysql主键、事务以及高级查询

一、Mysql主键和表的注释

（1）主键概述

当一个表的某个列（项）设置为主键后，这个列的数据不可以重复并且不能为空，一般一张表只有一个主键

• 主键特性:

1. 主键列的值具有唯一性
2. 主键列的值不允许为空（NULL）

• 设置主键

设置主键可以在创建表时指定也可以在原有表的基础上使用alter修改

使用 primary key auto_increment可以配置主键自增，主键自增即在插入值时，不写主键的值，那么主键的值会自加1，只限于主键类型是整数int类型

******搭建mysql，进入
mysql> use aaa;   #进入库aaa
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;  #查看aaa库的所有表
+---------------+
| Tables_in_aaa |
+---------------+
| bbb           |
+---------------+
1 row in set (0.00 sec)

mysql> desc bbb; #查看bbb表的结构
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| sex   | char(10) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into bbb values(1,"zhangsan","man","18"); #插入数据
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbb values(2,"wangwu","woman","20");
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbb values(3,"liliu","woman","22");
Query OK, 1 row affected (0.01 sec)

mysql> select * from bbb;  #查看bbb表的数据
+------+----------+-------+------+
| id   | name     | sex   | age  |
+------+----------+-------+------+
|    1 | zhangsan | man   |   18 |
|    2 | wangwu   | woman |   20 |
|    3 | liliu    | woman |   22 |
+------+----------+-------+------+
3 rows in set (0.00 sec)

-创建新表配置主键

mysql> create table aaa (id int primary key,name char(10));  #创建一个表，主键设置为id
Query OK, 0 rows affected (0.00 sec)

mysql> show create table aaa;  #查看aaa表的详细信息
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| aaa   | CREATE TABLE `aaa` (
  `id` int(11) NOT NULL,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)        #从这里发现成功设置主键为id
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-修改原有表配置主键

mysql> alter table bbb  modify id int primary key; #修改id为主键，修改属性时，必须加modify
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table bbb;  #查看bbb表的信息
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bbb   | CREATE TABLE `bbb` (
  `id` int(11) NOT NULL,
  `name` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `sex` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)  #发现成功修改
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-删除表的主键

mysql> alter table bbb  drop primary key;  #直接删除bbb表的主键
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table bbb;  #查看bbb表的信息
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bbb   | CREATE TABLE `bbb` (
  `id` int(11) NOT NULL,
  `name` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `sex` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL  #发现主键没有了
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-配置主键自增

mysql> alter table bbb modify id int primary key auto_increment;  #配置bbb表的id为主键并且自增，在创建新表时也是在primary key 后面跟auto_increment即可
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table bbb; #查看bbb表的信息
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bbb   | CREATE TABLE `bbb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `sex` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)  #成功增加主键
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk |  #成功配置主键自增
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from bbb;  #查看表的信息
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
+----+----------+-------+------+
3 rows in set (0.00 sec)

mysql> insert into bbb (name,sex,age) values("nimen","man","12"); #给bbb表插入数据，不写id的值，自增需要前面指定列，不能直接使用values插入
Query OK, 1 row affected (0.00 sec)

mysql> select * from bbb;  #查看表的信息，发现插入的数据的id自加了1
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
+----+----------+-------+------+
4 rows in set (0.00 sec)

-配置列不能为空

mysql> show create table bbb;  #先查看bbb表的信息
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bbb   | CREATE TABLE `bbb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `sex` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table bbb modify name char(10) not null; #修改bbb表的name项不能为空，要记得使用modify，在创建新表时配置不允许为空也是大同小异，直接在配置的项后面加not null即可，例如：create table aaa (aaa int not null);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

（2）表的注释

即可以给列增加一个注释，方便识别，不常用，只能再查看show create table 时才可以看到注释

mysql> create table ccc (id int comment "工号");
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ccc values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from ccc;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show create table ccc;
+-------+--------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------+
| ccc   | CREATE TABLE `ccc` (
  `id` int(11) DEFAULT NULL COMMENT '工号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
+-------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

二、事务概述

（1）什么是事务

• 事务就是多个SQL语句组成的，这些SQL语句会一起执行，维护数据的完整性，要么成功，要么不成功，就是说如果事务中的一条SQL语句没有执行成功，那么整个事务就会失败
• 一个事务中包含多条SQL语句，这些SQL语句之间存在着一定的关系，SQL语句的数量为n，n>0
• 不是所有的数据库引擎都支持事务，mysql的默认引擎innoDB是支持事务的
• 关系型数据库支持事务，非关系型数据库是不支持事务的

---

（2）事务的特性（ACID）

事务的特性为ACID，分别为：原子性、一致性、隔离性、持久性

• 原子性(Atomic)： 事务的所有操作要么全部执行，要么全部不执行，如果中途出现错误不会停止，而是回滚到执行事务之前的状态

例如：一个事务中有十条SQL语句，在执行事务的时候，这十条SQL语句必须全部执行成功之后，事务才会成功，反之只要有一条SQL语句失败，那么整个事务就会失败

• 一致性(Consistency)： 如果事务执行前是一致的，那么执行后也是一致的，不能破坏关系数据的完整性以及业务逻辑的一致性，事务按照预期实现

例如：用户A有1000RMB，用户B也有1000RMB，用户A向用户B转了500RMB，那么用户B会加500变成1500RMB，而用户A会减少500变成500RMB，但是两个用户RMB的总数始终都是2000RMB

• 隔离性(lsolation)： 隔离性可以防止多个事务一起执行时导致数据出现不一致

例如：用户A，用户B，用户C都有1000RMB，用户A向用户B转500RMB，用户C也向用户B转500RMB，此期间，用户A和用户B的操作是事务1，用户C和用户B的操作是事务2，两件事务是互相隔离的

• 持久性(Durability)： 事务执行成功后会对数据库进行永久性修改

例如：用户A有1000RMB，用户B也有1000RMB，用户A向用户B转了500RMB，那么用户B会加500变成1500RMB，而用户A会减少500变成500RMB，这个操作会在数据库里永久修改

---

（3）如果事务并发不进行事务隔离会怎么样

执行事务时，如果不进行事务隔离可以会造成三种错误操作，分别是：脏读、不可重复读、幻读

• 脏读： 事务1读到未提交的事务2修改的数据，如果此时事务2中途执行失败回滚，那么此时事务1读取的数据就是脏数据

例如：事务1对RMB进行修改，此时事务2已经读取到了事务1修改后的结果，但是此时事务1进行了回滚，又回到了修改之前的状态，但是事务2已经读取到了修改后的数据，此时，事务2读取到的就是脏数据

• 不可重复读： 同一个事务中，对同一份数据读取的结果不一致

例如：事务1在事务2执行之前已经读取了数据，在事务2执行之后又读取了一次数据，此时事务1是读取了两次数据，多读取了一次，造成数据结果的不一致

• 幻读： 同一个事务中，同一个查询但是多次返回的结果不一样

例如：事务2查询表的记录数量，查询完之后，事务1向表中插入一条记录，接着事务2又查询了一次表的数量，发现记录数量不一样

区别：

• 脏读和不可重复读： 脏读是事务读取了还未提交事务的更新数据，不可重复读是同一个事务中，进行多次读取的数据不一样
• 不可重复读和幻读的区别： 都是在同一个事务中，前者是进行多次读取的数据不一样，而后者是几次读取的数据整体不一致

---

（4）事务的隔离级别

隔离级别	作用
Serializable（串行化）	避免脏读、不可重复读、幻读
Repeatable（可重复度）	避免脏读、不可重复读
Read committed（读已提交）	避免脏读
Read uncommitted（读未提交）	NONE（没用）

Mysql支持这四种隔离级别，默认为可重复读

（5）Mysql数据库管理事务

管理事务的三个命令，分别是：begin、commit、rollback

• begin： 开始事务，后面有多条数据库操作语句开始执行
• commit： 开始提交一个事务，对应前面的begin操作，讲事务处理的结果保存到数据文件中
• rollback： 开始回滚一个事务，在begin和commit之间，讲事务中的全部语句撤回，恢复到执行begin之前的数据状态，要注意rollback需要在执行commit之前执行才有效
• set autocommit = 0 \ 1 ： 在数据库中执行，临时禁用或开启自动提交，自动提交为退出 mysql时就算没有输入commit也会提交或者是执行下一条DML （数据操纵语言，即insert、delete等） 语句时会自动提交，1为自动提交，0为禁止自动提交

---

（6）在Mysql中管理事务

-确保表的存储引擎为InnoDB，如果不是的话，使用alter修改即可

[root@rzy ~]# mysql -u root -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.10 sec)

mysql> use aaa;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables ;
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
| bbb           |
| ccc           |
+---------------+
3 rows in set (0.01 sec)

mysql> show create table bbb;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bbb   | CREATE TABLE `bbb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `sex` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk |  #查看表的存储引擎为innoDB
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-使用begin

mysql> select * from bbb;  #先查看bbb表的全部信息
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
+----+----------+-------+------+
4 rows in set (0.00 sec)

mysql> begin;  #开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bbb values(5,"hehe","man",80); #插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from bbb;  #再次查看，已经成功修改，但是这个事务还没有提交
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)

mysql> commit;  #提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bbb;  #再次查看bbb表的所有信息，发现成功插入数据，
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)
mysql> exit  #退出
Bye
[root@rzy ~]# mysql -u root -p123123  #重新进入mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use aaa;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from bbb;  #查看bbb表，发现刚刚插入的数据还在，说明刚才那个事务已经成功执行
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)

mysql> begin ;  #再次开启一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bbb values(6,"heihie","woman",99);  #插入数据
Query OK, 1 row affected (0.00 sec)

mysql> exit  #这次不进行提交就退出
Bye
[root@rzy ~]# mysql -u root -p123123  #重新退出
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from aaa.bbb;  #再次查看表，发现没有进行手动提交的数据没有成功插入表中
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)

-使用rollback回滚事务

mysql> use aaa;  #进入aaa库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from bbb;  #查看bbb表的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)

mysql> begin ;  #开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bbb values(6,"zzz","man",56);  #插入两行数据
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbb values(7,"cccc","woman",34);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bbb;  #再次查看表的数据，现在可以看到数据已经插入了
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | zzz      | man   |   56 |
|  7 | cccc     | woman |   34 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> rollback;  #此时进行回滚，将rollback命令之前，begin之后的操作全部撤销
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bbb; #此时再次查看，发现刚才的操作都没有了
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)

-配置是否自动提交事务

mysql> show variables like '%autocommit%'; #查看是否开启自动提交，ON为开启，OFF为关闭
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set autocommit = 0; #临时设置禁止自动提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit'; #再次查看确认关闭
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> begin ; #开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bbb values(6,"aaa","man",23); #插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from bbb; #查看bbb表的数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | aaa      | man   |   23 |
+----+----------+-------+------+
6 rows in set (0.00 sec)
 
mysql> exit  #不进行提交就退出
Bye
[root@rzy ~]# mysql -u root -p123123  #重新进入数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.12 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from aaa.bbb; #查看表，没有刚才插入的数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)

三、SQL高级查询

（1）别名

mysql> use aaa;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
| bbb           |
| ccc           |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from bbb; #先查看原来的正常表
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select id  as "员工号",name as "姓名",sex as "性别",age as "年龄" from bbb; #使用别名查看
+-----------+----------+--------+--------+
| 员工号    | 姓名     | 性别   | 年龄   |
+-----------+----------+--------+--------+
|         1 | zhangsan | man    |     18 |
|         2 | wangwu   | woman  |     20 |
|         3 | liliu    | woman  |     22 |
|         4 | nimen    | man    |     12 |
|         5 | hehe     | man    |     80 |
|         6 | wukong   | man    |   1230 |
|         7 | wukong   | man    |   1230 |
+-----------+----------+--------+--------+
7 rows in set (0.00 sec)

（2）去重

mysql> select * from bbb; #先查看bbb表的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select sex from bbb; #只查看sex这一列
+-------+
| sex   |
+-------+
| man   |
| woman |
| woman |
| man   |
| man   |
| man   |
| man   |
+-------+
7 rows in set (0.00 sec)

mysql> select distinct sex from bbb; #去掉重复的数据
+-------+
| sex   |
+-------+
| man   |
| woman |
+-------+
2 rows in set (0.00 sec)

（3）where

mysql> select * from bbb; #查看bbb表的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb where age > 20; #查看表中age项大于20的所有数据
+----+--------+-------+------+
| id | name   | sex   | age  |
+----+--------+-------+------+
|  3 | liliu  | woman |   22 |
|  5 | hehe   | man   |   80 |
|  6 | wukong | man   | 1230 |
|  7 | wukong | man   | 1230 |
+----+--------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from bbb where age = 20; #查看bbb表中age项等于20的所有数据
+----+--------+-------+------+
| id | name   | sex   | age  |
+----+--------+-------+------+
|  2 | wangwu | woman |   20 |
+----+--------+-------+------+
1 row in set (0.00 sec)

mysql> select name from bbb where id = 3; #查看bbb中id项等于3的name项的数据
+-------+
| name  |
+-------+
| liliu |
+-------+
1 row in set (0.00 sec)

mysql> select name,age from bbb; #只查看bbb表中的name和age项的数据
+----------+------+
| name     | age  |
+----------+------+
| zhangsan |   18 |
| wangwu   |   20 |
| liliu    |   22 |
| nimen    |   12 |
| hehe     |   80 |
| wukong   | 1230 |
| wukong   | 1230 |
+----------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb where name = "liliu"; #查看bbb表中name项等于liliu的所有数据
+----+-------+-------+------+
| id | name  | sex   | age  |
+----+-------+-------+------+
|  3 | liliu | woman |   22 |
+----+-------+-------+------+
1 row in set (0.00 sec)

mysql> select * from bbb where sex != "man"; #查看bbb表中sex项不等于man的数据
+----+--------+-------+------+
| id | name   | sex   | age  |
+----+--------+-------+------+
|  2 | wangwu | woman |   20 |
|  3 | liliu  | woman |   22 |
+----+--------+-------+------+
2 rows in set (0.00 sec)

（4）and和or

mysql> select * from bbb; #先查看bbb表中的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb where sex="man" and age=18; #使用and表示必须满足两个条件
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | man  |   18 |
+----+----------+------+------+
1 row in set (0.00 sec)

mysql> select * from bbb where sex="man" or age=18; #使用or表示只要满足一个条件即可
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | man  |   18 |
|  4 | nimen    | man  |   12 |
|  5 | hehe     | man  |   80 |
|  6 | wukong   | man  | 1230 |
|  7 | wukong   | man  | 1230 |
+----+----------+------+------+
5 rows in set (0.00 sec)

（5）in和between and

mysql> select * from bbb; #先查看bbb表的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb where age in (18,20); #in表示只筛选()里的指定数据，18和20就是只筛选age等于18和20的数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
+----+----------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from bbb where age between 12 and 22; #between and表示什么到什么，between 12 and 22 表示筛选12到22的数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
+----+----------+-------+------+
4 rows in set (0.00 sec)

（6）SQL的like操作符

mysql> select * from bbb; #先查看bbb表的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb where name like 'z%'; #筛选bbb表中name项以z开头的数据
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | man  |   18 |
+----+----------+------+------+
1 row in set (0.00 sec)

mysql> select * from bbb where name like '%n'; #筛选bbb表中name项以n为结尾的数据
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | man  |   18 |
|  4 | nimen    | man  |   12 |
+----+----------+------+------+
2 rows in set (0.00 sec)

mysql> select * from bbb where name like 'z%%n'; #筛选bbb表中name项以z开头n结尾的数据
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | man  |   18 |
+----+----------+------+------+
1 row in set (0.00 sec)

mysql> select * from bbb where name like '%e%'; #筛选bbb表中name项包含e的数据
+----+-------+------+------+
| id | name  | sex  | age  |
+----+-------+------+------+
|  4 | nimen | man  |   12 |
|  5 | hehe  | man  |   80 |
+----+-------+------+------+
2 rows in set (0.00 sec)

mysql> select * from bbb where name like '_e__'; #_表示任意字符，这里表示筛选bbb表中name项的四个单词其中第二个是e的数据
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  5 | hehe | man  |   80 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from bbb where name like '___e_'; #和上面相同，这里筛选的是5个单词，第四个为e的数据
+----+-------+------+------+
| id | name  | sex  | age  |
+----+-------+------+------+
|  4 | nimen | man  |   12 |
+----+-------+------+------+
1 row in set (0.00 sec)

mysql> select * from bbb where name like 'n%__e_'; #这里配合了%,筛选bbb表中name项以n开头的第四个单词是e的数据
+----+-------+------+------+
| id | name  | sex  | age  |
+----+-------+------+------+
|  4 | nimen | man  |   12 |
+----+-------+------+------+
1 row in set (0.00 sec)

mysql> select * from bbb where name like '_%'; #使用_%查看和正常查看所有数据是相同的，_%表示所有
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

（7）SQL的order by语句

mysql> select * from bbb ;  #先查看bbb表的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb order by age; #以升序的方式排序bbb表中age项的数据，默认使用order by就是升序
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  4 | nimen    | man   |   12 |
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb order by age desc; #以降序的方式排序bbb表中age的数据，加desc为降序
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
|  5 | hehe     | man   |   80 |
|  3 | liliu    | woman |   22 |
|  2 | wangwu   | woman |   20 |
|  1 | zhangsan | man   |   18 |
|  4 | nimen    | man   |   12 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb where sex = "man" order by age desc; #筛选bbb表中sex项等于man的数据并且age项以降序的方式排序
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  6 | wukong   | man  | 1230 |
|  7 | wukong   | man  | 1230 |
|  5 | hehe     | man  |   80 |
|  1 | zhangsan | man  |   18 |
|  4 | nimen    | man  |   12 |
+----+----------+------+------+
5 rows in set (0.00 sec)

mysql> select * from bbb where sex = "man" order by age; #筛选bbb表中sex项等于man的数据并且age项以升序的方式排序
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  4 | nimen    | man  |   12 |
|  1 | zhangsan | man  |   18 |
|  5 | hehe     | man  |   80 |
|  6 | wukong   | man  | 1230 |
|  7 | wukong   | man  | 1230 |
+----+----------+------+------+
5 rows in set (0.00 sec)

（8）SQL的limit语句

mysql> select * from bbb;  #查看bbb表的所有数据
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
|  6 | wukong   | man   | 1230 |
|  7 | wukong   | man   | 1230 |
+----+----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from bbb limit 5; #查看bbb表中的前5行
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)
 
mysql> select * from bbb limit 0,5; #查看bbb表中的从0行开始到5行显示结束
+----+----------+-------+------+
| id | name     | sex   | age  |
+----+----------+-------+------+
|  1 | zhangsan | man   |   18 |
|  2 | wangwu   | woman |   20 |
|  3 | liliu    | woman |   22 |
|  4 | nimen    | man   |   12 |
|  5 | hehe     | man   |   80 |
+----+----------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from bbb limit 4,6;  #查看bbb表中的从4行开始到5行显示结束
+----+--------+------+------+
| id | name   | sex  | age  |
+----+--------+------+------+
|  5 | hehe   | man  |   80 |
|  6 | wukong | man  | 1230 |
|  7 | wukong | man  | 1230 |
+----+--------+------+------+
3 rows in set (0.00 sec)

mysql> select * from bbb limit 5,6;  #查看bbb表中的从5行开始到6行显示结束
+----+--------+------+------+
| id | name   | sex  | age  |
+----+--------+------+------+
|  6 | wukong | man  | 1230 |
|  7 | wukong | man  | 1230 |
+----+--------+------+------+
2 rows in set (0.00 sec)

an | 12 |
| 5 | hehe | man | 80 |
±—±---------±------±-----+
5 rows in set (0.00 sec)

mysql> select * from bbb limit 0,5; #查看bbb表中的从0行开始到5行显示结束
±—±---------±------±-----+
| id | name | sex | age |
±—±---------±------±-----+
| 1 | zhangsan | man | 18 |
| 2 | wangwu | woman | 20 |
| 3 | liliu | woman | 22 |
| 4 | nimen | man | 12 |
| 5 | hehe | man | 80 |
±—±---------±------±-----+
5 rows in set (0.00 sec)

mysql> select * from bbb limit 4,6; #查看bbb表中的从4行开始到5行显示结束
±—±-------±-----±-----+
| id | name | sex | age |
±—±-------±-----±-----+
| 5 | hehe | man | 80 |
| 6 | wukong | man | 1230 |
| 7 | wukong | man | 1230 |
±—±-------±-----±-----+
3 rows in set (0.00 sec)

mysql> select * from bbb limit 5,6; #查看bbb表中的从5行开始到6行显示结束
±—±-------±-----±-----+
| id | name | sex | age |
±—±-------±-----±-----+
| 6 | wukong | man | 1230 |
| 7 | wukong | man | 1230 |
±—±-------±-----±-----+
2 rows in set (0.00 sec)