用户权限及角色

创建用户

with auth_plugin是指定用户口令加密方式，不写就是默认。role是指定用户的缺省角色，（缺省角色本质上是一组权限集合）在 MySQL 中，缺省角色（Default Role）是指为用户预先分配的角色，当该用户在登录时没有明确指定角色时，系统会自动使用这个缺省角色。角色是 MySQL 中的一种权限管理机制，角色本质上是一组权限的集合，用户可以通过角色继承这些权限。通过指定缺省角色，可以简化用户权限的管理。

create user 'usre_name'@'lochalhost' identified with auth_plugin by 'password' DEFAULT ROLE role,.....;

修改用户

--修改用户口令密码
alter user user_name identified with auth_plugin by 'password' DEFAULT ROLE role,......;

--修改用户缺省角色
alter user user_name DEFAULT ROLE role;
--设置用户缺省角色,效果与上一个相同
set DEFAULT ROLE role to user_name;

--启用所有角色
set role all;
--启用指定角色
set ROLE role;

--修改指定用户口令密码
set password for user_name = 'password';
--修改当前用户口令密码
set password = 'password';

--删除用户
drop user user_name;

权限管理

--授予权限
grant priv_type on [object_type] priv_level to user_or_role;
/*
priv_type:具体的权限类型
object_type:权限所属对象类型
    	table：表，默认类型
    	function：存储函数
	   	procedure：存储过程
priv_level:指定权限范围
		* 数据库权限，当前数据库所有对象
		*.* 全局对象，所有数据库的所有对象
		db_name.* 数据库权限，指定数据库的所有对象
		db_name.tbl_name 对象权限，指定数据库的指定对象
		db_name.routine 对象权限，指定数据库的存储程序对象，过程或函数
user_or_role:指定被授权者，可以是用户名也可以是角色名
*/

--回收权限
revoke priv_type on [object_type] priv_level from user_or_role;

--查看当前登录用户权限
show grants;
--查看指定用户权限
show grants for user_name;
--查看用户权限以及用户被授予的角色的权限
show grants for user_name using role;

--删除角色
drop ROLE role;

--刷新权限： 刷新权限表，使修改的权限立即生效。
FLUSH PRIVILEGES;
--刷新表缓存： 刷新表缓存，通常用于在某些情况下强制重新加载表结构。
FLUSH TABLES;
--刷新日志： 刷新日志文件（例如二进制日志），可以将当前日志文件关闭并开始一个新的日志文件。
FLUSH LOGS;
--刷新临时表： 刷新临时表。
FLUSH TABLES WITH READ LOCK;
--刷新所有缓存： 刷新所有缓存，强制清除一些缓存。
FLUSH STATUS;
--刷新查询缓存： 刷新查询缓存。
FLUSH QUERY CACHE;

数据库和表操作

--创建数据库
create database db_name;

--删除数据库
drop database db_name;

--创建表
create table tbl_name(id int,name char(10));
--修改表
alter table tbl_name add gender char(10) after name;--再name后添加gender
alter table tbl_name drop gender;--删除gender列
alter table tbl_name modify name char(10) not null;--设置name非空
alter table tbl_name rename column old_col_name to new_col_name--修改列的名字
--删除表
drop table tbl_name;

表数据操作

--插入数据
insert into tbl_name values(value_list)[,(value_list)];
--修改数据
update tbl_name set sol_name = .... [where 条件] [order by...排序] [limit 指定修改的行数];--asc升序，desc降序
--删除数据
delete from tbl_name [where 条件] [order by...排序] [limit 指定修改的行数];

表约束

主键约束

--主键约束 primary key
--添加主键约束
--创建表时添加
create table tbl_name(
	id int primary key,
    name char(10)
)
--或
create table tbl_name(
	id int,
    name char(10),
    primary key(id)
)
--修改表添加主键
alter table tbl_name add primary key(id);

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

外键约束

--外键约束 references、foreign
--添加外键约束
--创建表时添加
create table tbl_name(
	id int references ref_tbl_name(ref_col_name),
    name char(10)
)
--或
create table tbl_name(
	id int,
    name char(10),
    foreign key(id) references ref_tbl_name(ref_col_name)
)
--修改表添加
alter table tbl_name add foreign key(id) references ref_tbl_name(ref_col_name) [on delete,on update];
/*
[ON DELETE 删除操作]：定义当主表的记录被删除时，外键表的行为。常用的选项有：
        CASCADE：当主表记录被删除时，外键表中相关记录也会被删除。
        SET NULL：当主表记录被删除时，外键表中的相关列值将被设为 NULL。
        NO ACTION 或 RESTRICT：如果主表记录被删除，且外键表中存在相关记录，则不允许删除。
[ON UPDATE 更新操作]：定义当主表的记录被更新时，外键表的行为。常用的选项有：
        CASCADE：当主表记录的值被更新时，外键表中相关记录的外键值也会自动更新。
        SET NULL：当主表记录被更新时，外键表中相关列的值会被设为 NULL。
        NO ACTION 或 RESTRICT：不允许更新主表记录，如果外键表中存在相关记录。
*/

