常用DDL
SQL语句---DDL
数据定义语言
用于对数据库和表的管理和操作
库的管理
创建数据库
# 创建stuDB 数据库
CREATE DATABASE stuDB;
# 创建stuDB 数据库，如果这个数据已经创建也不会报错，提高容错性
CREATE DATABASE IF NOT EXISTS stuDB;
删除数据库
# 删除stuDB数据库
DROP DATABASE stuDB;
# 删除stuDB 数据库，如果这个数据库不存在也不会报错，提高容错性
DROP DATABASE IF EXISTS stuDB；
表的管理
数据类型
| 分类 | 字段类型 | 描述 |
|---|---|---|
| 整数 | bit | 0或1的整型数字 |
| int | 从-231(-2,147,483,648)到231-1(2,147,483,647)的整型数字 | |
| smallint | 从-215(-32,768)到215-1(32,767)的整型数字 | |
| tinyint | 从0到255的整型数字 | |
| bigint | -2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807) | |
| 带小数的类型... | decimal(m,n) | 128bit，不存在精度损失，常用于银行帐目计算。（28个有效位），从-1038到1038-1的定精度与有效位数的数字。最多m位。n位小数。 |
| numeric(m，n) | decimal的同义词 | |
| double(m,n) | 64bit，双精度实型，含字节数为8，数值范围-1.7E308~1.7E308（15个有效位） | |
| float(m,n) | 32bit，浮点型，含字节数为4，从-1.79E+308到1.79E+308（7个有效位），可变精度的数字 | |
| real(m,n) | 从-3.04E+38到3.04E+38可变精度的数字 | |
| 货币 | money | 从-263(-922,337,203,685,477.5808)到263-1(922,337,203,685,477.5807)的货币数据，最小货币单位千分之十，宽度4 |
| smallmoney | 从-214,748.3648到214,748.3647的货币数据，最小货币单位千分之十，宽度4 | |
| 日期时间 | time | 只有小时/分钟/秒，没有年月日 |
| date | 只有年月日，没有小时/分钟/秒 | |
| datetime | 两者皆有，保存时间范围广 | |
| timestamp | 两者皆有，保存时间范围窄/区别较大，此处地方太小.... | |
| smalldatetime | 两者皆有，保存时间范围窄/ | |
| 特殊数据类型 | Cursor | 对游标的引用 |
| uniqueidentifier | 全球唯一标识符GUID | |
| 字符数据 | char(n) | 定长非Unicode的字符型数据，最大长度为8000。n为最大字符个数，可选 |
| varchar(n) | 变长非Unicode的字符型数据，最大长度为8000。n为最大字符个数，必选 | |
| text | 变长非Unicode的字符型数据，最大长度为2^31-1(2G) | |
| Unicode | nchar | 定长Unicode的字符型数据，最大长度为8000 |
| nvarchar | 变长Unicode的字符型数据，最大长度为8000 | |
| ntext | 变长Unicode的字符型数据，最大长度为2^31-1(2G) | |
| 二进制数据 | binary | 定长二进制数据，最大长度为8000 |
| varbinary | 变长二进制数据，最大长度为8000 | |
| blob | 二进制大对象，可以存储二进制文件/图片/音像 |
创建表
# 语法：
CREATE TABLE [IF NOT EXISTS] 表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】
);
# 案例：创建一张表【没有添加约束】
CREATE TABLE IF NOT EXISTS stuinfo (
stuid INT,
stuname VARCHAR (20),
stugender CHAR(1),
email VARCHAR (20),
borndate DATETIME
) ;
约束
常见约束
说明：用于限制表中字段的数据，从而进一步令数据表中的数据一致、准确、可靠。
not null ; default ; primary key ; unique ; check ; foreign
NOT NULL 非空：用于限制该字段为必选项
DEFAULT 默认：用于限制该字段没有显式插入值，则直接显示默认值
PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空
UNIQUE 唯一：用于限制该字段不能重复，与主键不同，一个表可以有多个，且字段可以为空
CHECK 检查：用于限制该字段值必须满足指定条件
CHECK(age BETWEEN 1 AND 100)
FOREIGN 外键:用于限制两个表的关系，要求外键列的值必须来自主表的关联列
AUTO_INCREMENT 自增。
要求：
1. 主表的关联列和从表的关联列类型必须一致，含义相同，名称无要求
2. 主表的关联列要求必须是主键
实例：
-- 案例：
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
stuid INT PRIMARY KEY, --添加了 主键约束
stuname VARCHAR(20) UNIQUE NOT NULL, -- 添加了 唯一约束+非空
stugender CHAR(1) DEFAULT '男', -- 添加了默认约束
email VARCHAR(20) NOT NULL,
age INT CHECK( age BETWEEN 0 AND 100), -- 添加了检查约束，mysql不支持
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id) -- 添加了外键约束
-- majorid列 与 major表内的id列
);
修改表
语法：
ALTER TABLE 表名 ADD|CHANGE|MODIFY|DROP COLUMN 字段名 字段类型 【字段约束】
# 1，修改表名
ALTER TABLE stuinfo RENAME TO students ;
# 2.添加字段
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL ;
# 3.修改字段名
ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL ;
# 4.修改字段类型
ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;
# 5.删除字段
ALTER TABLE students DROP COLUMN birthday ;
删除表
DROP TABLE IF EXISTS students ;
复制表
# 复制表
CREATE TABLE newTable LIKE students ;
# 复制表的结构+数据
CREATE TABLE newTable SELECT * FROM girls.`admin`
案例：复制employees表中的last_name，department_id，salary字段到新表 xx 表，但不复制数据
CREATE TABLE xx
SELECT last_name, department_id, salary
FROM myemployees.`employees`
WHERE 1=2; # 恒不成立
案例
-- 案例1： 使用分页查询实现，查询员工信息表中部门为50号的工资最低的5名员工信息
SELECT *
FROM employees
WHERE department_id = 50
ORDER BY salary
LIMIT 0, 5;
-- 案例2：使用子查询实现城市为Toronto的，且工资>10000 的员工姓名
# 第一步
SELECT d.`department_id`
FROM locations l
JOIN departments d
ON l.`location_id` = d.`location_id`
AND l.`city` = 'Toronto'
# 第二步
SELECT e.`last_name`
FROM employees e
WHERE e.`salary` > 10000
AND e.department_id IN (
SELECT d.`department_id`
FROM locations l
JOIN departments d
ON l.`location_id` = d.`location_id`
AND l.`city` = 'Toronto'
);
-- 案例3：创建表qqinfo，里面包含qqid，添加主键约束（昵称nickname），添加唯一约束、邮箱email（添加非空约束）、性别gender
CREATE TABLE IF NOT EXISTS qqinfo (
qqid INT PRIMARY KEY,
email VARCHAR(20) NOT NULL,
gender CHAR
);
-- 案例4：删除表qqinfo
DROP TABLE IF EXISTS qqinfo;
-- 案例5：试写出sql查询语句定义顺序和执行顺序
1. 定义顺序（书写顺序）
SELECT DISTINCT 查询列表
FROM 表名 别名
JOIN 表名 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 条目数
2. 执行顺序
-- 选择表 ---> 连接表 ---> 分组 ---> 分组后筛选 ---> 字段名 ---> 排序 ---> 分页（分条）
FROM 子句
JOIN 子句
ON 子句
WHERE 子句
GROUP BY 子句
HAVING 子句
SELECT 子句
ORDER BY 子句
LIMIT 子句
浙公网安备 33010602011771号