SQL基础

笔记(一)入门

一.SQL语言的分类:

(1)DDL 语句:数据库定义语言。(和操作结构有关)主要用于定义数据库,表,视图,索引和触发器等。
CREATE 语句主要用于创建数据库,创建表,创建视图。
ALTER 语句主要用于修改表的定义,修改视图的定义。
DROP 语句主要用于删除数据库,删除表和删除视图等。 (数据库结构的删除)
(2)DML 语句:数据库操作语言。(和操作数据有关)主要用于插入数据,查询数据,更新数据,删除数据。
INSERT 语句用于插入数据,
SELECT 语句用于查询数据,
UPDATE 语句用于更新数据,
DELETE 语句用于删除数据。 (数据库中的数据的删除)
(3)DCL 语句:数据库控制语言。(和数据库用户权限有关)主要用于控制用户的访问权限。
其中 GRANT 语句用于给用户增加权限,
REVOKE 语句用于收回用户的权限。

二.创建、修改和删除表

1.创建表的语法结构

CREATE TABLE 表名(属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
.
.
属性名 数据类型);
完整性约束条件是对字段进行限制。要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件,数据库系统将不执行用户的操作。其目的是为了保证数据库中数据的完整性。MySQL 中基本的完整性约束条件如下表:
约束条件 说明
PRIMARY KEY 标识该属性为该表的主键,可以唯一的标识对应的元组
FOREIGN KEY 标识改属性为该表的外键,是与之联系的某表的主键
NOT NULL 标识该属性不能为空
UNIQUE 标识该属性的值是唯一的
AUTO_INCREMENT 标识该属性的值自动增加,这是 MySQL 的 SQL 语句的特色
DEFAULT 为该属性设置默认值

1.1设置表的主键

属性名 数据类型 PRIMARY KEY

1.2 设置表的外键

CONSTRAINT 外键别名 FOREIGN KEY(属性 1.1,属性 1.2,…,属性 1.n)
REFERENCES 表名(属性 2.1, 属性 2.2,…,属性 2.n)

1.3 设置表的非空约束

属性名 数据类型 NOT NULL 下面在 subject 表中设置字段俗不那么非空约束。
CREATE TABLE score (
scoid int(11) PRIMARY KEY AUTO_INCREMENT COMMENT ‘成绩 ID’,
stuid int(11) DEFAULT NULL COMMENT ‘学生 ID’,
subid int(11) NOT NULL AUTO_INCREMENT COMMENT ‘科目 ID’,
subname varchar(32) NOT NULL COMMENT ‘科目名称’,
CONSTRAINT fk_score_student FOREIGN KEY (stuid)
REFERENCES student (stuid)
);
注:score表中包含4个字段。其中scoid字段是主键;subname 字段为非空字段,这
两个字段的值都不能为空值(NULL)。 stuid字段是外键,fk_score_student
是外键的别名;

2.查看表结构
2.1DESCRIBE 语句的语法形式如下:

DESCRIBE 表名;
其中,“表名”参数指所要查看的表的名称。
例子:DESCRIBE student;或者 DESC student;

2.2查看表详细结构语句

SHOW CREATE TABLE 表名;
其中,“表名”参数指所要查看的表的名称。
SHOW CREATE TABLE student

3. 修改表
3.1修改表语法形式如下:

ALTER TABLE 旧表名 RENAME [TO] 新表名;

3.2修改字段的数据类型

ALTER TABLE 表名 MODIFY 属性名 数据类型;

3.3修改字段名

ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;

注意:总结

MODIFY 和 CHANGE 都可以改变字段的数据类型。不同的是:
(1)CHANGE 可以在改变字段数据类型的同时,改变字段名。MODIFY 只能用来改变字段的数据类型,不能修改字段名。
(2)CHANGE 如果不改变字段名,只修改字段类型,CHAGE 后面必须跟两个同样的字段名。
(3)养成良好的习惯,如果是指修改字段的数据结构就使用 MODIFY,如果要修改字段名+数据结构就使用 CHANGE。

3.4.删除字段

ALTER TABLE 表名 DROP 属性名;
其中,‘属性名’参数指需要从表中删除的字段的名称。
删除表的外键约束。其基本语法如下:
ALTER TABLE 表名 DROP FOREIGN KEY 外键别名:

4.删除表

DROP TABLE 表名

三.插入数据

3.1为表的所有字段插入数据

INSERT INTO 表名 VALUES(值 1,值 2,…,值 n);
INSERT INTO student VALUES (‘1’, ‘张三’, ‘男’, ‘13312341111’, ‘1995-07-13’, ‘1’);

3.2为表的指定字段插入数据

INSERT INTO (属性 1,属性 2,…,属性 m) VALUES (值 1,值 2,…,值 m);
INSERT INTO student(stuid,name,sex,cid) VALUES (‘4’, ‘马六’, ‘女’, ‘1’);

3.3同时插入多条记录

INSERT INTO 表名 [(属性列表)] VALUES (取值列表 1), (取值列表 2),…, (取值列表 n);
INSERT INTO student VALUES (‘5’, ‘赵四’, ‘男’, ‘13312345555’, ‘1995-07-13’, ‘1’),
(‘6’, ‘Tom’, ‘男’, ‘13312346666’, ‘1995-07-14’, ‘2’),
(‘7’, ‘Jim’, ‘男’, ‘13312347777’, ‘1995-07-15’, ‘2’);

