数据库基本操作（2）

数据库常用干货 （CRUD）

0、事务（transaction）
A、事务可以保证数据恢复到初始状态

B、事务的4个特征：
1）原子性：事务中的命令可么全部成功，要么全部失败
2）隔离性：多个事务相互隔离，互不干扰
3）持久性：事务一旦提交或回滚，将永远保存
4）一致性：事务前后的状态要保持一致

C、事务语法：
begin：开启事务
commit：提交事务，数据会永久保存
rollback：回滚事件，数据回滚到begin时候的状态

1、插入记录
语法：
insert into 表名 (字段列表) values(值列表)

注意：
A、如果给记录的每一个字段都赋了值，则字段列表可以省略
B、该命令只能插入一条记录
C、如果某个字段是auto_increment，则该字段的值可给可不给，自动编号是指取出该列的最大值加1

往班级表中插入班级信息：
mysql> desc classes;
+————+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+—————-+
| class_id | int(11) | NO | PRI | NULL | auto_increment |
| class_name | varchar(20) | NO | | NULL | |
+————+————-+——+—–+———+—————-+

insert into classes(class_id, class_name) values(1, ‘韬睿班’)
可以简写成：
insert into classes values(2, ‘清华班’)
或者写成：
insert into classes(class_name) values(‘北大班’)
或者写成(前提是可以为null)：
insert into classes(class_name) values(null)

插入多条记录：
insert into 表名 (字段列表) values(值列表), (值列表), (值列表), ……

insert into students values
(2, ‘刘德华’, ‘男’, ‘1989-9-9’, ‘北京’, 1),
(3, ‘李连杰’, ‘男’, ‘1977-2-12’, ‘香港’, 2);

2、更新记录
语法：
update 表名 set 字段名1=字段值1, 字段名2=字段值2，…… where 条件

说明：
A、set子句用于指定要更新哪些字段
B、where子句用于指定要更新哪些记录
C、先执行where子句，再执行set子句
D、如果没有where子句，则会修改所有的记录

mysql> select * from classes;
+———-+—————–+
| class_id | class_name |
+———-+—————–+
| 1 | 韬睿班 |
| 2 | 清华班 |
| 3 | 北大班 |
| 10 | 人民大学班 |
| 11 | 武大班 |
+———-+—————–+

比如：将“清华班”变成“清华大学班”
update classes set class_name=’清华大学班’ where class_id=2;

关于条件：
逻辑与：and -> java &&
逻辑或：or -> java ||
逻辑非：not -> java !

将学号为1或3的学生的性别更改为女
update students set stu_sex=’女’ where stu_id = 1 or stu_id = 3;

将所有的女生的籍贯改成“美国”、生日改成今天
update students set stu_from=’美国’,stu_birth=now() where stu_sex=’女’;

将所有学生的籍贯变成“株洲”
update students set stu_from=’株洲’

3、删除记录
语法：
delete from 表 where 条件

说明：
A、满足条件的记录都会被删除
B、如果没有where子句，则会删除所有的记录

删除所有的学生：
select * from students;

删除所有的女生：
delete from students where stu_sex=’女’;

4、查询记录
语法：
select 字段列表 from 表名 where 条件

说明：
A、where子句决定有多少行
B、select子名决定有多少列

查找所有学生的学号和姓名：
select stu_id, stu_name from students;

查找所有学生的所有字段
select * from students;

5、查询的种类
A、条件查询
常用的运算符——
比较：>、 >=、 <、 <=、 =、 !=、 <>
逻辑：and、or、not
范围：in、between…and
in：相当于or 比如：id=1 or id=2 or id=3 -> id in(1, 2, 3)
select * from students where stu_id=1 or stu_id=2;
select * from students where stu_id in(1, 2);
between..and：在…之间 比如：id >= 1 and id <=3 -> between 1 and 3
select * from students where stu_id >= 1 and stu_id<=3;
select * from students where stu_id between 1 and 3;
模糊：like 像
占位符：_ 表示一个字符，% 表示一个字符串
select * from students where stu_name like ‘张%’;查找所有姓张的学生
select * from students where stu_name like ‘%张%’;查找姓名中带张的学生
select * from students where stu_name like ‘%张’;查找姓名中以张结尾的学生

B、投影查询
select 字段1,字段2，…… from 表示
select stu_id, stu_name from students;

C、分页查询
limit：限制
语法：limit 起始索引n, 记录条数m
从第n条记录开始取出m条，n从0开始
mysql> select * from classes;
+———-+—————–+
| class_id | class_name |
+———-+—————–+
| 1 | 韬睿班 |
| 2 | 清华大学班 |
| 3 | 北大班 |
| 10 | 人民大学班 |
| 11 | 武大班 |
+———-+—————–+
5 rows in set (0.00 sec)

mysql> select * from classes limit 0,2;
+———-+—————–+
| class_id | class_name |
+———-+—————–+
| 1 | 韬睿班 |
| 2 | 清华大学班 |
+———-+—————–+
2 rows in set (0.00 sec)

