文档打包下载
1. 数据库MySQL 配置文件 my.ini
port=3306 端口
basedir="D:/Program Files/MySQL/MySQL Server 5.7/" 安装位置
datadir=D:/ProgramData/MySQL/MySQL Server 5.7/Data 数据存放位置
character-set-server=utf8 服务器编码设置
default-storage-engine=INNODB 引擎设置
说明： a.# 注释
b. [mysqld] MySQL服务器; [client] 客户端配置
c. 修改配置都要重启服务
d. show variables like 'character%'; 查看全局字符集编码
e. 执行文件配置 ： "D:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="D:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57-2
f. my.ini 保存时 默认编码： ANSI （重要）
2. 登录和退出
登录：
mysql -u用户名 -P端口 -h主机名（localhost） -D[数据库名] -p密码
说明： mysql -uroot -p 常用
没有;
退出
mysql> exit; 或 quit; 或 \q;3 DDL 操作
create (创建) alter(修改) drop(删除)
1. 创建数据库：
create database 数据库名 [character set utf8];
2. 查看数据库
show databases;
3. 查看创建信息（了解）
show create database 数据库名；
4. 删除数据库
drop database 数据库名;
5. 使用数据库
use 数据库;
6. 查看数据库有哪些表
show tables;
7. 创建表结构（简单）
create table 表名(
字段名 数据类型...,
字段名 数据类型...,
字段名 数据类型...
);
8. 查看表结构
desc 表名;
descible 表名;
show columns from 表名;
9. 查看创建表信息（了解）
show create table 表名；
10. 数据库中的数据的增删改查（简单 DML,DQL）
a.查看表中的数据
select * from 表名;
b.插入数据
insert [into] 表名(字段名,字段名...) value[s]
(字段值,字段值...),
(字段值,字段值...),
(字段值,字段值...);
c.更新数据
update 表名 set 字段=字段值 [where id=2 条件];
d. 删除数据
delete from 表名 [where id=2 条件];
说明：
help create database;4. MySql中的数据类型:
整型
TINYINT[(M)] [UNSIGNED|ZEROFILL]:(存储大小1字节 2^8) 1024Byte =1KB
范围：无符号位 0~255
有符号位 -128~127
SMALLINT[(M)] [UNSIGNED|ZEROFILL]：(存储大小2字节 2^16)
范围：无符号位 0~65535
有符号位 -32678~32677
MEDIUMINT[(M)] [UNSIGNED|ZEROFILL]：(存储大小3字节 2^24)
范围：无符号位 0~16777215
有符号位 -8388608~8388607
INT[(M)] [UNSIGNED|ZEROFILL]:(存储大小4字节 2^32)
范围： 无符号位 0~4294967295
有符号位 -2147483648~2147483647
BIGINT[(M)] [UNSIGNED|ZEROFILL]:(存储大小8字节 2^64)
说明：
UNSIGNED：无符号位 (正值)
ZEROFILL:填补0，结合数字长度使用
当添加的数字长度小于指定长度填补0
自动添加UNSIGNED.浮点型
FLOAT[(M,D)][UNSIGNED|ZEROFILL]
单精度浮点 范围：-3.4e38~3.4e38
DOUBLE[(M,D)][UNSIGNED|ZEROFILL]
双精度浮点 范围：-1.79e308~1.79e308
DECIMAL[(M,D)][UNSIGNED|ZEROFILL]
没误差存储浮点数
说明：M总长度 ，D小数点之后位数 字符型
CHAR[(M)]:定长字符，范围至多255字节(一个字母占一个字节)
VARCHAR[(M)]:变长字符，
范围受一下条件影响：
(1)范围至多65535字节
(2) gbk:至多2字节，65532/2
uf8:至多3字节，65532/3=21844
(3)字段中的所有CHAR, VARCHAR总长度不超多65532/3=21844
所以 : varchar(255)
TINYTEXT:至多255字节
TEXT:至多65535字节 （重要 文章字段）
MEDIUMTEXT:16777215字节
LONGTEXT:4294967295字节
blob 二进制类型， 图像视频 65k
说明：CHAR效率最高,不省空间
VARCHAR效率低,省空间
TEXT 效率最低枚举型
enum 最多能存65535个字节
例如： sex enum('man','women','secret')时间日期类型
year 1字节
time 3 字节
date 4 字节
datetime 8 字节 （没有时间限制）
timestamp 4字节 时间戳( a. 有时间限制 1970-01-01-2038-01-19 转为描述 2147483647描述停止
b. 时区) 5. DDL 中 alter 的使用
1.modify 修改
alter table 表名 modify 字段 修改的字段属性... [after 字段| first]2.change 改字段名
alter table 表名 change 字段名 新字段名 字段属性... [after 字段| first]
说明：注意 ： primary key , unique key foreign key 不能用 modify change修改， 查看语法（了解）3. add 添加字段
alter table 表名
add 字段 字段属性... [after 字段| first],
add 字段 字段属性... [after 字段| first]...4.drop 删除字段
alter table 表名 drop 字段名,drop 字段名...5.表的重命名
alter table 表名 rename [to] 新表名。6. DML和DQL
1. 插入(insert)
insert [into ] 表名 (字段1,字段2,....) value[s] (字段值,字段值....),(字段值,字段值....)...
说明： 如果没写字段那么所有字段值按顺序必须都写。2. 更新（update）
update 表名 set 字段=字段值,字段=字段值,... [where 条件]
说明： age 字段值年龄都增加1
update admin set age = age + 1;3. 删除（delete）
delete from 表名 [where 条件]
说明： delete 清空数据后不能重置 auto_increment.
truncate 清空数据后能重置 auto_increment.4. 查询（select）
(1) SELECT * FROM 表名
说明：效率低
(2) SELECT 字段名称|expr,
字段名称|expr... FROM 表名
(3) 给字段起别名
SELECT 字段名称|expr [AS]别名
FROM 表名;
(4) 给表起别名
SELECT 字段名称|expr FROM
表名 [AS] 表别名;
(5) 表.字段名称 (说明字段来自于哪个表)
SELECT 表.字段名称|expr FROM
表名
(6) 库名.表（表来自于哪个数据库）
SELECT 字段名称|expr
FROM 库名.表名;
(7)
SELECT 字段名称 FROM 表名
[WHERE 条件]|
[GROUP BY 字段名称]|
[HAVING 条件]|
[ORDER BY 字段名称]|
[LIMIT OFFSET,LENGTH]
--[WHERE 条件]:过滤条件查询
a.比较运算符 = > >= < <=
!= <> <=>(判断null值)
b.IS [NOT] NULL 判断null值
c.&& ,AND 逻辑与
d.|| ,OR 逻辑或
e. [NOT]BETWEEN ...AND 范围值
f. [NOT] IN (值,值...) 指定值
g. [NOT] LIKE string [ESCAPE string]
说明：模糊查询
关键字：
%：代表0个或1个或多个字符
_: 代表1个字符
--[GROUP BY 字段名称]：将某个字段中的相同值分
为一组 实现分组效果，每组中相同的
值只显示一个值
说明:一般 GROUP BY会结合聚合函数一起使用
COUNT: 获得每组个数
COUNT(字段名称):不包含null值
COUNT(*):包含null值（常用，效率高）
AVG(字段名称): 获得每组中平均值
MAX(字段名称): 获得每组中最大值
MIN(字段名称): 获得每组中最小值
SUM(字段名称): 获得每组中的和
-- [HAVING 条件]：对分组后的信息二次过滤
说明:[HAVING 条件]必须和 GROUP BY
一起使用
-- [ORDER BY 字段名称]:排序
ORDER BY 字段名称
[ASC升序默认值|DESC 降序]
--[LIMIT OFFSET,LENGTH]
说明：
a. OFFSET:偏移量，
起始编号，编号从0开始
b. LENGTH:显示的条数（记录数）
c.作用：实现WEB页面的分页效果
计算每页中的起始编号
OFFSET:($curpage-1)*$pagesize
(当前页-1)*显示的条数5.多表联合查询
多表操作
1.多表连接查询
SELECT 表.字段名称,表.字段名称...
FROM 表1
连接类型 表2 ON 条件
连接类型 表3 ON 条件...
连接类型：
(1)内连接: [INNER] JOIN 查找符合条件的信息
条件分类：
等值 (常用)
非等值
自连接(菜单级联)
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
(2)外连接：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
2.子查询
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：将子查询的结果生成一个新表，必须写别名。（也可以是 inner join后）；支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询 （多行） √
行子查询
exists后面（相关子查询）表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
#一、where或having后面
/*
1、标量子查询
2、列子查询
3、行子查询（结果集有一行多列）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
说明：
= any/some 等同 in
> >= any/some（大于最小值） 任意
> >= all (大于最大值) 所有
< <= any/some（小于最大值）
< <= all (小于最小值)
7. 外键约束
一.约束(CONSTRAINT)
1.约束：实现对字段的非空，唯一，完整性的约束。
2.约束分类
(1)按功能分
NOT NULL(非空)
DEFAULT (默认值)
PRIMARY KEY (主键)
UNIQUE KEY (唯一)
FOREIGN KEY (外键)
(2)按约束字段个数分
a.列约束:在字段后面实现约束
NOT NULL ,DEFAULT
必须用列约束实现
b.表约束:对两个字段或两个字段以上
实现的约束PRIMARY KEY ，
UNIQUE KEY，FOREIGN KEY
可以实现表约束
(3)约束格式
a.列约束
CREATE TABLE user(
id SMALLINT UNSIGNED PRIMARY KEY,
name VARCHAR(30) NOT NULL UNIQUE KEY
);
b. 表约束
CREATE TABLE user(
id SMALLINT UNSIGNED
name VARCHAR(30) NOT NULL,
PRIMARY KEY(id),
UNIQUE KEY(name)
);
说明：如果对两个字段或两个字段以上
添加约束，同时为主键PRIMARY
KEY 必须用表约束
(4)FOREIGN KEY (外键)
1.实现对两个表的字段完整性和一致性约束
2.
FOREIGN KEY (外键列名称) REFERENCES 参考表名称(字段名称)
说明：
a.MYSQL引擎为 InnoBD
alter table employ engine=InnoDB;
b.数据类型必须一致，如果是整型时
大小,UNSIGNED 必须一致，如果字符
型长度可以不同，但编码必须一致
c.外键列如果不是索引，MYSQL引擎
会自动将外键列定义为索引
d.外键列(子表)添加信息，参考表必须有相应信息，
参考表不能删除/更新在子表中已经使用的外键值。
3 完整格式：
FOREIGN KEY (外键列名称)
REFERENCES 参考表名称(字段名称)
[ON UPDATE RESTRICT|CASCADE|SET NULL|NO ACTION
ON DELTED RESTRICT|CASCADE|SET NULL|NO ACTION]
说明：
a.RESTRICT:等同NO ACTION
参考表(父表)不能更新/删除外键表
(子表)中被应用的字段值
b.CASCADE(级联)：参考表(父表)
参考表(父表)更新/删除外键表(子表)也更新或删除相应的信息
c.SET NULL:参考表(父表)
更新/删除将外键表(子表)中
更新或删除相应信息设置为NULL
外键表(子表)的字段必须允许为NULL
即不能为NOT NULL8.数据库设计三范式
1.第一范式：要求有主键，并且要求每一个字段原子性不可再分
2.第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
3.第三范式：所有非主键字段和主键字段之间不能产生传递依赖
一对一：
1.第一种方案：分两张表存储，共享主键
2.第二种方案：分两张表存储，外键唯一
一对多：
1.分两张表存储，在多的一方添加外键，
2.这个外键字段引用一方中的主键字段
多对多：
1.分三张表存储，在学生表中存储学生信息，在课程表中存储课程信息，
2.在学生成绩表中存储学生和课程的关系信息9.MySql中的运算符和函数
MySql中的运算符和函数 ,说明：用 select 显示结果
1.算术运算：+ - * / % mod
数学函数
pow(number)|power(number)求幂运算
sqrt(number)平方根
ceil(number)向上取整
floor(number)向下取整
round(number,m)四舍五入
rand() 随机数 0<=x<1
2.字符函数
concat(string,string...):连接字符串
concat_ws(separator,string,string...):连接字符串
lower(string)/upper(string):小/大写
trim(string)/ltrim(string)/
rtrim(string)去除左右两边空格
substr(string,start,length)/
substring(string,start,length)
字符串截取（字符从1开始编号）
replace(string,search,replacement)
字符串替换
length(string) 获得字符串长度
3. 信息函数
version():返回版本
user()：返回登录用户信息
now()：返回时间日期
database()：返回当前数据库信息
last_insert_id():当字段中有auto_increment时使用
返回插入记录中的最后一个id号，如果是多行信息插入只返回
插入记录的第一个id号
row_count():增/删/改的影响行数
found_rows():返回所有查询到的行数
4.聚合函数
count():每组中的条数
avg():求平均值
max():最大值
min():最小值
sum():求和
5.加密函数
password()
md5()10.索引
1. 索引概述
2. 分类
普通索引 ， 唯一索引， 主键索引, 外键索引，全文索引fulltext
查看索引
desc employee;
show create table employee;
3.普通索引
创建普通索引3种方法
(1) 创建索引CREATE INDEX indexName ON employee(name[(length)]);
create index index_sex on employee(sex);
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length
(2) 修改表结构(添加索引)ALTER table employee ADD INDEX indexName2(name);
alter table employee add index index_age(age);
(3) 创建表的时候直接指定
CREATE TABLE myta1(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX index_username(username)
);
(4) 删除索引的语法 DROP INDEX [indexName] ON mytable;
drop index index_sex on employee;
alter table employee drop index index_age;
4.唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一：
(1)创建索引CREATE UNIQUE INDEX indexName ON employee(name(length));
create unique index unique_username on myta1(username);
(2) 修改表结构ALTER table employee ADD UNIQUE [indexName] (name(length));
alter table myta1 add unique unique_id(id);
(3)创建表的时候直接指定
CREATE TABLE myta2(
ID INT NOT NULL primary key,
username VARCHAR(16) NOT NULL,
UNIQUE(username)
);
(4) 删除唯一索引
drop index username on myta2;
alter table myta1 drop index unique_id;
5.主键索引
添加主键索引
alter table myta1 add primary key(id);
删除主键
alter table myta1 drop primary key;
注意： 如果主键是自增的列,应该先取消自增,才能删除,ALTER TABLE employee MODIFY id int;
6.外键索引
添加外键 [constraint employee_fk1]
alter table employee add foreign key(job_id) references job(id);
参考表 id 必须主键，参考表和子表的外键类型必须一致
alter table employee modify job_id int unsigned not null;
删除外键： 注意: 先删除外键约束关系,然后删除Mysql添加的对应索引
(1) 解除外键约束关系
alter table employee drop foreign key employee_fk1;
(2) 删除索引
alter table employee drop index employee_fk1;
注意（重要重要） 上面所有 查询 like范围值 索引不起作用
7.全文索引FULLTEXT (将来有第三方技术实现中文全文索引)
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
注意：如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
8.组合索引
CREATE TABLE myta4(
ID INT NOT NULL,
col_a VARCHAR(16) NOT NULL,
col_b VARCHAR(16) NOT NULL,
col_c VARCHAR(16) NOT NULL,
INDEX abc_index (col_a,col_b,col_c)
);
组合索引遵循最左优先原则
索引方法是Btree，树状的，搜索时需要从根节点出发，上层节点对应靠左的值，所以有最左优先原则。
未使用索引的情况
--where col_b = "some value"
--where col_c = "some value"
--where col_b = "some value" and col_c = "some value"
--where col_c = "some value" and col_b = "some value"
9.explain sql语句（select） ： 执行计划，可以分析查询语句的信息 \G
explain select name,salary from employee where id between 2 and 5\G;11.grant,revoke及导入导出
1. grant 权限 on 数据库名.表名 to 用户名@登陆方式 【identified by 'password1'】;
案例1：
（1）创建用户
CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456';
（2）赋予权限
GRANT ALL ON company.* TO 'jerry'@'localhost'
（3）查看用户权限
SHOW GRANTS FOR jerry@localhost;
(4) 在 root 用户下查看所有 有哪些用户
SELECT User, Host FROM mysql.user;
案例2 ： 更新用户密码
use mysql；
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
2. revoke 权限 on 数据库名.表名 from 用户名@登陆方式；
案例3：
REVOKE ALL ON company.* from jerry@localhost;
3. 数据库的导入导出：
案例4：
mysqldump -u root -p company > d:/companyback.sql
mysql -uroot -p mytest< d:/companyback.sql
12.视图
二、视图
-- 1.什么是视图：数据库视图被称为“虚拟表”，允许您查询其中的数据。
--2. 格式: CREATE VIEW <视图名> AS <SELECT语句>
-- （1） 创建视图
create view myview1 as select name,salary from employee;
-- (2) 查询视图
select * from myview1;
select * from myview1 where salary between 3000 and 5000;
--【案例 1】查询姓名中包含a字符的员工名、部门名和工种名
#①创建
CREATE VIEW myview3
AS
SELECT e.name,d.department_name,j.job_name
FROM employee e
JOIN department d ON e.department_id = d.department_id
JOIN job j ON j.job_id = e.job_id;
#②使用
SELECT * FROM myview3 WHERE name LIKE '%a%';
#二、视图的修改
#方式一：
/*
create or replace view 视图名
as
查询语句;
*/
create or replace view myview3
as select name from employee;
#方式二：
/*
语法：
alter view 视图名
as
查询语句;*/
alter view myview3
as select salary from employee;
#三、删除视图
/*
语法：drop view 视图名,视图名,...;
*/
drop view myview3;
#四、查看视图
DESC myv1;
SHOW CREATE VIEW myv1;
#五、视图的更新（了解）
#具备以下特点的视图不允许更新数据
#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all13事务
事务的四个特性是: 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)，简称ACID。
# 1.原子性说的是数据要么一起成功，要么一起失败，即事务提交（commit）和事务回滚（rollback）。
# 2.数据一致性
# 3.隔离性的体现，是多个并发事务之间是隔离的，张三给李四转账，如果事务没有提交的话，那么在另外一个事务中并不能查看另外一个事务未提交的数据
# 4.一个事务一旦提交，它对数据库中数据的改变就应该是持久性的，接下来的其他操作或故障不应该对其有任何影响。
# 一旦提交commit 不能改为 回滚rollback（持久性的）
show variables like 'autocommit';
--步骤1：开启事务
--set autocommit=0;
--start transaction;可选的
--步骤2：编写事务中的sql语句(select insert update delete)
--语句1;
--语句2;
--...
--步骤3：结束事务
--- 逻辑: 得sql的result 结果集 if(result){commit;提交成功}else{rollback;回滚失败}
--commit;提交事务
--rollback;回滚事务
#1.演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE employee SET salary = 8000 WHERE name='tom';
UPDATE employee SET salary = 5001 WHERE name='alice';
#结束事务: 二选一
ROLLBACK;
#commit;
#2.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM employee WHERE id=5;
SAVEPOINT a;#设置保存点
DELETE FROM employee WHERE id=8;
ROLLBACK TO a;#回滚到保存点
--事务的隔离级别：
-- 脏读 不可重复读 幻读
-- read uncommitted（读取未提交内容）： √ √ √
-- read committed（读取提交内容）： × √ √ ORCALE默认
-- repeatable read（可重读）： × × √ MySQL默认
-- serializable （可串行化） × × ×
--这三者都是数据库事务的错误情况。
--1、脏读：事务A读到了事务B未提交的数据。也就是说,当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。
--2、不可重复读：事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到row1，但内容发生了变化。
--3、幻读：事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到两行记录row1和row2。
--mysql中默认 第三个隔离级别 repeatable read
--oracle中默认第二个隔离级别 read committed
--查看隔离级别
select @@transaction_isolation ;
--设置隔离级别
--set session|global transaction isolation level 隔离级别;14触发器
触发器: 实现由一些表事件触发的某个操作，是与数据库对象表关联最紧密的数据库对象之一。在数据库系统中，当执行表事件时，则会激活触发器，从而执行其包含的操作。
1.创建触发器
创建有一条执行语句的触发器
例子1:
创建表tab1
CREATE TABLE tab1(
tab1_id varchar(11)
);
创建表tab2
CREATE TABLE tab2(
tab2_id varchar(11)
);
创建触发器:t_afterinsert_on_tab1
作用：增加tab1表记录后自动将记录增加到tab2表中
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
insert into tab2(tab2_id) values(new.tab1_id)
END
测试一下
INSERT INTO tab1(tab1_id) values("0001")
例子2:
创建触发器:t_afterdelete_on_tab2
作用：删除tab1表记录后自动将tab2表中对应的记录删去
CREATE TRIGGER t_afterdelete_on_tab2
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
delete from tab2 where tab2_id=old.tab1_id
END
测试一下
DELETE FROM tab1 WHERE tab1_id="0001"
例子3:
创建触发器:t_afterdelete_on_tab3
作用：删除tab1表记录后自动将tab2表中对应的记录修改
CREATE TRIGGER t_afterdelete_on_tab1
AFTER update ON tab1
FOR EACH ROW
BEGIN
update tab2 set tab2_id = new.tab1_id where id = new.id
END
测试一下
update tab1 set tab1_id=('2') where id=1;
2.查看触发器
SHOW TRIGGERS 语句查看触发器show triggers \G;
3.删除触发器
通过DROP TRIGGER语句删除触发器drop trigger tri_diarytime2;
15.变量
系统变量：
全局变量
会话变量
自定义变量：
用户变量
局部变量
#一、系统变量
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
#1》全局变量
#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
#2》会话变量
/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量
SHOW SESSION VARIABLES;
#二、自定义变量
#1》用户变量
/*
作用域：针对于当前会话（连接）有效，作用域同于会话变量
*/
#2》局部变量
/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
#案例：声明两个变量，求和并打印
#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
#用户变量和局部变量的对比
作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 加@符号，不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@, 需要指定类型16.存储过程
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
#注意：
/*
1、参数列表包含三部分
参数模式 参数名 参数类型
举例：
in stuname varchar(20)
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/
#一、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END
#调用语法
CALL 存储过程名(实参列表);
delimiter $
#1.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据根据员工用户名(比如tom),查找对应的员工名称，薪水，部门名
CREATE PROCEDURE myp1(IN username VARCHAR(20))
BEGIN
SELECT e.name,e.salary,d.department_name
FROM employee as e
INNER JOIN department as d ON e.department_id = d.department_id
WHERE e.name=username;
END $
#调用
CALL myp1('tom')$
#2 ：创建存储过程实现，用户是否登录成功 procedure
CREATE PROCEDURE myp2(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result#赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp2('张三','8888')$
#3.创建out 模式参数的存储过程
#案例1：根据输入的员工信息，返回对应的部门名称
CREATE PROCEDURE myp3(IN userName VARCHAR(20),OUT departmentName VARCHAR(20))
BEGIN
SELECT d.department_name INTO departmentName
FROM employee as e
INNER JOIN department as d ON e.department_id = d.department_id
WHERE e.name=userName;
END $
CALL myp3('张三',@dname)$
SELECT @dname;
#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp4(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp4(@m,@n)$
SELECT @m,@n$
#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×
#四、查看存储过程的信息
DESC myp2;×
SHOW CREATE PROCEDURE myp2;
#函数
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果
*/
#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
/*
/*
注意：
1.参数列表 包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记
*/
#二、调用语法
SELECT 函数名(参数列表)
#1.无参有返回
#案例：返回公司的员工个数
drop function myf1;
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c#赋值
FROM employee;
RETURN c;
END $
SELECT myf1()$
#2.有参有返回
#案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(dName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;#定义用户变量
SELECT salary INTO @sal #赋值
FROM employee
WHERE name = dName;
RETURN @sal;
END $
SELECT myf2('tom') $
#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ;
SELECT AVG(salary) INTO sal
FROM employee e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myf3('部门1')$
#三、查看函数
SHOW CREATE FUNCTION myf3;
#四、删除函数
DROP FUNCTION myf3;
17.流程控制语句
#流程控制语句
#一、分支结构
#1.if函数
/*
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面
*/
select if(1=1,'ok','error');
#1.
/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if
只能应用在begin end 中
*/
#2.case结构
/*
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end
情况2：
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end
*/
# if结构多分支语句
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
delimiter $
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
delimiter ;
SELECT test_if(87);
# case 结构的分支
delimiter $
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
case
when score>90 then set ch='A';
when score>80 then set ch='B';
when score>60 then set ch='C';
else set ch='d';
end case;
RETURN ch;
END $
delimiter ;
SELECT test_case(87);
#二、循环结构
/*
分类：
while、loop、repeat
循环控制：
iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于 break，跳出，结束当前所在的循环
*/
#1.while
/*
语法：
【标签:】while 循环条件 do
循环体;
end while【 标签】;
*/
#2.loop
/*
语法：
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/
#3.repeat
/*
语法：
【标签：】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
*/
#案例1：批量插入，根据次数插入到admin表中多条记录
delimiter $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,password) VALUES(CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;
END $
delimiter ;
CALL pro_while1(100);
#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
delimiter $
trancate admin;
CREATE PROCEDURE test_while2(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,password) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
delimiter ;
CALL test_while2(100);
# 练习
--1.存储过程实现：返回公司的员工个数
delimiter $
create procedure mypr8(out count int)
begin
select count(*) into count
from employee;
end$
delimiter ;
call mypr8(@count);
select @count;
-- 2.分支语句实现:创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
delimiter $
CREATE PROCEDURE t_if_pro(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employee WHERE employee.salary=sal;
ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employee SET salary=salary+1000 WHERE employee.`salary`=sal;
ELSE UPDATE employee SET salary=salary+500 WHERE employee.`salary`=sal;
END IF;
END $
delimiter ;
CALL t_if_pro(3200);
--3.循环使用iterate：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin;
DROP PROCEDURE test_while1;
delimiter $
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
delimiter ;
CALL test_while1(100);18.动态程序语言操作数据库
-- Web程序开发语言: javaweb, node.js(javascript v8引擎上), php, asp.net,python
-- 开发模式: (1)模板引擎渲染， (2)前后端分离的 json接口（后端操作数据库返回json接口，前端项目ajax,后端管理界面ajax--vue，react框架实现）
-- node.js 来操作数据库(操作数据库的函数)
-- 1. 安装node.js https://nodejs.org/zh-cn/download/
-- cmd 查看 node -v 版本
node -v
-- 2. cmd 查看 npm -v （装node.js 自带命令工具)
npm -v
-- 3. 将node.js 的服务器换成 taobao源的国内服务器
-- 安装了 cnpm
npm install -g cnpm -registry=https://registry.npm.taobao.org
-- 4. 安装 mysql 模块 (注意:局部安装在一个文件夹中安装，装的模块在node_modules中)
cnpm install mysql (或) npm install mysql
#数据库操作( CURD )
--1. 查询数据库 user表中的所有信息
[/lv]
扫描二维码，在手机上阅读
推荐阅读：
版权所有：伸手党盘
文章标题：MySql上课笔记
文章链接：https://ssdpan.cn/?post=47
本站文章来源于网络搜集和原创，仅供学习使用，未经授权请勿用于任何商业用途，如有侵权及时联系删除
文章标题：MySql上课笔记
文章链接：https://ssdpan.cn/?post=47
本站文章来源于网络搜集和原创，仅供学习使用，未经授权请勿用于任何商业用途，如有侵权及时联系删除
