lhl
2024-04-28
目录

SQL

# 通用语法

  1. SQL 可以单行或多行，以分号 ; 结尾
  2. SQL 可以使用空格或缩进增强语句的可读性
  3. SQL 不区分大小写，建议 关键字大小写
  4. SQL 注释， 单行注释 -- 注释内容 或者 # 注释内容， 多行注释 /* 注释内容 */

# 分类

  1. DDL, Data Definition Language 数据库模式定义语言， 用来定义数据库对象（数据库，表，字段）
  2. DML, Data Manipulation Language 数据操纵语言，用于对数据库表中数据进行增删改
  3. DQL, Data QueryLanguage 数据查询语言, 用来查询数据库中表中记录
  4. DCL, Data Control Language 数据控制语言， 用来创建数据库用户，控制数据库的访问权限

# DDL

# 查询 所有数据库

-- 查询所有数据库
SHOW DATABASES;
/* 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bill               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)
*/

SHOW DATABASES;,查询所有数据库

# 创建 数据库

-- 创建数据库 test1 如果存在，则不重复创建
CREATE DATABASE IF NOT EXISTS test1;
-- Query OK, 1 row affected (0.00 sec)

SHOW DATABASES;
/*
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bill               |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test1              |
+--------------------+
*/

CREATE DATABASE IF NOT EXISTS test1;,创建数据库 test1 如果存在，则不重复创建

# 删除数据库

--  如果存在数据库 test1，则删除
DROP DATABASE IF EXISTS test1;
-- Query OK, 0 rows affected (0.01 sec)

SHOW DATABASES;
/*
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bill               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
*/

DROP DATABASE IF EXISTS test1;,如果存在数据库 test1，则删除

# 使用 数据库

-- 使用数据库 test
USE test;
-- Database changed

USE test;,使用数据库 test

# 显示 当前使用哪个数据库

-- 显示当前使用哪个数据库

SELECT DATABASE();
/*
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
 */

USE test; SELECT DATABASE();,使用数据库 test,显示当前使用哪个数据库

# 查询 当前数据库所有表

-- 查询当前数据库所有表

 SHOW TABLES;

/*
+----------------+
| Tables_in_test |
+----------------+
| list           |
+----------------+
 */

SHOW TABLES;,查询 当前数据库所有表

# 查询 表结构

-- 查询 表结构

DESC list;

/*
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
 */

DESC list;,查询 表结构

# 查询 指定表的建表语句

-- 查询 指定表的建表语句

 SHOW CREATE TABLE list;

/*
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
 */

DESC list;,查询 指定表的建表语句

# 表创建

-- 表创建

CREATE TABLE tb_user(
  id int comment '编号',
  name varchar(50) comment '姓名',
  age int comment '年龄',
  gender varchar(1) comment '性别'
) comment '用户表';

DESC tb_user;
/*
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
 */

# 数据类型

数据类型 (opens new window)

# 表-修改

# 添加字段

desc tb_employee;
/*
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | YES  |     | NULL    |       |
| eId      | varchar(10)      | YES  |     | NULL    |       |
| name     | varchar(10)      | YES  |     | NULL    |       |
| gender   | char(1)          | YES  |     | NULL    |       |
| age      | int(10) unsigned | YES  |     | NULL    |       |
| identify | varchar(18)      | YES  |     | NULL    |       |
| income   | date             | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
 */
-- 向 tb_employee 添加字段 nickname 
ALTER TABLE tb_employee ADD nickname varchar(20);
 /*
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | YES  |     | NULL    |       |
....
| income   | date             | YES  |     | NULL    |       |
| nickname | varchar(20)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
 */

# 修改数据类型 或 字段名

desc tb_employee;
 /*
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | YES  |     | NULL    |       |
....
| nickname | varchar(20)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
 */
