一篇学会MySQL

木鱼木心.

已于 2022-10-31 21:23:18 修改

阅读量328

点赞数

分类专栏：计算机基础文章标签： mysql 数据库 sql

于 2022-10-17 22:46:16 首次发布

本文链接：https://blog.csdn.net/qq_52357869/article/details/127378912

版权

计算机基础专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

一篇学会MySQL

一篇学会MySQL

SQL语句

DDL语句(数据定义语言)

数据库模式定义语言并非程序设计语言，DDL数据库模式定义语言是SQL语言（结构化查询语言）的组成部分.

SQL语言包括四种主要程序设计语言类别的语句：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL,本文是从DDL语句开始.

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言.这些定义包括结构定义、操作方法定义等.

DDL数据库操作语句

注意SQL语句中不区分大小写.

#查询所有数据库
SHOW DATABASES;
#查询当前数据库
SELECT DATABAASE();
#创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
#删除数据库
DROP DATABASE [IF EXISTS] 数据库名称;
#使用某数据库
USE 数据库名;
#查询当前数据库所有表
SHOW TABLES;
#查询表结构
DESC 表名称;
#查询指定表的建表语句
SHOW CREATE TABLE 表名;

DDL-表操作语句

创建表语句

#创建一个新的表以及表中字段,字段类型
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
    字段3 字段3类型[COMMENT 字段3注释],
    字段4 字段4类型[COMMENT 字段4注释]
)

常用数值类型:

常见的日期类型:

常见的字符串类型:

创建testTable表

添加name字段字段类型为char,无论存放什么数据都占用10个字符,效率较高.

CHAR与VARCHAR相似,VARCHAR只占用使用的字符空间,效率较低.

添加age字段字段类型为TINYINT ,存储空间中存放无符号数,允许存放空数据

添加score字段,字段类型为DOUBLE,存放4位的空间,保留1位小数

create table testTableb(
stuname char(10) not null comment ‘名称’,
age tinyint UNSIGNED null comment ‘年龄’,
score double(4,1) comment ‘分数’
)

表结构的修改语句

#为表中新添加字段
ALTER TABLE 表名称 字段名 类型(长度) [COMMENT 注释] [约束];

#为testTABLE表中新添加字段

alter table testtable add field varchar(1) comment ‘新添加测试字段’

#修改表中数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
#修改表中的字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

#修改表中数据类型
alter table testTable modify field varchar(10);
#修改表中的字段名
alter table testTable change field new_field varchar(20) comment ‘更改后的属性’;

#修改表名称
ALTER TABLE 表名 RENAME TO 新表名;

alter table testTable rename TO new_testTable;

表结构的删除语句

#将某个表中的字段删除
ALTER TABLE 表名 DROP 字段名;

alter table new_testTable dropnew_field ;

#删除某个表
DROP TABLE [IF EXISTS] 表名;
#删除指定表,并重新创建该表
TRUNCATE TABLE 表名;

DML(数据操作语言)

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作.DML中最核心的是三种语句,即添加数据,修改数据,删除数据的SQL语句.

添加表数据

#给指定字段添加数据
INSERT INTO表名(字段名1,字段名2, ...) VALUES (值1,值2...)
#给全部字段添加数据
INSERT INTO表名VALUES (值1,值2, .);
#批量添加数据
INSERT INTO 表名(字段名1,字段名2,..) VALUES (值1,值2,..),(值1,值2,..),(值1,值2..);
INSERT INTO 表名 VALUES (值1,值2,...) (值1,值2,...) (值1,值...);

insert into new_testtable (stuname,age,score,new_field) values (“wjk”,20,99.9,“a”);

insert into new_testtable values (“wjk_a”,18,60,“b”);

insert into new_testtable (stuname,age,score,new_field) values (“wjk_b”,20,99.9,“a”),(“wjk_c”,20,99.9,“a”);

insert into new_testtable values (“wjk_a”,18,60,“b”),(“wjk_a”,18,60,“b”),(“wjk_a”,18,60,“b”);

修改表数据

#修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据.
UPDATE 表名 SET 字段名1= 值1 , 字段名2 = 值2....[WHERE 条件];

update new_testtable set stuname = “updateData”;

删除表数据

#DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据.
#DELETE语句不能删除某一个字段的值(可以使用 UPDATE).
DELETE FROM 表名 [WHERE 条件]

delete from new_testtable ;

DQL(数据查询语言)

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言,用来查询数据库中表的记录.

查询表数据

基本查询语句

#查询多个字段
SELECT 字段1, 字段2,字段3... FROM 表名;
SELECT * FROM 表名;
#设置别名
SELECT 字段1 [AS 别名1],字段2 [AS别名2] .. FROM 表名;
#去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

select * from new_testtable ;

select age as ‘年龄’,score as ‘分数’ FROM new_testtable ;

select distinct stuname from new_testtable ;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

select * from new_testtable where age>18;

select * from new_testtable where score is null;

select * from new_testtable where score is not null;

select * from new_testtable where stuname like ‘%wjk%’;

select * from new_testtable where stuname like ‘wjk%’;

select * from new_testtable where stuname like ‘%wjk’;

select * from new_testtable where age in(18,20);

select * from new_testtable where age between 20 and 60;

等价于:

select * from new_testtable where age >=20 and age<=40;

select * from new_testtable where age != 20;

聚合函数

聚合函数是将一列数据作为一个整体,进行纵向计算,NULL值不进入聚合函数计算.下图为常用的聚合函数:

select count(*) from new_testtable;

select count(age) from new_testtable;

select avg(age) from new_testtable;

select min(age) from new_testtable;

select sum(score) from new_testtable where age=18;

分组查询

SELECT 字段列表 FROM 表名 [WHERE条件] GROUP BY 分组字段名 [ HAVING分组后过滤条件];

where. 与having区别:
执行时机不同: where是分组之前进行过滤，不满足where条件,不参与分组;而having是分组之后对结果进行过滤.
判断条件不同: where不能对聚合函数进行判断，而having可以.

分组查询返回的字段一般是聚合函数和分组的字段,其他的字段没有意义.

select age,count(*) from new_testtable group by age;

select age,avg(score) from new_testtable group by age;

select age,avg(score) from new_testtable group by age having avg(score) > 60;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 ,字段2 排序方式2;

排序方式:

ASC:升序(默认值)
DESC:降序

select * from new_testtable where age is not null order by age asc,stuname desc ;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

分页查阅的注意事项:

起始索引从0开始，起始索引= (查询页码-1) *每页显示记录数.
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT.
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10.

