标签：-- employees MySQL 表名 department id SELECT

SQL语句三大主线

DDL:(Data Definition Languages,数据定义语言),主要用来创建,删除,修改库和表的结构.

DML:(Data Manipulation Language,数据操作语言),主要用于添加,删除,更新,查询数据库的记录.

DCL:(Data Control Language,数据控制语言),主要用于定义数据库,表,字段,用户的访问权限和安全级别.

SELECT是SQL语言的基础,最重要.
所以很多时候会把查询单独称为DQL(数据查询语言).

1.运算符

1.1比较运算符

用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真返回1,结果为假返回0,其他情况返回NULL

• 符号类的

!=或<>	不等于
=		等于
<=>		 安全等于
-- 字符串存在隐式转换,如果转换数值不成功,则看做0
-- 如果两边都是字符串,则按照ANSI的比较规则进行比较

-- 安全等于和等于的区别
安全等于   两边都为NULL时,返回值为1,只有一边为NULL时,返回值为0
等于		其中一边或两边都为NULL时,返回值为NULL

>		大于 
>=		大于等于 
<		小于 
<=		小于等于 

• 关键字类的

为空运算符		 IS NULL/ISNULL
不为空运算符		IS NOT NULL
最小值运算符		LEAST
最大值运算符		GREATEST
在某个区间之内	    BETWEEN...AND... 	-- 包含两个点
属于运算符		 IN((....)) 		  -- 多选1
不属于运算符		NOT IN((...))	     -- 多选1
模糊匹配运算符		LIKE 占位符		 -- _匹配单个字符,%匹配多个字符

1.2逻辑运算符

逻辑与		AND或&&	-- 两边都为真才是真
逻辑或		OR或||	-- 两边有一个为真就为真
逻辑非		NOT或!	-- 取反
逻辑异或    XOR		-- 满足左边但不满足右边,满足右边但不满足左边

1.3位运算符

&	按位与
|	按位或
^	按位异或
~	按位取反
>>	按位右移
<<	按位左移

1.4运算符优先级

越往下优先级越高

2.DQL

2.1基本查询

-- 查询多个字段
SELECT 字段1,字段2,字段3.... FROM 表名;
-- 例
SELECT name,age FROM student;

-- 查询全部字段
SELECT * FROM 表名;
SELECT * FROM employees;

-- 设置别名
SELECT 字段1 [as别名1],..... FROM 表名;
-- 例
SELECT age AS "newAge" FROM student;
-- AS可以省略,双引号可以不写(别名出现空格时要写)

-- 去重
SELECT DISTINCT 字段列表 FROM 表名;
-- 例
select distinct age from employees;

空值

• null不等于0,'',null'
• 空值参与运算,结果一定为null

着重号

当表名(字段名)和SQL定义的关键字重复了,就要使用着重号引起来

2.2条件查询

-- where关键字
SELECT 字段列表 FROM 表名 WHERE 条件列表;
-- 例,查询学生表年龄等于18的学生
select name,age from student where age=18;

2.3排序查询

-- ORDER BY关键字
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
-- 排序方式
asc		升序(默认)
desc	降序
-- 例
select employee_id,last_name,salary from employees order by salary desc;

二级排序
select employee_id,last_name,salary from employees order by salary desc,
employee_id;
-- 当salary相等时,按照employee_id升序排序

2.4分页查询

-- LIMIT关键字
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
-- 例
select * from employees limit 0,20;

• 起始索引
  • 从第几条数据开始(0开始)
• 查询记录数
  • 每页展示多少条数据

公式:(页-1)*数量,数量;
-- 例,第二页的信息,每页15条
(2-1)*15,15
结果就是15,15

-- offset关键字
select * from employees limit 2,20;
两条语句相等
select * from employees limit 20 offset 2;

2.5多表查询

等值连接vs非等值连接

-- 等值连接示例
-- 带等号的连接都称为等值连接
select employee_id,department_name 
from employees,departments 
where employees.department_id=departments.department_id;

-- 非等值连接示例
select last_name,salary,grade_level
from employees,job_grades
where employees.salary
between job_grades.lowest_sal and job_grades.highest_sal;

自连接vs非自连接

