[大数据]SQL 查询语句

初始化数据库

1、DB（database）：数据库，保存一组有组织的数据的容器

2、2、DBMS：（Database Management System）数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据

3、3、SQL:（Structure Query Language）结构化查询语言，用于和DBMS通信的语言

数据库的启动

启动关闭数据库 net start mysql ; net stop mysql

我的电脑”→“管理”→“服务”在服务器的列表中找到mysql服务并右键单击，在弹出的快捷菜单中，完成MySQL服务的各种操作（启动、重新启动、停止、暂停和恢复）

进入数据库 mysql -u root -p 退出数据库，命令行exit; mysql > quit;

DDL 数据定义语言

(Data Defintion Language)语句：数据定义语句，用于定义不同的数据对象、数据库、表、列、索引等。常用的语句关键字包括create、drop、alter等。

对数据库的操作

#查询所有数据库
SHOW DATABASES;

#创建数据库  数据库名不能为全数字
CREATE DATABASE 2104javaee;

#使用数据库
USE 数据库名;

#查询该数据库中的所有的表
SHOW TABLES;

#查询其他库的所有表
show tables from 库名;

#删除xxx数据库
DROP DATABASE IF EXISTS  数据库名;

对表的操作

#创建表 
#+ 主键 + 主键自动增长 可以在字段末尾写，也可以在创建时写
#注释可以添加可以不添加  默认值可以设置也可以不设置

CREATE TABLE 表名student(
	id int(32) PRIMARY KEY auto_increment  [COMMENT ‘注释’],//注释可写可不写
	name VARCHAR(32)  NOT NULL , //设置为不为空
	sex VARCHAR(32),
	age int(3) DEFAULT 4 , //设置默认值
	salary FLOAT(8,2) DEFAULT 4 ,
	course VARCHAR(32)
    #PRIMARY KEY (id)
)ENGINE=InnoDB  [COMMENT ‘用户表’]    DEFAULT CHARSET=utf8 



# 获取创建表信息
SHOW CREATE TABLE db_student;
显示的结果
CREATE TABLE `db_student` (
  `s_id` int(3) DEFAULT NULL,
  `s_name` varchar(32) DEFAULT NULL,
  `s_sex` char(3) DEFAULT NULL,
  `s_salary` float(8,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#获取表信息 字段 字段的所有数据
DESC 表名;

# 修改表,视图名
ALTER TABLE 旧表名 RENAME TO 新表名 ;
ALTER TABLE db_student rename student;
# 修改表的注释
ALTER TABLE 表名 COMMENT '新注释'

# 删除表
DROP TABLE 表名;
TRUNCATE TABLE 表名; 清空数据   
deleate table 表名;
三者的区别：
drop:删除表所有数据与表的数据结构，也就是表直接不存在了(无法回滚)
truncate: 清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表(一旦提交不可回滚)
delete: 逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据
总结:
	速度:一般来说: drop> truncate > delete
	安全性:小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及
	使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.
想删除表,当然用 drop；想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,用delete




# 添加字段，可以指定添加位置，如果为中文不需要加 ‘ ’
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型(长度) [是否可为空，或者设置默认值]  [COMMENT '注释'] [AFTER 指定某字段] ;
ALTER TABLE student ADD COLUMN `sex` VARCHAR(5) NOT NULL DEFAULT 'ssss'  COMMENT '性别' AFTER age;

alter table white_user_new add column erp varchar(32) not null comment 'erp账号' after name ;
ALTER TABLE 表名 ADD  字段名  VARCHAR(32);

# 修改字段类型
ALTER TABLE 表名 MODIFY  字段名 新数据类型(长度) 新默认值 新注释;
alter table table1 modify column1 decimal(10,2) [DEFAULT NULL] [COMMENT '注释'];

#mysql修改字段名：
ALTER TABLE 表名 CHANGE  旧字段名 新字段名 新数据类型(长度) [DEFAULT] [COMMENT '注释']]; 
alter table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释'; 
-- 正常，此时字段名称没有改变，能修改字段类型、类型长度、默认值、注释

# 删除字段
ALTER TABLE 表名 DROP 字段名;
ALTER TABLE student DROP column1;


#修改字段顺序
放到第一位  ALTER TABLE student MODIFY age INT(3) FIRST  
--把字段1放到字段2后面
ALTER TABLE student MODIFY 字段1 字段1的属性  AFTER 字段2

复制表使用子查询创建表

#复制表结构到新表 
//后面where给一个false 就不会复制数据，只有表结果
1.  CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2;
2.CREATE TABLE sing3 LIKE sing;
注意上面两种方式，前一种方式是不会复制时的主键类型和自增方式是不会复制过去的，而后一种方式是把旧表的所有字段类型都复制到新表。
#复制表结构及数据到新表
 CREATE TABLE 新表 SELECT * FROM 旧表;
 
#一张表数据插入到另一张表 (假设两个表结构一样)
INSERT INTO 新表 SELECT * FROM 旧表

#一张表数据插入到另一张表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,…) SELECT 字段1,字段2,… FROM 旧表
 
#使用 AS subquery 选项，将创建表和插入数据结合起来  ,使用As后面的数据插入到表中
CREATE TABLE table [(column, column...)] AS subquery;

#复制现有的表：
create table emp1 as select * from employees;
create table emp2 as select * from employees where 1=2;

#创建表并插入数据，表头为 emoloyee_id,last_name,ANNSAL,hire_date
CREATE TABLE dept80
AS 
SELECT employee_id, last_name, 
salary*12 ANNSAL, 
hire_date
FROM employees
WHERE department_id = 80;
Table 

查看表的详细信息

1.查看所有表的注释

SELECT
table_name 表名,
table_comment 表的注释
FROM
information_schema.TABLES
WHERE
table_schema = '数据库名'
ORDER BY
table_name

2.查询所有表及字段的注释

SELECT
a.table_name 表名,
a.table_comment 表说明,
a.create_time 创建时间
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM
information_schema. TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
a.table_schema = '数据库名'
ORDER BY
a.table_name

3.查询某表的所有字段的注释

select  //  中间可以填* 会查询出很多信息 
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 
from information_schema.columns 
where table_schema = '数据库名'  //如果使用use 数据库名 这个命令 则不需要这句话
and table_name = '表名' ;

#或者
show full columns from 表名;

获取表索引

所有表索引

# 拼接删除索引的语法(排除主键索引)
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,' ;') 
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = '库名' AND i.INDEX_NAME <> 'PRIMARY';

