关于MySQL数据库，你需要知道这些

数据库相关操作

1. 建立数据库
   CREATE DATABASE 数据库名 CHARSET utf8;
创建数据库时，判断不存在，再创建：CREATE DATABASE IF NOT EXISTS 数据库名
   设定编码格式可以省略，会使用默认编码格式

2. 查看所有数据库
   SHOW DATABASES;

3. 查看数据库的建立方式，查看建立数据库的相关信息
   SHOW CREATE DATABASE 数据库名;

4. 查看正在使用的数据库，通过函数方式
   SELECT database();

5. 删除数据库drop
   DROP DATABASE数据库名;

6. 使用数据库 use 语句制定使用的数据库
   USE 数据库名;

7. 修改数据库，alter 目前只能修改编码格式
   ALTER DATABASE数据库名 CHARSET utf8;

数据库中表结构的操作

• 建立数据表

规范:
       1.所有的表结构,建立时都至少需要包含一个字段。
       2.所有的表结构,建立时必须添加引擎设定,编码格式,以及注释说明。

CREATE TABLE user1(
	name1 char(30),
	name2 char(30),
	......
) ENGINE innodb CHARSET utf8 COMMENT'用户表1';

• 查看数据库中所有数据表
  SHOW TABLES ;

• 模糊查询数据表
  SHOW TABLES LIKE ‘%s%’;

• 查询表结构的建立方式
  SHOW CREATE TABLE 数据表名;

• 查看表结构的字段信息
  DESC 数据表名;

• 修改表结构
  –修改引擎 InnoDB MyISAM
  ALTER TABLE 数据表名 ENGINE MyISAM;

• 修改编码格式 utf8 GBK
  ALTER TABLE 数据表名 CHARSET= gbk;

• 修改注释说明
  ALTER TABLE 数据表名 COMMENT ‘用户表结构123’;</code

• 删除数据表
  DROP TABLE 数据表名;

• 修改表字段
  ALTER TABLE 表名 CHANGE 旧字段名称 新字段名称 数据类型 属性 约束条件 注释

ALTER  TABLE user2 CHANGE age nianling SMALLINT UNSIGNED DEFAULT 180 COMMENT '年龄字段';

• 修改字段的属性以及约束条件等设定
  ALTER TABLE 表名 MODIFY 字段名 数据类型 属性 约束条件 注释

ALTER TABLE user2 MODIFY name VARCHAR(50) NOT NULL COMMENT '用户年龄字段';

如果使用CHANGE ,新旧两个字段名称设定相同,也可以达到只修改字段设定的效果

• 新增表字段 （三种语法）

一、默认语法格式,新增字段在所有字段最后
ALTER TABLE 表名 ADD 字段名称 字段数据类型 字段属性 字段约束条件 字段注释

ALTER TABLE user2 ADD name1 VARCHAR(30) NOT NULL COMMENT '姓名1字段';

二、设定在所有字段之前,添加新字段
ALTER TABLE 表名 ADD 字段名称 字段数据类型 字段属性 字段约束条件 字段注释 FIRST

ALTER TABLE user2 ADD name2 VARCHAR(30) NOT NULL COMMENT '姓名1字段' FIRST;

三、设定在指定字段之后,添加新字段
ALTER TABLE 表名 ADD 字段名称 字段数据类型 字段属性 字段约束条件 字段注释 AFTER 指定字段名称

ALTER TABLE user2 ADD name3 VARCHAR(30) NOT NULL COMMENT '姓名1字段' AFTER name;

• 删除表字段
  ALTER TABLE 表名 DROP 字段名称;

ALTER TABLE user2 DROP name3;

---

• 添加数据
  INSERT INTO 表名称 (字段1,字段2) VALUES (数据1,数据2);

// 新增两条数据
INSERT INTO user2 (name,addr) VALUES ('张三','北京'),('李四','郑州');

注意:
1,需要输入数据的字段使用逗号间隔,最后一个字段不要加逗号
2,输入的数据的顺序必须与设定的字段顺序一一对应
3,可以一次输入多个数据,数据使用小括号包裹,使用逗号间隔,最后一个数据以分号结束

• 删除数据
  DELETE FROM 表名称 WHERE 约束条件

// 删除name='张三' 的数据
DELETE FROM user2 WHERE name = '张三';

如果想要删除所有数据使用 DELETE FROM 表名 或者使用TRUNCATE删除数据 实际操作时,将表结构完全删除,再建立一张完全一致的,新的表结构,没有数据信息。
语法：TRUNCATE [TABLE] 表名;

