数据库第四天
进阶9：联合查询
union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：
要查的结果来自多个表，且多个表没有直接点连接关系，但查询的信息一致时
特点：
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好是一致的
3.union关键字默认是去重的 如果使用union可以包括重复项
引入案例：查询部门编号>90或又想包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
案例：查询中国用户中年龄>12岁的用户信息
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';
DML语言
数据操纵语言
插入:insert
修改：update
删除：delete
一、插入语句
语法：
insert into 表名(列名,...) value(值1,...);
方式一：
经典的插入
1.插入的值的类型要与列的类型一致或者兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'张三','男','1998-08-31','18998898',NULL,2);
2.不可以为null的列必须插入值，可以为null的列是如何插入值？
方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'张三','男','1998-08-31','18998898',NULL,2);
方式二：
INSERT INTO beauty(id,NAME,sex,borndate,phone)
VALUES(14,'李四','男','1993-04-31','182998898');
3.列的顺序是否可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('王二','男','12304',16,'132142')
4.列数必须和值的个数一致
INSERT INTO beauty(NAME,sex,id,phone,boyfriend_id)
VALUES('王二码字','男','12304',16,'132142',123)
5.可以省略列名，默认所有列，而且列的顺序 和表中的列的顺序一致
INSERT INTO beauty
VALUES(19,'张飞','男',NULL,'119',NULL,NULL)
方式二：
语法：
insert into 表名
set 列名=值,列名=值,...
INSERT INTO beauty
SET id=19,NAME='王思',phone='999';
1.方式一支持一次性插入多行
INSERT INTO beauty
VALUES(19,'张飞','男',NULL,'119',NULL,NULL),
(19,'张飞1','男',NULL,'119',NULL,NULL),
(19,'张飞2','男',NULL,'119',NULL,NULL);
2.方式一支持子查询，方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'斩杀','1123132'
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'11132'
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表中姓唐的电话为213312124
UPDATE beauty SET phone='213312124'
WHERE NAME LIKE '唐%'
案例2：修改boys表中id号为2的名称为张飞，魅力值为10
UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;
2.修改多表的记录
案例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
SELECT * FROM boys;
三、删除语句
方式一：delete
语法：
1.单表的删除
delete from 表名 where 筛选条件
2.多表的删除[补充]
sql92语法：
delete 表1的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
sql99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件
方式二：truncate
语法：truncate table 表名
方式一：delete
1.单表的删除
案例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='黄晓明';
方式二：truncate
案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys WHERE usercp>100;
TRUNCATE TABLE boys;
delete pk truncate
1.delete可以加where条件 truncate 不能加
2.delete删除 效率高一点
3.假如要删除的表中有自增长列，如果用delete删除后 再插入数据，自增长列的值从断点开始 而truncate删除后 再插入数据，自增长列的值从1开始
SELECT * FROM boys;
DELETE FROM boys; id从断点处开始自增
TRUNCATE FROM boys; id从0开始自增
INSERT INTO boys (boyname,usercp)
VALUES('张三1',100),('张三2',100),('张三3',100)
4.truncate删除没有返回值 delete有返回值
5.truncate删除不能回滚,delete可以回滚
DDL语言
数据库定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建：create
修改：alter
删除：drop
一、库的管理
1.库的创建
语法：
create database 库名;
案例：创建库Books
CREATE DATABASE IF NOT EXISTS books;
2.库的修改
RENAME DATABASE books TO 新库名;
更改库的字符集
ALTER DATABASE books CHARACTER SET gkb;
3.库的删除
DROP DATABASE IF EXISTS books;
二、表的管理
1.表的创建
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
案例：创建表Book
CREATE TABLE book(
id INT ,
bName VARCHAR(20),图书名
price DOUBLE,价格
authorId VARCHAR(20),作者
publishDate DATETIME 出版日期
);
DESC book;
案例：创建表author
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(20)
)
DESC author;
2.表的修改
alter table 表名 add|drop|modify|chage column 列名 【列类型 约束】;
1.修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
2.修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
3.添加列
ALTER TABLE author ADD COLUMN annual DOUBLE;
4.删除列
ALTER TABLE author DROP COLUMN annual;
5.修改表名
ALTER TABLE author RENAME TO book_author;
DESC book;
3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
4.表的复制
INSERT INTO author VALUES
(1,'村上春树1','日本'),
(2,'村上春树2','日本'),
(3,'村上春树3','日本'),
(4,'村上春树4','日本')
SELECT * FROM Author;
SELECT * FROM copy;
1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='日本';
仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1;
常见的数据类型
数值型：
整型
小数：
定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob(较长的二进制数据)
日期型：
一、整型
分类：
tingint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点：
1.如果不设置无符号还是有符号，默认是有符号 如果想设置无符号 在类型后面加上unsigned关键字
2.如果插入的数值超过了整型的范围，会报out of range异常 并且插入的是临界值
3.如果不设置长度 会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充 但必须搭配zerofill使用
1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT ZEROFILL,
t2 INT ZEROFILL UNSIGNED
);
DESC tab_int;
INSERT INTO tab_int VALUES(-123142);
INSERT INTO tab_int VALUES(-123142,-12312);
二、小数：
1.浮点型
float(M,D)
double(M,D)
定点型
dec(M,D)
decimal(M,D)
特点：
1.M和D
M:整数部位+小数部位
D：小数部位
M和D都可以省略
如果是decimal,则M默认为10,D默认为0
如果是float或double则会根据插入值的精度来决定精度
定点型的精确度较高，如果要求插入数值的精度较高如货币运算则考虑使用
测试M和D
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DECIMAL(5,2)
);
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES(123.4125,123.45,123.45);
INSERT INTO tab_float VALUES(1232.45,123.45,123.45);
SELECT * FROM tab_float;
原则：
所选择的类型越简单越好 能保存数值的类型越小越好
三、字符型
较短的文本：
char、varchar
其他
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：
text、blob（较大的二进制）
特点：
写法 M的意思 特点 空间的耗费 效率
char char(M) 最大的字符数，可以省略默认为1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数，不可以省略 可变长度的字符 比较节省 低
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');
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('A,B,C');
四、日期型
分类：
date只保存日期
time 只保存时间
year 只保存年
datetime保存日期+时间
timestamp保存日期+时间
特点：
字节 范围 时区等的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone';
SET time_zone='+9:00';