单表索引

# 拼接删除索引的语法(排除主键索引)
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,' ;') 
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0919' AND TABLE_NAME='t_question_bak' AND i.INDEX_NAME <> 'PRIMARY';

DML数据操作语言

( Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新数据库记录，并检查数据的完整性。常用的语句关键字主要包括insert、delete、update和等。

注意：varchar 和date型的数据要用单引号引起来

1.插入数据

# 插入数据
字符和日期型数据应包含在单引号中。
INSERT INTO table [(column1 [, column2...])]
VALUES (value1 [, value2...]);

INSERT INTO student VALUES(1,'宋钢','男',28,8000,'JAVA');

#向表中插入空值
隐式方式
INSERT INTO departments (department_id, department_name )
VALUES (30, 'Purchasing');
显式方式
INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);

#插入指定的值，时间或，
NOW()函数：记录当前系统的日期和时间 now()。
INSERT INTO employees ( 
first_name, last_name, 
email, phone_number,
hiredate, job_id, salary, 
commission_pct, manager_id,
department_id)
VALUES ( 
'Louis', 'Popp', 
'LPOPP', '515.124.4567', 
NOW(), 'AC_ACCOUNT', 6900, 
NULL, 205, 100);

#一次插入多行数据
INSERT INTO world (country, population)
VALUES
  ('加拿大', 100),
  ('英国', 200),
  ('法国', 300),
  ('日本', 250),
  ('德国', 200),
  ('墨西哥', 50),
  ('印度', 250);


#从其他表中拷贝数据  在语句中加入子查询
? 不必书写 VALUES 子句。
? 子查询中的值列表应与 INSERT 子句中的列名对
应
例一
INSERT INTO emp2 
SELECT * 
FROM employees
WHERE department_id = 90;
例一:
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

更新数据

UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];


# 修改数据(所有的年纪都改成61岁)
UPDATE student SET 字段1 = 61，字段2=值 where 条件;
UPDATE student SET age = 61 WHERE id = 3;

# 需求：把id大于5并且年龄大于30的学生的工资改成8000
UPDATE student SET salary = 8000 WHERE id>5 AND age>30;

# 需求：把id大于5或者年龄大于30的学生的工资改成8000
UPDATE student SET salary = 8000 WHERE id>5 OR age>30;

 


 

删除 数据

# 删除数据(清空所有数据)
DELETE FROM student;

