MySQL数据库之 DDL语句入门

MySQL 入门

1.SQL 语言概述

SQL(Structured Query Language) 是结构化查询语言的缩写，使用SQL和DBMS进行交互，操纵关系型数据库中的资源。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bRteWywc-1651459622998)(E:/富贵的编程日记/MySQL数据库/SQL.png)]

SQL 语言分类 ：

• 数据定义语言（DDL）： Data Definition Language，用来定义数据库的对象，如数据表、视图、索引等。

• 数据操纵语言（DML）： Data Manipulation Language，用来在数据库表中更新，增加和删除记录。

• 事务控制语言（TCL）： Transaction Control Language，用来做数据库中的事务管理。

• 数据查询语言（DQL）： Data Query Language，用来查询数据库中的数据。

• 数据控制语言（DCL）： Data Control Language，用来控制数据库的用户权限。

SQL 特点：

1. 不区分大小写，关键字建议使用大写，而数据库名，数据表名，列名建议使用小写;
2. 可以写成一行，也可以写成多行。
3. 可使用空格和缩进来增强语句的可读。
4. 3种注释 :
   • 单行注释： -- 注释内容 或 # 注释内容(MySQL特有);
   • 多行注释： /* 注释内容 */;

2. DDL 数据定义语言

DDL(Data Definition Language):数据定义语言，==用来定义数据库对象，库、表、列等；创建、删除、修改 库，表结构。==主要分为操作数据库的DDL和操作表的DDL。

• 一个数据库服务器包含多个库。
• 一个数据库包含多张表。
• 一张表包含多条记录

2.1 DDL操作数据库

2.1.1 创建数据库

• 直接创建数据库

-- 语法 
CREATE DATABASE [IF NOT EXISTS] 数据库名
-- 示例
CREATE DATABASE mydb1;
CREATE DATABASE IF NOT EXISTS mydb2;

• 指定字符集方式创建数据库

-- 语法
CREATE DATABASE [IF NOT EXISTS] 数据库名 CHARACTER SET 字符集 ;
-- 示例
CREATE DATABASE mydb3 CHARACTER SET UTF8;

• 指定字符集和排序规则方式创建数据库

-- 语法
CREATE DATABASE [IF NOT EXISTS] 数据库名 CHARACTER SET 字符集 COLLATE 排序规则；
-- 示例
CREATE DATABASE  mydb4 CHARACTER SET UTF8 COLLATE utf8_bin ;

排序规则说明：

针对不同字符集，MySQL指定了不同的排序规则，例如utf8 字符集 中指定的 utf8_general_ci 和 utf8_bin，对数据库中存储数据库的规则就有所不同。

• utf8_general_ci ：即 “大小写不敏感”, a 和 A 会在字符判断中会被当做一样的；
• utf8_bin：将字符串每个字符串用二进制数据编译存储，区分大小写，而且可以存二进制的内容；

2.1.2 查看数据库

• 查看所有数据库

-- 语法
SHOW databases;
-- 示例
SHOW databases;

• 查看指定数据库

-- 语法
SHOW CREATE DATABASE 数据库名;
-- 示例
SHOW CREATE DATABASE mydb3;

2.1.3 修改数据库

只能修改数据库的字符集和排序规则，不能修改数据库名字；

-- 语法
ALTER DATABASE 数据库名 CHARACTER SET 字符集 [COLLATE 排序规则];
-- 示例 ：修改mydb3的字符集为gbk 排序规则为 gbk_bin
ALTER DATABASE mydb3 CHARACTER SET gbk collate gbk_bin;

2.1.4 删除数据库

-- 语法
DROP DATABASE 数据库名;
-- 示例
DROP DATABASE mydb3;

2.1.5 使用数据库

• 查看正在使用的数据库

-- 语法
SELECT DATABASE();

• 切换数据库

-- 语法
USE 数据库名;
-- 示例
USE mydb1;

2.2 DDL 操作表

2.2.0 数据类型

2.2.0.1 整数类型

类型名称	字节	说明
tinyint	1	无符号：0~255 有符号：-128~127
smallint	2	无符号：0~65535 有符号：-32768~32767
mediumint	3	无符号：0~1677215 有符号：-8388608~8388607
int、integer	4	无符号：0~4294967295 有符号：-2147483648~2147483647
bigint	8	无符号：0~18446744073709551615 ；有符号：-9223372036854775808~9223372036854775807

建表时默认都是有符号的 ，如果需要使用无符号的，在建表语句中指定，例如： id int unsigned

2.2.0.2 近似数类型

在MySQL中，可以通过浮点数和定点数来表示近似值。

• 浮点数

浮点数包括float和double两种，定义格式为float(M,D)、double(M,D)

M-表示所存储的值共有M位(M的取值范围是0~255)

D-表示小数点后有D位(D的取值范围是0~30)

例如: float(7,4)存储的数据范围时-999.9999~999.9999。

• 定点数

decimal是MySQL的定点数。相较与浮点数定点数表示的近似值更加精确 。定义格式为decimal(M,D)

M-表示所存储的值共有M位(M的默认取值是10，取值范围是1~65)

