MySQL基本语句

一、创建数据库基本操作

-- 1.创建
create database 数据库名
-- 2.修改数据库名
rename database old_数据库名 to new_数据库名
-- 3.删除数据库
drop database 数据库名
-- 4.查看数据库列表
show databases;

二、基本表操作

-- 1.创建表
 create table 表名;
 
-- 2.修改表名
alter table old_表名 rename new_表名;

-- 3.删除表
drop table 表名;

-- 4.查看数据表结构
describe 表名;

-- 5.查看数据库中的表
show tables;

-- 6.给表中添加字段
alter table 表名
add 字段;

-- 7.修改表中字段的数据类型
alter table 表名
modify [column] 列名 新数据类型;

-- 8.删除列
alter table 表名
drop column 列名;

-- 9.创建主键
(1)在创建表时添加主键，直接在字段后添加primary key即可；

(2)表创建好之后增加主键：
alter table 表名
add constraint pk_字段名
primary key(字段名);

-- 10.添加外键约束
(1)创建表时添加外键,
constraint fk_字段名 foreign key(字段名) references 主表名(外键关联的主表中的字段名);

(2)表创建好之后添加外键
alter table 表名
add constraint fk_字段名 foreign key(字段名) references 主表名(字段名);

-- 11.删除主键
(1)不存在其他约束时，直接删除
(2)如果存在其他约束，先解除约束再删除
alter table 表名 drop primary key;

-- 12.默认约束
(1)创建默认约束：
alter table 表名 
alter column 字段名 set default 默认值;
(2)删除默认约束：
alter table 表名
alter column 字段名 drop default;

-- 13.修改列名
alter table 表名
change column 旧字段名 新字段名 数据类型;

三、增删改

-- 1.插入数据
insert into 表名 (列名) values(值);

-- 2.更新数据
update 表名 set 列名 = 值 where 条件;

-- 3.删除数据
delete from 表名 where 条件；
**需要注意的是利用子查询删除数据时，**
**这个子查询不能是同一张表，如果是同一张表，需要用as取别名**

四、单表查询语句

-- 1.基本查询语句
select * from 表名;
select distinct表示输出无重复的结果；

-- 2.where 
select * from 表名 where 条件;

-- 3. 模糊匹配
select * from 表名 where 列名 like ' % ';

-- 4.排序
order by语句必须是最后一个子句
asc升序，desc降序;

-- 5. limit限制结果数量
select * from 表名 limit 10;  //查询前10条；
select * from 表名 limit 2,5; //从3开始的5条，跳过
select * from 表名 limit 5 offset 2; //从3开始五条，即取3，4，5，6，7
注意：limit语句必须在order by语句之后

