MySQL常用命令

show databases;

create database test_db;

use test_db;

create table tb_emp1
(
    id     int(11),
    name   varchar(25),
    depId  int(11),
    salary float
);

show create table tb_emp1;

show tables;

create table tb_emp2
(
    id     int(11) primary key,
    name   varchar(25),
    depId  int(11),
    salary float
);

show create table tb_emp2;

desc tb_emp2;

create table tb_emp3
(
    id     int(11),
    name   varchar(25),
    depId  int(11),
    salary float,
    primary key (id)
);

show create table tb_emp3;

desc tb_emp3;

# 多字段联合主键
create table tb_emp4
(
    name   varchar(25),
    depId  int(11),
    salary float,
    primary key (name, depId)
);

show create table tb_emp4;

desc tb_emp4;

use aaa;
drop table job;

# 使用外键约束
# 创建主表
create table tb_dept1
(
    id       int(11) primary key,
    name     varchar(25),
    location varchar(50)
);
# 创建从表,添加名称为fk_emp_dept1的外键约束,外键字段名称为depId,依赖于tb_dept1表的id字段
# 创建外键关联关系时,子表的外键的类型必须与主表的主键的类型一致。
create table tb_emp5
(
    id     int(11) primary key,
    name   varchar(25),
    depId  int(11),
    salary float,
    constraint fk_emp_dept1 foreign key (depId) references tb_dept1 (id)
);

desc tb_emp5;

# 创建表时使用非空约束 not null
create table tb_emp6
(
    id     int(11) primary key,
    name   varchar(25) not null,
    depId  int(11),
    salary float
);

desc tb_emp6;

# 创建表时使用唯一性约束unique:要求字段的值唯一,允许为空,但只能出现一个空值。
create table tb_dept2
(
    id       int(11) primary key,
    name     varchar(25) unique,
    location varchar(50)
);

desc tb_dept2;
drop table tb_dept3;

create table tb_dept3
(
    id       int(11) primary key,
    name     varchar(25),
    location varchar(50),
    constraint STH unique (name)
);

desc tb_dept3;

# 创建表时使用默认约束:default
create table tb_emp7
(
    id     int(11) primary key,
    name   varchar(25) not null,
    depId  int(11) default 1111,
    salary float
);

# 设置表的属性值自动增加auto_increment
create table tb_emp8
(
    id     int(11) primary key auto_increment,
    name   varchar(25) not null,
    depId  int(11),
    salary float
);

# 插入数据时没有填写id字段,系统自动生成id字段的值,每次递增1
insert into tb_emp8 (name, salary)
values ('小花', 1000),
       ('小张', 2000),
       ('小罗', 3000);

select *
from tb_emp8;

# 查看数据表结构
describe tb_emp8;
desc tb_emp8;
show create table tb_emp8;

# 修改表名
show tables;
alter table tb_dept3 rename tb_department3;

# 修改字段的数据类型
desc tb_dept1;

alter table tb_dept1
    modify name varchar(30);

desc tb_dept1;

# 修改字段名字
alter table tb_dept1
    change location loc varchar(60);

# 添加字段
# 添加无完整性约束条件的字段
alter table tb_dept1
    add managerId int(10);

# 添加有完整性约束条件的字段
alter table tb_dept1
    add dep_desc varchar(50) not null;

# 在表的第一列添加一个字段
alter table tb_dept1
    add first_col int(11) first;

# 在表的指定列之后添加一个字段
alter table tb_dept1
    add via_col int(11) after loc;

# 删除字段
alter table tb_dept1
    drop first_col;

alter table tb_dept1
    drop via_col;

# 修改字段的排列位置
# 修改字段为表的第一个位置
alter table tb_dept1
    modify managerId varchar(60) first;

# 修改字段的位置为某个字段的后面
alter table tb_dept1
    modify managerId varchar(50) after dep_desc;

# 查看系统支持的存储引擎
show engines;
# 更改表的存储引擎
show create table tb_department3;

alter table tb_department3
    engine =MyISAM;

# 删除表的外键约束
create table tb_emp9
(
    id     int(11) primary key not null auto_increment,
    name   varchar(25)         not null,
    deptId int(11),
    salary float,
    constraint fk_emp9_dept1 foreign key (deptId) references tb_dept1 (id)
);

desc tb_emp9;

show create table tb_emp9;

# 删除外键约束
alter table tb_emp9
    drop foreign key fk_emp9_dept1;

# 删除没有被关联的数据表
drop table if exists tb_dept2;

# 删除被其他表关联的的主表
# 创建主表
create table tb_dept2
(
    id       int(11) primary key,
    name     varchar(25),
    location varchar(50)
);

# 创建从表
create table tb_emp
(
    id     int(11) primary key,
    name   varchar(25),
    deptId int(11),
    salary float,
    constraint fk_emp_dept2 foreign key (deptId) references tb_dept2 (id)
);

# 直接删除主表会报错
drop table tb_dept2;

# 解除tb_emp表的外键约束
alter table tb_emp
    drop foreign key fk_emp_dept2;
# 在删除主表就可以删除了
drop table tb_dept2;

# MySQL8.0的新特性1 ===> 默认字符集改为utf8mb4

# 综合案例 ===> 数据表的基本操作
create database company;
use company;
create table offices
(
    officeCode int(11) primary key not null unique,
    city       varchar(50)         not null,
    address    varchar(50),
    country    varchar(50)         not null,
    postalCode varchar(15) unique
);

create table employees
(
    employeeNumber int(11) primary key not null unique auto_increment,
    lastName       varchar(50)         not null,
    firstName      varchar(50)         not null,
    mobile         varchar(25) unique,
    officeCode     int(11)             not null,
    jobTitle       varchar(50)         not null,
    birth          datetime            not null,
    note           varchar(255),
    sex            varchar(5),
    constraint fk_emp_off foreign key (officeCode) references offices (officeCode)
);

show tables;
desc employees;
desc offices;

alter table employees
    modify mobile varchar(25) after officeCode;

alter table employees
    change birth employee_birth datetime;

alter table employees
    modify sex varchar(1) not null;

alter table employees
    drop note;

alter table employees
    add favorite_activity varchar(100);

# 在未删除外键关联关系前,删除主表会报错
drop table offices;
# 删除外键约束
alter table employees
    drop foreign key fk_emp_off;

show create table employees;

drop table offices;

alter table employees
    engine =MyISAM;