唯一约束

--唯一约束 unique
--添加唯一约束
--创建表时添加
create table tbl_name(
	id int unique,
    name char(10)
)
--或
create table tbl_name(
	id int,
    name char(10),
    unique(id)
)
--修改表时添加
alter table tbl_name add uniques(id);

非空约束

--非空约束 not null
--添加非空约束
--创建表时添加
create table tbl_name(
	id int not null,
    name char(10)
)
--修改表时添加
alter table tbl_name modify id int not null;

单表查询

--查询所有信息
select * from tbl_name;
--查询指定列信息
select col_name from tbl_name;

--分页查询
select * from tbl_name order by col_name {desc|asc} limit 0,3;--从第一条数据开始显示3条数据

--指定过滤条件
select * from tbl_name where 条件
/*
分组函数
avg(x)返回x列的平均值
min(x)返回x列的最小值
max(x)返回x列的最大值
count(x)返回x列的行数
sum(x)返回x列的和
*/
--分组查询 group by
select * from tbl_name group by col_name;

--排序
select * from tbl_name order by [asc,desc]--asc升序,desc降序，默认是asc

连接查询和子查询

连接查询

--内连接（INNER JOIN）是 SQL 中最常用的连接类型，它用于返回两个表中匹配的行。通过内连接，只有当两张表中某些字段的值匹配时，才会返回这些行的数据。
--连接语法一
select column_list from tbl_name1,tbl_name2 where condition;
--连接语法二
select column_list from tbl_name1 join tbl_name2 on condition
--join 子句，指定要连接的表，并在on后指定连接条件，如果要连接多个表就使用多次join...on... ，using(col_name)等同于tbl_name1.col_name=tbl_name2.col_name

--外连接（OUTER JOIN）是 SQL 中另一种常用的连接类型，它用于返回两个表中所有的匹配记录和未匹配的记录。
select column_list from tbl_name1 join tbl_name2 {lift|right} on condition
--左外连接返回左边表（第一个表）中的所有记录，以及右边表（第二个表）中与之匹配的记录。如果右边表没有匹配的记录，则会返回左边表的记录，并且右边表的相关列会显示 NULL。
--右外连接与左外连接相反，返回右边表（第二个表）中的所有记录，以及左边表（第一个表）中与之匹配的记录。如果左边表没有匹配的记录，则会返回右边表的记录，并且左边表的相关列会显示 NULL。

子查询

--单行子查询
--查询表中年龄最小的员工信息
select * from tbl_name where age=(select min(age) from tbl_name);

--多行子查询
--查询年龄大于所有部门平均年龄的员工信息
select * from emp where age>all(select avg(age) from emp group by dname)
--多行子查询（Multiple-row Subquery）是指在 SQL 查询中使用子查询返回多行数据，然后将这些多行数据用于外部查询的条件中。
/*
多行子查询通常用于以下几种情况：
    IN：检查某列的值是否在多行子查询的结果中。
    ANY / SOME：与子查询结果中的任何一行进行比较。
    ALL：与子查询结果中的所有行进行比较
*/

--关联子查询
--查询每个部门年龄最小的员工
select * from emp o where age=(select min(age) from emp i where i.dno=o.dno);
--关联子查询（Correlated Subquery） 是指在 SQL 查询中，子查询的执行依赖于外部查询的每一行数据。

索引和视图

索引

--创建索引
--创建表并创建索引
create table tbl_name(id int,{index|key} index_name(col_name));
--修改表创建索引
alter table tbl_name add {index|key} index_name(col_name);
--使用create index创建索引
create [unique] index index_name on tbl_name(col_name);

--查询索引
show index from tbl_name;

--删除索引
--使用alter
alter table tbl_name drop {index|key} index_name;
--使用drop
drop index index_name on tbl_name;

视图

--创建索引
create view view_name as select_startment;
--例
create view v1 as select * from tbl_name where salary>5000;

--删除视图
drop view view_name;

