DQL语言的学习
联合查询
引入:
union 联合、合并
语法:
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
…
select 字段|常量|表达式|函数 【from 表】 【where 条件】应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点:
- 多条查询语句的查询的列数必须是一致的
- 多条查询语句的查询的列的类型几乎相同
- union代表去重,union all代表不去重
#引入的案例:查询部门编号>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;
#案例:查询中国用户中男性的信息以及外国用户中男性的信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL # UNION ALL表示不去重,保存有相同名字的信息
SELECT t_id,tname FROM t_ua WHERE tGender='male';
DML语言的学习
插入语句
语法:
insert into 表名(字段名,…)
values(值1,…);
特点:
- 字段类型和值类型一致或兼容,而且一一对应
- 可以为空的字段,可以不用插入值,或用null填充
- 不可以为空的字段,必须插入值
- 字段个数和值的个数必须一致
- 字段可以省略,但默认所有字段,并且顺序和表中的存储顺序一致
#方式一:经典的插入
SELECT * FROM beauty;
#1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','188888888',NULL,2);
#2.不可以为null的列必须插入值。可以为null的列如何插入值?
#方式一:
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','188888888',NULL,2);
#方式二:
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES(14,'金星','女','1990-4-23','178888888',2);
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','178888888');
#3.列的顺序是否可以调换 -- 可以
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'123456789');
#4.列数和值的个数必须一致 Column count doesn't match value count at row 1
INSERT INTO beauty(NAME,sex,id,phone,boyfriend_id)
VALUES('关晓彤','女',17,'1234');
#5.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
#方式二:
/*
语法:
insert into 表名
set 列名 = 值,列名= 值,...
*/
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='000';
#两种方式的比较
#1、方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','188888888',NULL,2),
(24,'唐艺昕2','女','1990-4-23','188888888',NULL,2),
(25,'唐艺昕3','女','1990-4-23','188888888',NULL,2);
#2、方式一支持子查询,方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','1245612';#相当于把查询结果的结果集插入到beauty里面
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1245612'
FROM boys WHERE id < 3;
修改语句
- 修改单表语法:
update 表名 set 字段=新值,字段=新值
【where 条件】 - 修改多表语法:
sql92语法:
update 表1 别名1,表2 别名2
set 字段=新值,字段=新值
where 连接条件
and 筛选条件
sql99语法:
update 表1 别名
inner | left |right join 表2 别名
on 连接条件
set 列=值,…
where 筛选条件;
#案例 1:修改张无忌的女朋友的手机号为‘111’
UPDATE boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
SET b.phone ='111'
WHERE bo.`boyName`='张无忌';
SELECT *
FROM beauty;
#案例2:修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.id = b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE b.boyfriend_id IS NULL;
SELECT * FROM boys;
删除语句
方式1:delete语句
- 单表的删除:
delete from 表名 【where 筛选条件】 - 多表的删除:
sql92语法:
delete 表(别名1,别名2 …)
from 表1 别名1,表2 别名2
where 连接条件
and 筛选条件;
sql99语法:
delete 表(别名1,别名2…)
from 表1 别名
inner | left | right join 表2 别名 on 连接条件
where 筛选条件;
方式2:truncate语句
truncate table 表名
#方式一: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` = '黄晓明';
#方式二:truncate语句 又叫清空数据 不能加where语句
#案例:将魅力值>100的男神信息删除
TRUNCATE TABLE boys;
两种方式的区别:
- truncate不能加where条件,而delete可以加where条件
- truncate的效率高一点
- truncate 删除带自增长的列的表后,如果再插入数据,数据从1开始,而delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始
- truncate 删除没有返回值(没有说明受影响的函数),delete删除有返回值
- truncate删除不能回滚,delete删除可以回滚
简言之:
truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表
delete的效果有点像将mysql表中所有记录一条一条删除到删完
DDL语句的学习
库和表的管理
库的管理:
一、创建库
create database 库名
二、删除库
drop database 库名
三、修改库
alter databast 库名 …
#案例:创建库Book
CREATE DATABASE IF NOT EXISTS books;
#2、库的修改
RENAME DATABASE books TO 新库名; #不安全,这条语句已经删除了
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#3、库的删除
DROP DATABASE IF EXISTS books;
表的管理:
一、表的创建★
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
…
列名 列的类型【(长度) 约束】
);
#案例:创建表book
CREATE TABLE book(
id INT ,#书的编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT ,#作者 (不直接写名字,分类存储减少冗余)
publishDate DATETIME#出版日期
);
DESC book;
#案例:创建表author
CREATE TABLE IF NOT EXISTS author (
id INT ,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
二、表的修改
语法:
alter table 表名 add | drop |modify | change column 列名 【列类型 约束】;
change时可加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;#不能用exists
#⑤修改表名
ALTER TABLE author RENAME TO book_author;
#自己尝试能不能省略COLUMN
USE TEST;
CREATE TABLE TEST_ORACLE(
ID INT PRIMARY KEY,
NAME VARCHAR(20),
SEAT INT
);
ALTER TABLE TEST_ORACLE MODIFY ID VARCHAR(20);
ALTER TABLE TEST_ORACLE ADD SEAT2 INT;
#CHANGE的力度比较大,我的见解是MODIFY的力度 + RENAME COLUMN TO 的力度
ALTER TABLE TEST_ORACLE CHANGE SEAT2 SEAT_2 INT;
ALTER TABLE TEST_ORACLE DROP SEAT_2;
#修改列名
ALTER TABLE TEST_ORACLE RENAME COLUMN SEAT TO SEAT2;
#修改表名
ALTER TABLE TEST_ORACLE RENAME TO TEST_MYSQL;
DESC TEST_ORACLE;
三、表的删除
DROP TABLE 【IF EXISTS】 表名
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
#通用的写法:
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
四、表的复制
#表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
SELECT * FROM author;
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
SELECT * FROM copy;
#2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
SELECT * FROM copy2;
#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
SELECT * FROM copy3;
#仅仅复制某些字段但是没数据
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1=2;#where 0;
常见的数据类型
分类:
- 数值类型
- 字符类型
- 日期类型
一、整型
分类:(括号内是字节数)
tinyint(1)、smallint(2)、 mediumint(3)、int/integer(4)、bigint(8)
特点:
①如果不设置无符号还是无符号,默认是有符号,如果想设置无符号,需要添加关键字unsigned
②如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值。 我用的8.0版本只报错什么都不插入。
③如果不设置长度,会有默认的长度
④长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用!使用zerofill默认是unsigned
#如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT(7) ZEROFILL,
t2 INT ZEROFILL
);
DESC tab_int;
INSERT INTO tab_int VALUES(-1231244);
#可能是版本问题,我的直接报错不能执行,老师的t1正常插入,t2为0
INSERT INTO tab_int VALUES(-1231244,-12313);
#直接报错
INSERT INTO tab_int VALUES(3333333333333,45);
INSERT INTO tab_int VALUES(123,123);
SELECT * FROM tab_int;
二、 小数
1.浮点型:
float(M,D)
double(M,D)
2.定点型:dec就是decimal
dec(M,D)
decimal(M,D)
特点:
①
M:整数部位+小数部位
D:小数部位
如果超过范围,则插入临界值,8.0版本依然是报错
②
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.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(16123.4,16123.4,16123.4);
#选择数据类型的原则:
/*
所选择的类型越简单越好,能保存数值的类型越小越好
*/
三、字符型
- 较短的文本:
char
varchar
其他:
binary 和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合- 较长的文本:
test
bolb(较大的二进制)
特点:
写法 | 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('n');#不能插入
INSERT INTO tab_char VALUES('sdf');#不能插入
INSERT INTO tab_char VALUES('A');
SELECT * FROM tab_char;
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('Ab');#不行
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 受
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';