• 修改数据
  UPDATE 表名称 SET 字段1 = 数据1，字段2 = 数据2 … WHERE 条件;

// 张三的地址修改为上海
UPDATE user2 SET addr = '上海' WHERE name = '张三';

– 判断唯一符合条件的数据,一般使用id主键值
– 符合条件的数据,可以是一条,也可以是多条

• 查询数据基本语法
  SELECT * FROM 表名称 WHERE 条件
GROUP BY 字段
HAVING 聚合条件
ORDER BY 字段 DESC|ASC
LIMIT 起始序号，数量;

 WHERE 约束条件
-- 对字段数据的约束条件

-- 比较运算符
 >  <  >=  <= 

SQL语句的 等于比较  不等判断
             =       !=
             
对于NULL的判断

SQL语句中的NULL表示不确定数值
is null 		判断数据是null
is not null  判断数据不是null

逻辑运算符

逻辑与  and  &&(不推荐使用)
逻辑或  or   ||(不推荐使用)
逻辑非  not  ! (不推荐使用)

查询语句详解以及案例分析

首先创建一张数据表方便后边的练习：

CREATE TABLE IF NOT EXISTS user1(
--主键id,一般设定无符号位属性,配合主键以及自增属性
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键字段',
--姓名,不确定字符长度,可以有重复数据,不能null
name VARCHAR(30) NOT NULL COMMENT '用户姓名字段',
--年龄,一般为tinyint 设定无符号位,可以添加默认值
age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '用户年龄字段,默认值为18',
-- 性别,一般为枚举,设定输入数据,一般为男女保密,可以设定默认值
sex ENUM('男','女','保密') DEFAULT '保密' COMMENT '用户性别字段,枚举类型,允许输入值为男,女',
-- 爱好,一般为集合,可以多选,设定输入数据
hoddy SET('吃','喝','玩','乐') COMMENT '用户爱好字段,聚合类型,允许输入数值为吃,喝,玩,乐',
-- 地址,不确定字符长度,不为空
addr VARCHAR(50) NOT NULL COMMENT '用户地址字段',
-- 有关钱的字段尽量使用DECIMAL
pay DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '用户薪资',
-- 手机号,11位数字,如果存储为数值,只能是BIGINT类型,会占用大量存储空间,设定为CHAR(11),手机号不能重复
phone CHAR(11) UNIQUE KEY NOT  NULL COMMENT '用户手机号字段' ,
-- 邮箱,不确定字符长度,不能重复
email VARCHAR(50) UNIQUE KEY NOT NULL COMMENT '用户邮箱字段' ,
-- 注册时间,自动获取时间
regdate TIMESTAMP COMMENT '用户注册时间字段,自动获取执行时间'
) ENGINE innodb CHARSET utf8 COMMENT '用户表结构2';

查询练习：

• 查询 薪资大于10000的人员信息

SELECT id,name,sex,city,dep,pay FROM user1 WHERE pay > 10000;

• 查询 薪资大于10000,并且年龄小于25岁的人员信息

SELECT id,name,age,sex,city,dep,pay FROM user1 WHERE pay > 10000 and age < 25;

• 查询北京市薪资超过15000的人员信息

– 人员信息是需要显示的内容 由SELECT 和 FROM语句之间字段的设定来决定查询结果中显示的内容

– 数据的数量,也就是符合条件的查询结果,由WHERE约束条件决定

SELECT id,name,age,sex,city,dep,pay FROM user1 WHERE pay > 15000 and city = '北京';

• 查询北京市,年龄小于25岁,薪资超过15000的人员信息

SELECT id,name,age,sex,city,dep,pay FROM user1 WHERE city = '北京' and age < 25 and pay > 150000;

以上几个案例分析：
– 1,分析需求
– 显示内容,约束条件
– 显示内容由,SELECT 和 FROM 之前设定的内容决定
– 约束条件由,WHERE 之后的语句决定
– 2,分析逻辑结构
– 3,WHERE语句,是针对数据库中字段中存储数据的约束条件

• 模糊查询 LIKE
  WHERE 字段 LIKE ‘%_关键词’

_ 占位符 表示一个长度的任意字符
% 占位符 表示任意长度的任意字符

• 查询所有姓张的人员信息,id,姓名,城市：
  姓张:姓名中第一个字是张,之后内容不限制(不限制内容,不限制字符个数)

