学习MySQL笔记

一：数据库介绍

数据库分为关系型数据库和非关系型数据库。关系型数据是指由二维表之间的存在数据关系的数据组织；非关系型数据库又称NoSql数据库，是由数据结构化法的方式存储数据的的集合。

二：SQL语句的操作（工具MySQL5.7和Navicat15）

1.数据库的创建、删除、查看、选择

创建语句（关键词 create）：create database 数据库名：create database student 

 删除数据库（关键词 drop)：drop 数据库名：drop database student

 查看数据库（关键词 show）：show databases

 选择数据库（关键词 use）：use  数据库名：use student

2.常用数据库的数据

整数型：int、smallint、bigint、tinyint、mediumint；

浮点数：（float、double（会丢失精度一般不用)）、decimal、numeric；

• float、double的精度都比较大，在数据存储时会造成一定的精度丢失，一般都不会用；
• decimal(size,d)、numeric(size,d)：size表示最大数值的最大位数，d表示小数的最大位数
• decimal(10,1)格式存储了10.5，decimal(10,2)格式存储了10.50，两个数值相等，但数据库会判定为数据类型不相等。

字符类型：char、varchar、text；

char、varchar、text的区别：

• char长度是固定的，varchar和text的长度是可变的；
• varchar和text的长度都要比char长，当不知道字符长度时，推荐用varchar；
• 访问速度：char、varchar、text；
• 补充char比varchar访问速度快的原因是：在定义相同的字符长度以及存储相同的长度的字符时，如char(10)和varchar(10)都存入一个数据5，char存入的5加9个隐藏空格，而varchar存入的是5，只有一个字节，在此情况下，数据库对char的访问时定长访问，很快就能检索到数据5，而对varchar的访问得进行判断5后面是否还有内容。
• 使用场景：

1. char：一定长度的，比如电话号码、邮编、身份证、工号等、性别（不考虑第三个性别）；
2. varchar：不知道长度，但内容偏少的，比如名字、地址、等；
3. text：长度比较长的，比如文本描述、备注等

 日期类型：date（日期）、time（时间）、datetime（日期时间）、datestamp

• date：存放日期，一般格式为：YYYY-MM-DD
• time：存放时间，一般格式为HH-MM-SS。H为24小时制，h为12小时制

3.数据表的创建，删除，修改

创建（create）：create table 表名（列名（数据类型），列名（数据类型）。。。）

create table student (id int(10))

 删除数据表（关键词 drop）：drop table 数据表名：drop table student 

 

 修改数据表内容（关键词：alter）

修改表名：alter table 旧表名 rename 新表名：

alter table student rename students

修改列名：alter table 表名 change column 列名 新列名 数据类型：

alter TABLE students change COLUMN id studentId int(20)

新增列名：update table 表名 add column 新列 数据类型 ：

alter TABLE students add column studentName VARCHAR(20)

 修改列类型：alter table 表名 modify 列名 新类型：

alter table students modify studentId char(10)

 第3部分结束之后的数据截图：

 4.数据的增、删、改

增加数据（关键词 insert）

指定列添加数据：insert into 表名（列名）  value（值）：

INSERT into students(studentId) VALUE("s123456")

 添加全部数据：insert into 表名 values（值1，值2，。。。）：

INSERT into students VALUE("s123457"，"sakura");

 修改数据（update）：update 表名 set 列名 = 值 where 条件 

 删除数据（delete）

删除全部：delete * from 表名；

指定删除 delete from 表名 where 条件

 清空表（truncate）：truncate from 表名

delete和truncate的区别

1. delete和truncate清空表时，truncate的速度要比delete快；
2. truncate不能单独删除数据表中的数据；
3. truncate不支持事务回滚

三：数据库中的约束

主键约束（PRIMARY KEY） ：主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。

外键约束（FOREIGN KEY） ：一般与主键约束搭配使用，确保数据的完整；

唯一约束（UNIQUE KEY）：确保数据的唯一性，不过一个表中可以存在多个唯一约束且值允许为空值；

非空约束（NOT NULL）：约束字段值不能为空；

检查约束（CHECK）：用于数据表中的数据是否符合要求，如学生的单科成绩范围0-150等

PS：MySQL数据库不支持CHECK约束，它的用法：

