MySQL的结构化语言 DDL DML DQL DCL

火腿炒馕

已于 2023-10-06 17:38:05 修改

阅读量557

点赞数

文章标签： mysql 数据库

于 2023-10-01 20:48:05 首次发布

本文链接：https://blog.csdn.net/2301_78315274/article/details/133440944

版权

一、SQL结构化语言介绍

数据查询语言DQL：其语句称为“数据检索语言”，用以从库中获取数据，确定数据怎样在应用程序给出，保留select是dql（也是所有sql）用的最多的动词

数据操作语言DML:其语句包括动词insert，update和delete，分别用于添加，修改和删除表中的行，也称为动词查询语言

数据控制语言DCL：他的语句通过grant或者revoke获得许可，确定单个用户和用户组对数据库对象的访问，某些rdbms可用grant或者revoke控制对表单个列的访问。

数据定义语言DDL：其语句包括动词create和drop，在数据库创建新表或者删除表，为表加入索引等，DDL包括许多人数据库目录中获得数据有关的保留字，他也是动作查询的一部分。

二、数据定义语言DDL

1.系统数据库的介绍

information_schema       虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等
performance_schema      主要存储数据库服务器的性能参数
mysql      授权库，主要存储系统用户的权限信息
sys            主要存储数据库服务器的性能参数（把 performance_schema的复杂度降低）

2.创建数据库：DDL

（1）命令行创建数据库

语法;mysql -u用户 -p密码 -e “create database 数据库名 default charset ‘utf8’ ”

[root@localhost ~]# mysql -uroot -p123 -e "create database db2 default charset 'utf8'"

（2）在mysql里面创建数据库

语法：create database 数据库名指定字符集；

mysql> create database db3  default charset 'utf8';
Query OK, 1 row affected (0.00 sec)

数据库命名规则：

区分大小写

唯一性

不能使用关键字 create select

不能单独使用数字

（3）查看所有数据库

语法：show databases；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

（4）查看创建库的信息

语法：show create database 库名；

mysql> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

（5）查看当前库（路径）

语法：select database（）；

mysql> select database();
+------------+
| database() |
+------------+
| db2        |
+------------+
1 row in set (0.00 sec)

（6）切换数据库

语法:use 数据库名；

mysql> use db2
Database changed

mysql> show tables;
Empty set (0.00 sec)

（7）删除数据库

语法：drop database 库名；

mysql> drop database db2;
Query OK, 0 rows affected (0.01 sec)

3.表的DDL操作

（1）在命令行创建表

语法：mysql -u用户 -p密码 -e “use 库名；create table 表名（字段数据类型约束条件）”

[root@localhost ~]# mysql -uroot -p123 -e"use db3;create table t1(name varchar(30),age int)"
mysql: [Warning] Using a password on the command line interface can be insecure.

（2）在数据库中创建表

语法：create table 表名(字段名称数据类型约束条件)

mysql> create table t2(
    -> id int primary key auto_increment not null,
    -> age int,
    -> name varchar(40)
    -> );
Query OK, 0 rows affected (0.00 sec)

（3）查看表结构

语法：desc 表名；

mysql> desc t2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| age   | int(11)     | YES  |     | NULL    |                |
| name  | varchar(40) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

（4）显示数据的详细信息

语法：show create 表名；

show table status like '表名'；

mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show table status like 't2' \G
*************************** 1. row ***************************
           Name: t2
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2023-09-28 08:33:56
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

（5）添加字段

语法：alter table 表名 add 字段名称字段类型约束条件；

mysql> alter table t2 add  math   int    not null;
                         #新字段 数据类型  约束条件
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

（6）修改数据类型

语法：alter table 表名 modify 字段名新的数据类型新的约束条件；

mysql> alter table t2 modify math varchar(20) null;  #将数据类型改为varchar，约束条件改变
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

（7）修改字段名（列）和数据类型

语法：alter table 表名 change 旧字段名新字段名新的数据类型约束条件；

mysql> alter table t2 change math chinese int null; #将math改名为Chinese，int ，null
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

（8）删除字段（列）

语法：alter table 表名 drop 字段名；

mysql> alter table t2 drop chinese;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

（9）删除表

语法：drop table 表名；

mysql> drop table t2;
Query OK, 0 rows affected (0.00 sec)

扩展：约束

表完整性约束
作用：用于保证数据的完整性和一致性
==============================================================
约束条件说明
NOT NULL 标识该字段不能为null
UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT       标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号(正数)
ZEROFILL    使用0填充，例如0000001

