Mysql基础语法_slwoby-CSDN博客

本文链接：https://blog.csdn.net/weixin_42149982/article/details/106255688

windows平台下下载

http://dev.mysql.com/downloads/mysql

my.ini文件作用

更改mysql的配置信息

mysql安装

# 安装url: https://blog.csdn.net/a774630093/article/details/79270080
# 卸载mysql
1.卸载 先停掉mysql进程   没有安装过的可以直接跳过
kill -9 mysqld
rpm -qa|grep -i mysql
yum -y remove mysql-community-client-5.6.38-2.el7.x86_64
卸载不掉的用 rpm -ev 
依次卸载 直到没有

mysql的启动和关闭

net start mysql # 自己其的mysql名字
net stop mysql

登录mysql方式

mysql [-h 主机名用户名(ip) -P端口号] -u用户名 -p密码

MySQL DQL

mysql函数汇总

1.字符函数

# 1.字符函数
concat：连接
substring：截取字符串
upper：变大写
lower：变小写
replace：替换
length：获取字节长度
trim：去前后空格
lpad：左填充
rpad：右填充
instr：获取字符串第一次出现索引

2.数学函数

# 2.数学函数
ceil：向上取整
floor：向下取整
round：四舍五入
mod：取模
truncate：截断
rand：获取随机数，返回0-1之间的小数

3.日期函数

# 3.日期函数
now：返回当前日期+时间
year：返回年
month：返回月
monthname:以英文形式返回月
day：返回日
hour：返回小时
minute：分钟
second：秒
date_format:将日期转换成字符
curdate：返回当前日期
str_to_date:将字符转换成日期
curtime:返回当前时间
datediff:返回两个日期相差的天数

4.其它函数

# 4.其它函数
version： 当前数据库服务器的版本
database： 当前打开的数据库
user：当前用户
password("字符")：返回该字符的密码形式
md5("字符")：返回该字符的md5加密形式

5.流程控制函数

# 5.流程控制函数
if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
# case 情况1
case 变量或表示式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
# case 情况2
case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end

6.聚合函数

# 6.聚合函数
max 最大值
min 最小值
sum 求和
avg 平均值
count 计算个数

7.分组查询，group by

# 7.分组查询，group by
select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】# 分组前的筛选，查询想要的分组结果
group by 分组的列表
【having 筛选条件】  # 分组后的筛选，即就是把分组的结果查询出来了，在做处理 
【order by 子句】
# 分组查询中的筛选条件分为两类
			数据源           位置                    关键字
分组前筛选    原始表           group by子句的前面       where
分组后筛选   分组后的结果集     group by子句的后面       having

8.连接查询

select 字段1，字段2 from 表1，表2 where 条件1 and 条件2
sql91
# 等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 表1.key = 表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
# 非等值连接
# 自连接

sql99语法
select 查询列表
from 表1 别名 【连接类型】inner(取交集),left(左连接)
join 表2 别名
on 连接条件
【where筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接：inner
外连接： 左外*：left 【outer】
		右外*：right 【outer】
		全外：full 【outer】
交叉连接：cross

9.子查询

where或having后面：（主要）
		标量子查询（主要）结果集只有一行一列
		列子查询（主要）结果集只有一列多行
# 案例：# 标量子查询
SELECT * 
FROM employees 
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = "Abel"
);

10.分页查询

limit 起始位,条数

11.全局匹配concat

SELECT * FROM 表名 WHERE CONCAT(字段1，字段2，...) LIKE '%a%' AND CONCAT(字段1，字段2，...) LIKE '%b%';

12 查询字段为空和不为空的情况

select * from 表名 where (cluster_name in %s OR %s=''); % ([元素...],[元素...][0])
# 这条件里面必定会成立一条

mysql语句

# 查看所有的表名
show tables from mysql;

# 查看所属那个库
select database();

# 创建数据库
create database python01 charset=utf8;

# 创建表
-- 创建 classes 表(id、name)
create table classes (id int unsigned primary key auto_increment,name varchar(30));
create table classes (id int unsigned primary key auto_increment,name varchar(30)) engine=inndb;
InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁;
如果一个表只做查询的，推荐使用MyISAM

# 添加索引
ALTER TABLE <表名> ADD INDEX (<字段>)
# 来尝试为test中t_name字段添加一个索引
alter table test add index(t_name);