3.4将查询结果插入到另一个表中

INSERT INTO 表名 1 (属性列表 1) SELECT 属性列表 2 FROM 表名 2 WHERE 条件表达式;

四.更新数据 、删除数据

更新数据

UPDATE 表名 SET 属性名 1=取值 1,属性名 2=取值 2,…,属性名 n=取值 n WHERE 条件表达式;
UPDATE student SET name=‘Lily’,sex=‘女’ WHERE stuid=6;

删除数据

DELETE FROM 表名 [WHERE 条件表达式];
DELETE FROM student WHERE stuid=4;
DELETE FROM student WHERE cid=2;

笔记(二)基础查询

一.单表查询
1.基本查询语句

SELECT 属性列表
FROM 表名和视图列表
[WHERE 条件表达式 1]
[GROUP BY 属性名 1[HAVING 条件表达式 2]]
[ORDER BY 属性名 2[ASC|DESC]]

SELECT stuid,name,sex,phone,birthday
FROM student
WHERE birthday<‘1995-07-15’
ORDER BY phone DESC;

带 IN 关键字的查询

[NOT] IN(元素 1,元素 2,…,元素 n)
SELECT * FROM student WHERE stuid IN(2,3,4);
带 BETWEEN AND 的范围
[NOT] BETWEEN 取值 1 AND 取值 2
SELECT * FROM student WHERE birthday BETWEEN ‘1995-07-13’ AND ‘1995-07-15’;

带 LIKE 的字符匹配查询

[NOT] LIKE ‘字符串’
“%”可以代表任意长度的字符串,长度可以为 0。例如,b%k 表示以字母开头,以字母 k 结尾的任意长度的字符串。该字符串可以代表 bk、buk、book、break、bedrock 等字符串。
”只能表示单个字符。例如,b_k 表示以字母 b 开头,以字母 k 结尾的 3 个字符。中间的“”可以代表任意一个字符。字符串可以代表 bok、bak 和 buk 等字符串。下面使用 LIKE 关键字来匹配一个字符串‘Amy’。
SELECT 语句的代码如下:
查询姓张的学员信息
SELECT * FROM student WHERE NAME LIKE ‘张%’
查询姓名为两个字包含字符"张"的学生信息
SELECT * FROM student WHERE NAME LIKE '张_'

查询空值 IS [NOT] NULL

IS [NOT] NULL
“NOT”是可选参数,加上 NOT 后,表示字段不是空值时满足条件。
查询电话号码为空的学生信息解答:
SELECT * FROM student WHERE phone IS NULL

多条件查询 AND

多条件查询的语法如下:
SELECT * FROM TABLE_NAME WHERE 条件 1 AND/OR 条件 2 …
例如:查询 cid 为 1, 生日为 1995-07-13 的学生信息解答:
SELECT * FROM student
WHERE cid = 1 AND birthday = ‘1995-07-13’

对查询结果排序 ORDER BY 关键字

SELECT * FROM TABLE_NAME
[WHERE 条件]
ORDER BY 字段 1 [ASC/DESC] [,字段 2 [ASC/DESC]…]

SELECT * FROM student ORDER BY birthday DESC

用 LIMIT 限制查询结果的数量

SELECT * FROM TABLE_NAME
LIMIT [初始位置], 记录数
SELECT * FROM student ORDER BY birthday LIMIT 0, 5

DISTINCT(去除重复)

SELECT DISTINCT birthday FROM student
SELECT DISTINCT IFNULL( brithday,‘出生不详’) FROM student(有错误)
练习
一、简单查询
1.查询全部的行和列
USE studentmanager
SELECT * FROM student

2.查询所有学生姓名
SELECT name FROM student

3.查询多个列(学号、姓名、性别)
SELECT stuid,NAME,sex FROM student

– 4.DISTINC(去除重复)
SELECT DISTINCT birthday FROM student
SELECT DISTINCT IFNULL( brithday,‘出生不详’) FROM student(有错误)

– 二、常用算术运算符
– 使用别名 AS
SELECT DISTINCT IFNULL (brithday,‘出生不详’) AS ‘出生日期’ FROM student
SELECT
stuid AS id,
NAME stuName,
sex AS ‘性别’
FROM
student
– 三、where比较运算符
– 1.查询学号为4的学生信息
SELECT * FROM student WHERE stuid=4

SELECT * FROM student WHERE brithday>‘1995-07-13’

– 2.查询所有女生的学员信息
SELECT * FROM student WHERE sex=‘女’
SELECT stuid,NAME,sex FROM student WHERE sex = ‘女’

SELECT * FROM student WHERE sex=‘男’ AND birthday<=‘1995-07-16’

SELECT * FROM student WHERE cid =1 AND sex = ‘女’
– 四、其他比较

– 1.BETWEEN AND(包括临界值)

– 1)查询在1995-07-13到199student5-07-15之间出生的学生信息、
SELECT * FROM student WHERE birthday>=‘1995-07-13’ AND birthday<=‘1995-07-15’
SELECT * FROM student WHERE birthday BETWEEN ‘1995-07-13’ AND ‘1995-07-15’
– 2 IN 查询男生和女生的信息
SELECT stuid,NAME,sex FROM student WHERE sex=‘男’ OR sex=‘女’
SELECT stuid,NAME,sex FROM student WHERE sex IN (‘男’,‘女’)