-- 自连接
-- 当前表与自身的连接查询,自连接必须使用表别名
SELECT 字段列表 FORM 表A 别名A JOIN 表A 别名B ON 条件;
-- 自连接示例,查询员工id,员工姓名极其管理者的id和姓名
select e1.employee_id,e1.last_name,e2.employee_id,e2.last_name
from employees e1,employees e2
where e1.manager_id=e2.employee_id;

-- 自连接以外的连接都成为非自连接

内连接vs外连接

• 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行.
  • 查询两个表的交集数据(排除null的值)
    • 隐式内连接SELECT 字段列表 FORM 表1,表2.... WHERE 条件;
• 外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(右)表中不满足条件的行,这种连接称为左(右)外连接,没有匹配的行时,结果表中相应的列为空(NULL).
  • 左外连接:连接条件中的左边的表称为主表,右边的表称为从表.
    • 查询A表所有数据和B表交集部分数据
  • 右外连接:连接条件中的右边的表称为主表,左的表称为从表.
    • 查询B表所有数据和A表交集部分数据
  • 满(全)外连接:连接条件中的左边的表称为主表,右边的表称为从表.
    • 查询A表和B表的所有数据
• 联合查询
  • 把多次查询的结果合并起来,形成一个新的结果集
  • 可以解决MySQL不支持全外连接的查询

-- 隐式内连接(92语法)
SELECT 字段列表 FORM 表1,表2.... WHERE 条件; 
-- 例
select employee_id,department_name
from employees,departments
where employees.department_id=departments.department_id
-- 显示内连接(99语法)
SELECT 字段列表 FORM 表1 [INNER] JOIN 表2 ON 条件; -- INNER可以省略
-- 例
select last_name,department_name
from employees join departments
on employees.department_id = departments.department_id
-- 两个以上的表时在后面继续join 表名 on 条件即可

-- 左外连接
SELECT 字段列表 FORM 表1 LEFT [OUTER] JOIN 表2 ON 条件;-- OUTER可以省略
-- 例
select last_name,department_name
from employees left join departments
on employees.department_id = departments.department_id
-- 右外连接
SELECT 字段列表 FORM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;-- OUTER可以省略
-- 例
select last_name,department_name
from employees right join departments
on employees.department_id = departments.department_id
-- 满(全)外连接
SELECT 字段列表 FORM 表1 FULL [OUTER] JOIN 表2 ON 条件;-- OUTER可以省略
-- 例
select last_name,department_name
from employees full join departments
on employees.department_id = departments.department_id

左(右)外连接只在显示内连接的情况上添加一个left或者right即可
满(全)外连接在MySQL数据库的语法中不支持full这个关键字,但MySQL有一个联合查询

-- 联合查询
SELECT 字段列表 FORM 表A.... 
UNION [ALL] 
SELECT 字段列表 FORM 表B...;
-- UNION不加ALL(效率较低)
去重
-- UNION加ALL(效率较高,推荐使用)
全部信息的结果集,包括重复的

-- 例
select employee_id,department_name
from employees left join departments
on employees.department_id=departments.department_id
union all
select employee_id,department_name
from employees right join departments
on employees.department_id=departments.department_id
where employees.department_id is null ;

2.6分组查询

• 主要用来和聚合函数联合使用

• select出现的非聚合函数的字段在group by中也一定要出现

• SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组过滤条件];
  -- GROUP BY例,查询各个部门的工种的平均工资
  select department_id,job_id,avg(salary) 
  from employees 
  group by department_id,job_id;
  -- 结果如下
  

• -- HAVING例,查询各个部门的最高工资,并且只展示最高比10000高的
  select department_id,max(salary)
  from employees group by department_id
  having max(salary)>10000;
  

2.7子查询

• 单行子查询

  • 作为条件值使用= != < > <= >=等条件判断

  • SELECT 字段列表 FORM 表 WHERE 字段名 = (子查询);
    

• 多行子查询

  • 作为条件值使用IN,ANY,SOME,ALL 等关键字条件判断

  • SELECT 字段列表 FORM 表 WHERE 字段名 in (子查询);
    

• 表子查询

  • 作为虚拟表(常用操作符IN)

  • SELECT 字段列表 FORM (子查询) WHERE 条件;
    

