数据库-MySql

MySql

什么是数据库？

一般是一个数据库系统可以分为：

数据库（Database，DB）是按照数据结构来组织、存储和管理数据的仓库集合。从广义来说只要能存储数据的都可以称之为数据库。

数据库管理系统（Database Management System，DBMS）就是 操作和管理数据库的应用软件，用于建立、使用和维护数据库（持久化存储、优化读写、保证数据的有效性等）。

为什么要使用数据库？

• 首先数据可以永久的保存
• 提供了接口对数据进行操作和检索
• 支持SQL语句，可用于复杂的查询数据
• 方便对数据的日常维护和管理

数据库有哪些？

数据库可以分为三大类

关系型数据库(RDBMS)

是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据，是当前应用最广泛的数据库管理系统。

关系数据库中常见的：表，列，行

例如常见的：MySQL、Oracle、SQL Server都是关系数据库。

非关系数据库(NoSQL)

NoSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。

例如常见的：Redis就是NoSQL数据库

SQL+NoSQL : NewSQL

NewSQL数据库是一种开源软件产品，相较于传统关系型数据库和NoSQL，它既能够使用SQL语句来查询数据，同时具备现代化，分布式，高容错，基于云的集群架构。

我们具体学习Mysql

了解基本类型

常用类型：

• int：整型
• double：浮点型，例如 double(5,2)表示最多 5 位，其中必须有 2 位小数，即最大值为 999.99；
• decimal：泛型型，在表单线方面使用该类型，因为不会出现精度缺失问题；
• char：固定长度字符串类型；(当输入的字符不够长度时会补空格)
• varchar：固定长度字符串类型；
• text：字符串类型；
• blob：字节类型；
• date：日期类型，格式为：yyyy-MM-dd；
• time：时间类型，格式为：hh:mm:ss
• timestamp：时间戳类型；

1，MySQL 创建数据库

可以使用create命令进行创建数据库

CREATE DATABASE 数据库名;

CREATE TABLE IF NOT EXISTS `tb`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `date` DATE,
   PRIMARY KEY ( `id` ) //主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

2，MySQL删除数据库

可以使用drop命令进行删除数据库

DROP DATABASE 数据库名;

3，MySQL插入数据

可以使用insert into values sql语句进行插入数据

INSERT INTO 表名称 VALUES (值1, 值2,....)

insert into 表名 (元素1，元素2...)values(值1，值2...)

4，MySQL查询数据

在MySQL数据库中查询数据通用的 SELECT 语法

SELECT <目标列名序列> [distinct] -- 需要哪些列
	FROM <表名> [JOIN <表名> ON <连接条件>] -- 来自哪些表
	[WHERE <行选择条件>] -- 根据什么条件
	[GROUP BY <分组依据列>]
	[HAVING <组选择条件>]
	[ORDER BY <排列依据列>]

查询并展示表中的所有元素

select * from 表名

查询展示指定元素

select (名称1，名称2) from 表名

SELECT column_name,column_name
FROM table_name

distinct去重

在SQL查询中可能出现许多重复值，不过，有时您也许希望仅仅列出不同（distinct）的值。

使用关键字distinct用于返回唯一不同的值

SELECT DISTINCT 列名称 FROM 表名称

select语句联合 where使用

SELECT * from tb WHERE author='馒头';

WHERE 子句常用的查询条件

-- 比较大小 =, >, >=, <=, <, <>, != --------------------------------------------
-- 查询计算机系所有学生的姓名
SELECT Sname FROM Student WHERE Sdept='计算机系'

-- 查询考试成绩大于90的学生的学号、课程号和成绩
SELECT Sno, Cno, Grade FROM SC WHERE Grade > 90

-- 确定范围--------------------------------------------
/*
注意：
BETWEEN ... AND ... ：包括边界
NOT BETWEEN ... AND ... ：不包括边界
*/
-- 查询学分在2～3之间的课程的课程名称、学分和开课学期
SELECT Cname, Credit, Semester FROM Course WHERE Credit BETWEEN 2 AND 3
-- 等价于
SELECT Cname, Credit, Semester FROM Course 
WHERE Credit >= 2 AND Credit <=3

-- 查询学分不在2～3之间的课程的课程名称、学分和开课学期
SELECT Cname, Credit, Semester FROM Course 
WHERE Credit NOT BETWEEN 2 AND 3
-- 等价于
SELECT Cname, Credit, Semester FROM Course
WHERE Credit < 2 OR Credit > 3

-- 查询出生在2000年的学生的全部信息
SELECT * FROM Student
WHERE Sbirthday BETWEEN '2000-01-01' AND '2000-12-31'

-- 确定集合  IN, NOT IN--------------------------------------------
-- 查询‘计算机系’和‘机电系’学生的学号、姓名和所在系
SELECT Sno, Sname, Sdept FROM Student
WHERE Sdept IN ('计算机系', '机电系')

