MySql基础SQL大全，兄弟们别看分一章两章的，来这看大全了

–查看表结构
DESC 表名；
DESCRIBE 表名；
SHOW COLNMNS FROM 表名；

–创建表
CREATE TABLE 表名（
名字 类型；
名字 类型
）；

–查询表
SELECT * FROM 表名；

–向表中插入记录
INSERT 表名 VALUES(值，…)；
–注意VALUES（）括号中的 值的类型 和 大小的对应；

TINYINT :有符号值：-128~127 无符号值：0~255 可代表布尔类型TINYINT（1）代表turn，0代表false
INT:有符号值：-2147683648~2147683648 无符号值：0~4294967295

创建整型表，例：
CREATE TABLE 表名（
num1 INT UNSIGNED,
num2 INT,
num3 INT
）；
–UNSIGNED代表无符号的，即上例num1是没有正负号概念的（一般用这个，下面那个了解以下就行）；

CREATE TABLE 表名（
num1 INT（3） ZEROFILL,
num2 INT,
num3 INT
）；
–上例ZOREFILL作用是不够现实长度自动补零，num1加上ZEROFILL，显示长度为3，如果设num1值为1，则数据库中显示的将会是001，以此类推（没有设置显示长度，会有默认显示长度）；

创建浮点类型表，例：
CREATE TABLE 表名（
num1 FLOAT（6,2），
num1 DOUBLE（6,2），
num1 DECIMAL（6,2），
）；
–显示长度（6,2）即6位，可带两位小数，如：3.1415926，设值超过这个范围的话就会出现WARNINGS,真正存进去的就只有3.14。 显示警告语句：SHOW WARNINGS;

CHAR（M）：定长字符串，占用空间大，但速度快（M为规定占用的字符长度，CHAR类型规定M为多少就会占用多少）
VARCHAR（M）：变长字符串，占用空间小，但速度慢（VARCHAR类型是实际存放多少字符串就占用多少空间，不包括超出规定长度）

ENUM（value1，value2，…）：枚举类型，想要对枚举类型的数据进行操作只能对ENUM（）括号内枚举出来的值进行操作，每一个value都有一个序号，也可以对序号进行操作（从1开始）例：
CREATE TABLE IF NOT EXISTS test(
sex EUNM(“男”，“女”，“保密”)
)；
EUNM当中有三个值（在值后面打空格可自动清除）比如：
INSERT test VALUES(“男”);
INSERT test VALUES(“女”);
INSERT test VALUES(“保密”);
INSERT test VALUES(“1”);//这与 INSERT test VALUES(“男”); 是等同的；
都是可以的，因为test表中sex枚举类型中包含，但
INSERT test VALUES(“男111”);
则不行，因为没有做出枚举，插入即报错；

SET（“VALUE1”，“VALUE2”，“VALUE3”，…）:集合类型，最多可以存储64个值，存储方式以二进制存储，例：
CREATE TABLE IF NOT EXISTS test(
word SET(“A”，“B”，“C”,“D”)
)；
INSERT test VALUES(“A”,“D”,“C”);
以上插入输入是ADC，但在数据库中他会按照列举的顺序排列，即显示ACD;

日期时间类型：（TIME,DATE,DATETIME,TIMESTAMP,YEAR）

