SQL语句-2

第三章 Trigger

# 创建单个触发器
CREATE trigger trigger_name trigger_time[before/after] trigger_event[insert/update/delete]
ON table_name FOR EACH ROW trigger_stmt
/*
eg:
    create trigger ins_sum before insert
    on table_name 
    for each row set @sun=@sum+NEW.table_name
*/
# 创建有多个执行语句的触发器
CREATE TRIGGER trigger_name trigger_time[before/after] trigger_event[INSERT/UPDATE/DELETE]
ON table_name 
FOR EACH ROW 
    BEGIN
        语句执行列表
    END

# 查看触发器
SHOW TRIGGERS

# 在trigger表中查看触发器信息
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='<trigger_name>';

# 删除触发器
DROP TRIGGER [schema_name.]trigger_name

第四章 Groupby&Having

SELECT * FROM score
--属性取值相同的元素被分到一个组中
--然后对分组过后的进行操作
--统计每一个学生的平均分数
SELECT stu_id ,avg(stu_score) AS avg_stu_score
FROM score
GROUP BY stu_id

--统计每一科的平均分
SELECT class_id ,avg(stu_score) AS avg_class_score
FROM score
GROUP BY class_id

--统计每一科及格的人数
SELECT class_id ,count(stu_score) AS class_score60_count
FROM score
WHERE stu_score>=60
GROUP BY class_id

--统计学生平均分数在75以上的学科
SELECT class_id ,avg(stu_score) AS avg_class_score
FROM score
GROUP BY class_id
HAVING avg(stu_score)>=75

--统计学生平均分数在75以上的学生
SELECT stu_id ,avg(stu_score) AS avg_class_score
FROM score
GROUP by stu_id
HAVING avg(stu_score)>=75

--统计及格人数在4个的学科
SELECT class_id,count(stu_score) AS avg_class_score
FROM score
WHERE stu_score>=60
GROUP BY class_id
HAVING count(stu_score)>=4

--统计有不及格学科的学生以及不及格的科目总数
SELECT stu_id,count(stu_score) AS num
FROM score
WHERE stu_score<60
GROUP BY stu_id

--统计学科每一科最高分
SELECT class_id,max(stu_score) AS max_score 
FROM score 
GROUP BY class_id

--统计每一个学生最高的分数
SELECT stu_id,max(stu_score) AS max_score 
FROM score 
GROUP BY stu_id

--统计平均最高分学生的姓名
SELECT student.stu_id AS 学号,stu_name AS 姓名, avg(stu_score) AS 平均分
FROM student,score
WHERE student.stu_id=score.stu_id
GROUP BY student.stu_id,stu_name
HAVING AVG(stu_score)>=all(select AVG(stu_score) FROM score group by stu_id)

--统计学科每一科最高分和对应的学生好
SELECT t.class_id AS 学科号码,class.class_name AS 学科名称,score.stu_id AS 学生学号 ,student.stu_name AS 学生姓名,score.stu_score AS 最高分
FROM (SELECT class_id, max(stu_score) AS m FROM score group by class_id) AS t ,score LEFT OUTER JOIN student ON score.stu_id=student.stu_id,class
WHERE t.class_id = score.class_id AND t.m = score.stu_score AND t.class_id=class.class_id

-- GROUP_CONCAT() 表示每个分组各个字段的值显示出来
-- HAVING XXX 表示过滤分组
-- WITH ROLLUP 相关字段统计记录数量
-- LIMIT <num> 限制显示多少条记录










					

第五章 Select

go
SELECT * FROM student
--as 的使用方法 防止不同关系的属性在相同的情况下，发生冲突
SELECT stu_name AS Name 
FROM student

--  as 为字段和表名 取别名
go
SELECT T.stu_id AS ID,T.stu_name AS Name,C.class_name AS CName,S.stu_score AS SCore
FROM student AS T,class AS C,score AS S
WHERE T.stu_id=S.stu_id AND C.class_id=S.class_id  AND S.stu_score >=60
ORDER by S.stu_score desc--降序使用desc，升序asc

