一、新增数据
(1)需求:往数据表中新增一条数据
语法:insert into 数据表名(列名1,列名2,......,列名n) values (数据值1,数据值2,......,数据值n);
代码:insert into student666(id,name,age) values (1,"张三",18);
注意:
1.列名和数据值要一一对应
2.如果往数据表中所有列进行数据值的添加,将(列名)进行省略,格式如下:- 语法:insert into 数据表名 values (数据值1,数据值2,......,数据值n);
- 代码:insert into student666 values (2,"李四",18);
二、删除数据
(1)需求:删除数据表中的数据
语法:delete from 数据表名;
代码:delete from student666;
(2)需求:删除数据表中指定条目的数据
语法:delete from 数据表名 where 列名 = 数据值;
代码:delete from student666 where id = 1;
注意:删除指定数据表中的所有数据语法有两种
- 第一种:delete from 数据表名
- 第二种:truncate table 数据表名
- 区别:二者从结果看,是一样的,都可以将数据表中所有的数据进行删除;但过程不一样,
- delete方式是逐条进行删除,效率低;
- truncate方式先进行数据表的删除,然后创建一张一样的表
三、修改数据
(1)需求:修改数据表中指定列的所有数据
语法:update 数据表名 set 列名1 = 数据值1, 列名1 = 数据值1, ......, 列名n = 数据值n;
代码:update student666 set age = 16;
(2)需求:修改数据表中指定条目的指定列数据
语法:update 数据表名 set 列名1 = 数据值1, 列名1 = 数据值1, ......, 列名n = 数据值n where 列名 = 数据值;
代码:update student666 set age = 18 where id = 3;
具体案例演示
1-添加语句
1)添加一条记录到某个表中
insert into 表名称 values(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应
mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid | int(11) | YES | | NULL | |
| tname | varchar(5) | YES | | NULL | |
| salary | double | YES | | NULL | |
| weight | double | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| blood | enum('A','B','AB','O') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
insert into teacher values(1,'张三',15000,120.5,'1990-5-1','男','O','13789586859');
insert into teacher values(2,'李四',15000,'1990-5-1','男','O','13789586859'); #缺体重weight的值
ERROR 1136 (21S01): Column(列) count(数量) doesn't match(不匹配) value(值) count(数量) at row 1
2)添加一条记录到某个表中
insert into 表名称 (字段列表) values(值列表); #值列表中的值的顺序、类型、个数必须与(字段列表)一一对应
insert into teacher(tid,tname,salary,phone) values(3,'王五',16000,'15789546586');
3)添加多条记录到某个表中
insert into 表名称 values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应
insert into 表名称 (字段列表) values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与(字段列表)一一对应
insert into teacher (tid,tname,salary,phone)
values(4,'赵六',16000,'15789546586'),
(5,'汪飞',18000,'15789548886'),
(6,'天琪',19000,'15909546586');
总结
#演示基本的,简单的DML语句
#基于tempdb数据库演示
create database tempdb;
use tempdb;
#创建teacher表
create table teacher(
id int,
name varchar(20),
gender enum('m','f'),
birthday date,
salary double,
tel varchar(11)
);
#查看teacher表结构
mysql> desc teacher;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | double | YES | | NULL | |
| tel | char(18) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
#添加数据
#(1)第一种情况,给所有字段赋值
insert into 表名称 values(值列表);
#这种情况要求(值列表)的每一个值的类型、顺序与表结构一一对应
#表中有几个字段,(值列表)必须有几个值,不能多也不能少
#值如果是字符串或日期类型,需要加单引号
#例如:添加一条记录到teacher表
insert into teacher values
(1,'张三','m','1998-7-8',15000.0,'18256953685');
#例如:添加一条记录到teacher表
insert into teacher values
(2,'李四','f','1998-7-8',15000.0); #少了电话号码
mysql> insert into teacher values
-> (2,'李四','f','1998-7-8',15000.0);
ERROR 1136 (21S01): Column count doesn't match value count at row 1'
#(值列表)中值的数量和表结构中column列的数量不一致。
#例如:添加一条记录到teacher表
insert into teacher values
(2,'李四','f','北京宏福苑',15000.0,'18256953685'); #把生日写称为地址
mysql> insert into teacher values
-> (2,'李四','f','北京宏福苑',15000.0,'18256953685');
ERROR 1292 (22007): Incorrect date value: '北京宏福苑' for column 'birthday' at row 1
#日期格式不对
#(2)第二种情况,给部分字段赋值
insert into 表名称 (部分字段列表) values(值列表);
#此时(值列表)中的值的数量、格式、顺序与(部分字段列表)对应即可
#例如:添加一条记录到teacher表,只给id和name字段赋值
insert into teacher (id,name) values (2,'李四');
mysql> select * from teacher;
+------+------+--------+------------+--------+-------------+
| id | name | gender | birthday | salary | tel |
+------+------+--------+------------+--------+-------------+
| 1 | 张三 | m | 1998-07-08 | 15000 | 18256953685 |
| 2 | 李四 | NULL | NULL | NULL | NULL |
+------+------+--------+------------+--------+-------------+
2 rows in set (0.00 sec)
#没有赋值的字段都是默认值,此时默认值是NULL
#这种情况,当某个字段设置了“非空NOT NULL”约束,又没有提前指定“默认值”,
#那么在添加时没有赋值的话,会报错。明天演示非空约束。
#(3)一次添加多条记录
insert into 表名称 values(值列表1),(值列表2)...;
insert into 表名称 (部分字段列表) values(值列表),(值列表2)...;
#上面一个insert语句有几个(值列表)就表示添加几行记录。
#每一个值列表直接使用逗号分隔
#添加多条记录到teacher表
insert into teacher (id,name) values
(3,'王五'),
(4,'宋鑫'),
(5,'赵志浩'),
(6,'杨业行'),
(7,'牛钰琪');
#查看数据
mysql> select * from teacher;
+------+--------+--------+------------+--------+-------------+
| id | name | gender | birthday | salary | tel |
+------+--------+--------+------------+--------+-------------+
| 1 | 张三 | m | 1998-07-08 | 15000 | 18256953685 |
| 2 | 李四 | NULL | NULL | NULL | NULL |
| 3 | 王五 | NULL | NULL | NULL | NULL |
| 4 | 宋鑫 | NULL | NULL | NULL | NULL |
| 5 | 赵志浩 | NULL | NULL | NULL | NULL |
| 6 | 杨业行 | NULL | NULL | NULL | NULL |
| 7 | 牛钰琪 | NULL | NULL | NULL | NULL |
+------+--------+--------+------------+--------+-------------+
7 rows in set (0.00 sec)
2-修改语句
1)修改所有行
update 表名称 set 字段名 = 值, 字段名 = 值; #给所有行修改
#修改所有人的薪资,都涨了1000
update teacher set salary = salary + 1000 ;
2)修改部分行
update 表名称 set 字段名 = 值, 字段名 = 值 where 条件; #给满足条件的行修改
#修改天琪的薪资降低5000
update teacher set salary = salary-5000 where tname = '天琪';
3-删除语句
1)删除部分行数据
delete from 表名称 where 条件;
delete from teacher where tname = '天琪';
2)删除整张表数据,但表结构留下
delete from 表名称;
delete from teacher where tname = '天琪';
3)截断表,清空表中的数据,只有表结构
truncate 表名称;
truncate teacher;
truncate表和delete表的区别:
delete是一条一条删除记录的。如果在事务中,事务提交之前支持回滚。(后面会讲事务)
truncate是把整个表drop,新建一张,效率更高。就算在事务中,也无法回滚。
【几个问题】
#同学问:是否可以删除salary字段的值,字段留着,值删掉
#可以实现,但是不是用delete,用update#同学问:是否可以删除salary字段,连同字段和这个字段的数据都是删除
#可以实现,但是不是用delete,用alter table 表名称 drop column 字段名;#同学问:只删除某个单元格的值
#可以实现,但是不是用delete,用update
4-UPDATE中嵌套子查询
#子查询也可以嵌套在update语句中
#(1)修改“t_employee”表中部门编号(did)和
#“测试部”部门编号(did)相同的员工薪资为原来薪资的1.5倍。
UPDATE t_employee
SET salary = salary * 1.5
WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');
#(2)修改“t_employee”表中did为NULL的员工信息,
#将他们的did值修改为“测试部”的部门编号。
#子查询select did from t_department where dname = '测试部'
#这种子查询必须是单个值,否则无法赋值
UPDATE t_employee
SET did = (SELECT did FROM t_department WHERE dname = '测试部')
WHERE did IS NULL;
#(3)修改“t_employee”表中“李冰冰”的薪资值等于“孙红梅”的薪资值。
#这里使用子查询先在“t_employee”表中查询出“孙红梅”的薪资。
#select salary from t_employee where ename = '孙红梅';
UPDATE t_employee
SET salary = (SELECT salary FROM t_employee WHERE ename = '孙红梅')
WHERE ename = '李冰冰';
#You can't specify target table 't_employee' for update in FROM clause'
UPDATE t_employee
SET salary = (SELECT salary FROM(SELECT salary FROM t_employee WHERE ename = '孙红梅')temp)
WHERE ename = '李冰冰';
#当update的表和子查询的表是同一个表时,需要将子查询的结果用临时表的方式表示
#即再套一层子查询,使得update和最外层的子查询不是同一张表
#(4)修改“t_employee”表“李冰冰”的薪资与她所在部门的平均薪资一样。
#子查询,查询李冰冰的部门编号
#select did from t_employee where ename = '李冰冰';
#子查询第二层,查询李冰冰所在部门的平均薪资
#select avg(salary) from t_employee where did = (select did from t_employee where ename = '李冰冰');
#子查询第三层,把第二层的子查询结果当成临时表再查一下结果
#目的使得和外层的update不是同一张表
SELECT pingjun FROM (SELECT AVG(salary) pingjun FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李冰冰') temp)
#update更新
UPDATE t_employee
SET salary =
(SELECT pingjun FROM
(SELECT AVG(salary) pingjun FROM t_employee WHERE did =
(SELECT did FROM t_employee WHERE ename = '李冰冰') ) temp)
WHERE ename = '李冰冰';
5-DELETE中嵌套子查询
#delete语句中也可以嵌套子查询
#(1)从“t_employee”表中删除“测试部”的员工记录。
DELETE FROM t_employee
WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');
#(2)从“t_employee”表中删除和“李冰冰”同一个部门的员工记录。
#子查询 “李冰冰”的部门编号
#select did from t_employee where ename = '李冰冰';
DELETE FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李冰冰');
#You can't specify target table 't_employee' for update in FROM clause'
#删除和子查询是同一张表
DELETE FROM t_employee WHERE did = (SELECT did FROM (SELECT did FROM t_employee WHERE ename = '李冰冰')temp);
6-使用子查询复制表结构和数据
#演示通过子查询复制表,
#(1)复制表结构
#(2)复制一条或多条记录
#(3)同时复制表结构和记录
#仅仅是复制表结构,可以用create语句
CREATE TABLE department LIKE t_department;
#使用INSERT语句+子查询,复制数据,此时INSERT不用写values
INSERT INTO department (SELECT * FROM t_department WHERE did<=3);
#同时复制表结构+数据
CREATE TABLE d_department AS (SELECT * FROM t_department);
#如果select后面是部分字段,复制的新表就只有这一部分字段