查询new_testtable表中,第1-5条数据

select * from new_testtable limit 0,5;

查询new_testtable表中,第6-10条数据

select * from new_testtable limit 5,5;

查询emp表中性别为男,同时年龄在20-40之间的前五个信息,查询的结果按照年龄升序

select * from emp where gender ‘男’ and age between 20 and 40 order by age asc Limit 5 ;

到此我们的DQL语句也学习完毕,这个时候我们还需要了解不同的DQL之间的执行顺序.一般的DQL语句书写格式如下

# FROM->WHERE->GROUP BY->SELECT->ORDER BY->LIMIT
SELECT
	字段列表 
FROM
	表名列表 
WHERE
	条件列表 
GROUP BY
	分组字段列表 
HAVING
	分组后条 列表 
ORDER BY
	排序字段列表 
LIMIT 
	分页参数

DCL(数据控制语言)

DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户、控制数据库的访问权限.

#查询用户
USE mysql;
SELECT * FROM user;
#创建用户
CREATE USER '用户名’@'主机名’ IDENTIFIED BY '密码';
#修改用户密码
SET PASSWORD FOR '用户名’@'主机名’ = PASSWORD('新密码’);
#删除用户
DROP USER '用户名'@'主机名’;

create user ‘wjk’@‘localhost’ identified by ‘123456’;

create user ‘wjk’@‘%’ identified by ‘123456’;

set password for ‘wjk’@‘localhost’ = password (‘1234567’);

drop user ‘wjk’@‘localhost’;

DCL-权限管理

#查询权限
SHOW GRANTS FOR‘用户名'@'主机名’ ;
#授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
#撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名'@'主机名';

show grants for ‘wjk’@‘localhost’;

grant all on testDatabase.* to ‘wjk’@‘localhost’;

grant all on . to ‘wjk’@‘localhost’;

MySQL常用函数

函数是指一段可以直接被另一段程序调用的程序或代码.

字符串函数

MySQL中内置了很多的字符串函数,常用的几个如下:

select connat(‘hello’,‘world!’);

select lower(‘HELLO’);

select upper(‘hello’);

select lpad(‘str’,5,‘_’);

select rpad(‘str’,5,‘_’);

select trim(’ hello world! ');//不去除中间的空格

select substring(‘hello’,1,5)//print hello

update new_testtable set stuname= lpad(stuname,5,‘*’);

update new_testtable set age = lpad(age,3,‘9’)

数值函数

常见的数值函数:

select ceil(1.1);

select floor(1.1);

select mod(5,4);

select rand();

select round(3.444,4);

select lpad(round(rand()*100000,0),6,‘0’);

日期函数

常见的日期函数:

select curdate();

select curtime();

select now();

select year(now());

select month(now());

select day(now());

select date_add(now(),interval 70 year);

select date_add(now(),interval 70 day);

select datediff(now(),date_add(now(),interval 70 day));

流程函数

流程函数可以在SQL语句中实现条件筛选,提高语句的效率.

select if(true,‘ok’,‘false’);

select ifnull(‘’,‘false’);

select ifnull(null,‘false’);

查询new_testtable表中,用户等级

select (case stuname when ‘wjk_a’ then ‘第一个root用户’ when ‘wjk_b’ then ‘第二个root用户’ else ‘其他用户’ end) from new_testtable;

新建score表,查询成绩等级,80以上A,60以上B,其他分数C

CREATE TABLE score (
id INT COMMENT ‘ID’,
NAME VARCHAR (20) COMMENT ‘姓名’,
math INT COMMENT ’ 数学’,
english INT COMMENT ‘英语’,
chinese INT COMMENT ‘语文’
) COMMENT ‘学员成绩表’;
INSERT INTO score (id,NAME,math,english,chinese) VALUES

(1,‘Tom’,67, 88, 95),

(2,‘Rose’,23,66,90),

(3,‘Jack’,56,98,76);

SELECT
( CASE WHEN math > 80 THEN ‘A’ WHEN math > 60 AND math < 80 THEN ‘B’ ELSE ‘C’ END ) AS ‘成绩’ ,
( CASE WHEN english > 80 THEN ‘A’ WHEN english > 60 AND english < 80 THEN ‘B’ ELSE ‘C’ END ) AS ‘成绩’ ,
( CASE WHEN chinese > 80 THEN ‘A’ WHEN chinese > 60 AND chinese < 80 THEN ‘B’ ELSE ‘C’ END ) AS ‘成绩’
FROM score;

MySQL约束

约束

约束是作用于表中字段上的规则,用于限制存储在表中的数据.为了保证数据库中数据的正确、有效性和完整性.

约束是作用于表中字段.上的，可以在创建表/修改表的时候添加约束.

创建一个如下图所示约束创建表结构:

CREATE TABLE USER (
id INT PRIMARY KEY auto_increment COMMENT ‘主键’,
NAME VARCHAR ( 10 ) NOT NULL UNIQUE COMMENT ‘姓名’,
age INT CHECK (age>0 && age<=120) ,
STATUS CHAR ( 1 ) DEFAULT ‘1’ COMMENT ‘状态’,
gender CHAR ( 1 ) COMMENT ‘性别’
) comment ‘用户表’;

测试约束:

insert into user (name ,age, status, gender) values(‘Toml’ ,19,‘1’,‘男’),(‘Tom2’ ,25,‘0’,男’);
insert into user (name , age, status, gender) values (‘Tom3’ ,19,‘1’，男’);
insert into user (name , age, status, gender) values (null,19,‘1’,‘男’);
insert into user (name , age, status, gender) values (‘Tom3’ ,19,‘1’,男’);
insert into user (name , age, status, gender) values (‘Tom4’ ,80,‘1’，‘男’);
insert into user (name , age, status, gender) values (‘Tom5’ .-1,‘1’,‘男’);
insert into user (name , age, status, gender) values (‘Tom5’ ,121,‘1’,‘男’);
insert into user(name , age , gender) values ('Tom5 ',120,‘男’);

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性.

添加外键
CREATE TABLE表名(
	字段名数据类型,
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

外键的关联

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
ALTER TABLE 表名 DROP FOREIGN KEY外键名称;

外键的删除/更新

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) 
ON UPDATE CASCADE 
ON DELETE CASCADE;

CREATE TABLE dept (
id INT auto_increment COMMENT ‘ID’ PRIMARY KEY,
NAME VARCHAR ( 50 ) NOT NULL COMMENT ’ 部门名称’ ) COMMENT ’ 部门表’;
INSERT INTO dept (NAME)
VALUES
(‘研发部’),
(‘市场部’),
(‘财务部’),
(‘销售部’)，
(‘总经办’)

CREATE TABLE emp(
id INT auto_increment COMMENT ‘ID’ PRIMARY KEY,
NAME VARCHAR ( 50 ) NOT NULL COMMENT ‘姓名’,
age INT COMMENT ‘年龄’,
job VARCHAR (20) COMMENT ‘职位’,
salary INT COMMENT ’ 薪资’,
entrydate date COMMENT ’ 入职时间’,
managerid INT COMMENT ‘直属领导ID’,
dept_id INT COMMENT ‘部门ID’
)COMMENT ‘员工表’;

INSERT INTO emp (NAME,age,job,salary,entrydate,managerid,dept_id )
VALUES
( ‘金庸’,66,‘总裁’, 20000,‘2000-01-01’,NULL, 5 ),
(‘张无忌’, 20,‘项目经理’,12500,'2005-12-05 ', 1, 1 ),
(‘杨道’, 33, ‘开发’, 8400, ‘2000-11-03’, 2, 1 ),
(‘韦一笑’, 48, ‘开发’, 11000, ‘2002-02-05’, 2, 1 ),
(‘常遇春’,43, ‘开发’,10500, ‘2004-09-07’, 3, 1 ),
(‘小昭’, 19,‘程序员鼓励师’, 6600, '2004-10-12 ',2,1);

//插入外键,添加后外键关联字段不能随意修改

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

//删除外键

alter table emp drop foreign key fk_emp_dept_id ;

//外键更新/删除时关联表受到影响

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

多表查询

多表关系

项目开发中，在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构，由于业务之间相互关联,所以各个表结构之间也存在着各种联系.

基本上分为三种: 一对多(多对一)、多对多、一对一

一对多(多对一)

案例: 部门与员工的关系
关系:一个部门对应多个员工，一个员工对应一个部门
实现: 在多的一方建立外键，指向一的一方的主键

多对多

案例:学生与课程的关系
关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

create table student(
id int auto_increment primary key comment ‘主键ID’,
name varchar(10) comment ‘姓名’,
no varchar(10) comment ‘学号’
)comment ‘学生表’;
insert into student values (NULL,‘黛绮丝’,‘2000100101’), (NULL,‘谢逊’,‘2000100102’),(NULL,‘殷天正’,‘2000100103’);

CREATE TABLE course ( id INT auto_increment PRIMARY KEY COMMENT ‘主键ID’, NAME VARCHAR ( 10 ) COMMENT ‘课程名称’ ) COMMENT ‘课程表’;
INSERT INTO course
VALUES
( NULL, ‘Java’ ),
( NULL, ‘PHP’ ),
( NULL, ‘MySQL’ ),
( NULL, ‘Hadoop’ );

CREATE TABLE student_course (
id INT auto_increment COMMENT ‘主键’ PRIMARY KEY,
studentid INT NOT NULL COMMENT ‘学生ID’,
courseid INT NOT NULL COMMENT ‘课程ID’,
CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course ( id ),
CONSTRAINT fk_studentid FOREIGN KEY (studentid ) REFERENCES student ( id )
) COMMENT ‘学生课程中间表’;
INSERT INTO student_course
VALUES
( NULL, 1, 1 ),
( NULL, 1, 2 ),
( NULL, 1, 3 ),
( NULL, 2, 2 ),
( NULL, 2, 3 ),
( NULL, 3, 4 );

一对一

案例:用户与用户详情的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

CREATE TABLE tb_USER (
id INT auto_increment PRIMARY KEY COMMENT ‘主键ID’,
NAME VARCHAR ( 10 ) COMMENT ‘姓名’,
age INT COMMENT ‘年龄’,
gender CHAR ( 1 ) COMMENT “1:男，2:女”,
phone CHAR ( 11 ) COMMENT ‘手机号’
) COMMENT ‘用户基本信息表’;
CREATE TABLE tb_user_edu (
id INT auto_increment PRIMARY KEY COMMENT ‘主键ID’,
degrele VARCHAR ( 20 ) COMMENT ‘学历’,
major VARCHAR ( 50 ) COMMENT ’ 专业 ',
primaryschool VARCHAR ( 50 ) COMMENT ‘小学’,
middleschool VARCHAR ( 50 ) COMMENT ‘中学’,
university VARCHAR ( 50 ) COMMENT ‘大学’,
userid INT UNIQUE COMMENT ‘用户ID’,
CONSTRAINT fk_userid FOREIGN KEY ( userid ) REFERENCES tb_user ( id )
) COMMENT ‘用户教育信息表’;

;
INSERT INTO tb_USER ( id, NAME, age, gender, phone )
VALUES
( NULL,‘黄渤’, 45, ‘1’, ‘188800111’ ),
( NULL,‘冰冰’, 35, ‘2’, ‘1800002222’ ),
( NULL,‘码云’, 55, ‘1’, ‘1880008888’ ),
( NULL,‘李彦宏’,50,‘1’,‘188000999’ );
);
INSERT INTO tb_user_edu (degrele,major,primaryschool,middLeschool,university,userid)
VALUES
( ‘本科’, ‘舞蹈’, ‘静安区第一小学’, ‘静安区第一中学’, ‘北京舞蹈学院’, 1 ),
( ‘硕士’, ‘表演’, ‘朝阳区第一小学’, ‘朝阳区第一中学’, ‘北京电影学院’, 2 ),
( ‘本科’, ‘英语’, ‘杭州市第一小学’, ‘杭州市第一中学’, ‘杭州师范大学’, 3 ),
( ‘本科’, ‘应用数学’, ‘阳泉第一小学’, ‘阳泉区第一中学’, ‘清华大学’, 4 );

多表查询

多表查询概述: 指从多张表中查询数据笛卡尔积.

笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况.(在多表查询时，需要消除无效的笛卡尔积)

select * from A,B;

//笛卡尔积现象,将AB两个表中的集合进行排列组合,数据量巨大.

内连接

内连接相当于查询A、B交集部分数据.

--内连接查询的是两张表交集的部分
--内连接查询语法:
--隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件.. ;
--显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件.. ;

select * from A,B on A.b_id=B.id;

select * from A join B on A.b_id=B.id;

外连接

左外连接:查询左表所有数据，以及两张表交集部分数据.
右外连接:查询右表所有数据，以及两张表交集部分数据.

--查询表1(左表)的所有数据包含表1和表2交集部分的数据
--左外连接查询语法
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件.. ;
--相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
--右外连接查询语法
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件..;

select * from A left outer join B on A.b_id=B.id;

select * from A right outer join B on A.b_id=B.id;

自连接

当前表与自身的连接查询，自连接必须使用表别名

--自连接查询，可以是内连接查询，也可以是外连接查询
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名 B ON 条件 ...;

–自连接,查询每个员工的上级

seLect a.name,b.name from emp a，emp b where a.managerid = b.id;

–外连接,查询所有人的上级

select a. name ‘员工’, b.NAME as ‘领导’ from emp a Left join emp b on a.managerid = b.id;

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集.

对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致.

SELECT 字段列表 FROM 表A ..
UNION [ALL]
SELECT 字段列表 FROM 表B

– 不去重,将两次的结果集合并

select * from emp where salary < 5000
union
seLect * from emp where age >50;

– 去重,将两次的结果集合并

select * from emp where salary < 5000
union
seLect * from emp where age >50;

子查询

概念: SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询.

SELECT * FROM t1 WHERE column1= ( SELECT column1 FROM t2 );
-- 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个.

根据子查询结果不同，分为:

标量子查询 (子查询结果为单个值),常用的操作符: =、<>、>、>=、<、<=

select id from dept where NAME=‘销售部’;

select * from emp where dept_id=(select id from dept where NAME=‘销售部’);

列子查询(子查询结果为一列,常用的操作符: IN、NOT IN、ANY、SOME、ALL

//查询销售部的所有员工信息

select id from dept where NAME=‘销售部’ or NAME='市场部 ;

select * from emp where dept_id in (select id from dept where NAME=‘销售部’);

//查询比财务部所有人工资都高的员工信息

select id from dept where name = ‘财务部’ ;
select salary from emp where dept. id = (select id from dept where name = ‘财务部’);
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = ‘财务部’) );