说明：
1. 是否允许为空，默认NULL；可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
    sex enum('male','female') not null default 'male'
   age int unsigned NOT NULL default 20        必须为正值（无符号）不允许为空默认是20

三、数据操作语言DML

1.插入数据insert

语法：insert into 表名（字段1...字段n）values（值1....值n）

mysql> insert into  employee values (1,'qiancheng','man','20180314','hr','talk',7000,501,102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee(id,name,hire_date,post,salary) values (50,'zxvb','20190423','hr',8000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee(id,name,salary) values(51,'ab',6500), (52,'cd',7600), (53,'ef',8900);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

2.更新数据update

语法：update 表名 set 字段1=值1，字段2=值2 where 条件；

mysql> update employee6 set emp_name='ab';
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | ab       |   19 |     200 |
|      2 | ab       |   26 |     201 |
|      3 | ab       |   30 |     201 |
|      4 | ab       |   24 |     202 |
|      5 | ab       |   40 |     200 |
|      6 | ab       |   28 |     204 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)

mysql> update employee6 set emp_name='cd' where emp_id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | ab       |   19 |     200 |
|      2 | ab       |   26 |     201 |
|      3 | cd       |   30 |     201 |
|      4 | ab       |   24 |     202 |
|      5 | ab       |   40 |     200 |
|      6 | ab       |   28 |     204 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)

3.删除数据delete

语法：delete from 表名 where 条件；（删除一行）

delete from 表名；（删除表中所有数据，硬盘数据还在）

truncate 表名；（效率高，时间快，硬盘数据不在）

mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | ab       |   19 |     200 |
|      2 | ab       |   26 |     201 |
|      4 | ab       |   24 |     202 |
|      5 | ab       |   40 |     200 |
|      6 | ab       |   28 |     204 |
+--------+----------+------+---------+
5 rows in set (0.00 sec)

mysql> delete from employee6;
Query OK, 5 rows affected (0.01 sec)

mysql> select * from employee6;
Empty set (0.00 sec)

mysql> desc employee6;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| emp_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| emp_name | varchar(50) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
| dept_id  | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec）

四、数据查询语言DQL

1.单表查询

（1).基础查询

a.查询所有信息

语法：select * from 表名；

mysql> select * from employee;
+----+----------+-------+------------+------------+-----------------+----------+--------+--------+
| id | name     | sex   | hire_date  | post       | job_description | salary   | office | dep_id |
+----+----------+-------+------------+------------+-----------------+----------+--------+--------+
| 20 | tom      | man   | 2017-09-15 | instructor | teach           |  8000.00 |    501 |    100 |
| 21 | alince   | woman | 2013-04-28 | instructor | teach           |  5500.00 |    501 |    100 |
| 22 | robin    | man   | 2020-09-18 | instructor | teach           |  7200.00 |    501 |    100 |
| 23 | zhuzhu   | man   | 2016-12-09 | hr         | hrcc            |  6000.00 |    502 |    101 |
| 24 | gougou   | woman | 2015-04-27 | hr         | NULL            |  6000.00 |    502 |    101 |
| 30 | maomao   | man   | 2019-08-12 | sale       | talk            | 20000.00 |    503 |    102 |
| 31 | yiyi     | man   | 2015-06-17 | talk       | NULL            |  8000.00 |   NULL |   NULL |
| 40 | harry    | woman | 2018-02-05 | hr         | hrcc            |  6900.00 |    502 |    102 |
| 41 | tianyuan | man   | 2018-02-05 | null       | salecc          |  9700.00 |    501 |    102 |
| 42 | xiaoyi   | man   | 2018-02-05 | null       | salecc          |  5700.00 |    501 |    102 |
+----+----------+-------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

b.去除重复记录

语法：select distinct 字段 from 表名；

mysql> select distinct office from employee;
+--------+
| office |
+--------+
|    501 |
|    502 |
|    503 |
|   NULL |
+--------+
4 rows in set (0.00 sec)

（2）.条件查询

语法： select 字段 from 表名 where 条件列表；