# 彻底删除数据
delete from 表名 where ...
optimize table 表名

# 对id进行初始化,清空数据
truncate table 表名  # 清空所有的id,即清空所有的数据

# 插入数据
insert into 表名(字段1，字段2,...) values(xx,yy,...)
# 多行插入,一般使用关联查询,不要values
insert into 表名(字段1，字段2,...) 
select name,age,... from 表名

# 更新
update 表名 set name="zs",age=20 where id=1
# 多行更新 不需要 select * from 
update (goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;

# 添加字段
alter table 表名 add name varchar(100)
alter table event_data add event_id int
# 修改字段
alter table 表名 change old new 约束条件
alter table event_data change content content text

mysql查询语句格式

语法：
select 查询列表 from 表名;

1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的记过是一个虚拟的表格

# 去重 查询总共有多少部门编号，只是查询动作
select distinct department_id from employees;

mysql函数

concat 拼接字符串

# + 
运算符
select "123"+90; 只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
如果转换失败，则将字符型数值转换成0
select null+90; 只要其中一方为null，则结果肯定为null

# concat
select concat("a","b") as 结果；

ifnull 空

# ifnull(a,"b")  如果 a 为null，那么把它改为b
select concat(first_name,ifnull(last_name,"zs")) from employees;

转义——escape

# 名字第二个为 _  模糊查询里的 _ 表示代替任何一个字符
select * from employees where first_name like "_\_%"  # \ 进行转义
select * from employees where first_name like "_$_%" escape "$";  # 告诉mysql $为转义字符

isnull 判断是否为null

判断某字段或表达式是否为null，如果是，则返回1，否则返回0

排序——order by … limit

select * from employess order by name asc, age desc limit 10;
# order by 一般都放在sql最后，limit除外

length() 长度，字节数

select * from employess order by length(name);

upper()、lower() 大小写

select upper("zs");

substr() 、substring()、instr()、left() 截取

# substr(字符, 起始位置，字符长度) ，不加字符长度，默认到最后
select substr("李莫愁爱上了陆展元",1,3);  # 结果为：李莫愁

# instr 返回子串第一次出现的索引，如果找不到返回0
select instr("杨不悔爱上了殷六侠","殷六侠") as out_put;  # 结果为 7

# left(s,n) 函数返回字符串的前n个字符
select left("mrbccd",2)
# 结果为：mr

trim() 去掉两边空格

select trim("   张翠山   ") as out_put;  # 结果为：张翠山
select trim("a" from "aaaa张a翠a山aaa")  # 结果为：张a翠a山

lpad() 左填充、 rpad() 右填充

# lpad(字符, 字符长度, 填充的字符)
select lpad("a",3,"b");  # 结果为：bba

replace() 替换

# replace(字符，原字符内容，新字符内容)
select replace("张无忌爱上了周芷若","周芷若","赵敏");

insert() 替换

insert(s1,x,len,s2)  # s1表示原内容，x表示位置，len表示长度，s2表示替换的新内容
select insert("mrkj",3,2,"book")
# 结果为：mrbook

条件判断函数case when和if

CASE WHEN expr1 THEN v1 WHEN expr2 THEN v2...ELSE vn END
case 表示函数开始，end表示函数结束.如果表达式expr1成立，则返回v1的值；如果表达式expr2成立，则返回v2的值，依次类推，最后遇到else时，返回vn的值
SELECT id,grade,CASE WHEN grade > 100 THEN "very good" WHEN grade <100 and grade>=100 THEN "Popularly" ELSE "Not Good" END level FROM tb_bccd;
# 例子
case when a.color="red" then "超警戒" when a.color="yellow" then "超关注" end,
case when a.color="red" then "警戒值" when a.coloe="yellow" then "关注值:" end,
case when a.coloe="red" then round(b.red_vals,3) when a.color="yellow" then round(b.yellow_vals,3) end

# IF(expr,v1,v2)
如果表达式expr成立，则执行v1;否则执行v2

group by分组

select 分组的格式 from 表名 ... group by 分组的格式
# 例如
select substr(create_time,1,6),max(vals) from prometheus_imp where create_time >= "2018-11-15 07:00:00" and create_time <= "2018-11-15 09:00:00" and metrics = "osdIOPS" groub by substr(create_time1,16)
# 显示分组信息
group_concat(表头)

复制表

# 加这个where条件就是不需要原表里的数据
CREATE TABLE log_data_temp AS SELECT * FROM log_data WHERE 1=2;

数学函数

round 四舍五入

select round(1.56)  # 结果为：2
# 保留两位小数
select round(1.567,2)  # 结果为：1.57

cell 向上取整

# 返回>=该参数的最小整数
select ceil(1.22)  # 结果为:2
select ceil(-1.22)  # 结果为：-1

floor 向下取整

# <=该参数的最小整数
select floor(9.99)  # 结果为：9
select floor(-9.99)  # 结果为：-10

truncate 截断

# 里面的1表示是截断1个小数
SELECT TRUNCATE(2.69,1)  # 结果为：2.6

mod 取余

SELECT MOD(-10,3)  # 结果为：-1  余数的正负取决于 第一个参数
mod(a,b)  # 底层计算为： a-a/b*b

日期函数

# now() 返回当前系统日期+时间
SELECT NOW()
# curdate()返回当前系统的日期，不包含时间
SELECT CURDATE()
# curtime() 返回当前时间，不包含日期
SELECT CURTIME()

# 可以获取指定的部分，年、月、日、小时、分钟、秒
year,month,day,hour,minute,second,获取因为名, monthname
SELECT YEAR(NOW());  # 结果为：2019
SELECT YEAR("2018-12-30")  # 结果为：2018 
SELECT MONTHNAME(NOW())  # 结果为：January

# %Y 四位的年份，%y 2位的年份，%m 月份(01,02...11,12)
# %c 月份(1,2,...11,12), %d 日(01,02...)
# %H 小时(24小时制) %h(12小时制)
# %i 分钟(00,01,02...59)
# %s 秒(00,01,..59)

# str_to_date():将日志格式的字符转换成指定格式的日期
SELECT STR_TO_DATE("2019-01-02","%Y-%m-%d")  # 结果为：2019-01-02

# date_format():将日期转换成字符
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日") AS out_put  # 结果为：2019年1月2日

# datediff,计算两个日期的差
SELECT DATEDIFF("2019-01-09","2019-01-07")  # 结果为：2

其它函数

SELECT VERSION();  # 查看数据库的版本号
SELECT DATABASE()  # 查看当前数据库
SELECT USER()  # 查看用户

流程控制函数 if

# 如果第一个条件成立，结果为第一个，否则结果为第二个
# if else的效果
SELECT IF(10>5,"大","小")  # 结果为：大
SELECT IF(10<5,"大","小")  # 结果为：小
SELECT last_name,IF(commission_pct IS NULL,"没奖金","有奖金") AS 备注 
FROM employees

case 语句

# case 使用一，等于
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要像是的值2或语句2；
...
else 要显示的值或语句n；
end
# 案例
部门好=30 ，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原始工资
SELECT salary AS 原始工资,department_id,
CASE department_id 
WHEN 30 THEN salary*1.1  # 当department_id等于30时：执行
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees

# case 使用二，区间
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值或语句n
end
# 案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资10000，显示c级别
其它，显示D级别
SELECT salary AS 原工资,
CASE 
WHEN salary > 20000 THEN "A"
WHEN salary > 15000 THEN "B"
WHEN salary > 10000 THEN "C"
ELSE "D"
END AS 工资情况
FROM employees;

聚合函数

# sum、avg、max、min、count
sum、avg一般用于处理数值型
max、min、count可以处理任何类型
以上分组函数都忽略null值

# 可以和distinct 搭配使用
SELECT SUM(DISTINCT salary),SUM(salary) 
FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary)
FROM employees;