//查询比财务部任意一人工资高的员工信息

select id from dept where name = ‘财务部’ ;
select salary from emp where dept. id = (select id from dept where name = ‘财务部’);
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = ‘财务部’) );

行子查询(子查询结果为一行),常用的操作符: =、<>、IN、NOT IN

//查询与"张无忌”的薪资及直属领导相同的员工信息;
//1.查询"张无忌”的薪资及直属领导
select salary, managerid from emp where name = ’ 张无忌’;
//2.查询与"张无忌”的薪资及直属领导相同的员工信息;
select * from emp where (saLary , managerid) = (seLect salary, mahagerid from emp where name = ’ 张无忌|) ;

表子查询(子查询结果为多行多列)，这种子查询称为表子查询.常用的操作符: IN

//查询与"鹿杖客”，"宋远桥” 的职位和薪资相同的员工信息
//1.查询"鹿杖客”，"宋远桥”的职位和薪资
select job，salary from emp where name = ’ 鹿杖客’ or name = ‘宋远桥’ ;
//2.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
SELECT * FROM emp WHERE ( job, salary ) IN ( SELECT job, salary FROM emp WHERE NAME = ‘常遇春’ OR NAME = ‘韦一笑’);

例题

create TABLE salgrade ( grade INT, Losal INT, hisal INT ) COMMENT ’ 薪资等级表’;
INSERT INTO salgrade VALUES ( 1, 0, 3000 );
INSERT INTO salgrade VALUES ( 2, 3001, 5000 );
INSERT INTO salgrade VALUES ( 3, 5001, 8000 );
INSERT INTO salgrade VALUES ( 4, 8001, 10000 );
INSERT INTO salgrade VALUES ( 5, 10001, 15000 );
INSERT INTO salgrade VALUES ( 6, 15001, 20000 );
INSERT INTO salgrade VALUES ( 7, 20001, 25000 );
INSERT INTO salgrade VALUES ( 8, 25001, 30000 );