2.8执行顺序

FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY>LIMIT

3.函数

把经常使用的代码封装起来,需要的时候再调用,这样即提高了代码效率,又提高了可维护性.在SQL中也可以使用函数对检索出来的数据进行操作,使用这些函数,可以极大地提高用户对数据库的管理效率.

3.1单行函数

• 单行函数可以嵌套使用

数值函数

基本函数

-- 简单举例几个
select ABS(-123),PI(),RAND() from dual;

三角函数

指数对数

进制转换

字符串函数

字符串中,索引是从1开始的

日期函数

获取日期和时间

时间戳的转换

获取月份,星期,天数等

日期的操作函数

EXTRACT(type FROM date)
-- 返回指定日期中的特定部分,type指定返回的值

时间和秒钟转换的函数

计算日期和时间的函数

• type的取值
  

日期的格式化与解析

• fmt参数常用的格式符
  

• date_type和format_type参数取值
  

流程控制函数

流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择.MySQL中的流程处理函数主要包括IF(),IFNULL()和case()函数.

• -- IF()函数
  IF(value,t,f); -- 如果value为true,返回t,否则返回f
  -- 例
  SELECT IF(1>0,'对','错); -- 结果为对
  

• -- IFNULL()函数
  -- IFNULL(value1,value2); --如果value1不为空,返回value1,否则返回value2
  -- 例
  SELECT IFNULL(null,'Hello World'); -- 结果为Hello World
  

• -- case()函数1
  CASE WHEN [val1] THEN [res1] ... ELSE [default] END;
  -- 如果val1为true,返回res1,...否则返回default默认值,类似于if...else if...else
  -- 例
  SELECT CASE 
  WHEN age>18 THEN '成年' 		-- 如果age大于18就为成年
  WHEN age<18 THEN '未成年' 	    -- 不大于18就为未成年
  ELSE  '错误年龄' END; 		  -- 其他情况为错误年龄
  -- ELSE可以不写,不写默认值为NULL
  

• -- case()函数2
  CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END;
  -- 如果expr的值等于val1,返回res1,...否则返回default默认值,类似于switch语句
  -- 例
  SELECT age=12 CASE 
  WHEN age>18 THEN '成年' 		
  WHEN age<18 THEN '未成年' 	   
  ELSE  '错误年龄' END;	-- 结果为未成年
  -- ELSE可以不写,不写默认值为NULL
  

3.2聚合函数

对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值

• SELECT 聚合函数(字段列表) FROM 表名;
  -- NULL值不参加运算
  

• -- 常见的聚合函数
  统计		count
  最大值		max
  最小值		min
  平均值		avg
  求和	     sum
  -- 例 平均值和总值
  select avg(salary),sum(salary) from employees; -- 只使用于数值类型
  -- 例 最大值最小值
  select max(salary),min(salary) from employees; -- 可以适用于字符串
  

• -- count的作用:计算指定字段在查询结构中出现的个数
  select count(salary)) from employees;
  

4.DDL

4.1数据库操作

