MySQL基础笔记

数据库基本知识

数据库相关概念

DB

• 数据库（database）。存储数据的仓库。它保存了一系列有组织的数据。

DBMS

• 数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器，用于管理DB中的数据
• 常见的数据库管理类系统：MySQL、Oracle、DB2、SqlServer等

SQL

• 结构化查询语言（Structure Query Language）。专门用来与数据库通信的语言。
• SQL的优点：
  • 不是某个特定数据库供应商专有的语言，几乎所有的DBMS都支持SQL
  • 简单易学
  • 虽然简单，但实际上是一种强力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

数据库存储数据的特点

1. 将数据放到表中，表再放到库中
2. 一个数据库可以有多个表，每个表都有一个名字，用来标识自己。表明具有唯一性
3. 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中类的设计
4. 表由列组成，也称之为字段。所有表都是由一个或多个列组成的，每一列类似于Java中的属性
5. 表中的数据是按行存储的，每一行类似于Java中的对象

MySQL常见命令

1. 查看当前所有的数据库

   show databases;
   

2. 打开指定的库

   use 库名;
   

3. 查看当前库的所有表

   show tables;
   

4. 查看其它库的所有表

   show tables from 库名;
   

5. 创建表

   create table 类名(
   	列名 列类型,
       列名 列类型,
       ...
   );
   

6. 查看表结构

   desc 表名;
   

MySQL语法规范

1. 不区分大小写，但建议关键字大写，表名、列名小写
2. 每条命令最好用分号结尾
3. 每条命令根据需要，可以进行缩进或换行
4. 注释
   • 单行注释：#注释文字
   • 单行注释：-- 注释文字
   • 多行注释：/* 注释文字 */

DQL语言的学习

数据查询语言

以下图所示数据库为例进行学习

基础查询

• 语法：select 查询列表 from 表名;

• 特点

  • 查询列表可以是：表中的字段、常量值、表达式、函数

  • 查询的结果是一个虚拟的表格

1、查询表中的单个字段

#查询员工的姓名
SELECT last_name FROM employees;

2、查询表中的多个字段

#查询员工的姓名、工资、邮箱
SELECT last_name,salary,email FROM employees;

3、查询表中的所有字段

#查询表中所有字段
SELECT 
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees ;

或者是

SELECT * FROM employees;

4、查询常量值

SELECT 100;

SELECT 'Link';

5、查询表达式

SELECT 100*8898;

6、查询函数

SELECT VERSION();

7、起别名

优点

1. 提高代码可读性，便于理解

2. 如果要查询的字段有重名的情况，使用别名可以区分开来

• 方式一：使用AS

SELECT 100*8898 AS 结果;

SELECT last_name AS 姓,first_name AS 名 FROM employees;

• 方式二：使用空格

SELECT last_name 姓,first_name 名 FROM employees;

案例：查询salary，显示结果为out put

SELECT salary AS "out put" FROM employees;

8、去重

案例：查询员工表中涉及到的所有的部门编号

SELECT DISTINCT department_id FROM employees;

9、+号的作用

+号仅有一个功能：运算符

• 两个操作数都为数值型，则做加法运算

• 其中一方为字符型，试图将字符型转换成数值型

  • 转换成功，则继续进行加法运算

  SELECT '123'+50;
  

  

  • 转换失败，则将字符型数值转换为0

  SELECT 'Link'+50;
  

  

• 只要其中一方为null，结果肯定是null

SELECT NULL+99798;

案例：查询员工名和姓连接成一个字段，显示为 姓名

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

练习

显示出表employees的全部列，各个之间用逗号链接，列头显示OUT_PUT

#这里只选了一部分列
SELECT
	CONCAT(`first_name`,',',`last_name`,',',`salary`,',',IFNULL(`commission_pct`,0)) AS OUT_PUT
FROM
	employees;

这里使用了一个函数IFNULL(exp1,exp2)，作用是判断exp1里的值若为null，则转化为exp2。

因为commisssion_pet里有些值为null，而查询时有一方为null，则查询结果就为null，所以此处做一个转换。

SELECT 
	IFNULL(commission_pct,0),
	commission_pct 
FROM
	employees;

条件查询

• 语法：select 查询列表 from 表名 where 筛选条件;
• 分类
  • 按条件表达式筛选 条件运算符：> < + != <> >= <= ，其中**<>**是不等于
  • 按逻辑表达式筛选 逻辑表达式：&& || ! and or not
  • 模糊查询
    • like
    • betweeen and
    • in
    • is null

1、按条件表达式筛选

• 案例一：查询工资大于12000的员工信息

SELECT
	*
FROM
	employees
WHERE
	salary>12000;

• 案例二：查询部门编号不等于90号的员工名和部门编号

