Mysql 基础

1 登录Mysql

mysql -hlocalhost -uroot -p

• -h：host主机名。后面跟要访问的数据库服务器地址；如果是本机可以省略
• -u：user 用户名。后面跟登录数据的用户名，第一次安装后以root用户登录，是Mysql的管理用户
• -p：password 密码。一般不直接输入，而是回车后保密输入

# 访问异地 Mysql
mysql -h 172.20.113.116 -uroot -p

# 本地登录
mysql -uroot -p

2 访问数据库

show databases：显示Mysql中数据库列表，默认有四个自带的数据库

use mysql：切换当前数据库命令

show tables：查看表

select * from user：查看表的字段

# 切换当前数据库
use mysql

mysql> use mysql
Database changed


# 查看表
show tables

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)
mysql>

# 查看表的字段
mysql> select * from user;

3 退出数据库

• quit
• exit
• \q

# 三种退出方式

mysql> quit
Bye
root@36d6babd1630:/#

mysql> exit
Bye
root@36d6babd1630:/#

mysql> \q
Bye
root@36d6babd1630:/#

SQL语言分为五个部分：

1、数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用select子句，from 子句和 where子句的组合来查询一条或多条数据

2、数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据库中的数据进行增加、修改、删除的操作，其主要包括：

1. INSERT：增加数据
2. UPDATE：修改数据
3. DELETE：删除数据

3、数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作，其主要包括：

1. CREARTE：创建数据库对象
2. ALTER：修改数据库对象
3. DROP：删除数据库对象

4、数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数据库的权限，其主要包括：

1. GRANT：授予用户某种权限
2. REVOKE：回收授予的某种权限

5、事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：

1. START TRANSACTION：开启事务
2. COMMIT：提交事务
3. ROLLBACK：回滚事务
4. SET TRANSACTION：设置事务的属性

navicat 创建数据库表

一、创建数据库

二、创建表

SQL语法

• #单行注释，或者杠杠空格“-- ”
• /*多行注释*/
• desc t_student：查看表的结构：展示表的字段详细信息
• select * from t_student：查看表中的数据
• show create table t_student：查看建表语句

-- 创建表 t_student
create table t_student(
sno int(6), -- 6显示长度
sname varchar(5), -- 5个字符
sex char(1),
age int(3),
enterdate date,
classname varchar(10),
email varchar(10)
);


-- 查看表的结构：展示表的字段详细信息
desc t_student;
/*
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sno       | int(6)      | YES  |     | NULL    |       |
| sname     | varchar(5)  | YES  |     | NULL    |       |
| sex       | char(1)     | YES  |     | NULL    |       |
| age       | int(3)      | YES  |     | NULL    |       |
| enterdate | date        | YES  |     | NULL    |       |
| classname | varchar(10) | YES  |     | NULL    |       |
| email     | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
*/

-- 查看表中的数据；
select * from t_student;
/*
+------+--------------+------+------+------------+--------------+------------+
| sno  | sname        | sex  | age  | enterdate  | classname    | email      |
+------+--------------+------+------+------------+--------------+------------+
|    1 | 张飞         | 男   |   50 | 2021-09-01 | 软件一班     | zf@163.com |
|    2 | 周瑜         | 男   |   50 | 2023-11-04 | 软件一班     | zy@163.com |
|    3 | 刘备         | 男   |   50 | 2023-11-04 | 软件一班     | lb@163.com |
|    4 | 关羽         | 男   |   50 | 2023-11-04 | 软件一班     | gy@163.com |
|    5 | 诸葛孔明     | NULL | NULL | NULL       | 网络一班     | NULL       |
|    6 | 司马懿       | NULL | NULL | NULL       | 网络一班     | NULL       |
+------+--------------+------+------+------------+--------------+------------+
*/

-- 查看建表语句：
show create table t_student;

/*
CREATE TABLE `t_student` (
  `sno` int(6) DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

*/

数据库表列类型

1、整数型

整数类型	大小	表数范围（有符号）	表数范围（无符号）	作用
TINYINT	1 字节	（-128，127）	（0，255）	小整数值
SMALLINT	2 字节	（-32768，32767）	（0，65535）	大整数值
MEDIUMINT	3 字节	（-8388608,8388607）	（0,16777215）	大整数值
INT/INTEGER	4 字节	（-2147483648,2147483647）	（0,4294967295）	大整数值
BIGINT	8 字节	（-9233372036854775808, 9223372036854775807）	（0, 18445744073709551615）	极大整数值

