【蓝旭】第六周预习博客

---

前言

了解MySql基础语法。由于知识点太多，记得比较杂乱，还是实际应用起来了解的快。

---

1、数据库概述及其数据准备

1.1 SQL概述

SQL，全称为Structured Query Language。SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准。但是每一个数据库都有自己的特性（别的数据库没有），当使用这个数据库特性相关的功能时，SQL语句可能就不是标准的了。

SQL：结构化查询语言
程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

1.2 什么是数据库

数据库，通常是一个或一组文件，保存了一些符合特定规格的数据，数据库简称：DB（DataBase），数据库软件称为数据库管理系统（DBMS），如：Orade、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql。

1.3 MySql概述

MySql最初是由“MySql AB”公司开发的一套关系型数据库管理系统。MySql 不仅是最流行的开源数据库，而且是业界成长最快的数据库。

三者之间的关系：
DBMS - -执行 →SQL - - 操作 → DB

1.4 表

数据库当中最基本的单元是表（table）。
表是一种结构化的文件，可以用来存储特定类型的数据，如：学生信息、课程信息，都可以放到表中。
任何一张表都有行和列：
行（row）：被称为数据/记录。
列（column）：被称为字段。

每一个字段都有：字段名称、字段数据类型、字段约束、字段长度等属性。

1.5 SQL的分类

⭐数据查询语言(DQL)：select
⭐数据操纵语言(DML)：insert（增）,delete（删）,update（改）。主要操作表的数据。
⭐数据定义语言(DDL)：create（新建）,drop（删除）,alter（修改)。主要操作表的结构。
⭐事务控制语言(TCL)：commit（事务提交）,rollback（事务回滚）。
⭐数据控制语言(DCL)：grant（授权）,revoke（撤销权限）。

1.6 导入演示数据

使用 MySQL 命令行客户端来装载数据库。
（1）连接 MySql

（2）创建“bjpowernode”数据库
mysql> create database bjpowernode;
（3）选择数据库
mysql> use bjpowernode
（4） 导入数据
mysql>source D:\ bjpowernode.sql
（5）删除数据库(这里不要做!)
mysql> drop database bjpowernode;

1.7 表结构描述

表名称：dept
描述：部门信息表

表名称：emp
描述：员工信息表

注：DEPTNO 字段是外键，DEPTNO 的值来源于 dept 表的主键，起到了约束的作用。

表名称：salgrade
描述：薪水等级信息表

---

2、mysql常用命令

退出mysql ：mysql> exit 或 mysql> \q

查看mysql中有哪些数据库 ： show databases;

选择使用某个数据库 ：
mysql> use test;
Database changed
表示正在使用一个名字叫test的数据库。

创建数据库 ：
mysql> create database bjpowernode;
Query OK, 1 row affected (0.00 sec)

查看mysql数据库的版本号 ：
mysql> select version();

查看当前使用的是哪个数据库 ：
mysql> select database();

中止一条语句：mysql> \c

注意：mysql是不见“；”不执行，“；”表示结束。

3、简单查询

3.1 查询一个字段

select 字段名 from 表名;

注意：select和from都是关键字；字段名和表名都是标识符。

3.2 查询两个或多个字段

使用“，”隔开。
例如：查询员工的编号和姓名

3.3 查询所有字段

第一种方式：可以把每个字段都写上；
第二种方式：可以使用*
select * from emp;

4、条件查询

条件查询需要用到 where 语句，where 必须放到 from 语句表的后面.

4.1 等号操作符

查询薪水为 5000 的员工：

select empno, ename, sal from emp where sal=5000;

查询 job 为 MANAGER 的员工:

select empno, ename from emp where job=“manager”;
(也可以使用单引号)

4.2 <>操作符

查询薪水不等于 5000 的员工：

select empno, ename, sal from emp where sal <> 5000;

4.3 between … and …操作符

查询薪水为 1600 到 3000 的员工(第一种方式，采用>=和<=)：

select empno, ename, sal from emp where sal >= 1600 and sal <= 3000;

查询薪水为 1600 到 3000 的员工(第二种方式，采用 between …and …)：

select empno, ename, sal from emp where sal between 1600 and 3000;

注意：between …and …包含最大值和最小值。

4.4 is null

Null 为空，但不是空串，为 null 可以设置这个字段不填值，如果查询为 null 的字段，采用 is null.

查询津贴为空的员工：

select * from emp where comm is null;

4.5 and

and 表示并且的含义，表示所有的条件必须满足。

工作岗位为 MANAGER,薪水大于 2500 的员工：

select * from emp where job=‘MANAGER’ and sal > 2500;

4.6 or

or，只要满足条件即可,相当于包含。

查询出 job 为 manager 或者 job 为 salesman 的员工：

select * from emp where job=‘MANAGER’ or job=‘SALESMAN’;

4.7 in

in 表示包含的意思，完全可以采用 or 来表示，采用 in 会更简洁一些。

查询出 job 为 manager 或者 job 为 salesman 的员工：

select * from emp where job in (‘manager’,‘salesman’);

查询出薪水包含 1600 和薪水包含 3000 的员工：

select * from emp where sal in(1600, 3000);

4.8 not

查询出薪水不包含 1600 和薪水不包含 3000 的员工（第一种写法）：

select * from emp where sal <> 1600 and sal <> 3000;

查询出薪水不包含 1600 和薪水不包含 3000 的员工（第二种写法）：

select * from emp where not (sal = 1600 or sal = 3000);

查询出薪水不包含 1600 和薪水不包含 3000 的员工（第三种写法）：

select * from emp where sal not in (1600, 3000);

