MySQL 数据库学习笔记（三）——DML数据操作语句

创建表

语法格式

// 语句
create table 表名 (字段1 数据类型,字段2 数据类型, 字段3 数据类型);

// 演示，创建user表
create table test (id INT, `name` VARCHAR(10), age INT, gender VARCHAR(2), birthday DATE);

// 演示，设置性别默认'男'，生日默认'2000-1-1'
create table test (id INT, `name` VARCHAR(10), gender VARCHAR(2) DEFAULT('男'), birthday DATE DEFAULT('2000-1-1'));

数据类型

类型	说明
int	整数型
bigint	长整型
float	浮点型
char	定长字符串
varchar	变长字符串
date	日期
BLOB	二进制大对象
CLOB	字符大对象

常用约束

• 非空约束(not null)：修饰的字段不能为null

// 若表已存在则删除
drop table if exists user;
// 建表
create table user(
	id int,
	account varchar(20) not null,
	password varchar(20) not null,
	name varchar(20),
	phone varchar(20),
);

• 唯一约束(unique)：修饰的字段唯一，但可以为null

// 若表已存在则删除
drop table if exists user;
// 建表，列级约束
create table user(
	id int,
	account varchar(20) unique,
	password varchar(20),
	name varchar(20),
	phone varchar(20) unique,
);

// 建表，表级约束(联合约束)
create table user(
	id int,
	account varchar(20),
	password varchar(20),
	name varchar(20),
	phone varchar(20),
	unique(account, phone)
);

• 主键约束(primary key)：修饰的字段唯一，且不能为null

表的设计三范式中，第一范式要求任何一张表都应该含有主键。一张表的主键约束只能有一个。
按字段数量分为：单一主键(推荐)、复合主键(不建议使用)
按主键性质分为：自然主键(推荐)、业务主键

// 若表已存在则删除
drop table if exists user;
// 建表，列级约束
create table user(
	id int primary key,
	account varchar(20),
	password varchar(20),
	name varchar(20),
	phone varchar(20),
);

// 建表，表级约束
create table user(
	id int,
	account varchar(20),
	password varchar(20),
	name varchar(20),
	phone varchar(20),
	primary key(id)
);

• 主键自增(primary key auto_increment)

// 若表已存在则删除
drop table if exists user;
// 建表
create table user(
	id int primary key auto_increment,
	account varchar(20),
	password varchar(20),
	name varchar(20),
	phone varchar(20),
);

• 外键约束(foreign key)：

// 若表已存在则删除
drop table if exists student;
drop table if exists class;
// 建class表
create table class(
	cno int,
	cname varchar(50),
	primary key(ano)
);
// 建student表
create table student(
	sno int,
	sname varchar(50),
	cno int,
	foreign key(con) references class(cno)
);

表的复制

• 将查询结果创建为新表

// 语句
create table 表名 as select语句;

// 演示，将部门编号为10的查询结果创建为新表
create table emp1 as select * from emp where deptno = 10;

• 将查询结果插入到表中

// 语句
insert into 表名 select语句;

// 演示，将部门编号为20的查询结果插入到表emp1
insert into emp1 select * from emp where deptno = 20;

插入数据

// 语句
insert into 表名(字段1,字段2, 字段3) values(值1, 值2, 值3);

// 演示，插入一条数据
insert into test(id, name, gender, birthday) values(1, '张三', '男', '1996-5-8');

// 演示，插入多条数据
insert into test(id, name, gender, birthday) values(2, '李莉', '女', '1996-6-13'),(3, '王五', '男', '1998-3-23');

// 演示，插入部分数据，其余数据使用默认值
insert into test (id, name) valuesvalues(4, '小华');

// 演示，不写字段直接插入
insert into test valuesvalues(5, '徐薇', '女', '1997-4-16');

修改数据

注意：若没有条件，整张表全部会被更新！

// 语句
update 表名 set 字段1=值1, 字段2=值2, 字段3=值3 where 条件;

// 演示，将员工smith的岗位改为ANALYST，薪水改为1000
update emp1 set job='ANALYST',sal=1000 where ename = 'SMITH';

删除数据

注意：若没有条件，整张表全部删除！

// 语句
delete from 表名 where 条件;

// 演示，删除部门编号为10的数据
delete from emp1 where deptno = 10;

// 演示，删除emp1表中所有数据
delete from emp1;

// 数据库较大时，使用截断truncate，不可回滚
truncate table emp1;

删除表

// 语句，通用
drop table 表名;

// 语句，MySQL支持，Oracle不支持
drop table if exists 表名;

关于表结构的修改

由于表结构是设计阶段定好的，如果需要修改，则会导致大量业务代码重写，因此很少用到。