D-表示小数点后有D位(D的默认取值是0，取值范围是0~30)

2.2.0.3 字符串类型

字符串类型	取值范围和存储需求
char(M)	M为0~255之间的整数
varchar(M)	M为0~65535之间的整数
tinyblob	允许长度0~255字节
blob	允许长度0~65535字节
meduimblob	允许长度0~167772150字节
longblob	允许长度0~4294967295字节
tinytext	允许长度0~255字节
text	允许长度0~65535字节
mediumtext	允许长度0~167772150字节
longtext	允许长度0~4294967295字节
varbinary(M)	允许长度0~M个字节的变长字节字符串
binary(M)	允许长度0~M个字节的定长字节字符串
enum	允许的值选自一个允许值列表，这个列表最多能存储的值数量为65535个
set	和enum类似，set类型是一个多选字符串数据类型

char 和 varchar

char和varchar两种类型相似，都是MySQL数据库中用来存储较短字符串格式的类型,但这两种类型的主要区别在于存储方式不同，char类型的长度是固定的，而varchar类型的长度是可变的。例如，存储字符串”abc”，char(5)表示存储的字符将占5个字节（包括2个空字符），而varchar(5)只占用3个字节的长度，5只是最大值，当存储的字符小于5个时，按实际长度存储。同时在检索时，char类型会删除尾部的空格，而varchar类型会保留尾部的空格。因为长度的问题，所以导致两种数据类型的数据寻址速度不同，如果字符在10个以内，推荐使用char

BLOB和TEXT

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：tinyblob、blob、mediumblob和longblob。它们只是可容纳值的最大长度不同。

有4种TEXT类型：tinytext、text、mediumtext和longtext。这些对应4种BLOB类型，有相同的最大长度和存储需求。

因为大文件中的字符可能存在非法，所以推荐使用BLOB，比如ascii字符集下存储一张含有”0XFF”字节的图片，入库过滤。

2.2.0.3 日期和时间类型

类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	1970-01-01 00:00:01.000000	2038-01-19 03:14:07.999999’
time	3	-838:59:59	838:59:59
year	1	1901	2155

对于这些数据类型的选择，有如下使用准则：

1. 如果要表示年月日，通常使用date。
2. 如果要表示年月日时分秒，通常使用datetime。
3. 如果要表示时分秒，通常使用time。
4. 如果要表示年，通常使用year。
5. 如果要表示当前系统的时间，通常使用timestamp。

timestamp 和 datetime

虽然timestamp类型和datetime类型一样可以表示日期时间，但两者存在差别。

1、timestamp类型存储的时间与MySQL数据库系统安装时所选的时区有关，在不同时区下查看的同一批数据将会得出不同的结果。

2.2.1 创建表

注意：在创建表之前，一定要先使用数据库（也就是说，表一定要创建在某个数据库中）；

-- 语法
CREATE TABLE 表名 (
字段名1 数据类型, 
字段名2 数据类型,
     ...,
    字段名n 数据类型
);

-- 示例 ：创建student表包含id,name,age,sex字段
CREATE TABLE student (
      id INT,
      name VARCHAR(20),
      birthday date
    );

2.2.2 查看表

• 查看所有表：

-- 语法
SHOW TABLES;

• 查看表结构：

-- 语法
DESC 表名;
describe 表名;

• 查看创建表的 SQL语句：

-- 语法
SHOW CREATE TABLE 表名；
--示例
SHOW CREATE TABLE student;

2.2.3 复制表结构

-- 语法
CREATE TABLE 新表名 LIKE 旧表名;
-- 示例
CREATE TABLE stu LIKE student ;

2.2.4 修改表

2.2.4.1 添加新列

-- 语法
ALTER TABLE 表名 ADD 列名  类型;
-- 示例：为学生表s1添加一个新的字段remark，类型为varchar(20)
ALTER TABLE s1 ADD  remark VARCHAR(20);

2.2.4.2 修改列类型

-- 语法
ALTER TABLE 表名  MODIFY 列名  新的类型;
-- 示例：将s1表中的remark字段的改成varchar(100)
ALTER TABLE s1 MODIFY remark VARCHAR(100);

2.2.4.3 修改列名

-- 语法
ALTER TABLE 表名 CHANGE 列名 新列名 ;
-- 示例：将s1表中的remark字段名改成intro，类型varchar(30)
ALTER TABLE s1 CHANGE remark intro VARCHAR(30);

2.2.4.4 删除列

-- 语法
ALTER TABLE 表名 DROP 列名;
-- 示例：删除s1表中的字段intro
ALTER TABLE s1  DROP  intro;

2.2.4.5 修改表名

-- 语法
RENAME TABLE 表名 TO 新表名;
-- 示例 ：将学生表student改名成student2
RENAME TABLE  student  TO  student2;

2.2.4.6 修改字符集

-- 语法
ALTER TABLE  表名 CHARACTER SET 字符集;
-- 示例：将sutden2表的编码修改成gbk
ALTER TABLE  student2 CHARACTER SET  gbk;

2.2.5 删除表

-- 语法
DROP TABLE 表名;
-- 示例：删除s1表