MySQL学习笔记_中(数据操作语言(DML_insert&update&delete)、数据定义语言(DDL_create&drop&alter))

 
  把最麻烦的查询写完了，下面继续写DML中的insert、delete、update以及DDL中的CREATE、DROP、ALTER吧，这篇博客的内容只要记住就会用了。
注：这里会把章节都续上，都是SQL基础知识。这块内容写完后会把链接都贴到这里的。

上一篇：MySQL学习笔记_上(select查询)
 

---

第4章：数据处理之增删改 —DML

4.1 插入数据

-使用 INSERT 语句向表中插入数据。语法如下：

INSERT INTO table[(column[,column...])]
VALUES  [(value[,value...])]

注意事项：

• 使用这种语法一次只能向表中插入一条数据。
• 为每一列添加一个新值。
• 按列的默认顺序列出各个列的值。
• 在 INSERT 子句中随意列出列名和他们的值。
• VALUES也可以写成VALUE，但是VALUES是标准写法。
• 字符和日期型数据应包含在单引号中。

INSERT INTO departments(department_id, department_name, 
                        manager_id, location_id)
VALUES      (70, 'Pub', 100, 1700);

向表中插入空值

• 隐式方式：在列名表中省略该列的值。

INSERT INTO	departments (department_id,department_name )
VALUES (30, 'Purchasing');

• 显示方式：在VALUES 子句中指定空值。

INSERT INTO	departments
VALUES		(100, 'Finance', NULL, NULL);

 
同时插入多行数据

INSERT INTO emp(NAME)
VALUES('Tom'),('Jerry'),('Rose');

 
从其它表中拷贝数据

• 在 INSERT 语句中加入子查询。
• 不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对应。

INSERT INTO emp2 
SELECT * 
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';

4.2 更新数据

使用 UPDATE 语句更新数据。语法如下：

UPDATE table
SET    column=value[,column=value,...]
[WHERE  conition];

注意：

• 可以一次更新多条数据。

• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

• 使用 WHERE 子句指定需要更新的数据。

UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;

• 如果省略 WHERE 子句，则表中的所有数据都将被更新。

UPDATE 	copy_emp
SET    	department_id = 110;

 
更新中的数据完整性错误

UPDATE employees
SET    department_id = 55
WHERE  department_id = 110;

说明：不存在 55 号部门

 

4.3 删除数据

使用 DELETE 语句从表中删除数据，语法如下：

DELETE FROM table
[WHERE  conition];

• 使用 WHERE 子句删除指定的记录。

DELETE FROM departments
WHERE  department_name = 'Finance';

• 如果省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM  copy_emp;

 
删除中的数据完整性错误

DELETE FROM departments
WHERE       department_id = 60;

说明：You cannot delete a row that contains a primary key that is used as a foreign key in another table.

 
 

第5章：创建和管理表 —DDL

5.1 创建和删除数据库

• 创建一个保存员工信息的数据库

create database employees;

注意：database不能改名。一些可视化工具可以改名，它是建新库,把所有表复制到新库,再删旧库完成的。

• 查看当前所有的数据库

show databases; 

• 使用指定的某个数据库,不指定数据库会出错，千万不要忘记创建完use一下。

use 数据库名; 

• 查看指定库下所有的表

show tables from 数据库名;

• 删除指定的数据库

drop database 数据库名;

• 查看当前正在使用的数据库

SELECT DATABASE() from dual;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

 

5.2 标识符命名规则

• 数据库名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 不能在对象名的字符间留空格
• 必须不能和用户定义的其他对象重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突
• 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里就不要变成字符型了

 

5.3 常用的数据类型

5.3.1 MySQL中的数据类型

• 整型（xxxint）
• 位类型(bit)
• 浮点型（float和double、real）
• 定点数（decimal,numeric）
• 日期时间类型（date,time,datetime,year）
• 字符串（char,varchar,xxxtext）
• 二进制数据（xxxBlob、xxbinary）
• 枚举（enum）
• 集合（set）

注：黑体代表常用的。

 

5.3.2 常用数据类型及其范围

数据类型	描述
INT	从-231到231-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度。D<=M<=255,0<=D<=30，默认M+D<=15
DATE	日期型数据，格式’YYYY-MM-DD’
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

 

5.4 创建表

5.4.1 创建方式一

必须具备：

• CREATE TABLE权限
• 存储空间

CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

必须指定：

• 表名
• 列名, 数据类型, 尺寸

CREATE TABLE dept(
deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13));

DESCRIBE dept;