--  修改字段 nickname 为 varchar(25)
 alter table tb_employee modify nickname varchar(25);
  /*
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | YES  |     | NULL    |       |
...
| nickname | varchar(25)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
 */
--  修改字段nickname 为 varchar(30)，名称为 username

alter table tb_employee change nickname username varchar(30);

/*
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | YES  |     | NULL    |       |
....
| username | varchar(30)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
 */

# 删除字段

 desc tb_employee;

 /*
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | YES  |     | NULL    |       |
...
| income   | date             | YES  |     | NULL    |       |
| username | varchar(30)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
 */
-- 删除字段 username
ALTER TABLE tb_employee DROP username;
desc tb_employee;
 /*
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | YES  |     | NULL    |       |
...
| income   | date             | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
 */

# 表-修改表名

show tables;
 /*
+----------------+
| Tables_in_test |
+----------------+
| list           |
| tb_employee    |
| tb_user        |
+----------------+
 */
--  修改 tb_employee 表名为 tb_emp
 alter table tb_employee rename to tb_emp;
show tables;
 /*
+----------------+
| Tables_in_test |
+----------------+
| list           |
| tb_emp         |
| tb_user        |
+----------------+
 */

# 表-删除表

show tables;
 /*
+----------------+
| Tables_in_test |
+----------------+
| list           |
| tb_emp         |
| tb_user        |
+----------------+
 */
--  删除 表 tb_user
  drop table if exists tb_user;
show tables;
 /*
+----------------+
| Tables_in_test |
+----------------+
| list           |
| tb_emp         |
+----------------+
 */
--  删除 表 tb_emp 并重新创建 tb_emp，相当于 清空表
 truncate table tb_emp;

 show tables;
 /*
+----------------+
| Tables_in_test |
+----------------+
| list           |
| tb_emp         |
+----------------+
 */

# DDL

# 插入

-- 插入单条记录
insert into tb_character (id,name,gender,age) values ('001', '郭靖','男', 23);
-- 插入多条记录
insert into tb_character (id,name,gender,age) values ('002', '黄蓉','女', 24),('003', '杨康','男', 25);

select * from tb_character;
 /*
+------+--------+--------+------+
| id   | name   | gender | age  |
+------+--------+--------+------+
|    1 | 郭靖   | 男     |   23 |
|    2 | 黄蓉   | 女     |   24 |
|    3 | 杨康   | 男     |   25 |
+------+--------+--------+------+
3 rows in set (0.00 sec)
 */

# update


-- 修改 id = 2 的记录的名字为 张无忌
update tb_character set gender = '男', name = '张无忌' where id = 2;
select * from tb_character;
 /*
+------+-----------+--------+------+
| id   | name      | gender | age  |
+------+-----------+--------+------+
|    1 | 郭靖      | 男     |   23 |
|    2 | 张无忌    | 男     |   24 |
|    3 | 杨康      | 男     |   25 |
+------+-----------+--------+------+
 */

-- 更新全部，不指定 where
update tb_character set age = 30;
 /*
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 */
select * from tb_character;
 /*
+------+-----------+--------+------+
| id   | name      | gender | age  |
+------+-----------+--------+------+
|    1 | 郭靖      | 男     |   30 |
|    2 | 张无忌    | 男     |   30 |
|    3 | 杨康      | 男     |   30 |
+------+-----------+--------+------+
3 rows in set (0.00 sec)
 */

# delete


-- 删除 id = 2 的记录
delete from tb_character where id = 2;
 /*
Query OK, 1 row affected (0.00 sec)
 */
select * from tb_character;
 /*
Query OK, 1 row affected (0.00 sec)
+------+--------+--------+------+
| id   | name   | gender | age  |
+------+--------+--------+------+
|    1 | 郭靖   | 男     |   30 |
|    3 | 杨康   | 男     |   30 |
+------+--------+--------+------+
2 rows in set (0.00 sec)
 */

