SQL语句
基本sql语句
创建数据库;
create database 数据库名;
显示所有数据库:
show databases;
切换数据库:
use 数据库名;
显示数据表
show tables;
删除数据库:
drop database 数据库名;
查看表的结构:
desc 表名;
创建表练习
创建一个学生基本信息表(含部分外键约束),并进行简单的查询
CREATE TABLE student(
id INT(5) PRIMARY KEY auto_increment,-- 设置为主键,约束字段的值自动递增
name VARCHAR(10) not null,-- 不能为空
age INT(3) CHECK(age>=0 AND age<=100),-- 约束条件
email VARCHAR(20) UNIQUE, -- 值唯一
classname VARCHAR(10),
enterdate DATE
);
-- 指定字符集
ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 查询表结构
DESC student
-- 查看表的数据
SELECT * from student
-- 查看建表语句
show CREATE TABLE student
DML语言(对数据的修改)
-- 查询表数据
SELECT * FROM student
-- INSERT添加数据
INSERT INTO student VALUES(1, "zs", "1班", "2018/10/10")
INSERT INTO student VALUES(3, "zs", "1班", "2018/10/10")
INSERT INTO student VALUES(4, "zs", "1班", "2018/10/10")
INSERT INTO student VALUES(5, "zs", "1班", "2018/10/10")
-- 添加表中字段
ALTER TABLE allgoods ADD name varchar(100) NOT NULL --表示在allgoods表中添加name字段不能为空
-- 添加部分字段中的数据
INSERT INTO student (id, name)VALUES(2, "zs")
-- updata 修改表数据
UPDATE student SET name='ls',enterdate="2018-10-11",classname="2班" WHERE id=2
UPDATE student SET name='ww',enterdate="2018-10-11",classname="2班" WHERE id=3
update student set age=age+1 where id=2
-- delete 删除表数据
-- from不可少
-- 内容和查询字段可不区分大小写
-- 不区分单双引号
DELETE FROM student WHERE id=4
DELETE FROM student WHERE id=5
快速创建表的方式:
-- 得到所有数据创建一个新的表
CREATE TABLE studentInfo2
AS
SELECT * FROM studentInfo
SELECT * FROM studentInfo2
-- 得到表结构
CREATE TABLE studentInfo3
AS
SELECT * FROM studentInfo2 WHERE 1=2
SELECT * FROM studentInfo3
-- 得到部分列和部分行
CREATE TABLE studentInfo4
as
SELECT sno, sname FROM studentInfo WHERE classno=2
SELECT * FROM studentInfo4
DDL语言(对结构的修改)
-- 查询数据
SELECT * FROM student
-- 查询表结构
desc student
-- 修改表结构
-- 增加一列
ALTER TABLE student ADD sex VARCHAR(1) AFTER classname
ALTER TABLE student ADD sex VARCHAR(1)
ALTER TABLE student ADD age INT(3) AFTER name
-- 举例
-- 添加字段(规定只能规定为 未支付、未发货、已发货)
ALTER TABLE orderinfo ADD state VARCHAR(10) CHECK (state in ('未支付', '未发货', '已发货'))
-- 添加字段(快递单号)
ALTER TABLE orderinfo ADD courierNum VARCHAR(200) COMMENT '快递单号'
-- 修改一列modify
ALTER TABLE student MODIFY sex VARCHAR(2)
ALTER TABLE student MODIFY age DOUBLE(3,1)
UPDATE student SET age=18 WHERE id=1
-- 删除一列
ALTER TABLE student DROP sex
非外键约束
CREATE TABLE student(
id INT(5) auto_increment,-- 约束字段的值自动递增,自动递增和非空只能在后面添加
name VARCHAR(10) not null,-- 非空
age INT(3),
email VARCHAR(20),
classname VARCHAR(10),
enterdate DATE,
CONSTRAINT pk_stu PRIMARY KEY(id),-- 设置为主键,
CONSTRAINT ck_stu_age CHECK(age>=0 AND age<=100),-- 定义非外键约束数值范围
CONSTRAINT ck_stu_email UNIQUE(email)-- 定义不能为空
);
外键约束(foreign key)
参考值取决于另外一张表的约束
CREATE TABLE studentInfo(
sno INT(5) PRIMARY KEY auto_increment,
sname VARCHAR(5) not null,
classno INT(3),
-- 定义一个外键约束,将学生信息表和班级信息表的班级编号绑定
constraint fk_stu_classno FOREIGN KEY(classno) references classInfo(classno)
)
CREATE TABLE classInfo(
classno INT(3) PRIMARY KEY auto_increment,
classname VARCHAR(10)
)
优点:防止学生信息表中出现不存在的班级
外键策略
no action:先将对应信息设置为空,再删除
外键策略:cascade级联:
适用场景:如商品评论,朋友圈评论
-- 外键策略:cascade级联
ALTER TABLE studentInfo DROP FOREIGN KEY fk_stu_classno -- 先删除原有的外键
-- 1.添加cascade外键策略
ALTER TABLE studentInfo add constraint fk_stu_classno FOREIGN KEY(classno) references classInfo(classno) on update cascade on delete cascade
-- 2.测试
UPDATE classInfo SET classno=5 WHERE classno=2
外键策略:set null:
适用场景:学生班级
-- 外键策略:set null
ALTER TABLE studentInfo add constraint fk_stu_classno FOREIGN KEY(classno) references classInfo(classno) on update set null on delete set null
UPDATE classInfo SET classno=10 WHERE classno=3
查询语句
简单查询
-- 查询整个表
SELECT * FROM student
-- 查询部分列
SELECT SId,Sname,Sage FROM student
-- 查询部分行和列
SELECT SId,Sname FROM student WHERE SId>2
-- 别名 让列名显示为我们指定的
SELECT SId '编 号',Sname '姓 名' FROM student
-- 去重 去除ssex中重复的
SELECT DISTINCT sname FROM student
-- 排序 根据sage排升序
SELECT * FROM student ORDER BY sage
-- 降序排列 DESC不能省略
SELECT * FROM student ORDER BY sage DESC
单表查询
where基础子句
-- where子句
-- 条件大于小于等于不等于
SELECT * FROM sc WHERE score > 80
SELECT * FROM sc WHERE score < 80
SELECT * FROM sc WHERE score =80
SELECT * FROM sc WHERE score !=80
-- 逻辑运算符and or %
-- 且
SELECT * FROM sc WHERE score > 60 AND score <= 80 ORDER BY score
-- 或
SELECT * FROM sc WHERE score = 60 OR score = 80 ORDER BY score
-- 或
SELECT * FROM sc WHERE score in (60, 80)
-- 模糊查询 _代表一个字符 %代表任意
SELECT * FROM student WHERE sname like '_四%'
SELECT * FROM student WHERE sage like '%1990%'
-- 是否为空
SELECT * FROM student WHERE sname IS not NULL
SELECT * FROM student WHERE sname IS NULL
函数
多行函数MAX(score),MIN(score),AVG(score),SUM(score),COUNT(score)
SELECT MAX(score),MIN(score),AVG(score),SUM(score),COUNT(score) FROM sc
除此之外就都是单行函数
日期时间函数:
-- 日期和时间函数CURDATE(),CURTIME()
SELECT CURDATE(),CURTIME()
-- 当前时间的函数
SELECT NOW(),SYSDATE()
流程函数:
if函数
-- if函数
SELECT sid,cid,score,if(score>=60,'及格','不及格') AS 判断 FROM sc ORDER BY score
-- 如果是空,则赋值为0
SELECT sid,cid,score,cid+IFNULL(score,0) FROM sc
case函数:
-- CASE等值判断
SELECT sid,cid,score,
CASE score
WHEN 90 THEN '刚好90'
WHEN 80 THEN '刚好80'
WHEN 70 THEN '刚好70'
ELSE '其他'
end '分数判断' FROM sc
-- 不等值判断
SELECT sid,cid,score,
CASE
WHEN score<=60 THEN 'D'
WHEN score<=70 THEN 'C'
WHEN score<=80 THEN 'B'
ELSE 'A'
END '成绩等级'
FROM sc
-- 其他常用函数,DATABASE(),USER(),VERSION()分别是当前数据库 当前用户 版本
SELECT DATABASE(),USER(),VERSION()
其他函数可参考官方文档
https://www.mysqlzh.com/doc/118.html
分组:GROUP BY HAVING
-- 查询每科的平均分数并按照降序排列
-- GROUP BY HAVING属于分组加筛选
SELECT cid,avg(score)
FROM sc
GROUP BY cid
HAVING avg(score)>60
ORDER BY avg(score) DESC
注意:
1.如果没有group by分组,字段和多行函数是不能混用的
2.关键字顺序固定
3.和多行函数一起出现的字段需要出现在GROUP BY中
4.GROUP BY一级筛选,having二级筛选
多表查询(重点)
练习链接:
https://www.jianshu.com/p/476b52ee4f1b
内连接查询(只显示匹配的数据)
-- 多表连接查询join
-- 交叉连接 CROSS JOIN
SELECT *
FROM student
CROSS JOIN sc
-- 查询学生编号 学生姓名 学生选课编号 学生课程成绩
-- 自然连接 NATURAL JOIN
-- 优点:简单 方便
-- 自动匹配
SELECT sid,sname,cid,score
FROM student
NATURAL JOIN sc
-- 如果想要指定表中的指定字段数据,最好指定表名
-- 如:SELECT sc.sid,student.sname,sc.cid,sc.score
SELECT sc.sid,student.sname,sc.cid,sc.score
FROM student
NATURAL JOIN sc
-- 如果表名太长,则可以给表名简写
SELECT sc.sid,s.sname,sc.cid,sc.score
FROM student s
NATURAL JOIN sc
-- USING子句
-- 缺点:关联字段不同名的情况难以解决
SELECT sc.sid,s.sname,sc.cid,sc.score
FROM student s
JOIN sc
USING(SId)
-- on子句 可以解决using的缺点
-- 建议使用
SELECT sc.sid,s.sname,sc.cid,sc.score
FROM student s
JOIN sc
ON (s.sid = sc.sid)
外连接查询
除了显示匹配的数据以外,还会显示部分或者全部不匹配的数据
-- 外连接
-- 左外连接 LEFT OUTER JOIN 除了显示匹配的数据,还要显示左表不匹配的数据
SELECT *
FROM student s
LEFT JOIN sc
ON s.sid=sc.sid
-- 右外连接 RIGHT OUTER JOIN 除了显示匹配的数据,还要显示右表不匹配的数据
SELECT *
FROM student s
RIGHT JOIN sc
ON s.sid=sc.sid
-- 全外连接 FULL OUTER JOIN mysql目前还不支持全外连接 可以采用以下方法产生全外连接
SELECT *
FROM student s
LEFT JOIN sc
ON s.sid=sc.sid
UNION -- 加上UNION求并集 会去重 效率较低
SELECT *
FROM student s
RIGHT JOIN sc
ON s.sid=sc.sid
SELECT *
FROM student s
LEFT JOIN sc
ON s.sid=sc.sid
UNION ALL-- 加上UNION求并集 不会去重 效率较高
SELECT *
FROM student s
RIGHT JOIN sc
ON s.sid=sc.sid
三表查询
可举一反三就能多表查询
-- 先建立两个表之间的查询
SELECT s.sid,s.sname,sc.cid,sc.score
FROM student s
JOIN sc
ON (s.sid = sc.sid)
-- 再建立第三张表的查询
SELECT s.sid,s.sname,sc.cid,sc.score,c.cname
FROM student s
JOIN sc
ON (s.sid = sc.sid)
JOIN course c
ON (c.CId = sc.CId)
汇总练习
练习链接(原题在这儿):
https://www.jianshu.com/p/476b52ee4f1b
练习1:
-- 查询[" 01 "课程比" 02 "课程成绩高]的学生的信息及课程分数
-- 总体框架
SELECT s.*,sc.score
FROM student s
JOIN sc
ON s.sid=sc.sid
-- 步骤1 先分别查询出sc表中课程1和课程2的表
SELECT * FROM sc WHERE CId = 01
SELECT * FROM sc WHERE CId = 02
-- 步骤二、将步骤一中的两个表分别取出再进行select查询
SELECT class1.score,class2.score
FROM (SELECT * FROM sc WHERE CId = 01) class1,
(SELECT * FROM sc WHERE CId = 02) class2
WHERE class1.sid = class2.sid AND class1.score > class2.score
-- 步骤三、将步骤二中当作是一个表与student表关联起来
SELECT s.*,t.score,t.score2
FROM student s
JOIN (SELECT class1.*,class2.score AS 'score2'
FROM
(SELECT * FROM sc WHERE CId = 01) class1,
(SELECT * FROM sc WHERE CId = 02) class2
WHERE class1.sid = class2.sid AND class1.score > class2.score
) t
ON s.sid=t.sid
练习2:查询[平均成绩大于等于 60 分]的同学的学生编号和学生姓名和平均成绩
-- 查询[平均成绩大于等于 60 分]的同学的学生编号和学生姓名和平均成绩
-- 先将所需要的数据筛选出来
SELECT s.sid,s.sname,sc.score
FROM student s
JOIN sc
ON s.sid=sc.sid
-- 对学生进行分组,计算每个学生的平均成绩
SELECT SId,avg(score) as avgscore
FROM sc
GROUP BY sid
HAVING avg(score)>60
-- sql整体汇总
SELECT s.sid,s.sname,t.avgsc
FROM student s
JOIN (SELECT SId,avg(score) as avgsc
FROM sc
GROUP BY sid
HAVING avg(score)>60) t
ON s.sid=t.sid
练习3:查询在 SC 表存在成绩的学生信息
-- 练习3 查询在 SC 表存在成绩的学生信息
-- 先将所有学生信息查询出来
SELECT *
FROM student
-- 对成绩表进行筛选
SELECT *
FROM sc
WHERE score>0
-- 对两张表进行汇总
SELECT s.*
FROM student s
JOIN (SELECT *
FROM sc
WHERE score>0) c
ON s.sid=c.sid
练习4:查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 筛选出学生编号和学生姓名
SELECT s.sid,sname
FROM student s
-- 分别筛选选课总数、所有课程的总成绩
SELECT *,COUNT(cid) count
FROM sc
GROUP BY sc.sid
SELECT *,SUM(score) 'sum'
FROM sc
GROUP BY sc.SId
-- 将两张表看作一张表进行汇总
SELECT cou.sid,cou.count,sum.sum
FROM (SELECT *,COUNT(cid) count
FROM sc
GROUP BY sc.sid) cou
JOIN (SELECT *,SUM(score) 'sum'
FROM sc
GROUP BY sc.SId) sum
ON cou.sid=sum.sid
-- 汇总
SELECT s.sid,s.sname,c.count,c.sum
FROM student s
JOIN (
SELECT cou.sid,cou.count,sum.sum
FROM (SELECT *,COUNT(cid) count
FROM sc
GROUP BY sc.sid) cou
left JOIN (SELECT *,SUM(score) 'sum'
FROM sc
GROUP BY sc.SId) sum
ON cou.sid=sum.sid
) c
ON s.sid=c.sid
练习5:查询「李」姓老师的数量
-- 查询「李」姓老师的数量
SELECT *,count(Tname)
FROM teacher t
GROUP BY t.TId
HAVING Tname LIKE '李%'
练习6:查询学过「张三」老师授课的同学的信息
-- 查询学过「张三」老师授课的同学的信息
-- 先查询出所有学生信息
SELECT *
FROM student s
-- 教师表与课程分配表进行关联并筛选“张三”老师
SELECT t.*,c.cid
FROM teacher t
JOIN course c
ON t.tid=c.tid
WHERE tname='张三'
-- 再与成绩表进行关联
SELECT sc.SId,t.*
FROM sc
JOIN (
SELECT t.*,c.cid
FROM teacher t
JOIN course c
ON t.tid=c.tid
WHERE tname='张三'
) t
ON sc.CId=t.cid
-- 最后与学生表进行关联
SELECT s.*
FROM student s
LEFT JOIN (
SELECT sc.SId,t.*
FROM sc
JOIN (
SELECT t.*,c.cid
FROM teacher t
JOIN course c
ON t.tid=c.tid
WHERE tname='张三'
) t
ON sc.CId=t.cid
) tc
ON s.sid=tc.sid
练习7:查询[没有学全所有课程]的同学的信息
-- 查询[没有学全所有课程]的同学的信息
-- 先查询所有同学的信息
SELECT *
FROM student s
-- 筛选没有学完所有课程的sid
-- 这里先将学生分组,统计每个学生的选课数量,筛选出课程数不等于3的学生
SELECT *,COUNT(cid) csum
FROM sc
GROUP BY sid
HAVING csum != 3
-- 再和所有同学的信息进行关联
SELECT s.*
FROM student s
JOIN (
SELECT *,COUNT(cid) csum
FROM sc
GROUP BY sid
HAVING csum != 3
) c
ON s.sid=c.sid
练习8:查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 1.查询[至少有一门课与学号为" 01 "的同学所学相同]的同学的信息
SELECT *
FROM student s
-- 2.选课信息筛选出学号为01的同学所学课程
-- 得到01同学选了所有课程
SELECT sc.sid,sc.cid
FROM sc
WHERE sid=01
-- 3.筛选至少选了一门课
SELECT *,COUNT(CId) cidnum
FROM sc
GROUP BY SId
HAVING cidnum >=1
-- 1和3合并关联
SELECT s.*
FROM student s
JOIN (
SELECT *,COUNT(CId) cidnum
FROM sc
GROUP BY SId
HAVING cidnum >=1
) c
ON s.sid=c.sid
练习9:查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 查询和[" 01 "号的同学学习的课程 完全相同]的其他同学的信息
-- 先查询所学课程总数为3的所有学生
SELECT *,COUNT(cid) cs
FROM sc
GROUP BY sid
HAVING sid != 01 AND cs = 3
-- 再和学生信息表进行关联
SELECT s.*
FROM student s
JOIN (
SELECT *,COUNT(cid) cs
FROM sc
GROUP BY sid
HAVING sid != 01 AND cs = 3
) cs1
ON s.sid=cs1.sid
这里用了比较直的方法,如果说筛选课程很多的话就不推荐这么使用
不过这算是对于这个表来说比较直观的方法了
初学者能力有限
练习10:查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 查询[没学过"张三"老师讲授的任一门课程]的学生姓名
-- 1查询学生表
SELECT s.sid,s.sname
FROM student s
-- 2联接teacher表和cource表
SELECT c.*,t.tname
FROM course c
JOIN teacher t
ON c.tid = t.tid
-- 3查询sc表
SELECT sc.*,t1.tname
FROM sc
JOIN (
SELECT c.*,t.tname
FROM course c
JOIN teacher t
ON c.tid = t.tid
) t1
ON sc.CId=t1.cid
-- 汇总1 3
SELECT s.sname
FROM student s
JOIN (
SELECT sc.*,t1.tname
FROM sc
JOIN (
SELECT c.*,t.tname
FROM course c
JOIN teacher t
ON c.tid = t.tid
) t1
ON sc.CId=t1.cid
) t2
ON s.sid=t2.sid
WHERE t2.tname != '张三'
补充子查询
代码来源教学视频
主要点在于where中大于小于嵌套表再结合all,in,any的使用
--多行子查询
--查询[工资低于任意一个“CLERK”的工资的]雇员信息。
SELECT * FROM EMP WHERE SAL < ALL (SELECT SAL FROM EMP WHERE JOB ='CLERK')
--查询[工资比所有的"SALESMAN"都高的]雇员的编号、名字和工资。-- ALL
select empno,ename,sal from emp where sal > all (select sal from emp where job ='SALESMAN')select empno,ename,sal from emp where sal > (select max(sal) from emp where job ='SALESMAN')