数据类型

数值类型

unsigned 既为非负数，用此类型可以增加数据长度!	例如 num1 unsigned
unsigned 属性只针对整型，而binary属性只用于char 和varchar
整型字段有个ZEROFILL属性，在数字长度不够的数据前面填充0，以达到设定的长度。
column_name  DECIMAL(P,D);
P是表示有效数字数的精度。 P范围为1?65。D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。
DECIMAL(P，D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。

日期和时间类型

注意区分 ‘0‘ (字符串) 和 0 (数字)。
‘0‘ 表示的YEAR值是2000，
0 表示的YEAR值是0000。
# 设置d字段的数据类型为DATE
CREATE TABLE my_date (d DATE);
# 插入日期数据
INSERT INTO my_date VALUES(‘2020-01-21‘);
# 插入当前系统日期
INSERT INTO my_date VALUES(CURRENT_DATE);
# 插入当前系统日期和时间
INSERT INTO my_date VALUES(NOW());
#日期分隔符用-,时间分隔符用:,中间可以用空格隔开

字符串类型

ENUM类型：枚举类型

ENUM(‘值1‘, ‘值2‘, ‘值3‘, …, ‘值n‘)
#案例
#插入gender性别,只能是男女
CREATE TABLE my_enum (gender ENUM(‘male‘, ‘female‘));

约束

非空约束

#添加非空约束
ALTER TABLE yue_shu MODIFY age INT NOT NULL;

默认约束

#添加默认约束
ALTER TABLE yue_shu MODIFY id INT DEFAULT 18;

唯一约束

#唯一约束
#保证数据表中字段的唯一性，即表中字段的值不能重复出现
#添加唯一约束
ALTER TABLE yue_shu add UNIQUE(id);
#删除唯一约束
ALTER TABLE yue_shu DROP INDEX id;
#查看唯一性约束
show keys from yue_shu;
#复合唯一约束	删除同上面一样
#只有当两个字段同时发生重复时，插入记录失败
ALTER TABLE yue_shu add UNIQUE(id,name);

主键约束

#主键约束相当于唯一约束和非空约束的组合，
#要求被约束字段不允许重复，
#也不允许出现NULL值，
#每个表最多只允许含有一个主键。
#主键约束
#添加主键约束
ALTER TABLE yue_shu MODIFY id int PRIMARY KEY;
#删除主键约束
ALTER TABLE yue_shu DROP PRIMARY KEY;

自动增长

#字段名 数据类型 AUTO_INCREMENT
/*
一个表中只能有一个自动增长字段；
该字段的数据类型是整数类型；
必须定义为键，如UNIQUE KEY、PRIMARY KEY
若为自动增长字段插入NULL、0、DEFAULT或在插入时省略该字段，该字段就会使用自动增长值；
若插入的是一个具体值，则不会使用自动增长值。
自动增长值从1开始自增，每次加1。
若插入的值大于自动增长的值，则下次插入的自动增长值会自动使用最大值加1；
若插入的值小于自动增长值，则不会对自动增长值产生影响。
使用DELETE删除记录时，自动增长值不会减小或填补空缺。
自动增长删除并重新添加后，自动增长的初始值会自动设为该列现有的最大值加1。
在修改自动增长值时，修改的值若小于该列现有的最大值，则修改不会生效。
*/
#给表设置自动增长
ALTER TABLE yue_shu MODIFY id INT UNSIGNED UNIQUE AUTO_INCREMENT;
#删除自动增长
ALTER TABLE yue_shu MODIFY id INT UNSIGNED;
#修改自动增长值
ALTER TABLE yue_shu AUTO_INCREMENT = 10;

查看表结构

#查看表结构
DESC yue_shu;

字符集校对集

# 创建数据库，指定字符集为utf8，使用默认校对集utf8_general_ci
CREATE DATABASE mydb_1 CHARACTER SET utf8;
# 创建数据库，指定字符集为utf8，校对集为utf8_bin
CREATE DATABASE mydb_2 CHARACTER SET utf8 COLLATE utf8_bin;

复制表

#复制表结构    
create table new表 like old表
#复制表数据 
INSERT into new表 SELECT * FROM old表;

#去除重复数据   
select distinct 需要去重的字段名 from 表名;

排序-限量

排序

#排序查询：asc为正序，desc为倒序
select * from student order by id desc ;
#多个字段排序
#先根据age进行排序,再根据id进行排序
select * from student order by age asc,id desc;
#中文排序convert(字段名 using gbk)
select * from student 
order by convert(name using gbk) asc;

限量

#限量，参数1是从下标几开始，参数2是读取几行数据
select * from student order by id desc LIMIT 10,5;
#统计记录数
SELECT COUNT(id) FROM student;

分组-聚合函数

根据年龄统计出人数

#2020年03月24日21:36:13      
#根据年龄统计出人数
#concat连接字符串 as重命名 where id不等于2的时候
SELECT CONCAT(age,‘岁‘) as 年龄,
COUNT(1) as 人数 
FROM student_copy1
WHERE id<>2
GROUP BY age
ORDER BY 2 desc

根据班级进行分组，获取每个班最大的年龄

#根据班级进行分组，获取每个班最大的年龄
SELECT Class as 班级,
MAX(age) as 最大年龄
FROM student_copy1
GROUP BY Class
ORDER BY 2 DESC

多字段分组

#多字段分组
select Class as 班级,
CONCAT(age,‘岁‘) as 年龄,
COUNT(1) as 人数 
FROM student_copy1  
GROUP BY Class,age
ORDER BY 3 desc

回溯统计

#回溯统计		----就是对统计的结果加起来
select CONCAT(s.age,‘岁‘) as 年龄,
COUNT(1) as 人数 
FROM student_copy1 s 
GROUP BY s.age WITH ROLLUP;

统计筛选

#统计筛选		----也就是对分组完成的数据加条件
select CONCAT(age,‘岁‘) as 年龄,
COUNT(1) as 人数 
FROM student_copy1  
GROUP BY age 
having 人数>6

运算统计

#求和统计
select CONCAT(age,‘岁‘) as 年龄,
SUM(age) as 求和
FROM student_copy1
GROUP BY 1 


#平均值统计
select CONCAT(age,‘岁‘) as 年龄,
AVG(age) as 求平均
FROM student_copy1
GROUP BY 1 


#求最大值统计
select CONCAT(age,‘岁‘) as 年龄,
MAX(id) as 求最大值
FROM student_copy1
GROUP BY 1 


#求最小值统计
select CONCAT(age,‘岁‘) as 年龄,
MIN(id) as 求最小值
FROM student_copy1
GROUP BY 1 

# GROUP_CONCAT 返回符合条件的参数字段值的连接字符串 也就是把所有能查找的数据都列出来
SELECT Class,
GROUP_CONCAT(name) as 全部数据
from student_copy1
GROUP BY Class 
ORDER BY RAND()

运算符

#获取大于等于1且小于10的任意一个随机整数
#floor代表取整数
SELECT FLOOR(1 + RAND() * (10 - 1));

比较运算符

逻辑运算符

赋值运算符

MySQL中“=”是一个比较特殊的运算符，既可以用于比较数据是否相等，又可以表示赋值。因此，MySQL为了避免系统分不清楚运算符“=”表示赋值还是比较的含义，特意增加一个符号“:=”用于表示赋值运算。

位运算符

运算符优先级

运算符优先级可以理解为运算符在一个表达式中参与运算的先后顺序，优先级别越高，则越早参与运算；优先级别越低，则越晚参与运算。

多表操作

联合查询

若要对联合查询的记录进行排序等操作，需要使用圆括号“()”包裹每一个SELECT语句，在SELECT语句内或在联合查询的最后添加ORDER BY语句。并且若要排序生效，必须在ORDER BY后添加LIMIT限定联合查询排序的数量，通常推荐使用大于表记录数的任意值。

#排序要加括号,并且加limit限定数量
(SELECT id, name
FROM student_copy2
ORDER BY id ASC
LIMIT 10)
#联合查询---默认为去重复 ALL代表查询全部
UNION ALL
(SELECT id, name
FROM student_copy3
ORDER BY id DESC
LIMIT 10);

连接查询

交叉连接

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。
例如，商品分类表中有3个字段，4条记录；商品表中有5个字段，10条商品信息，那么交叉连接后的笛卡尔积就等于4*10条记录数，每条记录中含有3+5个字段。

#交叉连接查询
SELECT *
FROM student_copy2
	CROSS JOIN student_copy3;
#CROSS JOIN用于连接两个要查询的表。
#通过该语句可以查询两个表中所有的数据组合。

内连接

内连接是一种常见的连接查询，它根据匹配条件返回第1个表与第2个表所有匹配成功的记录。

#需要查找的,a是一张表,b是一张表
SELECT a.runoob_id, a.runoob_author, b.runoob_count
#在runoob_tbl中查找
FROM runoob_tbl a
	#INNER JOIN----连接 tcount_tbl并指这张表为b ON可以理解为where当这两个字段相等时
	INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

外连接

左连接

它用于返回连接关键字（LEFT JOIN）左表中所有的记录，以及右表中符合连接条件的记录。

当左表的某行记录在右表中没有匹配的记录时，右表中相关的记录将设为空值。

#左连接	----根据左表查右表
SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a
	LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

右连接

它用于返回连接关键字（RIGHT JOIN）右表（主表）中所有的记录，以及左表（从表）中符合连接条件的记录。

当右表的某行记录在左表中没有匹配的记录时，左表中相关的记录将设为空值。

#右连接	----根据右表查右表
SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a
	LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

子查询

在一个表表达中可以调用另一个表表达式，这个被调用的表表达式叫做子查询（subquery），我么也称作子选择（subselect）或内嵌选择（inner select）。子查询的结果传递给调用它的表表达式继续处理。

#2020年04月02日22:59:19
#表子查询实例
#获取编号小于10的男性球员的号码
SELECT playerno
FROM (
	#拿到所有编号小于10的的球员的编号和性别
	SELECT playerno, sex
	FROM players
	WHERE playerno < 10
) as players10
#对拿到的临时表进行查询当性别等于男
WHERE sex = ‘M‘;
#行子查询 select playerno 
#获取和100号球员性别相同并且居住在同一城市的球员号码。
SELECT playerno
FROM players
#对原表进行查询当性别等于临时表的性别城市等于临时表的城市
WHERE (sex, town) = 
#先找到这个100号球员的性别和城市
(
	SELECT sex, town
	FROM players
	WHERE playerno = 100
);
#标量子查询实例
#获取和27号球员出生在同一年的球员的号码
SELECT PLAYERNO
FROM players
#当年龄等于临时表的年龄
WHERE YEAR(BIRTH_DATE) = (
		#获取年龄
		SELECT year(BIRTH_DATE)
		FROM players
		#当编号等于27
		WHERE PLAYERNO = 27
	)
	#获取编号除了27号球员的
	AND playerno <> 27;
	#列子查询实例
	#获取球员性别为女的所有球员的球员号，名字及所在城市。
	/*
	IN：在指定项内，同 IN(项1,项2,…)。
? ??ANY：与比较操作符联合使用，ANY关键字必须接在一个比较操作符的后面，表示与子查询返回的任何值比较为 TRUE ，则返回 TRUE 。
? ??SOME：ANY 的别名，较少使用。
? ??ALL：与比较操作符联合使用，ALL关键字必须接在一个比较操作符的后面，表示与子查询返回的所有值比较都为 TRUE ，则返回 TRUE 。
	*/
	
	#展示球员号,姓名,城市当编号为里面的
SELECT PLAYERNO, name, TOWN
FROM players
WHERE PLAYERNO IN (
	#展示所有性别为女的球员号
	SELECT PLAYERNO
	FROM players
	WHERE sex = ‘F‘
);
#获取至少比同城的另一球员年轻的所有球员的号码，日期和居住城市。
SELECT playerno, birth_date, town
FROM players p1
WHERE birth_date > ANY (
	SELECT birth_date
	FROM players p2
	WHERE p1.town = p2.town
);
#获取最老球员的号码，名字及生日。（即出生日期数值小于或等于所有其它球员的球员）
SELECT playerno, name, birth_date
FROM players
WHERE birth_date <= ALL (
	SELECT birth_date
	FROM players
);

外键

外键定义

外键指的是在一个表中引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束，从而保证数据的一致性和完整性。

• 被引用的表称为主表。
• 引用外键的表称为从表。

添加外键约束

• 创建数据表（CREATE TABLE），在相应的位置添加外键约束。
• 修改数据结构（ALTER TABLE），在相应的位置添加外键约束。

ON DELETE与ON UPDATE参数

创建数据表时,添加外键约束

CREATE TABLE student3 (
	id int UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT ‘ID‘,
	name varchar(32) COMMENT ‘姓名‘, 
    /*关键字CONSTRAINT用于定义外键约束的名称，如果省略，MYSQL将会自动生成一个名字。,表示一致性,FOREIGN KEY表示是外键,(room_id)表示的是外键名称列表,REFERENCES参考 room(id) room是主表,id是约束字段 on delete代表它的删除权限,on update代表更新权限*/
	room_id int,
	CONSTRAINT fk_room FOREIGN KEY (room_id) REFERENCES room (id) ON DELETE RESTRICT ON UPDATE CASCADE
)

修改表结构时，添加外键约束

更新表时的外键语法

ALTER TABLE student3
	ADD CONSTRAINT fk_room FOREIGN KEY (room_id) REFERENCES room (id) ON DELETE RESTRICT ON UPDATE CASCADE;

MUL、PRI和UNI是什么

• 如果键是PRI，则列是主键或多列主键中的列之一。
• 如果键是UNI，则该列是唯一索引的第一列。(唯一索引允许多个空值，但可以通过检查Null字段来判断该列是否允许空。)
• 如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值

关联表操作

对于已建立外键约束的关联数据表来说，若要对主表执行删除操作，从表将按照其建立外键约束时设置的ON DELETE参数自动执行相应的操作。

删除外键约束

ALTER TABLE student3 drop foreign key fk_room;

mysql入门笔记-2