数据库约束
NOT NULL : 指示某列不能存储 NULL 值。
UNIQUE : 保证某列的每行必须有唯一的值。
DEFAULT : 规定没有给列赋值时的默认值。
PRIMARY KEY : NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中 的一个特定的记录。
FOREIGN KEY : 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK : 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句对于整数类型的主键， 常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1
//创建课程表
create table student(
id int primary key auto_increment,//如果删除一条记录,那么再新增记录,删除的id不在利用,比如删了3,再增加就是4,不会重新利用id=3
sn int unique,
name varchar(20) default'unknow',
qq_mail varchar(20),
classes_id int,
//class_id指定当前表的列,classes是i关联到另外一张表的名字,id是关联到另外一张表的哪一列,此处的id必须是主键
foreign key (classes_id) references classes(id)
);
//创建课程表
CREATE TABLE course (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20)
);
//创建中间表--成绩表
-- 创建课程学生中间表：考试成绩表
CREATE TABLE score (
id INT PRIMARY KEY auto_increment,
score DECIMAL(3, 1),
student_id int,
course_id int,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
查询
聚合查询
- COUNT
//统计班级共有多少同学
select count(*) from student;
//统计班级收集的qq_mail有多少个,null不算
select count(qq_mail) from student;
- SUM
//统计数学成绩总成绩
select sum(math) from exam_result;
//统计数学成绩<60的总分,没有结果返回null
select sum(math) from exam_result where math < 60;
+-----------+
| sum(math) |
+-----------+
| NULL |
+-----------+
- AVG
//统计平均总分
select avg(chinese+math+english) 平均总分 from exam_result;
- MAX
//显示数学最高分
select max(math) from exam_result;
- MIN
//显示数学最低分
select min(math) from exam_result;
GROUP BY子句
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
select * from score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 1 | 70.5 | 1 | 1 |
| 2 | 98.5 | 1 | 3 |
| 3 | 33.0 | 1 | 5 |
| 4 | 98.0 | 1 | 6 |
| 5 | 60.0 | 2 | 1 |
| 6 | 59.5 | 2 | 5 |
| 7 | 33.0 | 3 | 1 |
| 8 | 68.0 | 3 | 3 |
| 9 | 99.0 | 3 | 5 |
| 10 | 67.0 | 4 | 1 |
| 11 | 23.0 | 4 | 3 |
| 12 | 56.0 | 4 | 5 |
| 13 | 72.0 | 4 | 6 |
| 14 | 81.0 | 5 | 1 |
| 15 | 37.0 | 5 | 5 |
| 16 | 56.0 | 6 | 2 |
| 17 | 43.0 | 6 | 4 |
| 18 | 79.0 | 6 | 6 |
| 19 | 80.0 | 7 | 2 |
| 20 | 92.0 | 7 | 6 |
+----+-------+------------+-----------+
//查询每个学生的学生id,最高成绩,最低成绩,平均成绩
select student_id,max(score),min(score),avg(score) from score group by student_id;
+------------+------------+------------+------------+
| student_id | max(score) | min(score) | avg(score) |
+------------+------------+------------+------------+
| 1 | 98.5 | 33.0 | 75.00000 |
| 2 | 60.0 | 59.5 | 59.75000 |
| 3 | 99.0 | 33.0 | 66.66667 |
| 4 | 72.0 | 23.0 | 54.50000 |
| 5 | 81.0 | 37.0 | 59.00000 |
| 6 | 79.0 | 43.0 | 59.33333 |
| 7 | 92.0 | 80.0 | 86.00000 |
+------------+------------+------------+------------+
HAVING
GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING
//查询平均成绩小于70的学生id,最高成绩,最低成绩,平均成绩
select student_id,max(score),min(score),avg(score) from score group by student_id having avg(score) < 70;
+------------+------------+------------+------------+
| student_id | max(score) | min(score) | avg(score) |
+------------+------------+------------+------------+
| 2 | 60.0 | 59.5 | 59.75000 |
| 3 | 99.0 | 33.0 | 66.66667 |
| 4 | 72.0 | 23.0 | 54.50000 |
| 5 | 81.0 | 37.0 | 59.00000 |
| 6 | 79.0 | 43.0 | 59.33333 |
+------------+------------+------------+------------+
联合查询
实际数据往往来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积
内链接
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
//查询许仙同学的成绩
select sco.score from student stu inner join score sco on stu.id = sco.student_id and stu.name = '许仙';
+-------+
| score |
+-------+
| 67.0 |
| 23.0 |
| 56.0 |
| 72.0 |
+-------+
//双表关联查询,插入时老外不想说话的成绩为空,查询结果没有老外不说话
select stu.sn, stu.name, sum(sco.score) from student stu join score sco on stu.id = sco.student_id group by sco.student_id;
+-------+-----------------+----------------+
| sn | name | sum(sco.score) |
+-------+-----------------+----------------+
| 9982 | 黑旋风李逵 | 300.0 |
| 835 | 菩提老祖 | 119.5 |
| 391 | 白素贞 | 200.0 |
| 31 | 许仙 | 218.0 |
| 54 | 不想毕业 | 118.0 |
| 51234 | 好好说话 | 178.0 |
| 83223 | tellme | 172.0 |
+-------+-----------------+----------------+
//三表关联查询,插入成绩表时"老外学中文"的为空,查询结果没有"老外学中文"
select * from student stu join score sco on stu.id = sco.student_id
-> join course cou on sco.course_id = cou.id
-> order by stu.id;
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+----+--------------------+
| id | sn | name | qq_mail | classes_id | id | score | student_id | course_id | id | name |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+----+--------------------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 | 3 | 计算机原理 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 | 1 | Java |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 | 5 | 高阶数学 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 | 6 | 英文 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 | 1 | Java |
| 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 | 5 | 高阶数学 |
| 3 | 391 | 白素贞 | NULL | 1 | 7 | 33.0 | 3 | 1 | 1 | Java |
| 3 | 391 | 白素贞 | NULL | 1 | 8 | 68.0 | 3 | 3 | 3 | 计算机原理 |
| 3 | 391 | 白素贞 | NULL | 1 | 9 | 99.0 | 3 | 5 | 5 | 高阶数学 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 | 5 | 高阶数学 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 | 6 | 英文 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 | 1 | Java |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 | 3 | 计算机原理 |
| 5 | 54 | 不想毕业 | NULL | 1 | 15 | 37.0 | 5 | 5 | 5 | 高阶数学 |
| 5 | 54 | 不想毕业 | NULL | 1 | 14 | 81.0 | 5 | 1 | 1 | Java |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 | 6 | 英文 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 | 2 | 中国传统文化 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 | 4 | 语文 |
| 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 | 6 | 英文 |
| 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 | 2 | 中国传统文化 |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+----+-------------
外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
// 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
// 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
//右外连接student完全显示,查询结果包含了"老外学中文"
select * from score sco right join student stu on stu.id=sco.student_id;
//左外连接student完全显示,查询结果不包含"老外学中文"
select * from score sco left join student stu on stu.id=sco.student_id;
自连接
自连接是指在同一张表连接自身进行查询
//使用join on 语句来进行自连接查询Java成绩小于计算机原理成绩的学生信息与成绩
SELECT
-> stu.*,
-> s1.score Java,
-> s2.score 计算机原理
-> FROM
-> score s1
-> JOIN score s2 ON s1.student_id = s2.student_id
-> JOIN student stu ON s1.student_id = stu.id
-> JOIN course c1 ON s1.course_id = c1.id
-> JOIN course c2 ON s2.course_id = c2.id
-> AND s1.score < s2.score
-> AND c1.NAME = 'Java'
-> AND c2.NAME = '计算机原理';
+----+------+-----------------+-----------------+------------+------+-----------------+
| id | sn | name | qq_mail | classes_id | Java | 计算机原理 |
+----+------+-----------------+-----------------+------------+------+-----------------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 70.5 | 98.5 |
| 3 | 391 | 白素贞 | NULL | 1 | 33.0 | 68.0 |
+----+------+-----------------+-----------------+------------+------+-----------------+
子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询
返回一行记录的子查询
//返回一行记录的子查询,查询与“不想毕业” 同学的同班同学
select * from student where classes_id = (select classes_id from student where name = '不想毕业');
//
多行子查询
- [not] in 关键字
//使用in,查询语文或英文课程的成绩信息
select * from score where course_id in (select id from course where name = '语文' or name = '英文');
//使用 not in ,查询语文或英文课程的成绩信息
select * from score where course_id not in (select id from course where name != '语文' and name != '英文');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 4 | 98.0 | 1 | 6 |
| 13 | 72.0 | 4 | 6 |
| 17 | 43.0 | 6 | 4 |
| 18 | 79.0 | 6 | 6 |
| 20 | 92.0 | 7 | 6 |
+----+-------+------------+-----------+
// 查询重复的分数
SELECT * FROM score WHERE( score, student_id, course_id ) IN ( SELECT score, student_id,
course_id FROM score GROUP BY score, student_id, course_id HAVING count( 0 ) > 1 );
- [not] exists关键字
//查询语文或英文课程的成绩信息
select * from score sco where exists (select sco.id from course cou
where (name='语文' or name='英文') and cou.id = sco.course_id);
合并查询
为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION
和UNION ALL时，前后查询的结果集中，字段需要一致。
- union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
//查询id小于3，或者名字为“英文”的课程：
union all
select * from course where id<3
union
select * from course where name='英文';
-union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行
案例：查询id小于3，或者名字为“Java”的课程
select * from course where id<3
union
select * from course where name='英文';
?? 服务器开发领域中,涉及一个重要的概念–请求(request),需要给请求编号,设计一种方案,保证请求的编号能够唯一?
自增主键是一个办法,但是这个办法在分布式场景下不合适,唯一id => 时间戳+主机编号+机房编号+随机因子
1108
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
