mysql约束

主键约束

-- 添加单列主键
--1
create table user(
    id int primary key,
    name varchar(20)
);
--2
create table user(
    id int,
    name varchar(20),
    constraint pk1 primary key(id)
); 
-- 添加联合主键约束
-- 联合主键每个值都不能为null,重复比较的是全部
create table user(
    id int,
    name varchar(20),
    primary key (id,name)
);

-- 通过修改表来添加主键约束
alter table <表名> add primary key(字段列表)
create table user(
    id int,
    name varchar(20)
);
alter table user add primary key(id);


-- 删除主键约束
alter table 表名 drop primary key;

自增长约束 auto_increment

-- 添加自增长约束
create table user(
    id int primary key auto_increment,
    name varchar(20)
);
-- 指定自增长的初始值
--1
create table user(
    id int primary key auto_increment,
    name varchar(20)
)auto_increment=100;
--2
create table user1(
    id int primary key auto_increment,
    name varchar(20)
);
alter table t_user2 auto_increment=100;

非空约束 not null

--添加非空约束
--1
create teble user(
    id int not null,
    name varchar(20) not null
);
--2
create table user1(
    id int,
    name varchar(20)
);
alter table user1 modify id int not null;
alter table user1 modify name varchar(20) not null;

-- 删除非空约束
create teble user(
    id int not null,
    name varchar(20) not null
);
alter table user modify id int;
alter table user modify name varchar(20);

唯一约束 unique

-- 添加唯一约束
-- 1
create table user(
    id int unique,
    name varchar(20),
    phone_name varchar(20)
);
--2
alter table 表名 add constraint 约束名 unique(列);
create table user(
    id int unique,
    name varchar(20),
    phone_name varchar(20)
);
alter table user add constraint uni_pk unique(name);

-- 删除唯一约束
alter table 表名 drop index 约束名;
alter table user drop index uni_pk;

默认约束 default

-- 添加默认约束
--1
create table user(
    id int default 10;
    name varchar(20)
);
--2
alter table user modify name varchar(20) default 'zyf';

-- 删除默认约束
alter table user modify name varchar(20) default null;

零填充约束 zerofill

-- 添加约束
create table user(
    id int zerofill;
    name varchar(20)
);

DDL

1.对数据库的常用操作

--查看所有数据库
show databases;
--创建数据库
create database if not exists mysql1;
-- 删除数据库
drop database if exists mysql1;
--修改数据库编码
alter databse mysql1 character set utf8;

2.对表结构的常用操作

--创建表格式
create table [if not exists] 表名(
    列名 类型([宽度]) [约束条件] [comment '字段说明'],
    列名 类型([宽度]) [约束条件] [comment '字段说明']
);
--示例
create table user(
    id int primary key
);

-- 删除表
drop table 表名;
-- 示例
drop table user;

--查看所有表
show tables;

--查看表创建语句
show create table user;

--查看表结构
desc user;

3.修改表结构

--添加列
alter table 表名 add `列名` 类型(宽度) 约束; 
--示例
alter table user add `name` varchar(20) default 'aiiqi';

--修改列名和类型
alter table 表名 change `旧列名` `新列名` 类型(宽度) 约束;
--示例
alter table user change `name` `name1` varchar(30) default 'iiii';

-- 删除列
alter table drop 列名;
-- 示例
alter table drop name;

-- 修改表名
rename table 表名 to 新表名;
-- 示例
rename table `user` to `user1`;

DML

1.插入

--insert
insert into 表名(列名,列名···) values (值，值···);
insert into user (id,name,age) values (1,'aiqi',13);

2.删除

--delete
delete from 表名 [where 条件];
delete from user where id=1;

--删除表所有数据
delete from user;
--清空表数据
truncate user;

-- delete from 只是简单删除表数据，而truncate是可以理解为将表删除再创建新表

3.更新

--update
update 表名 set 列名=值,列名=值···· where 条件;
update user set name='aiqi',age=14 where id=1;

DQL查询