# count函数的单独介绍
# 效率
# MYISAM存储引擎下，count(*)的效率高
# INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些
count(*) > count(字段)

分组查询

语法：
select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】# 分组前的筛选，查询想要的分组结果
group by 分组的列表
【having 筛选条件】  # 分组后的筛选，就是把分组的结果查询出来了，在做处理 
【order by 子句】
# 案例1：查询邮箱中包含a字符的，每个部门的平均工资
select AVG(salary),department_id
from employees
where email llike "%a%"  # 分组前的筛选
group by department_id

# 案例：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# 1查询每个工种有奖金的员工的最高工资
SELECT MAX(salary) max_salary,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id

# 2根据1的结果进行筛选，最高工资>12000
SELECT MAX(salary) max_salary,job_id
FROM employees
WHERE commission_pct IS NOT NULL  # 分组前的条件
GROUP BY job_id
HAVING max_salary > 12000;  # 分组后的条件

# 分组查询中的筛选条件分为两类
			数据源           位置                    关键字
分组前筛选    原始表           group by子句的前面       where
分组后筛选   分组后的结果集     group by子句的后面       having

# 按多个字段分组
# 案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

连接查询

# 连接查询
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
出现笛卡尔积错误
# 起别名
select e.last_name,e.job_id,j.job_title
from employees e,jobs j
where e.job_id = j.job_id;

