mysql第二篇

权限

• 一.权限

DCL语句，数据库控制语句
1.查看账号权限
show grants for root@'localhost';

 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

WITH GRANT OPTION： 表示这个账号是可以创建账号并对其他账号做授权的
show grants: 表示查询当前账号的权限

2.通过权限管理表来查询
mysql.user 是数据库账号存储的表

select * from mysql.user;
select * from mysql.user WHERE user='root' and host='localhost';
select * from mysql.user WHERE user='root' and host='localhost'\G; #\G 表示格式化输出

通常使用场景：查看当前数据有哪些账号

select user,host from mysql.user ;

注意：
mysql的账号由两部分构成，user和host
host 指的是来源IP或host，类似于白名单性质

• 二.创建账号

create user '账号'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';

• 三.授权

#创建一个lesson2的库
 - create database lesson2;  
# 给账号lyy授权
 - grant select on lesson2.* to 'lyy'@'localhost'; 

注意：修改权限后，对于已存在的链接不生效，需要重新登陆

• 四.细分权限

1.增删改查 ： insert、 delete、 update、 select

#给账号授权
 grant insert,delete,update,select on lesson2.* to 'lyy'@'localhost';

2.DLL权限 ： create，drop，alter

grant drop on lesson2.* to 'lyy'@'localhost';
drop table t;

3.all权限
所有权限 包含了增删改查，创建表库等所有权限；一般不建议授予给账号，权限太大，风险太大

grant all privileges on lesson2.* to 'yeq'@'localhost';

4.局部权限和全局权限

grant create user on *.* to 'lyy'@'localhost';

grant create user on lesson2.* to ‘lyy’@‘localhost’; 执行会报错：ERROR
1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
原因：create user是个全局权限，授予的时候，左右范围必须是*.*

• 五.回收权限

revoke
标准语法： revoke 权限on x.x from user@‘localhost’;

revoke all on lesson2.* from 'lyy'@'localhost';

注：权限变化后，最好是重新创建连接，屏蔽session原因导致的权限不生效

• 六.修改账号密码

方式一：
set password for user@‘host’=password(‘新密码’)；
注：8.0版本上新密码前面不再需要password关键字

set password for 'lyy'@'localhost'='Abc@123';

方式二：

alter user 'lyy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Abc@123';

方式三：
Linux命令行： mysqladmin命令

• 七.删除账号

方式一：drop user ‘账号’@‘localhost’;
方式二：delete from mysql.user where user=‘账号’
注：delete 方式必须执行flush操作

• 八.help用法

help 关键字

• 九.FLUSH PRIVILEGES;

刷新权限：实际是指刷新权限信息到内存中

mysql的权限认证体系
@1.MySQL的权限是加载在内存中的，每次账号登录时在内存中完成权限校验
@2，磁盘上mysql库下面的权限信息何时主动加载到内存
grant，revoke等权限操作时
mysql启动时
执行flush privileges时

有一种场景就是通过sql语句insert,update方式来变更权限时这个时候权限信息是在磁盘上的，必须执行flush privileges
操作权限才会被加载到内存中生效

尤其时备份还原后，经常出现，select mysql.user有权限但不生效，这时候必须执行flush操作

sql基础

DCL 数据控制语句，主要是做权限控制，比如创建账号，授权，修改账号密码
DDL 数据定义语句，定义不同的数据段，数据库列表索引等 数据库对象的，常用的关键字主要包括 create/drop/alter 说白了 是库表等结构化的操作
DML 数据操操纵语句，用来添加，删除，更新，查询数据记录的，常用的关键字 insert/delete/update/select 说白了是具体数据层面操作

• 一.创建数据库

语法：

 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

{} 大括号里的是多选一
[] 中括号是可选项，可以选择有，也可以选择没有

create database lesson2;
create database IF NOT EXISTS  lesson2;

查看数据库创建信息

show create database lesson2;

CREATE DATABASE lesson2
/*!40100 DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci / /!80016
DEFAULT ENCRYPTION=‘N’ */ |

删除库：

drop database 库名;

查看当前数据库服务器上有哪些库:

show databases;

切换/使用库：

use 库名;

二.表的DDL操作
创建表
语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

简单创建：create table 表名 (至少一列字段);

create table tb_test(a int,b char);

如创建员工表emp

Create table emp
(ename varchar(20),
hiredate date,
salary int
);

查看表的创建情况：

show create table tb_test;

  desc tb_test;

查看当前库里的所有表：

