SQL (结构化 Structured 查询 Query 语言 Language)
分类
DDL(数据定义语言) 表的定义、用户的定义… create drop alter
DML(数据操控语言) 对数据的增加、修改、删除、查询 insert update delete select
DCL(数据控制语言) grant(授权) revoke(回收)
DML
insert into 表 (列...) values (值...);
insert into 表 (列...) values (值...), (值...)...
insert into 表1 (列...) select 列... from 表2
update 表 set 列=新值, ...
update 表 set 列=新值, ... where 条件
delete from 表;
delete from 表 where 条件
select 列... from 表
inner|left join 表 on 连接条件
where 条件
group by 列... having 条件
order by 列 asc|desc
limit n, m
子查询 (sub query)
求最高工资的员工信息
select max(sal),e.* from emp e; – 会报错
select max(sal) from emp e;
– 最大值 一行记录 , 但名字有两个
情况1:把子查询当做一个值
分解问题:
select max(sal) from emp; --> 第一步:5000 把它看做一个值
select * from emp where sal = 5000; --> 第二步:写主查询语句
select * from emp where sal = (select max(sal) from emp); --> 第三步:替换
情况2:把子查询当做一张表
每个部门的最高工资的员工
第一步:按部门分组
(select max(sal) msal, deptno from emp group by deptno) a
// 生成了临时表 a, 其中有两列: msal, deptno
第二步: 把上面的查询结果看做一张临时表,它可以与其他表之间做连接操作
select * from emp b inner join a on b.deptno=a.deptno and b.sal = a.msal;
第三步: 把子查询代入
select * from emp b inner join
(select max(sal) msal, deptno from emp group by deptno) a
on b.deptno=a.deptno and b.sal = a.msal;
case when
可以配合select工作, 把一列的取值根据不同的条件进行翻译
类似于 java 中的if else if
语法:
case
when 条件1 then 结果1
when 条件2 then 结果2
...
else 结果n
end
2000以下显示低工资,2000~3000显示中等,3000以上显示高工资
select empno, ename, sal,
case
when sal <= 2000 then '低工资'
when sal >2000 and sal <=3000 then '中等工资'
else '高工资'
end 工资级别 from emp;
dcl (了解)
grant 授权
revoke 回收权限
创建用户
create user 用户名 identified by '密码';
授权语法:
grant 权限 to 用户名;
例如:把查询test库中所有表的查询权限授权给user1
grant select on test.* to user1;
回收权限
revoke 权限 from 用户名;
例如:回收之前分配的权限:
revoke select on test.* from user1;
事务和锁
事务
transaction 事务 : 把多条sql语句视为一个整体执行,这些sql 要么都成功, 其中有一个失败了,之前的操作也需要撤销
两个人同时操作一张表的同一条数据
中国银行账户表 account
id name balance(余额)
1 张三 2000
建设银行账户
id name balance(余额)
2 张三 3000
张三转账
try {
begin;
update account set balance=balance+1000 where id = 2;
// 出现异常
update account set balance=balance-1000 where id = 1;
commit;
} catch( Exception e ) {
rollback;
}
mysql的事务控制
默认情况下,是一条语句一个事务
要多条语句一个事务,需要通过 begin
commit
rollback
来控制事务
begin; // 表示事务的起点 等价方式: start transaction
sql1
sql2
…
commit(提交,表示结果都将生效) 和 rollback(回滚,用来撤销事务内的更改) 表示事务的终点
TCL (事务控制语言)
锁
客户1 客户2
begin; begin;
update emp set sal = 4000 where empno = 7369;
update emp set ename = 'abc' where empno = 7369; // 被阻塞
直到超时为止
InnoDB 行级锁, 只要两个客户端更新的是不同的行,互不干扰
MyISAM 表锁,是锁住整个表
增删改(insert update delete) 都会在行上加排他锁(X锁)
查询可以加共享锁 (S锁)表示可以同时查询,但其他人不能增删改(insert update delete)
select * from 表 lock in share mode; 添加共享锁-别人可以再加共享锁,但不能再加排他锁
查询时加排他锁
select * from 表 for update; 添加排他锁, -别人不能再加共享锁和排他锁
mysql对查询有特别的优化:不用锁也能实现并发访问: 多版本并发访问(MVCC)
查询
1 张三 8000 旧版本
修改
1 张三 7000
select * from 表 就是利用的多版本并发查询, 好处:并发性高!
java.util.concurrent.CopyOnWriteArrayList 线程安全的ArrayList: 修改时会产生一个集合的副本,修改都是在副本上进行,查询查的是旧集合的内容,所以查询和修改可以并行执行,等修改结束会用副本替换旧的内容
线程安全的List: Vector 全部方法加锁
事务隔离性
有不同的隔离级别,
隔离级别越低,并发性越好,但数据的一致性差
隔离级别越高,并发性差,但数据的一致性高
由低到高四种:
读未提交 < 读提交 < 可重复读(mysql默认) < 序列化读
错误的级别由高到低
脏读 , 不可重复读, 幻读
脏读(读未提交)
7369的工资1000
事务1 事务2
begin begin
修改7369的工资为8000
select * sal from emp where empno=7369;// 8000
rollback
将隔离级别提高到读提交
,可以避免脏读
不可重复读
一边查询,另一边做update操作
一个事务内多次查询结果不一致
7369的工资1000
事务1 事务2
begin begin
select * sal from emp where empno=7369; // 1000
修改7369的工资为8000
commit;
select * sal from emp where empno=7369; // 8000
要避免脏读、不可重复读:将隔离级别提高到可重复读
隔离级别
幻读
一边查询,另一边做insert操作
事务1 事务2
begin begin
查询到10,20,30,40条记录
insert 50号部门
commit
insert 50号部门 报错,主键冲突
要避免脏读、不可重复读、幻读:将隔离级别提高到序列化读
所谓的序列化读
就是把多版本并发退化到锁的并发控制:select语句上会被偷偷加上共享锁
事务四特性 (ACID)
原子性 A 多个sql要作为一个整体运行,不可分割
一致性 C 一个事务内结果应当一致
隔离性 I
持久性 D 事务一旦提交,事务内的修改就应当永久生效
其他sql总结
查看有哪些库: show databases;
查看库的创建语句: show create database 库名;
使用库: use 库名;
查看有哪些表: show tables;
查看表结构: desc 表名;
查看建表语句: show create table 表名;
\G 可以取代; 效果是把表行转列
查看系统变量的值: select @@系统变量名;
select @@transaction_isolation; // 查看事务隔离级别
select @@port; // 查看服务器端口号
select @@character_set_server; // 查看默认字符集
select @@secure_file_priv;
…
show variables like ‘character%’;
作业解答
if not exists 指表不存在时才创建, 如果已经存在,不执行任何操作
create table if not exists student(
sid int primary key auto_increment,
sname varchar(20) not null unique,
birthday date,
sex char(1)
) auto_increment=1001;
auto_increment=起始值
语法:
create table [if not exists] 表名 (
列名 数据类型 约束,
....
);
约束类型:
- not null 表示此列取值不能为空
- unique 表示此列的取值是唯一的 (一张表可以有多个唯一约束)
- primary key 同时起到了约束的效果, 唯一且非空(一张表只能定义一次)
- mysql 中没有 check约束
- foreign key 外键约束, 某一列的取值来自于另一张表(列必须是主键或唯一的)
语法:
[constraint 约束名] foreign key(本表列名) references 主表(列名)
create table course(
cid int primary key,
cname varchar(20) not null,
tid int,
foreign key (tid) references teacher(tid)
);
// 本表中tid这一列的取值,要引用 teacher中的tid列
1 java 3
2 mysql 3
3 web 2
外键总是建在多的一方
create table sc (
sid int ,
cid int,
score int,
foreign key (sid) references student(sid),
foreign key (cid) references course(cid),
primary key (sid, cid)
);
组合主键: primary key(列1, 列2 …)
1001 1 90 插入
1001 2 70
1002 2
1001 1 80 ? 错误
一.
查询姓“李”的老师的个数
select count(*) from teacher where tname like '李%';
查询男女生人数个数
select sex,count(*) from student group by sex;
查询同名同姓学生名单,并统计同名人数
select sname, count(*) from student group by sname having(count(*)>1);
1981年出生的学生名单
select * from student where year(birthday)=1981;
查询平均成绩大于60分的同学的学号和平均成绩
select sid, avg(score) from sc group by sid having(avg(score)>60);
求选了课程的学生人数
distinct 列名 去除这一列的重复值
count(distinct 列名) 去除这一列的重复值之后再统计个数
select count(distinct sid) from sc;
查询至少选修两门课程的学生学号
select sid,count(*) from sc group by sid having(count(*)>=2);
查询各科成绩最高和最低的分。以如下形式显示:课程ID,最高分,最低分
select cid 课程ID, max(score) 最高分, min(score) 最低分 from sc group by cid;
统计每门课程的学生选修人数。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select cid, count(*) from sc group by cid order by count(*) desc, cid asc ;
二.
打印入职时间超过38年的员工信息
select * from emp where year(hiredate) <= 2018-38; 只考虑了年份
select * from emp where hiredate <= date_sub(now(), interval 38 year );
``
把hiredate列看做是员工的生日,求本月过生日的员工
把hiredate列看做是员工的生日,求下月过生日的员工
求1980年下半年入职的员工
请用两种的方式查询所有名字长度为4的员工的员工编号,姓名
```sql
char_length(ename) = 4; 推荐
ename like '____';
显示各种职位的最低工资
求1980年各个月入职的的员工个数
select month(hiredate), count(*) from emp where year(hiredate)=1981 group by month(hiredate);
查询每个部门的最高工资
查询每个部门,每种职位的最高工资
select ... from emp group by deptno, job;
查询各部门的总工资和平均工资
查询10号部门,20号部门的平均工资(尝试用多种写法)
select deptno, avg(sal) from emp where deptno in(10,20) group by deptno; 推荐
select deptno, avg(sal) from emp group by deptno having deptno in(10,20);
查询平均工资高于2000元的部门编号和平均工资
统计公司里经理的人数
select count(*) from emp where job='MANAGER';
查询工资最高的3名员工信息
select * from emp order by sal desc limit 3;
查询工资由高到低第6到第10的员工信息
select * from emp order by sal desc limit 5, 5;
三.
查询李四学习的课程,考试分数,课程的授课老师
select b.cname, c.score, d.tname from
student a inner join sc c on(a.sid=c.sid)
inner join course b on (c.cid=b.cid)
inner join teacher d on (b.tid=d.tid)
where a.sname='李四';
查询王五有哪些课程没选,显示这些课程名称
(select cid from student a inner join sc b on a.sid=b.sid
where a.sname=‘王五’) d;
select * from course where cid not in(1,2);
代入:
select * from course where cid not in(select cid from student a inner join sc b on a.sid=b.sid
where a.sname=‘王五’);
select * from course c left join (select cid from student a inner join sc b on a.sid=b.sid
where a.sname=‘王五’) d on c.cid = d.cid where d.cid is null;
查询所有同学的学号、姓名、选课数、总成绩
查询所有课程成绩都小于等于60分的同学的学号、姓名;
查询没有学全所有课的同学的学号、姓名;
查询每门课程选修人数,格式为课程名称,人数;
查询出只选修了一门课程的全部学生的学号和姓名 ;
查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
查询学生平均成绩大于80的所有学生的学号、姓名和平均成绩
查询课程相同且成绩相同的的学生的学号、课程号、学生成绩
(select cid,score from sc group by cid,score having(count(*)>=2)) b
select * from sc a inner join b on a.cid=b.cid and a.score=b.score;
select a.* from sc a inner join (select cid,score from sc group by cid,score having(count(*)>=2)) b on a.cid=b.cid and a.score=b.score;
查询全部学生都选修的课程的课程号和课程名
查询两门以上不及格课程的同学的学号及其平均成绩