爱站程序员基地权限
- 一.权限
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 empADD column phone_no int;
同时增加多个字段：
alter table empADD COLUMN dept_no int,ADD COLUMN age int;
删除字段：
ALTER table empdrop column salary;
修改字段/修改字段的位置
alter table empmodify age int FIRST;
ALTER table empmodify age int after ename;
修改字段的类型
在数据库中每一个字段只能有唯一的数据类型
数据类型就代表我这一列存储的数据属性，是数值的，还是字符的，还是时间
phone_no int 数值，char字符来存
alter table empmodify phone_no char(11);
修改字段名
alter table empchange 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_studentchange name ename char(20);
DML数据库操纵语句：
数据操纵语句
insert/delete/update/select
增删改查
- 1：insert
插入一条数据
insert into empvalues(\'sunwukong\',999,\'2008-07-01\',\'110\',1);
注意点，默认一个括号内是一行数据，括号内的数值或字符需要与表的列数匹配
如何字段数量不匹配，插入就会报错
查看表数据
select * from emp;
插入多条数据
insert into empvalues(\'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 empvalues(\'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 ondept.dept_no=emp.dept_no;
select name,dept_name from emp left join dept ondept.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 ondept.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;