-- 不指定 where 删除 全部
delete from tb_character;
-- Query OK, 2 rows affected (0.01 sec)
select * from tb_character;
-- Empty set (0.00 sec)

# DQL

select

# 基本查询

select * from emp;
 /*
 +------+--------+-----------+--------+------+--------------------+-------------+------------+
| id   | workno | name      | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
|    1 | 00001  | 柳岩666   | 女     |   20 | 123456789012345678 | 北京        | 2000-01-01 |
|    2 | 00002  | 张无忌    | 男     |   18 | 123456789012345670 | 北京        | 2005-09-01 |
|    3 | 00003  | 韦一笑    | 男     |   38 | 123456789712345670 | 上海        | 2005-08-01 |
|    4 | 00004  | 赵敏      | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭      | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍      | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶      | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|    8 | 00008  | 黛绮丝    | 女     |   38 | 123456157123645670 | 天津        | 2015-05-01 |
|    9 | 00009  | 范凉凉    | 女     |   45 | 123156789012345678 | 北京        | 2010-04-01 |
|   10 | 00010  | 陈友谅    | 男     |   53 | 123456789012345670 | 上海        | 2011-01-01 |
|   11 | 00011  | 张士诚    | 男     |   55 | 123567897123465670 | 江苏        | 2015-05-01 |
|   12 | 00012  | 常遇春    | 男     |   32 | 123446757152345670 | 北京        | 2004-02-01 |
|   13 | 00013  | 张三丰    | 男     |   88 | 123656789012345678 | 江苏        | 2020-11-01 |
|   14 | 00014  | 灭绝      | 女     |   65 | 123456719012345670 | 西安        | 2019-05-01 |
|   15 | 00015  | 胡青牛    | 男     |   70 | 12345674971234567X | 西安        | 2018-04-01 |
|   16 | 00016  | 周芷若    | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
16 rows in set (0.00 sec)
 */
select name,workno, age from emp;
/*
+-----------+--------+------+
| name      | workno | age  |
+-----------+--------+------+
| 柳岩666   | 00001  |   20 |
| 张无忌    | 00002  |   18 |
| 韦一笑    | 00003  |   38 |
| 赵敏      | 00004  |   18 |
| 小昭      | 00005  |   16 |
| 杨逍      | 00006  |   28 |
| 范瑶      | 00007  |   40 |
| 黛绮丝    | 00008  |   38 |
| 范凉凉    | 00009  |   45 |
| 陈友谅    | 00010  |   53 |
| 张士诚    | 00011  |   55 |
| 常遇春    | 00012  |   32 |
| 张三丰    | 00013  |   88 |
| 灭绝      | 00014  |   65 |
| 胡青牛    | 00015  |   70 |
| 周芷若    | 00016  |   18 |
+-----------+--------+------+
16 rows in set (0.00 sec)
 */

# 设置别名

select name as '姓名', workaddress as '地址' from emp;
 /*
+-----------+--------+
| 姓名      | 地址   |
+-----------+--------+
| 柳岩666   | 北京   |
| 张无忌    | 北京   |
| 韦一笑    | 上海   |
| 赵敏      | 北京   |
| 小昭      | 上海   |
| 杨逍      | 北京   |
| 范瑶      | 北京   |
| 黛绮丝    | 天津   |
| 范凉凉    | 北京   |
| 陈友谅    | 上海   |
| 张士诚    | 江苏   |
| 常遇春    | 北京   |
| 张三丰    | 江苏   |
| 灭绝      | 西安   |
| 胡青牛    | 西安   |
| 周芷若    | 北京   |
+-----------+--------+
16 rows in set (0.00 sec)
 */

# 去重

select distinct  workaddress as '地址' from emp;
 /*
+--------+
| 地址   |
+--------+
| 北京   |
| 上海   |
| 天津   |
| 江苏   |
| 西安   |
+--------+
5 rows in set (0.00 sec)
 */

# 条件查询 (where)