--创建表
CREATE TABLE emp (
  --int类型，自增
  emp_id INT AUTO_INCREMENT,
  --最多保存20个中英文字符
  emp_name CHAR (20),
  --总位数不超过15位
  salary DOUBLE,
  --日期类型
  birthday DATE,
  --主键
  PRIMARY KEY (emp_id)
) ;

• 查看表的定义：

SHOW CREATE TABLE 表名;

 

5.4.2 创建方式二：基于现有的表

使用 AS subquery 选项，将创建表和插入数据结合起来

create table 表名 [(column,column...)]
as subquery;

指定的列和子查询中的列要一一对应

• 通过列名和默认值定义列

create table emp1 as select * from employees;
create table emp2 as select * from employees where 1=2; --创建的emp2是空表

CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL
FROM    employees
WHERE   department_id = 80;

DESCRIBE dept80;

 

5.5 修改表

使用 ALTER TABLE 语句可以实现：

• 向已有的表中添加列

• 修改现有表中的列

• 删除现有表中的列

• 重命名现有表中的列

5.5.1 追加一个列

ALTER TABLE dept80 
ADD job_id varchar(15);

5.5.2 修改一个列

• 可以修改列的数据类型, 尺寸和默认值

ALTER TABLE	dept80
MODIFY (last_name VARCHAR(30));

ALTER TABLE	dept80
MODIFY		(salary double(9,2) default 1000);

• 对默认值的修改只影响今后对表的修改

5.5.3 重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15); 

5.5.4 删除一个列

使用 DROP COLUMN 子句删除不再需要的列。

ALTER TABLE  dept80
DROP COLUMN  job_id; 

5.6 重命名表

执行RENAME语句改变表, 视图的名称

• 方式一：

RENAME TABLE emp
TO myemp;

• 方式二：

ALTER table dept
RENAME [TO] detail_dept;  -- [TO]可以省略

• 必须是对象的拥有者

5.7 删除表

注意事项：

• 数据和结构都被删除

• 所有正在运行的相关事务被提交

• 所有相关索引被删除

• DROP TABLE 语句不能回滚

DROP TABLE dept80;

5.8 清空表

TRUNCATE TABLE语句:

• 删除表中所有的数据
• 释放表的存储空间

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚。

可以使用 DELETE 语句删除数据,可以回滚。

• 对比：

  set autocommit = false;
  
  delete from emp2;
  
  select * from emp2;
  
  rollback;
  
  select * from emp2;

 
 

第6章：约束(constraint)

6.1 什么是约束

• 为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
  • 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
  • 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
  • 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
  • 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。
• 约束是表级的强制规定
• 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）
• 根据约束数据列的限制，约束可分为：
  • 单列约束：每个约束只约束一列
  • 多列约束：每个约束可约束多列数据
• 根据约束的作用范围，约束可分为：
  • 列级约束：只能作用在一个列上，跟在列的定义后面
  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义
• 根据约束起的作用，约束可分为：
  • NOT NULL 非空约束，规定某个字段不能为空
  • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
  • PRIMARY KEY 主键(非空且唯一)约束
  • FOREIGN KEY 外键约束
  • CHECK 检查约束
  • DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

• 查看某个表已有的约束

SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'employees';

6.2 NOT NULL约束

• not null：保证列值不能为空

• 非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

• Null类型特征：

  • 所有的类型的值都可以是null，包括int、float等数据类型
  • 空字符串’'不等于null，0也不等于null

• 创建 not null 约束

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL DEFAULT 'abc',
sex CHAR NULL
);

• 增加 not null 约束

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

• 取消 not null 约束

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

• 取消 not null 约束，增加默认值

ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

 

6.3 UNIQUE约束

UNIQUE注意事项：

• 同一个表可以有多个唯一约束。

• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。

• 在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。

• MySQL会给唯一约束的列上默认创建一个唯一索引。

CREATE TABLE t_course(
	cid INT PRIMARY KEY,
	cname VARCHAR(100) UNIQUE,
	description VARCHAR(200)
);

CREATE TABLE USER(
 id INT NOT NULL,
 NAME VARCHAR(25),
 PASSWORD VARCHAR(16),
 --使用表级约束语法
 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

表示用户名和密码组合不能重复

• 添加唯一约束

ALTER TABLE USER 
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
MODIFY NAME VARCHAR(20) UNIQUE;

• 删除约束

  • 删除唯一约束只能通过删除唯一索引的方式删除。
  • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
  • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

ALTER TABLE USER 
DROP INDEX uk_name_pwd;

注意：如果忘记名称，可以通过“show index from 表名称;”查看。

 

6.4 PRIMARY KEY 约束

PRIMARY KEY注意事项：

• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

• 如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

• 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。

• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。

• 当创建主键约束时，系统默认会在所在的列和列组合上建立对应的主键索引。删除主键时，也会直接删除主键索引。

  • 列级模式

  CREATE TABLE emp4(
  id INT AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(20)
  );
  

  • 表级模式

  CREATE TABLE emp5(
  id INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20),
  pwd VARCHAR(15),
  CONSTRAINT emp5_id_pk PRIMARY KEY(id)
  );
  

  • 组合模式

  CREATE TABLE emp6(
  id INT NOT NULL,
  NAME VARCHAR(20),
  pwd VARCHAR(15),
  CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
  );
  