TRUNCATE TABLE student;# 清空后，再添加时id从1开始（不推荐使用，因为恢复备份时会造成id冲突，从而数据恢复失败）


# 删除数据 + 条件  如果没有条件中则表中数据丢要删除，id不会重置，会接着之前的ID

# 需求：把id为3的数据删除    
DELETE FROM student WHERE id=2;

DQL:查询语言

注意事项：

SQL 语言大小写不敏感。SQL 可以写在一行或者多行。 关键字不能被缩写也不能分行。各子句一般要分行写。 使用缩进提高语句的可读性。

列的别名:

别名紧跟列名，也可以在列名和别名之间加入关键字 ‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。 查询结果会显示成别名

SELECT last_name NAME ,phone_number phone FROM employees;
SELECT last_name AS "name",phone_number AS "phone number" FROM employees;

Where和Having

• where和having的区别
  where是一个约束声明,使用where来约束来自数据库的数据;
  where是在结果返回之前起作用的;
  where中不能使用组函数。
  having是一个过滤声明;
  在查询返回结果集以后，对查询结果进行的过滤操作;
  在having中可以使用组函数。
• .聚合函数和group by
  聚合函数就是例如SUM, COUNT, MAX, 等对一组(多条)数据操作的函数，需要配合group by 来使用。
  #如：
  SELECT SUM(population),region FROM T01_Beijing GROUP BY region; //计算北京每个分区的人数
• 3.where和having的执行顺序
  where 早于 group by 早于 having
  where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选

comparison_expr 关系式

expr 表达式

• 字符串可以是 SELECT 列表中的一个字符,数字,日期

1.基础查询

# 查询所有字段
SELECT * FROM student;

# 查询指定字段
SELECT name,age FROM student;

# 查询指定字段 + 别名
SELECT name AS '姓名',age AS '年龄' FROM student;

2.条件查询

• where 不能使用组函数，
• 明确：WHERE一定放在FROM后面

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];

查询数据 + 比较运算

条件表达式：> < >= <= = !=   <>(不等于)

SELECT name FROM student WHERE age < 28;
select * from student Where name is not null;

逻辑表达式

and（&&）；or(||)；not(!)

查询数据 + 去重

根据某个字段去重
SELECT DISTINCT 字段名 FROM student;
SELECT DISTINCT course FROM student;

# 注:如果是distinct 多列 是以多列一起判断是否重复
distinct a,b,c,d abcd全部相同才是重复的

LIke模糊查询

% 表示不确定多少个字符; _ 表示一个字符

# 需求：查询姓名带华字的数据
SELECT * FROM student WHERE name LIKE '%华%';

# 需求：查询姓名中间带华字的数据
SELECT * FROM student WHERE name LIKE '_华_';

# 需求：查询姓名开头带华字的数据
SELECT * FROM student WHERE name LIKE '华%';

查询 BETWEEN …AND…

在两个值之间：查询id在25到200之间的学生  包括边界
SELECT * FROM student WHERE id  BETWEEN 25 AND200;

查询空值

SELECT * FROM student WHERE name is NULL;

IN(set)

条件同时包含多个值

会把manger_id等于100，,101,201 的查询出来

SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);

NOT IN

不不包含表中的任意一个

ANY/SOME

会从括号中返回某一个项出来比较，如果<ANY(xx,xx) 会查询出满足小于最大的那个 ，相当于 <(MAX(xx,xx))

#查询job——id不等于括号中的某一项
SELECT last_name, job_id FROM employees  
WHERE job_id  <> ANY('IT_PROG', 'ST_CLERK', 'SA_REP');

ALL

会从括号中返回所有项出来比较

#查询job——id不等于括号中所有项
SELECT last_name, job_id FROM employees  
WHERE job_id  <> ALL('IT_PROG', 'ST_CLERK', 'SA_REP');

条件包含逻辑运算 and or not

SELECT employee_id  FROM employees  WHERE salary >= 10000  OR job_id <200

SELECT last_name, job_id FROM employees  WHERE job_id  NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

3.排序查询

# ASC：(ascend)表示升序   DESC：(descend)表示降序
ORDER BY 子句在SELECT语句的结尾。

需求：按照年龄排升序
SELECT * FROM student ORDER BY age ASC;

需求：按照工资排降序
SELECT * FROM student ORDER BY salary DESC;

#多字段排序查找
 需求：年龄做升序，年龄相同工资做降序
SELECT * FROM student ORDER BY age ASC,salary DESC;

