Mysql数据库知识总结

sql语言

1.1sql概述
简答题：sql 的概念及分类？
Sql 是专门操作数据库的增删改查的一种语言。
结构化查询语言(Structured Query Language)简称 SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
Sql 的分类：
1、数据定义语言(DDL Data Definition Language)：针对数据库或表做创建、 修改和删除操作。关键字：CREATE,ALTER,DROP 和 SHOW。
2、数据查询语言(DQL Data Query Language):针对表中数据做查询操作，关键 字：SELECT。
3、数据操作语言(DML Data Manipulation Language):针对表中数据做添加、修改和删除操作，关键字：INSERT、UPDATE 和 DELETE。
4、数据控制语言(DCL Data Control language)：通过 GRANT 或 REVOKE 关键字实现权限控制。
5、事务控制语言（TCL Transcation Cnontrol language）：通过 COMMIT、 SAVEPOINT、ROLLBACK 命令确保被 DML 语句影响的表的所有行及时得以更新。

1.2DDL语言
DDL数据定义语言：主要是针对数据库和数据库表进行增删改查的操作。
Create创建 alter修改 drop删除 show 查询

1）数据库常用命令：

#mysql注释：
#单行注释：# –
#多行注释：/**/
#1、展示所有数据库
SHOW DATABASES;
#2、创建数据库:一个连接下面数据库名不能重复
CREATE DATABASE mysql0131;
#3、查看当前使用的数据库版本
SELECT VERSION();
#4、切换数据库和使用数据库
USE mysql0131;
#5、查看当前数据库中的所有表
SHOW TABLES;
#6、查看其他数据库中的表
SHOW TABLES FROM xiaomi1229;
#7、查看当前正在使用的数据库
SELECT DATABASE();
#8、查看创建数据库的创建语句
#CREATE DATABASE mysql0131 /*!40100 DEFAULT CHARACTER SET utf8 */
SHOW CREATE DATABASE mysql0131;
#9、创建数据库时指定数据库的编码集
CREATE DATABASE mysql0121 DEFAULT CHARACTER SET utf8;
#10、删除数据库
DROP DATABASE mysql0121;

2）数据库表常用数据类型
整数类型:tinyint、int
浮点类型:float 、double
布尔类型:（MySQL 是没有 booblean 类型的）
在 MYSQL 中一般布尔类型都用 int 去代替了，1 代表 true，0 代表 false
字符串类型:
varchar：（相当java里面的string类型）可变长度类型，无论创建表时指定多大长度，存储数据的时候会按照数据
的实际长度进行空间占用，设置的最大值目的就是为设定最大范围。
char：固定长度类型，当存储数据的长度小于创建表时指定的长度时，则用空格填充，
而不会自动调整。
日期类型：
Date 年月日
Time 时分秒
Datetime（标准日期类型） 年月日时分秒
Timestamp（时间戳）年月日时分秒
Datetime Timestamp 区别：
Datetime ：最大值是 9999 年 12 月 31 23:59:59、默认值 null
Timestamp ：最大值是 2037 年 12 月 31 23:59:59、默认值当前系统时间

3）mysql数据库表约束：
对数据库表字段的一种规范要求
常用的约束：
主键：PRIMARY KEY 字段非空并且唯一
非空：NOT NULL 字段不能为空
唯一：UNIQUE 字段值在一个表里面不能重复
默认：DEFAULT 字段设置的默认值
自增长：AUTO_INCREMENT 整形数据每次自增加1，从1开始
外键：forgin key 两表关联的关系设置

4）数据库表的DDL语句