-- 查询不在‘计算机系’和‘机电系’学生的学号、姓名和所在系
SELECT Sno, Sname, Sdept FROM Student
WHERE Sdept NOT IN ('计算机系', '机电系')

-- 字符串匹配--------------------------------------------
/*
WHERE中的模糊查询 LIKE, NOT LIKE
匹配串中有如下四种通配符：
_：匹配任意一个字符
%：匹配0到多个字符
[]：匹配[ ]中任意一个字符。如[abcd]表示匹配a, b, c, d中的一个。
	若要比较的字符是连续的，也可以用连字符'-'表达，比如匹配 abcd中任意一个
	可写成 [a-d]
[^ ]：不匹配[ ]中的任意一个字符，用法与[ ]一致，也可以用'-'表示连续字符
*/

-- 查询姓‘李’的学生的学号、姓名和所在系
SELECT Sno, Sname, Sdept FROM Student
WHERE Sname LIKE '李%'

-- 查询姓名中第二个字是‘冲’的学生的学号、姓名和所在系
SELECT Sno, Sname, Sdept FROM Student
WHERE Sname LIKE '_冲%'

-- 查询学号最后不是‘2’或者‘3’的学生的学号、姓名和所在系
SELECT Sno, Sname, Sdept FROM Student
WHERE Sno NOT LIKE '%[23]'
/*
注：mysql的LIKE好像没有 []和 [^]的用法，但可以用 REGEXP和 NOT REGEXP
	来用正则表达式进行匹配
*/
-- 如mysql下，查询学号最后不是‘2’或者‘3’的学生的学号、姓名和所在系
SELECT Sno, Sname, Sdept FROM Student 
WHERE Sno NOT REGEXP '[23]$'

/*
ESCAPE：可以指定一个字符，将该字符后面的一个字符当作普通字符看待，
	可以用来匹配 '_' 和 '%' 等通配符
*/
-- 查询msg字段中包含 "30%" 的记录
WHERE msg LIKE '%30!%%' ESCAPE '!'

-- 涉及空值的查询  IS NULL, IS NOT NULL--------------------------------------------
-- 查询还没有考试的学生的学号、相应的课程号
SELECT Sno, Cno FROM SC
WHERE Grade IS NULL

-- 查询有备注的学生的学号、姓名和备注
SELECT Sno, Sname, Memo FROM Student
WHERE Memo IS NOT NULL

AND 和 OR 运算符

我们常常用AND和OR对于条件记录进行锅炉多重查询 一般用于where中

-- 多重条件查询  AND, OR--------------------------------------------
-- 查询‘计算机系’有备注的学生的学好、姓名、所在系和备注
SELECT Sno, Sname, Sdept, Memo FROM Student
WHERE Memo IS NOT NULL AND Sdelt = '计算机系'

-- 查询 ‘机电系’和‘计算机系’2000年出生的学生的学号、姓名、所在系和生日
SELECT Sno, Sname, Sdept, Sbirthday FROM Student
WHERE (Sdept = '计算机系' OR Sdept = '机电系')
AND Sbirthday BETWEEN '2000-01-01' AND '2000-12-31'

Order By语句

ORDER BY 语句用于对结果集进行排序 语句默认按照升序（asc）进行排序，如果想要倒叙排序可以使用降序（desc）关键字

-- 按照姓名升序排序
SELECT Sname FROM Student WHERE Sdept='计算机系' ORDER BY Sname

5，MySQL更新数据

是使用 UPDATE 命令修改 MySQL 数据表数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

-- 修改tb 表中id为3 的名称为title 学习
UPDATE tb SET title='学习' WHERE id=3;

6，MySQL删除数据

DELETE 语句从 MySQL 数据表中删除数据

DELETE FROM 表名 [WHERE 条件]

DELETE FROM tb WHERE id=3

MySQL常用的聚合函数

COUNT(*)：统计表中元组的个数  函数计算数量
COUNT([DISTINCT] <列名>)：统计本列的列值个数，DISTINCT表示去掉重复值后再统计
SUM(<列名>)：计算列值的和值（必须是数值类型）
AVG(<列名>)：计算列值的平均值（必须是数值类型）
MAX(<列名>)：得到列的最大值
MIN(<列名>)：得到列的最小值
Round()函数:用于把数值字段舍入为指定的小数位数。
Group By  分组查询
.....

COUNT：当需要纵向统计时可以使用 COUNT()。

-- 查询 emp 表中记录数：
SELECT COUNT(*) AS cnt FROM tb

SUM 和 AVG：当需要纵向求和时使用 sum()函数。

-- 查询所有分数和：
SELECT SUM(dept) FROM stu;
-- 查询平均分 
SELECT AVG(dept) FROM stu;