#对字段进行加减乘除之后 设置别名 再用别名排序
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;

限制查找的数量 
SELECT * FROM student LIMIT 3;

还可以这样
SELECT * FROM student ORDER BY age ASC,salary DESC SELECT * FROM student LIMIT 3

分页

限制查找的数据条数量 
查询所有是学生表中的数据，每次只查询num条
SELECT * FROM student LIMIT 偏移量,获取条数;
SELECT * FROM student LIMIT 1，3; //从第一个数据开始 查到底三个数据 
\# 分页的公式：SELECT * FROM student LIMIT (页数-1)*num,num);

\# 需求：分页查询，每一页展示3个信息
SELECT * FROM student LIMIT 0,3; # 第1页

SELECT * FROM student LIMIT 3,3; # 第2页

SELECT * FROM student LIMIT 6,3; # 第3页

\# 需求:取这个班年龄最大的前三个人(思路：先排序再限制)

SELECT * FROM student ORDER BY age DESC LIMIT 3;

4. 组函数

分组函数作用于一组数据，并对一组数据返回一个值

SELECT [column,] group_function(column), … FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];

• 可以对数值型数据使用AVG 和 SUM 函数
• 可以对任意数据类型的数据使用 MIN 和 MAX 函数
• COUNT(*) 返回表中记录总数,适用于任意数据类型

求和

SELECT SUM(salary) FROM student;

平均值

SELECT AVG(age) FROM student;

最大值

SELECT MAX(age) FROM student;

最小值

SELECT min(age) FROM student;

个数count

（返回expr不会空的记录总数，所以字段一般选择主键即可）

SELECT COUNT(id) FROM student;

分组数据GROUP BY

可以使用GROUP BY子句将表中的数据分成若干组

分组前筛选 where 原始表 group by的前面
分组后筛选 having 分组后的结果 group by 的后面

• ? where中不能使用组函数

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];


# 在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。如下
    SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id ;
#包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
    SELECT AVG(salary)
    FROM employees
    GROUP BY department_id ;

#在GROUP BY子句中包含多个列
SELECT department_id ,job_id,SUM(salary) FROM employees GROUP BY department_id;

#GROUP BY 1
以第一列分组 ，有时候查询出 结果，第一列有多个值就需要使用到

过滤分组Having

2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

#查询员工表中 每个部门的最高薪资中 最高薪资大于1000的结果，
SELECT
  department_id,
  MAX (salary)
FROM
  employees
GROUP BY department_id
HAVING MAX (salary) > 1000;

5.时间日期函数

时间数据类型

1.DATETIME:

类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以’YYYY-MM-DD HH:MM:SS’格式显示DATETIME值，支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。（“支持”意味着尽管更早的值可能工作，但不能保证他们可以。）

2.DATE:

类型用在你仅需要日期值时，没有时间部分。MySQL检索并且以’YYYY-MM-DD’格式显示DATE值，支持的范围是’1000-01-01’到’9999-12-31’。

3.TIMESTAMP:

每张表的第一个timestamp会随着插入与修改自动更新

可以表示年月日时分秒，你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。

CREATE TABLE test(
i INT,
a TIMESTAMP,
b TIMESTAMP
)
INSERT INTO test(i) VALUES(1) #a字段会自动更新为当前时间 b不会
SELECT * FROM test

4.TIME:

数据类型表示一天中的时间。MySQL检索并且以"HH:MM:SS"格式显示TIME值。支持的范围是’00:00:00’到’23:59:59’。

时间函数

#系统当前时间   now（）获取的语句开始的时间，，SYSDATE()获取的动态的实时时间
SELECT NOW(); 
SELECT SYSDATE();

# date_add函数 给数据类型 加一个时间量
给入班时间加一天
SELECT s_num,DATE_ADD(s_beginDate,INTERVAL 1 DAY) '加一天的日期' FROM sing;

select date_add(now(), interval 1 day); -- 当前时间add 1 day
select date_add(now(), interval 1 hour); -- add 1 hour
select date_add(now(), interval 1 minute); -- ...
select date_add(now(), interval 1 second);

3.timestampdiff函数 获取两个时间之间的时间里的差  时间正向流失  所以 有正负，后面减前面
SELECT TIMESTAMPDIFF(DAY,'2001-05-05','2001-5-01') 天数差; //-4
SELECT TIMESTAMPDIFF(HOUR,'2001-05-05','2001-5-01') '小时差';