/*
mysql数据库表创建的时候需要设置表中列对应的数据类型
通常我们将表的列称之为字段
/
/
创建一个学生表：姓名 年龄 性别 生日
CREATE TABLE 表名(
字段名称 字段数据类型(默认长度) ,
字段名称 字段数据类型(默认长度) ,
…
);
/
#1、创建表的语句：
CREATE TABLE students(
sid INT ,
sname VARCHAR(30),
age INT ,
gender INT ,
birthday DATETIME
);
/
mysql数据库表约束：对数据库表字段的一种规范要求
常用的约束：
主键：PRIMARY KEY 字段非空并且唯一
非空：NOT NULL 字段不能为空
唯一：UNIQUE 字段值在一个表里面不能重复
默认：DEFAULT 字段设置的默认值
自增长：AUTO_INCREMENT 整形数据每次自增加1，从1开始
外键：forgin key 两表关联的关系设置

/
/
创建一个商品表：
编号 主键
名称 不能重复 非空
颜色 不能为空
价格 默认为0
*/
CREATE TABLE products(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(60) NOT NULL UNIQUE,
color VARCHAR(20) NOT NULL,
price DOUBLE DEFAULT 0
);

#2、查看表结构:表字段名 类型 约束 …信息
DESC students;
#3、查看创建表的语句
/*
CREATE TABLE products (
pid int(11) NOT NULL AUTO_INCREMENT,
pname varchar(60) NOT NULL,
color varchar(20) NOT NULL,
price double DEFAULT ‘0’,
PRIMARY KEY (pid),
UNIQUE KEY pname (pname)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
*/
SHOW CREATE TABLE products;
#4、删除表
DROP TABLE students;
#6、给表添加一个字段
ALTER TABLE products ADD size VARCHAR(20);
#7、修改表字段的类型及约束
ALTER TABLE products MODIFY size INT;
DESC products;
#8、修改表中的字段名
ALTER TABLE products CHANGE size model VARCHAR(50);
#9、删除表的某个字段
ALTER TABLE products DROP model;
#10、修改表名称
ALTER TABLE products RENAME TO prods;
二、DML语言
DML数据库的操作语言：对数据库表中的数据进行增加 修改 删除的操作。
2.1增加数据：insert
增加的语法：
Insert [into] 表名 (字段1，字段2…)value/values(字段名的值…)
使用insert语法可以对数据库表增加单条或者多条数据。

代码示例：
准备工作
/*
创建一个员工表：
编号 姓名 岗位 薪资 入职日期 绩效奖金 部门编号
*/
CREATE TABLE emp(
eid INT PRIMARY KEY AUTO_INCREMENT,#编号
ename VARCHAR(30) NOT NULL,#姓名
job VARCHAR(30) NOT NULL ,#岗位
salay DOUBLE ,#基本工资
hiredate DATE NOT NULL,#入职日期
money DOUBLE ,#绩效奖金
dept_no INT #部门编号
);
增加练习
#增加一条员工数据:完整的语法结构
INSERT INTO emp
(eid,ename,job,salay,hiredate,money,dept_no)
VALUES
(1,“张三”,“销售员”,12300,“2020-01-01”,2000,1);
#增加员工：姓名 和岗位(没有非空约束的字段会自动设置null值)
INSERT INTO emp (ename,job,hiredate)
VALUES(“张三丰”,“经理”,“2019-12-11”);
#增加一条员工信息：增加所有字段，字段名可以省略
#此时所有的字段都必须按照表结构全部赋值
#主键字段设置为null即可实现自增加
INSERT INTO emp
VALUES(NULL,“赵四”,“文员”,8000,“2018-02-05”,500,2);
#into 可以省略，但是通常不建议省略
INSERT emp
VALUES(NULL,“王百万”,“老板”,80000,“2015-02-05”,5000,NULL);
#一次增加多条数据
INSERT INTO
emp (ename,job)
VALUE
(“李思琪”,“文员”),
(“王小二”,“销售员”),
(“光熙”,“保洁”);
#values可以使用value替换
INSERT INTO
emp (ename,job)
VALUES
(“李师师”,“经理”),
(“小二黑”,“销售员”),
(“许家”,“经理”);