//查询员工的工资等级

select * from emp e,salgrade s where e.salary >= s.Losal and e.salary <= s.hisaL;

select * from emp e,salgrade s where e.salary between s.Losal and s.hisaL;

//查询所有的部门信息，并统计部门的员工人数
select d.id, d.name , ( seLect count(*) from emp e where e.dept_ id = d.id ) '人数’ from dept d;

事务

事务的SQL语句

事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体-起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败.

CREATE TABLE account ( id INT auto_increment PRIMARY KEY COMMENT ‘主键ID’, NAME VARCHAR ( 10 ) COMMENT ‘姓名’, money INT COMMENT ‘余额’ ) COMMENT ’ 账户表’;
INSERT INTO account ( id, NAME, money )
VALUES
( NULL, ‘张三’, 2000 ),
( NULL, ‘李四’, 2000 );

-- 查看/设置事务提交方式,如果是1,就是自动提交
SELECT @@autocommit ;
SET @@autocommit=0 ;
-- 开启事务
START TRANSACTION或BEGIN ;
-- 提交事务
COMMIT ;
-- 回滚事务
ROLL BACK ;

//转账操作(张三给李四转账1000)
//查询张三账户余额
select * from account where name =‘张三’ ;
//将张三账户余额-1000
update account set money = money - 1000 where name = ’ 张三’;

//将李四账户余额+1000
update account set money = money + 1000 where name = ’ 李四’;

commit;

事务的四大特性

原子性(Atomicity) :事务是不可分割的最小操作单元，要么全部成功，要么全部失败.
一致性 (Consistency) :事务完成时，必须使所有的数据都保持一致状态.
隔离性(Isolation) :数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行.
持久性(Durability) :事务一旦提交或回滚,它对数据库中的数据的改变就是永久的.

并发事务问题

事物的隔离级别

--查看事务隔离级别
SELECT @@TRANSACTION_ _ISOl ATION;
--设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL { 
	READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ| SERIALIZABLE
}

存储引擎

MySQL体系结构

连接层
- 最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案.服务器也会为安全接入的每个客户
  端验证它所具有的操作权限
服务层
- 第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询, SQL的分析和优化，部分内置函数的执行.所有跨存
  储引擎的功能也在这一层实现，如过程、函数等
引擎层
- 存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信.不同的存储引擎具有不同的功能，这样我
  们可以根据自己的需要,来选取合适的存储引擎
存储层
- 主要是将数据存储在文件系统之上,并完成与存储引擎的交互

存储引擎层

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式.存储引擎是基于表的,而不是基于库的，所以存储引擎也可被称为表类型.MySQL5.5版本之后,默认存储引擎为InnoDB.

//查看建表语句

