本文共 8787 字，大约阅读时间需要 29 分钟。

MySQL 笔记

数据库概述

数据库是持久化数据的一种介质，可以理解成用来存储和管理数据的仓库！

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。持久化的大多数时候是将内存中的数据存储在数据库中，当然也可以存储在磁盘文件、XML数据文件中。

• DB

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

• DBMS

  数据库管理系统（ Database Management System ）。数据库是通过 DBMS 创

  建和操作的容器

• SQL

  结构化查询语言（ Structure Query Language ）：专门用来与数据库通信的语

  言。

数据库的特点

• 可将数据持久化到硬盘
• 可存储大量数据
• 方便检索
• 保证数据的一致性、完整性
• 安全，可共享
• 通过组合分析，可以产生新数据

常见的数据产品

• Oracle：甲骨文
• DB2：IBM；
• SQL Server：微软；
• MySQL：甲骨文；

数据库存储数据的特点

• 将数据放到表中，表再放到库中
• 一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
• 表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
• 表中的数据是按行存储的，每一行类似于java中的“对象”
• 表——类
• 列，字段——属性
• 行——对象

SQL

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。

语法要求

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

分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

DQL

DQL就是数据查询语言，`数据库执行DQL语句不会对数据进行改变`，而是让数据库发送结果集给客户端。

语法：

SELECT selection_list /*要查询的列名称*/FROM table_list /*要查询的表名称*/WHERE condition /*行条件*/GROUP BY grouping_columns /*对结果分组*/HAVING condition /*分组后的行条件*/ORDER BYsorting_columns /*对结果分组*/LIMIT offset_start, row_count /*结果限定*/

基础查询

1.查询表中的单个字段

SELECT   last_name FROM  employees ;

2.查询表中的多个字段

SELECT   last_name,  salary,  email FROM  employees ;

3. 查询表中的所有字段

SELECT   *   FROM    employees ;

4. 查询常量值

SELECT 100;SELECT 'john';

5. 查询表达式

SELECT 100%98;

6. 查询函数

SELECT VERSION();

7. 起别名 查询字段有重名的情况，使用别名可以加以区分开来
   使用AS

SELECT 100% 98 AS 结果;SELECT   last_name AS 姓,  first_name AS 名 formatemployees ;

使用空格

SELECT   last_name 姓,  first_name 名 FROM  employees ;

使用别名出错时候，对别名加以引号

SELECT   last_name 姓,  first_name 名 FROM  employees ;

8. 去重
   使用distinct

SELECT DISTINCT   department_id FROM  employees ;

9. 加号的作用
   select 100+90; 若两个操作数都为数值型，则做加法运算
   select'123' + 90; 若其中一方为字符型，则试图将字符型数值转换成数值型

如果成功，则继续做加法运算

如果失败，则将字符型数值转换成0，进行运算

select null + 10 任何一方为null，则结果为null

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

SELECT   last_name + first_name AS 姓名 FROM  employees ;

10. concat连接

SELECT   CONCAT('a', 'b', 'c') AS 结果 FROM  employees ;#-------------SELECT   CONCAT(last_name, first_name) AS 结果 FROM  employees ;

11. 显示结构

DESC departments;

小练习题

1. 下面语句是否可以执行成功

SELECT   last_name,  job_id,  salary AS sal FROM  employees ;

2. 下面的语句是否可以执行成功

SELECT   * FROM  employees ;;

3. 找出下面语句中的错误

SELECT   employee_id,  last_name,  salary * 12 “ANNUAL SALARY” FROM  employees ;# ------------------SELECT   employee_id,  last_name,  salary * 12 "ANNUAL SALARY" FROM  employees ;

4. 显示表deparments的结构，并查询其中的全部数据

DESC departments; SECOND * FROM departments;

5. 显示表employees中的全部job_id（不能重复）

SELECT DISTINCT   job_id FROM  employees ;

6. 显示表employees的全部列，各个列直接用逗号连接，列头显示成OUT_PUT

null与其他任何数据拼接都为null

SELECT   CONCAT(    'employes_id',    ',',    'first_name',    ',',    'last_name',    ',',    'email',    ',',    'phone_number',    ',',    'job_id',    ',',    IFNULL(commission_pct, 0)  ) AS 'OUT_PUT' FROM  employees ;

条件查询

语法：

select	查询列表	3from	表名		2where	筛选条件;	1

分类：

• 按条件表达式筛选

  条件运算符：> < =  !=  <>(不等于)

• 按逻辑表达式筛选

  逻辑运算符: && || !  或者 and  or  not (推荐)

• 模糊查询

  like
  • 一般和通配符搭配使用

  %

  • 任意多个字符

  _

  • 任意单个字符

  between and

  • 使用between and 可以提高语句的简介度
    包含临界值
    临界值不可用交换顺序

  in

  • 判断某字段属于in列表的某一项

    in列表的值类型必须一致或者兼容
    可以提高语句的简洁性

    等值号判断，不能使用通配符

  is null

  不能判断null值

  安全等于 （<=>）

  判断是否等于，等于返回true

  但是可读性比较差

  比较：最好用is null 可读性好，安全性高

一. 按条件表达式筛选

案例1： 查询工资> 12000的员工信息

SELECT * FROM employees WHERE salary > 12000;

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

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