Mysql支持选择在该类型关键字后面的括号内指定数值的显示宽度（例如，INT(4)）显示宽度并不限制可以在列内保存的数值的范围，也不限制主键自增：不使用序列，通过auto_increment，要求是整数类型

2、浮点数类型

浮点数类型	大小	作用
FLOAT	4 字节	单精度浮点数值
DOUBLE	8 字节	双精度浮点数值

3、字符串类型

字符串类型	大小	描述
CHAR(M)	0~255 字符	允许长度 0~M 个字符的定长字符串
VARCHAR(M)	0~65535 字符	允许长度 0~M 个字符的变长字符串
BINARY(M)	0~255 字节	允许长度 0~M 个字符的定长二进制字符串
VARBINARY(M)	0~65535 字节	允许长度 0~M 个字符的变长二进制字符串
TINYBLOB	0~255 字节	二进制形式的短文本数据（长度为不超过255个）
TINYTEXT	0~255 字节	短文本数据
BLOB	binary  large  object	二进制形式的长文本数据
TEXT		长文本数据
MEDIUMBLOB	0~16777215 字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215 字节	中等长度文本数据
LOGNBLOB	0~4294967295 字节	二进制形式的极大文本数据
LONGTEXT	0~4294967295 字节	极大文本数据

4、日期和时间类型

类型	格式	取值范围	0 值
TIME	'HH:MM:SS'	('-838:59:59'，'838:59:59')	'00:00:00'
DATE	'YYYY-MM-DD'	('1000-01-01'，'9999-12-31')	'0000-00-00'
YEAR	YYYY	(1901，2155)，0000	0000
DATETIME	'YYYY-MM-DD HH:MM:SS'	('1000-01-01 00:00:00'; '9999-12-31 23:59:59')	'0000-00-00 00:00:00'
TIMESTAMP 时间戳	'YYYY-MM-DD HH:MM:SS'	('1970-01-01 00:00:01'UTC '2038-01-19 03:14:07'UTC)	'0000-00-00 00:00:00'

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：

（1）数据的取值范围不同，TIMESTEMP类型的取值范围更小

（2）如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，Mysql会自动将该字段赋值为系统当前的日期与时间

（3）TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取当前系统的时间

（4）TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。不同的时区存储的时间也不一样

三、在数据库中增加、修改、删除数据

增加数据

insert into 表名 values(字段内容)

• insert into t_student values(1,"张飞","男","50","2021-9-1","软件一班","zf@163.com")

insert into 表名(需要插入数据的字段名) values(对应字段的值)

• insert into t_student(sno,sname,classname) values(5,"诸葛孔明","网络一班")

-- 查看表记录
select * from t_student;


-- 在t_student数据库中插入数据
insert into t_student values(1,"张飞","男","50","2021-9-1","软件一班","zf@163.com")
insert into t_student values(1,"周瑜","男","50",now(),"软件一班","zy@163.com")
insert into t_student values(1,"刘备","男","50",sysdate(),"软件一班","lb@163.com")
insert into t_student values(1,"关羽","男","50",CURRENT_DATE(),"软件一班","gy@163.com")
/*
NOW()和SYSDATE()都是一样的  指的是当前时间  精确到时分秒
CURRENT_DATE是精确到日期
很好记  date指的是日期  而now指的是当前  sysdate指的是系统时间
*/


-- 在自定义位置插入字段
insert into t_student(sno,sname,classname) values(5,"诸葛孔明","网络一班")

修改数据

update 表名 set 需要修改的字段 where 查找目标字段

• update t_student set sno = 2 where sname ="周瑜"

-- 修改表中数据，不区分大小写 where 查询的可以是任意存在的字段
update t_student set sno = 2 where sname ="周瑜" -- 这里周瑜sno为1 将其修改为2，选择姓名为筛选目标
update t_student set sno = 3 where sname = '刘备'
update t_student set SNO = 4 where SNAME = '关羽'

删除数据

delete from 表名 where 查找目标字段

• delete from t_student where sname = 't'

-- 删除表数据
delete from t_student where sname = 't' -- 在Mysql中delete后面必须要加 from 否者会报错 Oracle可以不写

添加新列

1、在末尾添加新列

alter table 表名 add 列名 字段类型

• alter table t_student add score double(5,2)

2、在第一列添加新列

alter table 表名 add 列名 字段类型 first 