查询班级编号为1,2,3班所有学生的信息
SELECT * FROM student WHERE cid IN(1,2,3)

– 查看所有电话为NULL的信息
SELECT * FROM student WHERE phone IS NULL

– 查询班级编号是1或者是2的学员信息
SELECT * FROM student WHERE cid IN(1,2)

– 查询班级编号不是1或者不是2的学员信息
SELECT * FROM student WHERE cid NOT IN(1,2)

– 3 LIKE _代表只能匹配单个字符(%)
SELECT * FROM student WHERE NAME LIKE ‘张%’

SELECT * FROM student WHERE NAME LIKE ‘%飞鸿’

查询姓名包含字符“a”的学生信息
SELECT * FROM student WHERE NAME LIKE ‘%a%’

– 查询姓张的学员信息
SELECT * FROM student WHERE NAME LIKE ‘张%’

查询姓名为两个字包含字符"张"的学生信息
SELECT * FROM student WHERE NAME LIKE ‘张_’

– 4 NULL
– 查询所有电话为NULL的学生信息
SELECT * FROM student WHERE phone IS NULL

逻辑运算
– 1 AND
– 查询成绩大于等于70并且科目编号是2的学生成绩,学生编号,科目编号
SELECT stuid,subid,socre FROM socre WHERE socre>=70 AND subid=2

– 课上练习2
– 1.查询工资大于等于5000的员工信息
SELECT * FROM employee WHERE salary>=5000

– 2.查询工资在3000-5000之间的员工信息
SELECT * FROM employee WHERE salary BETWEEN 3000 AND 5000

SELECT * FROM employee WHERE salary>=3000 AND salary<=5000

– 3.查询工资是3000,5000,8000的员工信息
SELECT * FROM employee WHERE salary IN(3000,5000,8000)

SELECT * FROM employee WHERE salary=3000 OR salary=5000 OR salary=8000

– 4.选择在2016-02-01到2016-03-01之间入职的员工姓名,职位,入职时间
SELECT ename,job,hiredate FROM employee WHERE hiredate BETWEEN ‘2016-02-01’ AND ‘2016-03-01’

– 5.查询没有上级领导的员工信息
SELECT * FROM employee WHERE manager IS NULL

– 6.查询姓王的员工姓名,职位,电话
SELECT ename,job,phone FROM employee WHERE ename LIKE ‘王%’

– 课上练习3
– 1.查询工资大于等于7000,职位是经理的员工信息
SELECT * FROM employee WHERE salary>=7000 AND job IS ‘经理’

– 2.查询工资大于6000或者小于3000的员工信息
SELECT * FROM employee WHERE salary<3000 OR salary>6000

– 3.查询2016-06-02入职的黄姓、王姓和吴姓员工信息
SELECT * FROM employee WHERE hiredate=‘2016-06-02’ AND (ename LIKE ‘王%’
OR ename LIKE’黄%’ OR ename LIKE’吴%’)

– 4.查询工资是3000,5000,8000同时是以李字开头的员工信息
SELECT * FROM employee WHERE salary IN(3000,5000,8000) AND ename LIKE ‘李%’

– 课上练习1
– 1. 查询所有员工的信息
USE employeemanager
SELECT * FROM employee

– 2.查询所有员工的姓名及对应的工资
SELECT ename,salary FROM employee

– 3.过滤员工表中重复数据
SELECT DISTINCT job FROM employee

– 4 查询出每位员工的工资,并在这个基础上为每位员工加1块钱
SELECT eid,salary+1 FROM employee

– 5 使用别名表示员工姓名,职位,电话,工资
SELECT
ename AS ‘姓名’,
job AS ‘职位’,
phone AS ‘电话’,
salary AS ‘工资’
FROM employee

– 课上练习2
– 1.查询工资大于等于5000的员工信息
SELECT * FROM employee WHERE salary>=5000

– 2.查询工资在3000-5000之间的员工信息
SELECT * FROM employee WHERE salary BETWEEN 3000 AND 5000

SELECT * FROM employee WHERE salary>=3000 AND salary<=5000

– 3.查询工资是3000,5000,8000的员工信息
SELECT * FROM employee WHERE salary IN(3000,5000,8000)

SELECT * FROM employee WHERE salary=3000 OR salary=5000 OR salary=8000

– 4.选择在2016-02-01到2016-03-01之间入职的员工姓名,职位,入职时间
SELECT ename,job,hiredate FROM employee WHERE hiredate BETWEEN ‘2016-02-01’ AND ‘2016-03-01’

– 5.查询没有上级领导的员工信息
SELECT * FROM employee WHERE manager IS NULL

– 6.查询姓王的员工姓名,职位,电话
SELECT ename,job,phone FROM employee WHERE ename LIKE ‘王%’

– 课上练习3
– 1.查询工资大于等于7000,职位是经理的员工信息
SELECT * FROM employee WHERE salary>=7000 AND job IS ‘经理’

– 2.查询工资大于6000或者小于3000的员工信息
SELECT * FROM employee WHERE salary<3000 OR salary>6000

– 3.查询2016-06-02入职的黄姓、王姓和吴姓员工信息
SELECT * FROM employee WHERE hiredate=‘2016-06-02’ AND (ename LIKE ‘王%’
OR ename LIKE’黄%’ OR ename LIKE’吴%’)