#单条件查询
mysql> select name,salary from employee where  salary = 8000
;
+------+---------+
| name | salary  |
+------+---------+
| tom  | 8000.00 |
| yiyi | 8000.00 |
+------+---------+
2 rows in set (0.00 sec)
#多条件查询
mysql> select name,salary from employee where salary between 4000 and 20000;
+----------+----------+
| name     | salary   |
+----------+----------+
| tom      |  8000.00 |
| alince   |  5500.00 |
| robin    |  7200.00 |
| zhuzhu   |  6000.00 |
| gougou   |  6000.00 |
| maomao   | 20000.00 |
| yiyi     |  8000.00 |
| harry    |  6900.00 |
| tianyuan |  9700.00 |
| xiaoyi   |  5700.00 |
+----------+----------+
10 rows in set (0.00 sec)
mysql> select name,salary from employee where salary=4000 or salary=6000;
+--------+---------+
| name   | salary  |
+--------+---------+
| zhuzhu | 6000.00 |
| gougou | 6000.00 |
+--------+---------+
2 rows in set (0.00 sec)

关键字is

#查询employee中office为空的人
mysql> select name,office from employee where office is null;
+------+--------+
| name | office |
+------+--------+
| yiyi |   NULL |
+------+--------+
1 row in set (0.00 sec)


#查询employee表中office不为空的人

mysql> select name,office from employee where office is not
 null;
+----------+--------+
| name     | office |
+----------+--------+
| tom      |    501 |
| alince   |    501 |
| robin    |    501 |
| zhuzhu   |    502 |
| gougou   |    502 |
| maomao   |    503 |
| harry    |    502 |
| tianyuan |    501 |
| xiaoyi   |    501 |
+----------+--------+
9 rows in set (0.00 sec)

关键字in

#查询employee表中工资为4000,8000，20000的员工
mysql> select name,salary from employee where salary in (4000, 8000,20000); 
+--------+----------+
| name   | salary   |
+--------+----------+
| tom    |  8000.00 |
| maomao | 20000.00 |
| yiyi   |  8000.00 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select name,salary from employee where salary in (4000,8000,20000);
+--------+----------+
| name   | salary   |
+--------+----------+
| tom    |  8000.00 |
| maomao | 20000.00 |
| yiyi   |  8000.00 |
+--------+----------+
3 rows in set (0.01 sec)

（3）.排序查询

语法：select 字段 from 表名 order by 排序字段1，排序字段2；

ASC：升序排列（默认）

DESC：降序排列

#按名字升序排列
mysql> select name from employee order by name;
+----------+
| name     |
+----------+
| alince   |
| gougou   |
| harry    |
| maomao   |
| robin    |
| tianyuan |
| tom      |
| xiaoyi   |
| yiyi     |
| zhuzhu   |
+----------+
10 rows in set (0.00 sec)

#按名字倒叙排列
mysql> select name from employee order by name desc;
+----------+
| name     |
+----------+
| zhuzhu   |
| yiyi     |
| xiaoyi   |
| tom      |
| tianyuan |
| robin    |
| maomao   |
| harry    |
| gougou   |
| alince   |
+----------+
10 rows in set (0.00 sec)

从第四行开始显示5行
mysql> select name from employee order by name desc limit 3, 5;
+----------+
| name     |
+----------+
| tom      |
| tianyuan |
| robin    |
| maomao   |
| harry    |
+----------+
5 rows in set (0.00 sec)

#按多列排序，先按入职时间，再按工资
mysql> select * from employee order by hire_date,salary asc;
+----+----------+-------+------------+------------+-----------------+----------+--------+--------+
| id | name     | sex   | hire_date  | post       | job_description | salary   | office | dep_id |
+----+----------+-------+------------+------------+-----------------+----------+--------+--------+
| 21 | alince   | woman | 2013-04-28 | instructor | teach           |  5500.00 |    501 |    100 |
| 24 | gougou   | woman | 2015-04-27 | hr         | NULL            |  6000.00 |    502 |    101 |
| 31 | yiyi     | man   | 2015-06-17 | talk       | NULL            |  8000.00 |   NULL |   NULL |
| 23 | zhuzhu   | man   | 2016-12-09 | hr         | hrcc            |  6000.00 |    502 |    101 |
| 20 | tom      | man   | 2017-09-15 | instructor | teach           |  8000.00 |    501 |    100 |
| 42 | xiaoyi   | man   | 2018-02-05 | null       | salecc          |  5700.00 |    501 |    102 |
| 40 | harry    | woman | 2018-02-05 | hr         | hrcc            |  6900.00 |    502 |    102 |
| 41 | tianyuan | man   | 2018-02-05 | null       | salecc          |  9700.00 |    501 |    102 |
| 30 | maomao   | man   | 2019-08-12 | sale       | talk            | 20000.00 |    503 |    102 |
| 22 | robin    | man   | 2020-09-18 | instructor | teach           |  7200.00 |    501 |    100 |
+----+----------+-------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