• alter table t_student add score double(5,2) first 

3、在某一列后面添加新列

alter table 表名 add 列名 字段类型 after sex  

• alter table t_student add score double(5,2) after sex  

alter table t_student add score double(5,2) -- 添加一列 5：总位数 2: 小数位数 默认添加在最后一列

-- 在第一列添加新列
alter table t_student add score double(5,2) first 

-- 在某一列后面添加新列
alter table t_student add score double(5,2) after sex  

修改一列

alter table 表名 modify 列名 字节类型

• alter table t_student modify score float(4,1)

alter table 表名 change 列名 要修改的列名 字节类型

• alter table t_student change score score1 double(5,1)

-- 修改一列
alter table t_student modify score float(4,1) -- modify修改是列的类型的定义，但是不会改变列的名字
alter table t_student change score score1 double(5,1) -- change修改列名和列的类型的定义

删除一列

alter table 表名 drop 列名

• alter table t_student drop score

-- 删除一列
alter table t_student drop score 

删除表

drop table 表名 

• drop table t_student1

-- 删除表
drop table t_student1 

DDL和DML的补充

快速添加

-- 添加一张表：快速添加，数据和结构跟t_student 都是一致的
create table t_student1 -- 新建表
as
select * from t_student -- 需要快速添加的表名

select * from t_student1


-- 快速添加，结构更t_student一致，没有数据
create table t_student2
as
select * from t_student where 1=2 -- where 1=2 为假所有只有表结构没用表数据

select * from t_student2


-- 快速添加：只要部分列，部分数据

create table t_student3 -- 添加一条数据
as
select sno,sname,classname from t_student where sno = 3

select * from t_student3

-- 添加全部
create table t_student4
as
select sno,sname,classname from t_student

select * from t_student4

DELETE 和 TRUNCATE

-- 两者的区别是DELETE是DML语言将，TRUNCATE是DDL语言
delete from  t_student3 -- 将表中所有记录一条一条删除直到删完
/*
delete from t_student5
> Affected rows: 6
> 时间: 0.015s
*/
truncate t_student4 -- 保留了表结构，重新创建了这个表，所有的状态相当于新表，相比DELETE操作效率更高
/*
truncate t_student6
> OK
> 时间: 0.07s


DELETE操作可以回滚，TRUNCATE操作会导致隐式提交，因此不回滚
DELETE操作执行成功后会返回删除的行数，截断操作不会返回删除的量，DELETE操作删除表中记录后，再次添加新纪录时，对于设置有自增约束字段的值会从删除前表中字段的最大加1开始自增，TRUNCATE则会重新从1开始自增
*/

练习表

create table DEPT(  
  DEPTNO int(2) not null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)  
);  
alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
        
create table EMP  
(  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),  
  MGR      int(4),  
  HIREDATE DATE,  
  SAL      double(7,2),  
  COMM     double(7,2),  
  DEPTNO   int(2)  
);  
alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
        
