MySql

一、数据库基础

1 数据库基础知识

背景：数据库管理发展到现在，经历了人工管理阶段、文件系统阶段和数据库系统阶段。

（1）在人工管理阶段，人们利用算盘、账簿管理数据；
（2）在文件系统阶段，人们利用计算机程序将数据以程序能识别的方式组织成电子文件，存储在计算机硬盘，再利用计算机程序读取文件；
（3） 在数据库系统阶段，人们利用数据库管理软件对数据进行组织、管理，变得更加高效；

数据库系统的模型：主要有层次模型、网状模型、关系型、对象关系型。目前的主流模型是关系型数据模型，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

微软的SQLServer数据库就是典型的关系模型。关系模型是采用二维表的形式表示实体与实体相关联的一种数据模型。

2 数据库的基本术语

2.1 数据库系统（DBS，DataBase System）

定义：是由应用程序、数据库管理系统、数据库、数据库管理员和用户构成的存储、管理、处理、维护数据的系统。管理的对象是数据。

2.2 数据库（DB，DataBase）

定义：存放数据的仓库。（数据库就是按照特定的数据结构来组织、存储、管理数据的仓库。）
含义：长期存储于计算机内的、有组织、可共享的数据集合。
优点：按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，可为各种用户共享。
特点：永久储存、有组织、可共享。

2.3 数据库管理系统（DBMS，DataBase Management System）

定义：一个大型复杂的软件系统。
含义：是位于用户和操作系统之间的管理软件，用于操作和管理数据库，是数据库系统的核心。
主要功能：数据库的创建、维护、数据定义、数据操纵和数据库的运行管理。

2.4 数据库管理员（DBA,DataBase Administrator）

定义：是负责管理维护数据库服务器的人员。负责管理和控制数据库管理系统。

3 当今主流数据库

数据库系统萌芽于20世纪50年代，产生于60年代中。目前在数据库存储领域应用比较广泛的数据库有Oracle、DB2、Sybase、MySql、SQL Server等，均为关系型数据库。

3.1 Oracle数据库

– 是一个“对象→关系”数据库管理系统，目前常用版本为Oracel 11g。
– 它提供了全面、开放、集成的信息管理方式。
– 是由全球著名的信息管理软件开发商Oracle（甲骨文）公司开发的关系模型数据库系统，全球占额32%左右。

特点：贵！但是性能好、安全性保密性高。

3.2 DB2数据库

由IBM公司开发的一种关系模型数据库系统。

特点：适用于大型软件系统，具有较好的伸缩性，在OS/2和Windows等平台使用最广泛。

3.3 Sybase数据库

由美国Sybase公司开发的一种关系模型数据库系统，是典型的基于Unix和Window NT平台上客机/服务器环境的数据库系统。

特点：广泛应用于我国中大型应用软件系统中。

3.4 MySql数据库

是一个跨平台的开源关系型数据库管理系统，目前广泛应用于Internet上的中小型网站中，属于Oracle旗下产品。
MySQL 软件采用了双授权政策，分为社区版和商业版

特点：体积小、速度快、总体拥有成本低，开放源码。（端口号：3306）MySQL中的sql语言属于弱语言。

二、创建和管理数据表

（1）数据库是一个框架，数据表才是实质内容，一个数据库可以包含若干数据表，表是数据库中非常重要的对象，是数据库最重要的组成部分之一，是其他对象的基础。
（2） 为保证录入数据的准确性和一致性，数据库提出了数据完整性。使用主键约束保证数据的唯一性，使用外键约束保证数据的一致性。

1 数据类型

主要包括字符类型、数字类型、日期类型等。

常用：int（整数类型）、decimal（小数类型）、char（定长字符）、varchar（变长字符）。

2 数据完整性

– 数据完整性用于保证数据库中数据在逻辑上的一致性、准确性、可靠性，即存储在数据库中的 值能正确反映实际情况，不能是无效值、错误值、乱码等。
– 数据完整性包括实体完整性、域完整性、参照完整性。

2.1 数据完整性的类型

①实体完整性：约束每一条数据在表中唯一。
②域完整性：保证数据的准确性、有效性。限制类型、格式、取值范围等等。
③参照完整性：通过主键外键连接主表从表，保证增删改数据时，关联表中数据一致。

2.2 数据完整性的实现方式

①实体完整性：主键约束、自增约束、唯一约束。
②域完整性：非空约束、检查约束、默认值约束。
③参照完整性：外键约束

补充：检查约束根据字符长度判断条件: LENGHT(字段)。
注意：对于CHAR类型的列，不能使用AUTO_INCREMENT作为列说明符。

StuTel VARCHAR(11) NOT NULL CHECK((LENGHT(StuTel) = 7) OR (LENGHT(StuTel) = 11)) NOT NULL,