-- 列出价格小于10美元的所有产品
SELECT prod_name, prod_price
FROM products 
WHERE prod_price < 10;

-- 列出价格小于10美元的所有产品
SELECT prod_name, prod_price
FROM products 
WHERE prod_price < 10;

# AND 操作符

-- 列出价格小于10美元的所有产品， vend_id 等于1003
SELECT prod_id, prod_name, prod_price, vend_id
FROM products 
WHERE prod_price <= 10 AND vend_id = 1003;

# OR 操作符

-- 列出价格小于10美元的所有产品
SELECT prod_id, prod_name, prod_price, vend_id
FROM products 
WHERE vend_id = 1002 OR vend_id = 1003;

注意语义

-- (vend_id=1002 OR vend_id = 1003) AND prod_price >= 10 √
-- vend_id=1002 OR vend_id = 1003 AND prod_price >= 10   ×

 SELECT prod_id, prod_name, prod_price, vend_id
 FROM products
 WHERE (vend_id=1002 OR vend_id = 1003) AND prod_price >= 10;

# IN 操作符


SELECT prod_id, prod_name, prod_price, vend_id
FROM products 
WHERE vend_id in (1002,1003);

IN 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。 IN操作符一般比OR操作符清单执行更快。 IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

# NOT 操作符


SELECT prod_id, prod_name, prod_price, vend_id
FROM products 
WHERE vend_id not in (1002,1003)
order by prod_name;

# IS NULL 操作符

-- idcard 是 null
select * from emp where idcard is NULL;
/*
+------+--------+-----------+--------+------+--------+-------------+------------+
| id   | workno | name      | gender | age  | idcard | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------+-------------+------------+
|   16 | 00016  | 周芷若    | 女     |   18 | NULL   | 北京        | 2012-06-01 |
+------+--------+-----------+--------+------+--------+-------------+------------+
1 row in set (0.00 sec)
 */

# BETWEEN ... AND... 操作符

-- age 区间为 10 ~ 20
select * from emp where age between 10 and 20;
/*
+------+--------+-----------+--------+------+--------------------+-------------+------------+
| id   | workno | name      | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
|    1 | 00001  | 柳岩666   | 女     |   20 | 123456789012345678 | 北京        | 2000-01-01 |
|    2 | 00002  | 张无忌    | 男     |   18 | 123456789012345670 | 北京        | 2005-09-01 |
|    4 | 00004  | 赵敏      | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭      | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|   16 | 00016  | 周芷若    | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)
 */

# like 操作符

-- 筛选 name 是两位, '__' 是占位符
select * from emp where name like '__';

/*
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   14 | 00014  | 灭绝   | 女     |   65 | 123456719012345670 | 西安        | 2019-05-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)
 */
-- 筛选 idcard 最后一位是 X, % 指定是从最后

  select * from emp where idcard like '%X';
/**
+------+--------+-----------+--------+------+--------------------+-------------+------------+
| id   | workno | name      | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
|    6 | 00006  | 杨逍      | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|   15 | 00015  | 胡青牛    | 男     |   70 | 12345674971234567X | 西安        | 2018-04-01 |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
2 rows in set (0.00 sec)
 */

# 聚合函数 (count, max, min, avg, sum)

  1. 将一列当作一个整体，进行计算
  2. null值不参与计算

# count

-- 统计人数
select count(*) as '人数' from emp;
/**
+--------+
| 人数   |
+--------+
|     16 |
+--------+
1 row in set (0.00 sec)
 */
--  `null` 值不参与计算
select count(idcard) as '人数' from emp;
 /**
+--------+
| 人数   |
+--------+
|     15 |
+--------+
1 row in set (0.00 sec)
 */

# max

-- age 最大
select max(age) from emp;

/**
+----------+
| max(age) |
+----------+
|       88 |
+----------+
1 row in set (0.00 sec)
 */

# min

