[MySQL基础1]数据定义语言DDL与数据操作语言DML-优快云博客

本文链接：https://blog.youkuaiyun.com/wjpwjpwjp0831/article/details/149409290

这是一个新的笔记系列，由于以后工作可能会用到数据库，所以是一个MySQL的基础笔记~

Data Definition Language, 数据定义语言，用来定义数据库对象，例如数据库、表、字段

1. 数据库操作

查看所有数据库：

show databases;

查询当前所在数据库：

select database();

创建新数据库：

create database (if not exists) NAME (default charset 字符集) (collate 排序规则);

加 if not exists是防止报错，通常我们用的默认字符集是utf8mb4, 之所以不是utf8的原因是，utf8字符占3个字节，但是有时候数据库中有些字符需要占4个字节。

例如：

create database if not exists test;

删除数据库：

drop database (if exists) NAME;

跳转到某数据库：

use NAME;

2. 表操作

查看当前数据库下的所有表：

show tables;

查看具体的表信息：

desc NAME;

例如，MySQL的默认mysql数据库中的user表：

use mysql;
desc user;

mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |

创建表：

create table NAME (
	字段1 字段1类型 (comment 字段1注释), 
	字段2 字段2类型 (comment 字段2注释), 
	..., 
	字段n 字段n类型 (comment 字段n注释)
) (comment 表注释)

注意最后一个字段后面不要加逗号，注释就是描述

例如

use test;
create table users (
	id int comment '编号', 
	name varchar(500) comment '姓名',
	gender varchar(1) comment '性别'
) comment '用户信息';

mysql> desc users;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | YES  |     | NULL    |       |
| name   | varchar(500) | YES  |     | NULL    |       |
| gender | varchar(1)   | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

查看创建表时候的指令（即包含的详细信息comment）

show create table users;

修改表：

添加字段：

alter table 表名 add 字段名 类型 (comment '注释信息');

例如：

alter table users add nickname varchar(100) comment '绰号';

mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(500) | YES  |     | NULL    |       |
| gender   | varchar(1)   | YES  |     | NULL    |       |
| nickname | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

修改字段的数据类型：

alter table 表名 modify 字段名 新数据类型

例如：

alter table users modify nickname varchar(10) (comment '注释');

修改字段的名称以及数据类型：

alter table 表名 change 旧字段名 新字段名 新数据类型 (comment '注释') (约束)

删除字段：

alter table 表名 drop 字段名;

注意这个不可以加comment.

例如

alter table users drop username;

修改表名：

alter table 表名 rename to 新表名

注意这个不可以加comment.

例如

alter table users rename to user;

删除表：

直接删除表：

drop table (if exists) 表名;

跟数据库操作类似

例如

drop table if exists user;

删除表，并重新创建该表，相当于把表格式化了：

truncate table 表名

例如

truncate table users;
desc users;

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | YES  |     | NULL    |       |
| name   | varchar(500) | YES  |     | NULL    |       |
| gender | varchar(1)   | YES  |     | NULL    |       |

3. 数据类型：

MySQL中数据类型大致分为三类：数值类型，字符串类型和日期时间类型

数值类型：

名称	大小	描述
tinyint	1byte	小整数相当于Java中的byte
smallint	2byte	小整数相当于Java中的short
mediumint	3byte	中等整数
int/integer	4byte	正常整数 Java中的int
bigint	8byte	大整数 Java中的long
float	4byte	浮点数
double	8byte	双精度浮点数
decimal	依赖于m（精度）和d（标度）	依赖于m（精度）和d（标度） Java中的BigDecimal

其中，decimal的精度标度的意思：比如123.45，精度是整个数字的位数5，标度是小数点后两位，即2. decimal是为了防止浮点数运算误差的。例如，Java中需要没有进制转换误差地处理小数一般这么用：

class Solution {
    public static void main(String[] args) {
        BigDecimal a = new BigDecimal("1.2");
        BigDecimal b = new BigDecimal("2.34");

        System.out.println(a.multiply(b));
    }
}

若是用无符号，在后面加unsigned即可，例如

age tinyint unsigned

用double/float时，要指定总的位数和精确的小数，例如

height double(4, 2)

字符串类型：