• 删除主键约束

ALTER TABLE emp5
DROP PRIMARY KEY;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

• 添加主键约束

ALTER TABLE emp5
ADD PRIMARY KEY(NAME,pwd);

• 修改主键约束

ALTER TABLE emp5
MODIFY id INT PRIMARY KEY;

• 补充：关于自增长auto_increment的使用
  • 整数类型的字段才可以设置自增长
  • 当需要产生唯一标识符或顺序值时，可设置自增长
  • 一个表最多只能有一个自增长列
  • 自增长列必须非空（已经隐式满足了）
  • 自增长列必须声明有主键约束或唯一性约束
  • InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。

 

6.5 FOREIGN KEY 约束

departments表将locations主键值拿过来就是外键，用于两张表的连接。


基本理解：

• 外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

• 同一个表可以有多个外键约束

• 从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

• 注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

• 注意点：

  • 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
  • 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。
  • 删除外键时，关于外键列上的普通索引需要单独删除。
  • 还有一种就是级联删除子表数据。

• 创建外键约束

  • 主表

  CREATE TABLE dept(
  dept_id INT AUTO_INCREMENT PRIMARY KEY,
  dept_name VARCHAR(20)
  );
  

  • 从表

  CREATE TABLE emp(
  emp_id INT AUTO_INCREMENT PRIMARY KEY,
  last_name VARCHAR(15),
  dept_id INT,
  CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) 
  REFERENCES dept(dept_id)
  );
  

  – FOREIGN KEY: 在表级指定子表中的列

  – REFERENCES: 标示在父表中的列

• 创建多列外键组合，必须使用表级约束

  • 主表

  CREATE TABLE classes(
  id INT,
  NAME VARCHAR(20),
  number INT,
  PRIMARY KEY(NAME,number)
  );
  

  • 从表

  CREATE TABLE student(
  id INT AUTO_INCREMENT PRIMARY KEY,
  classes_name VARCHAR(20),
  classes_number INT,
  FOREIGN KEY(classes_name,classes_number) 
  REFERENCES classes(NAME,number)
  );
  

• 删除外键约束

ALTER TABLE emp
DROP FOREIGN KEY emp_dept_id_fk;

说明：外键列上的索引，需要单独删除。比如：

ALTER TABLE 表名称 DROP INDEX 外键列索引名; 
ALTER TABLE t_emp DROP INDEX dept_id; -- 举例

查看索引名：

show index from 表名称;

• 增加外键约束

ALTER TABLE emp
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) 
REFERENCES dept(dept_id);

• 关于删除操作：级联删除与级联置空
  • ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
  • ON DELETE SET NULL(级联置空): 当父表中的列被删除时，子表中相应的列的值置空

CREATE TABLE student( 
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20), 
classes_name VARCHAR(20),
classes_number INT, 
/*表级别联合外键*/ 
FOREIGN KEY(classes_name, classes_number) 
REFERENCES classes(NAME, number) ON DELETE CASCADE);

• 关于修改操作：级联修改与级联置空
  • **ON UPDATE CASCADE(级联修改)：**当父表中的列值由a修改为b时，子表中相对应的列值为a的改为值b
  • **ON UPDATE SET NULL(级联置空)：**子表中相应的列的值置空

CREATE TABLE student( 
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20), 
classes_name VARCHAR(20),
classes_number INT, 
/*表级别联合外键*/ 
FOREIGN KEY(classes_name, classes_number) 
REFERENCES classes(NAME, number) ON UPDATE CASCADE);

 

6.6 CHECK 约束

MySQL可以使用check约束，但check约束对数据验证没有任何作用,添加数据时，没有任何错误或警告

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

 

6.7 DEFAULT约束

• 一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

CREATE TABLE myemp(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 2000
);

---

  关于数据操作语言(DML)与数据定义语言(DDL)就讲到这了，还有剩下关于事务那块的内容，也是很重要滴，期待吧。欢迎大家一起交流学习哈。

上一篇：MySQL学习笔记_上(select查询)
下一篇：MySQL学习笔记_下(事务、视图、索引)