alter table employees rename employees_info;

show create table employees_info;

# 对MySQL的表进行删除前,最好先对表进行备份后在删除。
# MySQL中:修改字段名用change,修改字段的数据类型用modify

# 经典习题
create database Market;
use Market;
create table customers
(
    c_num     int(11) primary key unique not null auto_increment,
    c_name    varchar(50),
    c_contact varchar(50),
    c_city    varchar(50),
    c_birth   datetime                   not null
);

desc customers;
desc customers_info;

alter table customers
    modify c_contact varchar(50) after c_birth;

alter table customers
    modify c_name varchar(70);

alter table customers
    change c_contact c_phone varchar(50);

alter table customers
    add c_gender char(1);

alter table customers rename customers_info;

alter table customers_info
    drop c_city;

alter table customers_info
    engine =MyISAM;

show create table customers_info;

alter table customers_info
    engine =InnoDB;

create table orders
(
    o_num  int(11) primary key not null unique auto_increment,
    o_data date,
    c_id   int(11),
    constraint cus_fk foreign key (c_id) references customers_info (c_num)
);

show create table orders;

alter table orders
    drop foreign key cus_fk;

drop table customers_info;

create table tmp1
(
    x tinyint,
    y smallint,
    z mediumint,
    m int,
    n bigint
);

desc tmp1;

create table tmp2
(
    x float(5, 1),
    y double(5, 1),
    z decimal(5, 1)
);

desc tmp2;

insert into tmp2
values (5.12, 5.13, 5.123);

show warnings;

create table tmp3
(
    y year
);

insert into tmp3
values (2010),
       ('2021');

select *
from tmp3;

# 会报错:Data truncation: Out of range value for column 'y' at row 1
insert into tmp3
values ('2166');

show warnings;

delete
from tmp3;

insert into tmp3
values ('0'),
       ('00'),
       ('88'),
       ('21');

insert into tmp3
values (0),
       (78),
       (11);

# time数据类型

create table tmp4
(
    t time
);

delete
from tmp4;

insert into tmp4
values ('10:05:05'),
       ('23:23'),
       ('2 10:10'),
       ('3 02'),
       ('10');

select *
from tmp4;

insert into tmp4
values ('101112'),
       (111213),
       ('0');

# 向tmp4表中插入系统当前时间
insert into tmp4
values (current_time),
       (now());

# date数据类型
create table tmp5
(
    d date
);

insert into tmp5
values ('2021-01-29'),
       ('20210301');

insert into tmp5
values ('12-09-21'),
       ('220101');

insert into tmp5
values (19990909),
       (990909),
       (000101),
       (111111);

insert into tmp5
values (current_date),
       (NOW());

# datetime数据类型
create table tmp6
(
    dt datetime
);

insert into tmp6
values ('1998-08-08 08:08:08'),
       ('19980808080808'),
       ('20210304143430');

insert into tmp6
values ('99-09-09 09:09:09'),
       ('090909090909'),
       ('111111111111');

insert into tmp6
values (19980808080808);

insert into tmp6
values (210304161616);

insert into tmp6
values (now());

# timestamp数据类型
create table tmp7
(
    ts timestamp
);

insert into tmp7
values ('19950101010101'),
       ('950101010101'),
       ('1996-02-01 01:01:01'),
       ('99@11@11 11@11@11'),
       ('121212121212'),
       (now());

# 文本字符串类型
# char和varchar

create table tmp8
(
    ch  char(4),
    vch varchar(4)
);

insert into tmp8
values ('ab  ', 'ab  ');

# 报错:插入的数据长度超过了字段设置的最大长度4.
insert into tmp8
values ('qwert', 'yuiop');

# ENUM枚举数据类型
create table tmp9
(
    enm enum ('first', 'second', 'third')
);

insert into tmp9
values ('first'),
       ('second'),
       ('third'),
       (null);

select enm, enm + 0
from tmp9;

create table tmp10
(
    soc   int,
    level enum ('excellent', 'good', 'bad')
);

insert into tmp10
values (70, 'good'),
       (90, 1),
       (75, 2),
       (50, 3);

# set数据类型
create table tmp11
(
    s set ('a', 'b', 'c', 'd')
);

insert into tmp11
values ('a'),
       ('a,b,a'),
       ('c,a,d');

# 报错:Data truncated for column 's' at row 1
insert into tmp11
values ('a,s');

# 二进制字符串类型
# bit类型
create table tmp12
(
    b bit(4)
);

insert into tmp12
values (2),
       (9),
       (15);

# binary类型和varbinary类型
create table tmp13
(
    b  binary(3),
    vb varbinary(3)
);

insert into tmp13
values (5, 5);

select length(b), length(vb)
from tmp13;

# blob类型

# 算术运算符
create table tmp14
(
    num int
);

insert into tmp14
values (64);

select num, num + 10, num - 3 + 5, num + 5 - 3, num + 36.5
from tmp14;

select num, num * 2, num / 2, num % 3, num / 3
from tmp14;

select num, num / 0, num % 0
from tmp14;

# 比较运算符
select 1 = 0, '2' = 2, 2 = 2, '0.02' = 0, 'b' = 'b', (1 + 3) = (2 + 2), null = null;

# 安全等于运算符
select 1 <=> 0,
       '2' <=> 2,
       2 <=> 2,
       '0.02' <=> 0,
       'b' <=> 'b',
       (1 + 3) <=> (2 + 2),
       null <=> null,
       2 <=> null;

# 不等于运算符:<> 或 !=
select 'good' <> 'god', 1 <> 2, 4 != 4, 5.5 != 5, (1 + 3) != (2 + 1), null <> null;

# 小于或等于运算符
select 'good' <= 'god', 1 <= 2, 4 <= 4, 5.5 <= 5, (1 + 3) <= (2 + 1), null <= null;

# 小于运算符
select 'good' < 'god', 1 < 2, 4 < 4, 5.5 < 5, (1 + 3) < (2 + 1), null < null;

# 大于或等于运算符
select 'good' >= 'god', 1 >= 2, 4 >= 4, 5.5 >= 5, (1 + 3) >= (2 + 1), null >= null;

# 大于运算符
select 'good' > 'god', 1 > 2, 4 > 4, 5.5 > 5, (1 + 3) > (2 + 1), null > null;

# is null(isnull)、is not null运算符
select null is null, isnull(null), isnull(10), 10 is null;

