Mysql核心(三)——表的操作
一、插入
一、方式一
语法:
insert into 表名(字段名,...) values(值,...);
特点:
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段,注意可以通过以下两种方式插入null值
①字段和值都省略
②字段写上,值使用null
4、字段和值的个数必须一致
5、字段名可以省略,默认所有列
二、方式二
语法:
insert into 表名 set 字段=值,字段=值,...;
两种方式 的区别:
1.方式一支持一次插入多行,语法如下:
insert into 表名【(字段名,..)】 values(值,..),(值,...),...;
2.方式一支持子查询,语法如下:
insert into 表名
查询语句;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #一、插入语句 2 #方式一:经典的插入 3 /* 4 语法: 5 insert into 表名(列名,...) values(值1,...); 6 */ 7 8 SELECT * FROM beauty; 9 #1.插入的值的类型要与列的类型一致或兼容 10 INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) 11 VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2); 12 13 #2.不可以为null的列必须插入值。可以为null的列如何插入值? 14 #方式一: 15 INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) 16 VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2); 17 #方式二: 18 INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id) 19 VALUES(14,'金星','女','1990-4-23','1898888888',NULL,9); 20 21 INSERT INTO beauty(id,NAME,sex,phone) 22 VALUES(15,'娜扎','女','1388888888'); 23 24 #3.列的顺序是否可以调换 25 INSERT INTO beauty(NAME,sex,id,phone) 26 VALUES('蒋欣','女',16,'110'); 27 28 #4.列数和值的个数必须一致 29 INSERT INTO beauty(NAME,sex,id,phone) 30 VALUES('关晓彤','女',17,'110'); 31 32 #5.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致 33 INSERT INTO beauty 34 VALUES(18,'张飞','男',NULL,'119',NULL,NULL); 35 #方式二: 36 /* 37 语法: 38 insert into 表名 39 set 列名=值,列名=值,... 40 */ 41 42 INSERT INTO beauty 43 SET id=19,NAME='刘涛',phone='999'; 44 45 #两种方式大pk ★ 46 47 #1、方式一支持插入多行,方式二不支持 48 INSERT INTO beauty 49 VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2) 50 ,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2) 51 ,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2); 52 53 #2、方式一支持子查询,方式二不支持 54 #把查询的结果集插入到表中 55 INSERT INTO beauty(id,NAME,phone) 56 SELECT 26,'宋茜','11809866'; 57 58 INSERT INTO beauty(id,NAME,phone) 59 SELECT id,boyname,'1234567' 60 FROM boys WHERE id<3;
二、修改
一、修改单表的记录 ★
语法:update 表名 set 字段=值,字段=值 【where 筛选条件】;
二、修改多表的记录【补充】
语法:
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #二、修改语句 2 /* 3 1.修改单表的记录★ 4 语法: 5 update 表名 6 set 列=新值,列=新值,... 7 where 筛选条件; 8 2.修改多表的记录【补充】 9 语法: 10 sql92语法: 11 update 表1 别名,表2 别名 12 set 列=值,... 13 where 连接条件 14 and 筛选条件; 15 sql99语法: 16 update 表1 别名 17 inner|left|right join 表2 别名 18 on 连接条件 19 set 列=值,... 20 where 筛选条件; 21 22 */ 23 24 #1.修改单表的记录 25 #案例1:修改beauty表中姓唐的女神的电话为13899888899 26 UPDATE beauty SET phone = '13899888899' 27 WHERE NAME LIKE '唐%'; 28 #案例2:修改boys表中id好为2的名称为张飞,魅力值 10 29 UPDATE boys SET boyname='张飞',usercp=10 30 WHERE id=2; 31 32 33 #2.修改多表的记录 34 #案例 1:修改张无忌的女朋友的手机号为114 35 UPDATE boys bo 36 INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id` 37 SET b.`phone`='119',bo.`userCP`=1000 38 WHERE bo.`boyName`='张无忌'; 39 40 41 #案例2:修改没有男朋友的女神的男朋友编号都为2号 42 UPDATE boys bo 43 RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id` 44 SET b.`boyfriend_id`=2 45 WHERE bo.`id` IS NULL; 46 SELECT * FROM boys;
三、删除
方式一:使用delete
一、删除单表的记录★
语法:delete from 表名 【where 筛选条件】【limit 条目数】
二、级联删除[补充]
语法:
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
方式二:使用truncate
语法:truncate table 表名
两种方式的区别【面试题】★
1.truncate删除后,如果再插入,标识列从1开始
delete删除后,如果再插入,标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #三、删除语句 2 /* 3 方式一:delete 4 语法: 5 1、单表的删除【★】 6 delete from 表名 where 筛选条件 7 2、多表的删除【补充】 8 sql92语法: 9 delete 表1的别名,表2的别名 10 from 表1 别名,表2 别名 11 where 连接条件 12 and 筛选条件; 13 sql99语法: 14 delete 表1的别名,表2的别名 15 from 表1 别名 16 inner|left|right join 表2 别名 on 连接条件 17 where 筛选条件; 18 19 20 方式二:truncate 21 语法:truncate table 表名; 22 */ 23 #方式一:delete 24 #1.单表的删除 25 #案例:删除手机号以9结尾的女神信息 26 DELETE FROM beauty WHERE phone LIKE '%9'; 27 SELECT * FROM beauty; 28 29 #2.多表的删除 30 #案例:删除张无忌的女朋友的信息 31 DELETE b 32 FROM beauty b 33 INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id` 34 WHERE bo.`boyName`='张无忌'; 35 36 #案例:删除黄晓明的信息以及他女朋友的信息 37 DELETE b,bo 38 FROM beauty b 39 INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id` 40 WHERE bo.`boyName`='黄晓明'; 41 42 43 #方式二:truncate语句 44 #案例:将魅力值>100的男神信息删除 45 TRUNCATE TABLE boys ; 46 47 48 #delete pk truncate【面试题★】 49 /* 50 1.delete 可以加where 条件,truncate不能加 51 2.truncate删除,效率高一丢丢 52 3.假如要删除的表中有自增长列, 53 如果用delete删除后,再插入数据,自增长列的值从断点开始, 54 而truncate删除后,再插入数据,自增长列的值从1开始。 55 4.truncate删除没有返回值,delete删除有返回值 56 5.truncate删除不能回滚,delete删除可以回滚. 57 */ 58 SELECT * FROM boys; 59 DELETE FROM boys; 60 TRUNCATE TABLE boys; 61 INSERT INTO boys (boyname,usercp) 62 VALUES('张飞',100),('刘备',100),('关云长',100);
[练习]数据的增删改
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #1. 运行以下脚本创建表my_employees 2 USE myemployees; 3 CREATE TABLE my_employees( 4 Id INT(10), 5 First_name VARCHAR(10), 6 Last_name VARCHAR(10), 7 Userid VARCHAR(10), 8 Salary DOUBLE(10,2) 9 ); 10 CREATE TABLE users( 11 id INT, 12 userid VARCHAR(10), 13 department_id INT 14 ); 15 #2. 显示表my_employees的结构 16 DESC my_employees; 17 #3. 向my_employees表中插入下列数据 18 ID FIRST_NAME LAST_NAME USERID SALARY 19 1 patel Ralph Rpatel 895 20 2 Dancs Betty Bdancs 860 21 3 Biri Ben Bbiri 1100 22 4 Newman Chad Cnewman 750 23 5 Ropeburn Audrey Aropebur 1550 24 #方式一: 25 INSERT INTO my_employees 26 VALUES(1,'patel','Ralph','Rpatel',895), 27 (2,'Dancs','Betty','Bdancs',860), 28 (3,'Biri','Ben','Bbiri',1100), 29 (4,'Newman','Chad','Cnewman',750), 30 (5,'Ropeburn','Audrey','Aropebur',1550); 31 DELETE FROM my_employees; 32 #方式二: 33 INSERT INTO my_employees 34 SELECT 1,'patel','Ralph','Rpatel',895 UNION 35 SELECT 2,'Dancs','Betty','Bdancs',860 UNION 36 SELECT 3,'Biri','Ben','Bbiri',1100 UNION 37 SELECT 4,'Newman','Chad','Cnewman',750 UNION 38 SELECT 5,'Ropeburn','Audrey','Aropebur',1550; 39 40 #4. 向users表中插入数据 41 1 Rpatel 10 42 2 Bdancs 10 43 3 Bbiri 20 44 4 Cnewman 30 45 5 Aropebur 40 46 INSERT INTO users 47 VALUES(1,'Rpatel',10), 48 (2,'Bdancs',10), 49 (3,'Bbiri',20); 50 51 52 53 #5.将3号员工的last_name修改为“drelxer” 54 UPDATE my_employees SET last_name='drelxer' WHERE id = 3; 55 56 57 #6.将所有工资少于900的员工的工资修改为1000 58 UPDATE my_employees SET salary=1000 WHERE salary<900; 59 60 #7.将userid 为Bbiri的user表和my_employees表的记录全部删除 61 DELETE u,e 62 FROM users u 63 JOIN my_employees e ON u.`userid`=e.`Userid` 64 WHERE u.`userid`='Bbiri'; 65 #8.删除所有数据 66 DELETE FROM my_employees; 67 DELETE FROM users; 68 #9.检查所作的修正 69 SELECT * FROM my_employees; 70 SELECT * FROM users; 71 #10.清空表my_employees 72 TRUNCATE TABLE my_employees;