• -- 查询所有数据库
  SHOW DATABASES;
  -- 查询当前数据库
  SELECT DATABASE();
  -- 创建数据库
  CREATE DATABASE[IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
  -- 删除数据库
  DROP DATABASE [IF EXISTS] 数据库名;
  -- 使用数据库
  USE 数据库名;
  

• -- 例:创建数据库
  create database if not exists students;
  -- 例:使用数据库
  use students;
  -- 例:删除数据库
  drop database if exists students;
  

4.2数据表操作

增

• -- 创建表
  CREATE TABLE [IF NOT EXISTS] 表名(
      字段1 字段1类型[COMMEN字段1注释],
      2,3,4....
  )[COMMENT 表注释];
   -- 最后一个字段没有逗号
   
   -- 添加字段
   ALTER TABLE 表名 ADD 字段名 字段类型[COMMENT 注释] [约束];
  

• -- 例:创建表
  create table if not exists student(
      id int,
      name varchar(15),
      age int
  );
  -- 例:添加字段
  alter table student add gender varchar(1);
  

删

• -- 删除字段
  alter TABLE 表名 DROP 字段名;
  -- 删除表
  DROP TABLE[IF EXISTS] 表名;
  -- 删除指定表并且重新创建该表(清除数据)
  TRUNCATE TABLE 表名;
  

• -- 例:删除字段
  alter table students drop employee_id;
  -- 例:删除表
  drop table student;
  -- 例:清除数据
  truncate table student;
  

改

• -- 修改数据类型
  alter TABLE 表名 MODIFY 字段名 新数据类型;
  -- 将字段移动
  alter table 表名 modify 要移动的字段 after 移动在哪;
  -- 修改字段名和字段类型
  ATLER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型 [COMMENT 注释] [约束];
  -- 修改表名
  ALTER TABLE 表名 RENAME TO 新表名;
  

• -- 例:修改数据类型
  alter table students modify first_name varchar(19);
  -- 例:修改字段名和字段类型
  alter table students change first_name one_name varchar(20);
  -- 例:修改表名
  alter table students rename to student;
  

查

• -- 查询当前数据库所有表
  SHOW TABLES;
  -- 查询表结构
  DESC 表名;
  -- 查询指定表的建表语句
  SHOW CREATE TABLE 表名;
  

• -- 例:查看表结构
  desc student;
  -- 例:查看指定表的建表语句
  show create table student;
  

复制

• -- 复制表数据,例
  create table studnet 
  [AS] 
  select id,name,age from employee;
  -- 此时student表就会有id,name,age的数据(数据类型和约束等于employee表一致)
  -- 相当于把查询的结果复制到新的表当中
  

• -- 复制表结构(不包括数据,只要结构),例
  create table studnet 
  [AS]
  select id,name,age from employee where 这里给一个不可能的条件,例如1=0;
  

5.DML

增

• 单个添加

• -- 给指定字段添加数据
  INSERT INTO 表名(字段名1,字段名2....) VALUES(值1,值2....);
  -- 例
  insert into detp01(name) values ('李四');
  
  -- 给全部指定添加数据
  INSERT INTO 表名 VALUES(值1,值2......);
  -- 例
  insert into detp01 values (1,'张三');
  

• 批量添加

• -- 给指定字段添加数据
  INSERT INTO 表名(字段名1,字段名2....) VALUES(值1,值2....),(值1,值2....),(值1,值2....);
  -- 例
  insert into detp01(id,name) values (2,'李四'),(3,'王五');
  
  -- 给全部指定添加数据
  INSERT INTO 表名 VALUES(值1,值2......),(值1,值2....),(值1,值2....);
  -- 例
  insert into detp01 values (2,'李四'),(3,'王五');
  

• -- 将查询的结果插入到表中
  insert into 表名(字段名1,字段名2,字段名3..) 查询语句
  -- 字段名要一一对应
  

删

• DELETE FROM 表名 [WHERE 条件];
  -- 例
  delete from detp01 where id=4;
  

改

• UPDATE 表名 
  SET 字段名1=值1,字段名2=值2,...[WHERE 条件];
  -- 例
  update detp01 set id=4 where id is null;
  

6.TCL

COMMIT:提交数据,一旦执行commit,则数据就被永久的保存在数据库中了,意味着无法回滚数据.

ROLLBACK:回滚数据,一旦执行rollback,则可以实现数据回滚,回滚到最近的一次commit之后.

-- 使用
直接COMMIT;
或者ROLLBACK;即可

• delete from和truncate table的使用选择
  • 虽然truncate table效率高,但是有可能造成事故,所以建议用delete
  • 如果truncate table不加where子句时,效率与delete相同

DDL和DML的说明
1.DDL的操作一旦执行,就不可回滚,set autocommit=false对DDL无效(DDL自带COMMIT)
2.DML的操作默认情况下,一旦执行,也不可回滚
	2.1在执行DML之前执行了set autocommit=false,则执行的DML操作可以实现回滚

7.数据类型

7.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	-9223372036854775808~9223372036854775807	0~18446744073709551615

浮点数

类型	字节	有符号数取值范围	无符号数取值范围
FLOAT	4	超级大	超级大
DOUBLE	8	超级大	超级大

• 浮点性用有符号类型,有误跟无符号类型的占用空间一样大
• 浮点数是不准确的,要避免使用=号来判断两个数是否相等.
• 同时,在对精度要求较高的项目中,不用使用浮点数,要使用定点数↓↓↓

定点数

类型	字节	含义
DECIMAL(M,D),DEC,NUMERIC	M+2字节	有效范围由M和D决定

• 使用DECIMAL(M,D)的方式表示高精度小数,其中,M被称为精度,D被称为标度.

  • M的有效范围:0<=M<=65

  • D的有效范围:0<=D<=30

  • -- 例
    DECIMAL(5,2)
    表示该列取值范围是-999.99~999.99
    

• decimal的最大取值范围和double一样,但是有效的数据范围由MD决定的.

7.2日期类型

类型	字节	日期格式	最小值	最大值	作用
YEAR	1	YYYY或YY	1901	2155	年
TIME	3	HH:MM:SS	-838:59:59	838:59:59	年月日
DATE	3	YYYY-MM-DD	1000-01-01	9999-12-03	时分秒
DATETIME	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	年月日时分秒
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTF	带时区的年月日时分秒

-- 总结:规范写即可,不要花里胡哨的

7.3字符串类型

文本字符串

文本字符串类型	字节	值的长度	长度范围
char(M)	M个字节	M	0<=M<=255
Varchar(M)	M+1个字节	M	0<=M<=65535
TinyText	L	L+2个字节	0<=L<=255
Text	L	L+2个字节	0<=L<=65535
MediumText	L	L+3个字节	0<=L<=16777215
LongText	L	L+4个字节	0<=L<=4294967295
ENUM	L	1或2个字节	1<=L<=65535
SET	L	1,2,3,4或8个字节	0<=L<=64

• char
  • char类型一般需要预先定义字符串长度.如不知道,则默认为1个字符
  • 如果保存时,数据的实际长度比char类型声明的长度小,则会在右侧填充空格以达到指定的长度,当MySQL检索char类型的数据时,char类型的字段会去除尾部的空格
  • 定义char类型字段时,声明的字段长度即为char类型字段所占的存储空间的字节数
• varchar
  • varchar类型定义时,必须指定长度,否则报错
  • 检索varchar类型的字段数据时,会保留数据尾部的数据,varchar类型的字段所占用的存储空间为字符串实际长度+1个字节

类型	特点	空间上	时间上	使用场景
char	固定长度	浪费存储空间	效率高	存储不大,速度要求高
varchar	可变长度	节省存储空间	效率低	非char的情况

• enum

  • -- 例
    create table student(
        season enum('春','夏','秋','冬')
    );
    -- 插入数据
    insert into student values ('春');
    insert into student values (1);
    

  • 可以添加null值(没有限制非空的情况下)

  • 也可以使用索引进行调用(从1开始)

  • 一次只能添加一个值(忽略大小写)

• set

  • 跟enum的区别就是set可以一次添加多个值

二进制字符串

主要用于存储一些二进制数据,如:图片,音频,视频等.

• binary与varbinary
  • 对应的是char和varchar类型,用的不多
• BLOB类型(用的不多)

二进制字符串类型	字节	值的长度	长度范围
TinyBlob	L+1个字节	L	0<=L<=255
Blob	L+2个字节	L	0<=L<=65535(相当于64kb)
MediumBLOB	L+3个字节	L	0<=L<=16777215(相当于16MB)
LongBlob	L+4个字节	L	0<=L<=4294967295(相当于4GB)

7.4JSON类型

json是一种轻量级的数据交换格式.简洁和清晰的层次结构使得json称为理想的数据交换语言.它易于人阅读和编写,同时也易于机器解析和生成.并有效地提升网络传输效率.json可以将JavaScript对象中表示一组数据转换为字符串,然后就可以在网络或者程序直接轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式.

• -- 创建
  create table test(
      js json
  );
  -- 插入数据
  insert into test(js) values ('{json对象值: 属性值,......}');
  

7.5使用建议

整数	INT
小数	DECIMAL(M,D)
日期	DATETIME
非负数的字段,必须使用unsigned

标签：--,employees,MySQL,表名,department,id,SELECT
From： https://www.cnblogs.com/Myvlog/p/16845082.html