MySQL : DDL; DML; DQL; DCL; 存储过程；触发器；视图；函数

MySQL

一、SQL结构化查询语言

概述：通过SQL语言可以操作所有的关系型数据库。每种数据库之间会存在差异，称为 “方言”。

分类：

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录(增、删、改);
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

二、DDL

操作库：
建库： create database 库名;
	例： create database mydb;
查看所有库： show databases;
删除： drop database 库名；
	例：drop database mydb;
修改数据库编码：
alter database 库名 character set='gbk';
查看数据库： show create database 库名;
切换库： use 库名;

操作表：
查看该库下所有的表： show tables;
创建表： create table 表名(字段1 数据类型,字段2 数据类型...);
例： create table student(name varchar(10),age int);
删除表： drop table 表名;
修改表名: 
alter table 旧表名 rename to 新表名;
对表头的操作： alter
添加一个字段：
alter table 表名 add(列名 数据类型);
删除一个列：
alter table 表名 drop 列名;
修改列名：
alter table 表名 change 旧列名 新列名 新数据类型;
修改列的数据类型：
alter table 表名 modify 列名 新数据类型;
查看建表语句：
show create table 表名;

三、DML

插入数据： insert into
insert into 表名(列名1,列名2...) values(值1,值2...);
给表中所有字段插入值：
insert into 表名 values(值1,值2,...值n);
注意：--字符串类型和日期类型的字段的值要用单引号引起来！
删除数据：
无条件删除表中所有数据：
delete from 表名;	--逐行删，效率低
truncate table 表名;	--先删除整个表，再创建一个空表

条件： where 
= >= <= > < 
and --且 
or  --或
(is not null)	--不为空 
(is null)	--为空 null不能使用=或！=判断
not	--非  
between 值1 and 值2 --在值1和值2之间

条件删除：
delete from 表名 where 条件;
例： delete from student where age>=20;	--删除年龄大于等于20的学生数据

修改表中数据： update
update 表名 set 字段名1=修改的值,字段名2=修改的值... where 条件;
例： update 表名 set age=20 where name='zhangsan'; --把 zhangsan 的年龄改为20

四、DQL

1、单表查询

无条件查询：
--查询表中所有数据：
select * from 表名;
* --通配符，通配表中所有字段
--查询个别字段数据：
select 字段名1,字段名2... from 表名;

条件查询：
select 字段名1,字段名2... from 表名 where 条件;
in(值1,值2...)--等于值1或值2或...

字段运算：
例： select sal,sal*12 from emp; 	--月薪，月薪*12=年薪
--注意：null参与运算结果是null
ifnull(num,原始值) --如果字段值为null，则将其替换为 num ,否则不改变其值
distinct --对字段去重

别名：关键字 as
例： select sal as 月薪,sal*12 as 年薪 from emp; 

模糊查询： like
% --通配多个任意字符
_ --通配一个任意字符
例： '_a%' --匹配第二个字母为a的所有单词
order by  --排序，默认升序 asc:升序  desc:降序
例：
select * from emp order by sal asc;--对工资升序排列
select * from emp order by sal desc;--对工资降序排列

聚合函数：
select count(sal) from emp;--查询工资这一列有多少行，null不参与统计
select SUM(sal) from emp;--查询工资的总和
select MAX(sal) from emp;--查询工资最大值
select MIN(sal) from emp;--查询工资最小值
select AVG(sal) from emp;--查询工资平均值

分组： group by
where --在分组之前对数据筛选
having --对分组后的数据再次进行筛选
例：按照部门编号分组，并求出每个部门的平均工资
select deptno as 部门号, AVG(sal) as 部门平均工资 from emp group by deptno;
例：按照部门编号分组，查询各个部门工资高于1500的员工的工资总和大于6000的部门
select deptno as 部门号, SUM(sal) as 高于3000的工资总和 from emp where sal>3000 group by deptno having 高于3000的工资总和>6000;

分页查询： limit
select from 表名 limit 起始索引，本页条数;
起始索引=(页码-1)*本页条数
例：emp 一共10行数据，每页展示4行
SELECT * FROM emp LIMIT 0,4;  -- 第一页
SELECT * FROM emp LIMIT 4,4;  -- 第二页
SELECT * FROM emp LIMIT 8,4;  -- 第三页,本页只有2行

2、约束

