目录
DML
数据操纵语言DML有三种形式
INSERT——插入
语法：
INSERT INTO 表名(列名,...)
VALUES(值1,...);
方式一：经典插入1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','189888888',NULL,2)SELECT * FROM beauty;
2.可以为null的列如何插入值？
方式一： 用null表示该列值为空
INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','189888888',NULL,2)方式二：不需要插入的字段可以不写
INSERT INTO beauty(id,`name`,sex,borndate,phone,boyfriend_id)
VALUES(14,'金星','女','1990-4-23','189888888',9)案例
INSERT INTO beauty (id,`name`,sex,phone)VALUES (15,'娜扎','女','1888884')3.列的顺序是否可以调换
可以， 但要一一对应上
INSERT INTO beauty (`name`,sex,id,phone) VALUES ('梁小哲','女',16,'1888889')4.列数和值的个数必须一致
5. 可以省略列名、默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty VALUES (18,'张飞','男',NULL,'119',NULL,NULL);方式二：简单插入
语法：
insert into 表名 set 列名 =值，列名=值
INSERT INTO beauty SET id=19,`name`='刘涛',phone='999'
两种方式大pk1.方式一支持插入多行
INSERT INTO beauty VALUES(21,'张飞','男',NULL,'119',NULL,NULL),(
22,'张飞','男',NULL,'119',NULL,NULL),
(23,'张飞','男',NULL,'119',NULL,NULL)
SELECT * FROM beauty;2.方式一支持子查询，方式二不支持
INSERT INTO beauty (id,NAME,phone)
SELECT 26,'宋茜','19222';
UPDATE——修改
1.修改单表的记录 ☆
语法：
UPDATE 表 ①
SET 列=新值,列=新值,..... ③
WHERE 筛选条件; ②2.修改多表的记录【补充】
1.修改单行记录：
案例一.修改beauty表中姓唐的女神的电话为13899888899UPDATE beauty
SET phone ='13899888899'
WHERE `name` LIKE '唐%';案例2. 修改boys表中id号为2的名称为张飞。魅力值 10
UPDATE boys
SET boyName='张飞' ,userCP=10
WHERE id=2;2.修改多表的记录
SELECT * FROM beauty;
SELECT * FROM boys;
案例1： 修改张无忌的女朋友的手机号为114UPDATE boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
SET b.phone='114'
WHERE bo.boyName='张无忌';案例2： 修改没有男朋友的女神 的男朋友编号为张飞的编号
方法一：UPDATE beauty
SET boyfriend_id=(SELECT id FROM boys WHERE boyName='张飞')
WHERE id IS NULL
方法二：SELECT * FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.boyfriend_id is NULL;
DELETE——删除
方式一：delete
语法：
1.单表的删除 ☆
DELETE FROM 表名 WHERE 筛选条件2多表的删除【补充】.
sql92语法：
delete 表一的别名，表2的别名
FROM 表1 别名,表2 别名
WHERE 连接条件
AND 筛选条件sql99语法：
DELETE 表一的别名,表2的别名
FROM 表1 别名
INNER ||LEFT||RIGHT||JOIN 表2 别名 on 连接条件
WHERE 筛选条件；（二者区别还是在连接那里）
方式二：TRUNCATE
语法： TRUNCATE TABLE 表名;删除了全部数据
方式一：DELETE
1.单表的删除
案例一： 删除手机号以9结尾的女神信息DELETE FROM beauty
WHERE phone LIKE '%9';SELECT * FROM beauty;
2.多表的删除
案例：删除张无忌的女朋友的信息
DELETE b FROM beauty b
JOIN boys bo ON b.boyfriend_id=bo.id
WHERE bo.boyName='张无忌'案例:删除黄晓明的信息以及他女朋友的信息
SELECT * FROM boys bo
JOIN beauty b ON b.boyfriend_id=bo.idDELETE b,bo FROM boys bo
JOIN beauty b ON b.boyfriend_id=bo.id
WHERE bo.boyName='黄晓明'
方式二：truncate语句案例：将魅力值>100的男神信息删除
做不到。。。。
DELETE pk TRUNCATE （面试题）☆
1.DELETE 可以加where 条件,TRUNCATE 不能加
2.truncate 删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate在删除后再插入数据，自增长的列从1开始
4.TRUNCATE 删除没有返回值
delete 删除返回值是删除的行数
5.truncate删除不能回滚，delete删除可以回滚
综合案例
-- 1.运行以下脚本创建表
-- 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(10),
-- userid VARCHAR(10),
-- department_id INT(10)
--
-- )2.显示表my_employyes的结构
desc my_employees
3.向my_employees 表插入数据
方式一：
INSERT INTO my_employees
VALUES (1,'patel','Ralph','Rpatel',895),
(2,'Dance','Betty','Bdance',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,'Dance','Betty','Bdance',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550
4.向users表中插入数据
INSERT INTO users
VALUES
(1,'Rpatel',10),
(2,'Bdance',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40)
5.将3号员工的last_name 修改为'drelxer'UPDATE my_employees me SET me.last_name ='drelxer'
WHERE me.id=36.将所有工资少于900的员工的工资修改为1000
UPDATE my_employees me
SET me.Salary=1000 WHERE me.Salary<9007.将userid为Bbiri的USER表和my_employees表的记录全部删除
DELETE u,me FROM users u JOIN my_employees me ON u.userid=me.Userid
WHERE u.userid='Bbiri'8.删除所有数据
DELETE me,u FROM my_employees me JOIN users u ON me.Userid=u. userid9.检查所做的修正
SELECT * FROM my_employees;
SELECT * FROM users
10.清空表my_employees
TRUNCATE TABLE my_employees;
DDL
含义：数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE / VIEW / INDEX / SYN / CLUSTER| 表 视图 索引 同义词 簇。DDL操作是隐性提交的！不能rollback。
库的管理
创建
语法：
CREATE DATABASE (IF NOT EXISTS) 库名 ;
案例：创建库Books
CREATE DATABASE IF NOT EXISTS Books ;
修改
下面的语句不能用了，因为可能会导致数据不安全了
RENAME DATABASE books TO 新库名;更改库的字符集
ALTER DATABASE books CHARACTER set gbk;
如果非要修改库名的话 可以先关闭mysql服务 然后修改数据库的文件夹名，再重启数据库服务，但可能还是会有问题。
删除
DROP DATABASE if EXISTS books ;
表的管理
创建
CREATE TABLE 表名 (
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
...
列名 列的类型[(长度) 约束]
)案例：创建表Book
CREATE TABLE Book(
id INT, #编号
b_name VARCHAR(20), #图书名
price DOUBLE ,# 价格
author VARCHAR(20), #作者
authorId INT, #作者编号
publishDate DATETIME #出版日期
);案例： 创建表author
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
修改
语法；
ALTER TABLE 表名 ADD|DROP|CHANGE|MODIFY COLUMN 列名 【列类型 约束】
① 修改列名
COLUMN 可以省略
ALTER TABLE book CHANGE COLUMN publishDate pubDate datetime;
② 修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;③ 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE
④ 删除列
ALTER TABLE author DROP COLUMN annual
⑤ 修改表名
ALTER TABLE author RENAME TO book_author;
删除
DROP TABLE IF EXISTS book_author
复制
有时候我们需要的数据来自于另一张表。
1.仅仅复制表的结构
CREATE TABLE copy LIKE author;2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
3.只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';4.仅仅复制某些字段
#筛选想要的字段但是筛选条件是恒不成立的CREATE TABLE copy4
SELECT id ,au_name FROM author
WHERE 1=2SELECT * FROM copy4;
综合案例
1.创建表 dept1
CREATE TABLE dept1(
id INT(7),
name VARCHAR(25));
2.将表departments中的数据插入新表dept2 中
CREATE TABLE dept2
SELECT * FROM departments;
3. 创建表emp5
CREATE TABLE emp5(id int(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
)
4.将列last_name 的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name INT(50);
ALTER TABLE emp5 CHANGE COLUMN last_name last_name INT(50);DESC emp5;
5.根据表employees创建employees2
CREATE TABLE employees2
SELECT * FROM employees;
DELETE FROM employees2;6.删除表emp5
DROP TABLE emp5;7.将employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp58.在表dept和emp5中添加新列 test_column,并检查所作的操作
ALTER TABLE dept1 ADD COLUMN test_column VARCHAR(50)
ALTER TABLE myemployees.emp5 ADD COLUMN test_column VARCHAR(50)
DESC dept1
DESC myemployees.emp59.直接删除emp5表中的列 dept_id
desc myemployees.emp5ALTER TABLE myemployees.emp5 DROP COLUMN department_id;
相关知识点——常见数据类型
常见的数据类型
数值型：
整型
小数：
定点数
浮点数
字符型：
较短的文本：char varchar
较长的文本:text、blob(较长的二进制数据)
日期型：
一、整型
分类：
tinyint,SMALLINT,MEDIUMINT,INT/INTEGER,BIGINT
1 2 3 4 8 占用字节数特点：
①：如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要设置unsigned
②：插入的值不能超出范围，有的版本会报出警告并插入临界值，有的版本会直接报out of range异常
③：如果不设置长度，会有默认的长度，
这里的长度不是数据的长度，而是显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用
搭配之后就都是无符号的了
1.如何设置无符号
CREATE TABLE tab_int (
t1 INT(7) ZEROFILL,
t2 INT(7) ZEROFILL UNSIGNED
)
INSERT INTO tab_int VALUES (123,1);
二、小数
分类：
1.浮点型
FLOAT (M,D)
DOUBLE(M,D)2.定点型
DEC(M,D)
DECIMAL(M,D)特点：
①M和D
M:整数部位+小数部位一共的宽度
D：小数部位的宽度
如果超过范围，则报错~~~有的版本会插入临界值
②M和D 都可以省略
如果是decimal，则M默认为10，D默认为0
如果float和double，则会根据插入数值的精度来决定精度
③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用测试M和D
DROP TABLE tab_float
CREATE TABLE tab_float(
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL)
desc tab_float
SELECT * FROM tab_floatINSERT INTO tab_float VALUES(123.45,123.45,123.45)
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
INSERT INTO tab_float VALUES(123.4,123.4,123.4)
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4)
原则：
所选择的类型越简单越好，能保存数值的类型越小越好
三、字符型
较短的文本：
char
varchar
其他： binary和varbinary 用于保存较短的二进制
enum用于保存枚举 set用于保存集合
较长的文本：
text
blob(较大的二进制)
特点：
写法 M的意思 特点 空间的耗费
char char（M） 最大的字符数，可以省略，默认为 固定长度的字符 比较耗费varchar varchar(M) 最大的字符数，不可以省略 可变长度的字符 比较节省
变化较小的建议char 变化较大建议varchar
枚举类 ENUM
不区分大小写
CREATE TABLE tab_char(
c1 ENUM('A','B','C'))
INSERT INTO tab_char VALUES ('A');
INSERT INTO tab_char VALUES ('B');
INSERT INTO tab_char VALUES ('C');
INSERT INTO tab_char VALUES ('M'); #报错
INSERT INTO tab_char VALUES ('a');SELECT * FROM tab_char;
set类型
也不区分大小写
CREATE TABLE tab_set(
c1 SET('A','B','C'))
INSERT INTO tab_set VALUES ('a');
INSERT INTO tab_set VALUES ('a,b');
INSERT INTO tab_set VALUES ('a,b,c');SELECT * FROM tab_set;
四、日期型
分类：
date只保存日期
time只保存时间
year只保存年
datetime 保存日期加时间
timestamp 保存日期加时间特点；
字节 范围 时区等的影响
datetime 8 1000--9999 不受
TIMESTAMP 4 1970-2038 受
常见约束
含义：一种限制，用于限制表中的数据，为了保证最终添加的数据的一致性
分类： 六大约束
NOT NULL :非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段的值有默认值
比如性别
PRIMARY KEY :主键，用于保证该字段的值具有唯一性
比如学号。员工编号等
UNIQUE:唯一 ，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGNKEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如 学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：
1.创建表时
2.修改表时
但是都要是数据添加之前
约束的添加分类：
列级约束
六大约束语法上都支持，但外键约束没有效果！
表级约束
除了 非空，默认，其他的都支持
主键和唯一的大对比： （组合就是括号里放多个列，逗号隔开）
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多一个 √ 不推荐
唯一 √ √（只允许一个为空） 可以有多个 √ 不推荐
外键：
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型一致或者兼容，名称无要求
3.主表的关联列必须是一个key（一般是主键或唯一）
4.插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再掺入主表
一、创建表时添加约束
1.添加列级约束
语法：
直接在字段名和类型后面追加约束类型即可
只支持：DEFAULT, not NULL ,PRIMARY KEY ,UNIQUECREATE TABLE stuinfo(
id INT PRIMARY KEY ,#主键
stuName VARCHAR(20) NOT NULL,# 非空
gender CHAR(1) CHECK(gender='男'OR gender='女') , #检查 -不支持
seat INT UNIQUE, #唯一
age INT DEFAULT 18, #默认约束
majorid INT REFERENCES major(id) #外键 ---不支持
)DESC stuinfo;
查询stuinfo表中所有的索引，包括主键、外键
show INDEX FROM stuinfo;
CREATE TABLE major(
id int PRIMARY KEY,
majorname VARCHAR(20))
2.添加表级约束
语法：在各个字段的最下面
[CONSTRAINT 约束名] 约束类型（字段名）
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender char(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id), #主键
CONSTRAINT uq UNIQUE(seat), #唯一键
CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
)show INDEX FROM stuinfo;
desc stuinfo;通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo (
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
gender CHAR(1),
age INT DEFAULT 10,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
)
二、修改表时添加约束
（有没有想到DDL中表的管理相关语句）
1.添加列级约束 --对列操作
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;2.添加表级约束（有名字） ---对表操作
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（字段名） 外键的引用】
1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) not NULL;2.添加主键
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);#3.添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY (majorid) REFERENCES major(id);三、修改表时删除约束
——删除就用MODIFY 不要写那个约束就好
1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) ;
#2.删除主键
ALTER table stuinfo DROP PRIMARY key ;主键要用表级操作删除
3.删除唯一索引
show INDEX FROM stuinfo;
ALTER TABLE stuinfo DROP INDEX majorid;
4.删除外键
注意：删除唯一索引和外键时都要最后都是他们的索引名字 如果没有名字就找不到
要用 CONSTRAINT 设置索引名
ALTER TABLE stuinfo DROP FOREIGN KEY 索引名;
案例讲解
show TABLES
DROP TABLE dept2
DROP TABLE if EXISTS emp2CREATE TABLE emp2
SELECT * FROM employeesdesc emp2
CREATE table dep2
SELECT * FROM departments
desc dep2;1.向表emp2的id列中添加 PRIMARY key 约束(my_emp_id_pk)
ALTER TABLE emp2 MODIFY COLUMN id PRIMARY KEY ;# 列级约束这样没办法添加约束名，因此不满足题意
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(employee_id);
#表级约束可以添加
2.向表dep2的id列中添加 PRIMARY KEY 约束(my_dept_id_pk)
ALTER TABLE dep2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(department_id);
3.向表emp2中添加列dept_id,并在其中定义foreign key约束，与之相关联的列是dept2表中的id列ALTER TABLE emp2 ADD COLUMN dept_id INT(4);
ALTER TABLE emp2 ADD CONSTRAINT my_emp_depid_fk FOREIGN KEY(dept_id) REFERENCES dep2(department_id)
1163
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