– 4.查询工资是3000,5000,8000同时是以李字开头的员工信息
SELECT * FROM employee WHERE salary IN(3000,5000,8000) AND ename LIKE ‘李%’

二.分组查询
1. 聚合函数

常用聚合函数有:
SUM 求和
AVG 求平均值
MAX 和 MIN 求最大值/最小值
COUNT 计数

2.SUM 求和

STUDENT 学生表中的 phone 列进行求和运算
解答:SELECT SUM(phone) FROM student

3. AVG 求平均值

SCORE 成绩表中科目编号为 1 的所有学生平均成绩
解答:SELECT AVG(score) FROM score WHERE subid = 1

4.MAX 和 MIN 求最大值/最小值

SCORE 成绩表中科目编号为 2 的学生最高成绩和最低成绩
解答:SELECT MAX(score), MIN(score) FROM score WHERE subid = 2

5.COUNT 计数

统计参加了科目编号为 1 考试学生数量
解答:SELECT COUNT(*) FROM score WHERE subid = 1

6.分组统计 GROUP BY

使用 DISTINCT 语句对重要数据进行过滤。
SELECT DISTINCT subid FROM score

SELECT <字段列表> [聚合函数]
FROM 表
[WHERE] 条件
GROUP BY <字段列表>
分组统计可根据 GROUP BY 所指定的字段进行合并,全并时指定字段完全相同的记录被合并为一条。
查询已经进行过考试的所有科目 ID。
解答:SELECT * FROM score GROUP BY subid

7.使用 HAVING 对分组过滤

SELECT <字段列表> [聚合函数]
FROM 表
[WHERE] 条件
GROUP BY <字段列表>
HAVING 条件

案例:获取平均成绩大于等于 70 的各科目的平均成绩
解答:SELECT *, AVG(score) FROM score GROUP BY subid HAVING AVG(score) >= 70
练习
– (三)针对学生课程数据库查询
– (1)查询全体学生的学号与姓名。
SELECT sno,sname FROM student
– (2)查询全体学生的姓名、学号、所在系,并用别名显示出结果。
SELECT sname AS ‘姓名’,sno AS ‘学号’,dept AS ‘院系’ FROM student
– (3)查询全体学生的详细记录。
SELECT * FROM student
– (4)查全体学生的姓名及其出生年份。
SELECT sname,birth FROM student
– (5)查询学校中有哪些系。
SELECT DISTINCT dept FROM student
– (6)查询选修了课程的学生学号。
SELECT sno,sname,dept
FROM student
WHERE dept
IS NOT NULL
– (7)查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT sname,age FROM student WHERE age <= 20
– (8)查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECT sname,dept,age FROM student WHERE age BETWEEN 20 AND 23
– (9)查询年龄不在20~23岁之间的学生姓名、系别和年龄。
SELECT sname,dept,age FROM student WHERE age
NOT IN(SELECT age FROM student WHERE age BETWEEN 20 AND 23)
(10)查询信息系、数学系和计算机科学系生的姓名和性别。
SELECT sname,sex FROM student WHERE dept IN (“信息系” ,“数学系” , “计算机系”)

(11)查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECT sname,sex FROM student WHERE dept NOT IN (“信息系” ,“数学系” , “计算机系”)
(12)查询所有姓刘学生的姓名、学号和性别。
SELECT sname,sno,sex FROM student WHERE sname LIKE ‘刘%’
(13)查询学号为2006016的学生的详细情况。(具体的学号值根据表中数据确定)
SELECT * FROM student WHERE sno = 2006016
(14)查询姓“欧阳”且全名为三个汉字的学生姓名
SELECT sname FROM student WHERE sname LIKE ‘欧阳_’

(15)查询名字中第2个字为“四”字的学生的姓名和学号
SELECT sname,sno FROM student WHERE sname LIKE ‘_四’

(16)查询所有不姓刘的学生姓名。
SELECT sname FROM student WHERE sname IN NOT
(SELECT sname FROM student WHERE sname LIKE ‘刘%’)

(17)查询语文课程的课程号和学分。
SELECT * FROM course WHERE cname=‘语文’
(18)查询缺少成绩的学生的学号和相应的课程号。
SELECT sno,cno FROM cs WHERE cj IS NULL
(19)查询计算机系年龄在20岁以下的学生姓名。
SELECT sname,dept,age FROM student WHERE dept=‘计算机系’ AND age<=20
(20)查询信息系、数学系和计算机系学生的姓名和性别。(使用多个条件表达式)
SELECT sname,sex FROM student
WHERE dept=‘信息系’ OR dept=‘数学系’ OR dept=‘计算机系’

(21)查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。(使用多个条件表达式)
SELECT sname,dept,age
FROM student
WHERE age BETWEEN 20 AND 23

(22)查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECT cno,sno,cj
FROM cs
WHERE cno=‘3’
ORDER BY cj DESC
(23)查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT * FROM student
ORDER BY dept,age DESC
(24)查询学生总人数。
SELECT COUNT(*) AS SUM FROM student
(25)查询选修了课程的学生人数。
SELECT COUNT(DISTINCT sno) AS SUM FROM student

(26)计算1号课程的学生平均成绩。
SELECT AVG(cj) AS AVG FROM cs WHERE cno=1
(27)查询选修1号课程的学生最高分数。
SELECT MAX(cj) AS MAX FROM cs WHERE cno=1