注意：

avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可

分组查询GROUP BY

当需要分组查询时需要使用 GROUP BY子句，例如 查询考试成绩大于90的学生的学号、课程号和成绩按照课程号分组

SELECT Sno, Cno, Grade
FROM SC WHERE Grade > 90
GROUP BY Cno

Having

having常常与Group by 连用.

SELECT sno, SUM(sal)
FROM sc
GROUP BY sno
HAVING SUM(sal) > 90;

注意：Where 是对分组前记录的条件，如果某行记录没有满足 Where子句的条件，那
么这行记录不会参加分组；而 HAVING 是对分组后数据的约束。

分页查询LIMIT

limit：是用于限定查询语句结果的起始行 和 条数 的语句

SELECT * FROM sc LIMIT 起始行,大小

-- 查询 5 行记录，起始行从 0 开始
SELECT * FROM sc LIMIT 0, 5;

那么如果一页分5条数据，要查询第三页的应该怎么查呢？

这里我们记住limit是起始行包含

第一页记录起始行为 0，一共查询 5 行；
第二页记录起始行为 5，一共查询 5行；
第三页记录起始行为 10，一共查询 5 行；

MySQL多表连接查询

按功能分连接方式

• 内连接 ：查询多个表中相同的记录
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接： 查询一个表有，另一个表没有的记录
  • 左外连接
  • 右外连接
  • 全外连接
• 交叉连接

基础常见的内连接查询sql92的语法

SELECT	查询列表
FROM 	待链接的多个表
WHERE 	连接条件 [和筛选条件]

SELECT sname,dept
FROM sc ,deptno
WHERE sc.name=deptno.name;

也可以取别名，用于区别和解决表名太多太长问题

select 	查询列表
from 	表名 as 别名, ...  #可以使用as 或者使用空格区分就好
where 	连接条件等

select sname,dept
from sc s ,deptno d
where s.name=d.name;

而在sql99语法连接中使用了Join

Join用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

SELECT 查询列表
FROM 表1别名 
	【连接类型】 join 表2 别名 
	on 连接条件
where xxx 

• 内连接：

  inner和sql92的等值连接是等效的

• 外连接：

  • 左外：left [outer]
  • 右外：right [outer]
  • 全外：full [outer]

• 交叉连接：cross

-- 内连接
SELECT sname,dept
FROM sc s 
INNER JOIN deptno d
ON sc.name=deptno.name;
-- 或者可以省略 inner
SELECT sname,dept
FROM sc s 
JOIN deptno d
ON sc.name=deptno.name;

-- 外连接
-- 左外连接
-- 左连接：显示左表student所有记录，如右表中没有与之
-- 匹配的项则以NULL值代替。
SELECT *
FROM sc s 
LEFT JOIN ts t
ON s.sid = t.id;	

当左表（即sc表）的sid字段找不到ts表中对应的id进行连接时，它也仍会保留这一记录，而右表的记录则全部设置为Null。

总而言之左表的数据不管是否满足连接条件，都至少会保留在最终查询集的一条记录之中

-- 右外连接
-- 右连接：显示右表所有记录，如左表中没有与之
-- 匹配的项则以NULL值代替。
SELECT sname
FROM sc s 
RIGHT JOIN ts t
ON s.sid=t.id
WHERE t.id IS NULL;

-- 全连接
-- 完全连接：显示两张表的并集，如果其中一张表的记录
-- 在另一张表中没有匹配的行，则对应的数据项填充NULL

SELECT sname
FROM sc s 
FULL JOIN ts t  
ON  s.sid=t.id

-- 交叉连接
-- 交叉连接：一张表中的数据依次取出分别与另一张表中的
-- 每条数据挨个组合，最后记录数量为两张表记录数的乘积
-- 即返回笛卡尔积（即所有组合的可能），左表数据为m条，右表数据为n条，最终查询集数据为m * n条

SELECT * 
FROM sc 
CROSS JOIN ts

小tip

• 在书写查询语句时应避免使用select *，如果数据量很大要使用的仅仅为几个字段会造成白白浪费数据库资源，还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。
• SQL运行顺序 (1)from 　　(2) join 　　(3) on 　　(4) where 　　(5)group by 　　(6) avg,sum… （组函数） 　　(7)having 　　(8) select 　　(9) distinct 　　(10) order by
• 可以多用limit例如在查询某些数据中的第一条，根据时间升序排序后limit 1返回时间最小的那一条就好，又或者在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。
• 尽量少使用子查询语句，而使用连接查询语句替代
• 在使用连接查询语句时join的表不宜过多 根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。
• 特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。
• 而索引也并不是越多越好，根据阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个。
• 在使用Group By分组操作通常与Having连用 为了提升效率可以使用where条件在分组前，就把多余的数据过滤掉缩小范围提升效率