数据库笔记二

UNION 的语法：

union合并多个查询的结果，

1、UNION 的语法如下：

（SQL语句的查询条件可以不同，但查询结果必须相同）

[SQL 语句 1]

UNION

[SQL 语句 2]

2、UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

当使用union 时，mysql 会把结果集中重复的记录删掉。

而使用union all ，mysql 会把所有的记录返回，且效率高于union

-- union合并
-- UNION 的语法
-- 使用union 时,会把结果集中重复的记录删掉
SELECT * FROM student WHERE age>=19 
UNION
SELECT * FROM student WHERE gender ='男'
-- UNION ALL 的语法
-- 使用union all,会把所有的记录返回，且效率高于union .
SELECT * FROM student WHERE age>=19 
UNION ALL
SELECT * FROM student WHERE gender ='男'

排序

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC

asc代表的是升序，desc代表的是降序，如果不写，默认是升序

order by子句中可以支持单个字段、多个字段

多个排序条件时，逐一执行排序条件

什么排序条件都没有时，默认为主键的升序排序。

-- 排序
-- 升序
SELECT * FROM student ORDER BY hight ASC
-- 降序
SELECT * FROM student ORDER BY age DESC
-- 加判断条件的排序
SELECT * FROM student WHERE hight>=1.80 ORDER BY age DESC
-- 多个排序条件
SELECT * FROM student ORDER BY age DESC,hight ASC
-- 什么排序条件都没有时，默认为主键的升序排序
SELECT * FROM student

数量限制

limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)

limit ①,② ①为开始位置(开始位置为0)，②为查询的数量

SELECT * FROM table LIMIT offset rows;

SELECT * from table LIMIT 0,5;

这种使用只在MYSQL中可用，在sqlsever中使用会报错。

-- 数量限制
-- limit 1,2   1为开始位置(开始位置为0),2为查询的数量.
SELECT * FROM student LIMIT 0,2
SELECT * FROM student LIMIT 2,4

分组查询

将某类数据分到一个组中进行处理。

语法：

select 分组函数，列（要求出现在group by的后面）

from 表

[where 筛选条件]

group by 分组的列表

[having 分组后的筛选]

[order by 子句]

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

分组查询中的筛选条件分为两类:

                             数据源                  源位置                    关键字

分组前筛选 ：       原始表           group by子句的前面       where ， 对原始表中的数据进行过滤。

分组后筛选： 分组后的结果集   group by的后面               having      对分组后的结果表中的数据进行过滤。

SELECT * FROM student LIMIT 0,2
SELECT * FROM student LIMIT 2,4


SELECT COUNT(*),gender FROM student GROUP BY gender
SELECT COUNT(*),hight  FROM student SELECT SUM(stu_score),stu_num FROM student GROUP BY stu_numWHERE hight>1.65 GROUP BY hight 
SELECT COUNT(*),age FROM student  GROUP BY age HAVING COUNT(*)>1
SELECT COUNT(*),DATE_FORMAT(birth,'%M') FROM student GROUP BY DATE_FORMAT(birth,'%M')

多表设计_关联查询

数据库设计范式（范例）

第一范式：

(每列保持原子性，列不可再分解)

/第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。/

例如：（姓名，班级，电话，QQ号）其中每个列都不可再分解

第二范式：

就要有主键，要求其他字段都依赖于某个列（主键列）

/没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。因为如果不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。/

第三范式：

不同的信息放在不同的表中，如果两张表有关系的话，只需要要在一张表里放置另一张表的主键进行关联。

/确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含已在其它表中包含的非主关键字信息。

修改表 的语法

ALTER TABLE 表名 ADD 要加的列名 （元素类型）

要加的列名 称为外键，外键必须是与另一个表的主键关联，其数据类型一致。

-- 外键
ALTER TABLE player ADD team INT
​
-- 外键约束
alter table student add constraint
（fk_学生表_选课表_on_学生表里添加的列名）约束名
foreign key(学生表里添加的列名) references 选课表(其主键)

表与表的关系：多对一关系，也可以为一对多。

添加外键约束，如果没有添加约束，数据之间，没有约束，就可以随意操作。

为外键添加约束后，就不能随便操作，要保持数据的完整性。

-- 外键约束
ALTER TABLE player ADD CONSTRAINT fk_player_team_on_qiudui FOREIGN KEY(qiudui) REFERENCES team(ID)

外键：引用另外一个数据表的某条记录。

外键列类型与主键列类型保持一致

数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键

（foreign key）建立起来的。