select [*]
<目标列表达式1> [别名],
<目标列表达式2> [别名] 
from 
<表名或视图> [别名],<表名或视图> [别名]
[where <条件表达式>]
[group by <列名> [having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>]

-- 基本查询
select * | 列名 from 表 where 条件

简单查询

-- 创建数据库
create database if not exists mydb2;
-- 创建表
create table if not exists product(
    pid int primary key auto_increment,
    pname varchar(20),
    price int,
    category_id varchar(20)
);
-- 插入数据
insert into product values(null,'海尔洗衣机',5000,'c001');
insert into product values(null,'美的冰箱',3000,'c001');
insert into product values(null,'格力空调',5000,'c001');
insert into product values(null,'九阳电饭煲',200,'c001');
insert into product values(null,'啄木鸟衬衣',300,'c002');
insert into product values(null,'恒源祥西裤',800,'c002');
insert into product values(null,'花花公子夹克',440,'c002');
insert into product values(null,'劲霸休闲裤',266,'c002');
insert into product values(null,'海澜之家卫衣',180,'c002');
insert into product values(null,'杰克琼斯运动裤',430,'c002');
insert into product values(null,'兰蔻面霜',300,'c003');
insert into product values(null,'雅诗兰黛精华水',200,'c003');
insert into product values(null,'香奈儿香水',350,'c003');
insert into product values(null,'SK-II神仙水',350,'c003');
insert into product values(null,'资生堂粉底液',180,'c003');
insert into product values(null,'老北京方便面',56,'c004');
insert into product values(null,'良品铺子海带丝',17,'c004');
insert into product values(null,'三只松鼠坚果',88,null);

--查询
-- 查询所有商品
select * from product;
-- 查询商品名和商品价格
selec pname,price from product;
-- 表别名
select * from product as p;
-- 列别名
select pname as pn from product;
-- 去掉重复值
select distinct price from product;
-- 运算查询
select price+10 as price, pname from product;

mysql四种运算符

算术运算符

-- 乘法
*
-- 加法
+
-- 减法
-
-- 除法
/ 或 div
-- 取余
% 或 mod

比较运算符

-- 等于
=
-- 小于和小于等于
< <=
-- 大于和大于等于
> >=
-- 安全等于 当两个值都为null，得到1，当有个值为null，得到0
<=>
-- 不等于
<> !=
-- 判断一个值是否为null
is null 或 isnull
-- 判断一个值是否不为null
is not null
-- 返回最小值
least
-- 返回最大值
greatest
-- 两值之间
between and
-- 列表任意一个值
in
-- 不为列表中任意一值
not in
-- 通配符匹配
like
-- regexp正则
regexp

逻辑运算符

-- 逻辑非
not 或 !
-- 逻辑与
and 或 &&
-- 逻辑或
or 或 ||
--逻辑异或
xor

位运算符(不重要)

-- 案例
-- 求最小值
select least(10,20,30);  --10 
select least(10,null,30); --null

-- 求最大值
select greatest(10,20,30); --30
select greatest(10,null,30); -- null

-- 查询含有裤字的所有产品
select * from product where pname like '%裤%';
-- 查询以裤字开头的所有产品
select * from product where pname like '裤%';
-- 查询第二个字为蔻字的所有产品
select * from product where pname like '_蔻%';

-- 查询category_id 不为null
select * from product where category_id is not null;
-- 查询category_id 为null的所有产品
select * from product where category_id is null;

排序查询

select * from 表名 where 条件 order by 字段名[asc升序|desc降序],字段名[asc升序|desc降序];
-- 在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price asc,category_id desc;

聚合查询

-- 针对是列 会忽略空值null 查询为一列

--1 count() 记录行数 建议列给*，假如列中有null，就会少算这一行
select count(*) from user;

--2 sum() 记录列所有行的值的和,会把null忽略
select sum(id) from user;

-- max() 比较列中最大值,会把null忽略
select max(id) from user;

-- min() 比较列中最小值,会把null忽略
select min(id) from user;

-- avg() 计算列的平均值,会null的那一行直接忽略（也不会算入行数）
select avg(price) from product;

分组查询 group by

-- 如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现
select 字段1,字段2 from 表名 group by 分组字段 having 分组条件;
select catgory_id,sum(price) priceSum from product group by category_id having priceSum>8416;

分页查询 limit

select * from product limit n(前几条数据);
select * from product limit 前几条数据((当前页-1)*显示数量),显示数量

insert into select语句

-- insert into select语句
--写法1
insert into table2(file1,file2....) select value1,value2... from table1;
--写法2
insert into table2 select * from table1;
--table2必须存在

select into from 代替语句

select value1，value2 into 插入的表 from 查询的表;


create table product1(select * from product);
-- 要求目标table2不存在，因为插入时会自动创建table2，并将table1表设计复制给table2

多表查询

关系：一对一，一对多或多对一，多对多

外键约束 => 一对多或多对一

主表被从表的数据不能顺表删除，也就是从表外键约束字段中有相应主表数据，主表不能随意删除该数据，从表数据随便删无所谓

主表可以随意添加数据，但从表只能添加主表有的数据或外键约束字段设置为null

-- 创建表时添加外键约束
create table 表名(
    字段名 数据类型,
    constraint 外键名 foreign key (从表字段名) references 主表名(字段名)
);
--创建主表
create table dept(
    id int primary key,
    name varchar(20)
);
--创建从表和外键约束
create table people(
    dept_id int,
    name varchar(20),
    constraint p_fk foreign key (dept_id) references dept (id)
);



-- 修改表时添加外键约束(从表外键约束的字段列中不可出现主表没有的数据，可以为null)
alter table 从表名 add constraint 外键名 foreign key (从表字段) references 主表名 (字段);
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);