（4）分组查询

语法：select 字段,(group_concat(字段名)） from 表名 group by 字段名；

GROUP_CONCAT 函数用于将一个分组内的多行数据合并成一个字符串，并以指定的分隔符进行分隔。

mysql> select post from employee group by post;
+------------+
| post       |
+------------+
| hr         |
| instructor |
| null       |
| sale       |
| talk       |
+------------+

mysql> select post,group_concat(name) from employee group by post;
+------------+---------------------+
| post       | group_concat(name)  |
+------------+---------------------+
| hr         | zhuzhu,gougou,harry |
| instructor | tom,alince,robin    |
| null       | tianyuan,xiaoyi     |
| sale       | maomao              |
| talk       | yiyi                |
+------------+---------------------+
5 rows in set (0.00 sec)

mysql> select post,group_concat(id) from employee group by
post;
+------------+------------------+
| post       | group_concat(id) |
+------------+------------------+
| hr         | 23,24,40         |
| instructor | 20,21,22         |
| null       | 41,42            |
| sale       | 30               |
| talk       | 31               |
+------------+------------------+
5 rows in set (0.00 sec)

（5）模糊查询（通配符%）

mysql> select * from employee where salary like "%20%";
+----+--------+------+------------+------------+-----------------+----------+--------+--------+
| id | name   | sex  | hire_date  | post       | job_description | salary   | office | dep_id |
+----+--------+------+------------+------------+-----------------+----------+--------+--------+
| 22 | robin  | man  | 2020-09-18 | instructor | teach           |  7200.00 |    501 |    100 |
| 30 | maomao | man  | 2019-08-12 | sale       | talk            | 20000.00 |    503 |    102 |
+----+--------+------+------------+------------+-----------------+----------+--------+--------+
2 rows in set (0.00 sec)

（6）正则查询

mysql> select * from employee where salary regexp '72+';
+----+-------+------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex  | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+------+------------+------------+-----------------+---------+--------+--------+
| 22 | robin | man  | 2020-09-18 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)


mysql> select * from employee where name regexp '^xiao';
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| id | name   | sex  | hire_date  | post | job_description | salary  | office | dep_id |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| 42 | xiaoyi | man  | 2018-02-05 | null | salecc          | 5700.00 |    501 |    102 |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)


mysql> select * from employee where name regexp "yi$";
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| id | name   | sex  | hire_date  | post | job_description | salary  | office | dep_id |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| 31 | yiyi   | man  | 2015-06-17 | talk | NULL            | 8000.00 |   NULL |   NULL |
| 42 | xiaoyi | man  | 2018-02-05 | null | salecc          | 5700.00 |    501 |    102 |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
2 rows in set (0.00 sec)


mysql> select * from employee where name regexp "yi$";
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| id | name   | sex  | hire_date  | post | job_description | salary  | office | dep_id |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| 31 | yiyi   | man  | 2015-06-17 | talk | NULL            | 8000.00 |   NULL |   NULL |
| 42 | xiaoyi | man  | 2018-02-05 | null | salecc          | 5700.00 |    501 |    102 |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
2 rows in set (0.00 sec)

2.多表查询

a) 内连接

只连接匹配的行

mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department.dept_name from employee6,department where employee6.dept_id = department.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | yunwei    |
|      5 | robin    |   40 | hr        |
+--------+----------+------+-----------+
5 rows in set (0.00 sec)

mysql> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,department b where a.dept_id=b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | yunwei    |
|      5 | robin    |   40 | hr        |
+--------+----------+------+-----------+
5 rows in set (0.00 sec)

mysql> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a inner join department b on a.dept_id=b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | yunwei    |
|      5 | robin    |   40 | hr        |
+--------+----------+------+-----------+
5 rows in set (0.00 sec)

b)外连接

外连接：（了解）
左连接：会显示左边表内所有的值，不论在右边表内匹不匹配
右连接：会显示右边表内所有的值，不论在左边表内匹不匹配

语法：SELECT 字段 ROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

mysql> select emp_id,emp_name,age,dept_name from employee6
 left join department on employee6.dept_id = department.dep
t_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      5 | robin    |   40 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | yunwei    |
|      6 | natasha  |   28 | NULL      |
+--------+----------+------+-----------+
6 rows in set (0.00 sec)

mysql> select emp_id,emp_name,age,dept_name from employee6
 right join department on employee6.dept_id = department.de