-- age 最小
 select min(age) from emp;

/**
+----------+
| min(age) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)
 */

# avg

-- age 计算平均值
select avg(age) from emp;
/**
+----------+
| avg(age) |
+----------+
|  40.1250 |
+----------+
1 row in set (0.00 sec)
 */

# sum

-- 计算西安地区的年龄总和
select sum(age) from emp where workaddress = '西安';
/**
+----------+
| sum(age) |
+----------+
|      135 |
+----------+
1 row in set (0.00 sec)
 */

# 分组查询 (group by)

语法，select 字段 from 表名 where 条件 Group by 分组字段名 Having 分组后过滤条件

where 与 having 区别

  1. 执行时机不同， where 是分组前进行过滤，不满足 where 条件不进行分组， having 是分组之后对结果进行过滤
  2. 判断条件不同， where 不能对聚合函数进行判断，而 having 可以
-- 根据 gender 分组
 select gender, count(*) from emp group by gender;

/**
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女     |        7 |
| 男     |        9 |
+--------+----------+
2 rows in set (0.00 sec)
 */

--  根据 gender 分组， 并计算平均年龄
select gender, avg(age) from emp group by gender;

/**
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 女     |  31.4286 |
| 男     |  46.8889 |
+--------+----------+
2 rows in set (0.00 sec)
 */

-- 年龄小于 45， 并按照 地区分组， 筛选出的地区再筛掉 人数小于 3
-- 年龄小于 45， 并按照 地区分组
select workaddress, count(*) as '该地区人数' from emp where age < 45 group by workaddress;

/**
+-------------+-----------------+
| workaddress | 该地区人数      |
+-------------+-----------------+
| 上海        |               2 |
| 北京        |               7 |
| 天津        |               1 |
+-------------+-----------------+
3 rows in set (0.00 sec)


 */
-- 筛选出的地区再筛掉 人数小于 3
select workaddress, count(*) as '该地区人数' from emp where age < 45 group by workaddress having count(*) > 3;
 /**
+-------------+-----------------+
| workaddress | 该地区人数      |
+-------------+-----------------+
| 北京        |               7 |
+-------------+-----------------+
1 row in set (0.00 sec)
 */

# 排序查询 (order by)

-- 查询一般是无序的，ORDER BY 
-- 单列
SELECT prod_name 
FROM products 
ORDER BY prod_name;

-- 多列
SELECT prod_name,prod_id 
FROM products
ORDER BY prod_name,prod_id;

-- 降序
SELECT prod_price,prod_name 
FROM products 
ORDER BY prod_price DESC;

升序 asc, 默认值 降序 desc, 降序

# 分页查询 (limit)

-- 分页查询，查询第一页，每页 10条记录
select * from emp limit 0, 10;

/**
+------+--------+-----------+--------+------+--------------------+-------------+------------+
| id   | workno | name      | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
|    1 | 00001  | 柳岩666   | 女     |   20 | 123456789012345678 | 北京        | 2000-01-01 |
|    2 | 00002  | 张无忌    | 男     |   18 | 123456789012345670 | 北京        | 2005-09-01 |
|    3 | 00003  | 韦一笑    | 男     |   38 | 123456789712345670 | 上海        | 2005-08-01 |
|    4 | 00004  | 赵敏      | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭      | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍      | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶      | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|    8 | 00008  | 黛绮丝    | 女     |   38 | 123456157123645670 | 天津        | 2015-05-01 |
|    9 | 00009  | 范凉凉    | 女     |   45 | 123156789012345678 | 北京        | 2010-04-01 |
|   10 | 00010  | 陈友谅    | 男     |   53 | 123456789012345670 | 上海        | 2011-01-01 |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
10 rows in set (0.00 sec)
 */

# 执行顺序

From 表名 ===> where ===> group by ===> having ===> select 字段 ===> orderby ===> limit

# DCL

# 管理用户