show tables; 

删除表：

drop table tb_test

修改表：
1.删除表后，重建，这种情况数据会被清空。alter语句做有存量数据的表的修改
如 员工表：

Create table emp
(
ename varchar(20),
hiredate date,
salary int
);

在员工表里增加一列手机号：
语法：

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

alter table emp 
ADD column phone_no int;

同时增加多个字段：

alter table emp 
ADD COLUMN dept_no int,
ADD COLUMN age int;

删除字段：

ALTER table emp
drop column salary;

修改字段/修改字段的位置

alter table emp
modify age int FIRST;

ALTER table emp
modify age int after ename;

修改字段的类型
在数据库中每一个字段只能有唯一的数据类型
数据类型就代表我这一列存储的数据属性，是数值的，还是字符的，还是时间
phone_no int 数值，char字符来存

alter table emp
modify phone_no char(11);

修改字段名

alter table emp
change ename name varchar(20);

练习：
1.创建一个学生表 tb_student
2.有三个字段，姓名，年龄，学号

CREATE TABLE `tb_student` (
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `ID` int DEFAULT NULL
)

3.增加手机号，籍贯，性别

CREATE TABLE `tb_student` (
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `ID` int DEFAULT NULL,
  `phone` int DEFAULT NULL,
  `Hometown` varchar(100) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL
)

4.尝试修改某一个字段名，字段类型

alter table tb_student
change name ename char(20);

DML数据库操纵语句：

数据操纵语句
insert/delete/update/select
增删改查

• 1：insert

插入一条数据

insert into emp 
values('sunwukong',999,'2008-07-01','110',1);

注意点，默认一个括号内是一行数据，括号内的数值或字符需要与表的列数匹配
如何字段数量不匹配，插入就会报错

查看表数据

select * from emp;

插入多条数据

insert into emp
values('zhubajie',888,'2009-01-01',110,1),
      ('shaseng',777,'2010-03-15',120,1);

Query OK, 2 rows affected (0.01 sec)—代表sql执行总时间
Records: 2 Duplicates: 0 Warnings: 0
2 rows affected 代表sql影响的行数

指定某一些字段进行插入

insert into emp(name,age)
values('tangseng','24');

其他字段系统会自动分配默认值
部分插入时，一定要保障未插入字段是有默认值得才可以，如果关闭默认值，插入则会报错
create table t(a int not null,b char not null );
insert into t(a) values(1)

• 2: delete 删除

delete from tb_name ;  #代表全部删除

delete from tb_name where name='xxx'; #部分删除

where 条件是个复杂的组合
支持范围 <>
多个条件并列 where name=‘xxx’ and age >100;
子查询等等

truncate tb_name ; #清空表所有数据

delete 和truncate的区别：
truncate只能用来清理表的所有数据，不支持where条件
相当于drop表后重建，会重置自增字段的起始值。

delete只清空表的数据,不会将自增字段的起始值置为1;

数据库的自增字段
create table test_del
(
id int auto_increment primary key,
name varchar(20)
)
insert into test_del values(1,‘aa’),(1,‘bb’);
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘test_del.PRIMARY’
mysql中主键必须是唯一的，一旦有重复，插入会报错
insert into test_del(name) values(‘cc’);