#SELECT TIMESTAMP 给左边的时间加上后边的 时间
SELECT TIMESTAMP('2008-08-08 08:00:00','01:00:00') '增加后的时间' ; 

NULL值处理

首先在mysql中，NULL表示的是“a missing unknown value”，而字符串’'是一个确定的值，这本质上就已经不一样了，NULL是指没有值，而空字符串 ” 则表示值是存在的，只不过是个空值

#1.null作为where条件是is null 或者 is not null 而不是=null
SELECT * FROM sing WHERE s_beginDate IS NULL;

#2.null值排序
用order by … asc时，null值会被放在最前面，而用order by … desc时，null时会被放在最后面，相当于null是一个无穷小的值。

#3.count（字段）计算 ，如果当前字段是null 会被忽略  而count（*） 不会忽略

#4.min、avg会忽略null值  但是这样计算出某个的avg结果是不对的 比如 有9行数据 有一会某字段为null name算均值就会算成8行 结果偏大 所以使用 ifnull函数
Ifnull(a,b) 表示如果a为null 就赋值为b
SELECT AVG(s_level) FROM sing;//2.25
SELECT AVG(IFNULL (s_level,0)) FROM sing;//1.5

#5.如果值为null 那么进行计算式 结果会为null 所以做计算时一定要加ifnull

多表查询join

使用连接在多个表中查询数据，把多个表中 有相同的数据把他对应起来

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

#表beauty 和表boys 的id相同 select的字段中如果两张表字段名字相同则必须使用表名。字段 进行区分，如果不同直接写就可以。
SELECT
  beauty.`id`,
  NAME,
  boyname
FROM
  beauty,
  boys
WHERE beauty.id = boys.id;
#使用别名可以简化代码方便区分
SELECT
  bt.id,
  NAME,
  boyName
FROM
  `beauty` bt,
  boys b
WHERE bt.`id` = b.`id`;

JOIN 使用on字句

当多个表中有关联数据 如 A.key=B.key 时 将两个表关联起来 可以使用join on

分类

• 内连接 [inner] join on 只显示匹配项
• 外连接 分左连接和右连接 （from后面的是左边，join后面的是右边）
  • 左外连接 left [outer] join on 显示 左边的所有项，只显示右边匹配项
  • 左外连接 right [outer] join on 显示 右边的所有项，只显示左边匹配项

#内连接 查询beauty表中 boyfriend_id和 boy表中id相等的数据
SELECT bt.id,NAME,boyname 
FROM beauty bt
Inner join boys b
On bt.`boyfriend_id`=b.id ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MUovw0ib-1636902205458)(D:\develops\note\我的坚果云\二阶段\图库\sql连接2.png)]

常用函数

1.字符控制函数

2、数学函数
round 四舍五入 ROUND(45.926, 2) — 45.93
rand 随机数
floor向下取整
ceil向上取整
mod取余 MOD(1600, 300) ----100
truncate截断 TRUNC(45.926, 2) ----45.92

3、日期函数

• now-----当前系统日期+时间

• curdate当前系统日期

• curtime当前系统时间

• year:返回年

• month：返回月

• day:返回日

• 还有返回时分秒 hour；minute；second

• str_to_date 将日期格式的字符转换成指定格式的日期

  • STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) -----1999-09-13

• date_format将日期转换成字符

  • DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) -------2018年06月06日

  %Y 四位的年份 ; %y 2位的年份 ; %m 月份（01,02…11,12 ）; %c 月份（1,2,…11,12 ）
  %d 日（01,02,…）; %H 小时（24小时制） ; %h 小时（12小时制）

  %i 分钟（00,01…59） ; %s 秒（00,01,…59）

流程控制函数 if then else…

CASE 表达式

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END


case :指定字段
when:当什么条件  或者常量值
then:满足上面的条件 就干什么，可以对字段内容进行更改，进行加减乘除
else:前面的条件都不满足
end:结束语



SELECT
  last_name,job_id, salary,
  CASE
    job_id  （字段或变量或表达式）
    WHEN 'IT_PROG'  THEN 1.10 * salary
    WHEN 'ST_CLERK' THEN 1.15 * salary
    WHEN 'SA_REP'   THEN 1.20 * salary
    ELSE salary
  END "REVISED_SALARY" #给遍历的字段取一个别名
FROM employees;


#还可以写成表达式形式
SELECT
  last_name,job_id,salary,
  CASE
    job_id
    WHEN job_id='IT_PROG'  THEN 1.10 * salary
    WHEN job_id='ST_CLERK' THEN 1.15 * salary
    WHEN job_id='SA_REP'   THEN 1.20 * salary
    ELSE salary
  END "REVISED_SALARY"