YEAR（存放年限为1901-2155），尽量写到具体数字或字符，例：
CREATE TABLE IF NOT EXISTS test(
birth TEAR
）；
INSERT test VALUES（1901）；
或
INSERT test VALUES（“1901“）；
即存进了 birth=1901；

TIME类型：输入形式（天数 小时：分钟：秒） 显示形式（小时：分钟：秒），TIME类型会自动把天数转换为小时数，从左到右遍历，例；
INSERT test VALUES(‘1 12：12：12’);//1天12小时12分钟12秒
SELECT *FROM test；//显示形式就会变成：36:12:12

DATE：存储范围（1000:01:01~9999:12:31），例：
CREATE TABLE IF NOT EXISTS test(
date DATE
）；
INSERT test VALUES（‘2012-6-7’）；或INSERT test VALUES（‘20120607’）；
显示就会为 2012-06-07
其他不做演示；

完整性约束

主键（PRIMARY KEY–PRI）：非空，不得重复，一个表里只有一个主键
CREATE TABLE IF NOT EXISTS test（
id INT （PRIMARY KEY）或（KEY）, //id为主键
username VARCHAR(20)
）；
–查看创建表的定义
SHOW CREATE TABLE test；

–复合主键
CREATE TABLE IF NOT EXISTS test（
id INT ，
username VARCHAR(20)
card CAHR(18),
PRIMARY KEY（id,card）//复合主键显示
）；
复合主键必须是其中包含属性都要相同则为一条数据，例：
INSERT test VALUES（1，‘king’，‘111’）；
INSERT test VALUES（1，‘queen’，‘112’）；
这里则是两条数据，不能存在复合主键中包含主键全部相同的数据，不得重复属性；位置靠前的主键是必须要被用到的，例如索引；

自增长（AUTO_INCREMENT）:实现对已有最大字号自动+1，一个表只有一个自增长，且要配合主键，是自增长一定是主键，但是主键不一定是自增长，只对整数类，整数对有效；
–测试自增长
CREATE TABLE test（
id SMALLINT KEY AUTO_INCREMENT, //主键且自增长
username VARCHAR（20）
）；
INSERT test VALUES(1，‘king’);
INSERT test(username) VALUES（‘queen’）；//指定想username当中插入‘queen’字段，表自动会对上一个id+1作为此次操作的id；

非空（NOT NULL）标识了非空的属性不能是为空的例如用户名和密码，否则报错。
CREATE TABLE IF NOT EXISTS test（
id INT UNSIGNED KEY AUTO_INCREMENT, //id为无符号，主键,自增长，
username VARCHAR(20) NOT NULL, //非空
password CHAR（32） NOT NULL, //非空
age TINYINT UNSIGNED
）；
INSERT test（username，password） VALUES（‘king’，‘12345’）；
–结果id将会自动+1，username为‘king’，password为‘12345’，age没填就是NULL；
–username、password标识了为NOT NULL，如果没填或者填为NULL，则会报错；

默认值（DEFAULT）：默认值是如果不给属性赋值，即会赋值成默认值
–测试默认值（DEFAULT）
CREATE TABLE IF NOT EXISTS test（
id INT UNSIGNED KEY AUTO_INCREMENT, //id为无符号，主键,自增长，
username VARCHAR(20) NOT NULL, //非空
password CHAR（32） NOT NULL, //非空
age TINYINT UNSIGNED DEFAULT 18, //age默认值 18
addr VARCHAR(20) NOT NULL DEFAULT ‘北京’ //addr默认值 北京
）；
–下面的age、addr如果不给赋值时，默认值就会是18和‘北京’
如果在赋值时想用默认值也可以用DEFAULT进行赋值，例：
INSERT test VALUES（1，‘queen’，‘12345’，DEFAULT，DEFAULT）;
–即在age和addr处赋默认值18和‘北京’；

唯一性约束（）：唯一性索引，一个表中只有一个主键，但可以有多个唯一，唯一不可重复，除值为NULL的属性，NULL不为重复；
CREATE TABLE IF NOT EXISTS test（
id INT UNSIGNED KEY AUTO_INCREMENT, //id为无符号，主键,自增长，
username VARCHAR(20) NOT NULL UNIQUE, //非空,唯一
card CHAR（32）UNIQUE, //唯一
）；
–被标中唯一的属性，不可以出现重复值，例：
INSERT test VALUES（‘A’）；
INSERT test VALUES（‘A1’）；
–这里是成功的，但是如果出现下面的情况，则报错：
INSERT test VALUES（‘A1’）；
–它会提示A1值已存在

重命名数据表：
一、
（1）ALTER TBALE（要改变的表） RENAME TO （新表名）；
（2）ALTER TABLE（要改变的表） RENAME AS （新表名）；
（3）ALTER TABLE（要改变的表） RENAME （新表名）；
二、
（1）RENAME TABLE （要改变的表） TO （新表名）；

向表内添加和删除字段：
一、添加字段：默认添加在表尾；完整性约束选择性写。
ALTER TABLE （表名） ADD （要添加的字段） （数据类型）【完整性约束条件】
例如：ALTER TABLE test ADD card VARCHAR（20）NOT NULL；
–如果想要指定位置的话
例如：ALTER TABLE test ADD card VARCHAR（20）NOT NULL FIRST；
–以上就添加到了表头上
例如：ALTER TABLE test ADD card VARCHAR（20）NOT NULL AFTER （某个字段）；
–以上就添加到某个字段的后面，两种方式可以实现把字段加到表的任何位置；

–以下添加多个字段
ALTER TABLE test
ADD id INT NOT NULL DEFAULT 123 AFTER password，
ADD addr VARCHAR(20) FIRST,
ADD score SET（‘A’，‘B’，‘C’）；
–以上实现了添加
1、选中表test
2、添加id为123，不为空自增长在userpass后面
3、添加地址排在首位
4、设置成绩，只能在ABC中选中
二、删除字段
LATER TABLE（表名）DROP（要删除的字段）；
–以下实现一次选中表删除多个字段
ALTER TABLE test
DROP id，
DROP password，
DROP addr；
–以上删除了id，password，addr；

–删除和添加可以合起来用
ALTER TABLE test
ADD id INT NOT NULL DEFAULT 123 AFTER password，
DROP id；
–以上实现了添加字段id和删除字段id工作；

修改字段
–注意原来的完整性约束条件，不改必须写上去，但改变不了字段名字
ALTER TABLE 表名 MODIFY 字段名称 字段类型 【完整性约束】【FIRST | ALTER 字段名称】
或
–以下便可以改变字段的名称，不改字段名称的可以用以上语句，修改的可以选用以下语句
ALTER TABLE 表名 CHANGE 旧字段名称 新字段名称 字段类型 【完整性约束】【FIRST | ALTER 字段名称】

添加默认值
ALTER TABLE （表名）ALTER （字段名称）SET DEFAULT （默认值）；
删除默认值
ALTER TABLE （表名）ALTER （字段名称）DROP DEFAULT;

添加主键
ALTER TABLE （表名）ADD PRIMARY KEY （字段名称）；
删除主键
ALTER TABLE （表名）DROP PRIMARY KEY ；//一个表只有一个主键，删除的只有一个目标
–一个字段同时被标识主键和自增长，直接用上述删除主键时是要报错的
–所以我们先删除自增长标识
ALYER TABLE （表名）MODIFY （字段名称）INT UNSIGNED;
ALTER TABLE （表名）DROP PRIMARY KEY ；
–以上两个步骤即可删除标识了主键和自增长的字段

---

添加唯一性约束条件
ALTER TABLE （表名）ADD UNIQUE 【索引名称】（字段名称）；
–以上字段名称的括号是要保留的，索引名称可以选择性加，但是他对删除唯一性条件便利作用
删除唯一性约束调节
ALTER TABLE （表名）DROP INDEX （字段名称）；
或
ALTER YABLE （表名）DROP KEY （索引名称）；//索引名称可以用作删除唯一性约束条件的标志

修改表的存储引擎
ALTER TABLE （表名）ENGING=（存储引擎名称）；
例如：ALTER TABLE test1 ENGING=MYISAM；

设置自增长的值
ALTER TABLE （表名） AUYO_INCREMENT=（值）;//自增长只可存在于主键，目标是唯一的

插入数据
一、不知道具体的字段名
INSERT （表名）VALUES（所有字段）；//这种方式必须给所以的字段赋值
二、列出指定字段
INSERT （表名）（字段名）VALUES（所指字段的值）；
例如：INSERT tset(id,username,password) VALUES（1,‘xph’,‘123’）；
三、同时插入多条数据
INSERT （表名）VALUES（所有字段值），（所有字段值），（所有字段值）…;
INSERT （表名）SET (字段)=(值),(字段)=(值),(字段)=(值)…;
四、将查询结果插入表中
INSERT （被插入表名）SELECT (字段名称…) FROM （被查询表名）；

更新记录
UPDATE（表名）SET（字段名称=值），（字段=值）…；
–以上是没有条件的更新，把表中所有的值都更新了
UPDATE（表名）SET（字段名称=值）WHERE （字段名称=><值）；
–以上where后的一般为主键，因为主键是唯一的,同样限定的范围，WHERE后是限定条件，并非固定形式

删除数据
DELETE FROM 表名；
–以上是删除表内数据，表中所有数据都会删除
DELETE FROM （表名）WHERE （字段名称=值）；
–以上是删除表中某一记录，WHERE后是限定条件，并非固定形式
TRUNCATE （表名）；
–以上是彻底清空数据表，包括默认值，自增长

查询SELECT

一、单表查询
SELECT *FROM （表名）；
–以上查询表中所有字段
SELECT （字段 , 字段…）FROM （表名）；
–以上查询你想要查询的字段
SELECT （字段 , 字段…）FROM （库名 . 表名）；
–防止不知道表名在哪个数据库，以上可以通过库名 . 表名来对表查询
SELECT （字段 , 字段…）FROM （表名）AS（别名）；
例：SELECT id，username FROM user AS u；
–以上是给表写别名，主要是方便写，一般不超过两个字母，如果一旦用了别名，就一定要AS一个别名
SELECT （字段）AS （别名），… FROM （表名）；
–以上可以给字段其别名，也可以给表和字段都起别名，注意AS用法和格式

查询语句（where条件）
1：比较运算符—=、<、<=、>=、!=、<>//不等于、<=>
<=>：比等于多一个判断null的作用，其他的和=是一样的
2：是否为控制 IS NULL IS NOT NULL
例：SELECT * FROM （表名）WHERE id IS NULL;
–以上输出的就是id为null的记录
例：例：SELECT * FROM （表名）WHERE id IS NOT NULL;
–以上输出的就是id不为null的记录

WHERE（范围查询）
一、指定范围
例：SELECT * FROM （表名）WHERE （字段名称）BETWEEN () AND ();
SELECT * FROM test WHERE id BETWEEN 3 AND 10;
–以上就完成了对test表id字段3~10内容的查询（包括3和10）
例：SELECT * FROM （表名）WHERE （字段名称）NOT BETWEEN () AND ();
SELECT * FROM test WHERE id NOT BETWEEN 3 AND 10;
–以上就完成了对test表id字段除开3~10内容的查询（不包括3和10）

二、指定集合范围
SELECT * FROM （表名）WHERE （字段名称）IN（值，值，值，值）；
例：SELECT * FROM test WHERE id IN（1,2,3,4）；
–以上就可以对id的1,2,3,4字段的查询；同样的，对字符串同样可以查询，会忽略大小写
SELECT * FROM （表名）WHERE （字段名称）NOT IN（值，值，值，值）；
例：SELECT * FROM test WHERE id NOT IN（1,2,3,4）；
–以上就可以对id的1,2,3,4字段之外的查询；同样的，对字符串同样可以查询，会忽略大小写

WHERE（模糊查询）
– %：代表0个、一个或者多个任意字符
– _ : 代表1个任意字符
例：查询姓张的用户
SELECT * FROM test WHERE username LIKE ‘%张%’；
或
SELECT * FROM test WHERE username LIKE ‘张%’；//张为第一个字
SELECT * FROM test WHERE username LIKE ‘%张’；//张为最后一个字
–关键字是LIKE
例：查找三个字符的记录
SELECT * FROM test WHERE username LIKE ‘_ _ ’；//三个下划线就代表三个字符
例：联合模糊查询，比如我知道x之前有一位，不知道x之后有几位
SELECT * FROM test WHERE username LIKE ‘ x %’；
–以上就可以达到题目的查询要求

反过来可以用NOT LIKE来查询除开什么条件之外的记录

逻辑运算符
多个查询条件：AND、OR 相当于且和或

查询组合条件时，可以用AND来连接
比如我查询用户名和密码形成的组合条件
SELECT * FROM table WHERE username=‘king’AND password=‘KING’；
只有两个条件都为真，即可成功；

同样的也可以查询范围数据，比如：（查询id大于3，且年龄不为空的记录）
SELECT * FROM table WHERE id>3 AND age IS NOT NULL；

查询区间组合条件的数据可以用到BETWEEN关键字，比如，（查询id是5-10之间，用户名为四位的数据）

SELECT * FROM table WHERE id BETWEEN 5 AND 10 AND username=‘_ _ _ _’；

组合条件语句同样包括多范围查询，这时我们可以用OR来查找
比如：（查询id大于3 或 age不为0的数据）

SELECT * FROM table WHERE id>3 OR age IS NOT NULL；

分组查询
GROUP BY 查询结果分组
按照某些条件来进行，分组，比如字段age有三个年龄，18/19/20，即会把值相同的放在一个组里

SELECT * FROM table GROUP BY age；
–以上就对于age来说进行分组，将会分成age=18/19/20的三个组，但显示时只会显示分组后的第一条记录

按照多个字段分组
SELECT * FROM table GROUP BY 字段，字段；

也可以先加条件后在分组

SELECT * FROM table WHERE （条件）GROUP BY 字段，字段；
例：(对id大于5的记录通过sex来分组)
SELECT * FROM table WHERE id>5 GROUP BY sex；

GROUP BY配合GROUP_CONCAT得到分组详情
例：查询id，sex，用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM table1 GROUP BY sex;
这样就可以看到通过sex分组后，组内的username，
SELECT id,sex,GROUP_CONCAT(username)，GROUP_CONCAT(password) FROM table1 GROUP BY sex;
以此类推，这样就可以看到通过sex分组后，组内的username和密码

同时和可以配合聚合函数使用
例：查询id，sex，用户名详情以及组中总人数按sex分组
SELECT id,sex,GROUP_CONCAT(username) AS users ，COUNT() AS num FROM table1 GROUP BY sex;
其中：AS为别名，COUNT()是分组后组内总人数，

统计某表中所有记录
SELECT COUNT(*) AS users FROM table；
即会显示表内所有记录的条数

COUNT(*)也可以写成指定字段COUNT(id),则会统计id的记录条数，但是COUNT不会统计NULL值，如果被统计的字段有NULL值，则不会被统计进去。

比如查询id，sex，GROUP_CONCAT()，组中总人数，组中最大年龄，最小年龄，平均年龄，以及年龄总和和按照性别分组
–这样我们将会用到所以聚合函数
SELECT id , sex , GROUP_CONCAT(username),
COUNT(*) AS numUser,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age,
FROM table
GROUP BY sex;

WITH ROLLUP 配合聚合函数加在语句结束后，可以对聚合函数进行统计，即COUNT(*)类型是可以统计总和，MAX()类型的统计最大数，MIN()类型的统计最小数，SUM()类型的总和。

having语句对分组结果进行二次筛选,必须配合分组进行使用，不能单独使用

比如查询sex，GROUP_CONCAT()，组中总人数，组中最大年龄、年龄总和和按照性别分组

SELECT id , sex , GROUP_CONCAT(username),
COUNT(*) AS numUser,
MAX(age) AS max_age,
SUM(age) AS sum_age,
FROM table
GROUP BY sex;

–基于以上条件，我们可以加上having语句，进行二次筛选

SELECT id , sex , GROUP_CONCAT(username),
COUNT() AS numUser,
MAX(age) AS max_age,
SUM(age) AS sum_age,
FROM table
GROUP BY sex
HAVING COUNT()>2;

这样我们就可以筛选出人数大于二的分组，我们还可以复合条件筛选

SELECT id , sex , GROUP_CONCAT(username),
COUNT() AS numUser,
MAX(age) AS max_age,
SUM(age) AS sum_age,
FROM table
GROUP BY sex
HAVING COUNT()>2 AND MAX(age)>60;

这样我们就可以筛选出人数大于二的分组且最大年龄年龄大于60的分组

如果语句有条件，我们应该在把条件写在前面做出第一次筛选

例如：查询id>=2的用户

SELECT id , sex , GROUP_CONCAT(username),
COUNT() AS numUser,
MAX(age) AS max_age,
SUM(age) AS sum_age,
FROM table
WHERE id>=2
GROUP BY sex
HAVING COUNT()>2 AND MAX(age)>60;

ORDER BY对查询结果排序

按照id降序排列DESC，默认是升序排列ASC，主要NULL是最小的

例如：SELECT * FROM table ORDER BY id；
或者：SELECT * FROM table ORDER BY id ASC；
这样默认是升序排列或者加上ASC标识升序排列

降序排列DESC，
例：SELECT * FROM table ORDER BY id DESC；·
这样就可以按照id降序排列

按照多个字段排序
按照年龄升序，id降序排列
例：SELECT * FROM table ORDER BY age ASC，id DESC；
如果有第三个条件，以此类推以逗号分隔，规则是以第一个条件为主，如果第一个条件相同时，则按第二个条件进行排序

那么我们综合来用一下
例：比如查询id>=2， sex，GROUP_CONCAT()，组中总人数，组中最大年龄、年龄总和和按照性别分组,然后对分好的组进行二次筛选，如COUNT(*)>2 AND MAX(age)>60，然后再对分组的结果进行排序 age ASC，id DESC；

SELECT id , sex , GROUP_CONCAT(username),
COUNT() AS numUser,
MAX(age) AS max_age,
SUM(age) AS sum_age,
FROM table
WHERE id>=2
GROUP BY sex
HAVING COUNT()>2 AND MAX(age)>60
ORDER BY age ASC，id DESC;

ORDER BY条件可以实现随机提取
例：对记录的随机提取
SELECT * FROM table ORDER BY RAND();

LIMIT 限制查询结果显示条数(分页语句的核心)

一、显示条数
SELECT * FROM table LIMIT 3;
他得到的是结果集之后的前三条
如果我先做了降序排列
SELECT * FROM table ORDER BY id DESC LIMIT 3；
那么他显示的将是降序排列之后的前三条

二、LIMIT 位置，偏移量 进行显示限制查询的结果

显示第一条记录
例：SELECT * FROM table LIMIT 0,1;
0为查询的起始的位置，1为查询的偏移量：当前0位，偏移1位，查询结果就为第一条数据

如果想要查询某个位置开始的多条数据
查询第五条开始的后五条数据
例：SELECT * FROM table LIMIT 5,5;
他显示的将是第6条到第10条数据

例：查询语句完整形式，所有查询语句条件

SELECT id , sex , GROUP_CONCAT(username),
COUNT() AS numUser,
MAX(age) AS max_age,
SUM(age) AS sum_age,
FROM table
WHERE id>=2
GROUP BY sex
HAVING COUNT()>2 AND MAX(age)>60
ORDER BY age ASC，id DESC
LIMIT 0，2;

更新数据UPDATE
这时的更新语句就可以配合ORDER BY 和 LIMIT 使用

例：更新用户名为4位，让其已有年龄-3(这里用到了模糊查询)
UPDATE table SET age=age-3 WHERE username LIKE ‘_ _ _ _’;

例：更新前三条记录，用ORDET BY，让年龄加10
UPDATE table SET age=age+10 LIMIT 3;

在更新和删除操作时，使用LIMIT只能有一个参数，如果写两个，就会报错
就像这样：UPDATE table SET age=age+10 LIMIT 0,3;

例：按id降序排列，更新前三条年龄+10
UPDATE table SET age=age+10 ORDER BY id DESC LIMIT 3;

例：删除用户性别为男的用户，按照年龄降序排列，删除前一条记录
DELETE FROM table WHERE SEX=‘男’ORDER BY age DESC LIMIT 1;

连接查询
将两个或者两个以上的表按照某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或两个以上的表示使用的。当不同的表中存在相同有一段字段时，可以通过该字段连接这几个表

内连接查询（JOIN/CROSS JOIN/INNER JOIN）
通过ON连接条件，显示两个表中复合连接条件的记录

例：查询用户表 id，username
省份表 proName
SELECT user.id username proName FROM user , provinces;
如果两张表中有相同却代表含义的字段，这时我们必须要进行区别，用 (表名.字段)，或者用别名来区分

例：user表中的proId对应省份表中的id
SELECT user.id user.name proName FROM user , provinces WHERE user.proId=provinces.id;

例：内连接示例
–查询user表中的id，username，email，sex
–查询provices表中proName
SELECT u.id , u.username , u.email , u.sex , p.proName
FROM user AS u
INNER JOIN provinces AS p
ON u.proId=p.id;

SELECT u.id , u.username , u.email , u.sex , p.proName
FROM user AS u
CLOSS JOIN provinces AS p
ON u.proId=p.id;

SELECT u.id , u.username , u.email , u.sex , p.proName
FROM user AS u
JOIN provinces AS p
ON u.proId=p.id;

三种查询方式是一样的

也可以加上条件，这和之前的单表查询是一样的，只是这里多加了一个类似筛选的内连接查询
例：
–查询user表的id，username，sex
–查询provinces表的proName
–条件是user的性别为男的用户

SELECT u.id，u.username，u.sex，p.proNmae，（COUNT(*) AS num），（GROUP_CONCAT(username)）//加上组中人数和组中人的名字
FROM user AS u
JOIN provinces AS P
ON u.proId=p.id
WHERE u.sex=‘男’;
（GROUP BY p.proName）//根据省份的名字进行分组

还可以进行二次筛选
例：用HAVING选出组中人数多于等于1的记录
SELECT u.id，u.username，u.sex，p.proNmae，COUNT() AS num，GROUP_CONCAT(username)
FROM user AS u
JOIN provinces AS P
ON u.proId=p.id
WHERE u.sex=‘男’
GROUP BY p.proName
HAVING COUNT()>=1；

加上排序，按照id升序排列
SELECT u.id，u.username，u.sex，p.proNmae，COUNT() AS num，GROUP_CONCAT(username)
FROM user AS u
JOIN provinces AS P
ON u.proId=p.id
WHERE u.sex=‘男’
GROUP BY p.proName
HAVING COUNT()>=1
ORDER BY u.id ASC；

限制显示条数，显示前两条LIMIT
SELECT u.id，u.username，u.sex，p.proNmae，COUNT() AS num，GROUP_CONCAT(username)
FROM user AS u
JOIN provinces AS P
ON u.proId=p.id
WHERE u.sex=‘男’
GROUP BY p.proName
HAVING COUNT()>=1
ORDER BY u.id ASC
LIMIT 0,2;

连接三张表查询

内连接查询可以连接多张表，两张表如此也可以三张表
–查询user表的id，username，sex
–查询provinces表的proName
–查询学校的shNmae
–条件是user的性别为男的用户
SELECT u.id，u.username，u.sex，p.proNmae，s.shName
FROM user AS u
JOIN provinces AS P
ON u.proId=p.id
JOIN school AS s
on u.sId=s.id
WHERE u.sex=‘男’;

外连接查询
一、左外连接：（LEFT JOIN）显示左表的全部记录及右表符合连接条件的记录

–左外连接以左表为主表，与内连接相似
–查询user表中的id，username，email，sex
–查询provices表中proName

SELECT u.id , u.username , u.email , u.sex , p.proName
FROM user AS u
LEFT JOIN provinces ASp
ON u.proId=p.id;

如果在省份表中没有用户表当中记录的数据，则会显示null

二、右外连接：（RIGHT JOIN）显示右表的全部记录以及左表符合条件的记录

–右外连接以右表为主表，与内连接相似
–查询user表中的id，username，email，sex
–查询provices表中proName

SELECT u.id , u.username , u.email , u.sex , p.proName
FROM user AS u
RIGHT JOIN provinces ASp
ON u.proId=p.id;

如果在用户表中没有省份表当中记录的数据，则会显示null

内连接比外连接用的多

外键操作：保证数据的完整性和一致性，属性表中的特殊字段
父表和子表必须使用相同的存储引擎，且禁止显示临时表，数据表的存储引擎必须是InnoDB

创建部门表table（主表）
字段：id depName depDesc

CREATE TABLE IF NOT EXISTS table（
id TINYINT UNSIGNED AU_TOINCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
）ENGINE=INNODB；

插入部门（市场部，教学部，运营部，督导部）

创建员工表table1（子表）
CREATE TABLE IF NOT EXISTS TABLES（
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED //部门id
）ENGINE=INNODB;

插入数据（king，1）（ls，2）（ww，3）

这时两个表是没有外键的，如果把其中一个部门删除，但是部门下的员工还是存在的，这肯定是不合理的，所以要用到外键

创建部门表table（主表）
字段：id depName depDesc

CREATE TABLE IF NOT EXISTS table（
id TINYINT UNSIGNED AU_TOINCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
）ENGINE=INNODB；

插入部门（市场部，教学部，运营部，督导部）

创建员工表table1（子表）
CREATE TABLE IF NOT EXISTS TABLES（
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED //部门id
FOREIGN KEY (depId） REFERENCES table（id）
//将表table1字段depId设置成表table字段id的外键
）ENGINE=INNODB;
插入数据（king，1）（ls，2）（ww，3）

一定是先有主表，再有子表

要删除主表的记录，如果子表当中存在外键的约束，也就是子表当中有主表的记录，是删除不了的，只有把子表中与主表要删除字段相关的记录删除，才能删除主表的记录

如果我们要向子表的当中插入数据，我们必须也要参照主表的记录，也就是外键字段的记录，插入一个外键所指主表字段中没有的字段是不会成功的。

删除外键

定义外键的时候可以给外键加上一个名称
CONSTRAINT 名称 FOREIGN KEY 子表字段 REFERENCES table 主表字段；

外键名称最好写成 主表_子表 的形式，方便我们对外键的认识

删除外键形式
ALTER TABLE 子表名称 DROP FOREIGN KEY 外键名称；

添加外键
ALTER TABLE 子表表名 ADD CANSTRAINT 外键名称 FOREIGN KEY（主表字段） REFERENCES table（子表字段）
前提是两张表关联起来是完整的，否则添加外键是不成功的

CASCADE：从父表删除或更新且自动删除或者更新子表中匹配的行

FOREIGN KEY (主表字段） REFERENCES table（子表字段） ON DELETE CASCADE
表示当删除父表的记录时，子表对应的外键列记录也会删除

FOREIGN KEY (主表字段） REFERENCES table（子表字段） ON DELETE CASCADE ON UPDATE CASCADE
上述后面加上的CASCADE表示父表删除记录时，子表对应的外键列的记录也会相应的删除

SET NULL ：从父表删除或更新，并设置子表中的外键列为null。如果使用该选项，必须保证子表没有指定NOT NULL

FOREIGN KEY (主表字段） REFERENCES table（子表字段） ON DELETE SET NULL ON UPDATE SET NULL

SET NULL的作用就是父类删除和更新时，对应的外键列就会被设置成NULL

联合查询（UNION/UNION ALL）

UNION：会去掉相同的记录
UNION ALL：是简单的合并在一起

例：查询两张表中的username

SELECT username FROM table1 UNION SELECT username FROM table2；
这样我们会得到两张表的username字段的合并显示，UNION是不显示重复的记录的

SELECT username FROM table1 UNION ALL SELECT username FROM table2；
同样的我们也会得到两张表的username字段的合并显示，但UNION ALL 是会显示重复的记录的

要保证两张纸的查询数目要相同，比如前表查询两个字段，后表也要查询两个字段

子查询

子查询是将一个查询语句嵌套在另一个语句中。内存查询语句的查询结果，可以作为外层查询语句提供条件

使用[NOT]IN的子查询
例：用子查询查询某表内某字段在另一张表中我们要查询的字段
如要通过部门表的id查询结果查询员工表的员工id和username
SELECT id，username FROM usertable WHERE depId IN（SELECT id FROM deptable）；
这样就可以显示我们要查询的结果

NOT IN查询的是IN相反的记录

使用比较运算符的子查询
=、>、<、>=、<=、<>、!=、<=>
例：查询奖学金表中id=1的所指分数，通过该分数查询学生表当中超过该分数的学生
SELECT id,usename FROM student WHERE score>=(SELECT level FROM scholar WHERE id=1);
这样就可以查询到想要的结果，其他的比较运算符是一样的也可以这么用

使用[NOT]EXISTS的子查询
EXISTS：表示子查询当中查询到结果，为真，才能执行外部查询；否则不执行外部查询
[NOT]EXISTS：与EXISTS相反

例：查询部门表中一个没有的部门id，，通过该id查询员工的id，username
SELECT id，username FROM usertable WHERE depId IN（SELECT * FROM deptable WHERE id=5）；
–上述id=5加入不存在，那么也就不会执行外部的语句，如果存在才会执行外部语句，类似于多加了一个判断

使用ANY|SOME或者ALL的子查询

例：查询所有奖学金的学员的id，username
SELECT id，username FROM student WHERE score>=ANY(SELECT level FROM scholar);

–将子查询的结果插入到表当中

INSERT 表名（字段）SELECT …;

正则表达式查询（REGEXP ‘匹配方式’）

常用的匹配方式：^,匹配以什么开始的记录，不区分大小写
例：匹配字符开始的部分，查询用户名以t开始的用户
SELECT * FROM user WHERE username REGEXP ‘^t’;

常用的匹配方式：$，匹配以什么结尾的记录例：例：匹配字符结束的部分，查询用户名以g结尾的用户SELECT\ast FROMuserWHEREusernameREGEX{P}^{\prime }g$’;

常用的匹配方式： . ， 点表示字符串中的任意一个字符，包括回车和换行
例：查询user表当中r和g中间有两个字符的用户
SELECT *FROM user WHERE username REGEXP ‘r…g’;
或模糊查询
SELECT *FROM user WHERE username LIKE ‘r_ _g’;

常用的匹配方式:[字符集合]-----字符集合内包含任意字符的的所有记录
例：查询user表当中存在字符 [xph] 的用户
SELECT *FROM user WHERE username REGEXP ‘[xph]’;

常用的匹配方式:[^字符集合]-----除了字符集合内字符之外的记录
SELECT *FROM user WHERE username REGEXP ‘[^xph]’;

常用的匹配方式: s1 | s2 | s3 匹配s1/s2/s3中任意一个字符串
SELECT *FROM user WHERE username REGEXP s1 | s2 | s3;

常用的匹配方式:

• ：代表0个1个或者多个其前的字符

• ：代表1个或者多个其前的字符
  String{N} :字符串出现了N次
  字符串{M,N} ：字符串至少出现了M次，最多出现N此

MySQL中的运算符的使用

算数运算符：MySQL支持数据转换，但注意对null做运算，结果都为null
如：SELECT 1+1,2-1,1*2,2/1，3%8,；

比较运算符：结果只有两种：true或false----1或0
=、!=、> 、>=、<、<=、is null、is not null、bBETWEEN AND、NOT BETWEEN、IN、NOT IN、LIKE、NOT LIKE、REGEXP

逻辑运算符：
&&或者AND（与、并且）
SELECT 1&&1；为真 SELECT 1&&0；为假

| | 或者OR（或、或者）
SELECT 1| |1；为真

! 或者NOT（非、取反）
SELECT !1；为假 SELECT !0；为真

XOR（异或、不同为真）
SELECT 1 XOR 1,1 XOR 0,0 XOR 1,0 XOR 0; 结果为：假、真、真、假

运算符优先级
见图

数学函数库
CEIL()----进1取整----SELECT CEIL（1.2）；结果就为2了
同理：
FLOOR()----舍一取整
MOD----取余数
POWER()----幂运算
ROUND()----四舍五入
TRUNCATE()----数字截取
ABS()----取绝对值
PI()----圆周率
RAND()或RAND(X)----返回0~1之间的随机数
SIGN(x)----返回x的符号，-1为负数，1为正数，0返回0
EXP(x)----计算某数的x次方

字符串函数库
CHAR_LENGTH(S):返回字符串的字符数
LENGTH:返回字符串的长度
例：SELECT CAHR_LENGTH(‘XPH’),LENGTH(‘XPH’);

CONCAT(S1,S2,S3…):将字符串合并成一个字符串
CONCAT_WS(X,S1,S2,S3…):以指定分隔符来连接字符串（分隔符为null，结果为null）
UPPER(S)/UCASE(S):将字符串转换为大写的字符串
LOWER(S)/LCASE(S):将字符串转换为小写的字符串
LEAF(S,number)/RIGHT(S,number):拿到左边或者右边的几个字符
LPAD(S1,LEN,S2)/RPAD(S1,LEN,S2):将字符串S1用S2填充到指定位置上，长度为LEN

日期时间函数
CURDATE()/CURDATE_DATE():返回当前日期
CRUTIME(),CRURENT_TIME():返回当前时间
NOW()：返回当前日期和时间
MONTH(D):返回月份
MONTHNAME(D):返回日期中月份名称，返回january
DAYNAME:返回日期是几，如Monday
DAYOFWEEK(D)：返回一周内的第几天，1代表星期天
WEEKDAY(D)：返回日期是星期几，0是星期一
WEEK(D)：一年中的第多少个星期
YEAR(D)：返回年份值----------------SELECT YEAR(NOW())；//下面都可以这么用
HOUR（T）：返回小时值
MINUTE(T)：返回分钟值
SECOND(T)：返回秒数
DATEDIFF(D1,D2)：计算两个日期之间相隔的天数

条件判断函数和系统函数
IF(EXRP,V1,V2)：如果表达式EXRP成立，返回结果V1，否则V2
IFNULL(V1,V2)：如果V1的不为空，就显示V1的值，否则V2
CASE WHERE exp1 THEN V1 [ WHEN exp2 THEN V2] [ ELSE vn ] END；
CASE表示函数的开始，END表示函数结束，如果表达式exp1成立时，返回v1；如果表达式exp2成立时，返回v2的值。以此类推，最后遇到ELSE时，返回vn的值。

例：SELECT id，username，score，IF(score>=60,‘及格’，‘不及格’)FROM user；

即可以显示score大于60 的则为及格，小于60的为不及格

常用的系统信息函数
SELECT VERSION():显示数据库的版本号
SELECT CONNECTION_ID():显示服务器的连接数
SELECT DATABASE(),SCHAEMA():显示当前数据库名
SELECT USER(),SYSTEM_USER():显示当前用户
SELECT CHARSET(STR):显示字符串STR的字符集
SELECT COLLATION(STR):显示字符串STR的校验字符集
SELECT LAST_INSERT_ID():显示最近生成的AUTO_INCREMENT值

其他常用函数
MD5（str）：返回一个32位长度的字符串
PASSWORD（str）：密码算法
ENCODE（str，pwd_str）：加密结果是一二

索引
索引分类：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

创建表的时候创建索引
例：创建普通索引( INDEX, KEY)
CREATE TABLE table （
id TINYINT UNSIGNED ,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
）;
-----in_id为索引名称，id为索引的值，同理id_username也为名称

例：创建唯一索引：
CREATE TABLE table （
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20),
UNIQUE KEY uni_id(card)
）;
-----唯一索引为: UNIQUE KEY

例：创建全文索引
CREATE TABLE table （
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20),
FULLTEXT INDEX full_userDesc(userDesc)
）;
-----全文索引为FULLTEXT INDEX

例：创建单列索引
CREATE TABLE table （
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username1 VARCHAR(20),
username2 VARCHAR(20),
username3 VARCHAR(20),
INDEX mul_u1_u2_u3(username1,username2,username3)
INDEX mul_u1(username1)
）;

例：创建多列索引
CREATE TABLE table （
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username1 VARCHAR(20),
username2 VARCHAR(20),
username3 VARCHAR(20),
INDEX mul_u1_u2_u3(username1,username2,username3)
）;

例：创建空间索引
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;

如何删除索引
DROP 索引类型 索引名称 ON 表名；
例：DROP INDEX in_id on table；

在已存在的表中创建索引
CREATE 索引类型 索引名称 ON 表名（字段）；
例：CREATE INDEX in_id on table（id）；

ALTER TABLE 表名 ADD INDEX 索引名称（字段名称）；