-- 6.case表达式
case 测试表达式 
	when 条件1 then 结果
	when 条件2 then 结果
	```
	else 结果
end;

-- 7.聚合函数
count(*) //求记录个数
sum(列名) //求某一列总和
avg(列名) // 求某一列平均值
max()
min()

-- 8. 分组统计group by语句
**常配合聚合函数使用**
**分组后进行筛选**
使用having语句；having一定跟在group by子句后面;

-- 9.union关键字
将一个结果表中包含两个select语句的结果，语法：
select 查询1
union
select 查询2

五、多表查询语句

-- 1. 内连接
select 所需内容 from 表1 inner join 表2
on 条件
where 条件;

-- 2.自身连接
**查询信息为同一张表**
**必须为两个表取别名**
例子：查询与徐浩同班的其他同学信息
select s1.code,s1.name from student s1,student s2
where s1.class = s2.class and
s2.name = '徐浩' and s1.name != '徐浩'
order by s1.name;

六、子查询语句

-- 1.各种子查询多值比较运算符
[not]in,[not]exists
any,all必须与比较运算符结合使用
-- 例：查询比子查询结果中所有值都大
select 内容 from 表名 where 列名 > all
(子查询);
-- 比任一结果大
select 内容 from 表名 where 列名 > any
(子查询);

七、索引

-- 1.创建索引
create [unique] index on 表名(列名);

-- 2.查看索引
show index from 表名;

-- 3.删除索引
drop index 索引名 on 表名;

八、视图

-- 1.创建视图
create view 视图名 
as
select语句;

-- 2.修改视图
create or replace view 视图名
as
select语句;

--3.删除视图
drop view 视图名;

九、一些库函数

bool常量用1代表true，0代表false
null值表示不确定的值，

局部变量：
	set @变量名 = 
全局变量：
	set @@变量名

重置命令结束标记
	delimiter 符号;

if 条件 then
	sql语句块
elseif then
	sql语句块
else
	sql语句块
end if;

while 条件 do
	sql语句块
end while;

repeat 
	sql语句块;
	untile 条件
end repeat;

-- 合并字符串函数
concat(s1,s2);
-- 返回字符串长度
char_length(str);
-- 返回字符串字节长度
length(str);

-- 字符串大小写转换
lower(str);
upper(str);

-- 删除空格
ltrim(str) -- 返回删除前导空格的字符串
rtrim(str) -- 返回删除尾部空格的字符串
trim(str) -- 返回删除两侧空格的字符串

-- 取子串函数
substring(str,start,length) -- 返回字符串str从start开始长度为length的字串
-- 返回n个空格
space(n)
-- 数学函数
abs(x) --返回绝对值
PI()-- 返回圆周率
sqrt()
mod(m,n)-- 返回m被n除后的余数
round(x,y)-- x四舍五入到y指定的精度
floor(x) -- 返回小于等于x的最大整数

-- 日期函数
curdate()  -- 返回当前系统日期，格式为'YYYY-MM-DD'
localtime(),now() -- 日期精确到秒
year(d),month(d),day(d),分别返回年月日
adddate(date,interval 值) -- 用于在日期上添加一段时间，值为几天几秒等等

十、存储函数

-- 创建存储函数
create function 函数名(参数名 数据类型···)
returns 返回值的数据类型                                /*注意returns语句不加分号
BEGIN
	declare 局部变量 数据类型 [default 默认值];  -- 声明变量
	-- 为变量赋值
	set 局部变量 = 表达式;
	select 查询变量 into 局部变量 from 表名 条件语句
	函数体;
	return语句;
END

-- 调用存储函数
select 函数名(参数值);

-- 删除存储函数
drop function 函数名;

十一、存储过程

-- 创建存储过程
delimiter $$
create procedure 名字 (
	in 输入参数名 数据类型,
	in 输入参数名 数据类型···
	out 输出参数名 数据类型
)
BEGIN
	
END$$
delimiter ;

-- 删除存储过程
drop procedure 名字;

-- 存储过程的异常处理，定义要放在declare的最后
declare continue|exit handler for 
SQLWARNING -- 值从01开始的sqlstate
|NOT FOUND -- 值从02开始的sqlstate
|SQLEXCEPTION --值不为00，01，02开始的其他sqlstate

或者直接在if then语句后写
signal sqlstate '  ' set message_txt = '异常';

十二、触发器

-- 创建触发器
delimiter $$
create trigger 名字
before|after
insert|update|delete
on 表名
for each row
BEGIN
	-- new代表更新操作的新行，old代表刚修改的历史行，使用new.和old.
END $$
delimiter ;

-- 删除触发器
drop trigger 名字;

十三、游标

1.声明游标
declare 游标名 cursor for select语句

2.打开游标
open 游标名

3.提取数据
fetch 游标名 into 变量名

4.关闭游标
close 游标名

十四、授权

1. 角色授权
grant connect|Resource|DBA to 用户 identified by 口令

2.角色取消
revoke <角色管理> from 用户

3.数据库对象授权
grant all privileges|select|delete|insert|update
on 表名 to 用户
with grant option;

其中all privileges为所有权限
with grant option表示得到授权的用户可将其获得的权限转移给其他用户。

4.取消数据库对象授权
revoke 权限 on 表名 from 用户

5.授予mysql存储程序级别权限
grant 权限名称 on function|procedure 数据库名.函数名|数据库名.存储过程名
to 用户 with grant option;

6.授予mysql数据库级别权限
grant 权限名 on 数据库名.* to 用户 with grant option;

7.授予mysql服务器管理员级别权限
grant 权限名 on *.* to 用户 with grant option;

8.撤销所有权限
revoke all privileges,grant option from 用户

9.撤销指定权限
revoke 权限名 on *.*|数据库名.*|数据库名.表名或视图名 from 用户