数据库二级基本操作资料
叁：
创建数据库（database）
CREATE DATABASE db_school
DEFAULT CHARACTER SET GB2312
DEFAULT COLLATE GB2312_chinese_ci;

use db_school;

show databases;

修改数据库
ALTER DATABASE db_school
DEFAULT CHARACTER SET GB2312
DEFAULT COLLATE GB2312_chinese_ci;

删除数据库
DROP DATABASE db_school;

创建表（table）
CREATE TABLE tb_student
(
studentNo CHAR(10) NOT NULL UNIQUE, (AUTO_INCREMENT)
studentName VARCHAR(20) NOT NULL,
sex CHAR(2),
birthday DATE,
native VARVHAR(10),
classNo CHAR(6)
)ENGINE=InnoDB;

查看表
SHOW TABLES FROM(或者IN) db_school;

查看数据表的基本结构
SHOW COLUMNS FROM tb_student
或者
DESC tb_student

查看数据表的详细结构
SHOW CREATE TABLE tb_student (\G)

修改表
①添加操作
ALTER TABLE db_school.tb_student
ADD COLUMN id INT NOT NULL UNIQUE AUTO_INCREMENT FIRST(AFTER 行名);

②修改操作
ALTER TABLE 语句提供了三个修改字段的字句（CHANGE、ALTER、MODIFY）
CHANGE：可以同时修改表中指定列的名称和数据类型
ALTER：可以修改或删除表中指定列的默认值
MODIFY：只会修改指定列的数据类型，而不会干涉它的列名

格式：
ALTER TABLE tb_name CHANGE 原字段名 新字段名 数据类型 [约束条件];
ALTER TABLE tb_name ALTER 字段名 SET|DROP DEFAULT;
ALTER TABLE tb_name MODIFY 字段名 数据类型 [约束条件] [FIRST|AFTER 已有字段]

③删除操作
ALTER TABLE tb_name DROP 字段名;

重命名表
ALTER TABLE 原表名 RENAME TO 新表名;

RENAME TABLE 原表名 TO 新表名;

删除表
DROP TABLE [IF EXISTS] 表名1[, 表名2];

肆：
数据查询
SELECT [ALL|DISTINCT|DISTINCTROW] <目标列表达式1>[, <目标列表达式2>]…
FROM <表名1或视图1>[, <表名2或视图2>]…
[WHERE<条件表达式>]
[GROUP BY <列名> [HAVING <条件表达式>] ]
[ORDER BY <列名> [ASC|DESC] ]
[LIMIT [m,]n];

单表查询
SELECT * FROM tb_student;

SELECT DISTINCT * FROM tb_student;

SELECT studentName ‘姓 名’, sex 性别
FROM tb_student;

SELECT *
FROM tb_student
WHERE [NOT] courseHour>=48;

SELECT *
FROM tb_student
WHERE native IN(‘北京’,‘天津’,‘上海’);

SELECT *
FROM tb_student
WHERE studentNo LIKE ‘2013110201’;

SELECT *
FROM tb_student
WHERE studentName LIKE ‘%林%’;

对查询结果排序
SELECT studentName, native, nation
FROM tb_student
ORDER BY studentName;

限制查询结果的数量
SELECT studentNo, courseNo, score
FROM tb_score
ORDER BY score DESC
LIMIT 2,3 (3 OFFSET 2)

分组聚合查询
SELECT courseNo, COUNT(studentNo)
FROM tb_score
GROUP BY courseNo
[HAVING <条件表达式>];

连接查询
交叉连接（CROSS JOIN）
SELECT * FROM 表1 CROSS JOIN 表2;
或
SELECT * FROM 表1，表2;

内连接
select tb_student.* , tb_score.*
from tb_student,tb_score
where tb_student.studentNo=tb_score.studentNo;
或
select tb_student.* , tb_score.*
from tb_student (INNER) join tb_score
on tb_student.studentNo = tb_score.studentNo
where ****;

自连接
select c1.*
from tb_course c1 join tb_course c2
on c1.credit = c2.credit
where c2.courseName = ‘数据库’;

自然连接

外连接
1.左外连接
用于返回该关键字左边表(基表)的所有记录，并用这些记录与该关键字右边表(参考表)中的记录进行匹配

2.右外连接
返回右表所有记录表，并用这些记录与左边表(参考表)中的记录进行匹配，左表没有的话，该字段值均被设置为空值NULL

子查询（嵌套查询）

1.带有IN关键字的子查询
select studentName
from tb_student
where tb_student.studentNo IN
(select distinct tb_score.studentNo from tb_score);
2.带有比较运算符的子查询
select studentNo, studentName from tb_student
where classNo =
(select classNo from tb_class
where className = ‘计算机14-1班’);
3.带EXISTS关键字的子查询
select studentName from tb_ student a
where exists
(select * from tb_score b
where a.studentNo = b.studentNo AND courseNo = ‘31002’);

联合查询(UNION)
使用UNION关键字可以把来自多个select语句的结果组合到一个结果集中，这种查询方式称为并(UNION)运算或联合查询
注意：合并时，多个select子句中对应的字段数和数据类型必须相同