三、使用DML操作数据库

DML是指数据库操纵语言，包括数据的增删改查。

1 SQL语句语法

（1）SQL语句用分号（;）结尾，目的是区分sql语句是一句指令还是两句指令。

（2） SQL语句可以使用空格或者换行符随意修饰，使其可读性更强，但是不影响执行效果。(关键字或者标识符不能拆分)

（3）sql语句不区分大小写，但是mysql中建议使用大写。

（4）注释：多行注释： /* 注释内容 */ 单行注释：-- 代码 快捷键ctrl+/

2 数据库操作

（1）创建数据库：CREATE DATABASE 数据库名;
（2）使用数据库：USE 数据库名;
（3）删除数据库：DROP DATABASE 数据库名;

3 数据库表操作(增删改查、回滚撤回)

3.1 创建数据表

/*
create table 表名 (
 	字段1 数据类型 [约束],
	字段2 数据类型 [约束],
	字段3 ......
)
*/
CREATE TABLE Employee(
	empId INT PRIMARY KEY AUTO_INCREMENT,
	empName VARCHAR(20) NOT NULL,
	empEmail VARCHAR(50) UNIQUE NOT NULL,
	empGJ VARCHAR(20) DEFAULT '中国'
 )
CREATE TABLE EmployeeInfo(
	empInfoId INT PRIMARY KEY,
	empInfoMoney DECIMAL(7,2),
	empInfoSex VARCHAR(2),
	empId INT,
	FOREIGN KEY(empId) REFERENCES Employee(empId)
)

3.2 INSERT插入数据

是对于表来说，是插入全体数据。
INSERT [INTO] 表名 [字段1，字段2，…] VALUES(值1，值2，…)

注意：

• 字段全省略，但是值必须按顺序一一对应；
• 字段部分省略，允许为空的字段值默认NULL；

（1）插入单条数据

INSERT INTO Employee(empName,empEmail,empGJ) 
VALUES('赵六','123456@qq.com','法国');

（2）插入多条数据

INSERT [INTO] 表名(字段1，字段2，......) VALUES
	(值1，值2，...),
	(值1，值2，...)；

注意：

• 添加数据，若字段类型为字符/日期类型，必须用 ’ '；
• INSERT对字段名顺序无要求，但是字段与值必须一一对应；
• 自增列无需显示添加值；
• 不能为空的字段不能省略；
• 允许为空的字段添加数据：①直接使用NULL作为值添加；②不添加该字段名，字段值会默认为NULL；
• 在从表中插入数据时，如果主表中没有数据存在，则从表插入不成功，应该先在主表中插入，再插入从表；

3.3 DELETE删除数据

是对于表来说的，是删除表中的数据。
DELETE FROM 表名 [WHERE 条件]
一般在删除时要加上WHERE 条件，否则会删掉表中所有数据。

DELETE FROM rank;
DELETE FROM Employee WHERE empId = 3;

注意：在删除数据时，如果存在主外键关系，应该先删除从表中的数据，在删除主表中的数据；

DELETE FROM EmployeeInfo WHERE empId = 1;
DELETE FROM Employee WHERE empId = 1;
UPDATE EmployeeInfo SET empId = 4 WHERE empInfoId = 2;
DELETE FROM Employee WHERE empId = 2;

3.4 UPDATE更新数据

是对于表来说的，是更新表中的记录，全部、部分数据。
UPDATE 表名 SET 字段1 = 值1，字段2 = 值2,… [WHERE 条件]

UPDATE Employee SET empGJ = '中国';
UPDATE Employee SET empName = '王五',empGj = NULL;
# 当ID等于 2 的时候将表中的名字改为李四，国籍改为美国
UPDATE Employee 
SET empName = '李四',empGj = '美国'
WHERE empId = 2;

在字符串后拼接字符串：UPDATE 表名 SET 字段 = CONCAT(字段+ “Str2”);

注意：这是MySQL独有的方法

# 将所有出版社地址后面加上两个点号..
UPDATE Publish SET Paddress = CONCAT(Paddress,"..");

3.5 SELECT查询数据

SELECT 字段1,字段2,… FROM 表名
[WHERE 条件]
[GROUP BY 字段1,字段2,… |HAVING 条件] GROUP BY 子句根据指定列分组，HAVING子句对分组后的结果进行过滤。
[ORDER BY 字段1,字段2,…]（ASC或DESC） ORDER BY 子句用于对查询结果排序。ASC 表示升序，DESC表示降序，默认升序。

SELECT * FROM 表名;
# 从student表中查询出生年份在1990到1995之间的学生信息并对年龄进行降序排列
SELECT * FROM Student WHERE birth > '1990/1/1' AND birth < '1995/12/31' ORDER BY birth DESC;
# 2. 显示歌曲名长度为4的歌曲
SELECT * FROM Song WHERE CHAR_LENGTH(Title) = 4;

