SQL
DQL的执行顺序
select 字段名 from 表名 where 条件 group by 分组字段名 having 分组后的条件列表 order by 排序字段名 limit 分页参数
- from
- where
- group by then having
- selelct
- order by
- limit
按照自然语言来说,就是从哪个表查记录(from),查询的条件是什么(where),为查询后的记录分组(group by),分组完再对记录进行过滤(having),完成二次筛选。然后列出查询的结果(select),之后再将结果按照某一字段进行排序(order by),最后再决定每页显示多少数据(limit)。
简单来说,只有三步。
- 先有目标,然后按条件筛选记录。
- 查询记录。
- 按照某种规则显示记录。
我们可以通过给表起别名的方式来验证它们的执行顺序。
先给user表设置别名为u,然后将name和age字段的别名设置为uname和uage,SQL语句可正确执行。
select u.name uname,u.age uage from user u where u.gender = '男' order by uname;
注:如果是多张表中的字段相同时,就需要采用表名.字段名的方式指定相应的字段名。如果表名过长,可以给表起别名。
DCL-数据控制语言
DCL用来管理用户对数据库的操作权限
查询数据库用户
注:可以对数据库进行操作的用户记录都存在mysql数据库中的user表中
#切换到mysql系统数据库
use mysql;
#从user表中查询记录
select * from user
创建数据库用户
create user '用户名'@‘localhost’ identified by '密码';
#实例 创建一个“penrose”用户,并设置密码为‘123456’
create user 'penrose'@'localhost' identified by '123456';
我们必须通过 用户名@主机名的方式才能定位到一个数据库用户
当我们以管理员的身份为数据库创建一个新用户时,默认情况下该用户没有任何操作权限。
此时我们可以通过命令行的方式使用“penrose”身份登录数据库。
修改用户密码
alter user '用户名'@‘localhost’ identified with mysql_native_password by '新密码';
#实例
alter user 'penrose'@'localhost' identified with mysql_native_password by '123123';
如果将主机名写为%,则说明任意主机都可以访问MySQL服务器
删除数据库用户
drop user '用户名'@'主机名';
#实例
drop user 'penrose'@'localhost';
查询用户权限
show grants for '用户名'@'主机';
#实例
show grants for 'penrose'@'localhost';
### 授予用户权限
grant 权限列表 on 数据库名.表名 to 'admin'@'localhost';
#实例 为penrose用户授予所有数据库的所有权限
grant all on *.* to 'penrose'@'localhost';
#为admin用户授予 更新,插入,查询test数据库中的所有表权限
grant update,insert,select on test* to 'admin'@'localhost';
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
#实例 撤销admin用户对所有数据库中的所有表的更新、查询和删除操作
revoke update,select,drop on *.* from 'admin'@'localhost';
注:代表所有,.*表示所有数据库的所有表
MySQL中的内置函数
字符串函数
select 函数(参数)
#将字段 name和age用“-”连接
select concat(name,'-',age) from employee;
#将“Hello”转化为小写和大写字母
select lower('Hello');
select upper('Hello');
#对字符串“Hello”进行左填充“x”,使之达到10个字符
select lpad('Hello',10,'x'); //xxxxxHello
#去掉字符串头部和尾部空格,但中间的空格去除不了
select trim(' Hel lo '); //Hel lo
#截取“Hello”字符串,从第一个位置截取,截取长度为2
#注:此处字符串的索引值从1开始,其函数的功能就是截取子串
select substring('Hello',1,2); //He
#将employee表中name字段的长度都设置为8,不够的右填充‘x’
#函数每一次返回的是一个值,需要用字段接收它
update employee set name = rpad(name,8,'x');
数值函数
#将18.1向上取整
select ceil(18.1); //19
#将18.9向下取整
select floor(18.9); //18
#10%9,并将此字段取别名为“取余”
select mod(10,9) 取余; //1
#生成一个包含0但不包括1的随机数
select rand() '[0,1)的随机数';
#将18.157四舍五入,保留两位小数
select round(18.157,2); //18.16
#将18.1四舍五入,不保留小数
select round(18.1,0); //18
#将18.19四舍五入,保留一位小数
select round(18.19,1); //18.2
#生成一个6位数的随机验证码
#有bug的写法,这种写法没有考虑到生成的随机数是0开头的整数,如0.0123456789*1000000=012345.6789,最后生成的结果是12345.,实际上最高位为0是不输出的,而且小数点也占一位。
select lpad(rand()*1000000,6,'0');
#正确写法,将其保留为整数就去除了小数点
select lpad(round(rand()*1000000,0),6,'0');
注:对于round()函数,四舍五入的值的位数尽量大于保留的位数,这样才有效果
日期函数
#查询现在的日期
select curdate();
#查询现在的时间
select curtime();
#查询现在的日期和时间
select now();
#查询现在往后退70天的日期和时间,如果是往前推70天,则就用负号
select date_add(now(),interval 70 day);
#现在的日期和“2021-12-26”相差多少天,用第一个参数--第二个参数
select datediff(curdate(),'2021-12-26');
流程函数
#如果条件为真,则返回ok,否则返回no,类似于三元表达式的逻辑
#if()的第一个参数多是条件表达式
select if('1','ok','no');
#只有ifnull()第一个参数不为null,则就返回第一个参数,否则返回第二个参数
select ifnull(null,'1'); //1
select ifnull('','1') //
#查询员工姓名和工作地址,如果工作地址在杭州或北京,则显示一线城市,否则显示二线城市
select
name,
(case workaddress when '杭州' then '一线城市' when '背景' then '一线城市' else '二线城市' end) as '工作地址'
from employee;
#统计班级学员分数
select
id,
name,
(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end) '数学',
(case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end) '英语',
(case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end) '语文'
from score;
注:如果某一属性只有几个值,可以使用(case 字段 when ‘x’ then ‘xx’ end)这样的语句,如果某一字段是在某一个范围内,则使用(case when 字段名 条件 end)