create table student(

id int not null auto_increment primary key,

num int,

name varchar(10)

majorid int,

CONSTRAINT 约束名 foreign key(majorid ) references major(id)

);

约束名规则:

例：FK_ForeignTable_PrimaryTable_On_ForeignColumn

CREATE TABLE student_course(
id INT PRIMARY KEY AUTO_INCREMENT, -- student_course表的主键
student_ID INT,-- 外键1在主表的列名
course_num INT,-- 外键2在主表的列名
-- fk_student_course_student_on_student_ID 
-- fk_主表名_副表名_on_主表中加的列名 
-- student(ID)：副表名（其主键）
-- 外键1的约束
CONSTRAINT fk_student_course_student_on_student_ID FOREIGN KEY(student_ID) REFERENCES student(ID),
-- 外键2约束
CONSTRAINT fk_student_course_course_on_course_num FOREIGN KEY(course_num) REFERENCES course(num)
)

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

内连接

只把满足条件的筛选出来

语法1：

Select 结果 from 表1，表2 where 表1.column1 = 表2.column2

语法2：

Select 结果 from 表1 INNER JOIN 表2 ON 表1.column1 = 表2.column2

SELECT *FROM student,zhuanye 


SELECT s.ID,
s.Name,
s.age,
z.kecheng 
FROM student s INNER JOIN zhuanye z ON s.xuanke=z.bainhao WHERE kecheng='教科'

外连接

左外连接

语法：select 结果 from

表1 left join 表2 on

表1.column1 = 表2.column2

右外连接

语法：select 结果 from

表1 right join 表2 on

表1.column1 = 表2.column2

-- 外连接
-- 左外连接
SELECT s.ID,
s.Name,
s.age,
z.kecheng 
FROM student s LEFT JOIN zhuanye z ON s.xuanke=z.bainhao
-- 右外连接
SELECT s.ID,
s.Name,
s.age,
z.kecheng 
FROM student s RIGHT JOIN zhuanye z ON s.xuanke=z.bainhao
-- 统计每个专业的学生
SELECT COUNT(s.ID),z.kecheng  FROM student s RIGHT JOIN zhuanye z ON s.xuanke=z.bainhao
GROUP BY z.bainhao

多表的关联查询 

-- 多表的关联查询
-- 学号 姓名 专业 选课（将选课放在同一栏里）
SELECT 
s.id,
s.name,
z.kecheng,
GROUP_CONCAT(c.ke) xuanke 
FROM student s INNER JOIN zhuanye z ON s.xuanke=z.bainhao
		INNER JOIN student_course sc ON s.id=sc.student_id
		INNER JOIN course c ON c.num=sc.course_num
	GROUP BY s.id,s.name,z.kecheng

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或 外查询.

分类： 按子查询出现的位置：

from后面：支持表子查询

表子查询（结果集一般为多行多列）

where：支持标量子查询，列子查询

按功能、结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

-- 子查询（嵌套查询）
-- 标量子查询（结果集只有一行一列） 查询最高的学生
SELECT NAME,hight FROM student WHERE hight=(SELECT MAX(hight) FROM student)
-- 列子查询（结果集只有一列多行） 查询年龄在17-19的学生
SELECT NAME,age,hight FROM student WHERE age IN (SELECT age FROM student WHERE age>16 AND age<20)
-- 表子查询（结果集一般为多行多列）查询姓名重复的学生
SELECT NAME,t.c FROM (SELECT NAME,COUNT(*) c FROM student GROUP BY NAME)AS t WHERE t.c>1

-- 查询大于平均身高的学生
-- 里查询
SELECT AVG(hight) FROM student 
-- 外
SELECT * FROM student WHERE hight>(SELECT AVG(hight) FROM student)

-- 查询与龚儿同专业的学生
-- 里查询
SELECT xuanke FROM student WHERE NAME='肖彤'
-- 外
SELECT * FROM student WHERE xuanke=(SELECT xuanke FROM student WHERE NAME='肖彤') AND NAME!='肖彤'

-- 查询每个专业中身高最高的学生信息 
-- 里查询
SELECT xuanke,MAX(hight) FROM student GROUP BY xuanke 
-- 外
-- 不完善
SELECT * FROM student AS s,
(SELECT xuanke,MAX(hight)h FROM student GROUP BY xuanke )AS t 
WHERE s.hight=t.h AND s.xuanke=t.xuanke
-- 完善
SELECT * FROM student AS s
INNER JOIN(SELECT xuanke,MAX(hight)h FROM student GROUP BY xuanke )AS t 
ON s.hight=t.h AND s.xuanke=t.xuanke