show create table account;

CREATE TABLE account (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
NAME varchar(10) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘姓名’,
money int(11) DEFAULT NULL COMMENT ‘余额’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb COMMENT=’ 账户表’

//ENGINE为默认引擎,AUTO_INCREMENT为下一行数据的个数,DEFAULT CHARSET为默认字符集,在建表中没有指定就会使用默认

-- 在创建表时，指定存储引擎
CREATE TABLE表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段n 字段n类型[COMMENT 字段n注释]
) ENGINE = INNODB[COMMENT表注释];
-- 查看当前数据库支持的存储引擎
SHOW ENGINES ;

存储引擎特点

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎.
在InnoDB存储引擎中:

DML操作遵循ACID模型，支持事务;
行级锁，提高并发访问性能;
支持外键FOREIGN KEY约束，保证数据的完整性和正确性;
文件
XXx.ibd: xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件, 存储该表的表结构(frm、 sdi) 、数据和索引

MyISAM

MyISAM是MySQL早期的默认存储引擎.

不支持事务，不支持外键
支持表锁，不支持行锁.
访问速度快

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用.

内存存放
hash索引( 默认)
文件格式:xxx.sdi,用来存储表结构信息

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎.对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合.

InnoDB :是Mysql的默认存储引擎，支持事务、外键.如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一-致
性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择.
MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那
么选择这个存储引擎是非常合适的.
MEMORY: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存.MEMORY的缺陷就是对表的大小有限制，太大的表
无法缓存在内存中，而且无法保障数据的安全性.

索引

索引(index) 是帮助MySQL高效获取数据的数据结构(有序).在数据之外,数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构.上实现高级查找算法,这种数据结构就是索引.

假如没有索引,那么SQL语句哪怕匹配到了相对的值也要继续进行全表扫描,因为不能保证值是唯一的.

索引结构

索引是建立在存储引擎层实现的,所以会根据存储引擎的不同,索引也有不同的索引结构.主要包含以下几种.

二叉树索引

在二叉树中,假如顺序插入时,会形成一个单向链表(图左二),查询性能会大大降低,数据量大的情况下,层级较深,检索速度慢.

采用红黑树(图右一)可以缓解这一问题,但是在大数据量情况下,层级较深,检索速度慢.

B-Tree索引

插入10065 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250数据为例

假如超过四个子节点,那么向上分裂,中间值为父结点

具体动态变化的过程可以参考网站: https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree索引

只有叶子结点存放数据,其他结点主要起到索引的作用,这些叶子结点会形成一个单向链表

插入10065 169 368 900 556 780 352151200 234 888 158 90 1000 88 120 268 250数据为例

MySQL的B+Tree索引

MySQL索引数据结构对经典的B+Tree进行了优化.在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问的性能.

Hash索引结构

哈希索引就是采用-定的hash算法，将键值换算成新的hash值，映射到对应的槽位上,然后存储在hash表中.如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决.假如发生了碰撞,那么链表中查找这个元素.

Hash索引特点:

Hash索引只能用于对等比较(=，in), 不支持范围查询(between, >，<, …) .
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

在MySQL中，支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的.

为什么InnoDB存储引擎选择使用B+Tree索引结构?

相比于二叉树,层级更少,搜索效率高.

对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据,这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低.

相对Hash索引，B+tree支持范围匹配及排序操作.

索引分类

在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引.
如果不存在主键，将使用第一个唯一(UNIQUE) 索引作为聚集索引.
如果表没有主键，或没有合适的唯一索引，则nnoDB会自动生成一个rowid作为隐藏的聚集索引.

一条SQL语句的索引查询过程,称为回表过程

1.以下SQL语句，那个执行效率高?
select * from user where id= 10;
select * from user where name= ‘Arm’;
备注: id为主键，name字段创建的有索引

select * from user where id= 10;这条语句执行效率更高,因为会直接去聚集索引查询,直接拿到行数据就会返回.

select * from user where name= ‘Arm’; 会回表查询,再去聚集索引查询行数据,所以效率较差.

2.InnoDB主键索引的B+Tree高度为多高呢?

行数据大小为1k, 一页中可以存储16行这样的数据.

InnoDB的指针占用6个字节的空间，主键即使为bigint,占用字节数为8.
当高度为2时:
n*8 + (n+ 1) * 6= 16*1024 ,算出n约为1170
1171* 16= 18736
高度为3时:
1171 * 1171 * 16 = 21939856

索引语法

创建索引

--单个索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name...);
--联合索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name1,index_col_name2...);

查看索引

SHOW INDEX FROM table name ;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

SQL的执行频率

MySQL客户端连接成功后,通过show [sessionlglobal] status命令可以提供服务器状态信息.通过如下指令,可以查看当前数据库的INSERT. UPDATE、 DELETE、 SELECT的访问频次:

SHOW GLOBAL STATUS LIKE '%Com%';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long. query time,单位:秒,默认10秒)的所有SQL语句的日志.MySQL的慢查询日志默认没有开启.

-- 查看MySQL慢查询开关
show vari ables like 'slow_query_log' 
-- 需要在MySQL的配置文件(/etc/my.cnf) 中配置
-- 开启MySQL慢日志查询开关
slow_query_log=1
-- 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time= 2
-- 配置完毕之后，重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
 /var/lib/mysql/localhost-slow.log.

profile详情

慢查询日志会记录超过2s的SQL语句,小于2S的语句不会记录在慢查询日志里.这类SQL也可能需要进行优化.show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了.通过have_ profiling参数,能够看到当前MySQL是否支持profile操作.

SELECT @@have_profiling;
SELECT @@profiling;
--默认profiling是关闭的，可以通过set语句在session/ global级别开启profiling:
SET profiling= 1;
--设置后可以通过下面语句来查看.
SHOW profiles;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL 语句各个阶段的耗时情况
show profile for query query id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query id;

explain执行计划

通过SQL的执行时间来判断SQL的执行效率是粗略的,因此还有第四种方式来查看性能分析.

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序.

-- 直接在select语句之前加上关键字explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

属性值	详情
ld	select查询的序列号,表示查询中执行select子句或者是操作表的顺 (id相同，执行顺序从上到下.id不同，值越大，越先执行)
select_type	表示SELECT的类型，常见的取值为: SIMPLE ( 简单表，即不使用表连接或者子查询) PRIMARY (主查询，即外层的查询) UNION ( UNION中的第二个或者后面的查询语句) SUBQUERY (SELECT/WHERE之后包含了子查询)等
type	表示连接类型. 性能由好到差的连接类型为: NULL、system、 const、 eq_ref、 ref、 range、index、 all
possible_key	显示可能应用在这张表.上的索引，一个或多个
Key	实际使用的索引，如果为NULL,则没有使用索引
Key_ len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是-一个估计值，可能并不总是准确的
filtered	表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