pt_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | yunwei    |
|      5 | robin    |   40 | hr        |
|   NULL | NULL     | NULL | fd        |
+--------+----------+------+-----------+
6 rows in set (0.00 sec)

c)全外连接

全外连接：（了解）包含左、右两个表的全部行

mysql> select * from employee6 full join department;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | tianyun  |   19 |     200 |     200 | hr        |
|      1 | tianyun  |   19 |     200 |     201 | it        |
|      1 | tianyun  |   19 |     200 |     202 | yunwei    |
|      1 | tianyun  |   19 |     200 |     203 | fd        |
|      2 | tom      |   26 |     201 |     200 | hr        |
|      2 | tom      |   26 |     201 |     201 | it        |
|      2 | tom      |   26 |     201 |     202 | yunwei    |
|      2 | tom      |   26 |     201 |     203 | fd        |
|      3 | jack     |   30 |     201 |     200 | hr        |
|      3 | jack     |   30 |     201 |     201 | it        |
|      3 | jack     |   30 |     201 |     202 | yunwei    |
|      3 | jack     |   30 |     201 |     203 | fd        |
|      4 | alice    |   24 |     202 |     200 | hr        |
|      4 | alice    |   24 |     202 |     201 | it        |
|      4 | alice    |   24 |     202 |     202 | yunwei    |
|      4 | alice    |   24 |     202 |     203 | fd        |
|      5 | robin    |   40 |     200 |     200 | hr        |
|      5 | robin    |   40 |     200 |     201 | it        |
|      5 | robin    |   40 |     200 |     202 | yunwei    |
|      5 | robin    |   40 |     200 |     203 | fd        |
|      6 | natasha  |   28 |     204 |     200 | hr        |
|      6 | natasha  |   28 |     204 |     201 | it        |
|      6 | natasha  |   28 |     204 |     202 | yunwei    |
|      6 | natasha  |   28 |     204 |     203 | fd        |
+--------+----------+------+---------+---------+-----------+
24 rows in set (0.00 sec)

五、数据库控制语言DCL

1.用户管理

（1）创建用户

语法：create user '用户名'@'客户端ip地址' identified by '密码'；

mysql> create user 'cd'@'10.36.192.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

（2）修改用户信息

语法：rename user '用户名'@'客户端来源ip地址' to '新用户名'@'客户端来源IP地址'

mysql> rename user 'cd'@'10.36.192.%' to 'ef'@'192.168.142.%';
Query OK, 0 rows affected (0.00 sec)

（3）修改普通用户的密码

a)语法：set password for '用户名'@'ip地址'=password（'新密码'）；

b)语法：alter user '用户名'@'客户端来源IP地址' identified by '新密码'；

c)语法：update mysql.user set authentication_string=password('密码') where user='root' and host='localhost';

mysql> set password for 'ef'@'192.168.142.%'=password('111');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter user 'ef'@'192.168.142.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

（4）删除用户

语法：drop user '用户名'@'客户端来源ip地址'；

mysql> drop user 'ef'@'192.168.142.%';
Query OK, 0 rows affected (0.00 sec)

2.权限管理

（1）.给普通用户授权并授权

语法：grant 权限 on 数据库.表 to '用户'@'客户端来源IP地址' identified by '密码';

mysql> grant all  on *.* to 'ef'@'192.168.142.%' identified
by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

（2）.取消普通用户的权限

语法：revoke 权限 on 数据库.表 from '用户'@'客户端来源IP地址

mysql> revoke all on *.*  from 'cd'@'192.168.142.%';
Query OK, 0 rows affected (0.00 sec)

（3）.刷新权限

语法：flush privileges；

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

（4）.查看授权信息

a.查看授权语句

语法：show grants for '用户'@'客户端来源IP地址';

mysql> show grants for 'ef'@'192.168.142.%';
+-----------------------------------------------------+
| Grants for ef@192.168.142.%                         |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'ef'@'192.168.142.%' |
+-----------------------------------------------------+
1 row in set (0.00 sec)

b.查看生效的授权信息

语法：select * from mysql.user where user='用户'\G

mysql> select * from mysql.user where user='ef'\G
*************************** 1. row ***************************
                  Host: 192.168.142.%
                  User: ef
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
      password_expired: N
 password_last_changed: 2023-09-28 15:06:11
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

火腿炒馕

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL的结构化语言 DDL DML DQL DCL

虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等主要存储数据库服务器的性能参数mysql授权库，主要存储系统用户的权限信息sys主要存储数据库服务器的性能参数（目标是把 performance_schema的把复杂度降低）
复制链接

扫一扫