mysql的ddl和dml_sql基本语句大全

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: stu1
Create 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=utf8
1 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: 16384
Max_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: 0

mysql> 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)