索引使用

最左前缀法则

如果索引了多列(联合索引) , 要遵守最左前缀法则.最左前缀法则指的是查询从索引的最左列开始,最左边的条件必须存在并且不跳过索引中的列,如果第一个索引不存在则会全表扫描.如果跳跃某一列,索引将部分失效(后面的字段索引失效).注意只需要最左边的列存在索引,与SQL语句中的顺序无关.

范围查询

联合索引中，出现范围查询(>, <),范围查询右侧的列索引失效

explain select * from tb_user where profession= ‘软件工程’ and age> 30 and status= ‘0’;
explain select * from tb_user where profession= ‘软件工程’ and age>= 30 and status= ‘0’;

索引列运算

在索引列上进行运算操作,如函数调用,索引会失效.

字符串查询

字符串类型字段使用时,如果不加引号,索引会失效.

模糊查询

尾部模糊匹配,索引不会失效,如果是头部模糊匹配,索引会失效.

or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到.

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引.

SQL提示

SQL提示，是优化数据库的一个重要手段,简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的.MySQL会权衡相应的效率,注意use和force并非是指定索引,如果效率较差则会使用其他方式.force为强制使用索引.

//use index:建议使用某个索引
explain select * from tb_user use index(idx_user_pro) where profession= ‘软件工程;
//ignore index: 建议忽略某个索引
explain select * from tb_user ignore index(idx_use_pro) where profession=软件工程’;
//force index: 强制使用某个索引
explain select * from tb_user force index(idx_user_pro) where profession=软件工程;

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *.假如有一个数据通过索引不能查找到,还需要回表.

using index condition:
-- 查找使用了索引，但是需要回表查询数据
using where; using index:
-- 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

前缀索引

当字段类型为字符串(varchar, text等) 时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘l0，影响查询效率.此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间，从而提高索引效率.

create index idx_xxXX on table_name(column(n)) ;
-- n为字符串的前缀数量

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的.

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1 ,5)) / count(*) from tb_user;

单列索引与联合索引

单列索引:即一个索引只包含单个列
联合索引:即一个索引包含了多个列

在业务场景中，如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引，而非单列索引.

多条件联合查询时, MySQL优化器会评估哪个字段的索引效率更高,会选择该索引完成本次查询.

索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引.
针对于常作为查询条件(where) 、排序(orderby) 、分组(group by)操作的字段建立索引.
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高,使用索引的效率越高.
如果是字符串类型的字段,字段的长度较长，可以针对于字段的特点,建立前缀索引.
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表,提高查询效率.
要控制索引的数量，索引并不是多多益善，索引越多,维护索引结构的代价也就越大,会影响增删改的效率.
如果索引列不能存储NULL值, 请在创建表时使用NOT NULL约束它.当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询.

SQL语句的优化

插入数据优化

插入多数的数据时,拆分为单句插入效率低于批量插入数据.

除此之外我们还可以避免频繁事物的开启,开启手动事物提交.

在插入数据时,如果按照主键顺序插入比主键乱序插入快速.

在大批量插入数据时,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入

#客户端连接服务端时，加上参数–local-infile
mysql -local-infile -U root -p
#设置全局参数local jinfile为1,开启从本地加载文件导入数据的开关
set global local_infile= 1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile ‘/root/sql1 .log’ into table tb_user fields terminated by ‘,’ lines terminated by ‘\n’ ;

主键优化

页分裂

主键乱序会生产页分裂现象,把当前插入的主键找到一个顺序排列的位置,因为此时不能保证数据是连续的,所以会将当前页超出50%的部分复制到一个新的页,找到合适的顺序插入数据,然后原页的下一个指针指向新页的地址.

页合并

当删除一行记录时，实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用.
当页中删除的记录达到MERGE_THRESHOLD (默认为页的50%) , InnoDB会前后寻找最靠近的页看看是否可以将两个页合并以优
化空间使用.

MERGE_THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定

满足业务需求的情况下，尽量降低主键的长度.
插入数据时，尽量选择顺序插入，选择使用AUTO_ INCREMENT自增主键.
尽量不要使用UUID做主键或者是其他自然主键，如身份证号.
业务操作时，避免对主键的修改.

order by优化

Using filesort :通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序
Using index :通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序，操作效率高

group by优化

group也最好满足联合索引,以及最左前缀法则.假如不满足最左前缀法则,可能会进行回表查询.

limit优化

limit对于后面的数据进行分页,越往后效率越慢.

优化思路:

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

explain select * from tb_ sku t (select id from tb_ sku order by id limit 2000000,10) a where t.id = a.id;

count优化

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数,效率很高.

InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数.

count(主键)

InnoDB引擎会遍历整张表，把每一行的主键jd值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)
count(字段)

没有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null, 计数累加。
有not null约束: InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层，直接按行进行累加。
count(1)

InnoDB引擎遍历整张表，但不取值.服务层对于返回的每一行，放一个数字’1’进去，直接按行进行累加
count(*)

InnoDB引擎并不会把全部字段取出来，而是专门做了优化,不取值，服务层直接按行进行累加.

按照效率排序的话，count(字段) < count(主键id) < count(1)≈count(*)，所以尽量使用count(*).

update优化

更新时最好根据索引字段进行更新,当使用索引更新时,会将整行锁住,如果根据字段来更新,此时是表锁,此时其他的事务无法更新.

视图/储存过程/触发器

视图

视图的基本操作

视图(View) 是一种虛拟存在的表.视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的.

-- 创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED| LOCAL] CHECK OPTION ]
-- 查询视图
SHOW CREATE VIEW 视图名称;
-- 查看视图数据
SELEQT * FROM 视图名称...;
-- 修改视图数据
-- 方式一:
CREATE [OR REPLACE] VIEW 视图名称 [(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION]
-- 方式二:
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED |LOCAL] CHECK OPTION]
-- 删除视图
DROP VIEW [IF EXISTS] 视图名称 [视图名称] ..

-- 查询视图
show create view stu _v _1;
seLect * from stu_v_1;
seLect * from stu_v_1 where id < 3;
-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;
-- 删除视图
drop view if exists stu_v_1;

-- 添加数据,此时会向视图的基表中插入数据

