MySQL
0.数据库基础
1.启动、停止、重启mysql服务器的命令
启动：sudo service mysql start
停止：sudo service mysql stop
重启：sudo service mysql restart
2.当字段只有两个值时，如性别、是否删除，使用哪个类型
bit
3.使用命令行连接数据库服务器的命令是什么？
mysql -u 用户名 -p
回车后写密码
4.列出数据库的命令
-
查看所有数据库
-
使用数据库
-
查看当前使用的数据库
-
创建数据库
-
删除数据库
查看所有数据库：show databases;
使用数据库：use 数据库名;
查看当前使用的数据库：select database();
创建数据库：create database 数据库名 charset=utf8;
删除数据库：drop database 数据库名;
5.创建数据库python
create database python charset=utf8;
6.设计班级classes表结构为id、name、isdelete，编写创建表的语句
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(10),
isdelete bit default 0
);
7.列出表的命令
-
查看当前数据库的所有表
-
查看指定表的结构
查看当前数据库中所有表：show tables;
查看表结构：desc 表名;
8.向班级表中插入数据python1、python2、python3
insert into classes(name) values('python1'),('python2'),('python3');
9.列出数据操作语句的语法
-
增加
-
修改
-
删除
-
基本查询
增加：insert into 表名(列...) values(值...);
修改：update 表名 set 列=值,... where ...;
删除：delete from 表名 where ...;
查询：select * from 表名;
一.单表查询
1. 简单查询（难度:**)
# 创建表语句
create table students(
id int unsigned auto_increment primary key not null,
name varchar(20) not null,
chinese tinyint not null,
english tinyint not null,
math tinyint not null
);
insert into students values('张小明',89,78,90);
insert into students values(2,'李进',67,53,95);
insert into students values(3,'王五',87,78,77);
insert into students values(4,"李一",88,98,92);
insert into students values(5,"李来财",82,84,67);
insert into students values(6,"张进宝",55,85,45);
insert into students values(7,"黄蓉",75,65,30);
1) 查询表中所有学生的信息。
select * from students;
2) 查询表中所有学生的姓名和对应的英语成绩。
select name, english from students;
3) 过滤表中重复数据。
# 过滤英语成绩中的重复数据
select distinct english from students;
4) 统计每个学生的总分。
select name,sum(chinese+english+math) as "总分" from students group by name;
5) 在所有学生总分数上加10分特长分。
select name,sum(chinese+english+math+10) from students group by name;
6) 使用别名表示学生分数。
select name,sum(chinese+english+math) as "总分" from students group by name;
7) 查询姓名为李一的学生成绩
select name,sum(chinese+english+math) from students where name="李一";
8) 查询英语成绩大于90分的同学
select * from students where english > 90;
9) 查询总分大于200分的所有同学
select * from students where (english+chinese+math) > 200;
10) 查询英语分数在 80－90之间的同学。
select * from students where english between 80 and 90;
11) 查询数学分数为89,90,91的同学。
select * from students where english in (89,90,91);
12) 查询所有姓李的学生英语成绩。
select name,english from students where name like "李%";
13) 查询数学分80并且语文分80的同学。
select name from students where math = 80 and chinese = 80;
14) 查询英语80或者总分200的同学
select * from students where english =80 or (english+chinese+math) > 200;
15) 对数学成绩排序后输出。
select name,math from students order by math desc;
16) 对总分排序后输出，然后再按从高到低的顺序输出
select name,(english+math+chinese) as a from students order by a desc;
17）对姓李的学生成绩排序输出
select name,(english+math+chinese) as a from students having name like "李%" order by a desc ;
2. 简单查询(二)（难度：***）
1）查询总成绩最高及最低的学生信息
# 最高成绩学生信息
select * from students where (english+math+chinese) = (select max(english+chinese+math) from students);
# 最低成绩学生信息
select * from students where (english+math+chinese) = (select max(english+chinese+math) from students);
2）查询每一个学生的平均的成绩
# 求平均值，保留两位小数
select name,round((chinese+english+math)/3, 2) from students order by name;
3）查询所有学生各科的平均成绩
select round(sum(chinese)/count(*), 2),round(sum(english)/count(*), 2),round(sum(math)/count(*), 2) from students;
4）查询数学成绩比李一同学高的学生成绩
# 先求出李一的数学成绩，再查询比较
select name,math from students where math > (select math from students where name="李一");
5）列出总成绩比学生“王五”高的所有学生姓名、总成绩
# 先求出王五的总成绩，再查找比王五总成绩高的人名及成绩
select name, (chinese+english+math) from students where (chinese + english + math) > (select sum(english+chinese+math) from students where name="王五");
6）列出总成绩高于所有学生平均成绩的学生信息
# 先求出所有学生平均成绩sum(chinese+english+math)/count(*),再求出总成绩高于平均成绩的信息
select * from students where (chinese+english+math) > (select sum(chinese + english + math)/count(*) from students);
二.员工部分表
1.部门表Dept
-
建表语句
CREATE TABLE dept (
DEPTNO FLOAT(2) PRIMARY KEY, -- 部门号
DNAME VARCHAR(14) , -- 部门名称
LOC VARCHAR(13) ) ; -- 部门地址
-
数据
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
2.员工表EMP
-
建表语句
CREATE TABLE emp
(EMPNO float(4) PRIMARY KEY, -- 员工编号
ENAME VARCHAR(10), -- 员工姓名
JOB VARCHAR(9), -- 员工职位
MGR float(4), -- 员工上级工号
HIREDATE DATE, -- 生日
SAL float(7,2), -- 薪水
COMM float(7,2), -- 年终奖
DEPTNO float(2) REFERENCES dept); -- 部门号
-
数据
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
单表基础查询
1) 查询没有上级的员工全部信息（mgr是空的）
SELECT * FROM emp WHERE ISNULL(mgr);
2) 列出30号部门所有员工的姓名、薪资
SELECT ename,sal,deptno FROM emp WHERE deptno = '30';
3) 查询姓名包含 'A'的员工姓名、部门名称
SELECT ename,dname FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO AND ename LIKE "%A%";
4) 查询员工“TURNER”的员工编号和薪资
SELECT ename,empno,sal FROM emp WHERE ename = 'TURNER';
5) -- 查询薪资最高的员工编号、姓名、薪资
SELECT empno,ename,sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);
6) -- 查询10号部门的平均薪资、最高薪资、最低薪资
SELECT AVG(sal),MAX(sal),MIN(sal) FROM emp WHERE deptno = '10';
子查询
1) 列出薪金比员工“TURNER”多的所有员工姓名（ename）、员工薪资（sal）
SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='TURNER');
2) 列出薪金高于公司平均薪金的所有员工姓名、薪金。
SELECT ename,sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
3) 列出与“SCOTT”从事相同工作的所有员工姓名、工作名称(不展示Scott的姓名、工作)
SELECT ename,job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'scott') AND ename != 'scott';
4) 列出薪金高于30部门最高薪金的其他部门员工姓名、薪金、部门号。
SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = '30') AND deptno != '30';
5) -- 查询薪资最高的员工编号、姓名、薪资
SELECT empno,ename,sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);
6) 列出薪金高于本部门平均薪金的所有员工姓名、薪资、部门号、部门平均薪资。
SELECT emp.ename,emp.sal,emp.deptno,t.avgsal FROM emp ,(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t WHERE emp.DEPTNO = t.deptno AND emp.sal > t.avgsal;
7) 列出所有部门的详细信息：部门名称、部门编号、部门人数。
SELECT d.dname,d.deptno,IFNULL(t.num,0) '部门人数' FROM (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) t RIGHT JOIN dept d ON t.deptno = d.deptno;
8) -- 查询出king所在部门的工作年限最大的员工名字及入职时间
SELECT ename,HIREDATE FROM emp WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'king'));
9) 查询出管理员工人数最多的人的名字和他管理的人的名字
SELECT a.ename '员工',a.empno '员工编号',b.ename '领导',b.empno '领导编号' FROM emp a,emp b WHERE a.mgr = b.empno AND b.empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(*) = (SELECT COUNT(*) num FROM emp GROUP BY mgr ORDER BY num DESC LIMIT 0,1));
10) 查询出工资成本最高的部门的部门号、部门名称、部门工资成本
SELECT emp.DEPTNO,dept.DNAME,SUM(sal + IFNULL(comm,0)) total FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO GROUP BY emp.DEPTNO,dept.DNAME HAVING total = (SELECT SUM(sal + IFNULL(comm,0)) total FROM emp GROUP BY deptno ORDER BY total DESC LIMIT 0,1);
自连接
1) 列出所有员工的姓名及其直接上级的姓名。
解法1：SELECT a.ename,b.ename "上级" FROM emp a LEFT JOIN emp b ON a.mgr=b.empno;
解法2：SELECT ename '员工姓名',( SELECT ename FROM emp WHERE empno = se.mgr) '上级姓名' FROM emp se;
2) 列出受雇日期早于其直接上级的所有员工编号、员工姓名、员工入职时间、上级姓名、上级入职时间
SELECT a.empno,a.ename,a.hiredate,b.ename,b.hiredate FROM emp a,emp b WHERE a.mgr=b.empno AND a.hiredate<b.hiredate;
左右连接
1) 查询所有的部门编号及部门下员工编号、员工姓名。
SELECT d.DEPTNO,e.EMPNO,e.ENAME FROM emp e RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO;
函数应用
1) 列出最低薪金大于1500的工作名称（job）以及最低薪金
SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='TURNER');
SELECT job,MIN(sal) msal FROM emp GROUP BY job HAVING msal > 1500;
2) 列出在每个部门工作的员工数量、平均工资
SELECT deptno '部门号',COUNT(*) '员工数量' ,AVG(sal) '平均薪资' FROM emp GROUP BY deptno;
3) -- 查询每个部门的部门号、最高薪资
SELECT deptno,MAX(sal) maxSal FROM emp GROUP BY deptno;
4) 查询每种工作的工作名称和最低工资
SELECT job,MIN(sal) '最低薪资' FROM emp GROUP BY job;
5) 列出所有员工的员工姓名、年工资,按年薪从低到高排序。
SELECT ename,(sal*12) AS yearSal FROM emp ORDER BY yearSal asc;
6) -- 查询每个部门中薪资最高的员工姓名、薪资、部门号
SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT MAX(sal) sal,deptno FROM emp GROUP BY deptno) t WHERE e.sal = t.sal AND e.DEPTNO = t.deptno;
7) -- 查询不是领导的员工编号、员工姓名、员工职位
SELECT empno,ename,job FROM emp WHERE empno NOT IN(SELECT DISTINCT IFNULL(mgr,'') FROM emp);
多表查询
1) 查询岗位（job）是 'CLERK' 的员工编号、员工姓名、所在部门名称
SELECT e.EMPNO,e.ENAME,d.DNAME FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO AND e.JOB = 'CLERK';
2) 列出所有员工的姓名、部门名称和工资。
SELECT emp.ENAME,dept.DNAME,emp.SAL FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO;
3) 查询至少有一个员工的部门编号、员工数量
SELECT b.deptno,COUNT(*) num FROM emp a,dept b WHERE a.deptno = b.deptno GROUP BY b.DEPTNO,b.DNAME HAVING num >=1;
4) -- 查询出没有员工的那个部门的部门编号和部门名称
SELECT deptno,dname FROM dept WHERE deptno NOT IN(SELECT DISTINCT deptno FROM emp);
5) 查询在部门“SALES”（销售部）工作的员工的姓名、部门编号
解法1：SELECT emp.ENAME,dept.DEPTNO FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO AND dept.DNAME = 'SALES';
解法2：SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');
三.教师学生表
学生表Student
-
建表语句
CREATE TABLE student ( sid varchar(10) NOT NULL, #（学生编号） sName varchar(20) DEFAULT NULL, #（学生姓名） sAge datetime DEFAULT '1980-10-12 23:12:36', #（学生出生日期） sSex varchar(10) DEFAULT NULL, #（学生性别） PRIMARY KEY (sid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
数据
insert into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),('1002','张无极','1995-10-12 23:12:36','男'),('1003','李奎','1992-10-12 23:12:36','女'),('1004','李元宝','1980-10-12 23:12:36','女'),('1005','李世明','1981-10-12 23:12:36','男'),('1006','赵六','1986-10-12 23:12:36','男'),('1007','田七','1981-10-12 23:12:36','女');
课程表 Course
-
建表语句
CREATE TABLE course ( cid varchar(10) NOT NULL, #（课程id编号） cName varchar(10) DEFAULT NULL, #（课程名称） tid int(20) DEFAULT NULL, #（课程节数） PRIMARY KEY (cid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
数据
insert into course(cid,cName,tid) values ('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
成绩表 SC
-
建表语句
CREATE TABLE sc ( sid varchar(10) DEFAULT NULL, cid varchar(10) DEFAULT NULL, score int(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
数据
insert into sc(sid,cid,score) values ('1001','001',80),('1001','002',60),('1001','003',75),('1002','001',85),('1002','002',70),('1003','004',100),('1003','001',90),('1003','002',55),('1004','002',65),('1004','003',60);
教师表 Teacher
-
建表语句
CREATE TABLE teacher (
tid int(10) DEFAULT NULL,
tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
数据
insert into teacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');
1.单表查询
1) 查询每门课程被选修的学生数
select cid,count(sid) from sc group by cid;
2) 分别查询男生、女生的数量
select sSex,count(*) from student group by sSex;
3) 查询姓“张”的学生名单
select sname from student where sname like '张%';
4) 查询同名同姓学生名单，并统计同名人数
select sname,count(*) from student group by sname having count(*) > 1;
5) 查询1981年出生的学生名单
select sid, sname from student where Year(sage) = 1981;
6) 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cid , avg(score) avgs from sc group by cid order by avgs asc, cid desc;
7) 选了课程的学生人数
select count(distinct sid) as 选课人数 from sc;
8) 查询各个课程及相应的选修人数
select cid, count(cid) from sc group by cid;
9) 查询至少选修了2门课程的学生学号
select sid from sc group by sid having count(*) > 2;
10) 统计每门课程的学生选修人数(至少有2人选修的课程才统计)。要求输出课程号和选修人数
select cid ,count(*)
from sc
group by cid having count(*) > 1
order by count(*) desc,cid asc;
11) 删除“1002”同学的“001”课程的成绩
delete from sc where sid ='1002' and cid = '001';
12) 查找“004”课程分数小于60，按分数降序排列的同学学号
select sid, score from sc where cid = '004' and score > 60 order by score desc;
2.子查询
1) 查询出只选修了一门课程的全部学生的学号和姓名
select sid,sname from student where sid=(select sid from sc group by sid having count(sid)=1);
3.自连接
1) 查询不同课程成绩相同的学生的学号、课程号、学生成绩；
select distinct sca.sid, sca.cid,scb.score from sc as sca ,sc as scb where sca.score=scb.score and sca.cid <>scb.cid;
4.多表查询
1) 查询平均成绩大于70 的所有学生的学号、姓名和平均成绩
select s.sid,s.sname,sas.avgs
from student as s,( select sid,avg(score) avgs from sc group by sid having avgs>70) sas
where s.sid=sas.sid;
2) 查询课程名称为“数据库”，且分数低于60的学生姓名和分数
select s.sid,s.sname
from sc,student as s,course as c
where sc.sid=s.sid and c.cid=sc.cid and c.cname='数据库' and sc.score<60;
3) 查询所有学生的选课情况
select
s.sid,s.sname,c.cid,c.cname
from sc,student as s,course as c
where sc.sid=s.sid and c.cid=sc.cid;
4) 查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
select s.sname,c.cname,sc.score
from sc,student s,course c
where sc.sid=s.sid and c.cid=sc.cid and sc.score>70;
5) 查询有不及格的课程的课程编号，课程名称，成绩，并按课程号从大到小排列
select
sc.cid,c.cname,sc.score
from sc ,course as c
where sc.score<60 and c.cid=sc.cid
order by sc.cid desc;
6) 查询选了课程编号为003且课程成绩在70分以上的学生的学号和姓名
select s.sid,s.sname
from sc,student s
where sc.sid=s.sid and sc.score>70 and sc.cid="003";
7) 查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select s.sname,c.cname, sc.score
from student as s,sc,course as c,taache'r as t
where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid
and t.tname ='叶平'
and sc.score=(select max(score) from sc where cid = c.cid);
8) 查询两门以上不及格课程的同学的学号及其平均成绩
select sid , avg(score), count(*)
from sc where score < 60 group by sid having count(*) > 2;
四。商品信息表
1.1. 建表语句
l 商家表
CREATE TABLE shop (
shopID varchar(10) NOT NULL COMMENT '商家ID',
shopName varchar(50) DEFAULT NULL COMMENT '商家名称',
PRIMARY KEY (shopID)
);
l 商品表
CREATE TABLE product (
pid varchar(32) NOT NULL COMMENT '商品ID',
productName varchar(32) DEFAULT NULL COMMENT '商品名称',
productPrice float DEFAULT NULL COMMENT '商品价格',
storeNum int(11) DEFAULT NULL COMMENT '库存数量',
factory varchar(20) DEFAULT NULL COMMENT '厂家',
shopID varchar(10) DEFAULT NULL COMMENT '商家ID',
PRIMARY KEY (pid),
KEY shop_product (shopID),
CONSTRAINT shop_product FOREIGN KEY (shopID) REFERENCES shop (shopID)
);
l 订单表
CREATE TABLE order_ (
oid varchar(32) NOT NULL COMMENT '订单ID',
shopID varchar(30) DEFAULT NULL COMMENT '商家ID',
orderStatus varchar(4) DEFAULT NULL COMMENT '订单状态',
orderTime datetime DEFAULT NULL COMMENT '订单时间',
PRIMARY KEY (oid),
KEY shop_order (shopID),
CONSTRAINT shop_order FOREIGN KEY (shopID) REFERENCES shop (shopID)
);
l 订单商品中间表 - 订单项
CREATE TABLE order_item (
oid varchar(32) NOT NULL COMMENT '订单ID',
pid varchar(32) NOT NULL COMMENT '商品ID',
num int(11) DEFAULT NULL COMMENT '购买数量',
price float DEFAULT NULL COMMENT '小计价格',
PRIMARY KEY (oid,pid),
KEY it_product (pid),
CONSTRAINT it_order FOREIGN KEY (oid) REFERENCES order_ (oid),