《SQL高级应用和数据仓库基础（MySQL版）》学习笔记 ·005【表的CRUD操作（DML语句）】

一、表的CRUD操作（DML语句）

1、insert语句

格式

-- 插入单条记录
insert into 表名 [列名列表]
values(record_value1, record_value2, ..., record_valuen);

-- 插入多条记录
insert into 表名 [列名列表]
values
	(record_value1, record_value2, ..., record_valuen),
	(record_value1, record_value2, ..., record_valuen),
	...
	(record_value1, record_value2, ..., record_valuen);

-- 通过子查询插入记录
insert into 表名 [列名列表]
子查询;

• 注：对于【含空字段】、【带有默认值的字段】、【自增字段】，在insert的时候可以不指定其值

举例

create table student(
	sno int primary key auto_increment,
	sname varchar(20) not null,
	gender enum('男', '女') default '男',
	birthday date,
	score int
);

insert into student(sno, sname, gender, score, birthday)
values(1, 'LILY', '女', 89, '1989-09-08');

insert into student
values(2, 'JIN', default, '1999-10-01', 90);

insert into student(sname)
values('LIUTAO');

insert into student
values(NULL, 'JIM', default, now(), 90);

insert into student
values(NULL, 'JIM2', default, sysdate(), 90);

insert into student(sname, score, birthday)
values
	('韩梅梅', 90, now()),
	('李四', 78, '1999/09/10'),
	('张三', 88, '1988-10-01');

insert into student(sname, gender, birthday, score)
select sname, gender, birthday, score from student;

2、update语句

格式

update 表名
set 列1=值1 [, 列2=值2, ..., 列n=值n]
[where 条件表达式];

举例

update dept10
set sal=1000;

update dept10
set sal=1500
where empno=7782;

update dept10
set sal=1500, deptno=20
where empno=7782;

3、delete语句

格式

delete from 表名
[where 条件表达式];

举例

-- 1、删除经理编号为7566的员工记录
delete from emp
where mgr=7566;

-- 2、删除工作在NEW YORK的员工记录
delete from emp
where deptno in (select deptno from dept where loc='NEW YORK');

-- 3、删除工资大于所在部门平均工资的员工记录
delete from emp e1
where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno);

二、其他注意点

1、MySQL查询区分大小写的方式

方法一：查询时加上binary

select * from dept
where binary loc='New York';

方法二：创表时指定校对集

可以对需要修改的字段设置好校对集（被设置的字段区分大小写）

create table test11(
	name varchar(20) character set utf8 collate utf8_bin,
	job varchar(20)
);

insert into test11
values
	('LILY', 'CLERK'),
	('JIM', 'HR');

/* 字段name区分大小写 */
select * from test11
where name='Lily';

/* 字段job不区分大小写 */
select * from test11
where job='hR';

可以对整张表设置好校对集（整张表的字段区分大小写）

create table test11(
	name varchar(20),
	job varchar(20)
)character set utf8 collate utf8_bin;

insert into test11
values
	('LILY', 'CLERK'),
	('JIM', 'HR');

/* 字段name区分大小写 */
select * from test11
where name='Lily';

/* 字段job区分大小写 */
select * from test11
where job='hR';

方法三：设置MySQL系统变量lower_case_table_names的值为0

可以通过以下SQL语句观察值

select @@lower_case_table_names;

如果要临时修改，需要在命令行里修改该变量的值
如果要永久修改，需要在配置文件里修改该变量的值

2、truncate语句与delete语句的区别

truncate语句

truncate table 表名;

truncate	delete
DDL语句	DML语句
只能删除表中所有记录	可以删除表中指定记录
释放存储空间	不释放存储空间
不可以用rollback回滚	可以用rollback回滚