insert into stu_v_1 values(30,'wjk');

-- 但是此时,我们查询视图的条件是id小于10.所以通过视图不能查询到我们的数据通过视图插入数据就没有意义,因此可以加上后面的部分`local check option`,此时会检查当前插入数据能不能满足条件,基于其他视图的条件不会检测.

CREATE OR REPLACE VIEW stu_V_1 L AS SELECT id,NAME FROM student  WHERE id <10 with local check option;

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行,例如插入,更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性.

为了确定检查的范围，mysq|提供了两个选项: CASCADED和LOCAL ,默认值为CASCADED。

-- CASCADED此时插入数据会进行校验,不仅会检查当前视图还会检查依赖的视图是否满足条件,假如后面的视图基于前面的视图,后面的视图如果没加检查,那么也会检查前面的视图是否满足条件,即使v2没有加语句`with cascaded check option`,也照样进行条件的检查，这也是级联检查的含义

CREATE OR REPLACE VIEW stu_V_1 L AS SELECT id,NAME FROM student  WHERE id <10 with CASCADED check option;

视图的更新

要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项,则该视图不可更新:

聚合函数或窗口函数(SUM()、 MIN()、 MAX()、 COUNT()等;
DISTINCT
GROUP BY
HAVING
UNION 或者UNION ALL

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件.
同时使用视图提高了安全性:数据库可以授权,但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据.保持了数据的独立性,视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的连接与传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用。

-- 创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	SQL语句...
END ;
-- 调用存储过程
CALL名称([参数]);

-- 查看存储过程

-- 查询指定数据库的存储过程及状态信息,通过INFORMATION_ SCHEMA.ROUTINES来查询

SELECT * FROM INFORMATION_ SCHEMA.ROUTINES WHERE ROUTINE_ SCHEMA= 'Xxx'; 

 -- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

-- 删除存储过程
DROP PROCEDURE  [IF EXISTS] 存储过程名称;

在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符

例如Delimiter $, 将 SQ L 语句结束符改为$

避免在MySQL命令行中使用存储过程时,遇见分号会认为SQL语句结束,会直接报错

变量

系统变量:

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面分为全局变量(GLOBAL)、会话变量(SESSION) .会话变量只会在当前会话生效,其他的会话窗口中不能生效

-- 查看系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE .... -- 可以通过lIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL].系统变量名; -- 查看指定变量的值
-- 设置系统变量
SET [SESSION | GLOBAL] 系统变量名=值;
SET @@[SESSION | GLOBAL] 系统变量名=值;

如果没有指定SESSION/GLOBAL,默认是SESSION,会话变量.
mysq|服务重新启动之后，所设置的全局参数会失效,要想不失效，可以在/etc/my.cnf中配置.

用户变量:

--赋值
SET @var_name = expr[,@var_name = expr] .. ;
SET @var_name := expr[,@var_name := expr] ... ;
SELECT @var_name = expr[,@var_name = expr] .. ;
SELECT 字段名 INTO @var_name FROM 表名;
--使用
SELECT @var_name ;

变量:用户变量
–赋值
set @myname =‘itcast’;
set @myage := 10;
set @mygender := ‘男’,@myhobby := ‘java’;
seLect @mycolor := ‘red’;
–使用
select @myname,@myage,@mygender,@myhobby;

select count(*) into @myvar from user;

局部变量:

局部变量是根据需要定义的在局部生效的变量,访问之前,需要DECLARE声明。可用作存储过程内的局部变量和输入参数,局部变量的范围是在其内声明的BEGIN … END块。

DECLARE 变量名 变量类型 [DEFAULT ... ];
-- 变量类型就是数据库字段类型: INT、BIGINT、 CHAR、VARCHAR、DATE、 TIME等.
-- 赋值局部变量
SET 变量名=值;
SET 变量名:=值;
SELECT 字段名 INTO 变量名 FROM表名.. ;

–变量:局部变量

–声明 DECLARE

–赋值
CREATE PROCEDURE p2 ()

BEGIN
DECLARE stu_count,INT DEFAULT 0;
SELECT count(*) INTO stu_ count FROM student;
SELECT stu_count;
END;
CALL p (2);

IF条件判断

IF 条件1 THEN
	SQLA...
ELSE IF 条件2 THEN
	SQLB...
ELSE
	SQLC...
END IF;

CREATE PROCEDURE p1 () BEGIN
DECLARE
score INT DEFAULT 58;
DECLARE
result VARCHAR ( 10 );
IF
score < 60 THEN SET result = ‘不及格’;
ELSE IF score >= 60 THEN
SET result = ‘及格’;
END IF;
SET result = ‘aaa’;
END IF;
SELECT result;
END;
call p1();

参数

CREATE PROCEDURE 存储过程名称([ IN/OUT/INOUT参数名参数类型])
BEGIN 
	-- SQL语句
END ;

CREATE PROCEDURE p7 (
IN score INT,
OUT result VARCHAR (10)
)
BEGIN
IF
score >= 85 THEN
SET result := ‘优秀’;
ELSEIF score >= 60 THEN
SET result := ‘及格’;
ELSE
SET result := ‘不及格’;
END IF;
END;
CALL p7(68,@retresult);
SELECT @retresult;

–将传入的200分制的分数,进行换算,换算成百分制，然后返回分数–inout
create procedure p50(inout score doubLe)
begin
set score := score * 0.5;
end;
set @score = 78;
call p50(@score);
select @score;

Case

-- 第一种方式
CASE case_value
WHEN when value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2]
[ ELSE statement_list]
END CASE;
-- 第二种方式
CASE
WHEN search_condition1 THEN statement_list 1
[WHEN search_condition2 THEN statement_list2]
[ELSE statement list]
END CASE;