SELECT
	last_name,department_id
FROM
	employees
WHERE
	department_id<>90;

2、按逻辑表达式筛选

• 案例一：查询工资在10000到20000之间的员工名、工资以及奖金

SELECT
	last_name,salary,commission_pct
FROM
	employees
WHERE
	salary>=10000 AND salary<=20000

• 案例二：查询部门编号不是在90到110之间，或者工资高于15000的工资信息

SELECT
	*
FROM
	employees
WHERE
	NOT(department_id>=90 AND department_id<=110) OR salary>15000;

3、模糊查询

like

一般和通配符搭配使用。

通配符：% 任意多个字符，包含0个字符。

​ 下划线 _ 任意单个字符。

• 案例一：查询员工名中包含字符a的员工信息

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

• 案例二：查询员工名中第三个字符为n，第五个字符为l的员工名和工资

SELECT
	last_name,salary
FROM
	employees
WHERE
	last_name LIKE '__n_l%'

• **案例三：查询员工名第二个字符为 _ 的员工名

  使用转义字符 \

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_\_%';

​ 或者是 ESCAPE 指定一个转义字符

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

between and

• 案例：查询员工编号在100-120之间的员工信息

SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100 AND 120;

in

​ 判断某字段的值是否属于in列表中的某一项

​ 不支持通配符

• 案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的员工名和工种编号

SELECT
	last_name,job_id
FROM
	employees
WHERE
	job_id IN ('IT_PROG','AD_PRES','AD_VP')

is null/is not null

• 案例一：查询没有奖金的员工名和奖金率

SELECT
	last_name,commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL

• 案例二：查询有奖金的员工名和奖金率

SELECT
	last_name,commission_pct
FROM
	employees
WHERE
	commission_pct IS NOT NULL

<=>

安全等于

• 案例一：查询没有奖金的员工名和奖金率

SELECT
	last_name,commission_pct
FROM
	employees
WHERE
	commission_pct <=> NULL

• 案例二：查询工资为12000的员工信息

SELECT
	*
FROM
	employees
WHERE
	salary <=> 12000

练习

• 查询没有奖金，且工资小于18000的salary，last_name

SELECT
	salary,last_name
FROM
	employees
WHERE
	commission_pct IS NULL AND salary<18000;

• 查询employees表中，job_id不为’FI_MGR’或者 工资为12000的员工信息

SELECT
	*
FROM
	employees
WHERE
	job_id<>'FI_MGR' OR salary=12000;

排序查询

语法：select 查询列表 from 表 [where 筛选条件] order by 排序列表 [asc|desc]

ASC代表升序，DESC代表降序，不写默认是升序

• 案例一：查询员工信息，要求工资从高到低排序

SELECT
	*
FROM
	employees
ORDER BY
	salary
DESC;

• 案例二：查询员工信息，要求工资从低到高排序

SELECT
	*
FROM
	employees
ORDER BY
	salary
ASC;

• 案例三：查询部门编号>=90的员工信息，按入职时间的先后进行排序

SELECT
	*
FROM
	employees
WHERE
	department_id>=90
ORDER BY
	hiredate
ASC;

• 案例四：按年薪的高低显示员工的信息和年薪

SELECT
	*,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM
	employees
ORDER BY
	年薪
DESC;

• 案例五：按姓名的长度显示员工的姓名和工资（按函数排序）

SELECT
	LENGTH(last_name) 字节长度,last_name,salary
FROM
	employees
ORDER BY
	LENGTH(last_name)
DESC;

• 案例六：查询员工信息，要求先按工资排序，再按员工编号排序（按多个字段排序）

SELECT
	*
FROM
	employees
ORDER BY 
	salary ASC,
	employee_id DESC;

常见函数

将一组逻辑语句封装在方法内，对外暴露方法名

• **调用：**select 函数名(实参列表) [from 表]

• 分类

  • 单行函数 例：concat、length、ifnull等
  • 分组函数 做统计使用

单行函数

字符函数

• length获取参数值的字节个数

SELECT LENGTH('Link');

#因为采用的是UTF-8编码格式，一个汉字为3个字节
SELECT LENGTH('林克Link');

• concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) FROM employees;

• upper、lower

大小写转换

SELECT UPPER('Link');

SELECT LOWER('Link');

• substr、substring

注意：索引从1开始

#截取从指定索引处后面的所有字符
SELECT SUBSTR('林克去救塞尔达',5);

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('林克去救塞尔达',1,2);

• 案例：姓名中首字符大写，其它字符小写，然后用_拼接，显示出来

SELECT
	CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM
	employees;

• instr

返回字串第一次出现的索引，如果找不到返回0

SELECT INSTR('林克去城堡救塞尔达','塞尔达');