# between and运算符
select 13 between 10 and 15,
       13 between 13 and 15,
       13 between 10 and 13,
       'b' between 'a' and 'f',
       'w' between 'x' and 'z';

# least运算符
select least(2, 0), least(20.0, 3.0, 100.3), least('a', 'c', 'b'), least(10, null);

# greatest(value1, value2)
select greatest(2, 0), greatest(20.0, 3.0, 100.3), greatest('a', 'c', 'b'), greatest(10, null);

# in、not in运算符
select 2 in (1, 3, 5, 'this'), 'this' in (1, 3, 5, 'this');
select 2 not in (1, 3, 5, 'this'), 'this' not in (1, 3, 5, 'this');
select null in (1, 2, 'this'), 10 in (1, 2, null, 'this');

# like运算符
# `%`匹配任何数目的字符,包括0字符;`_`只能匹配一个字符
select 'stud' like 'stud',
       'stud' like 'stu_',
       'stud' like '%d',
       'stud' like 's___',
       'stud' like 't___',
       'stud' like null;

# regexp运算符
select 'ssky' regexp '^s', 'ssky' regexp 'y$', 'ssky' regexp '.sky', 'ssky' regexp '[ab]';

# 逻辑运算符
# not或!
select not 10, not (1 - 1), not (-5), not null, not 1 + 1;
select !10, !(1 - 1), ! -5, ! null, ! 1 + 1;
select ! 1 + 1;
# `not`的优先级低于`+`,`!`的优先级高于`+`

# and或&&
select 1 and -1, 1 and 0, 1 and null, 0 and null;
select 1 && -1, 1 && 0, 1 && null, 0 && null;

# or或||
select 1 or -1, 1 or 2, 1 or null, 0 or null, null or null;
select 1 || -1, 1 || 2, 1 || null, 0 || null, null || null;

# xor
select 1 xor 1, 0 xor 0, 1 xor 0, 1 xor null, 1 xor 1 xor 1;

# 插入、更新、与删除数据
# 插入数据
# 新建数据表
create table person
(
    id   int unsigned not null auto_increment,
    name char(40)     not null default '',
    age  int          not null default 0,
    info char(50)     null,
    primary key (id)
);
desc person;
# 为表的所有字段插入数据
insert into person(id, name, age, info)
values (1, 'Green', 21, 'Lawyer');


insert into person(name, age, id, info)
values ('Suse', 21, 2, 'Lawyer');

insert into person
values (3, 'Mary', 24, 'musician');

# 为表的指定字段插入数据
insert into person (name, age, info)
values ('Willam', 20, 'sports man');

insert into person(name, age)
values ('Laura', 25);

# 同时插入多条记录
insert into person (name, age, info)
values ('Evans', 27, 'secretary'),
       ('Dale', 22, 'cook'),
       ('Edison', 28, 'singer');

# 不指定插入的字段列表,直接插入多条数据
insert into person
values (9, 'Harry', 21, 'magician'),
       (null, 'Harriet', 19, 'pianist');

# 将查询结果插入表中
insert into person (name, age, info)
select name, age, info
from person
where age = 21;

# 更新数据
update person
set age=15,
    name='LiMing'
where id = 11;

update person
set info='student'
where age between 19 and 22;

# 删除数据
# 删除id为11的记录
delete
from person
where id = 11;

# 一次性删除多条记录
delete
from person
where age between 19 and 22;

# delete删除表中所有的数据,如果再次新增数据,则主键从上一次删除的最后一条数据的主键开始累加
delete
from person;

insert into person (name, age, info)
values ('asfas', 34, 'sasdf');

# truncate删除表所有数据,truncate是直接删除原来的表并重新新建一个表,并不是删除数据记录,一次执行速度比delete快,且再次向表内插入数据时,
# 主键直接从头开始(比如直接从1开始)
truncate table person;

# 为表增加计算列
create table tb1
(
    id int not null auto_increment,
    a  int default null,
    b  int default null,
    c  int generated always as ((a + b)) virtual,
    primary key (id)
);

insert into tb1 (a, b)
values (100, 200);

update tb1
set a=1000;

select *
from tb1;

# 综合案例 ===> 记录的插入、更新和删除
create table books
(
    id      int          not null unique primary key comment '书编号',
    name    varchar(50)  not null comment '书名',
    authors varchar(100) not null comment '作者',
    price   float        not null comment '价格',
    pubdate year         not null comment '出版年份',
    note    varchar(25) comment '说明',
    num     int          not null comment '库存'
);

desc books;

insert into books
values (1, 'Tale of AAA1', 'Dickes1', 23, '1991', 'novel1', 11),
       (2, 'Tale of AAA2', 'Dickes2', 35, '1992', 'novel2', 21),
       (3, 'Tale of AAA3', 'Dickes3', 40, '1993', 'novel1', 31),
       (4, 'Tale of AAA4', 'Dickes4', 20, '1994', 'novel4', 41),
       (5, 'Tale of AAA5', 'Dickes5', 30, '1995', 'novel1', 51),
       (6, 'Tale of AAA6', 'Dickes6', 33, '1996', 'novel6', 61),
       (7, 'Tale of AAA7', 'Dickes7', 28, '1997', 'novel1', 71);

update books
set price=price + 5
where note = 'novel1';

select *
from books;

update books
set price=40,
    note='drama'
where note = 'novel4';

delete
from books
where num = 0;

create table pet
(
    name    varchar(20) not null comment '宠物名字',
    owner   varchar(20) comment '宠物主人',
    species varchar(20) not null comment '种类',
    sex     char(1)     not null comment '性别',
    birth   year        not null comment '出生日期',
    death   year comment '死亡日期'
);

desc pet;

# 第一种插入数据的方法
insert into pet (name, owner, species, sex, birth, death)
values ('Fluffy', 'Harold', 'cat', 'f', '2003', '2010');
# 第二种插入数据的方法
insert into pet
values ('Claws', 'Gwen', 'cat', 'm', '2004', null);
# 第三种插入数据的方法:批量插入
insert into pet
values ('Buffy', null, 'dog', 'f', '2009', null),
       ('Fang', 'Benny', 'dog', 'm', '2000', null),
       ('Bowser', 'Diane', 'dog', 'm', '2003', 2009),
       ('Chirpy', null, 'bird', 'f', '2008', null);

update pet
set owner='Kevin'
where name = 'Fang';