约束：对插入表中的数据做出一定的限定，为了保证数据的有效性和完整性
primary key --主键约束：非空且唯一，一张表只能有一个主键
方式一：建表时添加约束，字段名后加上约束即可
字段名 数据类型 primary key --添加主键约束
方式2：通过修改表添加约束：
alter table 表名 add primary key(字段名);
注意：表中如果已经有超出约束范围的数据，约束会添加失败
--联合主键：将多个字段看做一个整体设为主键
create table 表名(字段名1 数据类型,字段名2 数据类型...,primary key(字段名1,字段名2...));
alter table 表名 add primary key(字段名1,字段名2...);
删除主键约束：需要分部删除
alter table 表名 drop primary key;--删除唯一
alter table 表名 modify 字段名 数据类型 null;--删除非空

auto_increment --自增长约束：一般配合主键使用
例： id int primary key auto_increment
删除自增长约束：
alter table 表名 change 字段名 字段名 int;

unique	--唯一约束:数据不能重复，对null无效
not null  --非空约束
一个字段可以添加多个约束：
字段名 数据类型 not null unique  --非空且唯一
unsigned --非负约束
TINYINT --范围 -128~127
TINYINT UNSIGNED --范围 0~255

foreign key --外键约束
特点： 1、主表一方不能删除从表一方还在引用的数据；
	  2、从表一方不能添加主表一方没有描述的数据。
*添加外键约束：
在从表一方添加外键约束，去关联主表一方的主键
alter table 从表 add foreign key(从表字段) references 主表(主表字段);
*级联删除：
alter table 从表 add foreign key(从表字段) references 主表(主表字段) on delete cascade;
*级联更新：
alter table 从表 add foreign key(从表字段) references 主表(主表字段) on update cascade;  

*处理一对多:在从表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,为了保证数据的有效性和完整性,在从表的外键上添加外键约束即可.

*处理多对多:引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多了，为了保证数据的有效性和完整性，需要在中间表上添加两个外键约束.	

*删除主表中的数据：
方式1: 添加级联删除
方式2: 先把带有外键的从表的数据删除,再删除主表中的数据

3、多表查询

--多张表无条件查询，查询出的数据是笛卡尔积，没有任何意义
--笛卡尔积：多张表字段相加，行数相乘
select 表1.*,表2.* from 表1,表2;
--内连接：不符合条件的数据不做展示
格式1： 显式的内连接 inner可以省略不写
select a.*,b.* from a inner join b on 表a表b的连接条件;
格式2：隐式的内连接
select a.*,b.* from a,b where 表a表b的连接条件;

左外连接:
--先展示join左边的(a)表的所有数据,根据条件关联查询join右边的表(b),符合条件展示出来,不符合以null值展示.
select a.*,b.* from a left outer join b on 连接条件;  --outer 可以省略不写
右外连接:
--先展示join右边的(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件展示出来,不符合以null值展示.
select a.*,b.* from b right outer join a on 连接条件; --outer 可以不写

子查询：一个查询的查询条件依赖于另一个查询结果
自连接查询：给一张表起两个别名,将他视为两张表,来进行查询

4、复制表

-- 创建一张表，把另一张表中的字段和对应的数据全部复制过去
create table 表名 as select * from 另一张表 where true; --where true 可以省略不写
-- 创建一张表，只复制另一张表中的个别字段和其对应的数据
create table 表名 as select 字段1,字段2... from 另一张表;
-- 创建一张表,只把另一张表中的字段复制过去
create table 表名 as select * from 另一张表 where false; 
--另一张表也可以是临时表，或者子查询

五、DCL

root:拥有所有权限
权限账户：只拥有部分权限的账户
password：md5加密函数（单向加密）
select password('root')   --查询用户密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';--修改密码
SELECT * FROM USER;  --查询数据库用户
分配权限账户：
权限： select insert delete update drop create/  --或all
  	语法：
  GRANT 权限 ON 数据库名.某张表名 TO '用户名'@'localhost' IDENTIFIED BY '123456';
  --@ 后面可以是localhost，也可以是ip ，也可以给% ，%代表任意一台计算机都可以连接上来
  	注意分配多个权限用逗号隔开
  GRANT DELETE,SELECT,UPDATE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
  	删除账户：
  	Delete FROM user Where User='eric' and Host='localhost';

六、存储过程 procedure

1、概述

​ 存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似于Java中的方法。

2、语法

DELIMITER $$
CREATE
    PROCEDUR `performance_schema`.`myTestPro`()
    BEGIN
	--SQL语句
    END$$
DELIMITER;
--注意：创建存储过程需要管理员分配权限
drop procedure 存储过程名;--删除存储过程
show procedure status\G;  -- 查看所有的存储过程状态 \G：格式化
show create procedure 存储过程名\G; -- 查看创建存储过程的语句

3、参数

in:输入参数
out：输出参数
inout：输入输出参数

4、带有IF逻辑的存储过程 if then elseif else

DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(2))
BEGIN
	IF num>0 THEN
		SET str='正数';		-- 注意要用分号结束
	ELSEIF num<0 THEN        --注意elseif 连写
		SET str='负数';            
	ELSE
		SET str='零';
	END IF;         		 --注意要结束if，要写分号
	END $$
