四、SQL语言3(DQL+DCL)
6.DQL
目的
在mysql管理软件中，可以通过sql语句中的dql语言来实现数据的 查询操作
如互联网用户查询余额，查询装备，查询商品的操作。
查看表结构
desc 表名;
查询所有列
select * from 表名;
查部分列
select 列1,列2,列4 from 表名; select id,name from t3;
示例：
建立素材
建立database
create database company;
建立表employee5
create table company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
);
查看表结构
desc company.employee5;
插入数据
insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id)values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instrcutor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('aofa','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('guoguo','male','20180205','sale','',2200,503,102);
查看表内容
select * from employee5;
查看部分列
select name,salary from employee5; 只看名字与其对应工资
通过四则运算查看部分列
select name,salary,salary*12 from employee5; 看名字与其对应的月薪与年薪
单条件查询where
select name from employee5 where post='hr'; 只看职位是hr的名字
多条件查询and/or
select * from employee5 where post='hr' and salary>1000;
select name,salary from employee5 where salary=6000 or salary=8000;
关键字between and 在什么之间
select name,salary from employee5 where salary between 5000 and 15000;
关键字between and 可以加not 不在什么之间
select name,salary from employee5 where salary not between 5000 and 15000;
关键字in集合查询
select name,salary form employee5 where salary in (4000, 5000, 6000, 9000);
关键字is null
select * from employee5 where salary job_description is null;
关键字like模糊查询
select * from employee5 where name like 'al%'; 通配符%代表多个任意字符，shell里是*，mysql里是%
select * from employee5 where name like '%ice';
select * from employee5 where name like 'a____' 通配符‘_’代表1个任意字符，shell里是'?'，mysql里是'_'
查询排序
升序(从低到高)
select * from employee5 order by salary ASC;
降序(从高到低)
select * from employee5 order by salary DESC;
工资最高前五
select * from employee5 order by salary DESC limit 5;
7.DCL
权限级别
Global level 所有库，所有表的权限
Database level 某个数据库中的所有表的权限（常用）
Table level 库中的某个表的权限
Column level 表中的某个字段 的权限（基本不用）
创建用户
show databases;
use mysql;
show tables;
create user user1@'localhost' identified by 'User1@123'; 创建用户user1
create user user1@'192.168.153.%' identified by 'User1@123' 授权给192.168.153之下的所有ip
删除用户
show databases;
use mysql;
show tables;
drop user 'user1'@‘localhost’; 删除用户：user1@localhost
修改用户密码
示例1
root修改自己密码
mysql -uuser1 -p'User1@123' 登录user1
select user(); 可以看到用户是谁
show databases; 可以发现该用户没有其他库的权限
示例2
set password=password('User1@12345'); 重设密码+密文保存
flush privileges; 刷新权限
示例3
mysql -uroot -p'dsfjlk12dhe-' password 'Root@123'
丢失root用户密码
vim /etc/my.cnf 打开my.cnf
[mysqld]
skip-grants-tables 编辑条件登录不需要密码
:wq 保存
systemctl restart mysqld 重启mysqld服务
mysql -uroot 登录成功
update mysql.user set authentication_string=password('Qianfeng@123') where user='root' and host='localhsot'; 重设密码+密文
flush privileges; 刷新权限
\q 退出mysql
mysql -uroot 登录失败
mysql -uroot -p'Qianfeng@123' 登陆成功
\q 退出mysql
vim /etc/my.cnf 打开my.cnf
#skip-grants-tables 把之前编辑的注释掉
:wq 保存
systemctl restart mysqld 重启mysqld服务
mysql -uroot =p'Qianfeng@123' 登陆成功
登录mysql
mysql -P 3306 -u root -p‘Qianfeng@123’
-P mysql服务器端口 默认3306
-u 指定用户名 默认root
-p 指定登录密码 默认为空密码
ss -anpt | grep mysql 可以查看mysql的端口号