数据库编程（三）| 了解SQL语言与MySQL数据库管理

一、SQL语言

1.1 SQL语言简介

结构化查询语言(Structured Query Language)简称 SQL(发音：sequal['si:kwəl])，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL 能做什么？

• SQL 面向数据库执行查询
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限

1.2 SQL 标准

SQL 是 1986 年 10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组（ISO）颁布了 SQL 正式国际标准。1989 年 4 月，ISO 提出了具有完整性特征的 SQL89 标准，1992 年11 月又公布了 SQL92 标准，在此标准中，把数据库分为三个级别：基本集、标准集和完全集。在 1999年推出 99 版标准。最新版本为 SQL2016 版。比较有代表性的几个版本：SQL86、SQL92、SQL99。

1.3 SQL语言分类

1. 数据查询语言（DQL：Data Query Language）其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。关键字 SELECT 是 DQL（也是所有 SQL）用得最多的动词。
   • SELECT
   • FROM
   • WHERE
   • ORDER BY
   • HAVING
2. 数据操作语言（DML：Data Manipulation Language）其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行。
   • INSERT：添加数据
   • UPDATE：更新数据
   • DELETE：删除数据
3. 数据定义语言（DDL：Data Definition Language）定义数据库对象语言，其语句包括动词CREATE 和 DROP 等。
   • CREATE：创建数据库对象
   • ALTER：修改数据库对象
   • DROP：删除数据库对象
4. 数据控制语言（DCL：Data Control Language）它的语句通过 GRANT 或 REVOKE 获得许可，确定用户对数据库对象的访问。
   • GRANT：授予用户某种权限
   • REVOKE：回收授予的某种权限
5. 事务控制语言（TCL ：Transaction Control Language）它的语句能确保被 DML 语句影响的表的所有行及时得以更新。
   • COMMIT：提交事务
   • ROLLBACK：回滚事务
   • SAVEPOINT：设置回滚点

注意：
数据操纵语言DML（insert、update、delete）针对表中的数据 ；
而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure、触发器trigger；

1.4 SQL语言语法

1. SQL语句不区分大小写，关键字建议大写。
2. SQL语句可以单行或多行书写，以分号结尾。

二、创建与删除数据库

2.1 创建数据库

2.1.1 使用DDL语句创建数据库

CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符编码;

示例：
创建一个test 的数据库，并查看该数据库，以及该数据库的编码。
创建数据库：

create database test default character set utf8;

查看数据库

show database

2.1.2 使用Navicat创建数据库

示例：
创建一个test2 的数据库。

 点击连接名——右键——创建数据库——输入数据库名称——选择字符集——忽略排序规则——确定

2.2 删除数据库

2.2.1 使用DDL语言删除数据库

DROP DATABASE 数据库名称;

示例：
删除 test 数据库

drop database test

2.2.2 使用Navicat删除数据库

示例：
删除test数据库

点击数据库——右键——删除数据库

三、MySQL中的数据类型

3.1 整数类型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(±9.22*10的18次方)

数值类型中的长度 m 是指显示长度，并不表示存储长度，只有字段指定 zerofill 时有用

例如： int(3) ，如果实际值是 2 ，如果列指定了 zerofill ，查询结果就是 002 ，左边用 0 来 填充

3.3 浮点类型

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

3.4 字符类型

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
tinytext	可变长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

char和varchar：

1. char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。
2. varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3. text不设置长度， 当不知道属性的最大长度时，适合用text。

按照查询速度： char最快， varchar次之，text最慢。

字符串型使用建议：

1. 经常变化的字段用varchar
2. 知道固定长度的用char
3. 尽量用varchar
4. 超过255字符的只能用varchar或者text
5. 能用varchar的地方不用text

3.5 日期类型

MySQL数据类型	含义
date	日期 YYYY-MM-DD
time	时间 HH:MM:SS
datetime	日期时间 YYYY-MM-DD HH:MM:SS
timestamp	时间戳YYYYMMDD HHMMSS

3.6 二进制数据(BLOB)

1. BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。
2. BLOB存储的数据只能整体读出。
3. TEXT可以指定字符集，BLOB不用指定字符集。

四、创建表与删除表

4.1 创建表

4.1.1 使用DDL语句创建表

CREATE TABLE 表名(列名 类型,列名 类型......);

示例：
创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。

create employees (emloyee_id int ,employee_name varchar(10),salary float(8,2);

查看已创建的表

show tables;

4.1.2 使用Navicat创建表

示例：
创建employees2表。

选中数据库——选择表——右键——新建表 或者 直接点击右侧标签栏的新建表

4.2 删除表

4.2.1 使用DDL语句删除表

DROP TABLE 表名;

示例：
删除 employees 表。

drop table employees

4.2.2 使用Navicat删除表

示例：
删除employees2表

选中表——右键——删除表

五、修改表

5.1 修改表名

5.1.1 使用DDL语句修改表

ALTER TABLE 旧表名 RENAME 新表名;

示例：
将 employees 表名修改为 emp。

alter table employees rename emp

5.1.2 使用Navicat修改表名

选择表——右键重命名

5.2 修改列名

5.2.1 使用DDL语句修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

示例：
将 emp 表中的 employee_name 修改为 name。

alter table emp change column employee_name name varchar(20);

5.2.2 使用Navicat修改列名

选中表——右键——设计表——直接对标明修改——保存

5.3 修改列类型

5.3.1 使用DDL语句修改列类型

ALTER TABLE 表名 MODIFY 列名 新类型;

示例：
将 emp 表中的 name 的长度指定为 40。

alter table emp modify name varchar(40);

5.3.2 使用Navicat修改列类型

选中表——右键——设计表——直接对类型长度修改——保存

5.4 添加新列

5.4.1 使用DDL语句添加新列

ALTER TABLE 表名 ADD COLUMN 新列名 类型;

示例：
在 emp 表中添加佣金列，列名为 commission_pct。

alter table emp add column commission_pct;

5.4.2 使用Navicat添加新列

选中表——右键——设计表——添加字段——保存

5.5 删除指定列

5.5.1 使用DDL语句删除指定列

ALTER TABLE 表名 DROP COLUMN 列名;

示例：
删除 emp 表中的 commission_pct。

alter table emp drop column commission_pct

5.5.2 使用Navicat删除指定列

选中表——右键——设计表——删除字段——保存

六、MySQL中的约束

6.1 约束概述

数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

1. 主键约束(Primary Key) PK

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。
主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

2. 外键约束(Foreign Key) FK

外键约束经常和主键约束一起使用，用来确保数据的一致性。

3. 唯一性约束(Unique)

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的。

4. 非空约束(Not Null)

非空约束用来约束表中的字段不能为空。

5. 检查约束(Check)

检查约束也叫用户自定义约束，是用来检查数据表中，字段值是否有效的一个手段，但目前MySQL 数据库不支持检查约束。

6.2 添加主键约束(Primary Key)

1. 单一主键
   使用一个列作为主键列，当该列的值有重复时，则违反唯一约束。
2. 联合主键
   使用多个列作为主键列，当多个列的值都相同时，则违反唯一约束。

6.2.1 修改表添加主键约束

6.2.1.1 使用DDL语句添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(列名)

示例：
将 emp 表中的 employee_id 修改为主键。

alter table emp add primary key(employee_id);

主键自增长

MySQL 中的自动增长类型要求：

• 一个表中只能有一个列为自动增长。
• 自动增长的列的类型必须是整数类型。
• 自动增长只能添加到具备主键约束与唯一性约束的列上。
• 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然 后在删除约束。

alter table 表名 modify 主键 类型 auto_increment;

示例：
将 emp 表中的 employee_id 主键修改为自增。

alter table emp modify int auto_increment;

6.2.1.2 使用Navicat添加主键约束

选中表——右键——设计表——点击键——设置自动递增——保存

6.2.2 删除主键

1. 使用DDL语句删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

注意：
删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除 主键。

示例：
删除emp表中的 employee_id 主键约束。
去掉自动增长：

alter table emp modify employee_id int;

删除主键：

alter table emp drop primary key;

2. 使用Navicat删除主键

选中表——右键——设计表——点击钥匙取消——取消自动递增——保存

6.3 添加外键约束(Foreign Key)

6.3.1 修改表添加外键约束

1. 使用DDL语句添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY( 列 名 ) REFERENCES 参照的表名(参照的列名);

示例一：
创建 departments 表包含 department_id 、department_name ，location_id。

create table departments(department_id int,department_name varchar(30),location_id int);

示例二：
修改departments表，向department_id列添加主键约束与自动递增。

alter table departments add primary key(department_id);
alter table departments modify department_id int auto_increment;

示例三：
修改 emp 表，添加 dept_id 列。

alter table emp add column dept_id int;

示例四：
向 emp 表中的 dept_id 列添加外键约束。

alter table emp add constraint emp_fk foreign key(dept_id) references departments(department_id);

2. 使用Navicat添加外键约束
   选中表——设计表——外键——根据标签添加对应外键——保存
   

6.3.2 删除外键约束

1. 使用DDL语句删除外键约束。

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

示例：
删除 dept_id 的外键约束。

alter table emp drop foreign key emp_fk;

2. 使用Navicat删除外键约束
   选中表——设计表——选中外键——删除外键——保存

6.4 添加唯一性约束(Unique)

6.4.1 修改表添加唯一性约束

1. 使用DDL语句添加唯一性约束。

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);

示例：
向 emp 表中的 name 添加唯一约束。

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);

2. 使用Navicat添加唯一性约束
   选中表——设计表——索引——根据标签添加对应索引——保存
   

6.4.2 删除唯一索引

1. 使用DDL语句删除唯一性约束。

ALTER TABLE 表名 DROP KEY 约束名;

示例：
删除 name 的唯一约束。

alter table emp drop key emp_uk;

2. 使用Navicat删除唯一性约束。

选中表——设计表——选中索引——删除索引——保存

6.5 非空约束(Not Null)

6.5.1 修改表添加非空约束

1. 使用DDL语句添加非空约束。

ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;

示例：
向 emp 表中的 salary 添加非空约束。

alter table emp modify salary float(8,2) not NULL;

6.5.2 删除非空约束

1. 使用DDL语句删除非空约束。

ALTER TABLE 表名 MODIFY 列名 类型 NULL;

示例：
删除emp表中salary 的非空约束。

alter table emp modify salary float(8,2) NULL;

6.6 创建表时添加约束

查询表中的约束信息：

SHOW KEYS FROM 表名;

示例：
创建 depts 表包含 department_id 该列为主键且自动增长，department_name 列不 允许重复，
location_id 列不允含有空值。

create table depts(department_id int primary key auto_increment,department_name
varchar(30) unique,location_id int not null);

七、MySQL中DML操作

7.1 添加数据(INSERT)

7.1.1 选择插入

INSERT INTO 表名(列名 1 ，列名 2 ，列名 3.....) VALUES(值 1 ，值 2 ，值
3......);

示例：
向 departments 表中添加一条数据，部门名称为 market ，工作地点 ID 为 1。

insert into departments(department_name,location_id) values("market", 1);

7.2.2 完全插入

INSERT INTO 表名 VALUES(值 1 ，值 2 ，值 3......);

注意：
如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。

示例一：
向 departments 表中添加一条数据，部门名称为 development ，工作地点 ID 为 2 。使用 default 占位。

insert into departments values(default,"development",2);

示例二：
向 departments 表中添加一条数据，部门名称为human ，工作地点 ID 为 3 。使用 null 占 位。

insert into departments values(null,"human",3);

示例三：
向 departments 表中添加一条数据，部门名称为 teaching ，工作地点 ID 为 4 。使用 0 占 位。

insert into departments values(0,"teaching",4);

7.2 默认值处理(DEFAULT)

在 MySQL 中可以使用 DEFAULT 为列设定一个默认值。如果在插入数据时并未指定该列的值，那么MySQL 会将默认值添加到该列中。

7.2.1 创建表时指定列的默认值

CREATE TABLE 表名(列名 类型 default 默认值,......);

示例：
创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name ，包含 address 该列默认 值为”未知”。

create table emp3(emp_id int primary key auto_increment,name varchar(10),address varchar(50) default 'Unknown');

7.2.2 修改表添加新列并指定默认值

ALTER TABLE 表名 ADD COLUMN 列名 类型 DEFAULT 默认值;

示例：
修改 emp3 表，添加job_id 该列默认值为 0。

alter table emp3 add column job_id int default 0;

7.2.3 插入数据时的默认值处理

如果在插入数据时并未指定该列的值，那么MySQL 会将默认值添加到该列中。如果是 完全项插入需要使用 default 来占位。
示例：
向 emp3 表中添加数据，要求 address 列与job_id 列使用默认值作为该列的值。

insert into emp3(name) values("admin");
insert into emp3 values(default,"oldlu",default,default);

7.3 更新数据(UPDATE)

UPDATE 表名 SET 列名=值，列名=值 WHERE 条件;

示例：
更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。

update emp3 set address = "BeiJing" where emp_id = 1;

7.4 删除数据(DELETE)

7.4.1 DELETE删除数据

DELETE FROM 表名 WHERE 条件;

示例：
删除 emp3 表中 emp_id 为 1 的雇员信息。

delete from emp3 where emp_id = 1;

7.4.2 TRUNCATE清空表

TRUNCATE TABLE 表名;

示例：
删除 emp3 表中的所有数据。

truncate table emp3;

7.4.3 清空表时DELETE与 TRUNCATE 区别

• truncate 是整体删除(速度较快)， delete 是逐条删除(速度较慢)；
• truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
• truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而 不是接着原来的值。而 delete 删除以后， 自增值仍然会继续累加。