二. 按逻辑表达式筛选

案例1：查询工资在10000到20000直接的员工名、工资以及奖金

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

案例2：查询部门编号不是在90到110直接，或工资高于15000的员工信息

SELECT * FROM employees WHERE NOT(department_id >= 90 && department_id <= 110);

三、模糊查询

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

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

案例2： 查询员工名中第三个字符为e，第五个字符为a的员工信息

SELECT   last_name FROM  employees WHERE last_name LIKE '__e_a%' ;

案例3： 查询员工名中第二个字符为_的员工名

SELECT   last_name FROM  employees WHERE last_name LIKE '_\_%' ;SELECT   last_name FROM  employees WHERE last_name LIKE '_$_%' ESCAPE '$' ; # escape 标识为转义字符

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

SELECT   * FROM  employees WHERE employee_id BETWEEN 100   AND 120 ;

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

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

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

SELECT   last_name,  commission_pct FROM  employees WHERE commission_pct IS NULL ;

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

SELECT   last_name,  commission_pct FROM  employees WHERE commission_pct IS NOT NULL ;

四、字段控制查询

去除重复记录（两行或两行以上记录中系列的上的数据都相同）

常见函数

• 数学函数

名称	返回值
ABS（x）	返回x的绝对值
CEIL（x）	返回大于x的最小整数值
FLOOR（x）	返回大于x的最大整数值
MOD(x,y)	返回x/y的模
RAND(x)	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

• 字符串函数

名称	返回值
CONCAT(S1,S2,…,Sn)	连接S1,S2,…,Sn为一个字符串
CONCAT(s, S1,S2,…,Sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上s
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

· 日期时间类函数

名称	返回值
CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW()	↓
SYSDATE()	↓
CURRENT_TIMESTAMP()	↓
LOCALTIME()	↓
LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date)	↓
MONTH(date)	↓
DAY(date)	↓
HOUR(time)	↓
MINUTE(time)	↓
SECOND(time)	返回具体的时间值
WEEK(date)	↓
WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY…SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2)	↓
TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVALE expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

• 分组聚合函数

聚合函数是用来做纵向运算的函数

- COUNT()：统计指定列不为NULL的记录行数；- MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；- MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；- SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；- AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

练习

1. 查询员工工号为176的员工的姓名和部门号和年薪

SELECT   last_name,  department_id,  salary FROM  employees WHERE employee_id = 176 ;

2. 查询工资大于12000的员工姓名和工资

SELECT   last_name,  salary FROM  employees WHERE salary > 12000 ;

3. 选择工资不在5000到12000的员工的姓名和工资

SELECT   last_name,  salary FROM  employees WHERE NOT (salary >= 5000 AND salary <= 12000) ;

4，选择在20或50号部门工作的员工姓名和部门号

SELECT   last_name,  job_id,  department_idFROM  employees WHERE department_id = 20   OR department_id = 50

4. 选择公司中没有管理者的员工姓名以及job_id

SELECT       last_name,      job_id     FROM      employees     WHERE department_id IS NULL ;

5. 选择公司中有奖金的员工姓名，工资和奖金级别

SELECT   last_name,  salary,  commission_pct FROM  employees WHERE last_name IS NOT NULL   AND salary != 0   AND commission_pct IS NOT NULL ;

6. 选择员工姓名的第三个字母是a的员工姓名

SELECT   last_name FROM  employees WHERE last_name LIKE '__a%' ;

7. 选择按年薪降序的员工姓名、年薪

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

8. 选择按月薪降序且月薪不在8000到17000的员工姓名、月薪

SELECT   last_name,  salary FROM  employees WHERE NOT (salary BETWEEN 8000     AND 17000) ORDER BY salary DESC ;

9. 选择邮箱中包含’e’的员工信息，并按邮箱长度降序，部门号升序

SELECT   * FROM  employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,  department_id ;

10. 显示系统时间（日期+时间）

SELECT NOW();;

11. 查询员工号，姓名，工资，以及工资提高20%的结果（new salary）

SELECT   employee_id,  salary,  salary,  salary * 1.2 newsalary FROM  employees ;

12. 将员工的姓名按首字母排序，并写出姓名的长度

SELECT   last_name,  LENGTH(last_name) FROM  employees ORDER BY SUBSTR(last_name, 1, 1) ;

13. 做一个查询，产生下面的结果
    <last_name> earns monthly but wants *3

SELECT   CONCAT(    last_name,    'earns',    salary,    'monthly but wants',    salary * 3  ) FROM  employees ;

14. 查询最大工资，最小工资，平均工资，总工资

SELECT   salary,  MAX(salary),  MIN(salary),  AVG(salary),  SUM(salary) FROM  employees ;

15. 查询工龄最长与工龄最小的员工天数差
    DATEDIFF() 日期差

SELECT   DATEDIFF(MAX(hiredate), MIN(hiredate)) FROM  employees ;

16. 查询部门号为90的员工个数

SELECT   COUNT(department_id = 90) FROM  employees ;

17. 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT   job_id,  MAX(salary) mx_sal FROM  employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000 ;

18. 查询领导编号>105的每个领导收下的最低工资>5000的领导编号及最低工资

SELECT   manager_id,  MIN(salary) FROM  employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000 ;

转载地址：http://iclsn.baihongyu.com/