-- 删除外键约束
alter table 表名 drop foreign key 外键名;
alter table people drop foreign key p_fk;

外键约束 => 多对多表

--建立两个主表
create table if not exists student(
    sid int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(20)
);
create table course(
   cid  int primary key auto_increment,
   cidname varchar(20)
);

//建立中间表（从表）
create table student_course(
    student_sid int,
    course_cid int
);

//给从表设置外键约束

alter table student_course add constraint fk_sid foreign key (student_sid) references student (id);
alter table student_course add constraint fk_cid foreign key (course_cid) references course (id);

多表联合查询

1.交叉连接查询

查询出来的是连接两个所有数据行的笛卡尔积

A表m行，B表n行，查出来的数据也就是m × n行

select * from A,B

2.内连接查询

-- 隐式内连接
select * from A,B where 条件;

-- 显示内连接
select * from A inner join B on 条件;

3.外连接查询

-- 左外连接
select * from A表 left outer join B表 on 条件;

-- 右外连接
select * from A表 right outer join B表 on 条件;

-- 满外连接（mysql不支持，用union代替）
select * from A表 left outer join B表 on 条件
union
select * from A表 right outer join B表 on 条件;

4.子查询

子查询就是条件里面的值经过查询的

-- 子查询
select * from 表 where 条件 (select * from 表 where 条件)

-- all关键字(与最值作比较)
select * from A表 where age > all(select age frmo B表); -- 查出A表比B表中最大年龄大的人



-- any关键字和some关键字
-- some和any 意思一样，可以相互替代
select * from A表 where age > any(select age from B表); --查出A表比B表中最小年龄大的人



-- in关键字
select …from …where c in(查询语句)
--等价于：
select ...from ... where c = result1 or c = result2 or c = result3


-- exists关键字
-- 该子查询如果“有数据结果”(至少返回一行数据)， 则该EXISTS() 的结果为“true”，外层查询执行

select …from …where exists(查询语句);

自关联查询

--1
select 字段列表 from 表1 a , 表1 b where 条件;
--2
select 字段列表 from 表1 a [left] join 表1 b on 条件;

mysql视图

-- 创建视图

create [or replace] [algorithm = {undefined | merge | temptable}]
 
view view_name [(column_list)]
 
as select_statement
 
[with [cascaded | local] check option]

--参数说明：
--（1）algorithm：可选项，表示视图选择的算法。
--（2）view_name ：表示要创建的视图名称。
--（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
--（4）select_statement
--：表示一个完整的查询语句，将查询记录导入视图中。
--（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。


-- 查看表和视图
show full tables;


-- 修改视图
alter view 视图名 as 查询语句;


-- 更新视图数据 最好不要



-- 重命名视图
rename table 视图名 to 新视图名;


-- 删除视图
drop view if exists 视图名1, 视图名2....;

mysql函数

1.聚合函数

-- group_cancat实现合并选中字段为一列
select group_cancat([distinct] 字段 [order by 字段 asc/desc] [separator '指定分割符'])

create database mydb4;
use mydb4;
create table emp(
    emp_id int primary key auto_increment comment '编号',
    emp_name char(20) not null default '' comment '姓名',
    salary decimal(10,2) not null default 0 comment '工资',
    department char(20) not null default '' comment '部门'
);
insert into emp(emp_name,salary,department) 
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');


select department,group_cancat(distinct emp_name order by emp_name desc) from emp group by department;

2.数学函数

-- 绝对值 abs
select abs(-1111);
-- 向上取整 ceil
select ceil(1.4658);
-- 向下取整floor
select floor(1.4658);
-- 返回列表最大值 greatest
select greatest(1,2,1,5,100,11);
-- 返回列表最小值 least
select least(1,2,1,5,100,11);
-- 返回字段最大值 max
select max(字段) from 表;
-- 返回最小值 min
select min(字段) from 表;
-- 取两个的余 mod
select mod(7,6);
-- 获取圆周率值 PI
select PI();
--返回x的y次方 pow
select pow(2,8);
-- 返回0-1的随机数 rand()
select rand();
-- 四舍五入 round(值，保留的小数位数);
select round(1.8546,2);
-- 返回数值保留到小数位的值（不会四舍五入） truncate(值,保留的小数位位数);
select truncate(1.548563,4);