(28)求各个课程号及相应的选课人数。
SELECT cno,COUNT(sno) FROM cs
GROUP BY cno
(29)查询选修了3门以上课程的学生学号。
SELECT sno FROM cs
GROUP BY cno
HAVING COUNT(*)>3
(30)查询学生2006011选修课程的总学分。
SELECT SUM(cj) AS COUNT FROM cs
WHERE sno=2006011
(31)查询每个学生及其选修课程的情况。
SELECT sno,cs.cno,cname,cj
FROM course,cs
WHERE course.cno=cs.cno
(32)查询选修2号课程且成绩在90分以上的所有学生的学号、姓名
SELECT cs.sno sname
FROM cs,student
WHERE cs.sno=student.sno AND cj>90
GROUP BY cno
HAVING cno=2
(33)查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT student.sno,sname,cname,cj
FROM course,cs,student
WHERE student.sno = cs.sno AND cs.cno=course.cno

(34)查询与“王五”在同一个系学习的学生(分别子查询和连接查询)
SELECT sno,sname,dept FROM student
WHERE dept IN
(SELECT dept FROM student WHERE sname=‘张三’)
(35)查询选修了课程名为“信息系统”的学生学号和姓名
SELECT sno,sname FROM student
WHERE dept =
(SELECT dept FROM student WHERE dept=‘信息系’)
(36)查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄
SELECT sname,age FROM student
WHERE age<
(SELECT MIN(age) FROM student WHERE dept=‘信息系’)
(37)查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL谓词和集函数
– All
SELECT sname,age FROM student
WHERE age<ALL
(SELECT age FROM student WHERE dept=‘信息系’)
– 聚合函数
SELECT sname,age FROM student
WHERE age<
(SELECT MIN(age) FROM student WHERE dept=‘信息系’)

(38)查询所有选修了1号课程的学生姓名。(分别用子查询和连查询)
– 子查询
SELECT sname FROM student
WHERE sno IN
(SELECT sno FROM cs WHERE cno=1)
– 连接查询
– 1.交叉连接
SELECT student.sno,sname
FROM student,cs
WHERE student.sno=cs.sno AND cno=1
– 2.内连接
SELECT student.sno,sname
FROM student INNER JOIN cs
ON student.sno=cs.sno
WHERE cno=1
– 3.左外连接
SELECT student.sno AS ‘学号’,sname AS ‘姓名’
FROM student LEFT JOIN cs
ON student.sno=cs.sno
WHERE cno=1

(39)查询没有选修1号课程的学生姓名。
SELECT sno,sname
FROM student
WHERE sno IN
(SELECT sno FROM cs WHERE cno!=1)

SELECT DISTINCT student.sno,sname
FROM student INNER JOIN cs
ON student.sno=cs.sno
WHERE cno!=1
(40)查询数学系的学生及年龄不大于19岁的学生的信息。
SELECT * FROM student
WHERE dept=‘数学系’ AND age<=19
(41)查询选修了课程1或者选修了课程2的学生的信息。
SELECT *
FROM student INNER JOIN cs
ON student.sno=cs.sno
WHERE cno=1 OR cno=2

(42)查询既选修了课程1又选修了课程2的学生的信息。
SELECT DISTINCT *
FROM student,cs
WHERE student.sno=cs.sno AND
(cs.cno=1 OR cs.cno=2)

(43)通过查询求学号为2006011学生的总分和平均分。
SELECT SUM(cs.cj) AS SUM,AVG(cs.cj) AS AVG
FROM student INNER JOIN cs
ON student.sno=cs.sno
WHERE student.sno=‘2006011’
(44)求出每个系的学生数量
SELECT dept,COUNT(sno) AS SUM
FROM student
GROUP BY dept
(45)查询平均成绩大于85的学生学号及平均成绩。
SELECT sno,AVG(cj) AS AVG
FROM cs
GROUP BY sno
HAVING AVG>85
注:
1.在SELECT语句中,当WHERE子句,ORDER BY、GROUP BY和HAVING子句同时出现,执行顺序是那样的?
SELECT 属性列表
FROM 表名和视图列表
[WHERE 条件表达式 1]
[GROUP BY 属性名 1[HAVING 条件表达式 2]]
HAVING 条件
[ORDER BY 属性名 2[ASC|DESC]]
LIMIT [初始位置], 记录数
例子:
– 查询所有女学生的平均成绩,降序排序,及格 取前5名 (学生编号\学生姓名\学生平均成绩)
SELECT student.stuid,NAME,AVG(socre) AS score
FROM student INNER JOIN score
ON student.stuid=score.stuid
WHERE sex=‘女’
GROUP BY stuid
HAVING score>=60
ORDER BY score DESC
LIMIT 5
2.在SELECT语句中DISTINCT、ORDER BY、GROUP BY和HAVING子句的功能各是什么?
答:DISTINCT :查询唯一结果(去除重复)
ORDER BY:有序显示查询结果
GROUP BY:对查询结果进行分组显示
HAVING:筛选分组结果

三.多表查询

MySQL 中多表查询包括交叉连接、内连接、外连接三类。
3.1交叉连接(CROSS JOIN) 通过主外键连接

