本文主要讲述了MySQL的基础的CRUD，以及视图和索引。
| SQL功能 | 操作符 | ||
|---|---|---|---|
| DDL | 数据定义 | CREATE ALTER DROP | |
| DML | 数据查询 | SELECT | |
| 数据更新 | INSERT UPDATE DELETE | ||
| DCL | 数据控制 | GRANT REVOKE |
3.1增删改查
3.1.1数据库
-- 【增】创建名为test的数据库，默认字符集设置为 utf8，排序规则设置为gb2312_chinese_ci，
CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 【删】删除数据库服务器中名为test的数据库，
DROP DATABASE test;
-- 【改】使用SQL语句，修改数据库test的字符集设置为uft8，排序规则设置为utf8_bin。
ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;
-- 【查】
SHOW DATABASES;
show tables；
SHOW CREATE DATABASE test;
SHOW CREATE TABLE dept;
-- 使用数据库
use database;3.1.2表及约束
Sno好像不加``这两个符号也行
-- 【删】 是否存在该表，存在则删除
DROP TABLE IF EXISTS `student`;
-- 【创】 创建表：学生有
CREATE TABLE `student` (
`Sno` char(8) NOT NULL PRIMARY KEY, -- 列级约束
列名1 类型(长度) [约束],
列名2 类型(长度) [约束],
foreign key(Sno) references 其他表(字段) -- 表级完整性约束
-- CONSTRAINT 约束名 PRIMARY KEY(列或者列的组合)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 【增】【删】【改】
ALTER TABLE <表名>
[修改选项]:
| ADD <列名> <类型> ------------------添加字段
| DROP <列名> ------------------------删除字段
| ALTER <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -----------修改字段默认值或者删除字段默认值
| CHANGE <旧列名> <新列名> <新列类型>--修改字段名称
| MODIFY <列名> <类型> ------------- --修改字段数据类型
| RENAME TO <新表名> } ----------------修改表名
| add check (字段 in (1,2,3,4))--------增加字段约束
| add foreign key(外键字段) references 主表(主键字段);
| drop foreign key 外键名;
-- 【查】
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table teacher;
# 查看数据库中的表
show tables;
-- FOR EXAMPLE
alter table Reservation add primary key(ISBN, loanNo, reservationDate);
alter table Reservation add foreign key(ISBN) references BookInfo(ISBN);
alter table Reservation add check (rstatus in (T,F));3.1.3数据
-- 【增】
insert into 表名(字段1,字段2..) values(值1,值2…);
insert into emp values ('001','wl','1971-01-23',1,'zsl','210003','12345668','2');
-- 【删】
delete from 表 where 条件;
//删除数据带条件指定数据，否则删除全表数据
-- 【改】
update 表 set 字段=值 where 条件;
//带条件修改指定数据，否则修改全表
-- 【查】
select * from 表名 where 列名 = xx order by xx desc limit 5,5;
-- order by排序
-- desc 从高到低 默认从低到高asc
-- limit 5 限制 只显示前5个
-- offset 偏移 5,5 表示limit=5 offset=5 即查询第6-10条
select * from course where came like 'DB\_%王%';
-- \_ '\'是转义字符 escape '/' 用escape可以自定义其他转义字符 %王% 就是含有王的就匹配
select eid from sal where income between 5000 and 6000; # 在..之间
select * from 表名 where 列名 = xx group by 字段 having 聚合函数条件;
-- where 语句最先执行
-- 【聚合函数】 count、sum、avg、max、min
-- 查询nv同学的总人数 & 平均年龄 count(distinct xx) 去重
select count(*), avg(sage), count(distinct sno) from st where sex='nv';
-- group by 子句可以将查询结果的行按一列或多列取值相等的原则进行分组
-- having 对聚集函数的结果值进行筛选。 有 group by 才有 having
select dno, count(sno) from st group by dno having count(sno) >= 3; -- 统计各系学生人数,>=3的显示
-- 【ALL关键字】
select position,salary from tb_salary where salary > all(select max(salary) from tb_salary where position='Java');
-- 【连接】
select stu.name as studentName, c.name as className
from tb_student stu
left join tb_class c
on stu.class_id=c.id;
-- 【UNION】操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。
# ！包括有成绩和无成绩的 查询平均成绩小于60分的同学的学生编号，姓名和平均成绩（保留两位小数）
select stu.s_id, stu.s_name, round(avg(s_score),2) 'avg_score'
from student stu, score sc
where stu.s_id = sc.s_id
group by stu.s_id
having avg(s_score) < 60
union
select s_id,s_name,0 'avg_score'
from student
where s_id not in (select distinct s_id from score);# 检索记录行符合条件的11-20条数据
select * from table where xxx="xxx" limit 10 offset 10;
# 实际使用中，把offset直接省略掉 -> limit 10,10;
# 利用子查询可以优化分页查询
# 查询10000条以后的100条数据 即10001-10100
select * from table where xxx="xxx" and
id >= (select id from table where xxx="xxx"limit 10000,1)
limit 100; limit之后的数字代表偏移量，offset代表返回记录的最大值，可以通俗的理解为，从table中取出第limit+1行到limit+offset+1行数据( MySQL 偏移值从0开始计算)。
集合运算
SQL语言：并运算UNION, 交运算INTERSECT, 差运算EXCEPT。
基本语法形式： 子查询{ Union [ALL] |Intersect [ALL]|Except [ALL] 子查询}
通常情况下自动删除重复元组：不带ALL。若要保留重复的元组，则要带 ALL。
假设子查询1的一个元组出现m次，子查询2的一个元组出现n次 ，则该元组在：
子查询1 Union ALL 子查询2，出现m + n次
子查询1 Union 子查询2，去掉重复的元组
子查询1 Intersect ALL 子查询2，出现min(m,n)次
子查询1 Intersect 子查询2，出现1次
子查询1 Except ALL 子查询2，出现max(0, m –n)次
子查询1 Except 子查询2，出现0次
参考链接
#求学过002号课的同学或学过003号课的同学学号
Select student_id From SC Where couse_id = ‘002’
UNION
Select student_id From SC Where couse_id = ‘003’;
#上述语句也可采用如下不用UNION的方式来进行
Select student_id From SC Where couse_id = ‘002’ OR C# = ‘003’;#求既学过002号课，又学过003号课的同学学号
Select student_id From SC Where course_id = ‘002’
INTERSECT
Select student_id SC Where course_id = ‘003’;
#上述语句也可采用如下不用INTERSECT的方式来进行
Select student_id From SC
Where C# = ‘002’ and
student_id IN (Select student_id From SC Where course_id = ‘003’);#假定所有学生都有选课，求没学过002号课程的学生学号
Select DISTINCT student_id From SC
EXCEPT
Select student_id SC Where course_id = ‘002’
#前述语句也可不用EXCEPT的方式来进行
Select DISTINCT student_id From SC SC1
Where not exists
(Select * From SC Where course_id = ‘002’ and course_id = SC1.student_id) ;3.1.4视图
使用视图的大部分情况是为了 保障数据安全性，提高查询效率。
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
参数说明：
OR REPLACE：表示替换已有视图；
ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：
MySQL 自动选择要使用的算法 ；merge合并；temptable临时表；
column_list：可选参数，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同；
select_statement：表示select语句；
[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内；
cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件；
local表示更新视图的时候，要满足该视图定义的一个条件即可。-- 【创】
CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;
CREATE VIEW v_order(pid,pname,price) AS SELECT * FROM user_order;
-- 【增】
INSERT INTO 视图名(列1，列2...) VALUES('值1','值2');
INSERT INTO v_order(pid,pname,price) VALUES('p010','柴油','34');
-- 【删】
drop VIEW 视图名;
drop VIEW v_order;
-- 【改】
视图本身不储存数据，对视图修改就是对基表的数据进行修改
基本不用视图修改数据
-- 【查】
select * from 视图名 where 条件
select * from v_order where pid = 'p010';3.1.5索引
聚簇索引 （一个文件中只能有一个)
非聚簇索引：可以有多个
索引类型分成下列几个：
- 普通索引（INDEX）：基本索引类型
- 唯一性索引（UNIQUE）：该列的所有值没有重复
- 主键（PRIMARY KEY）：一种唯一性索引，一个表只能有一个主键
- 全文索引（FLLTEXT）：只能在varchar或text类型上创建
-- 【增】
CREATE [UNIQUE][cluster] INDEX <索引名称> ON <表名> (<列名>[<次序>] [,<列名>[<次序>]])
CREATE INDEX 索引名称 ON 表名 (列名1 [asc|desc],列名2 [asc|desc],...)
# 在classes字段上添加名为uniq_classes的唯一索引，并以降序排列；
create unique index uniq_classes on student(classes desc);
create unique index scno on SC(sno asc, cno desc);
# 组合索引
create index idx_group on student(name,sex,grade);
-- 【删】
DROP INDEX 索引名 ON 表名
DROP INDEX PersonIndex On Person
alter table table_name drop index index_name ;
alter table table_name drop primary key ; #删除主键索引
-- 【改】
ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX索引名称(字段名称[(长度)][ASC|DESC]);
alter table emp add constraint un_tel unique(tel); # un_tel 是索引名称
# 重命名索引
alter index emp_name_idx rename to emp_idx;
-- 【查】
show index from 表名组合索引：
ALTER TABLE student ADD INDEX name_city_score (name,city,score);
实际上是创建了如下三个索引：
- name
- name city
- name city score
查询名字或者同时查询名字城市又或者查询名字城市分数时都可以使用组合索引，但是查询城市分数等就不能使用，这是因为遵循了最左匹配原则，必须从左开始进行匹配。
# 完整性规则：分为：域[列]完整性、实体完整性和参照完整性。
# 域[列]完整性 主要是对一列的数据进行约束
alter table emp add(constraint ch_ph check(tel like '[0-9]')); -- 电话只能为数字 ch_phe 是约束的名字
alter table emp drop constraint ch_ph; -- 删除约束
# 实体完整性 通过unique约束和primary key约束可以实现实体完整性。
alter table emp add constraint un_tel unique(tel);
# 参照完整性 实现方式是定义外键与主键
alter table emp add constraint sal_id foreign key(eid) references sal(eid); 3.1.6触发器
- new-新添加/修改之后的行 old-删除/修改之后的行
- 触发器不能动态使用SQL语句
- 删除表时，在表上建立的所有触发器一并删除
-- 【增】
CREATE TRIGGER <触发器名>
< BEFORE | AFTER > <INSERT | UPDATE | DELETE > ON <表名>
FOR EACH Row [{ FOLLOWS | PRECEDES }]
<触发器主体>
-- 触发器执行顺序： FOLLOWS 触发器在某个触发器之后执行，PRECEDES 之前执行
[例]
CREATE TRIGGER sumNum
before INSERT ON salary
FOR EACH Row
SET @sum = @sum + NEW.money;
-- 【删】
DROP TRIGGER 触发器名
DROP TRIGGER if exists double_salary;
-- 【改】
一般先删除再创建
-- 【查】
SHOW TRIGGERS;create trigger tri_fine # 创建
after delete # 条件
on Loan # 基于谁
for each row # 循环
begin
declare diffDay int default 0;
declare dueDay int default 0;
# 查询借了几天
select DateDiff(now( ),old.borrowDate) into diffDay;
# 查询能借几天
select term into dueDay from Class_ User where classNO = (
select classNo from Users where loanNo = old. loanNo
);
if diffDay - dueDay > 0 then
insert into Money ( loanNo , bookNo , amount , reason, billdate)
values (old.loanNo ,old.bookNo, (diffDay- dueDay) * 0.1,"过期罚款" ,now());
end if;
end