名称	大小	描述
char	0~255bytes	定长字符串，若定为char(10), 则即便是单个字符’1’也占10个字符的大小
varchar	0~255bytes	不定长字符串，若定为varchar(10), 则最多占10个字符的大小
tinyblob/blob/mediumblob/longblob	255bytes~4_294_967_295bytes	二进制数据，例如视频音频安装包等
tinytext/text/mediumtext/longtext	255bytes~4_294_967_295bytes	文本数据

日期类型：

名称	格式	范围	描述
date	YYYY-MM-DD	1000-01-01到9999-12-31	年月日日期值
time	HH:MM:SS	-838:59:59到838:59:59	时分秒或表示为持续时间
year	YYYY	1901到2155	年份
datetime	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 到 9999-12-31 23:59:59	年月日时分秒混合日期
timestamp	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 到 2038-01-19 03:14:07	时间戳由于范围有限用的比较少

枚举类型：

MySQL内部将enum值存储为整数索引，而非字符串，因此非常节省空间. 对于排序规则，按照定义时的顺序排序，而非字母顺序。

格式：

enum ('A', 'B', 'C', ...)

例子：存储员工数据：

create table employee (
	id int comment '编号', 
	workno char(10) comment '员工工号', 
	name varchar(50) comment '姓名', 
	gender enum('M', 'F') comment '性别', 
	age tinyint unsigned comment '年龄', 
	idno char(18) comment '身份证号', 
	indate date comment '入职时间'
) comment '员工信息';

mysql> desc employee;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id     | int              | YES  |     | NULL    |       |
| workno | char(10)         | YES  |     | NULL    |       |
| name   | varchar(50)      | YES  |     | NULL    |       |
| gender | enum('M','F')    | YES  |     | NULL    |       |
| age    | tinyint unsigned | YES  |     | NULL    |       |
| idno   | char(18)         | YES  |     | NULL    |       |
| indate | date             | YES  |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+

Data Manipulation Language, 数据操作语言，用来对数据库中表的已有数据进行增、删、改操作

1. 添加数据

添加数据的基本语法：

假设有一个表：

mysql> desc users;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | YES  |     | NULL    |       |
| name   | varchar(500) | YES  |     | NULL    |       |
| gender | varchar(1)   | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

给指定的字段添加数据：

insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...)

给全部字段添加数据：

insert into 表名 values (值1, 值2, ...)

批量添加数据，直接写多个values即可：

insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...)
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...)

例如：

insert into users (id, name) values (2, '234');
insert into users values (1, '123', '1');
insert into users (id) values (10), (12);
insert into users values (3, '345', '1'), (4, '456', '0');

修改后表的内容：

mysql> select * from users;
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | 123  | 1      |
|    2 | 234  | NULL   |
|    3 | 345  | 1      |
|    4 | 456  | 0      |
|   10 | NULL | NULL   |
|   12 | NULL | NULL   |
+------+------+--------+

插入数据的注意事项：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的
字符串和日期型数据应该包含在引号中
插入的数据大小，应该在字段的规定范围内

2. 修改数据

语法：

update 表名 set 字段名1=值1, 字段名2=值2, ..., [where 条件]

例如

update users set name='999', gender='0' where id=1;

+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | 999  | 0      |
|    2 | 234  | NULL   |
|    3 | 345  | 1      |
|    4 | 456  | 0      |
|   10 | NULL | NULL   |
|   12 | NULL | NULL   |
+------+------+--------+

如果不加条件就修改所有数据

update users set name='999', gender='0'

+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | 999  | 0      |
|    2 | 999  | 0      |
|    3 | 999  | 0      |
|    4 | 999  | 0      |
|   10 | 999  | 0      |
|   12 | 999  | 0      |
+------+------+--------+

3. 删除数据

语法：

delete from 表名 [where 条件]

注意：这个删除的是符合条件的整条数据，而不是删除某一个字段的值。要想实现后者，应该按照修改字段，将其设置为null

例如：

mysql> delete from users where gender = '0';
Query OK, 6 rows affected (0.01 sec)

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

删除某个字段的值：

mysql> insert into users values (1, '999', '0');
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | 999  | 0      |
+------+------+--------+
1 row in set (0.00 sec)

mysql> update users set name=null;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | NULL | 0      |
+------+------+--------+
1 row in set (0.00 sec