交叉连接会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积。
迪卡尔积
笛卡尔乘积是指在数学中两个集合 X 和 Y 的笛卡尓积(Cartesian product),又称直积,表示为 X × Y,第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。
假设集合 A={a, b},集合 B={0, 1, 2},则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0),
(b, 1), (b, 2)}。

字母
A
B
C
数字
1
2
3
字母表 数字表
如对以上两表进行迪卡尔积运算,则得到的结果将包含两个列:字母、数字;再将字母表中的每一条记录与数字表中的每一条记录进行连接组合。结果如下表所示:
字母 数字
A 1
B 1
C 1
A 2
B 2
C 2
A 3
B 3
C 3
迪卡尔积
交叉连接语法如下:
(1)
SELECT * FROM TABLE_A CROSS JOIN TABLE_B
[WHERE 条件]
(2)
SELECT * FROM TABLE_A JOIN TABLE_B
[WHERE 条件]
(3)
SELECT * FROM TABLE_A, TABLE_B
[WHERE 条件]

以上三种格式是等效的。
例如:获取姓名为 Jim 的学生的所有成绩
解答:
SELECT * FROM student CROSS JOIN score
WHERE student.stuid = score.stuid
AND student.name = ‘Jim’

获取姓名为 Jim 的学生的所有成绩,查询结果保留学生 ID、姓名、性别、科目、成绩
解答:
SELECT student.stuid, name, sex, subid, score
FROM student, score
WHERE student.stuid = score.stuid
AND student.name = ‘Jim’ 结果:

3.2 内连接

其语法如下:
SELECT * FROM TABLE_A INNER JOIN TABLE_B
ON 连接条件
[WHERE 条件]
相比交叉连接,内连接可以使用 ON 设置连接条件,连接条件通常是在两个表中具有相同意义的列。这样不但可以将连接条件与筛选条件分开,便 SQL 语句更加清晰,还提高了执行效率。
例如:获取姓名为 Jim 的学生的所有成绩,查询结果保留学生 ID、姓名、性别、科目、成绩解答:
SELECT student.stuid, name, sex, subid, score
FROM student INNER JOIN score – 内连接
ON student.stuid = score.stuid – 连接条件
WHERE student.name = ‘Jim’

3.3外连接

外连接与内连接不同,内连接时,根据连接条件只保留两个表中有对应数据的记录;而外连接时,当一个表中记录在另一个表中没有对应记录时,会生成一条与 NULL 值对应的记录。
如有以下两个表:
学生 ID 姓名
1 张三
2 李四
3 王五
学生 ID 成绩
1 90
2 88
4 78
外连接查询
学生 ID 姓名 成绩
1 张三 90
2 李四 88
3 王五 NULL

外连接根据数据保留表的不同,又分为左外连接和右外连接。
左外连接时,保留左表中的所有数据右外连接时,保留右表的所有数据。

3.3.1左外连接
左外连接使用 LEFT JOIN 连接两表,连接时左表为主表,左表中的每条记录必定出现在结果集中,而在右表中没有对应的记录,将以 NULL 值进行填充。其语法格式如下:
SELECT * FROM TABLE_A LEFT [OUTER] JOIN TABLE_B
ON 连接条件 [WHERE 条件]
例如:查询所有学生课程 ID 为 1 的考试成绩,查询结果保留学生 ID、姓名、性别、课程 ID、成绩解答:
SELECT student.stuid, name, sex, subid, score
FROM student LEFT JOIN score
ON student.stuid = score.stuid WHERE subid = 1 OR subid IS NULL;
3.3…2 右外连接

右外连接与与外连接相似,不同的是右表为主表,右表中的每条记录必定出现在结果集中,而在左

表中没有对应的记录,将以 NULL 值进行填充。其语法格式如下:
SELECT * FROM TABLE_A RIGHT [OUTER] JOIN TABLE_B
ON 连接条件 [WHERE 条件]
内外连接总结:

内外连接结果示意图

内连接时,得到的结果是公有数据集 C。
左外连接时,得到的是 A1+C。
右外连接时,得到的是 B1+C。
上机练习一:数据库-employeemanager
题目: 查询所有员工信息、部门名称以及工资等级,结果保留员工 ID、姓名、职位、部门名、工
资、工资等级,无工资对应等级的显示“超越一切的工资”,并按员工 ID 升序排列。
提示: 员工信息、部门信息、工资等级分别存放在三个表,需要对三个表进行连接查询。
参考答案:
SELECT eid, ename, job, dname, salary, IFNULL(gid,‘超越一切的工资’) from employee
left JOIN dept – 与部门表进行连接
on employee.did = dept.did
left JOIN salarygrade – 与工资等级表进行连接
on employee.salary >= salarygrade.lowsalary and employee.salary < salarygrade.highsalary
ORDER BY eid;

3.3.3全连接

完全连接左表和右表中所有行,当某行数据在另一个表中没有匹配时,则另一个表的选择列值为 NULL。MySQL 不支持全外连接。语法格式如下:
SELECT * FROM TABLE_A FULL [OUTER] JOIN TABLE_B
[ON 条件]
[WHERE 条件]