update pet
set owner='Duck'
where owner is null;

delete
from pet
where death is not null;

delete
from pet;

# 索引
# 创建表的时候创建索引
# 创建普通索引
create table books1
(
    bookId           int          not null,
    bookName         varchar(255) not null,
    authors          varchar(255) not null,
    info             varchar(255),
    comment          varchar(255),
    year_publication year         not null,
    index (year_publication)
);

show create table books1;

# 查看索引是否正在使用
explain
select *
from books1
where year_publication = 1990;

# 创建唯一索引并自己指定索引名称为:UniqIdx
create table t1
(
    id   int      not null,
    name char(30) not null,
    unique index UniqIdx (id)
);

show create table t1;

# 创建单列索引
create table t2
(
    id   int not null,
    name varchar(25),
    index SingleIndex (name(25))
);

show create table t2;

desc t2;

# 创建组合索引:组合索引是在多个字段上创建一个索引
# 组合索引遵循`最左前缀`
create table t3
(
    id   int         not null,
    name varchar(25) not null,
    age  int         not null,
    info varchar(255),
    index MultiIdx (id, name, age)
);

show create table t3;

desc t3;

explain
select *
from t3
where id = 1
  and name = 'joe';

# 在已经存在的表上创建索引
# 使用alter table语句创建索引
# 使用show index查看指定表中创建的索引
show index from books1;

alter table books1
    add index BkNameIdx (bookName);

alter table books1
    add unique index UniqIdx (bookId);

alter table books1
    add index BkcmtIdx (comment(50));

# 创建组合索引
alter table books1
    add index BkAuAndInfoIdx (authors(30), info(50));

# 使用create index创建索引:create index被映射到一个alter table语句上。
create table books2
(
    bookId           int         not null,
    bookName         varchar(25) not null,
    authors          varchar(25) not null,
    info             varchar(255),
    comment          varchar(255),
    year_publication year        not null
);

create index BkNameIdx on books2 (bookName);

show index from books2;
# 创建唯一索引
create unique index UniqIdx on books2 (bookId);
# 创建单列索引
create index BkcmtIdx on books2 (comment(50));
# 创建组合索引
create index BkAuAndInfoIdx on books2 (authors(20), info(50));

# 删除索引
# alter table删除索引 ***添加auto_increment约束字段的唯一索引不能被删除***
alter table books2
    drop index UniqIdx;

# drop index删除索引
drop index BkAuAndInfoIdx on books2;

# 删除表中的所有列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。

# 综合案例 ===> 创建索引
create database index_test;
use index_test;

create table test_table1
(
    id          int         not null unique auto_increment,
    name        varchar(25) not null,
    address     varchar(25),
    description varchar(100)
);

create table test_table2
(
    id         int primary key not null unique,
    firstname  varchar(25)     not null,
    middlename varchar(25)     not null,
    lastname   varchar(25)     not null,
    birth      date            not null,
    title      varchar(25)
);

create table writers
(
    w_id      smallint primary key not null unique auto_increment,
    w_name    varchar(25)          not null,
    w_address varchar(25),
    w_age     char(2)              not null,
    w_note    varchar(25),
    unique index UniqIdx (w_id)
);

show index from writers;

alter table writers
    add index nameIdx (w_name);

create index MultiIdx on writers (w_address, w_age);

alter table writers
    drop index nameIdx;

# 视图

create table student
(
    s_id int,
    name varchar(25)
);

create table stu_info
(
    s_id  int,
    glass varchar(40),
    addr  varchar(90)
);

# 在单表上创建视图
create table t
(
    quantity int,
    price    int
);
insert into t
values (3, 50);

create view view_t as
select quantity, price, quantity * price
from t;

select *
from view_t;

# 创建视图并自己指定视图的字段
create view view_t2(qty, price, total) as
select quantity, price, quantity * price
from t;

select *
from view_t2;

# 在多表上创建视图
insert into student
values (1, '张三'),
       (2, '李四'),
       (3, '王五');

insert into stu_info
values (1, '1班', '北京'),
       (2, '2班', '湖北'),
       (3, '3班', '湖南');

create view stu_glass (id, name, glass) as
select student.s_id, student.name, stu_info.glass
from student,
     stu_info
where student.s_id = stu_info.s_id;

select *
from stu_glass;

# 查看视图
describe stu_glass;
desc stu_glass;

# 查看视图状态
show table status like 'stu_glass';
# 查看表状态
show table status like 'student';

show create view stu_glass;

# 在views表中查看视图的详细信息
select *
from information_schema.VIEWS;

# 修改视图 当基本表的某些字段发生变化时,通过修改视图来保持与基本表的一致性。
desc view_t;

create or replace view view_t as
select *
from t;

desc view_t;

# 使用alter语句修改视图
    alter view view_t as select quantity
                         from t;

desc view_t;

# 更新视图 对视图的增加和删除记录,实际上是对基本表的增加或删除记录。视图更新的3中方法:insert update delete
select *
from view_t;

select *
from t;

update view_t
set quantity=5;

select *
from view_t2;

insert into t
values (3, 5);


delete
from view_t2
where price = 5;

# 删除视图
drop view if exists stu_glass;

# 报错:Table 'index_test.stu_glass' doesn't exist
show create view stu_glass;

# 综合案例 ===> 视图应用
create table stu
(
    s_id   int primary key not null unique,
    s_name varchar(20)     not null,
    addr   varchar(50)     not null,
    tel    varchar(50)     not null
);

create table sign
(
    s_id       int primary key not null unique,
    s_name     varchar(25)     not null,
    s_sch      varchar(50)     not null,
    s_sign_sch varchar(50)     not null
);

create table stu_mark
(
    s_id   int primary key not null unique,
    s_name varchar(20)     not null,
    mark   int             not null
);

insert into stu
values (1, 'xiaowang', '北京', '1008131'),
       (2, 'xiaozhang', '河北', '1044564'),
       (3, 'xiaoling', '南京', '104545');

insert into sign
values (1, 'xiaowang', 'middle school1', 'Peking University'),
       (2, 'xiaozhang', 'middle school2', 'Tsinghua University'),
       (3, 'xiaoling', 'middle school3', 'Tsinghua University');

insert into stu_mark
values (1, 'xiaowang', 80),
       (2, 'xiaozhang', 71),
       (3, 'xiaoling', 70);