2.2修改数据：update
语法：
Update 表名
set 字段名1=“字段值1”，字段名2=字段值2…
[where 字段名=字段值 and 字段名=字段值]
其中：Update修改 set设置 where表示条件 and多个条件的连接
代码示例：

/*
2、修改员工数据
1)修改表中所有行数据：不需要where 条件
2）修改表中多个字段数据：多个字段之间使用，隔开
3）修改数据的时候根据多个条件：多个条件使用and关键字连接
*/
#将员工表中所有人的绩效设置为2000
UPDATE emp SET money=2000;
#将员工表中所有人的基本工资设置为20000绩效为5000
UPDATE emp SET salay=20000,money=5000;
#将4号员工的基本工资修改为50000绩效修改为null
UPDATE emp SET salay=50000,money=NULL WHERE eid=4;
#将2号部门的入职日期是2018-02-05的员工的基本工资修改为15000
UPDATE emp SET salay=15000
WHERE dept_no=2 AND hiredate=“2018-02-05”;

2.3删除数据：delete
语法：
Delete from 表名 [where 字段=字段值 and 字段名=值]
当没有where关键字的时候是删除表中所有数据，where条件表示根据指定条件删除数据。

Truncate删除：删除表中所有的数据

Delete和truncate区别：
1、delete和truncate都可以实现表数据的删除
2、Delete可以删除单条数据，truncate不可以
3、Delete删除表不会删除索引，truncate删除表数据同时删除索引。
注意事项：
在进行数据库删除操作的时候，一定要确认删除，谨慎操作！

代码示例：

/*
3、删除员工数据
*/
#删除小二黑员工数据
DELETE FROM emp WHERE ename=“小二黑”;
#删除基本工资15000绩效是5000数据
DELETE FROM emp WHERE salay=15000 AND money=5000;
#删除所有数据:删除数据但是不会删除索引
DELETE FROM emp;
#删除所有数据：将索引一起删除重建索引
TRUNCATE TABLE emp;

三、DQL语言
DQL数据查询语言：select

查询的分类：
1、不带条件查询：select 字段1，字段2 …from 表名
2、带条件查询：select 字段1，字段2…from 表名 where 字段名=值 and 字段名=值

示例代码：

/*
1、不带条件查询员工表数据
*/

#1、创建emp数据库表
SHOW CREATE TABLE emp;

CREATE TABLE emp (
eid INT(11) NOT NULL AUTO_INCREMENT,
ename VARCHAR(30) NOT NULL,
job VARCHAR(30) NOT NULL,
salay DOUBLE DEFAULT NULL,
hiredate DATE DEFAULT NULL,
money DOUBLE DEFAULT NULL,
dept_no INT(11) DEFAULT NULL,
PRIMARY KEY (eid)
) ENGINE=INNODB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
#2、查询所有员工的姓名
SELECT ename FROM emp;
#3、查询所有员工的姓名和基本工资
SELECT ename,salay FROM emp;
#4、查询员工的所有信息
SELECT eid,ename,job,salay,hiredate,money,dept_no FROM emp;
#当查询表中所有字段的时候可以使用替换所有字段*
#通常在实际开发的时候不建议使用*查询：查询速率慢 浪费服务器资源
SELECT * FROM emp;

#5、查询员工的姓名和工资，展示的时候使用“姓名”“工资”
#使用as给字段起别名,As可以省略
SELECT ename AS “姓名” ,salay AS “工资” FROM emp;
SELECT ename AS 姓名 ,salay AS 工资 FROM emp;
SELECT ename 姓名 ,salay 工资 FROM emp;
#as可以给字段或者表起别名
SELECT e.ename ,e.salay FROM emp AS e;

#6、查询所有员工的部门编号
SELECT dept_no FROM emp;

#7、查询所有员工的不重复的部门编号
#distinct表示查询的时候除去字段重复的值
SELECT DISTINCT dept_no FROM emp;

