MySQL学习笔记

数据库的好处

实现数据持久化；
可储存大量数据；
方便检索；
保证数据的一致性、完整性；
安全，可共享；
通过组合分析，获取新数据

数据库的相关概念

• DB（database）数据库：存储数据的仓库，保存一系列有组织的数据
• DBMS（Database Management System）数据库管理系统/数据库管理软件，用于管理DB中的数据
  MySQL：甲骨文
  Oracle：甲骨文，服务价格昂贵
  DB2： IBM，应用方向窄，兼容性不高
  SqlServer：微软 兼容性低，只支持Windows系统
• SQL（Structure Query Language）结构化查询语言：用于和DBMS通信的语言

数据库存储数据的特点

1. 将数据放到表中，表再放到库中
2. 一个数据库中可以有多个表，每张表都有自己的唯一标识名
3. 表具有一些特性，这些特性定义了数据在表中如何储存
4. 表由列组成，也称字段
5. 表中的数据按行储存

MySQL软件

• MySQL隶属于MySQLAB公司，总部位于瑞典，08年被sun收购，09年oracle收购sun
• 优点：开放源代码，成本低，体积小，执行快，兼容性好，容易安装和使用
• DBMS分为两类：

1. 基于共享文件系统的DBMS（Access微软）-不需要安装
2. 基于客户机-服务器（C/S，client/server）的DBMS（MySQL、Oracle、SqlServer），一般安装数据库指的是安装数据库的服务端

MySQL服务的启动和停止

• 方式一：图形化
  右击-计算机管理-服务-MySQL服务
• 方式二：通过管理员身份运行dos
  net start 服务名
  net stop 服务名

MySQL服务的登录和退出

• 方式一：通过dos命令
  mysql -h主机名 -P端口号 -u用户名 -p秘密
  注意：
  如果是本机，则-h主机名可以省略
  如果端口号是3306，则-P端口号可以省略
• 方式二：通过图形化界面客户端
  通过sqlyog，直接输入用户名、密码等连接进去即可

常见SQL命令演示

commands end with ; or \g

显示当前连接下所有数据库

show database;

打开/使用指定库

use 库名;

显示当前库中所有表

show tables;

显示指定库中所有表

show tables from 库名;

查询test数据库里面的表
当前的数据库还是在mysql,因为没有用use test

显示指定表中所有列

show columns from 表名；

• 查询当前所在数据库

select database();

• 创建表

create table stuinfo(
stuid int,   
tsunami varchar(20), 
gender char,      
borndate datetime);

int代表数值数据类型
varchar代表字符串数据类型
char代表代表单个字符
datetime代表日期和时间数据类型，最后一行数据不用逗号分隔
一般不存储年龄而是存储生日，年龄会变生日不会变

• 描述表的内容 desc+表名

desc stuinfo；

• 查询表中的数据

select * from stuinfo;

• 插入数据

insert into stuinfo values(1,'张无忌','男','1998-3-3');

• 更改数据

update stuinfo set borndate='1980-1-1' where stuid=1;

• 删除数据

delete from stuinfo where stuid=1;

• 更改表，修改表的结构

alter table stuinfo add column email varchar(20);

• 删除表

drop table stuinfo;

• 不区别大小写
• 可以加注释
  单行注释用#或者–空格
  多行注释/* */

SQL语言介绍

语法要求

1. SQL语句可以单行或多行书写，以分号结尾
2. 可以用空格和缩进来增强语句的可读性
3. 关键字不区别大小写，建议用大些

分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等 create/drop/alter
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）
  增删改 insert/update/delete
• DCL（Data Control Language）：数据库控制语言，用来定义访问权限和安全级别
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）select

图形化界面客户端的安装和使用

• 选中语句，F9执行
• 调整字体，工具-首选项/control+滚动轴

基础查询

语法

select 查询列表 from 表名；

特点

1. 查询的结果集是一个虚拟表 （如何保存）
2. select后面跟的查询列表，可以由多个部分组成，可以是单个字段、多个字段、常量、表达式、函数以及以上组合，用逗号隔开
3. 执行顺序
   select first_name from employees;
   1⃣️from子句
   2⃣️select子句

查询常量