• 格式的限制：alter table 表名 ada constraint 约束名 check( 列名 = ”“ or 列名=”“）
• 范围：alter table 表名 ada constraint 约束名 check( 列名 between 值1 and 值2）

约束的操作：

 添加主键约束：alter table 表名 add primary key(列名）：

 alter table students add primary key(studentId)

 

 主键约束一般都会有自增属性（auto_increment)

语法：alter table 表名 modify 主键 数据类型 auto_increment

将学生id主键改为自增长：alter table students modify studentId int auto_increment

 MySQL 中的自动增长类型要求：

• 一个表中只能有一个列为自动增长。
• 自动增长的列的类型必须是整数类型。
• 自动增长只能添加到具备主键约束与唯一性约束的列上。
• 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然 后在删
• 除约束。

 删除主键约束：alter table 表名 drop primary key

 添加外键约束：

alter table 表名 add constaint 约束名 foreign key(列名） references 参照表名（列名)

对照教师表的名字将学生表的学生名字设为外键：

alter table students add CONSISTENT name_fk FOREIGN KEY(studentName) REFERENCES teacher(teacherName)

删除外键约束：alter table 表名 drop foreign key 约束名

唯一约束：

添加：alter table 表名 add constaint 约束名 unique(列名）

给老师的名字添加唯一约束：

alter table teacher add CONSISTENT uk_name unique(teacherName)

删除唯一约束：alter table 表名 drop  key 约束名

非空约束：

添加：alter table 表名 modify 列名 类型 NOT NULL

删除：alter table 表名 modify 列名 类型  NULL

PS：唯一约束和外键约束都要月主键约束的前提。外键约束对应到参照表的主键约束；唯一约束对应自身表的主键约束。

四：查询语句（本快不再查用自己创建的数据库，而是采用MySQL官方的数据库进行操作）

查询关键词：select

• 查询全部：select * from 表名
• 查询某列：select 列名 from 表名
• 条件查询：select 需要查询的字段 from 表名 where 条件

例子一：查询员工表中的全部数据：

select * from employees

 例子二：查询员工表中的员工的名

SELECT last_name from employees

 例子三：查询员工表中工资高于一万的员工的姓名

SELECT FIRST_NAME, last_name from employees WHERE SALARY > 10000

查询的条件介绍（这一块，每个部分我举一个例子）

1. 四则运算：+、-、*、/ 分别对应加、减、乘、除
2. 逻辑运算：and、or、not，对应与、或、非
3. between ... and ：在什么与什么之间
4. Like：模糊搜索
5. in：在什么里面
6. null：IS NULL 条件和 IS NOT NULL
7. <、=、>、<>（!=)：小于、等于、大于、不等于

 例子一：计算员工表中的一年工资大于20万的员工姓名和年薪

SELECT FIRST_NAME, last_name ,salary*12 from employees WHERE SALARY*12 > 200000

 例子二：查询月工资大于一万并且年薪大于二十多号万的员工id、姓名、具体的月工资和具体年薪

SELECT EMPLOYEE_ID,FIRST_NAME, last_name,SALARY ,salary*12 as year_salary 
from employees 
WHERE salary*12 > 250000 
and SALARY > 10000

 例子三：查询干IT且工资在五千，九千或一万的员工的id、姓名和工资

SELECT EMPLOYEE_ID,FIRST_NAME, last_name,SALARY 
from employees 
WHERE salary IN(5000,9000,10000)
and JOB_ID like "IT%"

 在这里做几个注解：

1. IN是指在（）中规定的数值中寻找一样的，如IN（1，2，3，4，5）就是说在1，2，3，4，5这堆数中寻找满足条件的；而between and时在区间内寻找；
2. like的”%“指匹配一个或者多个含有此字符串的，如”%k%“，就是指中间有K字符的，同理”%k"就是指后面包含k；”_"是指_的下一个字符串得符合要求，如“_k"就是指第二个字符得是k

例子四：查找佣金和管理员id都不为空值的且工资还大于一万的员工姓名、工资、佣金和管理员的id

SELECT FIRST_NAME, last_name,SALARY,commission_pct ,MANAGER_ID
from employees 
WHERE COMMISSION_PCT is NOT NULL
and MANAGER_ID is NOT NULL

 五：聚合函数、分组和排序介绍

1.聚合函数

• AVG：求平均值；
• sum：求和；
• MAX：求最大值；
• MIN：求最小值；
• count：合计

2.分组：group by

group by指靠什么分组，比如group by department_id就是说靠部门id分组；group by 后面有条件的话，跟的关键字是having

所有不在聚合函数里的列都得在group by 后面

3.排序：order by

order by根据什么排序

整个查询语句的结构：

select 列名 ，列名

from 表名

where 条件

group by 列名

having 条件

order by 排序条件

例子一： 计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。

SELECT AVG(salary),SUM(salary)FROM employees WHERE JOB_ID like "%REP%"

 例子二：查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

例子三：显示员工表中部门编号是80中有佣金的雇员人数。 

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

 例子四：计算每个部门的员工总数

SELECT DEPARTMENT_ID, COUNT(*) FROM employees GROUP BY DEPARTMENT_ID;

 例子五：显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。

 六：多表查询

在正式进入多表查询的模块之前，介绍一个东西，别名，关键字是as(可以省略）

• 别名可用于表、列，更好的体现这个语句需要展示的一些东西。
• 比如：salary*12 as ”年薪“、employees e这个e就是表的别名
• 我们还可以用别名进行操作如：e.salary、e.job_id等
• 在多表查询中，由于表的名字可能会很长，不容易看懂查询语句的具体要求，所以，会采用表别名的方式进行练习，有助于理解语句和减少语句的一些非必要编写 

1.SQL92标准：内连接(等值连接 、非等值连接 、 自连接)。

等值连接：两个表中有一个共同的东西可以对应连接，比如员工表中有部门id，部门表也有部门id，它们就可以对应相等：e.EMPLOYEE_ID,d.DEPARTMENT_ID。

例子一：显示每个雇员的 last name、departmentname 和 city。

SELECT e.LAST_NAME ,d.DEPARTMENT_NAME,l.city
FROM employees e, departments d, locations l
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND d.LOCATION_ID = l.LOCATION_ID

 非等值连接：不是靠两个值相等，而是取决于其中一个表的数据范围，如：e.salary between j.salary_min and j.salary_max。

例子二：查询所有雇员的薪水级别

自连接：连接一个表到它自己。有时需要连接一个表到它自己。

例子：查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。

SELECT worker.LAST_NAME W,manager.LAST_NAME M
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID;

2.SQL99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接、自然来连接。

内连接（inner join on)：取两个表相同的部分

例子：查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

SELECT e.EMPLOYEE_ID , e.SALARY ,d.DEPARTMENT_NAME
from employees e join departments d
on e.department_Id = d.DEPARTMENT_ID
WHERE e.LAST_NAME = "FOX"

外连接——左外（left join）：以左表为主，列出相同数据之后，还会列出左表中特有的数据

例子：查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

SELECT e.LAST_NAME, d.DEPARTMENT_NAME
FROM employees e LEFT JOIN departments d 
on e.DEPARTMENT_ID = d.DEPARTMENT_ID

 外连接——右外（righ join）：以右表为主，列出相同数据之后，还会列出右表中特有的数据

例子：查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

SELECT e.LAST_NAME, d.DEPARTMENT_NAME
FROM employees e right JOIN departments d 
on e.DEPARTMENT_ID = d.DEPARTMENT_ID

 对比一下左右外连接的值，发现是几乎不一样的。

全外连接（full join）：取两个表中都不相同的数据作为展示，语法和之前相似，只不过mysql不支持，暂时不做演示。

 自然连接（natural join）：连接只能发生在两个表中有相同名字和数据类型的列上。

例子：使用自然连接查询所有有部门的雇员的名字以及部门名称。

select e.last_name , d.department_name  from employees e natural join departments d

 交叉连接（cross join）：获得两个表的笛卡尔乘积

笛卡尔乘积 ：
当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积 (Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。

例子： 使用交叉连接查询 employees 表与 departments 表。

select * from employees cross join departments

七：子查询

定义： 子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。使用子查询可以用简单的语句构
建功能强大的语句。

使用方法和原则：

分类：

• 单行
• 多行（ANY：< ANY 意思是小于最大值；> ANY 意思是大于最小值；= ANY 等同于 IN。）

使用子查询的原则：

• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

可以将子查询放在许多的 SQL 子句中，包括：

• WHERE 子句
• HAVING 子句
• FROM 子句

 例子一：查询与Fox同一部门的同事，并显示他们的名字与部门ID。

select LAST_NAME , DEPARTMENT_ID
FROM employees
WHERE DEPARTMENT_ID = 
(SELECT DEPARTMENT_ID from employees
WHERE LAST_NAME = "FOX")

例子二：查询 Fox的同事，但是不包含他自己。

select ep.last_name 
from employees ep 
where ep.department_id= 
(select e.department_id 
from employees e where e.last_name = 'Fox') 
and ep.last_name <> 'Fox';

 注意：在同一个表里进行操作的时候，表的别名要么不同，要么没有，参考例子一和二

 例子三：查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。

select e.LAST_NAME , e.SALARY ,e.DEPARTMENT_ID
from employees e
join (SELECT MIN(ep.salary) as salary,DEPARTMENT_ID
FROM employees ep
GROUP BY ep.DEPARTMENT_ID) t
on e.DEPARTMENT_ID = t.DEPARTMENT_ID 
and e.SALARY = t.salary
ORDER BY  e.DEPARTMENT_ID;

学习还没结束，内容还得完善，再过几天，更新SQL的练习题和数据库其他的部分。