#DML语言:数据操作语言
/*
插入:insert
修改:update
删除:delete*/
#一、插入语句
#方式1:经典
/*
语法:insert into 表名(列名...) values(值1...)
*/
#1、插入值的类型与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'卞怡倩','女','1998-10-5','123456',NULL,2);SELECT * FROM beauty;
#2、不可以为null的列必须插入值,可以为null的列:
#方式1:null
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'卞怡倩','女','1998-10-5','123456',NULL,2);
#方式2:
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES(14,'byq','女','1998-8-5','1234567',9);INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','12345678');#3、顺序可调换:✔
#4、列数和值的个数必须一致
#5、可以省略列名,默认所有列,且顺序不变
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
SELECT * FROM beauty;#方式2:
/*
insert into 表名
set 列名 = 值,列名 = 值,,,*/
INSERT INTO beauty
SET id = '19',NAME = 'lt',phone = '1829';
/*
solo:
方式1支持插入多行,方式2不支持
方式1支持子查询,方式二不支持
*/
子查询:
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','1273';INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'123456'
FROM boys WHERE id<3;SELECT * FROM boys;
SELECT * FROM beauty;#二、修改语句(单表、多表)
/*
单表:
update 表名
set 列 = 新值,,,,
where 筛选条件多表:
sql92:
udate 表1 别名 ,,,
set 列=值,,
where 连接条件
and筛选条件sql99:
udate 表1 别名
inner/left/right join 表2 别名
on连接条件
set 列 = 值
where 筛选条件
*/#1、修改单表的记录
#案例1:修改beauty表中姓卞的女神的电话为139
UPDATE beauty SET phone = '139'
WHERE NAME LIKE '卞%';
SELECT * FROM beauty;#案例2:修改boys表中id为2的名称为张飞,魅力值为10
UPDATE boys SET boyname = '张飞',usercp = 10
WHERE id = 2;
SELECT * FROM boys;#修改多表:
#案例1:修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
SET b.`phone` = '114'
WHERE bo.`boyName` = '张无忌';#案例2:修改没有男朋友的女神的男朋友的编号为2
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id` = b.`boyfriend_id`
SET b.`boyfriend_id` = '2'
WHERE bo.`id` IS NULL;#三、删除语句
/*
方式一、delete
语法:
1、单表删除
delete from 表名 where 筛选条件
2、多表删除【补充】
sql92:
delete 别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;sql99:
delete 别名
from 表1 别名
inner left right join 表2 别名 on连接条件
where 筛选条件;
方式二、truncate
语法:truncate tabel 表名;*/
#方式一:delete
#1、单表删除
#案例:删除手机号9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;#2、多表删除
#案例:删除张无忌的女朋友信息
DELETE b
FROM beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';#案例:删除黄晓明的信息和女友信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName` = '黄晓明';SELECT * FROM boys;
#方式二、truncate
#案例:将魅力值大于100的男神信息删除
TRUNCATE TABLE boys;/*
solo:
1、delete可以加where,trun不可以
2、trun删除效率更高3、如果要删除的表中有自增长列,
如果用delete删除后再插入数据,自增长列的值从断点开始
如果用truncate删除再插入数据,自增长列的值从1开始4、truncate删除不能回滚,delete可以回滚
*/#运行以下脚本创建表my_employees
CREATE TABLE my_employees(
Id INT(10),
first_name VARCHAR(10),
last_name VARCHAR(10),
userid VARCHAR(10),
salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);#显示表my_employees的结构
DESC my_employees;#向my_empl中插入数据
#方式一
INSERT INTO my_employees
VALUE(1,'patel','ralph','rpatel',895),
(2,'dancs','betty','bdancs',860),
(3,'biri','ben','bbiri',1100),
(4,'newman','chad','cnewman',750),
(5,'ropeburn','audrey','aropebur',1550);
DELETE FROM my_employees;
#方式二
INSERT INTO my_employees
SELECT 1,'patel','ralph','rpatel',895 UNION
SELECT 2,'dancs','betty','bdancs',860 UNION
SELECT 3,'biri','ben','bbiri',1100 UNION
SELECT 4,'newman','chad','cnewman',750 UNION
SELECT 5,'ropeburn','audrey','aropebur',1550;#user中插入
INSERT INTO users
VALUES(1,'rpate',10),
(2,'bdancs',10),
(3,'bbiri',20),
(4,'cnewman',30),
(5,'aropebur',40);
DELETE FROM users;#将3好员工last——name修改
UPDATE my_employees SET last_name = 'drelxer' WHERE id = 3;#将所有工资小于900的员工工资修改为1000
UPDATE my_employees SET salary =1000 WHERE salary <900;#将userid为bbiri的user表和my-employees表的记录全部删除
DELETE u,m
FROM users u
JOIN my_employees m
ON u.`userid` = m.`userid`
WHERE u.userid = 'bbiri';#删除所有数据
DELETE FROM my_employees;
DELETE FROM users;#检查所有的修正
SELECT * FROM my_employees;
SELECT * FROM users;
#清空表My——empl
TRUNCATE TABLE my_employees;
DML语言
最新推荐文章于 2023-06-24 21:33:35 发布