# 查询用户

-- 
use mysql;
select * from user;
/*
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
3 rows in set (0.00 sec)
*/

# 创建用户

语法,create user '用户'@'主机名' IDENTIFIED BY '密码';

-- 创建用户 lv0502， 密码 123456
create user 'lv0502'@'localhost' IDENTIFIED BY '123456';
select host,user from user;
/**
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | lv0502        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)
 */

# 修改用户密码

语法,alter user '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

-- 修改用户 lv0502 密码为  12345678
alter user 'lv0502'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';

# 删除用户

语法,drop user '用户名'@'主机名';

-- 删除用户 lv0502
drop user 'lv0502'@'localhost';
select host,user from user;
/**
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
3 rows in set (0.00 sec)
 */

# 权限控制

以下是一些常见的MySQL权限：

  1. SELECT：允许用户读取数据。
  2. INSERT：允许用户向表中插入数据。
  3. UPDATE：允许用户修改表中的数据。
  4. DELETE：允许用户删除表中的数据。
  5. CREATE：允许用户创建新的数据库和表。
  6. DROP：允许用户删除现有的数据库和表
  7. ALTER：允许用户修改现有表的结构。
  8. INDEX：允许用户创建和删除索引。
  9. EXECUTE：允许用户执行存储过程。

# 查询权限

语法,SHOW GRANTS FOR 'username'@'host';,显示用户username的权限

-- 创建 lv0502
create user 'lv0502'@'localhost' IDENTIFIED BY '123456';
-- 查询 lv0502 权限
show grants for  'lv0502'@'localhost';
/*
+--------------------------------------------+
| Grants for lv0502@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'lv0502'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)
 */

# 授予权限

GRANT SELECT, INSERT ON database.table TO 'username'@'host';,给用户username授予在指定数据库database的table表上选择和插入的权限，限制访问来源为host。

-- 授予 lv0502 查询  test.emp 表权限
grant select on test.emp to 'lv0502'@'localhost';
show grants for  'lv0502'@'localhost';
/**
+------------------------------------------------------+
| Grants for lv0502@localhost                          |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lv0502'@'localhost'           |
| GRANT SELECT ON `test`.`emp` TO 'lv0502'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)
 */
-- insert, update, delete 都做不了
delete from emp where workaddress = '北京';
-- ERROR 1142 (42000): DELETE command denied to user 'lv0502'@'localhost' for table 'emp'
update emp set name = '周芷若222' where name = '周芷若';
-- ERROR 1142 (42000): UPDATE command denied to user 'lv0502'@'localhost' for table 'emp'
 insert into emp  values (17,00017, '周芷若111',23, NULL, '北京', '2012-06-01');
-- ERROR 1142 (42000): INSERT command denied to user 'lv0502'@'localhost' for table 'emp'

grant update on test.emp to 'lv0502'@'localhost';
-- Query OK, 0 rows affected (0.00 sec)
show grants for  'lv0502'@'localhost';
/**
+--------------------------------------------------------------+
| Grants for lv0502@localhost                                  |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lv0502'@'localhost'                   |
| GRANT SELECT, UPDATE ON `test`.`emp` TO 'lv0502'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
 */

# 撤销权限

REVOKE INSERT ON database.table FROM 'username'@'host';,撤销用户 username 在 database 数据库的 table 表上的插入权限。

REVOKE update ON test.emp FROM 'lv0502'@'localhost';

-- Query OK, 0 rows affected (0.00 sec)
show grants for  'lv0502'@'localhost';
/**
+------------------------------------------------------+
| Grants for lv0502@localhost                          |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lv0502'@'localhost'           |
| GRANT SELECT ON `test`.`emp` TO 'lv0502'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)
 */
#SQL
产品需求文档
函数

← 函数→

最近更新
01
lhl learn notes
02
filter
06-09
03
decorator
06-09
更多文章>
Theme by Vdoing