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

一、增删改查

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)
BEGINDECLARE  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 																				#创建
Count_ voverdue(vloanno varchar(16) ,vbookno char(9))   #函数
RETURNS INT																						  #返回啥
BEGINdeclare days tinyint default 0;declare adays tinyint default 0;declare diff tinyint default 0;select term into days from Class_ User where classNo = (select classNo from Users where loanNo = vloanno);select datediff (now( )，borrowDate) into adays from Loanwhere loanNo = vloanno and bookNo = vbookno;set diff = adays- days;if(adays < days)thenreturn 0;else return diff ;end if;
end;

3.触发器

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

create trigger tri_fine  #创建
after delete             #条件
on Loan                  #基于谁
for each row             #循环
begindeclare 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 > 0theninsert into Money ( loanNo , bookNo , amount , reason, bi lldate)values (old. loanNo ,old . bookNo, (diffDay- dueDay) * 0.1,"过期罚款" ,now()).end if;
end

三、 数据库管理

服务器开启触发器：

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，事务结束才释放


• 一级封锁：修改数据加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)
BEGINDECLARE tatal_money  double DEFAULT 0.0;DECLARE  done int DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLstate '02000' SET done = 1;  #异常处理select  balance into tatal_money  from bank where cid = out_cid;if( tatal_money - amount >= 10) THEN BEGINSTART transaction; 				update bank set balance = balance - amount where cid = out_cid;update bank set balance = balance + amount where cid = in_cid;          if done <> 0 then beginset result = 2;    -- 转账失败rollback;    -- 事务回滚end;elsebeginset result = 1;    -- 转账成功commit;    -- 事务提交end;end if;END;elseset result = 3;	-- 转账余额不足。	end if;
END

跑路

drop database DBName;

待更新

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！