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