slq91语法：
# 等值连接
# 案例：查询有奖金的员工名，部门名
select last_name,department_name,commission_pct
from employees e,department d
where e.department_id = d.department_id  # 连接条件
and e.commisssion_pct is not null; 
# 还可以3表连接

# 非等值连接
# 案例:查询员工的工资和工资级别
select salary,grade_level
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal
and g.grade_level="A"

# 自连接
# 案例：查询 员工名和上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m   # 先把其看成员工表，在看成领导表
where e.manager_id=m.employee_id;

sql99语法：
select 查询列表
from 表1 别名 【连接类型】inner(取交集),left(左连接)
join 表2 别名
on 连接条件
【where筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接：inner
外连接： 左外*：left 【outer】 左表都有的
		右外*：right 【outer】
		全外：full 【outer】
交叉连接：cross
# 案例，查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE "%e%";

# 多表连接
# 案例：查询员工、部门名、工种名，并按部门名降序()
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;

# 外链接
特点：
1、外链接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中的没有和它匹配的，则显示null
	外链接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
   右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
# 案例：查询男朋友 不在男神表的女神名
# 左外链接
select b.*, bo*
from boys bo
left join beauty b
on b.boyfriend_id = bo.id
where b.id is null;

子查询

子查询
# 含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
	select后面：
		仅仅支持标量子查询
	from后面：
		支持表子查询
	where或having后面：（主要）
		标量子查询（主要）结果集只有一行一列
		列子查询（主要）结果集只有一列多行
		
		行子查询
		
	exists后面(相关子查询)
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）
# 案例：# 标量子查询
SELECT * 
FROM employees 
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = "Abel"
);  

# 列子查询，一列多行
操作符：in / not in (主要)
any / all
SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

# 行子查询
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

分页查询

select 查询列表
from 表
【inner join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order b 排序的字段】
limit offset,size;
offset 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
# 案例：查询第11条到第25条
SELECT * FROM employees LIMIT 10,15;

联合查询

# union 联合 合并：将多条查询语句的结果合并成一个结果
# 语法：
查询语句1
union 【ALL】  # 默认会进行去重，加了all就不会去重
查询语句2
union
查询语句3
...
# 案例：查询部门编号 > 90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE "%a%" OR department_id > 90;

SELECT * FROM employees WHERE email LIKE "%a%"
UNION
SELECT * FROM employees WHERE department_id > 90;

# 应用场景
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
1、要求多条查询语句的查询猎术是一致的
2、要求多条查询语句的查询的每一列的类型和循序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

mysql的正则表达式

# 关键字 regexp
# 查询以python开头的字段books
select * from tb_book where books regexp "^python";
# 这里正则表达式和python的正则基本一致

MySQL DML语言(对数据操作)

insert 插入

# 单行插入-方式1 支持多行插入
insert into 表(字段1...) values(值1...)
# 多行插入
insert into 表(字段1...) values(值1...),(值11...)

insert into 表(字段1...)  # 去掉values
select 字段1... from 表

# 单行插入-方式2,不支持多行插入（了解）
insert into 表名
set 字段1=值1，字段2=值2
# 案例：
insert into beauty
set id=19,`name`="刘涛",phone="999";  # 也可以这样来插入想要的数据

# 多行插入

update 修改

# 1.修改单标的记录
语法：
update 表名
set 列=新值，列=新值，...
where 筛选条件；

# 2.修改夺标的记录【补充】
sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值，...
where 筛选条件；
# 案例：修改张无忌的女朋友的手机号为114
update boys bo
inner join beauty b on bo.id = b.boyfriend_id
set b.phone="114"
where bo.boyName = "张无忌";