DELIMITER;
--调用存储过程，查看结果
call pro_testIf(100,@rr);
select @rr;

5、while循环 while do

--求1~num的和
DELIMITER $$
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
	-- 定义一个局部变量
	DECLARE i INT DEFAULT 1;
	DECLARE vsum INT DEFAULT 0;
	WHILE i<=num DO
	      SET vsum = vsum+i;
	      SET i=i+1;
	END WHILE;  	--结束循环
	SET result=vsum;
	END $$
DELIMITER;
--控制循环的关键字
leave 相当于java中的 break 
iterate 相当于java中的 continue

6、变量

全局变量（内置变量）：可以在多个会话中访问
show variables;  -- 查看所有全局变量： 
select @@变量名  -- 查看某个全局变量： 
set @@变量名=新值   -- 修改全局变量： 
--设置SQL服务器输出数据编码
set @@character_set_results='gbk';
--设置SQL服务器接收数据编码
set @@character_set_client='utf8';
会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
 set @变量=值     -- 定义会话变量: 
 select @变量     -- 查看会话变量：    
局部变量：在存储过程或函数中定义的变量是局部变量。只要存储过程执行完毕，局部变量就丢失！
DECLARE i INT DEFAULT 1;  	--定义局部变量
set i=10;		--给变量设置值 

七、触发器 Trigger

1、概述

​ 触发器：数据库中的一个对象，相当于JS中的监听器，触发器可以监听增、删、改三个动作。

2、语法

DELIMITER $$

CREATE
    TRIGGER `mytestdb`.`myTriger` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `mytestdb`.`<Table Name>`
    FOR EACH ROW 
    BEGIN

    END$$
DELIMITER ;
BEFORE --行为发生之前就触发
AFTER --行为发生之后触发
FOR EACH ROW --行级触发，每操作一行就触发
old.字段 --可以获取到被监听的表中的字段的旧值
new.字段 --可以获取到被监听表中更新后的字段的新值

例：修改表t1中的数据，另一张表t2中的数据跟着修改

DELIMITER $$
CREATE
    TRIGGER `mytestdb`.`MyTri7` AFTER UPDATE
    ON `mytestdb`.`t1`
    FOR EACH ROW 
    BEGIN
	UPDATE t2 SET id=new.id,username=new.username,age=new.age WHERE id=old.id;
    END$$

DELIMITER ;

八、视图

视图：跟表一样，具有行和列的结构，可以简化查询,视图中的数据，来源于表
--创建视图
create view my_view as select * from emp;
--查询视图，和查表的语法一样
select * from my_view;
--单表视图：视图中的数据来源于一张表
--多表视图：视图中的数据来源于多张表的联合查询
注意：视图不能封装子查询的数据
--查看创建视图语句：
show create view my_view;
--删除视图
drop view my_view;
--视图本身不能修改，但可以修改视图的来源
alter view 视图名 as 新的select语句;

九、函数

函数：包括内置函数和自定义函数
自定义函数语法：
DELIMITER $$
  CREATE
      FUNCTION `mytestdb`.`myFun`(num INT)
      RETURNS INT
      BEGIN
  	  DECLARE i INT DEFAULT 100;
  	  SET i=i+num;
      RETURN i;
      END$$
  
  DELIMITER ;
调用函数： select 函数名();

函数和存储过程的区别：
1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out实现返回值这个功能
2.存储过程有in out inout这几个参数类型，函数没有

十、数据库表设计

三大范式

第一范式： 要求表的每个字段必须是不可分割的独立单元；

第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

第三范式： 在第二范式基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。