1.DML剩余内容
#方式二
/*
insert into 表名
set 列名=值,列名=值
*/
#1.
INSERT INTO beauty
SET id=19,NAME='koko',phone='000';
#两种方式对比
#方式一:支持一次插入多行,方式二不支持
INSERT INTO beauty()
VALUES(20,'koko2','女',NULL,'19999',NULL,3),
(21,'koko3','女',NULL,'19999',NULL,3),
(22,'koko4','女',NULL,'19999',NULL,3);
#方式一支持子查询,方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'lplp','1111';
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'213123123'
FROM boys WHERE id<3;
#二、修改语句
/*
1、修改单表的记录
语法:
update 表名
set 列=新值,列=新值,、、、
where 筛选条件;
2、修改多表的记录
语法:
sql92
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
sql99
update 表1 别名 inner|left|right join 表2 别名
on 连接条件
set 列= 值,。。。
where 筛选条件;
*/
#1、修改单表的记录
#案例1:修改beauty表中姓k的女生的电话为13899888899;
UPDATE beauty
SET phone = '13899888899'
WHERE NAME LIKE 'k%';
SELECT * FROM boys;
#案例2:修改boys表中ID号为2的名称为大喵喵,魅力值为10
UPDATE boys
SET boyName='大喵喵',userCP=10
WHERE id=2;
#2.修改多表的记录
#案例1:把张无忌的女朋友的手机号修改为114
UPDATE beauty AS b1 INNER JOIN boys AS b2
ON b1.`boyfriend_id`=b2.`id`
SET phone ='114'
WHERE b2.`boyName`='张无忌';
#案例2:修改没有男朋友的女生的男朋友的编号都为2号
UPDATE beauty AS b1 LEFT JOIN boys AS b2
ON b1.`boyfriend_id`=b2.`id`
SET b1.`boyfriend_id`=2
WHERE b2.`boyName` IS NULL;
#三、删除语句
/*
方式一: delete
语法:
1.单表的删除
delete from 表名 where 筛选条件
2.多表的删除
sql92:delete 表1的别名或表2的别名
from 表1, 别名,表2 别名
where 连接条件
and 筛选条件;
sql99:delete 表1的别名或表2的别名
from 表1,别名 inner|left| 表2 别名
on 连接条件
where 筛选条件;
方式二:truncate
语法: truncate table 表名;
*/
#方式一:delete
#1.单表的删除
#案例1:删除手机号以9结尾的女生信息
DELETE FROM beauty
WHERE phone LIKE '%9';
#2.多表的删除
#案例1:删除张无忌女朋友的信息
DELETE b1
FROM beauty AS b1 INNER JOIN boys AS b2
ON b1.boyfriend_id = b2.id
WHERE b2.boyname='张无忌';
#案例2,黄晓明的信息以及他女朋友的信息
DELETE b1,b2
FROM beauty AS b1 INNER JOIN boys AS b2
ON b1.boyfriend_id = b2.id
WHERE b2.boyname='黄晓明';
#方式二;truncate语句,清空数据,不加WHERE
#案例:将魅力值大于100的男生信息删除
报错 TRUNCATE TABLE boys WHERE usercp>100;
#delete和truncate【面试题】
/*
1.delete可以加条件,truncate不能加
2.truncate效率更高
3.假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长的值从断点开始
而truncate删除后,再插入数据,自增长列的值从1开始
4.truncate没有返回值,delete有返回值
5.truncate删除不能回滚,delete删除可以回滚
*/
SELECT * FROM boys;
DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys(boyname,usercp)
VALUES('张飞',100),('刘备',200),('关羽',150);
#练习
#练习1.运行以下脚本创建表my_employees
USE myemployees;
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
);
#练习2. 显示表my_employees的结构
DESC my_employees;
#练习3. 向my_employees表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
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
SELECT * FROM my_employees;
#方式一
INSERT INTO my_employees()
VALUES(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);
#方式二,联合查询+子查询
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;
TRUNCATE TABLE my_employees;
#练习4. 向users表中插入数据
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur40
#方式一
INSERT INTO users()
SELECT 1,'Rpatel',10 UNION
SELECT 2,'Bdancs',10 UNION
SELECT 3,'Bbiri',20 UNION
SELECT 4,'Cnewman',30 UNION
SELECT 5,'Aropebur',40;
SELECT * FROM users;
TRUNCATE TABLE users;
#方式二
INSERT INTO users
VALUES(1,'Rpatel',10),(2,'Bdancs',10),(3,'Bbiri',20),
(4,'Cnewman',30),(5,'Aropebur',40);
#练习5:将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name='drelxer'
WHERE id=3;
#练习6:将所有工资少于900的员工修改为1000
UPDATE my_employees
SET salary=1000
WHERE salary<900;
#练习7:将userid为Bbiri的user表和my_employee表的记录全部删除
DELETE u,m
FROM users AS u INNER JOIN my_employees AS m
ON u.`userid`=m.`Userid`
WHERE u.`userid`='Bbiri';
#练习8:删除所有数据
DELETE FROM users;
DELETE FROM my_employees;
#练习9:清空my_employee
TRUNCATE TABLE my_employees;
2.DDL初步
#DDL data define language
/*
数据定义语言
库和表的(结构)管理
一、库的管理
创建、修改、删除
二、表的管理
创建:create
修改:alter
删除:drop
*/
#一、库的管理
#1、库的创建
/*
语法:create database if not exists
*/
#案例:创建库Books
CREATE DATABASE IF NOT EXISTS books;
#2.库的修改
RENAME DATABASE books TO 新库名;
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#3.库的删除
DROP DATABASE IF EXISTS books;
3.明日计划DDL剩余内容