# 多行修改，省略 select * from
update (slwo_io_map s inner join instance_config i
       on s.cluster_name=i.instance_cluster_ip)
set s.cluster_ip_name=i.instance_cluster_name
where s.cluster_ip_name is null;

delete 删除

# 方式一
delete from 表 where 筛选条件
# 多表删除
sql99语法：
delete 表1的别名，表2的别名  # 删除两个原始表的内容，如果只写一个，就只删哪一个里面的内容
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件；
# 案例：删除张无忌的女朋友的信息
delete b  # 删除的是beauty表里的内容
from beauty b 
inner join boys bo on b.boyfrined_id = bo.id
where bo.boyNname = "张无忌";

# 方式二，不能加where筛选条件
truncate table 表名; # 清空表
truncate 删除，效率高一丢丢
delete删除后，在插入数据，自增长类的值从断点开始（没有初始化）
而truncate删除后，在插入数据，自增长列的值从1开始；
truncate 删除后没有返回值，delete删除有返回值；
truncate删除不能回滚，delete删除可以回滚；

MySQL DDL语言(对库和表操作)

1.库的操作

# 一、库的管理
创建、修改、删除
# 二、表的管理
创建、修改、删除

创建： create
修改： alter
删除： drop

# 库的管理：
# 创建库
create database if not exists books;  # 如果不存在，就创建该库
create database 库名 charset=utf8;
# 库的修改-字符集
alter database books character set utf-8;
ALTER DATABASE books CHARSET=gbk;

# 删除库
drop database 库名;
drop database if exists books;  # 如果ku存在，就删除

2.表的操作

2.1.表的创建

# 表的创建
语法：
create table 表名(
	列名 列的列些【(长度)，约束】,  # 中括号里面表示可选的项
    列名 列的列些【(长度)，约束】,
    列名 列的类型【(长度)，约束】,
    ...
    列名 列的类型【(长度)，约束】
);

-- 创建表
-- int unsigned 无符号整形
-- auto_increment 表示自动增长
-- not null 表示不能为空
-- primary key 表示主键
-- default 默认值
-- create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
例如：
create table classes (
    id int unsigned primary key auto_increment,
    name varchar(30));

2.2.表的修改

# 表的修改
alter table 表名 change|modify|add|drop column 列名 列些和约束条件;
# 1、修改列名
alter table 表名 change column 旧字段 新字段 类型和约束条件;
alter table book change column publishdate ubDate datetime;
# 2、修改列的列些或约束
alter table 表名 modify column 字段 类型和约束条件;
alter table book modify column pudate timestamp;
# 3、添加新列
alter table 表名 add column 字段 类型和约束条件;
alter table book add column book_id int;
alter table book add column book_id int first|after id;  # 可以把添加的列放在某个位置
# 4、删除列
alter table 表名 drop column 字段;
# 5、修改表名
alter table 旧表名 rename to 新表名;
alter table book rename to new_book;

2.3.表的删除

# 表的删除
drop table 表名;
drop table if exists book;

2.4.表的复制

# 表的复制
# 1.仅复制表的结构，表名2想复制表名1的结构
 create table 表名2 like 表名1;
 create table copy like book;
 # 2.复制表的结构+数据
 create table copy2 select * from book;
 # 3.只复制部分数据
 create table copy3
 select id,au_name from author
 where nation="中国";
 # 4.仅仅复制某些字段
 create table copy4 select id,au_name from author where 1=2;  # 或者直接写0
 
 # 跨库
 # 案例：将表departments中的数据插入新表dept2中
 create table 表1 select * from 库名.表2;
 create table dept2 select department_id,department_name from myemployees.departments;

3.常见的数据类型

# 常见的数据类型
数值型：
	整型
	小数：
		定点数
		浮点数
字符型：
	较短的文本：char，varchar
	较长的文本：text，blob（较长的二进制数据）
日期型：

3.1.整型

# 1.整型
分类：
tinyint、smallint、mediumint、int/integer、bigint
1		2			3		4			8
特点：
1、如果不设置无符号还是有符号，默认是有符号，如果设置无符号，需要添加unsigned关键字
2、如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
3、如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
# 如何设置无符号和有符号
create table tab_int(
	t1 int,  # 这个字段可以为负数
    t2 int unsgined  # 这个字段不能为负数
);
create table tab_int(
	t1 int(6) zerofill,  # 数值的大小是由int来决定，里面的6表示显示6位，如果不足6位，可以由0来填充,并且默认不为负数；
)