create table SALGRADE  
(  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
create table BONUS  
(  
  ENAME VARCHAR(10),  
  JOB   VARCHAR(9),  
  SAL   double(7,2),  
  COMM  double(7,2)  
);  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (40, 'OPERATIONS', 'BOSTON');  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (5, 3001, 9999);  
-- 查看表：
select * from DEPT; 
-- 部门表：dept:department 部分 ，loc - location 位置
select * from EMP;
-- 员工表：emp:employee 员工   ,mgr :manager上级领导编号，hiredate 入职日期  firedate 解雇日期 ，common：补助
-- deptno 外键 参考  dept - deptno字段
-- mgr 外键  参考  自身表emp - empno  产生了自关联
select * from SALGRADE;
-- losal - lowsal
-- hisal - highsal
select * from BONUS;

最简单的SQL语句

查询

• select * from EMP -- *代表所有数据
• select EMPNO,ENAME from EMP  -- 显示部分列
• select * from EMP where SAL > 2000 -- 显示部分行，where子句
• select EMPNO,ENAME,JOB,MGR from EMP where SAL > 2000 -- 显示部分列部分行

where 子句

将过滤条件放在where子句的后面，可以筛选，过滤出我们想要的符合条件的数据

where 子句 + 关系运算

起别名

以下两种方法效果一样，as是alias的缩写

• select EMPNO 员工编号,ENAME 姓名,SAL 工资 from EMP
• select EMPNO as 员工编号,ENAME as 姓名,SAL as 工资 from EMP
• select EMPNO as '员工编号',ENAME as "姓名",SAL as 工资 from EMP -- 别名用单引号和双引号或者不用引号都可以，如果别名中有特殊字符则必须要引号（例如空格等）

算数运算符

• select EMPNO,ENAME,SAL,SAL+1000 as '涨薪后',DEPTNO from EMP where SAL < 2500
• select EMPNO,ENAME,SAL,COMM,SAL+COMM from EMP -- 之后讲

去重：distinct 

• select job from EMP
• select distinct job from EMP
• select distinct job,deptno from EMP -- 对后面的所有组合去重，而不是单独的某一列去重

排序 asc、desc

• select * from EMP order by sal asc  -- 升序，可以默认不写
• select * from EMP order by sal desc -- desc 降序

select * from EMP -- *代表所有数据

select EMPNO,ENAME from EMP  -- 显示部分列

select * from EMP where SAL > 2000 -- 显示部分行，where子句

select EMPNO,ENAME,JOB,MGR from EMP where SAL > 2000 -- 显示部分列部分行

-- 起别名：以下两种方法效果一样
select EMPNO 员工编号,ENAME 姓名,SAL 工资 from EMP

-- as是alias的缩写 

select EMPNO as 员工编号,ENAME as 姓名,SAL as 工资 from EMP

select EMPNO as '员工编号',ENAME as "姓名",SAL as 工资 from EMP -- 别名用单引号和双引号或者不用引号都可以，如果别名中有特殊字符则必须要引号（例如空格等）


-- 算数运算符：
select EMPNO,ENAME,SAL,SAL+1000 as '涨薪后',DEPTNO from EMP where SAL < 2500

select EMPNO,ENAME,SAL,COMM,SAL+COMM from EMP -- 之后讲

-- 去重：distinct 
select job from EMP
select distinct job from EMP
select distinct job,deptno from EMP -- 对后面的所有组合去重，而不是单独的某一列去重

-- 排序

select * from EMP order by sal
select * from EMP order by sal asc -- 升序，可以默认不写
select * from EMP order by sal desc -- desc 降序
select * from EMP order by SAL asc ,DEPTNO desc


-- 查看EMP表：
select * from EMP

select * from EMP where DEPTNO = 10
select * from EMP where DEPTNO < 10
select * from EMP where DEPTNO <> 10 -- 不等于
select * from EMP where binary job = 'clerk' -- 不区分大小写

-- 逻辑运算符 and
select * from EMP where sal > 1500 and sal < 3000 -- 查询1500-3000，不包含1500和3000 
select * from EMP where sal > 1500 && sal < 3000

select * from EMP where sal between 1500 and 3000 -- 查询1500-3000，包含1500和3000

-- 逻辑运算符 or
select * from EMP where DEPTNO = 10 or DEPTNO = 20
select * from EMP where DEPTNO = 10 || DEPTNO = 20
select * from EMP where DEPTNO in (10,20)

-- where 子句+模糊查询
-- 查询名字带A的员工

select * from EMP where ENAME  like '%A%' -- %代表任意多个字符
select * from EMP where ENAME  like '_A%' -- 查询第二个字母是A的，-代表任意一个字符


-- 关于null的判断
select * from EMP where comm is null -- 查询为空的
select * from EMP where comm is not null -- 查询不为空的

-- 小括号的使用：因为不同的运算符优先级的不同，加括号为了可读性


select * from EMP where JOB ='SALESMAN' or JOB = 'CLERK' and sal >=1500 -- 先and在or,and优先级大于or
select * from EMP where JOB ='SALESMAN' or (JOB = 'CLERK' and sal >=1500)
select * from EMP where (JOB ='SALESMAN' or JOB = 'CLERK') and sal >=1500

函数

单行函数

大小写转换

小写转换：

select lower(列名) from 表名

• select lower(ename) from EMP

大写转换：
select upper(列名) from 表名

• select upper(ename) from EMP

PS：除了多行函数（max,min,count,sum,avg），都是单行函数

多行函数

函数	描述
count()	统计表中记录的数目
sum()	计算指定字段的总和
avg()	计算指定字段值的平均值
max()	统计指定字段值的最大值
min()	统计指定字段值的最小值

-- 单行函数
select empno,ename,lower(ename),upper(ename),sal from EMP 
-- 函数的功能：封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能
-- 函数作用：为了提高select的能力
-- 注意：函数没有改变数据自身的值，而是在真实数据上进行加工处理，展示新的结果而已

-- 单行函数包括
-- 1、字符串函数
select ename,length(ename),substring(ename,2,3) from EMP
-- substring字符串截取，从字符下标为2开始，截取长度3 （下标从1开始）

-- 2、数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual -- dual实际就是一个伪装
select abs(-5)绝对值,ceil(5.3)向上取整,floor(5.9)向下取整,round(3.14) 四舍五入 -- 如果没有where条件的话，from dual可以省略不写

-- 3、日期与时间函数
select * from EMP
select curdate(),curtime() -- curdate()年月日 curtime()时分秒
select now(),sysdate(),sleep(3),now(),sysdate() from dual -- now(),sysdate()年月日时分秒


-- 4、流程函数

-- if相关
select empno,ename,sal,if(sal >= 2500,'高薪','底薪') as '薪资等级' from EMP
select empno,ename,sal,comm,sal+ifnull(comm,0) from EMP -- 如果是comm是null
select nullif(1,1),nullif(1,2) from dual -- 如果value1等于value2，则返回null 否则返回valuel

-- case相关
-- case等值判断
select empno,ename,job,
case job
when 'CLERK' then '店员'
when 'SALESMAN' then '销售'
when 'manager' then '经理'
else '其他'
end '岗位',
sal from EMP;

-- case 区间判断
select empno,ename,sal,
case
when sal<=1000 then 'A'
when sal<=2000 then 'b'
when sal<=1000 then 'c'
else 'D'
end '工资等级',
DEPTNO from EMP

-- 5、JSON函数

-- 6、其他函数
select database(),user(),version() from dual


-- 多行函数：
/*
count：统计表中记录的数目
sum：计算指定字段的总和
avg：计算指定字段值的平均值
max：统计指定字段值的最大值
min：统计指定字段值的最小值
*/

select max(sal),min(sal),count(sal),sum(sal)/count(sal),avg(sal) from EMP
select * from EMP
-- 多行函数自动忽略null值
select max(comm),min(comm),count(comm),sum(comm)/count(comm),avg(comm) from EMP

-- max(),min(),count()针对所有类型 sum(),avg() 只针对数值类型有效
select max(ename),min(ename),count(ename),sum(ename)/count(ename),avg(ename) from EMP


-- count 计数
-- 统计表的记录数：方式1
select count(*) from EMP
select count(comm) from EMP


-- 统计表的记录数：方式2
select 1 from dual
select 1 from EMP
select count(1) from EMP

分组group by

select 列名,使用函数(列名) from 表名 group by 列名 

• select job,avg(sal) from EMP group by job 

select * from EMP
-- 统计各部门的平均工资
select deptno,avg(sal) from EMP -- 字段和多行函数不可以同时使用
select deptno,avg(sal) from EMP group by deptno -- 字段和多行函数不可以同时使用，除非这个字段属于分组
select deptno,avg(sal) from EMP group by deptno order by deptno desc -- 还可以进行降序排序


-- 统计各个岗位的平均工资
select job,avg(sal) from EMP group by job 
select job,lower(job),avg(sal) from EMP group by job -- 可以在查询时加入其他语句或多个列名

having分组后筛选

select 列名,使用函数(列名) from 表名 group by 列名 having 使用函数(列名) 筛选动作

• select deptno,avg(sal) from EMP group by deptno having avg(sal) > 2000

-- having分组后筛选

-- 统计各个部门的平均工资，只显示平均工资2000以上的，分组后进行二次筛选 
select deptno,avg(sal) from EMP group by deptno having avg(sal) > 2000
select deptno,avg(sal) from EMP group by deptno having avg(sal) > 2000 order by avg(sal) desc -- 查询deptno sal的平均值筛选小于2000的按照sal的平均值进行降序排列

-- 统计各岗位的平均工资，除了manager
-- 方法1：
select job,avg(sal) from EMP where job !='manager' group by job
-- 方法2：
select job,avg(sal) from EMP group by job having job != 'manager'
-- where在分组前进行过滤的，having在分组后进行过滤

单表查询总结

【1】select语句总结
select column, group_function(column) 
from table 
[where condition] 
[group by  group_by_expression] 
[having group_condition] 
[order by column]; 

注意：顺序固定，不可以改变顺序

【2】select语句的执行顺序
from--where -- group  by–  select   -  having-   order  by

单表查询练习

-- 单表查询练习
-- 列出工资最小值小于2000的职位
select * from EMP
select job,min(sal) from EMP group by job having min(sal) < 2000 

-- 列出平均工资大于1200元的部门和工作搭配组合
select deptno,job,avg(sal) from EMP group by job,deptno having avg(sal) > 1200 order by deptno 

-- 统计[人数小于4的]部门的平均工资
select deptno,count(1),avg(sal) from EMP group by deptno having count(1) <4
select deptno,count(ename),avg(sal) from EMP group by deptno having count(ename) <4

-- 统计各部门的最高工资，排除最高工资小于3000的部门
select deptno,max(sal) from EMP group by deptno having max(sal) < 3000

多表查询语法

多表连接查询的类型：

1.交叉连接 cross joji 2.自然连接 natural join 3.内连接 using子句 4.内连接 on子句
综合看内连接 on子句

条件
1.筛选条件 where having 
2.连接条件 on,using,natural
SQL99语法筛选条件和连接条件是分开的

1、交叉连接：cross join

select 表字段 from 第一张表名 连接语句 第二张表名

• select * from EMP cross join DEPT

-- 交叉连接：cross join，cross在mysql可以省略不写，oracle中不可以
select * from EMP cross join DEPT; -- 14*4 = 56条 笛卡尔乘积 ： 没有实际意义，有理论意义

select * from EMP join DEPT;

PS：cross在mysql可以省略不写，oracle除外

2、自然连接：natural join

select 表字段 from 第一张表名 连接语句 第二张表名

• select * from EMP natural join DEPT

-- 自然连接：natural join 
-- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单
select * from EMP natural join DEPT;

select empno,ename,sal,dname,loc from EMP natural join DEPT;
-- 缺点： 查询字段的时候，没有指定字段所属的数据库表，效率低
-- 解决方法： 指定表名：

优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单

缺点： 查询字段的时候，没有指定字段所属的数据库表，效率低

解决方法： 指定表名

• select EMP.empno,EMP.ename,EMP.sal,DEPT.dname,DEPT.loc,DEPT.deptno 
• from EMP 
• natural join DEPT

select EMP.empno,EMP.ename,EMP.sal,DEPT.dname,DEPT.loc,DEPT.deptno 
from EMP 
natural join DEPT;
-- 缺点：表名太长
-- 解决方法：表起别名

缺点：表名太长
解决方法：表起别名

• select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
• from EMP e
• natural join DEPT d

select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
from EMP e
natural join DEPT d;

-- 自然连接 natural joji 缺点自动匹配表中所有的同名列但是有时候我们希望只匹配部分列
-- 解决方法：using子句

自然连接 natural joji 缺点自动匹配表中所有的同名列但是有时候我们希望只匹配部分列

解决方法：using子句

3、内连接：using

• select * 
• from EMP e 
• inner join DEPT d -- inner可以不写
• using(deptno) -- 这里就不能写natural join了，这里是内连接

using缺点：关联的字段必须是同名的
解决方法：内连接 on子句：

-- 内连接：using

select * 
from EMP e 
inner join DEPT d -- inner可以不写
using(deptno) -- 这里就不能写natural join了，这里是内连接

-- using缺点：关联的字段必须是同名的
-- 解决方法：内连接 on子句：

4、内连接：on子句

• select * from EMP e inner join DEPT d on (e.deptno = d.deptno)

多表连接查询的类型：1.交叉连接 cross joji 2.自然连接 natural join 3.内连接 using子句 4.内连接 on子句
综合看内连接 on子句

-- 内连接：on子句

select *
from EMP e
inner join DEPT d
on (e.deptno = d.deptno)

-- 多表连接查询的类型：1.交叉连接 cross joji 2.自然连接 natural join 3.内连接 using子句 4.内连接 on子句
-- 综合看内连接 on子句
select *
from EMP e
inner join DEPT d
on (e.deptno = d.deptno)
where sal > 3500

-- 条件
-- 1.筛选条件 where having 
-- 2.连接条件 on,using,natural
-- SQL99语法筛选条件和连接条件是分开的

条件：
1.筛选条件 where having 
2.连接条件 on,using,natural
SQL99语法筛选条件和连接条件是分开的

5、外连接：inner join - on

子句显示的是所有匹配的信息

• select *
• from EMP e
• inner join DEPT d
• on e.deptno = d.deptno

外连接：除了显示匹配的数据之外，还可以显示不匹配的数据

-- inner join - on 子句：显示的是所有匹配的信息
select *
from EMP e
inner join DEPT d
on e.deptno = d.deptno

6、左外连接：left outer join

左外连接：left outer join -- 左面的那个表的信息，即使不匹配也可以查看出效果

• select *
• from EMP e
• left outer join DEPT d
• on e.deptno = d.deptno

-- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据


-- 左外连接：left outer join -- 左面的那个表的信息，即使不匹配也可以查看出效果
select *
from EMP e
left outer join DEPT d
on e.deptno = d.deptno

7、右外连接：right outer join

右外连接：right outer join -- 左面的那个表的信息，即使不匹配也可以查看出效果

• select *
• from EMP e
• right outer join DEPT d
• on e.deptno = d.deptno

-- 右外连接：right outer join -- 左面的那个表的信息，即使不匹配也可以查看出效果
select *
from EMP e
right outer join DEPT d
on e.deptno = d.deptno

8、全外连接 full outer join

 全外连接 full outer join -- 这个语法在MySQL中不支持，在Oracle中支持 -- 展示左右全部不匹配的数据
Scott，40号部门都可以看到

• select *
• from EMP e
• full outer join DEPT d
• on e.deptno = d.deptno

-- 全外连接 full outer join -- 这个语法在MySQL中不支持，在Oracle中支持 -- 展示左右全部不匹配的数据
-- Scott，40号部门都可以看到
select *
from EMP e
full outer join DEPT d
on e.deptno = d.deptno

解决MySQL中不支持全外连接的问题

9、并集去重

• select *
• from EMP e
• left outer join DEPT d
• on e.deptno = d.deptno
• union -- 并集 去重 效率低
• select *
• from EMP e
• right outer join DEPT d
• on e.deptno = d.deptno

select *
from EMP e
left outer join DEPT d
on e.deptno = d.deptno
union -- 并集 去重 效率低
select *
from EMP e
right outer join DEPT d
on e.deptno = d.deptno

10、并集不去重

• select *
• from EMP e
• left outer join DEPT d
• on e.deptno = d.deptno
• union all -- 并集 不去重 效率高
• select *
• from EMP e
• right outer join DEPT d
• on e.deptno = d.deptno

select *
from EMP e
left outer join DEPT d
on e.deptno = d.deptno
union all -- 并集 不去重 效率高
select *
from EMP e
right outer join DEPT d
on e.deptno = d.deptno

MySQL中对集合支持比较弱，只支持并集操作，交集，差集不支持（Oracle中支持）
outer可以省略不写

11、三表查询

-- 查询员工的编号、姓名、部门编号、部门名称、薪水等级
select * from EMP
select * from DEPT
select * from SALGRADE

-- 三表查询
select * 
from EMP e
right outer join DEPT d
on e.deptno = d.deptno
inner join SALGRADE s
on e.sal between s.losal and s.hisal

12、自连接

-- 查询员工的编号、姓名、上级编号，上级的姓名
select *  from EMP

-- 自关联：自己和自己
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名 
from EMP e1
inner join EMP e2
on e1.mgr =e2.empno

92语法的多表查询

-- 查询员工编号，员工薪水，员工部门编号，部门名称
-- 相当于99语法的cross join，出现笛卡尔积，没有意义
select e.empno,e.ename,e.sal,e.deptno,d.dname
from EMP e,DEPT d

-- 相当于99语法中的natural join
select e.empno,e.ename,e.sal,e.deptno,d.dname
from EMP e,DEPT d
where e.deptno = d.deptno


-- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称，查询出工资大于2000的员工
select e.empno,e.ename,e.sal,e.deptno,d.dname
from EMP e,DEPT d
where e.deptno = d.deptno and e.sal > 2000


-- 查询员工的名字，岗位，上级编号，上级名称（自连接）
select e1.ename,e1.job,e1.mgr,e2.ename
from EMP e1,EMP e2
where e1.mgr = e2.empno


-- 查询员工的编号、姓名、部门编号、部门名称、薪水等级
-- 三表查询
select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade
from EMP e,DEPT d,SALGRADE s
where e.deptno = d.deptno and e.sal >= s.losal and e.sal <= s.hisal


-- 终结：
-- 1、92语法麻烦
-- 2、92语法中 表的连接条件和筛选条件 是放在一起的没有分开
-- 3、99语法中提供了更多的查询连接类型：cross，natural，inner，outer

单行字句查询

【1】什么是子查询？

• 一条SQL语句含有多个select 

【2】执行顺序：

• 先执行子查询，再执行外查询；

【3】不相关子查询：

• 子查询可以独立运行，称为不相关子查询。

【4】不相关子查询分类：

• 根据子查询的结果行数，可以分为单行子查询和多行子查询。

-- 引入子查询：
-- 查询所有比“CLARK”工资高的员工的信息
-- 步骤1：“CLARK”工资
select sal from EMP where ename ='CLARK'
-- 步骤2：查询所有工资比2450高的员工信息
select * from EMP where sal > 2450
-- 两次命令解决问题效率低，第二个命令依托于第一个命令的结果给第二个命令使用
-- 但是因为第一个命令的结果可能不确定要改，所有第二个命令也会导致修改

-- 将步骤1和2合并 子查询：
select * from EMP where sal > (select sal from EMP where ename = 'CLARK')
-- 一个命令解决问题 -->效率高


-- 单行字句查询：
-- 查询工资高于平均工资的雇员名字和工资

select ename,sal
from EMP 
where sal > (select avg(sal) from EMP)

-- 查询和CLARK同一部门且比他工资低的雇员名字和工资
select ename,sal
from EMP
where deptno = (select deptno from EMP where ename = 'CLARK') and sal < (select sal from EMP where ename ='CLARK')


-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
select * from EMP where 
job = (select job from EMP where ename ='SCOTT') 
and 
hiredate < (select hiredate from EMP where ename = 'SCOTT')

多行子句查询

• 多行子句和单行子句的区别在于 in，any，all 等关键字

-- 多行子句查询：
-- 【1】查询（部门20中职务同部门的雇员一样的）雇员信息
-- 查询雇员信息
select * from EMP
-- 查询部门20中的雇员信息
select * from EMP where deptno = 20 
-- 查询部门10的雇员的职务
select job from EMP where deptno =10 -- MANAGER,PRESIDENT,CLERK
-- 查询部门20中职务部门10的雇员一样的雇员信息

-- 方法1、
select * from EMP
where deptno = 20 
and job in (select job from EMP where deptno = 10)

-- 方法2、
select * from EMP
where deptno = 20 
and job =any(select job from EMP where deptno = 10)
-- any表示任意一个


-- 【2】查询工资比所有的“SALESMAN”都高的雇员编号、名字和工资。

-- 多行子句查询：
select empno,ename,sal
from EMP
where sal > all(select sal from EMP where job = 'SALESMAN')

-- 单行子句查询：
select empno,ename,sal
from EMP
where sal > (select max(sal) from EMP where job = 'SALESMAN')



-- 【3】查询工资低任意一个“CLERK”的工资的雇员信息。

-- 多行子句查询
select * from EMP
where sal < any(select sal from EMP where job = 'CLERK')
and job != 'CLERK'

-- 单行子句查询
select * from EMP
where sal < (select max(sal) from EMP where job = 'CLERK')
and job != 'CLERK'

相关子查询

【1】不相关的子查询引入：

• 不相关的子查询：子查询可以独立运行，先运行子查询，再运行外查询。
• 相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询

【2】不相关的子查询优缺点：

• 好处：简单   功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
• 缺点：稍难理解

-- 【1】查询最高工资的员工（不相关子查询）
select * from EMP where sal =(select max(sal) from EMP)
-- 不相关查询是先执行后面的查询(括号里面的查询)再执行前面的查询，两者之间是独立可运行的


-- 【2】查询部门最高工资的员工（相关子查询）

-- 方法1：通过不相关子句查询
select * from EMP where deptno =10 and sal = (select max(sal) from EMP where deptno =10)
union
select * from EMP where deptno =20 and sal = (select max(sal) from EMP where deptno =20)
union
select * from EMP where deptno =30 and sal = (select max(sal) from EMP where deptno =30)
-- 缺点：语句比较多，具体到底有多少个部分未知

-- 方法2：相关子查询
select * from EMP e  where sal = (select max(sal) from EMP where deptno = e.deptno)


-- 【3】查询工资高于其他所在岗位的平均工资的那些员工（相关子查询）
-- 不相关子查询：
select * from EMP where job = 'CLERK' and sal >= (select avg(sal) from EMP where job = 'CLERK') -- 岗位有多少未知性太大了


-- 相关子查询：
select * from EMP e where sal >= (select avg(sal) from EMP where job = e.job)