MySQL DDL 、DML、DQL语句

DDL：数据定义语言

注意：对表操作的时候需要进入到对应的数据库里面去。

创建表：CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ ( 字段1 修饰符, col2 字段2 修饰符, …)

例如：创建一张名为stu1的表，表中包含的字段有id，name和age，id的数据类型是int，且是主键并且自动增长。

mysql> create table stu1 (id int primary key auto_increment,name varchar(20) not null, age tinyint unsigned);Query OK, 0 rows affected (0.06 sec)mysql> show tables;+-------------------+| Tables_in_student |+-------------------+| stu1              |+-------------------+1 row in set (0.00 sec)

查看表

• 查看表列表

• 查看创建表的命令

• 查看表结构（字段）信息

• 查看表属性信息

查看表：show tables [from db_name]

注意：不加db_name，默认查看的是当前数据库里面的所有表。

mysql> show tables from student;+-------------------+| Tables_in_student |+-------------------+| stu1              |+-------------------+1 row in set (0.00 sec)

查看创建表的命令：SHOW CREATE TABLE tbl_name

mysql> show create table stu1 \G*************************** 1. row ***************************       Table: stu1Create Table: CREATE TABLE `stu1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL,  `age` tinyint(3) unsigned DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 row in set (0.00 sec)

查看表的结构(字段)信息：

• desc tb_name

• SHOW COLUMNS FROM [db_name.]tb_name

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

查看表的属性信息：show table status like ‘tb_name’

注意：mysq客户端的ego–(\G)命令可以垂直显示结果

*************************** 1. row ***************************           Name: stu1         Engine: InnoDB        Version: 10     Row_format: Compact           Rows: 6 Avg_row_length: 2730    Data_length: 16384Max_data_length: 0   Index_length: 0      Data_free: 0 Auto_increment: 10    Create_time: 2022-09-09 00:56:44    Update_time: NULL     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:         Comment: 1 row in set (0.00 sec)

修改表：

#修改表名ALTER TABLE students RENAME s1;#添加字段ALTER TABLE s1 ADD phone varchar(11) AFTER name;#修改字段类型ALTER TABLE s1 MODIFY phone int;#修改字段名称和类型ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);#删除字段ALTER TABLE s1 DROP COLUMN mobile;#修改字符集ALTER TABLE s1 character set utf8;#修改数据类型和字符集ALTER TABLE s1 change name name varchar(20) character set utf8;#添加字段ALTER TABLE students ADD gender ENUM('m','f');alter table student modify is_del bool default false;#修改字段名和类型ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;#删除字段ALTER TABLE students DROP age;#查看表结构DESC students;#新建表无主键，添加和删除主键CREATE TABLE t1 SELECT * FROM students;ALTER TABLE t1 add primary key (stuid);ALTER TABLE t1 drop primary key ;#添加外键ALTER TABLE students add foreign key(TeacherID) references teachers(tid);#删除外键SHOW CREATE TABLE students #查看外键名ALTER TABLE students drop foreign key ;

DML:数据操纵语言（insert、updete、delete）

注意：设计字符类型的数据类型，进行操作的时候要加上引号（单双都可以）

INSERT 语句：insert tb_name(col1…coln) values （value1…valuen）

例如：

mysql> insert stu1(name,age) values('tom',10);Query OK, 1 row affected (0.01 sec)

全值插入：不指定col

mysql> insert stu1 values(3,'BOB',20);Query OK, 1 row affected (0.00 sec)

UPDATE 语句

注意：使用update语句的时候需要指定限制条件，不然将修改所有行的指定字段

mysql> update stu1 set name='bob' where name='BOB';Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from stu1;+----+------+------+| id | name | age  |+----+------+------+|  1 | tom  |   10 ||  2 | bob  |   20 ||  3 | bob  |   20 |+----+------+------+3 rows in set (0.00 sec)

可以通过在配置文件指定选项来避免这个错误。

[root@centos8 ~]#vim /etc/my.cnf[mysql]safe-updates

删除指定的记录：

ysql> delete from stu1 where id=3 ;Query OK, 1 row affected (1.68 sec)mysql> select * from stu1 ;+----+------+------+| id | name | age  |+----+------+------+|  1 | tom  |   10 ||  2 | bob  |   20 |+----+------+------+2 rows in set (0.00 sec)

删除数据： delete from tb_name where 限制条件

注意：不加限制条件会清空表里面的所有数据。

mysql> delete from stu1 where  id=5;Query OK, 1 row affected (0.00 sec)mysql> select * from stu1;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   10 ||  2 | bob    |   20 ||  4 | bob2   |   22 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 ||  8 | tom2   | NULL |+----+--------+------+6 rows in set (0.00 sec)

清空数据表，保留表结构的方法；

• delete from tb_name（不会缩减数据文件的大小）

• TRUNCATE TABLE tbl_name（会自动缩减数据文件的大小）

• 缩减表的大小：OPTIMIZE TABLE tb_name

DQL：数据查询语言（select）select查询

• 单表操作

• 多表操作

针对单表操作：简单查询：select 需要查询得字段 from tb_name where 限制条件

• 指定字段别名

范例：字段显示的时候使用别名

mysql> select id as '编号',name as '名字',age as '年龄' from stu1;+--------+--------+--------+| 编号   | 名字   | 年龄   |+--------+--------+--------+|      1 | tom    |     10 ||      2 | bob    |     20 ||      3 | bob    |     20 |+--------+--------+--------+3 rows in set (0.00 sec)

• select可以实现加减乘除运算

mysql> select 1+2+3*4-5+9;+-------------+| 1+2+3*4-5+9 |+-------------+|          19 |+-------------+1 row in set (0.00 sec)

• select可以实现比较的操作（大于、小于、等于等）

mysql> select 1>99;+------+| 1>99 |+------+|    0 |+------+1 row in set (0.00 sec)mysql> select 1<99;+------+| 1<99 |+------+|    1 |+------+1 row in set (0.00 sec)

• BETWEEN：查询一个范围: BETWEEN min_num AND max_num

mysql> select * from stu1 where age  between 20 and 25;+----+--------+------+| id | name   | age  |+----+--------+------+|  2 | bob    |   20 ||  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 |+----+--------+------+4 rows in set (0.00 sec)

• IN：实现不连续的查询: IN (element1, element2, …)

mysql> select * from stu1 where age in(10,20,25);+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   10 ||  2 | bob    |   20 ||  6 | bob5=3 |   25 |+----+--------+------+3 rows in set (0.00 sec)

• 空查询: IS NULL, IS NOT NULL

mysql> select * from stu1 where age is null;+----+------+------+| id | name | age  |+----+------+------+|  8 | tom2 | NULL |+----+------+------+1 row in set (0.01 sec)mysql> select * from stu1 where age is NOT null;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   10 ||  2 | bob    |   20 ||  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 |+----+--------+------+6 rows in set (0.00 sec)

• DISTINCT： 去除重复行

mysql> select * from stu1;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   10 ||  2 | bob    |   20 ||  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 ||  8 | tom2   | NULL ||  9 | bob    |   20 |+----+--------+------+8 rows in set (0.00 sec)mysql> select distinct * from stu1;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   10 ||  2 | bob    |   20 ||  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 ||  8 | tom2   | NULL ||  9 | bob    |   20 |+----+--------+------+8 rows in set (0.00 sec)

• like： 模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符

#like 后面的字符需要用引号括起来，可以是单引号，也可以是双引号mysql> SELECT * from stu1 where age like '1%';+----+------+------+| id | name | age  |+----+------+------+|  1 | tom  |   10 |+----+------+------+1 row in set (0.00 sec)

• 逻辑操作符：NOT，AND，OR，XOR

group by：根据指定的条件把查询结果进行”分组”以用于做”聚合”运算

group by通常结合聚合函数来使用。常用聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计

注意：

• 一旦对表进行分组以后，select后面的字段要么是聚合函数要么就是分组的字段。

• group by（分组后）的后面加条件必须用having

• gtoup by（分组前）的前面加条件可以用where

例如：按照姓名来进行分组，统计每个姓名都有多少人。

mysql> select * from stu1;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   10 ||  2 | bob    |   20 ||  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 ||  8 | tom2   | NULL ||  9 | bob    |   20 |+----+--------+------+8 rows in set (0.00 sec)mysql> select name , count(*) from stu1 group by name;+--------+----------+| name   | count(*) |+--------+----------+| bob    |        2 || bob2   |        1 || bob2=3 |        1 || bob5   |        1 || bob5=3 |        1 || tom    |        1 || tom2   |        1 |+--------+----------+

ORDER BY: 根据指定的字段对查询结果进行排序

• 升序：ASC

• 降序：DESC

mysql> select * from stu1 order by age desc;+----+--------+------+| id | name   | age  |+----+--------+------+|  7 | bob5   |   26 ||  6 | bob5=3 |   25 ||  5 | bob2=3 |   24 ||  4 | bob2   |   22 ||  2 | bob    |   20 ||  9 | bob    |   20 ||  1 | tom    |   10 ||  8 | tom2   | NULL |+----+--------+------+8 rows in set (0.00 sec)mysql> select * from stu1 order by age asc;+----+--------+------+| id | name   | age  |+----+--------+------+|  8 | tom2   | NULL ||  1 | tom    |   10 ||  2 | bob    |   20 ||  9 | bob    |   20 ||  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 |+----+--------+------+8 rows in set (0.00 sec)

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0

例如：limit 3，5表示的就是跳过前三个，只显示五条记录。实现分页显示。

mysql> select * from stu1;+----+---------+------+| id | name    | age  |+----+---------+------+|  1 | tom     |   10 ||  2 | bob     |   20 ||  4 | bob2    |   22 ||  6 | bob5=3  |   25 ||  7 | bob5    |   26 ||  8 | tom2    | NULL || 10 | liyi    |   25 || 11 | lier    |   26 || 12 | zhangwu |   22 || 13 | xiaosi  |   30 || 14 | wuad    |   40 |+----+---------+------+11 rows in set (0.00 sec)mysql> select * from stu1 limit 3,5;+----+--------+------+| id | name   | age  |+----+--------+------+|  6 | bob5=3 |   25 ||  7 | bob5   |   26 ||  8 | tom2   | NULL || 10 | liyi   |   25 || 11 | lier   |   26 |+----+--------+------+5 rows in set (0.00 sec)

例如：显示年龄最小的五个（会自动去掉重复的）

mysql> select * from stu1  order by age;+----+--------+------+| id | name   | age  |+----+--------+------+|  8 | tom2   | NULL ||  1 | tom    |   10 ||  2 | bob    |   20 ||  9 | bob    |   20 ||  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 |+----+--------+------+8 rows in set (0.00 sec)mysql> select * from stu1  order by age limit 5;+----+------+------+| id | name | age  |+----+------+------+|  8 | tom2 | NULL ||  1 | tom  |   10 ||  2 | bob  |   20 ||  9 | bob  |   20 ||  4 | bob2 |   22 |+----+------+------+5 rows in set (0.00 sec)

多表查询：查询的结果来自于多张表。多表查询的方法：

• 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询

• 联合查询：UNION ，两张表纵向合并形成一个大表

• 交叉连接：笛卡尔乘积 CROSS JOIN ，横向连接，把第一个表的每条记录都和第二张表进行组合，从而形成一个大表

• 内连接：取两张表得交集（都符合条件得那一部分）

• 外连接：outer inner
  左外连接：左边表的全部内容+交集部分，FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
  右外连接：右边表的全部内容+交集部分，FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

子查询：一个查询结果作为另一个查询的条件。

例如：

mysql> select * from stu1 where age >(select avg(age) from stu1);+----+--------+------+| id | name   | age  |+----+--------+------+|  4 | bob2   |   22 ||  5 | bob2=3 |   24 ||  6 | bob5=3 |   25 ||  7 | bob5   |   26 |+----+--------+------+4 rows in set (0.00 sec)

union：联合查询 ，将两张表纵向合并，合成一个新的大表

前提：

• 字段（列）需要保持一致。

• 数据类型要匹配

mysql> select * from stu1 union select * from teach;+----+-----------+------+| id | name      | age  |+----+-----------+------+|  1 | tom       |   10 ||  2 | bob       |   20 ||  4 | bob2      |   22 ||  5 | bob2=3    |   24 ||  6 | bob5=3    |   25 ||  7 | bob5      |   26 ||  8 | tom2      | NULL ||  1 | zhang san |   40 ||  2 | li si     |   45 ||  3 | wang wu   |   46 |+----+-----------+------+10 rows in set (0.00 sec)

交叉连接: cross join (横向笛卡尔积)

• 横向合并：把第一个表的每条记录都和第二张表进行组合，从而形成一个大表。（笛卡尔乘积）

• 最终生成的记录数：A表的记录数*B表的记录数

注意：交叉连接慎用，同意造成数据库死机

mysql> select * from stu1    -> cross join    -> teach;+----+--------+------+-----+-----------+------+| id | name   | age  | TID | NAME      | age  |+----+--------+------+-----+-----------+------+|  1 | tom    |   10 |   1 | zhang san |   40 ||  1 | tom    |   10 |   2 | li si     |   45 ||  1 | tom    |   10 |   3 | wang wu   |   46 ||  2 | bob    |   20 |   1 | zhang san |   40 ||  2 | bob    |   20 |   2 | li si     |   45 ||  2 | bob    |   20 |   3 | wang wu   |   46 ||  4 | bob2   |   22 |   1 | zhang san |   40 ||  4 | bob2   |   22 |   2 | li si     |   45 ||  4 | bob2   |   22 |   3 | wang wu   |   46 ||  5 | bob2=3 |   24 |   1 | zhang san |   40 ||  5 | bob2=3 |   24 |   2 | li si     |   45 ||  5 | bob2=3 |   24 |   3 | wang wu   |   46 ||  6 | bob5=3 |   25 |   1 | zhang san |   40 ||  6 | bob5=3 |   25 |   2 | li si     |   45 ||  6 | bob5=3 |   25 |   3 | wang wu   |   46 ||  7 | bob5   |   26 |   1 | zhang san |   40 ||  7 | bob5   |   26 |   2 | li si     |   45 ||  7 | bob5   |   26 |   3 | wang wu   |   46 ||  8 | tom2   | NULL |   1 | zhang san |   40 ||  8 | tom2   | NULL |   2 | li si     |   45 ||  8 | tom2   | NULL |   3 | wang wu   |   46 |+----+--------+------+-----+-----------+------+21 rows in set (0.00 sec)

内连接：inner join 取两张表横向合并交集（两张表都符合条件的部分）

注意：内连接的条件要使用on来进行连接。

mysql> select * from stu1 inner join teach on stu1.id=teach.TID;+----+------+------+-----+-----------+------+| id | name | age  | TID | NAME      | age  |+----+------+------+-----+-----------+------+|  1 | tom  |   10 |   1 | zhang san |   40 ||  2 | bob  |   20 |   2 | li si     |   45 |+----+------+------+-----+-----------+------+2 rows in set (0.00 sec)#挑选对应想要的字段mysql> select stu1.id,stu1.name,teach.name  from stu1 inner join teach on stu1.id=teach.TID;+----+------+-----------+| id | name | name      |+----+------+-----------+|  1 | tom  | zhang san ||  2 | bob  | li si     |+----+------+-----------+2 rows in set (0.00 sec)对表起别名：直接在表名后面加别名mysql> select s.id,s.name,t.name  from stu1 s inner join teach t on s.id=t.TID;+----+------+-----------+| id | name | name      |+----+------+-----------+|  1 | tom  | zhang san ||  2 | bob  | li si     |+----+------+-----------+2 rows in set (0.00 sec)

外连接：outer join

• 左外连接：left join

• 右外连接：right join

左外连接：left join 左边表的全部内容+交集部分

mysql> select stu1.id,stu1.name,teach.name  from stu1 left join teach on stu1.id=teach.TID;+----+--------+-----------+| id | name   | name      |+----+--------+-----------+|  1 | tom    | zhang san ||  2 | bob    | li si     ||  4 | bob2   | NULL      ||  5 | bob2=3 | NULL      ||  6 | bob5=3 | NULL      ||  7 | bob5   | NULL      ||  8 | tom2   | NULL      |+----+--------+-----------+7 rows in set (0.00 sec)

右外连接： right join 右边表的全部内容+交集部分

mysql> select stu1.id,stu1.name,teach.name  from stu1 right  join teach on stu1.id=teach.TID;+------+------+-----------+| id   | name | name      |+------+------+-----------+|    1 | tom  | zhang san ||    2 | bob  | li si     || NULL | NULL | wang wu   |+------+------+-----------+3 rows in set (0.00 sec)