3.2.小数

# 2.小数
分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)

特点：
1.M和D
M：整数部位+小数部位
D：小数部位
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是flaot和double，则会根据插入的数值的精度来决定
定点型的精度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
create table tab_float(
	f1 float(5,2)  # 表示，总共为5位，小数位为2位
    f2 double,  # 
    f3 decimal  # 默认整数位为10位，小数位为0位
);

3.3.字符型

# 3.字符型
较短的文本：
char  默认里面为1
varchar 必须带参数

较长的文本：
text
blob(较大的二进制)
# 枚举
enum
create table tab_enum(
	e1 enum("a","b")  # 表示这个字段只能插入a和b，不区分大小写
);
insert into tab_enum values("a");
insert into tab_enum values("b");
insert into tab_enum values("c");  # 报错，插入不进去

3.4.日期型

# 日期型
分类：
date 只保存日期
time 只保存时间
year 只保存年

datetime 保存日期+时间
timestamp 保存日期+时间

4.常见的约束条件

分类：六大约束
not null：非空，用于保证该字段的值不能为空，比如姓名，学好等
default：默认，用于保证该字段有默认值，比如性别
primary key：主键，用于保证该字段的值具有唯一性，并且非空，比如学好，员工编号等
unique：唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号
check：检查约束【mysql中不支持】，比如年龄，性别
foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值在从表中添加外键约束，用于引用主表某列的值，比如学生表的专业编号，员工表的部门编号，员工表的工种编号

# 添加约束的时机：
	1.创建表时
	2.修改表时
# 约束的添加分类
	列级约束：
		六大约束语法上都支持，但外键约束没有效果
	表级约束：
		除了非空、默认，其它都支持
create table 表名(
	字段名 字段类型 列级约束，
    字段名 字段类型，
    表级约束
);
# 添加列级约束
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,# 主键，会默认创建索引
	stuName VARCHAR(20) NOT NULL,# 非空
	gender CHAR(1) CHECK(gender="男" OR gender="女"),  # 检查
	seat INT UNIQUE, # 唯一，会默认创建索引
	age INT DEFAULT 18, # 默认约束
	majorId INT REFERENCES major(id)  # 外键
);

# 添加表级约束
语法：在各个字段的最下面
【constraint 约束名】 约束类型（字段名）
CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorId INT,
	
	PRIMARY KEY(id), # 主键 
    # PRIMARY KEY(id,stuName)  # 组合主键
	UNIQUE(seat), # 唯一键
	CHECK(gender="男" OR gender="女"), # 检查
	FOREIGN KEY(majorId) REFERENCES major(id)  # 外键  这个表时从表
);
# 主表
CREATE TABLE major(
	id INT PRIMARY KEY
);

# 查看表中的所有索引，包括主键，外键，唯一
show index from 表名;

# 通用的写法：
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL,
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorId INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)  # 外键写表级约束
);
# 主键和唯一的大对比：
		保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键		是			否			至多有1个			是，但不推荐
唯一 		是			是			可以有多个		    是，但不推荐

# 外键：
1、要求在从表设置外键关系
2、从表的外键列的列些和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、删除数据时，先删除从表，在删除主表

5.标识列即自增长列

# 自增长列
含义：可以不用手动的插入之，系统提供默认的序列值
# 创建表时设置标识列
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUES(NULL,"zs");

特点：
1、标识列必须和珠江搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入之，设置起始值

MySQL TCL

transaction control language 事务控制语言

1.事务

1.1.事务的特性

# 事务：只针对增删改查，select insert update delete
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
# 事务的ACID属性
1.原子性（atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
2.一致性（consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并打执行的各个事务之间不能互相干扰
4.持久性（durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

# 总结
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会是数据从一个一直状态切换到另外一个一致状态
隔离线：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据

1.2.事务的使用

# 查看事务的状态
show variables like "autocommit";
# 隐形事务
update book set name = "ww" where id = 1;  # 默认,autocommit是开启的

# 显示事务：事务具有明显的开启和结束标记
前提L:必须先设置自动提交功能为金融
set autocommit=0;