不涉及到表中的字段可以不写来自于某个表

select 100;

查询表达式

select 100%3;

查询单个字段

select last_name from employees;

如果表名是关键字，需要加着重号``

查询多个字段

select last_name,email,employee_id from employees;

查询所有字段

select * from employees;

F12：快捷键对齐格式

查询函数（调用函数，获取返回值）

select database(); 查询当前所在的数据库
select version(); 查询当前数据库服务器的版本
select user();

引申1：起别名

方式一：使用AS关键字
select user() as 用户名；
select user() as “用户名”；
select user() as ‘用户名’；
以上三种形式都🉑️，什么时候需要加引号，例如：
select last_name as 姓 名 from employees; ❌
姓名之间有空格，系统无法识别

select last_name as "姓 名" from empolyees;☑️

方式二：使用空格
select user() 空格 用户名；
select user() 空格 “用户名”；
select user() 空格 ‘用户名’；
select last_name 空格 “姓 名” from employees;

拼接函数concat

需求：查询first_name和last_name拼接成的全名，最终起别名为：姓 名。

• 方案1:使用+ ❌
  select first_name+last_name as “姓 名”
  from employees;

JAVA中 + 的作用：

1. 加法运算 +加号两边都是数值
2. 拼接符 至少有一个操作数为字符串

引申2: mysql中 + 的作用：只能做加法运算

1. 如果两个操作数都是数值型，则直接做加法运算
2. 如果其中一个操作数为非数值型，系统将强制转换成数值型，如果转换失败，则当做0处理
   ‘123’+4➡️127
   ‘abc’+4➡️4
   ‘张无忌’ + 100 ➡️ 100
3. 如果其中一个操作数为null ，则结果直接为NULL
   null+null➡️null
   null+100➡️null

• 方案2:使用concat拼接函数

SELECT CONCAT(first_name,last_name) AS "姓 名"
FROM employees;

引申3:去重distinct的使用

需求：查询员工涉及到的部门编号有哪些

select distinct departement_id from employees;

查看表的结构（字段、类型、约束）

desc employees;
show columns from employees;

案例讲解

• 显示出表employees的全部列，各列之间用逗号连接，列名显示成OUT_PUT

• 错误答案❌
  SELECT CONCAT (employee_id,’,’,first_name,’,’,last_name,’,’,salary,’,’,commission_pct) AS “OUT_PUT”
  FROM employees;

• 结果有很多NULL值
  原因：commission_pct包含NULL值
  拼接的时候要注意，如果其中有一个参数为NULL，那结果也为NULL。所以拼接时，NULL值不能直接用于拼接。
  我们希望如果它为NULL时，不拼接它，当做一个空字符；不为NULL时，该是啥就拼接啥。这里需要借助一个函数IFNULL函数。

IFNULL函数（表达式1，表达式2）

- 表达式1:可能为NULL的字段或表达式
- 表达式2:如果表达式1为NULL，则最终结果显示的值
- 功能：如果表达式1为NULL，则显示表达式2；否则显示表达式1

SELECT commission_pct,IFNULL(commission_pct,‘空’)
FROM employees；

• 正确答案

select concat(employee_id,',',first_name,',',last_name,',',salary,',',IFNULL(commission_pct,'') AS "OUT_PUT"
FROM employees;

引申4:补充函数

select version();
select database();
select user();
select ifnull(字段名，表达式)
select concat(字符1，字符2，字符3);拼接函数
select length(字符/字段)；获取字节长度

条件查询

语法

select 查询列表
from 表名
where 筛选条件；

执行顺序

1⃣️from子句
2⃣️where子句
3⃣️select子句

特点

1. 按关系表达式筛选
   关系运算符：>, <, >=, <=,=,<>不等于
   补充：也可以使用!=，但不建议
2. 按逻辑表达式筛选
   逻辑运算符：与或非 and or not
   补充：也可以使用&& || ! ，但不建议
3. 模糊查询
   like，in，between and，is null

按关系表达式筛选

• 案例1：查询部门编号不是100的员工信息
  员工信息没有具体指明，所以用select *

SELECT * 
FROM employees
WHERE department_id <> 100;

按逻辑表达式筛选(and,or,not)

• 案例1：查询部门编号不是50-100之间的员工姓名、部门编号、邮箱

• 方式一：

SELECT last_name,department_id,email
FROM employees
WHERE department_id<50 OR department_id>100;

• 方式二，语意性更强

SELECT last_name,department_id,email
FROM employees
WHERE NOT (department_id>=50 AND department_id<=100);

• 案例2:查询奖金率>0.03或者员工编号在60-110之间的员工信息

SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id>=60 AND employee_id<=110);