create procedure p(in month int)
begin
decLare result varchar(10);
case
when month >= 1 and month <= 3 then
set result := ‘第一季度’;
when month >= 4 and month <= 6 then
set result := ‘第二季度’;
when month >= 7 and month <= 9 then
set result := ‘第三季度’;
when month >= 10 and month <= 12 then
set result := ‘第四季度’;
eLse
set result := ‘非法参数’ ;
end case ;
select concat(‘您输入的月份为:’,month,'所属的季度为: ’ , result);
end;
call p(4);

循环

while循环

while循环是有条件的循环控制语句.满足条件后，再执行循环体中的SQL语句.

具体语法为: .

#先判定条件，如果条件为true, 则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

– A.定义局部变量，记录累加之后的值;
– B.每循环一次，就会对进行减I，如知果减到0，则退出循环
create procedure p7(in n int)
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n :=n-1;
end while;
seLect total;
end;
call p7(100);

repeat循环

repeat是有条件的循环控制语句，当满足条件的时候退出循环.

具体语法为:

#先执行一次逻辑， 然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT 
	SQL逻辑..
	UNTIL 条件
END REPEAT;

-- A.定义局部变量，记录累加之后的值;
-- B.每循环一次， 就会对n进行1，如果n减到0， 则退出循环
create procedure p8(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n:= n-1;
until n <=0
end repeat;
seLect total;
end;
call p8(10);
call p8(100);

L0OP循环

L0OP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环.

LOOP可以配合以下两个语句使用:

LEAVE: 配合循环使用，退出循环。
ITERATE: 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环.

[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

-- A.定义局部变量， 记录累加之后的值;
-- B.每循环-次，就会对进行-1，如果n减到0， 则退出循环----> Leave xX 
-- C.如果当次累加的数据是奇数， 则直接进入下一次循环， -------> iterate xX
CREATE PROCEDURE p10 ( IN n INT ) 
BEGIN
	DECLARE
		total INT DEFAULT 0;
	sum :
	LOOP
		IF n <= 0 THEN
				LEAVE sum;
		END IF;
			IF n % 2 = 1 THEN
				SET n := n - 1;
				ITERATE sum;
			END IF;
		 		SET total := total + n ;
		 		SET n := n - 1;
	END LOOP sum;
	 SELECT total;
END;
CALL p10 (3)

-- loop 循环
/*loop 循环语法：
loop_name:loop
       if 条件 THEN -- 满足条件时离开循环
            leave loop_name;  -- 和 break 差不多都是结束训话
    end if；
end loop;
*/
create procedure sums(a int)
begin
     declare sum int default 0;
     declare i int default 1;
     loop_name:loop -- 循环开始
         if i>a then 
             leave loop_name;  -- 判断条件成立则结束循环  好比java中的 boeak
         end if;
         set sum=sum+i;
         set i=i+1;
     end loop;  -- 循环结束
     select sum; -- 输出结果
end;
-- 执行存储过程
call sums(100);
-- 删除存储过程
drop procedure if exists  sums;

游标

游标(CURSOR) 是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和CLOSE，其语法分别如下.

#声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
#打开游标
OPEN 游标名称;
#获取游标记录
FETCH 游标名称 INTO 变量[变量];
#关闭游标
CLOSE 游标名称;

-- 游标
-- 根据传入的参数uage，来查询用户表tb_user中,所有的用户年龄小于等Fuage的用户姓名(name) 和专业(profession) ,
-- 并将用户的姓名和专业插入到所创建的一张新 表(id, name , profession)中。
-- 逻辑:
-- A.声明游标， 存储查询结果集
-- B.准备:创建表结构
-- C.开启游标
-- D.获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标
CREATE PROCEDURE p11 ( IN uage INT ) BEGIN
#变量要在游标前
	DECLARE
		uname VARCHAR ( 100 );
	DECLARE
		upro VARCHAR ( 100 );
	DECLARE
		U_cursor CURSOR FOR SELECT NAME
		,
		profession 
	FROM
		tb_USER 
	WHERE
		age <= uage;
	DROP TABLE
	IF
		EXISTS tb_user_pro;
	CREATE TABLE
	IF
		NOT EXISTS tb_user_pro ( id INT PRIMARY KEY auto_increment, NAME VARCHAR ( 100 ), profession VARCHAR ( 100 ) );
	OPEN U_cursor;
	WHILE
		TRUE DO
			FETCH U_cursor INTO uname,
			upro;
		INSERT INTO tb_user_pro
		VALUES
			( NULL, uname, upro );
		
END WHILE;

END;

此时我们会遇到报错,原因是因为在while循环中采用了死循环.我们当前的游标数据为空时,就会报错,需要用到条件处理程序

条件处理程序

条件处理程序(Handler) 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

#具体语法为:
DECLARE handler_action HANDLER FOR condition_value [condition_value].. statement ;
handler_action
    CONTINUE :继续执行当前程序
    EXIT :终止执行当前程序
condition_value
    SQLSTATE sqlstate_value: 状态码，如02000
    SQLWARNING :所有以01开头的SQLSTATE代码的简写
    NOT FOUND :所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION: 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

-- 游标
-- 根据传入的参数uage，来查询用户表tb_user中,所有的用户年龄小于等Fuage的用户姓名(name) 和专业(profession) ,
-- 并将用户的姓名和专业插入到所创建的一张新 表(id, name , profession)中。
-- 逻辑:
-- A.声明游标， 存储查询结果集
-- B.准备:创建表结构
-- C.开启游标
-- D.获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标
CREATE PROCEDURE p11 ( IN uage INT ) BEGIN#变量要在游标前
	DECLARE
		uname VARCHAR ( 100 );
	DECLARE
		upro VARCHAR ( 100 );
	DECLARE
		U_cursor CURSOR FOR SELECT NAME
		,
		profession 
	FROM
		tb_USER 
	WHERE
		age <= uage;
	DECLARE
		EXIT HANDLER FOR SQLSTATE ' 02000' CLOSE U_CURSOR;
	DROP TABLE
	IF
		EXISTS tb_user_pro;
	CREATE TABLE
	IF
		NOT EXISTS tb_user_pro ( 
            id INT PRIMARY KEY auto_increment, 
            NAME VARCHAR ( 100 ), profession VARCHAR ( 100 ) 
        );
	OPEN U_cursor;
	WHILE
		TRUE DO
			FETCH U_cursor INTO uname,
			upro;
		INSERT INTO tb_user_pro
		VALUES
			( NULL, uname, upro );
		

END WHILE;

END;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

具体语法如下:

CREATE FUNCTION 存储函数名称 ([参数列表])
RETURNS type [characteristic ..]
BEGIN
	-- SQL语句
	RETURN... 
END;

characteristic说明:
	DETERMINISTIC: 相同的输入参数总是产生相同的结果
	NOSQL :不包含SQL语句。
	READS SQL DATA:包含读取数据的语句，但不包含写入数据的语句。

触发器

触发器是与表有关的数据库对象,指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

#创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ;
END;
#查看
SHOW TRIGGERS ;
#删除
DROP TRIGGER [schema_name.]trigger_ name; 
-- 如果没有指定schema_name,默认为当前数据库。

木鱼木心.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
一篇学会MySQL

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
复制链接

扫一扫

专栏目录