内置函数

/*
----数字函数
abs(x)			x的绝对值
ceil(x)			返回比x大的最小整数
floor(x)		返回比x小的最大整数
mod(n,m)		返回n除以m的余数
pow(x,y)		返回x的y次幂
rand([n])		返回一个随机数，如果指定n则返回的随机数固定
round(x[,d])	返回一个四舍五入的数，d指保留小数的位数，如果未指定则默认0
truncate(x,d) 	返回一个截断后的数，d指保留小数位数
----字符函数
ascii(str)		返回str左侧第一个字符的数字编码，如果str为空，返回0
char(n,...[using charset_name]) 将ascii数字还原成字符
char_length(str) 返回字符串str的字符个数
concat(str1,str2,...) 将str1,str2...拼接成一个字符串
instr(str,substr) 返回substr在str中的位置，没有则返回0
locate(substr,str,[position]) 返回substr在str中的位置，没有则返回0，从position位置开始，没指定就从第一个字符开始
left(str,len)	从str左侧取长度为len的字符串
length(str)		返回str的字节长度
lower(str)		将str全部转成小写
upper(str)		将str全部转成大写
lpad(str,len,padstr) 从左侧对字符串str进行填充，长度不足len的部分用padstr填充
rpad(str,len,padstr) 从右侧对字符串str进行填充，长度不足len的部分用padstr填充
ltrim(str)		将字符串左侧空格删除
rtrim(str)		将字符串右侧空格删除
repeat(str,count)将str重复count次
replace(str,from_str,to_str) 将字符串中所有from_str替换成to_str
replace(str)	将字符串str的字符进行反转
right(str,len)	从str的右侧取长度为len的字符
space(n)		构造包含n个空格的字符串
substr(str,pos[,len]) 从str的pos指定的位置开始取字符串，如果没有指定长度len，则从pos取到末尾
trim([remstr from] str) 从str的两侧删掉指定字符remstr，如果没指定就删除空格
----时间函数
1. 获取当前日期和时间
CURRENT_DATE / CURDATE(): 返回当前的日期（不包括时间）。
CURRENT_TIME / CURTIME(): 返回当前的时间（不包括日期）。
CURRENT_TIMESTAMP / NOW(): 返回当前的日期和时间。
LOCALTIME / LOCALTIMESTAMP: 返回当前的日期和时间（与 CURRENT_TIMESTAMP 等效，但用法更明确）
2. 日期和时间的提取
这些函数可以从日期或时间中提取具体的部分（年、月、日、小时、分钟、秒等）。
YEAR(date): 提取年份。
MONTH(date): 提取月份。
DAY(date) / DAYOFMONTH(date): 提取日期中的“日”部分。
HOUR(time): 提取小时。
MINUTE(time): 提取分钟。
SECOND(time): 提取秒。
DAYOFWEEK(date): 返回星期几，1 为星期日，7 为星期六。
WEEK(date): 返回日期所在的周数
3. 日期和时间的计算
DATE_ADD(date, INTERVAL expr unit): 给定日期加上指定的时间间隔。
DATE_SUB(date, INTERVAL expr unit): 从给定日期中减去指定的时间间隔。
DATEDIFF(date1, date2): 计算两个日期之间的天数差异。
TIMESTAMPDIFF(unit, datetime1, datetime2): 计算两个日期时间之间的差异，按指定的单位返回（如年、月、日、小时等）。
4. 日期格式化
DATE_FORMAT(date, format): 按照指定的格式返回日期或时间。
常用的格式化符号：
%Y: 四位数年份（如 2024）
%m: 月份（01-12）
%d: 日（01-31）
%H: 小时（00-23）
%i: 分钟（00-59）
%s: 秒（00-59）
5. 日期和时间的转换
STR_TO_DATE(str, format): 将字符串按照指定格式转换为日期。
DATE(): 提取日期部分（去掉时间部分）。
TIME(): 提取时间部分（去掉日期部分）。
CAST(): 将日期时间转换为不同的类型（如转换为 DATE、TIME 等）。
6. 时区处理
一些数据库系统（如 MySQL）允许对时区进行处理：
CONVERT_TZ(datetime, from_tz, to_tz): 将一个日期时间从一个时区转换到另一个时区
7. 其他有用的日期函数
LAST_DAY(date): 返回给定日期所在月的最后一天。
NOW(): 返回当前的日期和时间。
EXTRACT(unit FROM date): 提取指定时间部分，如年、月、日等（适用于某些数据库系统，如 PostgreSQL）。
*/