Day SQL查询

一、SQL语法补充

1.条件语句的写法

在SQL中可以通过 where 条件语句来对操作对象进行筛选 - 筛选

a. 比较运算符： =, <>, >, <, >=, <=

注意: 判断一个字段的值是否为空不用使用=和<>, 而是使用is null 和 is not null

SELECT addr FROM t_student;
SELECT stuname FROM t_student WHERE addr is NULL;    -- 判断是否为NULL（空）
SELECT stuname FROM t_student WHERE addr='';     -- 判断是否是空串

b. 逻辑运算符: and, or, not

c. where 字段名 between 值1 and 值2

筛选指定的字段的值在值1和值2之间

SELECT stuname,birth FROM t_student WHERE birth BETWEEN '1990-1-1' AND '1999-12-31'; 

d. where 字段名 in 集合

筛选出字段值是集合中的元素;(集合是使用括号括起来里面多个值)

SELECT * FROM t_student WHERE stuname in ('小花', '小明', '路飞'); 

e. like操作

SELECT * FROM t_student WHERE stuname like ('王%%'); -- 选出姓王的

2.数据类型

• varchar(size): 不定长字符串，size决定的是最大长度
• char(size): 定长字符
• text: 不限长度(最大是255个字符)
• int/tinyint(-128~127)
• FLOAT(size,d)/DOUBLE(size,d) - 这儿的size和d的值都有约束效果
• bit: 只有0和1两个值
• date/datetime/time: 值可以是时间函数的结果，也可以时间字符串；计算或者是比较的时候内部是按时间处理的

3.去重

SELECT DISTINCT addr FROM t_student;  

二、添加约束

1.创建表的时候添加约束

CREATE TABLE if not EXISTS t_college(
collid int, 
collname VARCHAR(20) NOT NULL,   -- 创建表的时候添加约束
website VARCHAR(1024), 
intro VARCHAR(200),
PRIMARY KEY(collid)
);

2.通过添加约束索引的方式添加约束

a.添加约束

alter table 表名 add constraint 索引名 约束 (字段名);
说明: 索引名 - 自己随便命名，用来指向当前添加的约束； 约束 - 需要添加的约束(支持唯一约束、主键约束和外键约束)

ALTER TABLE t_college ADD CONSTRAINT con_website UNIQUE (website);  

给t_college表中的website添加unique约束，约束索引为con_website

b.删除约束

alter table 表名 drop index 约束索引;

ALTER TABLE t_college DROP index con_website;

三、外键和E.R图

1.什么是外键：

表中的某个字段的值是根据其他表中主键的值来确定的。那么这个字段就是外键

1.1 对应关系

多对一的外键的添加： 将外键添加到多的一方对应的表中

一对一的外键的添加： 将外键随便添加到哪一方，同时添加值唯一约束
多对多的外键的添加： 关系型数据库中，两张表没法实现多多的关系，需要一个中间表。(中间表有两个外键分别参照多多的两个表的主键)

1.2 怎么添加外键：

a.添加外键对应的字段

alter TABLE tb_student add COLUMN collid int;

b.给设计好的外键对应的字段添加外键约束
alter table 表1 add constraint 索引名 foreign key (字段1) references 表2 (字段2);
将表1中的字段1设置为外键，并且让这个外键的值参照表2中的字段2

ALTER TABLE tb_student ADD CONSTRAINT fk_collid_stu 
FOREIGN KEY (collid) 
REFERENCES  tb_college (collid);

c.删除外键约束
alter table 表名 drop foreign key 外键索引名; - 可以删除外键约束，但是外键索引还存在；需要额外的把索引删掉
注意: 删除外键约束的时候直接删除约束的索引无效，必须先将约束删掉，然后再删除索引.

ALTER TABLE tb_student DROP FOREIGN KEY fk_collid_stu;
ALTER TABLE tb_student DROP INDEX fk_collid_stu; 

d. 多对多的外键约束

多对多的时候需要创建中间表分别来关联两个两个表。

-- 添加学生的外键约束 
ALTER TABLE tb_score ADD CONSTRAINT fk_stuid_score 
FOREIGN KEY (stuid) 
REFERENCES tb_student (stuid);
-- 添加课程的外键约束
ALTER TABLE tb_score ADD CONSTRAINT fk_couid_score 
FOREIGN KEY (couid) 
REFERENCES tb_course (couid);

四、高级查询操作

1.聚合：

max()/min()/sum()/avg()/count()
SELECT 聚合函数(字段) FROM 表名 WHERE 条件;

按条件多表查询指定字段数据，然后将查询结果做相应的聚合运算。聚合运算的结果是最后结果

SELECT mark FROM tb_score;    -- 获取tb_score表中的所有分数值
SELECT max(mark) as maxmark FROM tb_score;      -- 获取tb_score表中所有分数的最大值
SELECT min(mark) FROM tb_score; 
SELECT sum(mark) FROM tb_score; 
SELECT avg(mark) FROM tb_score WHERE scoreid>=123;   -- 计算平均值的时候如果参与运算的对象的值为NULL,那么这个数据不会参与计算
SELECT count(mark) FROM tb_score WHERE mark>=90;

2.分组

SELECT 字段操作 FROM 表名 WHERE 条件 GROUP BY(字段2);
将指定表中满足条件的记录按照字段2的进行分组(值是一样的在一个组里面), 然后再讲每个分组作为整体按照指定字段进行指定聚合操作

-- 求每个学生的平均成绩
SELECT stuid,avg(mark) FROM tb_score WHERE scoreid <= 115 GROUP BY(stuid) ;

注意:a.字段操作的位置除了分组字段不用聚合，其他字段都必须聚合 b.分组的时候where要放到分组前对需要分组的数据进行筛选

having: 分组的时候，在分组后用having代替where来对分组后的数据进行筛选

-- 获取平均分数大于60分的学生的id
SELECT stuid,avg(mark) FROM tb_score GROUP BY(stuid) HAVING avg(mark)>60;

3.子查询:

将一个查询操作的结果作为另外一个查询的数据源

-- 在tb_score表中获取成绩是大于90分的学生的id
SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL;   

获取成绩大于90分的学生的名字

SELECT stuname FROM tb_student WHERE stuid in 
(SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL);  
SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is  not NULL;

将一个查询的结果作为查询对象提供给另外一个查询。但是第一个查询结果需要重命名

SELECT mark FROM (SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is  not NULL) as t2;