# 创建视图
create view beida (id, name, mark, sch) as
select stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_sch
from stu_mark,
     sign
where stu_mark.s_id = sign.s_id
  and stu_mark.mark >= 41
  and sign.s_sign_sch = 'Pecking University';

# 修改视图
create or replace view beida (id, name, mark, sch) as
select stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_sch
from stu_mark,
     sign
where stu_mark.s_id = sign.s_id
  and stu_mark.mark >= 41
  and sign.s_sign_sch = 'Peking University';

select *
from beida;

create view qinghua (id, name, mark, sch) as
select stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_sch
from stu_mark,
     sign
where stu_mark.s_id = sign.s_id
  and stu_mark.mark >= 40
  and sign.s_sign_sch = 'Tsinghua University';

select *
from qinghua;

# 更新基本表后,视图的数据也同步进行了更新
update stu_mark
set mark=mark - 50
where stu_mark.s_name = 'xiaoling';

select *
from stu_mark;

# 删除视图
drop view qinghua;
drop view beida;

show create view view_t2;

# 查询数据
# 基本查询语句
create table fruits
(
    f_id    char(10)      not null,
    s_id    int           not null,
    f_name  char(255)     not null,
    f_price decimal(8, 2) not null,
    primary key (f_id)
);

INSERT INTO fruits (f_id, s_id, f_name, f_price)
VALUES ('a1', 101, 'apple', 5.2),
       ('b1', 101, 'blackberry', 10.2),
       ('bs1', 102, 'orange', 11.2),
       ('bs2', 105, 'melon', 8.2),
       ('t1', 102, 'banana', 10.3),
       ('t2', 102, 'grape', 5.3),
       ('o2', 103, 'coconut', 9.2),
       ('c0', 101, 'cherry', 3.2),
       ('a2', 103, 'apricot', 2.2),
       ('l2', 104, 'lemon', 6.4),
       ('b2', 104, 'berry', 7.6),
       ('m1', 106, 'mango', 15.6),
       ('m2', 105, 'xbabay', 2.6),
       ('t4', 107, 'xbababa', 3.6),
       ('m3', 105, 'xxtt', 11.6),
       ('b5', 107, 'xxxx', 3.6);

select f_id, f_name
from fruits;

# 单表查询

# 查询所有字段
# 使用*通配符
select *
from fruits;
# 指定所有字段进行查询
select f_id, s_id, f_name, f_price
from fruits;

# 查询指定字段
# 查询单个字段
select f_name
from fruits;

# 查询多个字段
select f_name, f_price
from fruits;

# 查询指定记录
select f_name
from fruits
where f_price = 10.2;

select f_price
from fruits
where f_name = 'apple';

select f_name, f_price
from fruits
where f_price < 10;

# 带in关键字查询
select *
from fruits
where s_id in (101, 102);

select *
from fruits
where s_id not in (101, 102);

# 带between and的范围查询
select *
from fruits
where f_price between 2.00 and 10.20;

select f_name, f_price
from fruits
where f_price not between 2.00 and 10.20;

# 带like的字符匹配查询
# 百分号通配符`%`,匹配任意长度的字符串,甚至包括零字符
select *
from fruits
where f_name like 'b%';

select *
from fruits
where f_name like '%g%';

select f_name
from fruits
where f_name like 'b%y';

# 下划线通配符,一次只能匹配任意一个字符
select *
from fruits
where f_name like '____y';

# 查询空值
CREATE TABLE customers
(
    c_id      int       NOT NULL AUTO_INCREMENT,
    c_name    char(50)  NOT NULL,
    c_address char(50)  NULL,
    c_city    char(50)  NULL,
    c_zip     char(10)  NULL,
    c_contact char(50)  NULL,
    c_email   char(255) NULL,
    PRIMARY KEY (c_id)
);

INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email)
VALUES (10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', 'LMing@163.com'),
       (10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo', 'Jerry@hotmail.com'),
       (10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL),
       (10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com');

select c_id, c_name, c_email
from customers
where c_email is null;

select c_id, c_name, c_email
from customers
where c_email is not null;

# 带and的多条件查询
select f_price, f_name
from fruits
where s_id = 101
  and f_price >= 5;

select f_price, f_name, s_id
from fruits
where s_id in (101, 102)
  and f_price > 5
  and f_name = 'apple';

# 带or的多条件查询
select f_name, f_price, s_id
from fruits
where s_id = 101
   or s_id = 102;

select s_id, f_name, f_price
from fruits
where s_id in (101, 102);

# 查询结果去重
select distinct s_id
from fruits;

# 对查询结果进行排序
# 单列排序
select f_name
from fruits
order by f_name;

# 多列排序 在进行多列排序时,首先第一列必须有相同的列值,才会对第二列进行排序,如果第一列数据中的所有值都是唯一的,将不再对第二列进行排序。
select f_name, f_price
from fruits
order by f_name, f_price;

# 指定排序方向
# 降序排列
select f_name, f_price
from fruits
order by f_price desc;

select f_price, f_name
from fruits
order by f_price desc, f_name;

# 分组查询
# 创建分组
select s_id, count(*) as total
from fruits
group by s_id;

select s_id, group_concat(f_name) as names
from fruits
group by s_id;

# 使用having过滤分组数据
select s_id, group_concat(f_name) as names
from fruits
group by s_id
having count(f_name) > 1;

# 在group by字句中使用with rollup:统计记录数量
select s_id, count(*) as total
from fruits
group by s_id
with rollup;

# 多字段分组
select s_id, f_name
from fruits
group by s_id, f_name;

# group by和order by一起使用
CREATE TABLE orderitems
(
    o_num      int           NOT NULL,
    o_item     int           NOT NULL,
    f_id       char(10)      NOT NULL,
    quantity   int           NOT NULL,
    item_price decimal(8, 2) NOT NULL,
    PRIMARY KEY (o_num, o_item)
);

INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES (30001, 1, 'a1', 10, 5.2),
       (30001, 2, 'b2', 3, 7.6),
       (30001, 3, 'bs1', 5, 11.2),
       (30001, 4, 'bs2', 15, 9.2),
       (30002, 1, 'b3', 2, 20.0),
       (30003, 1, 'c0', 100, 10),
       (30004, 1, 'o2', 50, 2.50),
       (30005, 1, 'c0', 5, 10),
       (30005, 2, 'b1', 10, 8.99),
       (30005, 3, 'a2', 10, 2.2),
       (30005, 4, 'm1', 5, 14.99);

select o_num, sum(quantity * item_price) as orderTotal
from orderitems
group by o_num
having sum(quantity * item_price) >= 100
order by orderTotal;

# 使用limit限制查询结果的数量
select *
from fruits
limit 4;

# 使用limit返回从第5个记录开始长度为3的记录
select *
from fruits
limit 4,3;

# 使用集合函数查询
# count函数
select count(*) as total_num
from customers;

select count(c_email)
from customers;

select o_num, count(f_id)
from orderitems
group by o_num;

# sum函数
select o_num, sum(quantity)
from orderitems
where o_num = 30005;

select o_num, sum(quantity)
from orderitems
group by o_num;

# avg函数
select s_id, avg(f_price)
from fruits
where s_id = 103;

select s_id, avg(f_price)
from fruits
group by s_id;

# max函数
select max(f_price)
from fruits;

select s_id, max(f_price) as max_price
from fruits
group by s_id;

select max(f_name)
from fruits;

# min函数
select min(f_price)
from fruits;

select s_id, min(f_price)
from fruits
group by s_id;

# 连接查询
# 内连接查询 inner join 在内连接查询中,只有满足条件的记录才能出现在结果关系中。
CREATE TABLE suppliers
(
    s_id   int      NOT NULL AUTO_INCREMENT,
    s_name char(50) NOT NULL,
    s_city char(50) NULL,
    s_zip  char(10) NULL,
    s_call CHAR(50) NOT NULL,
    PRIMARY KEY (s_id)
);

INSERT INTO suppliers(s_id, s_name, s_city, s_zip, s_call)
VALUES (101, 'FastFruit Inc.', 'Tianjin', '300000', '48075'),
       (102, 'LT Supplies', 'Chongqing', '400000', '44333'),
       (103, 'ACME', 'Shanghai', '200000', '90046'),
       (104, 'FNK Inc.', 'Zhongshan', '528437', '11111'),
       (105, 'Good Set', 'Taiyuang', '030000', '22222'),
       (106, 'Just Eat Ours', 'Beijing', '010', '45678'),
       (107, 'DK Inc.', 'Zhengzhou', '450000', '33332');

desc fruits;
desc suppliers;

select suppliers.s_id, suppliers.s_name, fruits.f_name, fruits.f_price
from fruits,
     suppliers
where fruits.s_id = suppliers.s_id;

# 使用inner join内连接进行查询
select suppliers.s_id, suppliers.s_name, fruits.f_name, fruits.f_price
from fruits
         inner join suppliers on fruits.s_id = suppliers.s_id;

select f_name
from fruits
where f_id = 'a1';

# 外连接查询 left join 和 right join
CREATE TABLE orders
(
    o_num  int      NOT NULL AUTO_INCREMENT,
    o_date datetime NOT NULL,
    c_id   int      NOT NULL,
    PRIMARY KEY (o_num)
);

INSERT INTO orders(o_num, o_date, c_id)
VALUES (30001, '2008-09-01', 10001),
       (30002, '2008-09-12', 10003),
       (30003, '2008-09-30', 10004),
       (30004, '2008-10-03', 10005),
       (30005, '2008-10-08', 10001);

SELECT customers.c_id, orders.o_num
FROM customers
         LEFT OUTER JOIN orders
                         ON customers.c_id = orders.c_id;

# 右连接
select c.c_id, o.o_num
from index_test.customers c
         right join index_test.orders o on o.c_id = c.c_id;

# 复合条件连接查询
select o.o_num, c.c_id
from customers c
         inner join orders o on c.c_id = o.c_id and c.c_id = 10001;

select s.s_id, s.s_name, f.f_name, f.f_price
from fruits f
         inner join suppliers s on f.s_id = s.s_id
order by f.s_id;

# 子查询
# 带any,some关键字的子查询
create table tb11
(
    num1 int not null
);

create table tb12
(
    num2 int not null
);

insert into tb11
values (1),
       (5),
       (13),
       (27);

insert into tb12
values (6),
       (14),
       (11),
       (20);

# 返回tb12表的所有num2列,然后将tb11中的num1的值与之比较,只要大于num2的任何一个值,即为符合条件的结果。
select num1
from tb11
where num1 > any (select num2 from tb12);
# 在子查询中,返回的是tb12表的所有num2列的结果(6,14,11,20),然后将tb11中的num1列的值与之比较,只要大于num2列的任意一个数即为符合条件的结果。

# 带all关键字的子查询
select num1
from tb11
where num1 > all (select num2 from tb12);
# 在子查询中,返回的事tb12表中所有num2列的结果(6,14,11,20),然后将tb11中的num1列的值与之进行比较,大于所有num2列的值只有27,因此返回结果为27.

# 带exists关键字的子查询
select *
from fruits
where exists(select s_id from suppliers where s_id = 107);

select *
from fruits
where f_price > 10.20
  and exists(select s_id from suppliers where s_id = 107);

# not exists关键字
select *
from fruits
where not exists(select s_id from suppliers where s_id = 107);

# 带in关键字的查询
select c_id
from index_test.orders
where o_num in (select o_num from orderitems where f_id = 'c0');

# not in关键字
select c_id
from index_test.orders
where o_num not in (select o_num from orderitems where f_id = 'c0');

# 带比较运算符的子查询
select s_id, f_name
from fruits
where s_id = (select s_id from suppliers where s_city = 'Tianjin');

select s_id, f_name
from fruits
where s_id != (select s_id from suppliers where s_city = 'Tianjin');

# 合并查询结果 利用union关键字可以给多条select语句的查询结果组合成单个查询结果集。合并时,两个表对应的列数和数据类型必须相同。
# union:执行的时候删除重复的记录,所有返回的行都是唯一的。 union all:不删除重复行,也不对结果进行自动排序。
select s_id, f_name, f_price
from fruits
where f_price < 9.0
union
select s_id, f_name, f_price
from fruits
where s_id in (101, 103);

# union all:不去掉重复行的数据
select s_id, f_name, f_price
from fruits
where f_price < 9.0
union all
select s_id, f_name, f_price
from fruits
where s_id in (101, 103);

# 为表和字段取别名
select *
from index_test.orders as o
where o.o_num = 30001;

select c.c_id, o.o_num
from customers as c
         left join orders o on c.c_id = o.c_id;

# 为字段取别名
select f1.f_name fruit_name, f1.f_price fruit_price
from fruits f1
where f1.f_price < 8;

select concat(trim(s_name), '(', trim(s_city), ')') as supp_title
from suppliers
order by s_name;

# 使用正则表达式查询
# 查询以特定字符或字符开头的记录
select *
from fruits
where f_name regexp '^b';

select *
from fruits
where f_name regexp '^be';

# 查询以特定字符或字符串结尾的记录
select *
from fruits
where f_name regexp 'y$';

select *
from fruits
where f_name regexp 'rry$';

# 用符号.来代替字符串中的任意一个字符
select *
from fruits
where f_name regexp 'a.g';

# 使用*和+匹配多个字符:*匹配前面的字符任意多次,包括0次。加号`+`匹配前面的字符至少一次。
select *
from fruits
where f_name regexp '^ba*';

select *
from fruits
where f_name regexp '^ba+';

# 匹配指定字符串
select *
from fruits
where f_name regexp 'on';

select *
from fruits
where f_name regexp 'on|ap';

select *
from fruits
where f_name like '%on%';

# 匹配指定字符中的任意一个
select *
from fruits
where f_name regexp '[ot]';

select *
from fruits
where s_id regexp '[456]';

# 匹配指定字符以外的字符:[^字符集合]
select *
from fruits
where f_id regexp '[^a-e1-2]';

# 使用{n,}或者{n,m}指定字符串连续出现的次数
select *
from fruits
where f_name regexp 'x{2,}';

select *
from fruits
where f_name regexp 'ba{1,3}';

# 综合案例 ===> 数据表查询操作
# 创建数据表employee和dept。
CREATE TABLE dept
(
    d_no       INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    d_name     VARCHAR(50),
    d_location VARCHAR(100)
);

# 由于employee表dept_no依赖于父表dept的主键d_no,因此需要先创建dept表,然后创建employee表。
CREATE TABLE employee
(
    e_no     INT          NOT NULL PRIMARY KEY,
    e_name   VARCHAR(100) NOT NULL,
    e_gender CHAR(2)      NOT NULL,
    dept_no  INT          NOT NULL,
    e_job    VARCHAR(100) NOT NULL,
    e_salary SMALLINT     NOT NULL,
    hireDate DATE,
    CONSTRAINT dno_fk FOREIGN KEY (dept_no)
        REFERENCES dept (d_no)
);

# 将指定记录分别插入两个表中。
# 向dept表中插入数据,SQL语句如下:
INSERT INTO dept
VALUES (10, 'ACCOUNTING', 'ShangHai'),
       (20, 'RESEARCH ', 'BeiJing '),
       (30, 'SALES ', 'ShenZhen '),
       (40, 'OPERATIONS ', 'FuJian ');

# 向employee表中插入数据,SQL语句如下:
INSERT INTO employee
VALUES (1001, 'SMITH', 'm', 20, 'CLERK', 800, '2005-11-12'),
       (1002, 'ALLEN', 'f', 30, 'SALESMAN', 1600, '2003-05-12'),
       (1003, 'WARD', 'f', 30, 'SALESMAN', 1250, '2003-05-12'),
       (1004, 'JONES', 'm', 20, 'MANAGER', 2975, '1998-05-18'),
       (1005, 'MARTIN', 'm', 30, 'SALESMAN', 1250, '2001-06-12'),
       (1006, 'BLAKE', 'f', 30, 'MANAGER', 2850, '1997-02-15'),
       (1007, 'CLARK', 'm', 10, 'MANAGER', 2450, '2002-09-12'),
       (1008, 'SCOTT', 'm', 20, 'ANALYST', 3000, '2003-05-12'),
       (1009, 'KING', 'f', 10, 'PRESIDENT', 5000, '1995-01-01'),
       (1010, 'TURNER', 'f', 30, 'SALESMAN', 1500, '1997-10-12'),
       (1011, 'ADAMS', 'm', 20, 'CLERK', 1100, '1999-10-05'),
       (1012, 'JAMES', 'm', 30, 'CLERK', 950, '2008-06-15');

select e_no, e_name, e_salary
from employee;

select *
from employee
where dept_no in (10, 20);

select *
from employee
where e_salary between 800 and 2500;

select *
from employee
where dept_no = 20;

select dept_no, max(e_salary)
from employee
group by dept_no;

select d_name, d_location
from dept
where d_no = (select dept_no from employee where e_name = 'BLAKE');

select d_name, d_location
from dept
where d_no in (select dept_no from employee where e_name = 'BLAKE');

select d_no, d_name, d_location
from dept
         inner join employee on dept.d_no = employee.dept_no;

select e_no, e_name, d_no, d_name, d_location
from employee,
     dept
where employee.dept_no = dept.d_no;

select dept_no, count(*) as emp_nums
from employee
group by dept_no;

select e_job, sum(e_salary)
from employee
group by e_job;

select dept_no, avg(e_salary)
from employee
group by dept_no;

select *
from employee
where e_salary < 1500;

select *
from employee
order by dept_no desc, e_salary desc;

select *
from employee
where e_name regexp '^[AS]';

# 查看到目前为止,工龄大于18年的员工信息
select *
from employee
where year(curdate()) - year(hireDate) >= 18;

select *
from employee
limit 2,6;

select min(e_salary)
from employee
where e_job = 'SALESMAN';

select *
from employee
where e_name regexp '[NS]$';

select e_name, e_job
from employee
where dept_no = (select d_no from dept where d_location = 'BeiJing');

select e_no, e_name, e_job, e_salary
from employee
         left join dept on employee.dept_no = dept.d_no;

select *
from employee
where year(hireDate) between 2001 and 2005
union
select *
from employee
where dept_no in (20, 30);

select *
from employee
where e_name like '%a%';

select *
from employee
where e_name regexp '[TCM]';

# MySQL函数
# 数学函数
select abs(-33), pi(), mod(31, 8);

select rand(), rand(), rand(10);

select round(-1.14), round(1.38, 1), sign(-21);

# 字符串函数
select char_length('date'), length('date');
select char_length('我是小花花'), length('我是小花花');
# 使用utf-8编码字符集时,一个汉子是3字节,一个数字或字母是1字节。

# 合并字符串函数 concat(s1,s2)、concat_ws(x,s1,s2)
select concat('小', '花', '花'), concat_ws('-', '小', '花', '花');

# 替换字符串函数insert(s1,x,len,s2)和字母大小写转换函数
select insert('Quest', 2, 4, 'What') as coll, lower('APPLE');
select insert('Quest', 2, 4, 'What') as coll, lcase('APPLE');

# 获取指定长度的字符串的函数left(s,n)和right(s,n)
select left('hello', 3), right('world', 4);

# 填充字符串的函数lpad(s1,len,s2)和rpad(s1,len,s2)
select lpad('hello', 10, '>'), rpad('world', 10, '*');

# 删除空格函数ltrim(s)和rtrim(s)和trim(s1 from s)
select ltrim(' hello '), rtrim(' world   '), trim('   Hello-World         ');
select trim('xy' from 'xyxyxyxhelloxyxyxy');

# 重复生成字符串repeat(s,n)
select repeat('好', 10);

# 空格函数和替换函数replace(s,s1,s2)
select concat_ws(space(1), '小', '花', '花');
select replace('www.example.com', 'com', 'cn');

# 比较字符串大小的函数strcmp(s1,s2)
select strcmp('tex', 'text2'), strcmp('txt2', 'txt'), strcmp('tt', 'tt');

# 获取子串的函数substring(s,n,len)和mid(s,n,len)
select substring('breakfast', 5)    as col1,
       substring('breakfast', 5, 3) as col2,
       substring('lunch', -3)       as col3,
       substring('lunch', -5, 3)    as col4;

select mid('breakfast', 5)    as col1,
       mid('breakfast', 5, 3) as col2,
       mid('lunch', -3)       as col3,
       mid('lunch', -5, 3)    as col4;

# 匹配子串开始位置的函数
select locate('ball', 'football'), position('ball' in 'football'), instr('football', 'ball');

# 字符串逆序的函数reverse(s)
select reverse('我爱花花');

# 返回值定位置的字符串的函数
select elt(3, '1st', '2ed', '3rd'), elt(3, 'net', 'os');

# 返回子串位置的函数find_in_set(s1,s2)
select find_in_set('Hi', 'hihi,Hey,Hi,bas');

# 日期和时间函数
# 获取当前日期的函数和获取当前时间的函数
select curdate(), current_date, curdate() + 0;
select curtime(), current_time, curtime() + 0;

# 获取当前日期和时间的函数
select current_timestamp(), localtime(), now(), sysdate();

# unix时间戳函数
select unix_timestamp(), unix_timestamp(now()), now();

select from_unixtime('1541844424');

# 返回UTC日期的函数和返回UTC时间的函数
select utc_date(), utc_date() + 0;

select utc_time(), utc_time() + 0;

# 获取月份的函数month(date)和monthname(date)
select month('2020-03-04'), monthname('2020-03-04');

# 获取星期的函数dayname(d),dayofweek(d)和weekday(d)
select dayname('2021-03-01'), dayofweek('2021-03-01'), weekday('2021-03-01');

# 获取星期数的函数week(d)和weekofyear(d)
select week('2021-03-01'), week('2021-03-01', 0), week('2021-03-01', 1), week('2021-03-01', 2);

select weekofyear('2021-03-01');

# 获取天数的函数dayofyear(d)和dayofmonth(d)
select dayofyear('2021-03-01');
select dayofmonth('2021-03-01');

# 获取年份,季度,小时,分数和秒钟的函数
select year('2020-01-01'), year('990214'), year('110405');

select quarter(now()); # 季度

select minute(now()); # 分钟

select second(now());
# 秒

# 获取日期的指定知的函数extract(type from date)
select extract(year from now())       col1,
       extract(year_month from now()) col2,
       extract(day_hour from now())   col3,
       extract(day_minute from now()) col4;

# 时间和秒钟转换的函数
select time_to_sec(now());

# 计算日期和时间的函数

# 系统信息函数
# 查看MySQL版本号
select version();

# 查看MySQL连接数
select connection_id();

# 显示有哪些进程在运行
show processlist;
show full processlist;

# 返回使用utf-8字符集的默认(当前)数据库名
select database(), schema();

# 获取用户名的函数
select user(), current_user(), system_user(), session_user();

# 获取字符串的字符集和排序方式的函数
select charset('abc'), charset(convert('abc' using latin1)), charset(version());

# 返回字符串的字符排列方式
select collation('abc');

# MySQL8.0新特性--加密函数
# md5加密函数
select md5('123456');

# sha加密函数
select sha('123456');

# sha2加密函数
select sha2('123456', 0) a, sha2('123456', 256) b, sha2('123456', 512);

# MySQL权限与安全管理
use mysql;

# user表
desc user;
select Host, User, authentication_string
from user;

# db表
desc db;
select Host, User, Db
from db;

# tables_priv表和columns_priv表
desc tables_priv;
desc columns_priv;

# 新建普通用户
# 使用create user语句创建新用户
# 用户名为:jeffery;主机名默认是'%'表示对所有的主机开放权限,这里是只对'localhost'开放权限;密码为:123456;且未设置该用户的权限。
create user 'jeffery'@'localhost' identified by '123456';
create user 'user1'@'localhost' identified by '111111';

use mysql;

select Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject
from user;

# 删除普通用户
# drop语句删除普通用户
drop user 'jeffery'@'localhost';

select Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject
from user;

# delete语句删除普通用户
delete
from mysql.user
where Host = 'localhost'
  and User = 'user1';

select Host, User, authentication_string
from user;

desc user;
# 修改用户密码
# root用户修改自己的密码
alter user 'root'@'localhost' identified by '123123';

# 修改完root用户密码后,需要执行`flush privileges;`来重新加载用户权限
flush privileges;

# root用户修改普通用户密码
# 创建普通用户
create user 'user2'@'%' identified by 'aaaaaa';
# 修改普通用户密码
alter user 'user2'@'%' identified by '222222';
set password for 'user2'@'%' = '333333';
flush privileges;

# 权限管理
# 授权
# 创建一个用户
create user 'work'@'%' identified by '123456';
# 授所有权限给work用户
grant all on *.* to 'work'@'%';
# with grant option:被授权的用户可以将这些权限赋予给别的用户
grant all on *.* to 'work'@'%' with grant option;

# 收回用户权限
revoke all on *.* from 'work'@'%';
# 可选的 ===> 删除该被收回权限的用户
drop user 'work'@'%';

# 查看用户的权限
show grants for 'user2'@'%';

Q.E.D.


一只鹅啊