4.AS-表和字段的别名
SELECT COLUMN AS ALIAS_COLNAME
FROM TABLE_A AS ALIAS_A, TABLE_B AS ALIAS_B
WHERE ALIAS _A.COLUMN = ALIAS _B.COLUMN
例如:查询每个员工的姓名及直属领导的姓名
解析:所有的员工信息都存放在 employee 表中,其直属领导也是员工,信息也存放在 employee
表。要查询每个员工的姓名及直属领导的姓名,需要将 employee 与自己进行关联。
解答:
SELECT EMP.ename, IFNULL(MAG.ename, ‘公司高层’) AS ‘直属领导’
FROM employee AS EMP LEFT JOIN employee AS MAG ON EMP.manager = MAG.eid
练习:
USE studentmanager

– 查询姓名为jim考试信息
– 交叉连接
SELECT stu.stuid,NAME,subid,socre
FROM score sco,student stu
WHERE sco.stuid=stu.stuid AND NAME = ‘jim’
– 内连接
SELECT stu.stuid,NAME,sex,subid,socre
FROM student stu
INNER JOIN score sco
ON stu.stuid=sco.stuid AND NAME=‘jim’
– 查询所有的考试科目对应的成绩信息
– 交叉连接
SELECT score.subid,subname,socre
FROM score,subject
WHERE score.subid = subject.subid
– 内连接
SELECT score.subid,subname,socre
FROM score
INNER JOIN subject
ON score.stuid=subject.subid

– 查询所有考试科目的平均成绩并降序排序 若成绩相同则按照科目id升序排序 (subname,avg)
SELECT score.subid,subname,AVG(socre) AS AVG
FROM score
INNER JOIN subject
ON score.subid=subject.subid
GROUP BY score.subid,subname
ORDER BY AVG DESC,score.subid ASC

– 查询所有学生的信息(stuid,name,sex,cid,cname)
SELECT stuid,NAME,sex,student.cid,cname
FROM student,classinfo
WHERE student.cid=classinfo.cid

– 查询所有学生的考试成绩(学生编号、姓名、考试科目、考试成绩)
SELECT
stu.stuid,
NAME,
c.cid,
cname,
subname,
socre
FROM
student stu,
classinfo c,
SUBJECT sub,
score sco
WHERE
stu.cid = c.cid AND
stu.stuid = sco.stuid AND
sub.subid = sco.subid
– 使用内连接
SELECT
stu.stuid,
NAME,
c.cid,
cname,
subname,
socre
FROM
student stu
INNER JOIN classinfo c ON stu.cid = c.cid
INNER JOIN score sco ON stu.stuid = sco.stuid
INNER JOIN SUBJECT sub ON sub.subid = sco.subid

– 查询所有学生的考试成绩
– 使用外连接(有主表,副表的分别)
– 左外连接(主表在左)
SELECT student.stuid,NAME,subid,socre
FROM student
LEFT JOIN score
ON student.stuid= score.stuid

SELECT student.stuid,NAME,IFNULL (subid,“未选修”) subid,IFNULL (socre,“缺考”)score
FROM student
LEFT JOIN score
ON student.stuid= score.stuid
– 查询所有参加考试的学生信息
– 使用内连接
SELECT student.stuid,NAME,subid,socre
FROM student
INNER JOIN score
ON student.stuid = score.stuid

– 查询考试成绩及格的学生信息
– 使用内连接
SELECT score.stuid,NAME,subid,socre
FROM score
INNER JOIN student
ON score.stuid = student.stuid
WHERE socre>=60

– 查询所有学生信息(stuid,name,cid,cname)
SELECT stuid,NAME,student.cid,cname
FROM student,classinfo
WHERE student.cid=classinfo.cid

– 查询所有考试科目对应的成绩(subid,subname,socre)
SELECT score.subid,subname,socre
FROM score
INNER JOIN subject
ON score.subid=subject.subid

– 查询每个科目对应的平均成绩,并按照成绩降序排序(subid,subname,avg)
SELECT subject.subid,subname,subname,IFNULL(AVG(socre),0) AS AVG
FROM subject
LEFT JOIN score
ON subject.subid=score.subid
GROUP BY subject.subid,subname
ORDER BY AVG DESC

– 查询所有学生的平均成绩(学生编号、姓名、考试成绩)

SELECT student.stuid,NAME,IFNULL(AVG(socre),“缺考”) AS score
FROM score
RIGHT JOIN student
ON score.stuid=student.stuid
GROUP BY student.stuid

– 查询所有参加考试的学生成绩(学生编号、学生姓名、班级名称、科目id,科目名称、考试成绩)
– 1.按照学生考试成绩降序排序
– 2.若成绩相同按照学生编号升序排序
SELECT * FROM
(SELECT sco.stuid,NAME,cname,sub.subid,subname,socre
FROM score sco
INNER JOIN student stu
ON sco.stuid=stu.stuid
INNER JOIN classinfo cla
ON stu.cid=cla.cid
INNER JOIN subject sub
ON sub.subid=sco.subid) newtable
ORDER BY socre DESC,stuid ASC

– 查询所有参加考试学生的平均成绩(学生编号、姓名、平均成绩)并按照平均成绩降序排序
– 查询所有学生的平均成绩(学生编号、姓名、考试成绩)

SELECT student.stuid,NAME,IFNULL(AVG(socre),“缺考”) AS score
FROM score
RIGHT JOIN student
ON score.stuid=student.stuid
GROUP BY student.stuid

