一、SQL
SQL(Structured Query Language)结构化查询语言,用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。
分类
- DDL:数据定义语言:create、alter、drop、truncate
- DML:数据操作语言:insert、update、delete
- DQL:数据查询语言:select、from、where
- DCL:数据控制语言:grant、revoke
- TCL:事务控制语言:commit、rollback
拼写
- SQL 语句要以分号
;
结尾 - SQL 语句不区分大小写,关键字建议使用大写
- 可以用倒引号``把表名和列名括起来。
- 可以使用空格和缩进来增强语句的可读性。
【强制】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
注释
单行注释:-- 注释内容 或 #注释内容(mysql特有)
多行注释:/* 注释 */
二、DDL
DDL:数据定义语言:create、alter、drop、truncate
(1)库的管理
1、创建数据库(create)
语法:
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];
例子:
-- eg:
CREATE DATABASE school;
CREATE DATABASE IF NOT EXISTS school
DEFAULT CHARACTER SET utf8 -- 指定默认字符集为utf8
DEFAULT COLLATE utf8_chinese_ci; -- 默认校对规则为utf8_chinese_ci(简体中文,不区分大小写)
2、查看数据库(show)
语法:
SHOW DATABASES [LIKE '数据库名'] ;
SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句
例子:
-- eg:
SHOW DATABASES; -- 查看所有数据库
SHOW DATABASES LIKE 'school'; -- 查看具体的数据库
SHOW DATABASES LIKE '%wo%'; -- like模糊查询
3、使用数据库(use)
语法:
USE 库名;
4、修改数据库(alter)
语法:
ALTER DATABASE [数据库名] {
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名> }
例子:
-- eg:
ALTER DATABASE school
DEFAULT CHARACTER SET gb2312 -- 指定字符集修改为 gb2312
DEFAULT COLLATE gb2312_chinese_ci; -- 默认校对规则修改为 gb2312_unicode_ci
-- 修改数据库名
RENAME DATABASE 旧库名 TO 新库名;
5、删除数据库(drop)
语法:
DROP DATABASE [IF EXISTS] 库名;
例子:
DROP DATABASE IF EXISTS `school`;
(2)表的管理
1、创建表(create)
语法:
CREATE TABLE [IF NOT EXISTS] `student`(
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
......
'字段名' 列类型 [属性] [索引] [注释] -- 最后一行不加逗号
)[表的类型][字符集设置][注释]
例子:
-- eg:创建学生表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` int(1) DEFAULT NULL COMMENT '性别 0:女,1:男',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='学生表';
2、修改表(alter)
- 修改表名
ALTER TABLE student RENAME AS students;
- 修改表字符集(eg:字符集修改为 gb2312,校对规则修改为 gb2312_chinese_ci。)
ALTER TABLE student CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;
- 新增字段(常用)
ALTER TABLE student ADD COLUMN age INT(4) DEFAULT NULL COMMENT '年龄'; -- 末尾添加字段(常用)
ALTER TABLE student ADD COLUMN stuId INT(4) FIRST; -- 开头添加
ALTER TABLE student ADD COLUMN stuno INT(11) AFTER name; -- 某个字段后面添加
- 修改字段类型和约束(常用)
ALTER TABLE student MODIFY COLUMN `name` VARCHAR(10) DEFAULT '匿名' COMMENT '姓名';
- 修改字段名
ALTER TABLE student CHANGE COLUMN sex gender INT(1); -- age 改为 gender
- 删除字段
ALTER TABLE student DROP COLUMN birthday;
COLUMN关键字可以省略;
字段名可以用 `` 括起来,避免字段名与关键字相同带了的困扰。
3、删除表(drop || truncate)
DROP TABLE [IF EXISTS] 表名; -- 删除表
TRUNCATE TABLE [IF EXISTS] 表名; -- 清空表数据
4、查看表
SHOW TABLES [LIKE '表名']; -- 查看有哪些表
SHOW CREATE TABLE 表名; -- 查看创建表的语句
DESCRIBE 表名; -- 查看表结构
DESC 表名; -- 简写
三、约束
为了保证表中的数据的准确和可靠性,对用户操作表的数据进行约束。
在 MySQL 中,主要支持以下 五种约束:主键约束、外键约束、唯一约束、非空约束、默认值约束。检查约束不支持
(1)主键约束:PRIMARY KEY
1. 一个表只能有一个主键,非空+唯一,不允许为空
2. 不要选择表的包含业务含义的字段作为主键,建议给每张表独立设计一个非业务含义的id字段。
3. AUTO_INCREMENT:自增长,只支持整数类型(TINYINT、SMALLINT、INT、BIGINT
4. ZEROFILL :用0填充,int(4) ->0001
CREATE TABLE test
(
id INT(11) PRIMARY KEY , -- 1.定义字段的同时指定主键
name VARCHAR(25)
);
CREATE TABLE test
(
id INT(11),
name VARCHAR(25),
PRIMARY KEY(id) -- 2.定义完所有字段之后指定主键,
);
CREATE TABLE test
(
id INT(11),
name VARCHAR(25)
);
ALTER TABLE test ADD PRIMARY KEY(id); -- 3.修改表时添加主键
ALTER TABLE test DROP PRIMARY KEY; -- 4.删除主键
(2)外键约束:FOREIGN KEY
1. 约束两个表中数据的一致性和完整性,被约束的表是从表,约束别人的表是主表,外键是在从表
2. 插入数据,先插入主表;删除数据,先删除从表
3. 一般不使用物理外键,使用逻辑外键
CREATE TABLE tb_dept -- 部门表
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL
);
CREATE TABLE tb_emp -- 员工表
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES tb_dept(id) -- 创建表时设置外键约束
);
-- 修改表时添加外键
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
ALTER TABLE tb_emp ADD CONSTRAINT fk_tb_dept FOREIGN KEY(deptId) REFERENCES tb_dept(id);
-- 删除外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
ALTER TABLE tb_emp DROP FOREIGN KEY fk_tb_dept;
(3)唯一约束:UNIQUE KEY
对字段的值不能重复, 唯一字段可以插入多个null
CREATE TABLE tb_dept
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) UNIQUE
);
-- 在修改表时添加唯一约束
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
-- 删除唯一约束
ALTER TABLE <表名> DROP INDEX <唯一约束名>;
(4)非空约束:NOT NULL
字段的值不能为空,如果用户在添加数据时没有指定值,数据库系统就会报错。
CREATE TABLE tb_dept
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
-- 改变字段的非空约束
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
(5)默认值约束:DEFAULT
在表中插入一条新记录时,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值。
CREATE TABLE tb_emp
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
addr VARCHAR(25) DEFAULT '上海'
);
-- 修改字段的默认约束
ALTER TABLE <数据表名> MODIFY COLUMN <字段名> <数据类型> DEFAULT '北京' COMMENT '地址';
(6)检查约束:CHECK
在更新表数据的时候,系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。
MySQL 的存储引擎不支持 CHECK 约束。
CREATE TABLE tb_emp
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
salary decimal(5,2),
CHECK(salary>0 AND salary<100)
);
-- 在创建表时设置检查约束
ALTER TABLE <表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
-- 在修改表时添加检查约束
ALTER TABLE <表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)