FROM employees;

#统计sing班级 初中高等级的个数，按如下结果显示  count使用不了，count是对行数进行技术，所有结过会一致
SELECT 
SUM(CASE s_level WHEN 1 THEN 1 ELSE 0 END) '初级',
SUM(CASE s_level WHEN 2 THEN 1 ELSE 0 END) '中级',
SUM(CASE s_level WHEN 3 THEN 1 ELSE 0 END) '高级'
FROM sing;

5、其他函数

版本 SELECT @@version;
当前库 SELECT DATABASE();
当前连接用户 SHOW PROCESSLIST;

?

5.子查询：

出现在其他语句中的select语句，称为子查询或内查询

• 子查询 (内查询) 在主查询之前一次执行完成。
• 子查询的结果被主查询(外查询)使用 。
• 外部的查询语句，称为主查询或外查询

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);

分类：
按子查询出现的位置：

• select后面：仅仅支持标量子查询
• from后面：支持表子查询
• where或having后面：★
  • 标量子查询（单行） √
  • 列子查询 （多行） √
  • 行子查询
• exists后面（相关子查询）
  表子查询

按结果集的行列数不同：
标量子查询（结果集一行一列）
列子查询（结果集一列多行）
行子查询（结果集一行多列）
表子查询（结果集一般为多行多列）

一、where或having后面

1.标量子（结果集只有一行一列）

#谁的工资比 Abel 高?
1.查询Abel的工资
SELECT salary
    FROM employees
    WHERE last_name = 'Abel'；
2.查询员工的信息，满足 salary>1结果       
SELECT last_name
FROM employees
WHERE salary >
    (SELECT salary
    FROM employees
    WHERE last_name = 'Abel');
    
#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资    
1.查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;

2.查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

3.查询员工的姓名，job_id 和工资，要求job_id=1并且salary>2
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
1.查询50号部门的最低工资
SELECT  MIN(salary)
FROM employees
WHERE department_id = 50

2.查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

3.在2基础上筛选，满足min(salary)>1
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);

2.列子查询,结果集一列多行

体会ALL 和ANY

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

1查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

2.查询员工姓名，要求部门号是1列表中的某一个 （每一个department_id 都会拿出来比较）

SELECT last_name
FROM employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

1.查询job_id为‘IT_PROG’部门任一工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

2查询员工号、姓名、job_id 以及salary，salary<(1)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
	)
AND job_id<>'IT_PROG';

#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN( salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

3.行子(一行多列)

#案例：查询员工编号最小并且工资最高的员工信息


1.查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
2.查询最高工资
SELECT MAX(salary)
FROM employees


3.查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

#简写为
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

二、selsect后面

仅仅支持标量子查询

#案例：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数 （给后面的查询结果起了个别名，不然第一行会显示成SELECT COUNT(*) FROM employees e，比较长
	WHERE e.department_id = d.`department_id`）
 FROM departments d;
 
 案例2：查询员工号=102的部门名
 SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102

三、from后面

将子查询结果充当一张表，要求必须起别名

#案例：查询每个部门的平均工资的工资等级
1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

2.  SELECT * FROM job_grades;


3.连接1的结果集(别名为ag_dep)和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、exists后面（相关子查询）

