SQL小结
一.管理数据库
- 数据库登陆，这里使用mysql
mysql -u root -p # 输入密码，默认mysql, -h 服务器地址
# 退出mysql
exit
1.数据库操作
-
创建数据库
-- 创建student数据库 create database student charset=utf8; -- 查看创建student数据库的SQL语句 show create database student; -
查看所有的数据库
show databases; -
删除数据库
drop database student; -
选择数据库进行操作
use student; -
查看当前所在的数据库
select database(); -- 如果当前没有在任何数据库，执行指令之后返回null
2.数据表操作
-
查看当前数据库的所有的数据表
show tables; -
创建数据表
create table test1(id int primary key auto_increment, name varchar(10), age int); -
查看表结构
desc test1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.04 sec) -
查看创建数据表的SQL语句
show create table test1; +-------+-----------+ | Table | Create Table | +-------+------------+ | test1 | CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------+ 1 row in set (0.00 sec) -
修改表
-- 给test1这个表增加一个字段gender alter table test1 add column gender enum('man', 'femail') not null; -
修改表字段
-- 这里把test1表中字段gender，修改为sex 类型varchar(10) alter table test1 change column gender sex varchar(10) not null; -
删除表字段
alter table test1 drop column sex; -
删除表
drop table test1;
二.查询数据
1.基本查询
-
查询students表中的所有数据
select * from students; -
select 语句不知能查询，还可以计算，但计算不是select的强项
select 100 + 100; +-----------+ | 100 + 100 | +-----------+ | 200 | +-----------+ 1 row in set (0.01 sec) -
select 语句还可以用来判断当前到数据库的连接是否有效
select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
2.条件查询
常用的条件表达式
- = 判断相等
- > 判断大于
- >= 判断大于等于
- < 判断小于
- <= 判断小于等于
- <> 判断不相等
- LIKE 判断相似
-
查询分数在90分及以上的所有学生
select * from students where score >= 90; -
条件查询之 and，查询左右两边条件都满足的结果
select * from students where score >= 90 and gender = 'M'; -
条件查询之 or，查询左右两边条件满足任一条件的结果
select * from students where score >= 90 or gender = 'M'; -
条件查询之 not，查询不满足指定条件的结果
select * from students where not gender = 'M'; -- 此处的 not gender = 'M' 等价于 gender <> 'M'，也可以使用如下SQL进行查询 select * from students where gender <> 'M'; -
条件查询之优先级
如果不加括号，条件运算按照
NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。
3.投影查询
什么是投影查询：
查询指定字段的记录的查询，称之为投影查询。
-
查询students表中name && score 字段的所有记录。
select name, score from students; -
给字段使用别名
select name, score 分数 from students; +--------+--------+ | name | 分数 | +--------+--------+ | 小明 | 90 | | 小红 | 95 | +--------+--------+ -
投影查询同样可以使用where子句进行条件查询
select name, score 分数 from students where score <= 80; +--------+--------+ | name | 分数 | +--------+--------+ | 小米 | 73 | | 小兵 | 55 | +--------+--------+
4.查询结果排序
给查询结果排序，可以使用 order by 子句。默认升序（asc 从低到高），desc 倒序（从高到低）
-
查询结果按照学生成绩升序排列
select * from students order by score; -- 默认升序排序，asc可省略。完整的SQL如下 select * from students order by score asc; -
查询结果按照学生成绩降序排列
select * from students order by score desc; -
如果score字段有相同的数据，需要进一步排序，可以继续添加字段名。
select * from students order by score desc, gender asc; -
如果有where子句，order by子句需要放到 where 子句后面
select * from students where id <= 8 order by score desc, gender asc;
5.分页查询
分页查询：其实就是从查询结果中截取 m~n 条记录。
分页查询可以通过 limit m offset n 子句来实现，m表示从查询结果集中截取的记录的数量，n表示从哪个索引开始截取，查询结果集中的索引是从0开始的。
注意： offset 是可选的，如果只写 limit 3，等同于 limit 3 offset 0
注意： 在mysql中，limit 3 offset 2 可以简写为： limit 3, 2
-
从查询结果中获取第一页记录，一页显示2条记录。
select * from students where id <= 8 order by score desc limit 2 offset 0; -
从查询结果中获取第二页记录，一页显示2条记录。
select * from students where id <= 8 order by score desc limit 2 offset 2; -- 也可以使用如下SQL进行查询 select * from students where id <= 8 order by score desc limit 2, 2; -
分页查询小结
- limit 总是设置为 pageSize；
- offset 计算公式：pageSize*（pageNumber-1）
-
计算总页数
可以使用聚合函数计算出总页数，示例如下：
select ceiling(count(*)/2) total_pageNum from students; +---------------+ | total_pageNum | +---------------+ | 58 | +---------------+ 1 row in set (0.00 sec)
6.聚合查询
常用的聚合函数
注意，
MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。如果聚合查询的
WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL
| 函数 | 说明 |
|---|---|
| SUM | 计算某一列的合计值，该列必须为数值类型 |
| AVG | 计算某一列的平均值，该列必须为数值类型 |
| MAX | 计算某一列的最大值 |
| MIN | 计算某一列的最小值 |
| COUNT | 计算数据表中所有记录的数量 |
-
查询students表共有多少条记录
select count(*) from students; +----------+ | count(*) | +----------+ | 98 | +----------+ 1 row in set (0.00 sec) -
给查询结果列名设置别名
使用聚合查询，查询结果仍然是一个二维表，列名为count(*)，我们可以给列名设置一个别名
select count(*) students_num from students; +--------------+ | students_num | +--------------+ | 98 | +--------------+ 1 row in set (0.00 sec) -
聚合查询使用where子句
select count(*) students_man_num from students where gender='M'; +------------------+ | students_man_num | +------------------+ | 58 | +------------------+ 1 row in set (0.00 sec) -
使用聚合函数AVG计算students表中所有男生的平均分数
select avg(score) avg_man_score from students where gender='M'; +---------------+ | avg_man_score | +---------------+ | 81.6000 | +---------------+ 1 row in set (0.05 sec)
7.分组查询
分组使用 group by 子句，分组查询通常和聚合函数配合使用。
注意： 聚合查询的列中，只能放入分组的列。例如：
select class_id, gender, avg(score) from students group by class_id, gender;
-
查询每个班级的学生的人数
select class_id, count(*) students_num from students group by class_id; +----------+--------------+ | class_id | students_num | +----------+--------------+ | 1 | 36 | | 2 | 33 | | 3 | 29 | +----------+--------------+ 3 rows in set (0.07 sec) -
查询每个班级男生和女的人数
select class_id, gender, count(*) students_num from students group by class_id, gender; +----------+--------+--------------+ | class_id | gender | students_num | +----------+--------+--------------+ | 1 | F | 21 | | 1 | M | 12 | | 2 | F | 15 | | 2 | M | 26 | | 3 | F | 25 | | 3 | M | 11 | +----------+--------+--------------+ 6 rows in set (0.04 sec) -
查询每个班级男生，女声的平均分
select class_id, gender, avg(score) from students group by class_id, gender; +----------+--------+------------+ | class_id | gender | avg(score) | +----------+--------+------------+ | 1 | F | 81.0000 | | 1 | M | 89.0000 | | 2 | F | 83.0000 | | 2 | M | 70.0000 | | 3 | F | 88.0000 | | 3 | M | 89.0000 | +----------+--------+------------+ 6 rows in set (0.00 sec)
8.多表查询
select 查询可以同时从多张表查询数据，语法：select * from students, classes;
这种一次查询两个表的数据，查询结果也是一个二维表，它是students和classes表的每一行两两拼在一起返回，结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。
这种多表查询又称为笛卡尔积查询。
注意： 使用多表查询一定小小心，一定要避免不带条件的多表查询，否则查询出来的结果集可能会非常庞大。
可以给查询的表设置别名。
多表查询示例：
SELECT s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c WHERE s.gender = 'M' AND c.id = 2;
+-----+--------+--------+-------+-----+--------+
| sid | name | gender | score | cid | cname |
+-----+--------+--------+-------+-----+--------+
| 1 | 张良 | M | 90 | 2 | 二班 |
| 3 | 王超 | M | 88 | 2 | 二班 |
| 6 | 马汉 | M | 55 | 2 | 二班 |
| 7 | 刘能 | M | 85 | 2 | 二班 |
| 9 | 赵四 | M | 89 | 2 | 二班 |
+-----+--------+--------+-------+-----+--------+
5 rows in set (0.06 sec)
9.连接查询
连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
-
内连接查询 inner join
inner join 内连接查询，选出两张表都存在的记录。
select s.id, s.name, s.class_id, c.name class_name from students s inner join classes c on s.class_id = c.id where s.id <= 3; +----+--------+----------+------------+ | id | name | class_id | class_name | +----+--------+----------+------------+ | 1 | 小明 | 1 | 一班 | | 2 | 小红 | 1 | 一班 | | 3 | 小军 | 1 | 一班 | +----+--------+----------+------------+ 3 rows in set (0.00 sec)注意：
- 先确定主表，仍然使用
FROM <表1>的语法； - 再确定需要连接的表，使用
INNER JOIN <表2>的语法； - 然后确定连接条件，使用
ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接； - 可选：加上
WHERE子句、ORDER BY等子句。
- 先确定主表，仍然使用
-
右连接查询 right outer join
right outer join 右连接查询，选出右表存在的记录。
select s.id, s.name, s.class_id, c.name class_name from students s right outer join classes c on s.class_id = c.id; +------+--------+----------+------------+ | id | name | class_id | class_name | +------+--------+----------+------------+ | 1 | 小明 | 1 | 一班 | | 2 | 小红 | 1 | 一班 | | 3 | 小军 | 1 | 一班 | | 4 | 小米 | 1 | 一班 | | 5 | 小白 | 2 | 二班 | | 6 | 小兵 | 2 | 二班 | | 7 | 小林 | 2 | 二班 | | 8 | 小新 | 3 | 三班 | | 9 | 小王 | 3 | 三班 | | 10 | 小丽 | 3 | 三班 | | NULL | NULL | NULL | 四班 | +------+--------+----------+------------+ 11 rows in set (0.00 sec) -
左连接查询 left outer join
left outer join 左连接查询，选出左表存在的记录。
select s.id, s.name, s.class_id, c.name class_name from students s left outer join classes c on s.class_id = c.id;
三.修改数据
1.插入数据，insert into
基本语法：
-- 添加单条记录
insert into <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
-- 添加多条记录
insert into <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;
示例：
insert into students (class_id, name, gender, score) VALUES (1, '金刚', 'M', 87), (1, '丽萨', 'F', 98);
2.修改数据 update
注意： 如果 where 条件没有匹配到任何记录，update 语句不会报错，也不会有任何记录被更新。
注意： update 语句可以没有where子句，这时整个表的所有记录都会被更新，所以，执行update语句时一定要谨慎。
基本语法：
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
-
修改students表中id=1的学生的成绩score
update students set score = 90 where id = 1; -
修改students表中id>=5 & id<=8的学生的成绩score
update students set score = 10 where id >= 5 and id <=8; -
修改记录时可以使用表达式，示例如下
update students set score = score + 80 where score < 15;
3.删除数据 delete
基本语法：
DELETE FROM <表名> WHERE ...;
注意： 如果 where 条件没有匹配到任何记录，delete 语句不会报错，也不会删除任何记录。
注意： delete 语句可以没有where子句，这时整个表的所有记录都会被删除，所以，执行delete语句时一定要谨慎。
-
删除id=1的记录
delete from students where id=1; -
删除多条记录
delete from students where id > 5 and id < 9;
四.数据库事务
1.什么是数据库事务？
把多条语句作为一个整体进行操作的功能，被称为数据库事务 。
在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：
-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。
这种把多条语句作为一个整体进行操作的功能，被称为数据库事务 。
数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。
2.数据库事务四大特性ACID
- A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
- C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
- I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
- D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。
3.数据库事务的使用
使用 BEGIN 开启一个事务，使用 COMMIT 提交一个事务，这种事务被称为显式事务 。
对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
开始事务并提交
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
开始事务，回滚事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
数据库事务隔离级别
对于多个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。
SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：
| Isolation Level | 脏读（Dirty Read） | 不可重复读（Non Repeatable Read） | 幻读（Phantom Read） |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | - | Yes | Yes |
| Repeatable Read | - | - | Yes |
| Serializable | - | - | - |
-
Read Uncommitted
Read Uncommitted是隔离级别最低的一种事务级别。
在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）
-
Read Committed
在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。
不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致
-
Repeatable Read
在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。
幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
-
Serializable
Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。
-
默认隔离级别
如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。
1693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