SELECT id,name,city FROM user1 WHERE name LIKE '张%';

• 查询姓名中包含张字的人员信息,id,姓名,城市：
  姓名中包含 的 字 , 只要name字段的数据中有的就可以
  之前有什么内容,之后是什么内容,不限制(不限制内容,不限制字符个数)

SELECT id,name,city FROM user1 WHERE name LIKE '%的%';

• 分组查询

将设定的字段,按照数据显示,相同的数据为一个分组,在分组之中,只显示查询到的第一条数据信息

SELECT id,name,sex,age,dep FROM user1 GROUP BY dep; 

• 聚合函数

COUNT(字段)  获取分组中,数据的数量
MAX(字段)    获取分组中,数据的最大值
MIN(字段)    获取分组中,数据的最小值
AVG(字段)    获取分组中,数据的平均值
SUM(字段)    获取分组中,数据的总和

对于分组之内的所有数据信息,执行函数效果

分组查询与聚合函数

分组查询是将数据,按照设定的字段显示,相同数据为一个分组,每个分组中有符合数据条件的所有数据,默认只显示查询到的第一条数据

每个分组中都有多条数据

聚合函数为,对数据中的数据,执行函数效果
聚合函数写在SELECT 与 FROM 语句之间
只是作为显示内容,显示聚合函数的执行结果
与其他显示的字段,与查询的其他条件都没有关系,只与分组有关
聚合函数小括号中,设定的为字段,执行聚合函数效果的字段

• 每个部门中的最大薪资

SELECT dep,MAX(pay) FROM user1 GROUP BY dep;

• 每个部门的员工平均年龄

SELECT dep,AVG(age) FROM user1 GROUP BY dep;

• 每个部门的员工一个月需要的薪资总数

SELECT dep,SUM(pay) FROM user1 GROUP BY dep;

• 每个城市的员工总人数

SELECT city,COUNT(city) FROM user1 GROUP BY city;

• 分组查询与聚合函数

分组查询是将数据进行分组,聚合函数是对分组内的数据执行函数效果

-- 1,先按照数据分组

-- 例如按照城市字段分组,先按照上海数据执行分组

SELECT * FROM user1 WHERE city = '上海';

-- 2,针对分组中的数据信息,执行聚合函数效果

-- 例如,求薪资最大值
-- 是在所有城市是上海的分组数据中,在查询薪资的最大值

SELECT name FROM user1;

-- 显示员工人数超过10人的城市

-- 显示 city COUNT(city)

-- 分组 city

-- 条件 人数 > 10 对于聚合函数执行结果的条件约束,

-- 使用HAVING来对聚合函数执行约束条件
SELECT city,COUNT(city) FROM user1 GROUP BY city HAVING  COUNT(city) > 10;

强调：
对于字段的约束条件,使用WHERE
对于聚合函数的约束条件,使用HAVING
语法位置在GROUP BY 语句之后

• 查询员工平均工资高于15000的城市 显示城市和平均薪资

SELECT city,AVG(pay) FROM user1 GROUP BY city HAVING AVG(pay) > 15000;

• WHERE与GROUP BY 配合使用

-- 查询部门当中年龄大于25岁的员工人数,大于3人的部门,显示部门和人数

-- 1,dep , COUNT(dep)

-- 2,年龄大于25岁 age > 25 对于字段的约束条件 使用WHERE

-- 3,查询部门 GROUP BY dep

-- 4,员工人数 聚合函数 COUNT(dep)

-- 5,员工人数,大于3人的部门 COUNT(dep) > 3 对于聚合函数的约束条件 使用HAVING


SELECT dep,COUNT(dep) FROM user1
WHERE age > 25
GROUP BY dep
HAVING COUNT(dep) > 3 ;

• LIMIT 分页显示

LIMIT语句需要两个参数
第一个参数为 每页页面显示首条数据的键名
公式 (当前页数 - 1) * 每页显示数据数量(第二个参数)。
第二个参数为 每页页面显示数据的数量。
对于查询结果,每条数据都有一个键名,是从0开始的整数数据，此数据与id主键值等都无关,只是查询结果的顺序有关

• 查询所有部门是PHP的人员信息 每页显示5条数据

SELECT * FROM user1 WHERE dep = 'php' LIMIT 0 , 5;

• 子查询

需求:查询薪资大于小龙的人员信息

分析实现过程：

