【MySQL】MySQL 数据库与简单 SQL 语句使用
本文已参加「新人创作礼」活动，一同开启创作之路。
前语
本博文专用于软件创新实验室 MySQL 数据库与简单 SQL 句子 课堂，请上课的同学们先自行安装 MySQL，可参阅群里发的视频，也能够参阅博文MySQL安装教程，在开发这条路上，数据库将会一直陪伴着咱们！
简介
什么是数据库？
数据库是一个以某种有组织的方法存储的数据调集，它是一个按数据结构来存储和办理数据的核算机软件体系。了解数据库的一种最简单的办法是将其想象为一个文件柜，这个文件柜仅仅一个存储数据的空间，而数据库便是这么一个空间。
数据库办理体系（Database Management System, DBMS）是用于创立、办理、和保护数据库时所运用的软件，用以办理数据库，必定程度上，用户都是在操作 DBMS，所以日常的数据库概念一般便是指 DBMS。
常见的数据库软件 DBMS 有甲骨文的 Oracle DB、微软的 Access、MongoDB等，
什么是MySQL？
MySQL 是一款安全、跨渠道、高效的，并与 PHP、Java 等主流编程言语严密结合的数据库体系。该数据库体系是由瑞典的 MySQL AB 公司开发、发布并支撑，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。
MySQL 的标志符号是一只名为 Sakila 的海豚，代表着 MySQL 数据库的速度、能力、准确和优秀本质。
特色
- 功用强大 MySQL 中供给了多种数据库存储引擎，各引擎各有所长，适用于不同的应用场合，用户能够挑选最合适的引擎以得到最高性能，能够处理每天拜访量超过数亿的高强度的查找 Web 站点。MySQL5 支撑业务、视图、存储过程、触发器等。
- 支撑跨渠道 MySQL 支撑至少 20 种以上的开发渠道，包括 Linux、Windows、FreeBSD 、IBMAIX、AIX、FreeBSD 等。这使得在任何渠道下编写的程序都能够进行移植，而不需求对程序做任何的修正。
- 运转速度快 高速是 MySQL 的显着特性。在 MySQL 中，运用了极快的 B 树磁盘表（MyISAM）和索引压缩；经过运用优化的单扫描多衔接，能够极快地完成衔接；SQL 函数运用高度优化的类库完成，运转速度极快。
- 支撑面向目标 PHP 支撑混合编程方法。编程方法可分为朴实面向目标、朴实面向过程、面句目标与面向过程混合 3 种方法。
- 安全性高 灵敏和安全的权限与暗码体系，允许根本主机的验证。衔接到服务器时，一切的暗码传输均选用加密方法，然后保证了暗码的安全。
- 成本低 MySQL 数据库是一种彻底免费的产品，用户能够直接经过网络下载。
- 支撑各种开发言语 MySQL 为各种盛行的程序设计言语供给支撑，为它们供给了许多的 API 函数，包括 PHP、ASP.NET、Java、Eiffel、Python、Ruby、Tcl、C、C++、Perl 言语等。
- 数据库存储容量大 MySQL 数据库的最大有用表尺度一般是由操作体系对文件大小的约束决议的，而不是由 MySQL 内部约束决议的。InnoDB 存储引擎将 InnoDB 表保存在一个表空间内，该表空间可由数个文件创立，表空间的最大容量为 64TB，能够轻松处理具有上千万条记载的大型数据库。
- 支撑强大的内置函数 PHP 中供给了很多内置函数，几乎涵盖了 Web 应用开发中的一切功用。它内置了数据库衔接、文件上传等功用，MySQL 支撑很多的扩展库，如 MySQLi 等，能够为快速开发 Web 应用供给便当。
SQL
1.什么是SQL？
Structured Query Language：结构化查询言语
其实便是界说了操作一切联系型数据库的规矩。每一种数据库操作的方法存在不一样的当地，称为“方言”。
2.SQL通用语法
1) SQL 句子能够单行或多行书写，以分号结尾。
2) 可运用空格和缩进来增强句子的可读性。
3) MySQL 数据库的 SQL 句子不区分大小写，关键字主张运用大写。
4) 3 种注释
* 单行注释: -- 注释内容 或 # 注释内容(mysql 特有)
* 多行注释: /* 注释 */
3. SQL分类
1) DDL(Data Definition Language)数据界说言语
用来界说数据库目标：数据库，表，列等。关键字：create, drop,alter 等
2) DML(Data Manipulation Language)数据操作言语
用来对数据库中表的数据进行增修正。关键字：insert, delete, update 等
3) DQL(Data Query Language)数据查询言语
用来查询数据库中表的记载(数据)。关键字：select, where 等
4) DCL(Data Control Language)数据控制言语(了解)
用来界说数据库的拜访权限和安全级别，及创立用户。关键字：GRANT， REVOKE 等
DDL：操作数据库、表
1. 操作数据库：CRUD
1. C(Create):创立
* 创立数据库：
* create database 数据库称号;
* 创立数据库，判别不存在，再创立：
* create database if not exists 数据库称号;
* 创立数据库，并指定字符集
* create database 数据库称号 character set 字符集名;
* 练习： 创立db4数据库，判别是否存在，并拟定字符集为gbk
* create database if not exists db4 character set gbk;
2. R(Retrieve)：查询
* 查询一切数据库的称号:
* show databases;
* 查询某个数据库的字符集:查询某个数据库的创立句子
* show create database 数据库称号;
3. U(Update):修正
* 修正数据库的字符集
* alter database 数据库称号 character set 字符集称号;
4. D(Delete):删去
* 删去数据库
* drop database 数据库称号;
* 判别数据库存在，存在再删去
* drop database if exists 数据库称号;
5. 运用数据库
* 查询当时正在运用的数据库称号
* select database();
* 运用数据库
* use 数据库称号;
2. 操作表
1. C(Create):创立
* 语法：
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
....
列名n 数据类型n
);
* 留意：终究一列，不需求加逗号（,）
* 数据库类型：
1) int：整数类型
* age int,
2) double:小数类型
* score double(5,2)
3) date:日期，只包括年月日，yyyy-MM-dd
4) datetime:日期，包括年月日时分秒 yyyy-MM-dd HH:mm:ss
5) timestamp:时刻错类型 包括年月日时分秒 yyyy-MM-dd HH:mm:ss
* 假如将来不给这个字段赋值，或赋值为null，则默认运用当时的体系时刻，来主动赋值
6) varchar：字符串
* name varchar(20):名字最大20个字符
* zhangsan 8个字符 张三 2个字符
* 创立表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
* 复制表：
* create table 表名 like 被复制的表名;
2. R(Retrieve)：查询
* 查询某个数据库中一切的表称号
* show tables;
* 查询表结构
* desc 表名;
3. U(Update):修正
1) 修正表名
alter table 表名 rename to 新的表名;
2) 修正表的字符集
alter table 表名 character set 字符集称号;
3) 添加一列
alter table 表名 add 列名 数据类型;
4) 修正列称号 类型
alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;
5) 删去列
alter table 表名 drop 列名;
4. D(Delete):删去
* drop table 表名;
* drop table if exists 表名 ;
- 客户端图形化工具：SQLYog，Navicat
DML：增修正表中数据
1. 添加数据：
* 语法：
* insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
* 留意：
1) 列名和值要一一对应。
2) 假如表名后，不界说列名，则默认给一切列添加值
insert into 表名 values(值1,值2,...值n);
3) 除了数字类型，其他类型需求运用引号(单双都能够)引起来
2. 删去数据：
* 语法：
* delete from 表名 [where 条件]
* 留意：
1) 假如不加条件，则删去表中一切记载。
2) 假如要删去一切记载
(1) delete from 表名; -- 不引荐运用。有多少条记载就会履行多少次删去操作
(2) TRUNCATE TABLE 表名; -- 引荐运用，效率更高 先删去表，然后再创立一张一样的表。
3. 修正数据：
* 语法：
* update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
* 留意：
* 假如不加任何条件，则会将表中一切记载悉数修正。
DQL：查询表中的记载
* select * from 表名;
1. 语法：
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限制
2. 根底查询
1) 多个字段的查询
select 字段名1，字段名2... from 表名；
* 留意：
* 假如查询一切字段，则能够运用*来代替字段列表。
2) 去除重复：
* distinct
3) 核算列
* 一般能够运用四则运算核算一些列的值。（一般只会进行数值型的核算）
* ifnull(表达式1,表达式2)：null参加的运算，核算结果都为null
* 表达式1：哪个字段需求判别是否为null
* 假如该字段为null后的替换值。
4) 起别名：
* as：as也能够省掉
3. 条件查询
1. where子句后跟条件
2. 运算符
* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( 调集)
* LIKE：含糊查询
* 占位符：
* _:单个任意字符
* %：多个任意字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或 !
-- 查询年纪大于20岁
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age >= 20;
-- 查询年纪等于20岁
SELECT * FROM student WHERE age = 20;
-- 查询年纪不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
-- 查询年纪大于等于20 小于等于30
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
-- 查询年纪22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);
-- 查询英语成果为null
SELECT * FROM student WHERE english = NULL; -- 不对的。null值不能运用 = （!=） 判别
SELECT * FROM student WHERE english IS NULL;
-- 查询英语成果不为null
SELECT * FROM student WHERE english IS NOT NULL;
-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询名字第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";
-- 查询名字是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
-- 查询名字中包括德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
跋文
本博文学习了MySQL 简介和MySQL数据库简介，上述仅仅简单的对数据库进行介绍以及稍微讲解了 SQL 句子，数据库还是很博大精深的，感兴趣的同学能够深入探求一番，比如业务，B+树等，冲冲冲！
拓展
范式
一般咱们在创立数据库需求必定的规矩去恪守，在 RDBMS 中，这种规矩便是范式。而规范化目的便是使结构更合理，消除存储反常，使数据冗余尽量小，便于刺进、删去和更新，使得它契合榜首范式的规矩，然后是第二范式，终究是第三范式。一般来说，数据库只需满意第三范式就行了。这里只介绍三大范式，当然还有 BCNF 等，感兴趣的同学自行了解。
榜首范式 1NF
-
每个表具有一个主键，主键能够由一个列或多个列组成，是记载的仅有标识符；
-
数据库表中的任何字段都是单一特点，不可再分。
例题
如下是记载学生家庭信息的数据库表，
| 学号 | 名字 | 性别 | 家庭地址 |
|---|---|---|---|
| 001 | 小明 | 男 | 浙江省金华市婺城区 |
| 002 | 小红 | 女 | 广东省深圳市南山区 |
| 003 | 小王 | 男 | 江苏省南京市江宁区 |
从图中咱们能够发现：学号、名字、性别都是具有仅有性的，是不可在切割的部分，而家庭住址则是由省、市、区三部分组成，因而能够被细分，如下图所示：
| 学号 | 名字 | 性别 | 省 | 市 | 区 |
|---|---|---|---|---|---|
| 001 | 小明 | 男 | 浙江省 | 金华市 | 婺城区 |
| 002 | 小红 | 女 | 广东省 | 深圳市 | 南山区 |
| 003 | 小王 | 男 | 江苏省 | 江宁区 | 南京市 |
这样子便是契合榜首范式的数据库表了。
第二范式 2NF
-
第二范式是在榜首范式的根底上建立起来的，即满意第二范式有必要先满意榜首范式；
-
要求数据表里的一切数据都要和该数据表的主键有彻底依靠联系，即不存在部分函数依靠。
例题1
如下是记载学生课程信息的数据库表，其中有四个字段，分别为学生学号 sno，课程编号 cno，终究成果 grade，课程学分 credit，
| sno | cno | grade | credit |
|---|---|---|---|
| 001 | 1 | 76 | 3 |
| 002 | 1 | 98 | 3 |
| 003 | 1 | 45 | 3 |
| 004 | 2 | 86 | 4 |
如此以来，学分被很多重复存储，形成数据冗余：
-
如要某课程学分，则要很多重复操作；
-
如要加新课程，由于
sno和cno共同做为主键，则在参加新课程时，有必要有人选该课；
总归，这种设计不太好，非关键字特点 credit 仅函数依靠于 cno，也便是credit 部分依靠组合关键字 (sno,cno) 而不是彻底依靠！
解决方法：分为两个联系形式，如下图所示，经过 cno 进行关联，
课程信息表
| cno | credit |
|---|---|
| 1 | 3 |
| 2 | 4 |
学生成果表
| sno | cno | grade |
|---|---|---|
| 001 | 1 | 76 |
| 002 | 1 | 98 |
| 003 | 1 | 45 |
| 001 | 2 | 86 |
例题2
如下是学生信息表，
| 学号 | 名字 | 性别 | 年纪 | 专业编号 | 课程编号 | 课程称号 | 任课教师 | 上课地址 | 上课时刻 |
|---|---|---|---|---|---|---|---|---|---|
| 001 | 小明 | 男 | 19 | 134 | 13401 | 核算机导论 | 张三 | 20幢 | 8:30 |
| 002 | 小红 | 女 | 18 | 069 | 06913 | 拉拉操 | 李四 | 19幢 | 14:00 |
| 003 | 小王 | 男 | 20 | 208 | 20832 | 毛概 | 王五 | 17幢 | 10:45 |
从这个表中能够看出：学号和专业编号是学生信息表的联合主键，而任课教师、上课地址等信息都与主键没有直接关联，违反了第二范式的原则，因而咱们要对这个表进行一些改动，使之契合第二范式的要求。
解决方法：根据两个主键的首要信息将该数据库表切割为学生信息表和专业信息表两个表，将上课专业的具体信息再分为课程信息表。学生所在专业作为了一个仅有性的列（一个学校只具有这一个专业），而专业所往下又可划分为不同信息，不同的课程所上课时刻、地址、老师又都各不相同。
学生信息表
| 学号 | 名字 | 性别 | 年纪 |
|---|---|---|---|
| 001 | 小明 | 男 | 19 |
| 002 | 小红 | 女 | 18 |
| 003 | 小王 | 男 | 20 |
专业信息表
| 学号 | 专业编号 | 课程编号 |
|---|---|---|
| 001 | 134 | 13401 |
| 002 | 069 | 06913 |
| 003 | 208 | 20832 |
课程信息表
| 课程编号 | 课程称号 | 任课教师 | 上课地址 | 上课时刻 |
|---|---|---|---|---|
| 13401 | 核算机导论 | 张三 | 20幢 | 8:30 |
| 06913 | 拉拉操 | 李四 | 19幢 | 14:00 |
| 20832 | 毛概 | 王五 | 17幢 | 10:45 |
第三范式 3NF
- 契合第二范式；
- 消除了传递依靠联系，任何两个非主键字段之间不存在依靠联系，即联系形式 R（U，F）中的一切非主特点对任何候选关键字都不存在传递依靠。
例题
学生信息表 |学号|名字|性别|年纪|课程编号| |—|—|—|—|—|—| |001|小明|男|19|13401| |002|小红|女|18|06913| |003|小王|男|20|20832|
课程信息表
| 课程编号 | 课程称号 | 任课教师 | 上课地址 | 上课时刻 |
|---|---|---|---|---|
| 13401 | 核算机导论 | 张三 | 20幢 | 8:30 |
| 06913 | 拉拉操 | 李四 | 19幢 | 14:00 |
| 20832 | 毛概 | 王五 | 17幢 | 10:45 |
从这两张表中咱们都能够显着的发现：不论哪一字段都与表当时的主键是严密相连的，都依靠它。比如：一个学生的学号是仅有的，当你从数据库中要查找一个学生所选的课程时，你只需运用 WHERE 子句指定查找学号即可查出，课程信息表也是如此。
所以关于建好一个数据库来说，这三个范式是多么的重要啊。不只使咱们避免了很多的数据冗余，节省了存储空间，并且坚持了数据的一致性。要查询不同表中的数据只需进行 SELECT 联合查询即可！
B+树
B+ 树是一种树数据结构，一般用于数据库和操作体系的文件体系中。B+ 树的特色是能够坚持数据安稳有序，其刺进与修正具有较安稳的对数时刻复杂度。B+ 树元素自底向上刺进，这与二叉树恰好相反。
节点结构
在 B+ 树中的节点一般被表明为一组有序的元素和子指针。假如此 B+ 树的阶数是 m，则除了根之外的每个节点都包括最少 [m/2] 个元素最多 m-1 个元素，关于任意的结点有最多 m 个子指针。关于一切内部节点，子指针的数目总是比元素的数目多一个。一切叶子都在相同的高度上，叶结点本身按关键字大小从小到大链接。
刺进
- 首先，查找要刺进其中的节点的方位。接着把值刺进这个节点中。
- 假如没有节点处于违规状况则处理完毕。
- 假如某个节点有过多元素，则把它割裂为两个节点，每个都有最小数目的元素。在树上递归向上继续这个处理直到抵达根节点，假如根节点被割裂，则创立一个新根节点。为了使它工作，元素的最小和最大数目典型的有必要挑选为使最小数不小于最大数的一半。
删去
-
首先，查找要删去的值。接着从包括它的节点中删去这个值。
-
假如没有节点处于违规状况则处理完毕。
-
假如节点处于违规状况则有两种可能情况：
-
它的兄弟节点，便是同一个父节点的子节点，能够把一个或多个它的子节点转移到当时节点，而把它返回为合法状况。假如是这样，在更改父节点和两个兄弟节点的别离值之后处理完毕。
-
它的兄弟节点由于处在低鸿沟上而没有额定的子节点。在这种情况下把两个兄弟节点兼并到一个单一的节点中，并且咱们递归到父节点上，由于它被删去了一个子节点。持续这个处理直到当时节点是合法状况或者抵达根节点，在其上根节点的子节点被兼并并且兼并后的节点成为新的根节点。
-
例题
给定下图中的B+树，刺进如下：20、13、15、10、11、12，每次刺进后画一个独自的B+树结构
参阅答案
- Write SQL DDL corresponding to the schema in Figure 3.17. Make any reasonable assumptions about data types, and be sure to declare primary and foreign keys.
create table person
(driver_id varchar(50),
name varchar(50),
address varchar(50),
primary key (driver_id));
create table car
(license_plate varchar(50),
model varchar(50),
year integer,
primary key (license_plate));
create table accident
(report_number integer,
year integer,
location varchar(50),
primary key (report_number));
create table owns
(driver_id varchar(50),
license_plate varchar(50),
primary key (driver_id, license_plate),
foreign key (driver_id) references person,
foreign key (license_plate) references car);
create table participated
(report_number integer
license_plate varchar(50),
driver_id varchar(50),
damage_amount integer,
primary key (report_number, license-plate),
foreign key (license_plate) references car,
foreign key (report_number) references accident)),
foreign key (driver_id) references person;
-
Consider the employee database of Figure 3.19, where the primary keys are underlined. Give an expression in SQL for each of the following queries.
a. Find ID and name of each employee who lives in the same city as the location of the company for which the employee works.
b. Find ID and name of each employee who lives in the same city and on the same street as does her or his manager.
c. Find ID and name of each employee who earns more than the average salary of all employees of her or his company.
d. Find the company that has the smallest payroll.
a. Find ID and name of each employee who lives in the same city as the location of the company for which the employee works.
select e.ID, person_name
from employee as e, works as w, company as
where e.lD = w.ID
and e.cit = .city
and w.company_name = c.company_nm
b. Find ID and name of each employee who lives in the same city and on the same street as does her or his manager.
select e.ID, e.person_name
from employee as e, employee as m, manages
where e.ID = manages.ID
and m.ID = manages.manager_id
and e.street = m.street
and e.city = m.city
c. Find ID and name of each employee who earns more than the average salary of all employees of her or his company.
select E.ID, person_name
from works as T, employee as E
where E.ID = T.ID
and salary > (select avg (salary)
from works as S
where .m_nm = S.y_nm)
The primary key constraint on works ensures that each person works for at most one company.
d. Find the company that has the smallest payroll.
select m_nm
from works
group by company_name
having sum (salary) <= all (select sum (salary)
from works
group by m_nm)
classroom(building, room_number, capacity)
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)
advisor(s_ID, i_ID)
time_slot(time_slot_id, day, start_time, end_time)
prereq(course_id, prereq_id)
Considering the database above, use SQL language to write the following queries.
Solution
Q1. Find the department names of all instructors
select distinct dept_name
from instructor;
Q2. Find the names of all instructors in the Computer Science department who have salary greater than $70,000.
select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 70000;
Q3. Find the names of all instructors, along with their department names and department building name.
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;
Q4. Find the names of all instructors whose salary is greater than at least one instructor in the Biological department.
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
Q5. Find the names of all departments whose building name includes the substring ‘Watson’.”
select dept_name
from department
where building like '%Watson%';
Q6. Using union property, find the set of all courses taught either in Fall 2009 or in Spring 2010, or both.
(select course_id
from section
where semester = 'Fall' and year= 2009)
union
(select course_id
from section
where semester = 'Spring' and year= 2010);
Q7. Find all instructors who appear in the instructor relation with null values for salary.
select name
from instructor
where salary is null;
4.范式
①
②
③
- B+树