模糊查询

LIKE关键字

NOT LIKE

• 功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询

• 常见的通配符：
  _ 代表任意单个字符
  % 代表任意多个字符，支持0-多个

• 案例1：查询姓名中包含字符a的员工信息
  注意⚠️：字符数据要用单引号
  注意⚠️：包含 a 代表前后可能都有其他字符

SELECT *
FROM employees
WHERE last_name LIKE '%a%';

• 案例2：查询姓名中包含最后一个字符为e的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%e';

• 案例3：查询姓名中包含第三个字符为x的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '__x%';

• 案例4：查询姓名中包含第二个字符为_的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';

转义字符可以自行设定，ESCAFE '${}^{\prime }代表设定$为转义字符

IN关键字

• 功能：判断某字段的值是否在指定的列表
  a IN(常量值1，常量值2，常量值3，…)
  a NOT IN(常量值1，常量值2，常量值3，…)

• 案例1：查询部门编号是30/50/90的员工名、部门编号
  方式一：

SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

方式二：逻辑表达式

SELECT last_name,department_id
FROM employees
WHERE department_id=30，
OR department_id=50，
OR department_id=90;

• 案例2：查询工种编号不是SH_CLERK或者IT_PROG的员工信息
  注意⚠️：数值型的常量值不用单引号，非数值型的常量值都加单引号
  方式1:

SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');

方式2:

SELECT *
FROM employees
WHERE NOT(job_id='SH_CLERK'
OR job_id='IT_PROG');

BETWEEN AND关键字

• 功能：判断某个字段的值是在指定的区间
  between and / not between and
• 案例1：查询部门编号是30-90之间的部门编号、员工姓名
  方式1:

SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

方式2:

SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;

注意⚠️：between and包含两个临界值；且顺序不能颠倒

• 案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000；

IS NULL/IS NOT NULL

• 案例1：查询没有奖金的员工信息

SELECT *
FROM employees
WHERE commission_pct IS NULL;

• 案例2：查询有奖金的员工信息

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

对比=和IS

= 只能判断普通内容
IS 只能判断NULL值
<=>安全等于，既能判断普通内容，又能判断NULL值，阅读性差

案例讲解

1. 选择姓名中有字母a和e的员工姓名

• 方式1:

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

• 方式2:

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

排序查询

语法

SELECT 查询列表
FROM 表名
【WHERE 筛选条件】可省略
ORDER BY 排序列表（可以多个）

执行顺序

1. from子句
2. where子句
3. select子句
4. order by子句

特点

1. 排序列表可以是单个字段、多个字段、表达式、函数、别名、列数以及以上的组合
2. 升序，通过ASC，默认行为；降序，通过DESC

按单个字段排序

案例1: 将员工编号>120的员工信息进行工资的升序

SELECT *
FROM employees
WHERE employee_id>120
ORDER BY salary ASC;

案例2: 将员工编号>120的员工信息进行工资的降序

SELECT *
FROM employees
WHERE employee_id>120
ORDER BY salary DESC;

按表达式排序

• 案例1: 对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFFULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFFULL(commission_pct,0)) DESC;

按别名排序

• 案例1: 对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+commission_pct) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

注意⚠️：WHERE条件筛选为什么不能用别名？
因为执行顺序是FROM，WHERE，SELECT，别名是在SELECT子句中起的，在执行WHERE子句时还不知道别名。

按函数的结果顺序

• 案例1: 按姓名的字数长度进行升序

SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

按多个字段排序

• 案例1: 查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序

SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

补充选学：按列数排序

-案例：按第二列排序(first_name)

SELECT * FROM employees
ORDER BY 2 DESC;

语意性较差

SELECT