查询出津贴不为 null 的所有员工：

select * from emp where comm is not null;

4.9 like

Like 可以实现模糊查询，like 支持%和下划线匹配。

查询姓名以 M 开头所有的员工：

select * from emp where ename like ‘M%’;

查询姓名以 N 结尾的所有的员工：

select * from emp where ename like ‘%N’;

查询姓名中包含 O 的所有的员工：

select * from emp where ename like ‘%O%’;

查询姓名中第二个字符为 A 的所有员工：

select * from emp where ename like ‘_A%’;

%匹配任意字符出现的个数
下划线只匹配一个字符
Like 中的表达式必须放到单引号 / 双引号中

5、排序数据

5.1 单一字段排序

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升序，如果存在 where 子句那么 order by 必须放到 where 语句的后面

按照薪水由小到大排序(系统默认由小到大)：

select * from emp order by sal;

取得 job 为 MANAGER 的员工，按照薪水由小到大排序(系统默认由小到大)：

select * from emp where job=‘MANAGER’ order by sal;

如果包含 where 语句 order by 必须放到 where 后面，如果没有 where 语句 order by 放到表的后面
按照多个字段排序，如：首先按照 job 排序，再按照 sal 排序

select * from emp order by job,sal;

5.2 手动指定排序顺序

手动指定按照薪水由小到大排序：

select * from emp order by sal asc;

手动指定按照薪水由大到小排序：

select * from emp order by sal desc;

5.3 多个字段排序

按照 job 和薪水倒序：

select * from emp order by job desc, sal desc;

如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

6、数据处理函数/单行处理函数

6.1 lower

查询员工，将员工姓名全部转换成小写：

select lower(ename) from emp;

6.2 upper

查询 job 为 manager 的员工：

select * from emp where job=upper(‘manager’);

6.3 substr

查询姓名以 M 开头所有的员工：

select * from emp where substr(ename, 1, 1)=upper(‘m’);

6.4 length

取得员工姓名长度为 5 的：

select length(ename), ename from emp where length(ename)=5;

6.5 trim

trim 会去首尾空格，不会去除中间的空格
取得工作岗位为 manager 的所有员工：

select * from emp where job=trim(upper('manager '));

6.6 str_to_date

查询 1981-02-20 入职的员工（第一种方法，与数据库的格式匹配上）:

select * from emp where HIREDATE=‘1981-02-20’;

查询 1981-02-20 入职的员工（第二种方法，将字符串转换成 date 类型）：

select * from emp where HIREDATE=str_to_date(‘1981-02-20’,‘%Y-%m-%d’);
select * from emp where HIREDATE=str_to_date(‘02-20-1981’,‘%m-%d-%Y’);

str_to_date 可以将字符串转换成日期，具体格式 str_to_date (字符串，匹配格式)

6.7 date_format

查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss

select empno, ename, date_format(hiredate, ‘%Y-%m-%d %H:%i:%s’) as hiredate from emp;

now() 获得当前时间
日期格式的说明
%Y：代表 4 位的年份
%y：代表 2 位的年份
37 / 103
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%H：代表小时,格式为(00……23)
%h： 代表小时,格式为(01……12)
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为 12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为 24 小时(hh:mm:ss)
%S：代表 秒,格式为(00……59)
%s：代表 秒,格式为(00……59)

6.8 format

查询员工薪水加入千分位：

select empno, ename, Format(sal, 0) from emp;

查询员工薪水加入千分位和保留两位小数：

select empno, ename, Format(sal, 2) from emp;

6.9 round

四舍五入：

select round(123.56);

6.10 rand()

生成随机数：

select rand();

随机抽取记录数：

select * from emp order by rand() limit 2;

order by 必须写上

6.11 case … when … then ……else …end

如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%：

select empno, ename, job, sal, case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 end as newsal from emp;

其他的工资不动，需要添加 else

select e.,sal ,case job when ‘salesman’ then sal1.1 when ‘clerk’ then sal1.2 else sal end as new_sal from emp e;
e.:取 emp 表所有的字段 , emp as e 是表的别名可以省略 as emp e

6.12 ifnull

select ifnull(comm,0) from emp;

如果 comm 为 null 就替换为 0 在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL
为了防止计算结果出现 NULL，建议先使用 ifnull 空值处理函数预先处理。
以下 SQL 是计算年薪的：

select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;

数据处理函数又被称为单行处理函数，特点：输入一行输出一行

7、分组函数/聚合函数/多行处理函数

注意：分组函数自动忽略空值，不需要手动的加 where 条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。
select count(comm) from emp; comm 这个字段中不为空的元素总数。
注意：分组函数不能直接使用在 where 关键字后面。

8、分组查询

分组查询主要涉及到两个子句，分别是：group by 和 having

8.1 group by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计：

select job, sum(sal) from emp group by job;

在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟分组函数+参与分组的字段。

8.2 having

如果想对分组数据再进行过滤需要使用 having 子句

取得每个岗位的平均工资大于 2000：

select job, avg(sal) from emp group by job having avg(sal) >2000;

分组函数的执行顺序：

根据条件查询数据
分组
采用 having 过滤，取得正确的数据

8.3 select 语句总结

一个完整的 select 语句格式如下：

select 字段
from 表名
where …….
group by ………
having …….(就是为了过滤分组后的数据而存在的，不可以单独的出现)
order by ………

执行顺序:

1. 首先执行 where 语句过滤原始数据
2. 执行 group by 进行分组
3. 执行 having 对分组数据进行操作
4. 执行 select 选出数据
5. 执行 order by 排序

总结

写的挺水的，凑活着看。
还有好多知识点。