步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select,insert,update,delete)
语句1；sql1
语句2；sql2
...
步骤3：结束事务
commit;提交事务
rollback;回滚事务
# 案例：
set autocommit=0;
start transaction;
update book set name = "ww" where id = 1;
update book set name = "aa" where id = 2;
commit;

1.3.事务的级别

# 事务的隔离级别：
					 脏读	 不可重复读	 幻读
read uncommitted	  yes     yes       yes
read committed		  no      yes       yes 
repeatable read		  no      no        yes
serializable  		  no	  no		no
mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set sesssion|gloabal transaction isolation level 隔离级别;

1.4.事务保存点

# savepoint 只和rollback一起使用
savepoint 节点名;设置保存点
# 案例：
set autocommit=0;
start transaction;
delete from account where id = 25;
savepoint a; # 设置保存点
delete from account where id = 28;
rollback to a;

1.5.事务中的删除

# delete和truncate在事务使用时的区别
delete 删除的数据可以回滚，rollback
truncate 输出的数据不可回滚

2.视图

# 视图：mysql从5.0.5版本开始提供视图功能，一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是使用视图时动态生成的，只保存了slq逻辑，不保存查询结果
# 应用场景
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
# 示例：
create vies my_v1
as 
select studentName,majorName
from student s
inner join major m
on s.majorid = m.majorid
where s.majorid=1;

# 使用视图来查询
select * from my_v1;

# 使用视图的优点
1、重用sql语句
2、简化复杂的sql操作，不必知道它的查询细节
3、保护数据，提高安全性

# 修改视图
方式一：
create or replace view 视图名
as 
查询语句;
方式二：
alter view 视图名
as 
查询语句;

# 删除视图
语法：
drop view 视图名1,视图名2,...;

# 查看视图
desc myv3;  # 视图名
show create view myv3\G;  # G格式化一下，使查询的数据显示的好看

mysql变量

1.变量

# 系统变量
说明：变量由系统提供的，不是用户定义，属于服务器层面使用的语法：
1、查看所有的系统变量
show global|session variable;

2、查看满足条件的部分系统变量
show global|[session] variables like "%char%";

3、查看指定的某个系统变量的值
select @@global|[seesion].系统变量名;

4、为某个系统变量赋值
方式一：
set global|[session] 系统变量名=值;
方式二：
set @@global|[session].系统变量名=值;

# 注意：
如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认session

1.1全局变量

# 全局变量
# 作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启
# 查看所有的全局便令、
show global varialbes;

# 查看部分的全局变量
show global variables like "%char%"

# 查看指定的全局变量的值
select @@global.autocommit;
select @@global.isolation;

# 为某个指定的全局变量赋值
set @@global.autocommit=0;

1.2会话变量

# 用法和全局变量一样
作用域：仅仅针对于当前会话（连接）有效
把global换成session即可

1.3自定义变量

# 自定义变量
说明：变量是用户自定义的，不是由系统的使用步骤：
声明
赋值
使用（查看、比较、运算等）
# 1、用户变量
作用域：针对于当前会话（连接），同于会话变量的作用域

赋值的操作符： =或：=
# 1、声明并初始化
set @用户变量名=值;或
set @用户变量名:=值;或
select @用户变量名:=值;
# 2、赋值（更新用户变量的值）
方式一：通过set或select
		set @用户变量名=值;或
		set @用户变量名:=值;或
		select @用户变量名:=值;
方式二：通过select into
		select 字段 into @变量名 from 表;
# 3、使用（查看用户变量的值）
select @用户变量名;
# 案例：
# 1、声明并初始化
set @name="john";
set @name=100;
set @count=1;
# 2、赋值
select count(*) into @count from employees;
# 3、查看
select @count;

1.4局部变量

# 局部变量
作用域：仅仅在定义它的begin end中有效
应用在begin end中的第一句话
# 1、声明
declare 变量名 类型；
declare 变量名 类型 default 值；
# 2、赋值
方式一：通过set或select
		set 局部变量名=值;或
		set 局部变量名:=值;或
		select @局部变量名:=值;
方式二：通过select into
		select 字段 into 局部变量名 from 表;
# 3、使用
select 局部变量名;

# 对比用户变量和局部变量
			作用域			定义和使用的位置				语法
用户变量	当前会话		会话中的任何地方				必须加@符号，不用限定类型
局部变量	begin end中	   只能在begin end中，且为第一句话	  一般不用加@符号，需要限定类型

