数据库小结

数据库小结：

• expire interval 密码存活时间
• expire never 密码永远存活
• 创建用户：create user “mike”@"%" identified by ‘000000’ password expire interval 2 day;
• 修改用户密码存活时间：alter user mike password expire interval 30day;
• 查看用户权限： select * from mysql.user where user=“mike” \G;

授权：

mysql > grant all privileges on . to ‘alex’@’%’ identified by ‘123456’ with grant option;
all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。
on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。
如果我要指定将权限应用到test库的user表中，可以这么写：test.user
to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。
比如：”alex”@”192.168.0.%”，表示alex这个用户只能在192.168.0IP段登录
identified by：指定用户的登录密码
with grant option：表示允许用户将自己的权限授权给其它用户

刷新权限： flush privileges;

撤权：revoke all privileges on . from “mike”@“localhost”;
改密码：set password for “mike”@“localhost” = password(“123456”);
更改用户名：rename user “mike”@“localhost” to “ake”@“localhost”;
查看权限：show grants;
show grants for “mike”@“localhost”;
select * from mysql.user where user=“mike”;
用户加解锁：alter user “mike”@“localhost” account lock;
alter user “mike”@“localhost” account unlock;

variables 查看配置 show variables like “%like%”;
engines 查看引擎 show engines;

事务的隔离级别：

	脏读	不可重复读	幻读
read uncommiteed	√	√	√
read committed	×	√	√	oracle（默认）
repeatable read	×	×	√	mysql（默认）
serializable	×	×	×

• 查看隔离级别 select @@tx_isolation;
• 设置隔离级别 set session|global transaction isolation level 隔离级别；

变量：

​ 查看变量： show global|session variables(like “%char%”);
​ 变量赋值： set global|session 系统变量名= 值；
用户变量：set @a:=10;
局部变量：declare c=10；

delimiter ￥

存储过程：

create procedure 存储过程名（参数列表）

​ begin
​ 存储过程体（一组合法的sql语句）
​ 每句sql语句必须；结尾
​ end ￥
​
参数列表： 参数模式 参数名 参数类型
​ in 输入 stuname varchar(20)
​ out 输出（into）
​ inout输入输出
调用方法 ： call 存储过程名（实参）￥
查看：show create function|proceduer 名字；
删除：drop function|proceduer 名字；
创建函数： create function 函数名（参数列表）returns 返回类型
​ begin
​ 函数体
​ return 值；
​ end ￥
参数列表 ： 参数名 参数类型
调用语法： select 函数名（参数列表）￥

字符函数：

​ length 长度 select length(“我爱你”); 根据服务器编码类型而定
​ concat 拼接字符 select concat(“a”,"_",“b”);
​ upper 大写 select upper(“asdd”);
​ lower 小写 select lower(“ASSS”);
​ substr 截取字符 select substr(“我爱你你你你你你你”,5); 截取坐标5后面所有字符
​ select substr(“woaininininininini”,2,5); 截取坐标2后面5个字符
​ instr 匹配第一次出现字符的坐标 select instr(“我爱你你知道不”,“我爱你”)；
​ trim 去除字符左右指定字符(默认空格) select length(trim(" 我爱你 "))；
​ select trim(“a”,“aaaaa我爱你aaa”)；
​ lpad,rpad 左右填充特定字符 select lpad(“我爱你”,10,“ab”); ab为填充的字符
​ replace 替换字符 select replace(“张无忌爱上周芷若”,“周芷若”,“赵敏”)； 张无忌爱上赵敏

数学函数：

​ round 四舍五入 select round(1.45);
​ 小数点保留位数 select round(1.456,2);
​ ceil 向上取整 select ceil(1.23);
​ floor 向下取整 select floor(12.2);
​ truncate 截断 select truncate(1.23,1);
​ mod 取余 select mod(10,3); mod(-10,3) a-a/bb -10-(-10)/33

日期函数：

​ now 当前日期时间 select now();
​ curdate 当前日期 select curdate();
​ curtime 当日时间 select curtime();
​ datediff 两时间差值 select datediff(“2018-02-1”,“2019-02-1”);
​ year,month,monthname,day…
​ str_to_date 时间格式 select str_to_date(‘1998-3-2’,’%Y-%c-%d’);
​ date_format 格式化时间 select date_format(now(),"%Y年%m月%d日")；