注意：

• “ * ”代表全部列；
• 区别于CHECK检查约束中的字符长度式LENGHT(字段)，在查询字符串长度时使用CHAR_LENGTH(字段)；

3.5.1 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，内部的查询是外部查询的条件，这个特性从MySQL4.1开始引入。
SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
常用子查询：
① WHERE 条件字段 [NOT] IN (subquery); – ‘IN’表示子查询范围;‘=’表示单值
② WHERE 条件字段 比较运算符 [ANY/ALL] (subquery); – ‘ANY’等于SOME
③ WHERE [NOT] EXISTS (subquery); – ‘EXISTS’只注重返回行，返回行则为真

-- 查找薪水大于所有员工平均薪水的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees);

-- 查询工资大于149号员工工资的员工的信息
select employee_id,last_name,salary
from employees
where salary > (
    select salary from employees where employee_id = 149)
   
-- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job _id和工资
select last_name,job_id,salary
from employees
where job_id = (
    select job_id from employees where employee_id = 141
    )
and salary > (
    select salary from employees where employee_id = 143
    );

-- 返回公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary
from employees
where salary = (
    select min(salary) from employees
    );

-- 查询与147号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
select employee_id,manager_id,department_id
from employees
where manager_id = (
    select manager_id from employees where employee_id = 147
    )
and department_id = (
    select department_id from employees where employee_id = 147
    )
and employee_id <> 147;    
    
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary) min_salary
from employees
where department_id is not null
group by department_id
having min_salary > (
    select min(salary) from employees where department_id = 50
    );

3.6 回滚

在MySQL中，回滚操作通常是指将数据库恢复到之前的状态，也就是撤回之前的一系列更改（通常是事务）。在MySQL中可以使用ROLLBACK语句来回滚事务。
在使用ROLLBACK之前，要确保已经开启了事务模式，可以用START TRANSACTION来简单的开启一个事务。

-- 开启事务
START TRANSACTION;
 
-- 进行一系列更改
INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2');
UPDATE my_table SET column1 = 'new_value' WHERE column2 = 'value2';
DELETE FROM my_table WHERE column1 = 'value1';
 
-- 如果需要回滚到事务开始之前的状态
ROLLBACK;
 
-- 如果决定保留这些更改，可以使用COMMIT来提交事务
COMMIT;

四、数据查询基础

1 AS设置字段别名

在 SELECT查询语句中，可以用 AS 或 空格 对查询字段名设置别名，字段名可以用别名代替（单引号）。
SELECT 字段1 AS ‘别名1’,字段2 AS ‘别名2’,… FROM 表名;
SELECT 字段1 ‘别名1’,字段2 ‘别名2’,… FROM 表名;

注意：别名加单引号’ ’ ；

2 DISTINCT过滤重复行

SELECT DISTINCT 字段名 FROM 表名;

SELECT DISTINCT empName FROM employee;

3 选择列为表达式

注意：不影响表原数据，仅是查询；

# 查询所有员工的姓名、月基本工资、增长10%之后的月基本工资
SELECT Name AS '姓名',Salary AS '月基本工资',Salary AS '加薪后的月基本工资' FROM Employee;

4 条件查询

4.1 运算符

（1）比较运算符：=、>、<、>=、<=、<>、!=
（2）逻辑运算符：AND、OR、NOT、IS NULL

# 通过条件判断内容是否为 null
SELECT * FROM employeeinfo WHERE empInfoMoney IS NOT NULL;

注意：

• NOT 表示取反；
• IS NULL 表示列值为NULL返回true，反之，返回false；

4.2 单条件查询

SELECT * FROM Rank WHERE RankID = 2;

4.3 多条件查询

SELECT * FROM Rank WHERE Birthday >= '1983-01-01' AND Salary > 3000;

5 ORDER BY查询结果排序

5.1 单行排序

SELECT 字段名 FROM 表名 ORDER BY 排序字段 ASC/DESC;

5.2 多行排序

SELECT 字段名 FROM 表名 ORDER BY 排序字段1,排序字段2，… ASC/DESC;

SELECT TOP 3 rankId AS '编号',rankName AS '部门名' 
FROM rank ORDER BY rankId DESC;

注意：先根据‘排序字段1’排序，再在1的基础上进行‘排序字段2’排序，以此类推。

6 TOP N 返回指定行（MySQL中使用LIMIT实现）

6.1 TOP N

返回前N项
SELECT TOP N 字段 FROM 表名;

6.2 TOP N PERCENT

返回前N%项，若为小数则向上取整
SELECT TOP N PERCENT 字段 FROM 表名;

6.3 LIMIT查询