2.字符串函数

-- 获取字符串长度 char_length()/character_length()
select char_length('asdsad');
select character_length('asdsad');
-- 合并字符串concat()
select concat('asd','ssss');
-- 可以指定分割符的合并字符串
select concat_ws(分隔符,字符串1，字符串2.....);

3. 控制流函数

-- case when
--1
select *,
case payType
    when 1 then '微信支付'
    when 2 then '支付宝支付'
    when 3 then '银行卡支付'
    else '其他支付方式'
 end as payTypeStr
 from orders;
 
 --2
 select *,
     case
     when payType=1 then '微信支付'
     when payType=2 then '支付宝支付'
     when payType=3 then '银行卡支付'
     else '其他支付'
     end as payTypeStr
     from orders;
     
-- if(expr,v1,v2)
-- 如果expr表达式成立，返回结果v1,否则返回结果v2;
select if(1>0,'true','false');

--ifnull(v1,v2) 如果v1不为null 则返回v1，否则返回v2;
select ifnull(null,'hi'); -- 返回hi

-- isnull(表达式) 判断表达式是否为null
select isnull(null); -- 返回1


--nullif(expr1,expr2) 如果expr1=expr2返回null,否则返回expr1

select nullif(25,25); --返回null

窗口函数

select *,
函数(参数)
over(
partition by 字段 --分区
order by 字段 --排序
frame_clause --调整窗口大小
);

1.序号函数

-- 序号函数:row_number()、rank()、dense_rank()

select *,row_number() over(partition by 字段 order by 字段) as rn from 表名;
select *, rank() over(partition by 字段 order by 字段) as rn from 表名;
select *,dense_rank() over(partition by 字段 order by 字段) as rn from 表名;

2.开窗聚合函数

 -- 开窗聚合函数 sum,avg,max,min
 select *,sum(salary) over(partition by dname order by eid desc) as salarySum from employee; 

 select *,avg(salary) over(partition by dname order by eid desc) as avg from employee; 

 select *,max(salary) over(partition by dname) as avg from employee;
 
 select *, min(salary) over(partition by dname) as min from employee;

数据类型

1.数字类型

类型	大小	范围	范围(无符号)	用途
tinyint	1byte	(-128,127)	(0,255)	小整数值
smallint	2byte	(-32768,32767)	(0,65535)	大整数值
mediumint	3byte	(-8388608,8388607)	(0,1677215)	大整数值
int/integer	4byte	(-2147483648,2147483647)	(0,4294967295)	大整数值
bigint	8byte	(-9223372036854775808,9223372036854775807)	(0,18446744073709551615)	极大整数值
float	4byte	(-3.402823466E+38,3.402823466351E+38)	(0,1.175494351E-38/3.402823466E+38)	单精度浮点数值
double	8byte	(-1.7976931348623157E+308,1.7976931348623157E+308)	(0,2.2250738585072014E-308/1.7976931348623157E+308)	双精度浮点数值
decimal		依赖于M和D的值	依赖于M和D的值	小数值

2.字符串类型

类型	大小	用途
char	0-255byte	定长字符串
varchar	0-65535byte	变长字符串
tinyblob	0-255byte	不超过255个字符的二进制字符串
tinytext	0-255byte	短文本字符串
blob	0-65535byte	二进制形式的长文本数据
text	0-65535byte	长文本数据
mediumblob	0-16777215byte	二进制形式的中等长度文本数据
mediumtext	0-16777215byte	中等长度文本数据
longblob	0-4294967295byte	二进制形式的极大文本数据
longtext	0-4294967295byte	极大文本数据

3.日期类型

类型	大小(byte)	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	'-838:59:59/838:59:59'	HH:MM:SS	时间值或持续时间
year	1	'1901/2155'	YYYY	年份值
datetime	8	'1000-01-01 00:00:00/9999-12-31 23:59:59'	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

mysql储存过程

delimiter 自定义结束符号
create procedure 储存名([in,out,inout]参数名 数据类型...)
begin
    sql语句
end 自定义的结束符号
delimiter ;


delimiter $$
create proceduce proc01()
begin
    select empno,ename from emp;
end $$
delimiter ;

--调用存储过程
call proc01();

局部变量

delimiter $$
create proceduce proc01()
begin
declare 变量名 类型 [defaule '默认值']; -- 声明变量
set 变量名='值'; --赋值
sql语句;
end $$
delimiter ;



--调用存储过程
call proc01();


--赋值也能select...into...(查出来的数据只能是单行单列)
select 字段,.... into 变量名,....

用户变量