1,查询出name为小龙的人员的薪资
2,以此薪资作为条件,查询薪资大于此薪资的人员信息
1, SELECT pay FROM user1 WHERE name = '小龙';
2, SELECT * FROM user1 WHERE pay > 12000;
将第一步的查询结果,作为第二步的查询条件
此种查询就是子查询
语法格式,将子查询语句,使用小括号包裹

SELECT * FROM user1 WHERE pay > (SELECT pay FROM user1 WHERE name = '小龙');

• 查询比小龙年轻的人员信息

SELECT * FROM user1 WHERE age < (SELECT age FROM user1 WHERE name = '小龙');

• 查询比小龙年轻,还挣得多的人员信息

SELECT * FROM user1 
WHERE age < (SELECT age FROM user1 WHERE name = '小龙') AND pay > (SELECT pay FROM user1 WHERE name = '小龙');

• 子查询形式

-- 如果子查询的结果为多个数值,可以是用子查询数据语句 
-- IN ANY ALL

-- IN() 等于其中任意一个数值
-- IN() 小括号中,可以是数值,也可以是子查询语句
 
-- >ANY()	>任意一个数值 大于最小值,就是大于任意一个 
-- <ANY()   <任意一个数值 小于最大值,就是小于任意一个

-- >ALL()   >所有数值  大于最大值,才是大于所有数值
-- <ALL()   <所有数值  小于最小值,才是小于所有数值

• 查询每个部门的最大年龄,使用聚合函数

SELECT * FROM user1 WHERE age IN(SELECT MAX(age) FROM user1 GROUP BY dep);

联合查询 INNER JOIN 内联接

多个表结构的数据查询

节省存储空间

将大量重复使用的文字内容,转存为数值,将数值作为id主键值,存储对应的文字内容在另一张表结构中
可以将重复的文字内容转化为数字,节省存储空间
查询数据时,需要多个表结构的联合查询

单独存储文字的表结构,称为父表
综合存储数据信息的表结构,为子表


注意事项：

1,子表中只存储父表中id主键值,所有文字的内容都存储在父表中,如果查询时需要显示文字内容,内容来源只能是父表,联合查询中,一定有一个或者多个字段的来源是父表结构,是父表中存储文字内容的字段

2,联合查询中,语法规定所有的字段必须添加表结构名称,以表示字段的来源

3,INNER JOIN 表示执行联合查询的父表表名

4, ON 表示子表与父表的关联   数 = 数 子表存储数值的字段 = 父表id主键值字段

首先准备几张表用户联合查询练习：

-- 1,建立性别父表
CREATE TABLE sexTable(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键字段',
sex VARCHAR(2) COMMENT '存储性别字段'
)ENGINE InnoDB CHARSET UTF8 COMMENT '存储性别文字内容父表';

-- 2,建立城市父表
CREATE TABLE cityTable(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键字段',
city VARCHAR(10) COMMENT '存储城市名称字段'
)ENGINE InnoDB CHARSET UTF8 COMMENT '存储城市名称文字内容父表';

-- 3,建立部门父表
CREATE TABLE depTable(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键字段',
dep VARCHAR(10) COMMENT '存储部门字段'
)ENGINE InnoDB CHARSET UTF8 COMMENT '存储部门文字内容父表';

-- 4,建立存储多项字段的子表
-- 特别注意:为了建立外键
-- 存储父表中对应的id主键值的字段,数据类型,要求必须与父表id主键字段的数据类型完全一致
-- 只能是数据长度不一致
CREATE TABLE user2(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
name VARCHAR(30) NOT NULL COMMENT '员工姓名',
age TINYINT UNSIGNED DEFAULT 18 COMMENT '员工年龄,默认18岁',
sex TINYINT UNSIGNED DEFAULT 1 COMMENT '员工性别,1--保密,2--男,3--女,默认值是1--保密',
city TINYINT UNSIGNED NOT NULL COMMENT '员工所在城市,1--北京,2--上海,3--广州,4--重庆,5--天津',
dep TINYINT UNSIGNED NOT NULL COMMENT '员工所在部门,1--PHP,2--JAVA,3--UI/UE,4--WEB',
pay FLOAT(8,2) DEFAULT 5000 COMMENT '员工薪资'
)ENGINE = InnoDB CHARSET = UTF8 COMMENT = '员工信息表';

• 尝试将城市字段,显示为文字内容

SELECT cityTable.city FROM user2
INNER JOIN cityTable 
ON user2.city = cityTable.id;

• 部门显示为文字内容