eg：使用UNION查询选修了"管理学"或"计算机基础"的学生学号
select studentNo
from tb_score,tb_course
where tb_score.courseNo = tb_course.courseNo AND courseName = ‘管理学’
UNION
selct studentNo
from tb_score,tb_course
where tb_score.courseNo = tb_course.courseNo AND courseName = “计算机基础”

伍：
数据更新

1.插入数据（insert或replace）
一般使用INSERT
insert into tb_name (column_list)
values(value_list1) [, (value_list2)];

使用REPLACE：
若一个待插入的表中存在有PRIMARY KEY 或 UNIQUE约束，使用INSERT语句将无法插入此行。
使用REPLACE语句可以在插入数据之前将表中与待插入的新纪录相冲突的旧记录删除
replace into tb_name(column_list)
values(value_list);

2.修改数据（update）
update tb_name
set column1 = value1, column2 = value2
[where ]

3.删除数据（delete或truncate）
百分之95使用delete
delete from tb_name
[where]

删除所有数据记录
truncate语句直接删除原来的表并重新创建一个表，而不是逐行删除表中的记录，因此执行速度会比delete操作更快
truncate [table] tb_name;
注意：使用truncate删除表数据时候一定要小心

陆：
索引

查看数据表上所建立的索引
show index from db_school.tb_score

创建索引
1.使用create table语句创建索引
create table tb_name(
studentNo CHAR(10) NOT NULL,
studentName VARCHAR(20) NOT NULL,
INDEX(studentName));
在创建新表的同时建立主键索引（primary key、unique、foreign key）
eg: constraint pk_score PRIMARY KEY(studentNo)

2.使用create index语句创建索引
CREATE ‌INDEX index_stu ON db_school.tb_student(studentNo);

3.使用ALTER TABLE语句创建索引
ALTER TABLE db_school.tb_student1 ADD INDEX idx_studentName(studentName);

删除索引
1.使用DROP删除
DROP INDEX index_name ON tb1_name;

2.使用ALTER TABLE删除
ALTER TABLE tb1_name DROP INDEX index_name;

柒：
视图

视图不是数据库中真实的表，而是一张虚拟表
视图的内容是由存储在数据库中进行查询操作的SQL语句来定义的
视图不是以数据集的形式存储在数据库中，它所对应的数据实际上是存储在视图所引用的真实表（基础表）中
视图是用来查看存储在别处的数据的一种设施

1.创建视图
CREATE OR REPLACE
VIEW view_name [(column_list)]
AS
SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

with check option: 这个可选子句可以确保数据修改后仍可以通过视图看到修改后的数据。
LOCAL： 仅会针对其自身检查对插入项进行测试
CASCADED： 不仅会针对它自己的检查对插入项进行测试，也会针对基本视图v_score的检查对插入项进行测试

2.删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name]…

3.修改视图定义
ALTER VIEW view_name [(column_list)]
AS SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

4.查看视图定义
SHOW CREATE VIEW view_name

5.更新视图数据
①使用INSERT语句通过视图向基础表插入数据
INSERT INTO db_school.v_student
values(‘111’,‘撒旦’)

②使用UPDATE语句通过视图修改基础表的数据
UPDATE db_school.v_student
SET native=‘河南’;

③使用DELETE语句通过视图删除基础表的数据
DELETE FROM db_school.v_student
WHERE studentName = ‘周明’;

6.查询视图数据（跟基本的SELECT操作是差不多的）
SELECT studentNo, studentName
FROM db_school.v_student
WHERE classNo = ‘CS1401’;

捌：
触发器

触发器是一个被指定关联到一个表的数据库对象，当对一个表的特定事件出现时，它将会被激活
具体而言，触发器就是MySQL响应INSERT、UPDATE和DELETE语句而自动执行的一条MySQL语句
(或位于BEGIN和END语句之间的一组MySQL语句)

1.创建触发器
CREATE
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body

2.删除触发器
DROP TRIGGER [IF EXISTS]
trigger_name

3.使用触发器
①INSERT触发器
在INSERT触发器代码内可引用一个名为NEW的虚拟表来访问被插入的行
eg: CREATE TRIGGER db_school.tb_student_insert_trigger AFTER INSERT
ON db_school.tb_student FOR EACH ROW SET @str = NEW.studentNo;

②DELETE触发器
在DELETE触发器代码内可以引用一个名为OLD的虚拟表来访问被删除的行

③UPDATE触发器
在UPDATE触发器中可以引用NEW也可以引用OLD的虚拟表来访问被更新的行
当触发器涉及对表自身的更新操作时，只能使用BEFORE UPDATE触发器
eg: CREATE TRIGGER db_school.tb_student_update_trigger BEFORE UPDATE
ON db_school.tb_student FOR EACH ROW
SET NEW.nation = OLD.native;

玖
事件(event)

事件调度器可以在指定的时刻执行某些特定的任务，并以此可取代原先只能由操作系统的计划任务来执行的工作