-- 内连接 [from] inner join             on[代替where]   
-- right[left] out join 右[左]连接
SELECT T.stu_id AS ID,T.stu_name AS Name,C.class_name AS CName,S.stu_score AS SCore
FROM student AS T INNER JOIN class AS C,score AS S
ON T.stu_id=S.stu_id AND C.class_id=S.class_id  AND S.stu_score >=60
ORDER BY S.stu_score DESC--降序使用desc，升序asc

go
/*
%:任意字符
_:一个字符
*/
SELECT  *
FROM student 
WHERE stu_name LIKE 'wang%' 


# union 保证选出的字段名类型要相等
go
SELECT stu_id 
FROM student
UNION   -- 并集: union all 不去除重复值   union 去除重复值
SELECT stu_id 
FROM score

go
SELECT stu_id 
FROM student
intersect   -- 交集
SELECT stu_id 
FROM score

go
SELECT stu_id 
FROM student
except   -- 差集
select stu_id 
from score

go
SELECT avg(stu_age) AS avg_age
FROM student

go 
SELECT avg(stu_score) AS avg_score
FROM score
WHERE stu_id=14401
go 
SELECT max(stu_score) --最大max，最小min
FROM score

SELECT sum(stu_score) 
FROM score


go 
SELECT count(stu_id) AS num_count
FROM score

go 
SELECT count(distinct stu_id) AS num_count --distinct 自动去除重复
FROM score

-- [not] in/ [not] between and / is [not] null /  and or /
SELECT xxx
FROM xxx
WHERE xxx IN (相关值)
ORDER BY xxx -- 默认升序asc  降序 desc

-- 不重复显示结果
SELECT DISTINCT xxx FROM table_name

-- 分组查询 [GROUP BY 字段] [HAVING <条件表达式>]
-- GROUP_CONCAT(xxx) as xxx 将分组每个字段显示出来

-- 某列的函数 avg count(* 空值也算一行  字段名  不计空值) max min sum
-- 子查询 any(任意一个子查询的值) all(所有子查询的值) 
-- 子查询 [not] exists(子查询的值是否存在)
-- 子查询 in 
-- 子查询 带比较符号查询

-- 正则表达式
...WHERE <某一字段> REGEXP '<正则表达式>'

第六章 Join

SELECT *
FROM score
JOIN student
    ON score.stu_id=student.stu_id


--左连接（右连接）
SELECT *
FROM score left outer
JOIN student
    ON score.stu_id=student.stu_id

--全连接（空值也会输出来）
SELECT *
FROM score full outer
JOIN student
    ON score.stu_id=student.stu_id

第七章 Index

--创建普通索引
CREATE TABLE book(
    bookid INT NOT NULL,
    bookname VARCHAR(255) NOT NULL,
    INDEX(bookid)
);

-- 查看表结构
SHOW CREATE TABLE book \G

-- 查看索引是否正在使用
explain select * from book where bookid=1000 \G

-- 创建唯一索引
CREATE TABLE book2(
    bookid2 INT NOT NULL,
    bookname2 VARCHAR(255) NOT NULL,
    UNIQUE INDEX(bookid2)
);

--创建单列索引
CREATE TABLE book3(
    bookid3 INT NOT NULL,
    bookname3 VARCHAR(255) NOT NULL,
    INDEX SingleIdx(bookid3)  # 后面括号指定索引长度【索引名】
);

--创建组合索引
CREATE TABLE book4(
    bookid4 INT NOT NULL,
    bookname4 VARCHAR(255) NOT NULL,
    INDEX MultiIdx(bookid4,bookname4) # MultiIdx为组合索引名
);
--创建全文索引 必须是char varchar text类型 默认InnoDB索引不支持全文索引
--全文索引引擎更改为 ENGINE=MyISAM
CREATE TABLE book5(
    bookid5 INT NOT NULL,
    bookname5 VARCHAR(255) NOT NULL,
    FULLTEXT INDEX FULLTxtIdx(bookname5)
);

--在已经创建的表中添加索引
ALTER TABLE <表名> add <相关索引> create index index_name
    ON <table_name>(index_length)

--删除索引
ALTER TABLE <table_name> drop index <index_name> drop index <index_name>
    ON <table_name>