mysql基础总结

1.数据库常用命令：
show databases; 查看所有数据库
use test; 打开test数据库
show tables; 查看当前库的所有表
show tables from mysql; 查看mysql数据库的所有表
select database(); 查看所属库
desc stuinfo； 查看stuinfo表结构
select version(); 登陆mysql，查看mysql版本
mysql --version; // mysql -V 没有登陆mysql查看mysql版本

2.sql语法规范：
不区分大小写，建议关键字大写，表名，列名小写
每条命令最好用分号结尾
每条命令根据需要可以进行缩进换行
注释：#注释文字 – 注释文字 /注释文字/

4.常见函数/方法：
distinct 去重
ifnull(a,b) 判断是否为空,如果a为null，赋值为b
[not] between and
escape 转义 where name like ‘_KaTeX parse error: Expected group after '_' at position 1: _̲%' escape '’ 指定$为转义字符
order by [asc|desc] 排序
exists() 有值返回1，空值返回0
(1)字符函数
length(),获取参数值的字节个数
concat(‘a’,‘b’) 拼接 一个为null,结果为null
upper() 变大写
lower() 变小写
substr()/substring() 截取函数
select substr(‘李莫愁爱上了陆展元’,a,b) 索引从1开始不是0，a代表从哪开始，b代表截取几个长度
instr(a,b) 返回b在a中首次出现的索引值，找不到为0
select instr(‘杨不悔爱上了殷六侠’,‘殷六侠’) 结果为7
trim() 去除前后空格
trim(’ sdfsfd ‘)
trim(‘a’ from ‘aaaaadsfdsafaaaaaa’) 去除前后a
lpad() 左填充至指定长度
lpad(‘殷素素’,10,’*’) *****殷素素
lpad(‘殷素素’,2,’’) 殷素 从右切断
rpad() 右填充至指定长度
replace() 替换
replace(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’)
(2)数学函数
round(1.65) 四舍五入
round(1.567,2) 保留2位，四舍五入
ceil(1.01) 向上取整
floor(1.99) 向下取整
truncate(1.65,1) 截断 保留小数点后一位
mod(10,3) 取余 a-a/bb
rand:获取0-1之间随机数
(3)日期函数
now() 返回当前系统日期+时间
curdate() 返回当前日期
curtime() 返回当前时间
year(now()) month((now()) monthname(now()) 英文形式返回月
str_to_data(‘2020-6-22’,’%Y-%c-%d’) 将字符通过指定格式转换为日期
data_format() 将日期转换为字符
datediff(a,b) 返回b，a,之间时间差
(4)分组函数
sum(a) 求和
avg(a) 平均值
max(a) 最大值
min(a) 最小值
count(a) 计算个数
分组函数都忽略null值

5.流程控制函数：
1.if(10>5,‘大’,‘小’) 类似三目运算
2.case 要判断的字段或表达式
when 常量1 then 显示语句1; 是语句加分号，是数字不加分号
when 常量2 then 显示数字2
…
else 显示语句n;
end
3.case #区间判断
when 条件1 then 显示语句1
when 条件2 then 显示数字2
…
else 显示语句n;
end
4.循环（while,loop,repeat）
iterate 类似于continue
leave 类似于break
1.[标签：]while 循环条件 do
循环体；
end while[标签：];
2.[标签：] loop
循环体;
end loop[标签：];
3.[标签：] repeat
循环体;
until 结束循环的条件
end repeat [标签；];

6.分组查询：
group by
案例一：查询每个工种的最高工资
select max(salary),job_id from employees group by job_id;
案例二：查询邮箱中包含a字符的，每个部门的平均工资
select avg(salary),department_id from employees where email link “%a%” group by department_id;
案例三：查询员工数大于2的部门
select count(),department_id from employees group by department_id having count()>2;

7.多表查询（连接查询）
内连接：inner join #inner可以省略
等值连接
92语法：select name,boyname from boys,beauty where beauty.boyfriend = boys.id;
99语法：select last_name,department_name from employees e inner join department d on e.department_id=d.department_id;
非等值连接
自连接
外连接：
左外连接 left [outer] join
右外连接 right [outer] join
全外连接 full [outer] join
交叉连接：cross join

8.子查询

9.分页查询
limit

10.联合查询
union [all]

11.DML语言(数据的管理)
1.插入：insert
insert into 表名（列1,…列n) values(值1,…值n) 插入一条
insert into 表名（列1,…列n) values(值1,…值n),(值1,…值n),… 插入多条
insert into set 列1=值1,列n=值n
2.修改单表记录：update
update 表名 set 列1=值1,列2=值2,… where 筛选条件
修改多表记录
92:update 表1 别名 表2 别名 set 列1=值1,… where 连接条件 and 筛选条件;
99:update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列1=值1,… where 筛选条件
3.删除单表记录：delete
delete from 表名 where 筛选条件
truncate table 表名; 删除所有
删除多表记录
92:delete 表1的别名,表2的别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;
99:delete 表1的别名,表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件