#8、查询所有员工的姓名和绩效，如果绩效为空则用0表示
#ifnull (字段,替换的值)
SELECT ename 姓名 ,IFNULL(money,0) 绩效 FROM emp;

#9、查询员工姓名及其岗位： 如我是张三，我的岗位是销售员
SELECT ename,job FROM emp;
#字符串拼接concat(str1,str2,str3…)
SELECT CONCAT(“我是：”,ename,",我的岗位是：",job) 个人简介 FROM emp;

#10、今年业绩不错，每个员工工资sal涨个1000，把涨薪前后的结果显示出来
#字段可以直接参与运算操作
SELECT ename 姓名, salay 涨薪前,salay+1000 涨薪后 FROM emp;
#11、查询每个员工的薪资（基本工资+绩效）
SELECT ename 姓名,salay+IFNULL(money ,0) 薪资 FROM emp;

/*
2、带条件查询

比较运算符：> < 不等于<> not(3>0)
between 3 and 5 相当于>= <=

逻辑运算：       和and  或or  

模糊查询：like _表示一个字符 %一个或者多个字符
*/
#12、查询出基本工资在10000~20000之间的员工信息
SELECT * FROM emp WHERE salay >=10000 AND salay <=20000;
SELECT * FROM emp WHERE salay BETWEEN 10000 AND 20000;

#13、查询部门编号为1或者2的员工信息
SELECT * FROM emp WHERE dept_no =1 OR dept_no =2;
SELECT * FROM emp WHERE dept_no IN (1,2);

#14、查询出部门编号不是2部门的员工信息
SELECT * FROM emp WHERE dept_no <>2;
SELECT * FROM emp WHERE NOT(dept_no=2);

#15、查询出绩效为null的员工信息
SELECT * FROM emp WHERE money IS NULL;
#16、查询出绩效不为null的员工信息
SELECT * FROM emp WHERE money IS NOT NULL;

#17、查询出名字以王开头，并且两个字的员工信息
SELECT * FROM emp WHERE ename LIKE “王_”;
#18、查询出名字是三个字的员工信息
SELECT * FROM emp WHERE ename LIKE “___”;
#19、查询出名字以王开头的员工信息
SELECT * FROM emp WHERE ename LIKE “王%”;
#20、找到职位最后是以"员"结尾的员工信息
SELECT * FROM emp WHERE job LIKE “%员”;
#21、查询出名字里面带小的员工信息
SELECT * FROM emp WHERE ename LIKE “%小%”;

四、高级查询
1、排序查询
/*
1、排序查询：
语法： select 查询字段 from 表名 order by 字段 asc /desc
asc表示升序：从小到大排序,默认值asc可以省略
desc表示降序：从大到小排序
注意事项：

当排序的字段是多个字段的时候：
先根据第一个字段进行排序，当第一个字段值相等的时候，
再按照第二个字段排序，当第二个字段值相等的时候再按照
第三个字段排序
以此类推
/
#按照基本工资降序显示员工信息
SELECT * FROM emp ORDER BY salay DESC;
#按照部门编号升序显示员工信息
SELECT * FROM emp ORDER BY dept_no ;
#按照部门编号升序，再按照入职日期降序，最后按照薪资降序排列，显示员工信息
SELECT * FROM emp ORDER BY dept_no ASC,hiredate DESC,salay DESC;
2、聚合查询
/
2、聚合查询
mysql常用的聚合函数：
max()查询最大值
min()查询最小值
sum()查询字段值的总和
avg()查询字段的值平均值
count()查询表数据总条数
*/