show create table test_del\G
Table: test_del
Create Table: CREATE TABLE test_del (
id int NOT NULL AUTO_INCREMENT,
name varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

• 3: 更新 update：

update
全表更新，不带where条件

update emp set dept_no=2;

insert into emp
values('zhubajie',888,'2009-01-01',110,1),
      ('shaseng',777,'2010-03-15',120,1),
      ('Li',23,'2001-01-01',119,1),
      ('Zhang',30,'2019-03-15',110,2)

带有where 条件，就会将where条件匹配到的行进行更新操作

update emp set age=40 where name='sunwukong';
update emp set age=38 where age >100;
update emp set dept_no=3 where age>35;

部门号是3，同时年龄到40岁的要被裁掉,将部门号置位改为0
update emp set dept_no=0 where dept_no=3 and age >=40;
同时更新多个字段
将部门号为0的改为1
update emp set dept=1,hiredate=now() where dept_no=0;

• 4: 查询 select：

全表查询

select * from tb_name;

where 条件查询

select * from emp where name='Li';
select * from emp where name='Li' and dept_no=1;
select * from emp where age >100;

去重查询 distinct

select distinct(dept_no) from emp;

排序
order by 字段，默认升序

select * from emp order by age;

降序用关键字DESC

select * from emp order by age desc ;

限制返回行数 limit N,M
查询年龄最高的三个人

select * from emp order by age desc limit 3;

想取年龄排序中，由大到小，第四到第五，

select * from emp order by age desc limit 3,2;

limit N,M ,N代表跳过多少行，M代表跳过后取多少行

这种场景常用在翻页上

聚合:
统计员工总数
count()函数
select count() from emp;
也可以带where条件
select count() from emp where dept_no=2;
sum()求和
select sum(age) from emp;

最大值，最小值，max(),min()

select max(age) from emp;

• 表连接

表连接也叫复杂查询
emp

dept：

create table dept 
(
dept_no int,
dept_name varchar(20)
)
insert into dept values(1,'HR'),(2,'Sales'),(3,'IT')

mysql> select * from dept;
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
|       1 | HR        |
|       2 | Sales     |
|       3 | IT        |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----------+------+------------+----------+---------+--------+
| name     | age  | hiredate   | phone_no | dept_no | salary |
+----------+------+------------+----------+---------+--------+
| zhubajie |   40 | 2020-08-16 | 110      |       1 |   1000 |
| shaseng  |   38 | 2010-03-15 | 120      |       3 |   7000 |
| Li       |   23 | 2001-01-01 | 119      |       2 |   4000 |
| Zhang    |   30 | 2019-03-15 | 110      |       2 |   3000 |
| He       |   21 | 2009-01-01 | 110      |       4 |   6000 |
| wang     |   27 | 2010-03-15 | 120      |       4 |      0 |
| Li       |   23 | 2001-01-01 | 119      |       1 |   4000 |
| Zhang    |   30 | 2019-03-15 | 110      |       2 |   3000 |
+----------+------+------------+----------+---------+--------+

查询每个员工都是属于哪个部门？

select name,dept_name from emp left join dept on 
dept.dept_no=emp.dept_no;

select name,dept_name from emp left join dept on 
dept.dept_no=emp.dept_no where name='zhubajie';

• 左连接

以左表为基表，右表的数据按行搜索一一去匹配，匹配到则输出值
匹配不到侧输出NULL

+----------+------+------------+----------+---------+--------+                   +---------+-----------+
| name     | age  | hiredate   | phone_no | dept_no | salary |                   | dept_no | dept_name |
+----------+------+------------+----------+---------+--------+                   +---------+-----------+
| zhubajie |   40 | 2020-08-16 | 110      |       1 |   1000 |                   |       1 | HR        |
| shaseng  |   38 | 2010-03-15 | 120      |       3 |   7000 |                   |       2 | Sales     |
| Li       |   23 | 2001-01-01 | 119      |       2 |   4000 |                   |       3 | IT        |
| Zhang    |   30 | 2019-03-15 | 110      |       2 |   3000 |                   +---------+-----------+
| He       |   21 | 2009-01-01 | 110      |       4 |   6000 |
| wang     |   27 | 2010-03-15 | 120      |       4 |      0 |
| Li       |   23 | 2001-01-01 | 119      |       1 |   4000 |
| Zhang    |   30 | 2019-03-15 | 110      |       2 |   3000 |
+----------+------+------------+----------+---------+--------+

select name,dept_name from emp left join dept on 
dept.dept_no=emp.dept_no where name='zhubajie';

zhubajie —> dept_no=1 ————> 去dept 搜索，有匹配到的话就输出 —HR
shaseng —> dept_no=3 ----> 去dept 搜索，-----IT
He —> dept_no=4 ----> 去dept 搜索，未匹配到---- NULL

练习：
每个同学都选了哪门课程？ 哪门课程没人选？

学生表 课程表
name 选修课程号 课程号，课程名
Xiaoli 1 1 math
xiaowang 2 2 English
xiaozhang 3 3 draw
xiaohe 4 4 IT
xiaolin 2 5 history

创建student表

create table student (name varchar(20),course_no int );
insert into student values('xiaoli',1),('xiaozhang',2),('xiaozhang',3),('xiaohe',4),('xiaolin',2);

创建course_table表

create table course_table (course_no int, course_name varchar(20));
insert into course_table values(1,'math'),(2,'English'),(3,'draw'),(4,'IT'),(5,'history');

查询每个同学都选了哪门课程

select name,course_name from student left join course_table on course_table.course_no=student.course_no;

查询哪门课程没人选

select name,course_name from student right join course_table on student.course_no =course_table.course_no;