12.DDL语言(库的管理)
1.创建：create
create database if not exists 库名;
2.修改：alter
alter database books character set gbk;更改库的字符集
3.删除：drop
drop database books;
4.表的创建：
creat table book(
id int,
bname varchar(20),
price double,
authorId int
);
5.表的修改：
(1)修改列名
alter table 表名 change 【column】 原列名 新列名 类型;
(2)修改列的类型或约束
alter table 表名 modify column 列名 类型;
(3)添加新列
alter table 表名 add column 列名 类型;
(4)删除列
alter table 表名 drop column 列名;
(5)修改表名
alter table 表名 rename to 新表名;
6.表的删除：
drop table if exists 表名;
7.表的复制：
1.仅仅复制结构
create table 表名 like 存在表名;
2.复制结构和数据(可复制部分列和行数据)
create table 表名 select * from 存在表名;

13.约束（6大约束）（列级约束：外键约束没效果，表级约束：除了默认，非空都可以写）
not null 非空,
default 默认值,
primary key 主键，唯一，非空,
unique 唯一，可空,
check 检查约束【mysql中不支持】
foreign key 外键约束，用于限制两个表的关系，用于保证该字段的值必须来自于主表关联列的值。
例子:
#列级约束
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,#默认
majorid int foreign key references marjor(id) #外键
)
create table major(
id int primary key,
majorname varchar(20)
)
#表级约束：
create table stuinfo(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int,

		【constraint pk 】primary key(id),#主键
		【constraint uq 】union(seat),#唯一
		【constraint ck 】check(gender='男' or gender='女'),#检查
		【constraint fk 】foreign key(majorid) references major(id)#外键
	)
	
添加约束：
	alter table stuinfo modify column stuname varchar(20) not null;
	alter table stuinfo add primary key(id);

14.事务，视图
1.#创建(修改)视图
create as replace view 视图名
as
查询语句;
2.#修改视图
alter view 视图名
as
查询语句;
3.删除视图
drop view 视图名,视图名,…;

15.存储过程
1.创建存储过程
create procedure 存储过程名（参数列表）
begin
存储过程体（一组合法的sql语句）
end

	注意：
	参数列表包含三部分：
		(参数模式    参数名   参数类型)
		in   该参数可以作为输入，就是该参数需要调用方传入值
		out   该参数可以作为输出，就是该参数可以作为返回值
		inout  既可以作为输入又可以作为输出，就是该参数既需要传入值，又可以返回值
	如果存储过程体仅仅只有一句话，begin end 可以省略
	存储过程体中的每条sql语句的结尾要求必须加分号
	存储过程的结尾可以使用 delimiter 重新设置
2.调用存储过程
	call 存储过程名（实参列表）;
3.创建IN模式的存储过程
	delimiter $
	create procedure myp2(in beautyName varchar(20))
	begin
		select bo.*
		from boys bo
		right join beauty b on bo.id=b.boyfriend_id
		where b.name = beautyName
	end $
	
	call myp2('柳岩')$
	
4.创建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.boyfriend_id
		where b.name=beautyName;
	end$
	
	call myp5('小昭',@bName)$
	select @bName$
5.创建inout模式的存储过程
	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,@name)$
	select @m,@n$
6.删除存储过程
	drop procedure 存储过程名
7.查看存储过程的信息
	show create procedure 存储过程名		

16.函数
1.创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end

	注意：参数列表包含两部分：参数名 参数类型
		  函数体：肯定会有return语句，如果没有会报错
		  建议放在函数体的最后
	函数体仅有一句话，则可以省略begin end
2.调用语法
	select 函数名(参数列表)
3.案例	
	1.无参有返回
		create function myf1() returns int
		begin
			declare c int default 0; #定义局部变量
			select count(*) into c   #赋值
			from employees;
			return c;
		end$
	
		select myf1()$
	2.有参有返回
		create function myf2(empname varchar(20)) returns double
		begin
			set @sal=0;#定义用户变量
			select salary int @sal
			from employees
			where last_name=empname;
			return @sal;
		end$
	
		select myf1('k_ing')$
4.查看函数
	show create function 函数名;
5.删除函数
	drop function 函数名;