-使用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)