#求出员工的最高薪资
SELECT MAX(salay) FROM emp;
#求最低薪资
SELECT MIN(salay) FROM emp;
#求最高薪资的员工信息:聚合查询的结果可以作为一个变量值使用
SELECT * FROM emp WHERE salay =(SELECT MAX(salay) FROM emp);
#求员工的平均薪资
SELECT AVG(salay) FROM emp;
#查询大于平均薪资的员工信息
SELECT * FROM emp WHERE salay >(SELECT AVG(salay) FROM emp);
#求员工的薪资总和
SELECT SUM(salay) FROM emp;
#求员工的数量:数据的总行数
SELECT COUNT(eid) FROM emp;
SELECT COUNT() FROM emp;
SELECT COUNT(1) FROM emp;
3、分组查询
/
3、分组查询：
select 字段 ，聚合查询 from 表名 group by 字段 [having 聚合查询] ;
having和wehere类似表示分组查询的条件
分组查询使用场景：涉及 到每个 每组 每日
/
#统计每个部门下的员工数量
SELECT dept_no, COUNT() FROM emp GROUP BY dept_no ;
#统计每个岗位下的员工平均薪资
SELECT job,AVG(salay) FROM emp GROUP BY job;
#统计每个部门的最高薪资
SELECT dept_no,MAX(salay) FROM emp GROUP BY dept_no;

#查询每个部门中薪资大于10000的员工人数
SELECT dept_no,COUNT(*) FROM emp
WHERE salay >10000 GROUP BY dept_no;

#查询出部门人数大于2的部门及其人数，并按照人数降序排列
SELECT dept_no,COUNT() FROM emp
GROUP BY dept_no HAVING COUNT()>2
ORDER BY COUNT(*) DESC;

#查询每个部门下最高薪资大于10000且名字中含有小的员工信息
SELECT * FROM emp
WHERE ename LIKE “%小%”
GROUP BY dept_no HAVING MAX(salay)>10000;
4、分页查询
/*
4、分页查询
select 字段 from 表名 limit index,size;
参数说明：
index 查询表数据的起始索引值，索引从0开始
size表示分页查询的数据条数
注意：
其中index可以省略，此时表示查询第一页数据，也就是从索引为0开始查询。

*/

#将我们的员工表数据进行分页查询，设置每页显示三条
#查询一页数据每页显示三条
SELECT * FROM emp LIMIT 0,3;
#查询第二页数据
SELECT * FROM emp LIMIT 3,3;
#查询第三页数据
SELECT * FROM emp LIMIT 6,3;

#limit 的参数可以省略前面的第一个参数
#查询薪资的前三名员工信息
SELECT * FROM emp ORDER BY salay DESC LIMIT 3;
#查询公司入职最早的员工信息
#如果没有重复数据可以使用limit求最值
SELECT * FROM emp ORDER BY hiredate LIMIT 1;
#如果表中有重复数据使用聚合查询实现
SELECT * FROM emp WHERE hiredate=(SELECT MIN(hiredate) FROM emp);
5、子查询
/*
5、子查询
将查询的结果作为一个条件使用的操作
涉及的关键字：
all 所有
any 任意

*/
#1、查询薪资大于平均薪资的员工信息
SELECT * FROM emp WHERE salay >(SELECT AVG(salay) FROM emp);

#2、查询大于0号部门所有员工的薪资的员工信息
#第一种方式
SELECT * FROM emp WHERE salay >
(SELECT MAX(salay) FROM emp WHERE dept_no =0);
#第二种方式
SELECT * FROM emp WHERE salay >
ALL(SELECT salay FROM emp WHERE dept_no =0);

#3、查询大于任意一位经理工资的员工信息
SELECT * FROM emp WHERE salay >
(SELECT MIN(salay) FROM emp WHERE job=“经理”);
SELECT * FROM emp WHERE salay >
ANY (SELECT salay FROM emp WHERE job=“经理”);
#4、查询与王百万岗位和薪资一样的员工信息
SELECT * FROM emp
WHERE job=(SELECT job FROM emp WHERE ename=“王百万”)
AND salay =(SELECT salay FROM emp WHERE ename=“王百万”)
AND ename <> “王百万”;