/*
语法：exists(完整的查询语句)
结果：1或0
*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000); 返回结果为0

#案例1：查询有员工的部门名
#in 也可以作出啦
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

#使用exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);


#案例2：查询没有女朋友的男神信息

#in的做法
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`
);

解决编码问题

Set charset gbk 临时改变命令行运行程序编码方式

数据类型

在MySQL里面我们将数据类型分为了以下一些类型：

1. 数值类型（整型、浮点）

2. 字符串类型

3. 日期时间类型

4. 复合类型

整型

UNSIGNED（无符号） 主要用于整型和浮点类型，使用无符号。即，没有前面面的-（负号）。
存储位数更长。tinyint整型的取值区间为，-128~127。而使用无符号后可存储0-255个长度。

创建时在整型或浮点字段语句后接上：

Unsigned

浮点类型

字符类型

时间类型

注意

1. 时间类型在web系统中用的比较少，很多时候很多人喜欢使用int来存储时间。插入时插入的是unix时间戳，因为这种方式更方便计算。在前端业务中用date类型的函数，再将unix时间戳转成人们可识别的时间。

2. 上面的类型你可以根据实际情况实际进行选择，有些人为了在数据库管理中方便查看，也有人使用datetime类型来存储时间。

约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

1.主键约束 primary key

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。

当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

-- 基本模式，创建时定义id字段为主键
create table temp( 
id int(3) primary key,
name varchar(20)
);
 
-- 组合模式，定义两个字段为主键
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);

 
-- 删除主键约束
alter table temp drop primary key;

 
-- 添加主键约,束较少使用
alter table temp add primary key(id,name);

 
-- 修改id为主键约束
alter  table temp modify id int primary key；

2、外键约束 foreign key

1.外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
• 还有一种就是级联删除子表数据。
• 注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列注意：字段和参照1、检查sc表的外键字段的类型以及大小是否和s表c表完全一致

2、试图引用的其中一个外键没有建立起索引，或者不是一个primary key , 如果其中一个不是primary key 的话，你必须为它创建一个索引。

3、一个或两个表是MyISAM引擎的表，若想要使用外键约束，必须是InnoDB引擎

-- 基本模式

-- 主表
create table temp(
id int primary key,
name varchar(20)
);
-- 副表
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);


-- 多列外键组合，必须用表级别约束语法
-- 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);

 
-- 副表
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,

/*表级别联合外键*/
foreign key(classes_name, classes_number) references classes(name, number) );
在创建表时创建 所以key后不跟表名

-- 删除外键约束
注意：-- 获取表的创建信息 SHOW CREATE TABLE student;找到系统自动生成的外键名
alter table student drop foreign key 外键名;

-- 增加外键约束
alter table表一 add foreign key(classes_name, classes_number) references 表二(name, number);

3、 唯一约束unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

唯一约束不允许出现重复的值，但是可以为多个null。

同一个表可以有多个唯一约束，多个列组合的约束。

在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。

唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

-- 创建表时设置，表示用户名、密码不能重复

  create table temp(
  id int not null ,
  name varchar(20),
  password varchar(10),
  unique(name,password)
);

-- 添加唯一约束
alter table temp add unique (name, password);

-- 修改唯一约束
alter table temp modify name varchar(25) unique;

-- 删除约束
alter table temp drop index name;

4、非空约束 not null 与 默认值 default

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null类型特征：

所有的类型的值都可以是null，包括int、float 等数据类型

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc

create table temp(
id int not null,
name varchar(255) not null default  'abc',
sex char null
)；

-- 增加非空约束
alter table temp modify sex varchar(2) not null;

-- 取消非空约束
alter table temp modify sex varchar(2) null;

-- 取消非空约束，增加默认值
alter table temp modify sex varchar(2) default 'abc' null;

视图

含义：虚拟表，抽取真实表的部分字段到虚拟表中，和普通表一样使用，mysql5.1版本出现的新特性，是通过表动态生成的数据，一般会定义子账户权限，只读 。只保存了sql逻辑，不保存查询结果

视图的好吃

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

创建视图：

create [or replace] view 视图名 AS select 字段名 1，字段名2,from 表名 [where 条件] [with check option]

? with check option – 当修改值超过条件时，不允许修改

? or replace – 如果已有视图，则替换

EXP：create or replace view view1 as select name,age from student [where age>20 with check option];

查询视图数据：select * from 视图名

插入视图数据insert into 视图名（字段1，字段2）values（字段1的值，字段2的值）；

修改视图数据：update 视图名 set 字段=”值”；

删除视图：drop view 视图名

存储过程

#案例1：最简单的的存储过程
[ delimiter XX ]
create procedure myprocedure01()
begin
      select * from student;
      select * from class;
endxx
[ delimiter ; ]
call myprocedure01(); --- 调用存储过程

参数列表
输入类型的参数 -- in
输出类型的参数 -- out
输入输出类型的参数 -- inout

#案例2：输入一个学生的id，查询该学生的信息
[ delimiter XX ]
create procedure myprocedure02(in s_id int(100))
begin
      select * from student where id = s_id;
end      
[ delimiter ; ]
call myprocedure02(); --- 调用存储过程

#案例3：输入一个学生的id，返回该id对应的学生姓名
[ delimiter XX ]
create procedure myprocedure03(in s_id int(100), out rusername varchar(100) )
begin
      select username into rusername from student where id = s_id;
end      
[ delimiter ; ]
call myprocedure03(1,@a); --- 调用存储过程
select @a;//@a为局部变量