SELECT depTable.dep FROM user2
INNER JOIN depTable
ON user2.dep = depTable.id;

• 一次多个父表的联合查询

语法:需要将 INNER JOIN … ON 写多个

SELECT u.id,u.name,u.age,s.sex,c.city,d.dep,u.pay FROM user2 as u
INNER JOIN sexTable as s
ON u.sex = s.id
INNER JOIN cityTable as c
ON u.city = c.id
INNER JOIN depTable as d
ON u.dep = d.id;

总结:

1,查询语句中,所有需要显示的字段一定要表名字段的来源,也就是要添加字段的表结构名称
 语法:表结构名称.字段名称

2,联合查询中,一定有一个或者多个字段,来源是父表
父表名称.存储文字的字段名称

3,INNER JOIN 表示联合查询的父表名称

4,ON 字表父表的关联
字表.存储父表id的字段 = 父表.id 

外联接 left right

insert user2(name,age,sex,city,dep,pay) value ('July',29,5,5,2,10000);
-- 这条数据当中,性别字段,存储的数值,父表没有对应的id值

-- 如果使用 INNER JOIN 内联接查询,将没有对应的id主键值,会不显示该条数据

-- 此时,必须使用外联接

-- 外联接如果没有匹配的数据,将会显示为NULL

-- 外联接 :  left 完全显示左表的内容   FROM 之后的表结构
--         right  完全显示右表的内容   JOIN 之后的表结构

SELECT u.id,u.name,u.age,s.sex,u.pay FROM user2 as u
LEFT JOIN sexTable as s
ON s.id = u.sex ;


SELECT u.id,u.name,u.age,s.sex,u.pay FROM sexTable as s
LEFT JOIN user2 as u
ON s.id = u.sex ;

无限极菜单

-- 在一个表结构中,实现联合查询

-- 在一个表结构中,使用上一级内容对应的id主键值,作为下一级内容的字段数据

-- 无限极菜单表结构

-- 目前字段有3个,id主键字段,name字段存储菜单的文字内容,nameId字段,存储上一级菜单文字内容对应的id主键值

-- 一个表结构中,又有父表,又有子表

-- nameId字段数据类型与id主键字段数据类型必须一致

再新建一个分类表：

CREATE TABLE category (
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键字段',
name VARCHAR(10) NOT NULL COMMENT '菜单内容字段',
nameId SMALLINT UNSIGNED NOT NULL COMMENT '存储上级内容id主键值字段,数据类型需要与id主键数据类型一致' 
)ENGINE InnoDB CHARSET utf8 COMMENT '无限极菜单表结构';

-- 写入数据

-- 1,写入一级菜单内容

-- name字段写入的是菜单文字内容,nameId字段,要求存储入数值0

INSERT category (name,nameId)
VALUE
('家用电器' , 0),
('手机' , 0),
('电脑' , 0);


-- 2,写入第一个一级菜单 家用电器,对应的二级菜单内容
-- name字段存储对应的文字内容 
-- nameId字段存储 一级菜单 家用电器 对应的id主键值 1

INSERT category (name,nameId)
VALUE
('电视' , 1),
('空调' , 1),
('洗衣机' , 1);
-- 3,写入 二级菜单 电视,对应的三级菜单内容

-- name字段对应的是文字内容
-- nameId字段对应上一级菜单 二级菜单 电视 对应的id主键值 4


INSERT category (name,nameId)
VALUE
('曲面电视' , 4),
('超薄电视' , 4),
('OLED电视' , 4);

-- 4,写入 二级菜单 空调,对应的三级菜单内容

-- name字段对应的文字内容
-- nameId字段对应上一级菜单 二级菜单 空调 对应的id主键值 5

INSERT category (name,nameId)
VALUE
('壁挂空调' , 5),
('柜式空调' , 5),
('中央空调' , 5);

-- 5,写入 二级菜单 洗衣机,对应的三级菜单内容

-- name字段对应的文字内容
-- nameId字段对应上一级菜单 二级菜单 洗衣机 对应的id主键值 6

INSERT category (name,nameId)
VALUE
('滚动洗衣机' , 6),
('洗烘一体洗衣机' , 6),
('波轮洗衣机' , 6);

-- 无限极查询语句无需修改

• 执行无限极查询,将数值1,转化为对应id主键值1存储的name字段文字

SELECT cat.id,cat.name,c.name FROM category as cat
LEFT JOIN (SELECT id,name FROM category) as c
ON cat.nameId = c.id;