【数据库】超全的SQL语句总结[从建库到跑路]

总结了所有的数据库基本语法
大致整理分类如下；
一、增删改查 – 按建库-建表-建字段-建视图-建索引整理
二、存储与函数 – 整理基础存储和函数语句
三、数据库管理 – 创建用户-权限分配
四、并发控制与安全 – 三级封锁协议与安全
面向复习，不包含很基础的查询语

1. 数据库

-- 【增】创建名为onlinedb的数据库，默认字符集设置为  utf8，排序规则设置为gb2312_chinese_ci，
CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;

— 【删】删除数据库服务器中名为test的数据库，
DROP DATABASE test;

— 【改】使用SQL语句，修改数据库onlinedb的字符集设置为uft8，排序规则设置为utf8_bin。
ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;

— 【查】
SHOW DATABASES;
show tables；
SHOW CREATE DATABASE test;
SHOW CREATE TABLE dept\G;
}

2. 表及约束 [外键脑袋疼0.0]

-- 【删】 是否存在该表，存在则删除
DROP TABLE IF EXISTS `student`;

— 【创】 创建表：学生有
CREATE TABLE `student` (
  `Sno` char(8) NOT NULL PRIMARY KEY,
  列名1 类型(长度) [约束],
  列名2 类型(长度) [约束],
  foreign key(Sno) references 其他表(字段)
) 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;

3. 数据

-- 【增】
insert into 表名(字段1,字段2..) values(值1,值2…);    

— 【删】
delete from 表 where 条件;  
//删除数据带条件指定数据，否则删除全表数据

— 【改】
update 表 set 字段=值 where 条件;  
//带条件修改指定数据，否则修改全表

— 【查】
select * from 表名 where 列名 = xx;
select * from 表名 where 列名 = xx group by 字段 having 聚合函数条件;

4. 视图

-- 【创】
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’;

5. 索引

增快查询速度

-- 【增】
CREATE UNIQUE INDEX 索引名称 ON 表名称 (列名) //不重复
CREATE INDEX 索引名称 ON 表名 (列名1,列名2,...)
CREATE INDEX PersonIndex ON Person (LastName);

— 【删】
DROP INDEX 索引名 ON 表名 
DROP INDEX PersonIndex On Person

— 【改】
如果想修改索引，可以先删除再创建

— 【查】
show index from 表名

6. 触发器

-- 【增】
CREATE TRIGGER <触发器名>
< BEFORE | AFTER >  <INSERT | UPDATE | DELETE > ON <表名> 
FOR EACH Row<触发器主体>

[例]
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;

二、存储与函数

存储函数触发器

1.储存

储存创建不返回值，调用用call，带入参数即可。有in 有out

【例】定义名称为proc_add的存储过程，该存储过程有两个int类型的参数，分别为a和b。*/
CREATE PROCEDURE proc_add(in a int, in b int)
BEGIN
DECLARE  c  int  DEFAULT 0;
SET c = a + b;
SELECT  c  AS 'Result';
END;
-- 调用上述存储过程
CALL proc_add(2,3);

2.函数

创建函数输入书号和借阅证查询超期天数

函数调用

select Count_voverdue(xxxx,xxxx) into diffDay;
该例用于查询

CREATE FUNCTION 

3.触发器

如果有人还书，就把借阅记录loan删除，并且插入loanHist，并且查询是否有超期，超期则插入还款记录
本触发器实现后者

create trigger tri_fine  

三、 数据库管理

服务器开启触发器：

mysql登录后开启权限：set global log_bin_trust_function_creators = 1\g

用户管理

用户创建

要记得修改内容后要flush privileges刷新权限才能生效；

//请在下面输入创建新用尸FD-user的MySQL语句
/*********begin*********/
CREATE USER "FD-user"@"127.0.0.1" IDENTIFIED BY "FDpassword" ;
/********* end *********/
//请在下面输入修改FD-user用户登陆密码的MySQL语句
//注意:当前用户为root用户
UPDATE mysq1.user SET PASSWORD=PASSWORD("newFDpass") WHERE USER = "FD-user"
AND HOST= "127.0.0.1";
FLUSH PRIVILEGES;
//请在下面输入删除FD-user用户的MySQL语句
/*********begin*********/
DELETE FROM mysql. user WHERE user = "FD-user" AND HOST = "127 .0.0.1" ;
FLUSH PRIVIL EGES;

权限授权

-- 【增】
grant 权限列表 on 库.表 to 用户名@'ip' identified by "密码";
-- 【删】
revoke 权限列表 on 库.表 from 用户名@'ip';
-- 【改】
增+删 == 改...0.0
-- 【查】
show grants for '用户'@'IP地址'

删除用户

drop user ‘用户名’@’允许登录的主机地址’;

修改用户名

rename user ‘用户名’@’IP地址’ to ‘新用户名’@’IP地址’;

修改密码

set password for ‘用户名’@’IP地址’ = Password(‘新密码’);

数据库备份

//请在下面输入将员工信息表emp和部门]信息表dept导出的命令行语句
/*********begin*********/
mysq1dump -uroot p123123 -h127.0.0.1 db1
--tables emp dept > /home/ db1/backup/ emp-dept.sq1

//请在下面输入从备份sq1文件中恢复工资信息表sa1的MySQL语句
/********* begin*********/
drop database sala;
create database sala;
use db1;
source / home/ db1/backup/ sala.sq1;

四、并发控制与事务安全

并发控制

并发问题：1. 丢失修改 2. 脏读 3. 不可重复读

一级 修改时加X，
二级 在一级基础上读数据前加S，读完释放
三级 在一级基础上读数据前加S，事务结束才释放

![image.png](https://img-blog.csdnimg.cn/img_convert/23f476086e9fbfe758d2aab1e0d87f79.png

一级封锁：修改数据加x锁直到事务结束才释放。在此协议中，仅仅是读数据是不需要加锁的，所以只能解决丢失修改问题，不能解决脏读和不可重复读。
二级封锁：在一级封锁的基础上，加了一条：T事务在读取数据R之前必须先对其加上S锁，读完释放S锁。可以解决丢失修改和脏读（加了读锁就可以防止在读的期间其他事务进行修改，但是读完之后，事务结束之前，依然可能会其他事务进行修改，导致不可重复读）。
三级封锁协议：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。：解决了丢失修改、脏读和不可重复读的问题。

-- 下面给数据库中的account表上写锁，给mylock上读锁:
lock table account write,mylock read;

— 解锁
unlock tables

事务

start transaction – 开启事务
rollback – 事务回滚
commit – 事务提交

drop PROCEDURE if EXISTS p_transfer;
CREATE PROCEDURE p_transfer(in out_cid char(4),in in_cid char(4),in amount double,out result INT)
BEGIN
    DECLARE tatal_money  double DEFAULT 0.0;
DECLARE  done int DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLstate '02000' SET done = 1;  

跑路

drop database DBName;

21个