查看事件调度器是否已经开启
SHOW VARIABLES LIKE ‘EVENT_SCHEDULER’;
或者
SELECT @@ EVENT_SCHEDULER;
如若没有被开启，可以通过执行如下MySQL命令语句来开启该功能
SET GLOBAL EVENT_SCHEDULER = 1;
或
SET GLOBAL EVENT_SCHEDULER = TRUE;

1.创建事件
CREATE
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO event_body

eg:

delimiter c r e a t e e v e n t i f n o t e x i s t s e v e n t i n s e r t o n s c h e d u l e e v e r y 1 m o n t h s t a r t s c u r d a t e ( ) + i n t e r v a l 1 m o n t h e n d s ′ 2021 − 09 − 2 3 ′ d o b e g i n i f y e a r ( c u r d a t e ( ) ) < 2013 t h e n i n s e r t i n t o t b s c h o o l v a l u e s ( N U L L , ′ 张 晓 勇 ′ , ′ 男 ′ , ′ 1997 − 12 − 1 1 ′ , ′ 山 西 ′ , ′ 汉 ′ , ′ A C 130 1 ′ ) ; e n d create event if not exists event_insert on schedule every 1 month starts curdate()+interval 1 month ends '2021-09-23' do begin if year(curdate()) < 2013 then insert into tb_school values(NULL,'张晓勇','男','1997-12-11','山西','汉','AC1301'); end createeventifnotexistseventi​nsertonscheduleevery1monthstartscurdate()+interval1monthends′2021−09−23′dobeginifyear(curdate())<2013theninsertintotbs​choolvalues(NULL,′张晓勇′,′男′,′1997−12−11′,′山西′,′汉′,′AC1301′);end

2.修改事件
ALTER
EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[DO event_body]

3.删除事件
DROP EVENT [IF EXISTS] event_name;

拾：
存储过程与存储函数

存储过程：
存储过程是一组为了完成某个特定功能的SQL语句集
优点： 可增强SQL语言的功能和灵活性
良好的封装性
高性能
可减少网络流量
存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性
1.创建存储过程
CREATE
PROCEDURE sp_name (proc_parameter[…])
[characteristic…] routine_body

eg:
DELIMITER C R E A T E P R O C E D U R E s p u p d a t e s e x ( I N s n o C H A R ( 20 ) , I N s s e x C H A R ( 2 ) ) B E G I N U P D A T E t b s t u d e n t S E T s e x = s s e x W H E R E s t u d e n t N o = s n o ; E N D CREATE PROCEDURE sp_update_sex(IN sno CHAR(20),IN ssex CHAR(2)) BEGIN UPDATE tb_student SET sex = ssex WHERE studentNo = sno; END CREATEPROCEDUREspu​pdates​ex(INsnoCHAR(20),INssexCHAR(2))BEGINUPDATEtbs​tudentSETsex=ssexWHEREstudentNo=sno;END

2.存储过程体
①局部变量
DECLARE var_name [, …] type [DEFAULT value]

②SET语句
SET var_name = expr[, var_name = expr]…

③SELECT…INTO 语句
SELECT col_name[, …] INTO var_name[, …] table_expr

3.调用存储过程
CALL sp_name([parameter[, …]])
CALL sp_name[( )]

4.删除存储过程
DROP PROCEDURE FUNCTION [IF EXISTS] sp_name

存储函数
区别： 存储函数不能拥有输出参数
可以直接对存储函数进行调用，且不需要使用CALL语句
存储函数中必须包含一条RETURN语句
创建存储函数
CREATE
FUNCTION sp_name([func_parameter[, …]])
RETURNS type
routine_body

调用存储函数
SELECT sp_name([func_parameter[, …]])

删除存储函数
DROP FUNCTION [IF EXISTS] sp_name

十一
访问控制和安全管理

用户账号管理
1.创建用户账户
CREATE USER user_specification
[, user_specification]
eg:
CREATE USER ‘zhangsan’@‘localhost’ IDENTIFIED BY ‘123’;

2.删除用户
DROP USER user[, user]…;
注意：一般user是：lisa@localhost

3.修改用户账号
RENAME USER old_user TO new_user …

4.修改用户口令
SET PASSWORD [FOR user] =
PASSWORD(‘new_password’) | ‘encrypted password’

用户管理权限

1.查看账户权限
SHOW GRANTS FOR user

2.权限的授予
eg：
GRANT SELECT(studentNo,studentName)
ON db_school.tb_student (db_school.* | .)
TO ‘zhangsan’@‘localhost’;
当系统不存在该用户时
GRANT SELECT，UPDATE (| ALL)
ON db_school.tb_student
TO ‘lisi’@‘localhost’ IDENTIFIED BY ‘123’;

3.权限的转移和限制
转移权限
eg：
GRANT SELECT, UPDATE
ON db_school.tb_student
TO ‘zhou’@‘localhost’ IDENTIFIED BY ‘123’
WITH GRANT OPTION;

限制权限
eg:
GRANT DELETE
ON db_school.tb_student
TO ‘huang’@‘localhost’
WITH MAX_QUERIES_PER_HOUR 1;

4.权限的撤销
eg：
REVOKE SELECT
ON db_school.tb_student
FROM ‘zhou’@‘localhost’;