mysql> select * from classes limit 1,3;
+———-+—————–+
| class_id | class_name |
+———-+—————–+
| 2 | 清华大学班 |
| 3 | 北大班 |
| 10 | 人民大学班 |
+———-+—————–+
3 rows in set (0.00 sec)

分页查询：当前页为curpage（从1开始），页大小为pagesize
limit (curpage - 1) * pagesize, pagesize

D、分组统计查询
统计函数：
sum()：求和
select sum(class_id) from classes;将class_id的字段值相加
max()：求最大值
select max(class_id) from classes;找出class_id字段中的最大值
min()：求最小值
select min(class_id) from classes;找出class_id字段中的最小值
avg()：求平均值
select min(class_id) from classes;找出class_id字段中的平均值
count()：求记录条件，括号中如果是字段名，刚会忽略null值，如果是*，则会全部计算
select count(class_id) from classes;找出查找出来的记录条件，会忽略class_id为
null的记录

分组子名：
group by 字段名

分组统计的语法：
select 字段, 统计函数 from 表 where 条件 group by 分组 having 分组条件
分组之前的条件查询使用where子句，分组之后的条件查询使用having子句

统计出男女各多少人
翻译：按照性别分组，统计出每组有多少
SQL：select stu_sex, count(*) from students group by stu_sex;

统计出不同年龄的人数分别是多少？
select DATE_FORMAT(now(), ‘%Y’) - DATE_FORMAT(stu_birth, ‘%Y’), count(*) from students group by DATE_FORMAT(now(), ‘%Y’) - DATE_FORMAT(stu_birth, ‘%Y’);

统计出人数大于等于2的性别
select stu_sex, count() from students group by stu_sex having count() >= 2;

E、子查询
一个查询可以作为表达式使用。

找出id大于平均值的班级记录
第一步：找出id的平均值
X <- select avg(class_id) from classes;
第二步：找出大于平均值的记录
select * from classes where class_id > X;

完整的子查询：
select * from classes where class_id > (select avg(class_id) from classes);

F、连接查询
将多个表的数据连接再进行查询。

分类：
内连接（inner join）：
t1表
———————-
f1 f2
———————-
1 1
2 2
3 3

 t2表 ---------------------- f3          f4 ---------------------- 2           2 3           3 3           5 4           4 内连接（公共字段t1.f1 == t2.f3） ---------------------------------------- f1          f2          f3          f4 ---------------------------------------- 2           2            2           2 3           3            3           5 3           3            3           3 内连接的语法： select 表名.字段名 from 表1 inner join 表2 on 表1.字段 = 表2.字段

mysql> select s.stu_id,s.stu_name,s.stu_sex, c.class_name from students s inner
join classes c on c.class_id=s.classes_id;
+——–+———–+———+—————–+
| stu_id | stu_name | stu_sex | class_name |
+——–+———–+———+—————–+
| 1 | 张震岳 | 女 | 韬睿班 |
| 2 | 刘德华 | 男 | 韬睿班 |
| 3 | 李连杰 | 女 | 清华大学班 |
+——–+———–+———+—————–+
3 rows in set (0.00 sec)

mysql> select s.stu_id 学号,s.stu_name 姓名,s.stu_sex 性别, c.class_name 班级 fr
om students s inner join classes c on c.class_id=s.classes_id;
+——–+———–+——–+—————–+
| 学号 | 姓名 | 性别 | 班级 |
+——–+———–+——–+—————–+
| 1 | 张震岳 | 女 | 韬睿班 |
| 2 | 刘德华 | 男 | 韬睿班 |
| 3 | 李连杰 | 女 | 清华大学班 |
+——–+———–+——–+—————–+
3 rows in set (0.00 sec)

 左外连接（left outer join）： 先执行内连接，再将左表未显示的记录都显示出来 select * from t1 left outer join t2 on t1.f1=t2.f3;

mysql> select * from t1 left outer join t2 on t1.f1=t2.f3;
+——+——+——+——+
| f1 | f2 | f3 | f4 |
+——+——+——+——+
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 |
| 3 | 3 | 3 | 5 |
| 1 | 1 | NULL | NULL |
+——+——+——+——+

 右外连接（right outer join）： 先执行内连接，再将右表未显示的记录都显示出来

mysql> select * from t1 right outer join t2 on t1.f1=t2.f3;
+——+——+——+——+
| f1 | f2 | f3 | f4 |
+——+——+——+——+
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 |
| 3 | 3 | 3 | 5 |
| NULL | NULL | 4 | 4 |
+——+——+——+——+

G、排序查询
排序的类型：升序asc，降序desc
语法：
order by 字段 asc|desc
默认为升序

按员工的入职日期排序
select * from employee order by hire_date asc

G、联合查询
将两个表的记录做纵向连接
union：去掉重复元素
union all：不会去掉重复元素

语法：
select * from 表1 union select * from 表2

说明：
A、两个表的字段数要相同
B、相同位置的字段的数据类型要兼容

t1

f1 f2
1 1
2 2

t2

f3 f4
3 3
4 4

联合查询：select * from t1 union select * from t2;

f1 f2

1 1
2 2
3 3
4 4