– 查询所有科目的总成绩(科目编号、科目名称、总成绩) 成绩升序排序
SELECT subject.subid,subname,IFNULL(SUM(socre),“缺考”) AS score
FROM subject
LEFT JOIN score
ON subject.subid=score.subid
GROUP BY subject.subid
ORDER BY score
– 查询所有参加考试学生的平均成绩前3名(学生编号、姓名、平均成绩),成绩降序排序
SELECT score.stuid,NAME,AVG(socre) AS score
FROM score
INNER JOIN student
ON score.stuid=student.stuid
GROUP BY score.stuid
HAVING score>=60
ORDER BY score DESC
LIMIT 3
– 查询所有学生的考试成绩(学生编号、学生姓名、性别、考试成绩)
SELECT student.stuid,NAME,sex,score.socre
WHERE student.stuid=score.stuid

– 使用内连接
SELECT student.stuid,NAME,subid,socre
FROM student
INNER JOIN score
ON student.stuid = score.stuid

– 查询所有科目的平均成绩,如没有平均成绩则为0,平均成绩降序排序
– 1.分别使用左 右连接实现
– 2.要求查询出 科目编号、科目名称、平均成绩
SELECT score.subid,subname,IFNULL(AVG(socre),0) AS AVG
FROM score LEFT JOIN SUBJECT
ON score.subid=subject.subid
GROUP BY score.subid
ORDER BY AVG DESC

四.子查询
1.子查询概念 (嵌套查询)
当一个查询需要用到另一个查询的结果时,在查询语句中可以出现多层嵌套查询。这种查询语句叫做子查询。
例如:查询比 Tom 小的所有学生的信息。
SELECT * FROM student
WHERE birthday>
(
SELECT birthday FROM student
WHERE name=‘Tom’
)
2.比较子查询
2.1单值比较子查询
使用子查询时,可以利用比较运算符>、>=、<、<=、=、!=、<>、!>、!<进行条件过滤
查询年龄最大的学生的信息
解答:
SELECT * FROM student
WHERE birthday=
(
– 年龄最大即生日最小
SELECT MIN(birthday) FROM student
)
2.2 IN 和 NOT IN
在嵌套子查询中,子查询的结果如果是一个集合,此时就不能直接使用比较运算符和结果进行比较了,判断的规则是属不属于子查询的结果集合,在 T_SQL 中 IN 表示属于子查询的结果集,NOT IN 表示不属于子查询的结果集。
例如:查询参加了考试的所有学生的信息解答:
SELECT * FROM student
WHERE stuid IN (
SELECT stuid FROM score
)
2.3EXISTS 和 NOT EXISTS
EXISTS 和 NOT EXISTS 也是判断是否存在,和 IN 类似,但效率要比 in 高。
使用 EXISTS 和 NOT EXISTS 时:
首先取外层查询表的第一个记录,拿这个记录与内层查询相关的属性值去参与内层查询的求解,若
内层查询的 WHERE 子句返回真值,则将这个记录放入结果集。然后再取外层查询表的下一条记录;重复上述过程,直到外层表处理完为止。
例如:查询参加了考试的所有学生的信息解答:
SELECT * FROM student
WHERE EXISTS (
SELECT * FROM score
WHERE student.stuid = score.stuid
)

IN/NOT IN 与 EXISTS/NOT EXISTS 的区别:
IN/NOT IN 先执行子查询,子查询返回的是一个集合,然后再将子查询的结果作为外层查询的条件进行过滤。
EXISTS/NOT EXISTS 先执行外层查询,再将外层查询的每一条记录作为条件进行子查询,子查询返回的只是返回一个 TRUE或 FALSE,因此一般情况下子查询中直接使用 SELECT 1提高效率。
练习:
USE employeemanager
– 1.查询工资大于陈乔恩的所有员工信息
SELECT * FROM employee
WHERE salary>(student
SELECT salary FROM employee WHERE ename=‘陈乔恩’)

– 2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
SELECT eid,ename,salary FROM employeecs
WHERE salary>(cs``student
SELECT AVG(salary) FROM employee)

– 3.查询和鹿晗相同部门的员工姓名和雇用日期
SELECT ename,hiredate student``studentFROM employee
WHERE did=(
SELECT did FROM employee WHERE ename=‘鹿晗’
)

– 4.查询工资比财务部的所有员工工资都高的员工信息
SELECT * FROM employee
WHERE salary>(
SELECT MAX(salary) FROM employee
INNER JOIN dept
ON employee.did=dept.did
WHERE dname=‘财务部’
)

– 上机练习2
– 1.查询部门工资总和大于15000的员工信息
– 内层查询:查询部门工资总和大于15000的部门ID
– 外层查询:根据部门ID查询部门的所有员工信息
SELECT * FROM employee
WHERE did IN(
SELECT did FROM employee
GROUP BY did
HAVING SUM(salary)>15000)

– 2.查询部门所有人工资都大于等于3000的部门信息
SELECT * FROM dept
WHERE did IN(
SELECT did FROM employee
GROUP BY did
HAVING MIN(salary)>=3000
)

– 3.使用EXISTS查询所有“2016-02-02”有员工入职的部门信息
SELECT * FROM dept
WHERE EXISTS(
SELECT 1 FROM employee WHERE dept.did=employee.did AND hiredate=‘2016-02-02’
)

– 4.查询管理者是甘婷婷的员工姓名和工资
SELECT ename,salary FROM employee
WHERE manager=(
SELECT eid FROM employee WHERE ename=‘甘婷婷’
)

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值