day04(联合查询，DML，DDL)

联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
...


应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

#查询部门编号>90或邮箱包含a的员工信息
use myemployees;
select * from employees where last_name like '%a%' or department_id >= 90;
select * from employees where department_id >= 90 union select * from employees where last_name like '%a%';

DML

数据操作语言

插入：insert

#方式一：经典的插入
/*
语法：
insert into 表名(列名,...) values(值1,...);
*/
use girls;
#1.插入的值的类型要与列的类型一致或兼容
insert into beauty(id, name, sex, borndate, phone, photo, boyfriend_id) VALUES (14,"小唐",'女','1990-1-1','19888888888',null,2);
#2.不可以为null的列必须插入值。可以为null的列如何插入值？
-- 方式一
insert into beauty(id, name, sex, borndate, phone, photo, boyfriend_id) VALUES (17,"小唐",'女','1990-1-1','19888888888',null,2);
-- 方式二
insert into beauty(id, name,phone) values (18,'张三','1898888888');
#3.列的顺序是否可以调换
insert into beauty(phone,name) values ('199999999','李四');
#4.列数和值的个数必须一致
insert into beauty(phone,name,sex) values ('199999999','王二','女');
#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
insert into beauty values (22,'233','女','1999-02-01','18999999999',null,2);
#方式二：
/*
语法：
insert into 表名
set 列名=值,列名=值,...
*/
insert into beauty set id = 28,name = 'xiao',phone = '110';
#两种方式大pk ★
#1、方式一支持插入多行,方式二不支持
insert into beauty values (32,'233','女','1999-02-01','18999999999',null,2),(33,'233','女','1999-02-01','18999999999',null,2);
#2、方式一支持子查询，方式二不支持
insert into beauty(id,name,phone) select 34,'小宋','119';

修改：update

/*
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表中姓名含唐的女神的电话为13899888899
update beauty set phone = '13899888899' where name like '%唐%';
#案例2：修改boys表中id好为2的名称为张飞，魅力值 10
update boys set boyName = '张飞',userCP = 10 where id = 2;
#2.修改多表的记录
#案例 1：修改张无忌的女朋友的手机号为114
update boys,beauty set phone = '114' where boyfriend_id = boys.id and boyName = '张无忌';
#案例2：修改没有男朋友的女神的男朋友编号都为2号
update boys right join beauty on boyfriend_id = boys.id set boyfriend_id = 2 where boyfriend_id is null;

删除：delete

#三、删除语句
/*
方式一：delete
语法：
1、单表的删除【★】
delete from 表名 where 筛选条件
2、多表的删除【补充】
sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

方式二：truncate
语法：truncate table 表名;
*/
#方式一：delete
#1.单表的删除
#案例：删除手机号以9结尾的女神信息
delete from beauty where phone like '%9';
#2.多表的删除
#案例：删除张无忌的信息和他女朋友的信息
delete b,bo from beauty b,boys bo where boyfriend_id = bo.id and bo.boyName = '张无忌';
#案例：删除张飞的女朋友的信息
delete b from beauty b,boys bo where boyfriend_id = bo.id and bo.boyName = '张飞';
#方式二：truncate语句

#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;-- 加不了删选条件

#delete pk truncate【面试题★】

/*

1.delete 可以加where 条件，truncate不能加

2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值

5.truncate删除不能回滚，delete删除可以回滚.

*/

DDL

数据定义语言

库和表的管理

库的管理

#1、库的创建
/*
语法：
create database  [if not exists]库名;
*/
#案例：创建库Books
create database if not exists Books;
#2、库的修改
rename database books to books1;-- 不支持了
#更改库的字符集
alter database books character set gbk;
#3、库的删除
drop database if exists books;

表的管理

#二、表的管理
#1.表的创建 ★
/*
语法：
create table 表名(
   列名 列的类型【(长度) 约束】,
   列名 列的类型【(长度) 约束】,
   列名 列的类型【(长度) 约束】,
   ...
   列名 列的类型【(长度) 约束】
)
*/
create database if not exists test001;
use test001;
create table book(
    id int,
    bookname varchar(10),
    price double,
    authorId int,
    publishDate datetime
);
desc book;
#案例：创建表author
create table author(
    id int,
    name varchar(5),
    nation varchar(10)
);
desc author;
#2.表的修改
/*
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
#①修改列名
alter table book change column id book_id int;
#②修改列的类型或约束
alter table book modify column book_id varchar(20);
#③添加新列
alter table book add column page int;
#④删除列
alter table book drop column page;
#⑤修改表名
alter table book rename to book_author;
#3.表的删除
drop table if exists book_author;

#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();
#4.表的复制
insert into author values (1,null,null),(2,'2','2'),(3,'3','3');
#1.仅仅复制表的结构
create table copy like author;
#2.复制表的结构+数据
create table copy2 select * from author;
#只复制部分数据
create table copy3 select * from author where id = 1;
#仅仅复制某些字段
create table copy5 select id,nation from author;

数据类型

#常见的数据类型
/*
数值型：
	整型
	小数：
		定点数
		浮点数
字符型：
	较短的文本：char、varchar
	较长的文本：text、blob（较长的二进制数据）

日期型：
*/
#一、整型
/*
分类：
tinyint、smallint、mediumint、int/integer、bigint
1	 		2		3				4		8

特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
*/
#1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL 
);
DESC tab_int;
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);
INSERT INTO tab_int VALUES(123,123);
SELECT * FROM tab_int;
#二、小数
/*
分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(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
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);
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)		最大的字符数，可以省略，默认为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');
INSERT INTO tab_char VALUES('A');
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,c,d');


#四、日期型

/*
分类：
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';

---