流程控制函数：

​ select if(10<5,‘da’,‘xiao’); xiao
​ case case 条件 when 值 then 值 else end(case)；
​ case case when 条件 tnen 值 else end(case)；
​ if结构：if 条件1 then 语句1；
​ elseif 条件2 then 语句2；
​ …
​ else 语句n；
​ end if；（只能用在begin end中）

循环结构：

while，loop，repeat
while： （标签：）while 循环条件 do
循环体；
end while（标签）；
loop： （标签：）loop
循环体；
end loop（标签）；
repeat： （标签：）repeat
循环体；
until 结束循环的条件
end repeat（标签）；

循环控制：

iterate like continue
leave like break

if not exists 判断是否存在
distinct 查重 select distinct * from day02;
between 两者之间 select * from day02 where id between 2 and 6;
like 模糊查询 select * from day02 where name like ‘%js_’;
% 表示多个字值，
_ 下划线表示一个字符；
M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M_ : 表示查询以M在倒数第二位的所有内容。
order by 排序 select * from day02 where id >2 order by name;
update 更新数据 update day02 set cal=‘121’ where id=3;
limit 显示条数 select * from day02 limit 2;
select * from day02 where id>6 limit 2;
regexp 正则查询 select * from day02 where name regexp ‘¹’;
as 别名 select d.name,d.cal from day02 as d;
inner join 内连接 select day02.id,student.age from day02 inner join student on day02.id=student.id;
创建拷贝数据表结构及数据 create table day02_1 as select * from day02;
拷贝数据表数据 insert into day02_1 select * from day02;
约束： NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

table创建完成后想修改:

not null 修改非空 alter table day02_1 modify name varchar(3) null;
alter table day02_1 modify name varchar(3) not null;
unique 唯一识别 alter table day02 add unique(id);
alter table day02 drop index id;
primary key 主键 alter table day02_2 add primary key(id);
alter table day02_2 drop primary key;
foreign key 外键 alter table stus add foreign key(sid) references stu(id);
alter table stus drop foreign key stus_ibfk_1;
cascade 级联 create table 表明(…,on delete set null,on update cascade);
constraint 约束 create table 表明(…,constraint 约束名 on delete set null,on update cascade);
check 检查

default 默认值 alter table day02_2 alter set defalut ‘jss’;
alter table day02_2 alter nam drop default;
truncate 删除数据 truncate table day02_1;
auto_increment 自增 alter table day02_1 modify id int(10) auto_increment;(必须是主键)
alter table day02_1 modify id int(10);
创建索引 create index xiao on day02(name);
创建索引 alter table day02 add index xiao(names);
删除索引 alter table day02 drop index xiao;
drop index xiao on day02;
查看索引 show index from day02;
创建唯一索引 create unique index xiao on day02(names)
alter table day02 add index xiao(naems);
删除索引 drop index xiao on day02;
alter table day02 drop index xiao;
创建视图 create view xiao as select id,name from day02 where id<3;
创建或更新视图 create or replace view xiao select id,name from day02 where id>3;
drop view xiao;

group by 分组 select name,count(*) from day02 group by name;
select * from day02 where id>2 group by id;
select * from day02 group by id having id>2;
begin | strat transaction 开启事务； 各种操作
rollback 事物回退；
summit 事务提交；
first 插入第一列位置 alter table day02 add pid varchar(3) first;
after 在特定列后插入 alter table day02 add pid varchar(3) after name;
modify 修改 alter table ady02 modify name varchar(4);
change 修改 alter table day02 change name names varchar(4);
rename to 修改表名 alter table day02_1 rename to day02_4;
temporary 创建临时表 create temporary table ceshi(id int,name varchar(2));
insert ignore into 忽视已存在的数据 insert ignore into day02 value(1,‘ws’,‘123’);
insert into 添加已存在的数据报错 insert into day02 value(1,‘ws’,‘123’);

---

1. j ↩︎