#案例4：输入一个学生的id，返回该id对应的学生年龄
[ delimiter XX ]
create procedure myprocedure04(in param int(100) )
begin
      select age into param from student where id= param ;
end      
[ delimiter ; ]

set @a = 1;//定义局部变量
call myprocedure04(@a); --- 调用存储过程
select @a;


#案例5：定义存储过程中的变量(获取学生表中学生的人数)
[ delimiter XX ]
create procedure myprocedure05(out s_out int(100) )
begin
    //定义变量
    //declare 变量名 类型[default 默认值];
    declare a int default 0;
    //给变量赋值
    //set a = 1000;
    select count(1) into a from student;
    set s_out = a;
end      
[ delimiter ; ]

call myprocedure05(@a); --- 调用存储过程
select @a;

如果存在就删除存储过程：drop procedure if exists procedure_name;

函数

#数学函数：
绝对值函数：select abs(字段) from 表  ps:此函数指的是求绝对值
平方根函数：select sqrt(9)  ps:得到的值为3
求余函数：select mod(10,3) / select 10%3  ps:得到的值为1
随机函数：select rand()   ps:得到的值为0~1
幂运算函数：select pow(10,2)  ps:10的2次方

#字符串函数：
      长度函数：select length(‘内容’)
      合并函数：select concat(’hello’,’world’)
      截取字符串函数：select substring(字段,start,end) 
#日期/时间函数：
       当前日期函数：curdate()
       当前时间函数：curtime()
       当前日期+时间函数：now()
       年函数：year(now())
       月函数：month(now())
       日函数：dayofmonth(now())
       时函数：hour(now())
       分函数：minute(now())
       秒函数：second(now())
       时间格式函数：date_format(now(),’%Y年%m月%d日 %H时%i分%s秒’) as ‘别名’

#自定义函数
      语法：
         create function 函数名([参数列表]) returns 数据类型
			begin
            sql语句;
             return 值;
			end

# 最简单的仅有一条sql的函数
create function method01() returns int return 666;	
SELECT method01();

# 带返回值的函数
delimiter xx
create function method02() returns int
	begin 
			declare c int;
			select age into c from student where id=1 ;
			return c;
	end xx
delimiter ;
select method02();

# 带返回值和参数的函数
delimiter xx
create function method03(s_name VARCHAR(32)) returns int
	begin 
			declare c int;
			select age into c from student where name=s_name ;
			return c;
	end xx
delimiter ;
select method03('bbb');

# 删除函数：
drop function method03;

fault 默认值];
declare a int default 0;
//给变量赋值
//set a = 1000;
select count(1) into a from student;
set s_out = a;
end
[ delimiter ; ]

call myprocedure05(@a); — 调用存储过程
select @a;

如果存在就删除存储过程：drop procedure if exists procedure_name;

# 函数

```sql
#数学函数：
绝对值函数：select abs(字段) from 表  ps:此函数指的是求绝对值
平方根函数：select sqrt(9)  ps:得到的值为3
求余函数：select mod(10,3) / select 10%3  ps:得到的值为1
随机函数：select rand()   ps:得到的值为0~1
幂运算函数：select pow(10,2)  ps:10的2次方

#字符串函数：
      长度函数：select length(‘内容’)
      合并函数：select concat(’hello’,’world’)
      截取字符串函数：select substring(字段,start,end) 
#日期/时间函数：
       当前日期函数：curdate()
       当前时间函数：curtime()
       当前日期+时间函数：now()
       年函数：year(now())
       月函数：month(now())
       日函数：dayofmonth(now())
       时函数：hour(now())
       分函数：minute(now())
       秒函数：second(now())
       时间格式函数：date_format(now(),’%Y年%m月%d日 %H时%i分%s秒’) as ‘别名’

#自定义函数
      语法：
         create function 函数名([参数列表]) returns 数据类型
			begin
            sql语句;
             return 值;
			end

# 最简单的仅有一条sql的函数
create function method01() returns int return 666;	
SELECT method01();

# 带返回值的函数
delimiter xx
create function method02() returns int
	begin 
			declare c int;
			select age into c from student where id=1 ;
			return c;
	end xx
delimiter ;
select method02();

# 带返回值和参数的函数
delimiter xx
create function method03(s_name VARCHAR(32)) returns int
	begin 
			declare c int;
			select age into c from student where name=s_name ;
			return c;
	end xx
delimiter ;
select method03('bbb');

# 删除函数：
drop function method03;