# 案例：声明两个变量并赋值初始值，求和，并打印
# 1、用户变量
set @m=1;
set @n=2;
set @sum = @m + @n
select @sum;

2.存储过程

2.1存储过程的含义

# 存储过程
含义：一组预先编译好的sql语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

# 一、创建语法
create procedure 存储过程名(参数列表)
begin
	存储过程体（一组合法的sql语句）
end
注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
举例：
in name varchar(20)
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方法传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略存储过程体重每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用delimiter重新设置
语法：
delimiter 结束标记
案例：
ddelimiter $

# 二、调用语法
call 存储过程名(实参列表);

# 案例：插入到admin表中五条记录
delimiter $
create procedure myp1()
begin 
	insert into admin(name,age) values("aa",18),("bb",19),("cc",20);
end $
# 调用
call myp1()$
# 查询
select * from admin$

2.2带in

# 2、创建带int模式参数的存储过程
# 案例1：创建存储过程实现 根据女神名，查询对应的男神消息
delimiter $
create procedure myp2(in beatutyName varchar(20))
begin 
	select bo.*
	from boys bo
	right join beauty b on on bo.id = b.boyfrined_id
	wehre b.name = beautyName
end $

# 调用
call myp2("柳岩")$

# 案例2：创建存储过程实现，用户是否登录成功
create procedure myp3(in username varchar(20),in password varchar(20))
begin
	declare result int defalut 0;# 声明并初始化
	
	select count(*) into result  # 赋值
	from admin a
	where a.username = username
	and a.password = password
	
	select if(result>0,"成功","失败");# 使用
end $

# 调用
call myp3("张飞","666")$

2.3带out

# 案例：
create procedure myp5(in beautyName varchar(20),out boyName varchar(20))
begin
	select bo.boyName into boyName
	from boys bo
	inner join beauty b on bo.id = b.boyfrined_id
	where b.name = beautyName;
end $
# 调用
call myp5("小昭",@bName)$
select @bName$

# 案例：根据女神名，返回对应的男神名和男神魅力值
create procedure myp6(in beautyName varchar(20),out boyName varchar(20),out userCP int)
begin
	select bo.boyName,bo.userCP into boyName,userCP
	from boys bo
	inner join beauty b on bo.id = b.boyfriend_id
	where b.name = beautyName;
end $
# 调用
call myp6("小昭",@bName,@userCP)$

2.4带inout

# 创建带inout模式参数的存储过程
# 案例1：传入a和b两个值，最终a和b都翻倍并返回
create procedure myp8(inout a int,inout b int)
begin
	set a = a * 2;
	set b = b * 2;
end $
# 调用
set @m=10$
set @n=20$
call myp8(@m,@n);
select @m,@n;

2.5删除存储过程

# 语法
drop procedure 存储过程名;
drop procedure myp1;

2.6查看存储过程的信息

# 查看存储过程的信息
show create procedure myp2;

3.函数

3.1函数的含义

# 存储过程
含义：一组预先编译好的sql语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果

3.2函数的创建

create function 函数名(参数列表) returns 返回类型
begin
	函数体
end 
注意：
1.参数列表 包含两部分
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记

3.3函数的调用

# 函数的调用
select 函数名(参数列表)

# 无参数有返回
# 案例：返回公司的员工个数
create function myf1() returns int
begin
	declare c int default 0;# 定义局部变量
	select count(*) into c  # 赋值
	from employees;
	return c;
end $
# 调用
select myf1()$

# 有参数有返回
# 案例1：根据员工名，返回它的工资
create function myf2(empName varchar(20)) return double
begin
	set @sal=0;  # 定义用户变量
	select salary into @sal  # 赋值
	from employees
	where last_name = empName;
	retun @sal;
end $

# 调用
select myf2("k_ing") $

# 案例2：根据部门名，返回该部门的平均工资
create function myf3(deptName varchar(20)) returns double
begin
	declare sal double;
	select avg(salary) into sal
	from employees e
	inner join departments d on e.deparement_id = d.department_id
	where d.department_name = deptName;
	return sal;
end $
select myf3("IT") $

# 案例3：实现传入两个float，返回二者之和
create function test_fun1(num1 float,num2 flaot) returns float
begin
	declare sum flaot default 0;
	set sum = num1 + num2;
	return sum;
end @
select test_fun1(1,2)$