[大数据]数据库语句大全

数据库语句大全

1.sql分类

1. DDL（Data Definition Language）：数据定义语?，?来定义数据库对象：库、表、列等；
2. DML（Data Manipulation Language）：数据操作语?，?来定义数据库记录（数据）；
3. DCL（Data Control Language）：数据控制语?，?来定义访问权限和安全级别；
4. DQL(重要)（Data Query Language）：数据查询语?，?来查询记录（数据）。 注意：sql语句以;结尾

2.DDL(操作库，表，列)

2.1.数据库

创建数据库
 1.create database +库名;
 2.create database if not exists +库名;
 3.create database +库名 character  set gbk;     //创建字符集型的数据库
 4.create database + 库名 character set gbk_chinese_ci;
删除数据库
 drop database +库名;
修改数据库
 alter database +库名 character set utf8;   //查看服务器中的数据库，并把数据库的字符集修改为utf8
查询数据库
 1.show databases; 
 2.show create database +库名  //查看数据库的定义信息
其他
 1.查看当前使用的数据库
	select databases();
 2.切换数据据库
	use +库名;

2.2.数据表

创建表
 1.create table (if not exists)+表名（
	字符段	int：整型 
		  double：浮点型，例如double(5,2)表示最多5位，其中必须有2位?数，即最?值为999.99； 
		  char：固定?度字符串类型； 
		  char(10): 'aaa ' 占10位 
		  varchar：可变?度字符串类型；
		  varchar(10): 'aaa' 占3为 
		  text：字符串类型； 
		  blob：字节类型； 
		  date：?期类型，格式为：yyyy-MM-dd； 
		  time：时间类型，格式为：hh:mm:ss 
		  timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会?动赋值 
		  datetime:?期时间类型 yyyy-MM-dd hh:mm:ss
		  boolean:mysql不?持,oracle?持
   ）
增加
	1.alter table +表名 add 字段(name) 字段类型(varchar(10))  //在表中在增加一列
删除
	1.alter table  +表名 drop +字段(name);   //删除表中字段(name)，只能一行一行删 
	2.drop table +表名
修改
	1.alter table +表名 modify 字段(name) 字段类型(varchar(10))  //修改name列的长度，使他长度为10
	2.rename table +表名 to +新表名      //修改表名
    3.alter table +表名 character set gbk   //修改表的字符集为gbk;
    4.alter table +表名 change +列名 新列名 字段类型    //修改列名
查询
	1.show tables; 
	2.desc +表名;
	3.show create table user;    //修改表格的创建细节

其他
	create table 表名 like 新表名   //备份表

3.DML

插入数据
-- 结构:insert into 表名(字段列表) values(字段值)
-- 字段列表--形参
-- 字段值---实参
-- 注意:字符串和日期都要使用''括起来
insert into emp(name,id,height,birthday,sex) values('zhangsan',2,125.63,'1989-10-12','女');
-- 如果所有的属性都要添加可以省略（）；
insert into emp values('zhangsan',2,125.63,'1989-10-12','女');
修改数据
update emp set salary = 500;
update emp set salary = 500 where name='zhangsan';    //将zhangsan的薪水加500
update emp set salary = 500,gender = 'female' where name = 'lisa';  //将lisa的女性工资加500
update emp set salary = salary+500  where gender = 'male'   //将男性的薪水都加500
删除数据
删除表中名称为’zs’的记录。
delete from emp where name=‘zs’;
删除表中所有记录。
delete from emp;
使?truncate删除表中记录。
truncate table emp;     //创建一个于emp一样的新表，永久删除

4.DQL

查询指定列
select 指定列名 +表名
单字段查询
SELECT first_name FROM t_employees;
多字段查询
SELECT first_name,salary FROM t_employees;
查询所有字段
SELECT * FROM t_employees;
取别名 //as 
SELECT employee_id AS '员工编号',first_name AS '姓',salary * 12 AS '年薪' FROM	t_employees;
去重  distinct
SELECT DISTINCT MANAGER_ID FROM t_employees;

#排序查询：asc(升序) desc(降序)
查询员工的编号，名字，薪资。按照工资高低进行降序排序。 
SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary DESC;

查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）。 
SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary ASC,EMPLOYEE_ID ASC;

#条件查询 where
查询薪资是11000的员工信息（编号、名字、薪资）
SELECT employee_id,first_name,salary FROM t_employees WHERE salary = 11000;

#逻辑判断 and  or  not
查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资） 
SELECT employee_id,first_name,salary FROM t_employees WHERE salary = 11000 AND COMMISSION_PCT = 0.3;

#不等值判断 （> 、< 、>= 、<= 、!= 、<>）
查询员工的薪资在6000 ~ 10000之间的员工信息（编号，名字，薪资） 
SELECT employee_id,first_name,salary FROM t_employees WHERE salary >= 6000 AND salary <=10000;

#区间判断 between and 
查询员工的薪资在6000 ~ 10000之间的员工信息（编号，名字，薪资） #闭区间，包含区间边界的两个值
SELECT
employee_id,
first_name,
salary
FROM
t_employees
WHERE
salary BETWEEN 6000
AND 10000;

# null值判断 is NULL  is not NULL
查询没有提成的员工信息（编号，名字，薪资, 提成） 
SELECT
employee_id,
first_name,
salary
FROM
t_employees
WHERE
COMMISSION_PCT IS NULL;

#  枚举查询（where in (值1，值2，值3)）
查询部门编号为70、80、90的员工信息（编号，名字，薪资,部门编号） 
SELECT
employee_id,
first_name,
salary,
DEPARTMENT_ID
FROM
t_employees
WHERE
DEPARTMENT_ID IN (70, 80, 90);

# 模糊查询 
# LIKE _ （单个任意字符）列名  LIKE '张_'
# LIKE %（任意长度的任意字符）列名 LIKE '张%'
查询名字以 "L" 开头的员工信息（编号，名字，薪资, 部门编号） 
SELECT
employee_id,
first_name,
salary
FROM
t_employees
WHERE
	first_name LIKE 'L%' 
查询名字以 "L" 开头并且长度为4的员工信息（编号，名字，薪资,部门编号） 
SELECT
employee_id,
first_name,
salary
FROM
t_employees
WHERE
first_name LIKE 'L___' 

# 分支结构查询
# case when 条件1 then 结果1
#      when 条件2 then 结果2
# end
查询员工信息（编号，名字，薪资,
薪资级别 < 对应条件表达式生成 > ） 
SELECT
employee_id,
first_name,
salary,
(CASE
 WHEN salary >= 10000 THEN 'A'
 WHEN salary >= 8000 AND salary < 10000 THEN 'B'
 WHEN salary >= 6000 AND salary < 8000 THEN 'C'
 WHEN salary >= 4000 AND salary < 6000 THEN 'D'
 ELSE 'E'
 END
) AS '薪资级别'
FROM
t_employees;

# 时间查询
SELECT sysdate();

查询系统时间 
SELECT now();

SELECT curdate();

查询日期  
SELECT curtime();

查询时间 
SELECT YEAR (now());

查询年份 
SELECT DATEDIFF('2021-08-09', CURDATE());

时间数之差 (前面的减后面的) 
SELECT ADDDATE(CURDATE(), 10);

在指定的时间上，加天数 #字符串查询
SELECT CONCAT(CURDATE(), '-', CURTIME());

字符串拼接
SELECT INSERT ('helloworld', 2, 1, "xx");

将原字符串指定位置添加子串 
SELECT upper('mysql');

转换为大写 
SELECT lower('MYSQL');

转换为小写 
SELECT substring('hellomysql', 5, 6);指定内容截取 

#聚合函数(sum() avg() max() min() count())
统计所有员工每月的工资总和 
SELECT
sum(salary)
FROM
t_employees;

求总条数 -- count（字段）：不包括null值的条数
SELECT
COUNT(commission_pct)
FROM
t_employees;

SELECT
count(*)
FROM
t_employees;

#分组查询  group by
查询各部门的总人数 
思路： 
1.按照部门编号进行分组（分组依据是 department_id） 
2.再针对各部门的人数进行统计（count） 
SELECT
department_id,
count(*)
FROM
t_employees
GROUP BY
department_id;

查询各个部门、各个岗位的人数 
SELECT
department_id,
job_id,
count(*)
FROM
t_employees
GROUP BY
department_id,
job_id;

查询各个部门id、总人数、first_name --分组查询匹配的字段必须是聚合函数或分组依据列 
SELECT
department_id ,
count(*),
first_name
FROM
t_employees
GROUP BY
department_id,
count(*),
first_name;'错误查询' 

查询各部门的平均工资 
SELECT
department_id,
AVG(salary)
FROM
t_employees
GROUP BY
department_id;

# 分组过滤查询  HAVING
统计60、70、90号部门的最高工资 
SELECT
department_id,
max(salary)
FROM
t_employees
GROUP BY
department_id
HAVING
department_id IN (60, 70, 90);

#限定查询   LIMIT
查询表中前五名员工的所有信息 
SELECT *
FROM
t_employees
LIMIT 0,5;

查询表中从第四条开始，查询 10 行 
SELECT *
FROM
t_employees
LIMIT 3, 10;


#关联查询
#子查询（作为条件判断）
查询工资大于Bruce 的员工信息
select *
from t_employees
where 
salary >(select salary
from t_employees
where first_name='Bruce');

#子查询（作为枚举查询条件）
查询与名为'King'同一部门的员工信息
select * 
from t_employees
where department_id in
(select department_id 
from t_employees 
where last_name='King');
工资高于60部门所有人的信息  'all()'(高于所有)
select *
from t_employees
where salary >
all(select salary from t_employees where department_id=60);
#子查询（作为一张表）
查询员工表中工资排名前 5 名的员工信息
select * from (select * from t_employees order by salary desc) as t limit 0,5;

# 合并查询 
合并两张表的结果 (去除重复的)  union
合并两张表的结果（保留重复记录）union all

#内连接  inner join on 
查询所有有部门的员工信息（不包括没有部门的员工） SQL 标准
select * 
from t_employees a 
inner join t_departments b 
on a.department_id=b.department_id; 
查询所有员工工号、名字、部门名称、部门所在国家ID 
select * 
from t_employees a 
inner join t_departments b 
on a.department_id=b.department_id
inner join t_locations c
on b.location_id=c.location_id;

#左外连接  left join on 
查询所有员工信息,以及所对应的部门名称(没有部门的员工,也在查询结果中,部门名称以NULL填充)
select * 
from t_employees a 
left join t_departments b 
on a.department_id=b.department_id; 

#右外连接  right join on
查询所有部门信息,以及此部门中的所有员工信息(没有员工的部门,也在查询结果中,员工信息以NULL填充)
select * 
from t_employees a 
right join t_departments b 
on a.department_id=b.department_id; 

5.约束

5.1.实体完整性约束

主键约束  primary key
create table if not exists student(
id char(10) primary key,
name char(20),
age char(10),
birth char(40));                 //创建表
insert into student VALUES
(01,'张三',18,'2000.12.15'),
(02,'李四',19,'2000.06.17'),
(03,'王五',18,'2000.04.09');    //插入数据
select * from student;          //查表
drop table student;             //删表

唯一约束  unique
create table if not exists student(
id char(10) primary key,
name char(20) unique,
age char(10),
birth char(40));                 //创建表
insert into student VALUES      
(01,'张三',18,'2000.12.15');
(02,'李四',19,'2000.06.17'),
(03,'王五',18,'2000.04.09');     //插入数据
drop table student;              //删表

自动增长列 auto_increment
create table if not exists student(
id int primary key  AUTO_INCREMENT,
name char(20) unique,
age char(10),
birth char(40));                   //创建表
insert into student VALUES
(1,'张三',18,'2000.12.15');
insert into student(name,age,birth) VALUES
('李四',19,'2000.06.17'),
('王五',18,'2000.04.09');          //插入数据
select * from student;             //查表
drop table student;                //删表

5.2.域完整性约束

非空约束 not null(非空，此列必须要有值)
create table if not exists student(
id int primary key  AUTO_INCREMENT,
name char(20) unique not null,
age char(10),
birth char(40));   

默认值约束 default 为列赋予默认值
create table if not exists student(
id int primary key  AUTO_INCREMENT,
name char(20) unique not null,
age char(10) default 18,
birth char(40));   

引用完整性约束(外键约束)

6.事务

1.概念：多个sql的捆绑，都成功就commit提交，只要有一个失败就rollback回滚
2.事务原理：
   数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL	语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。
   
start TRANSACTION;   开启事务
update account set money=money-20000 where id=1;
//中间可能出现了异常
update account set money=money+20000 where id=2;
commit;   都成功则提交
ROLLBACK;  如果出现异常则回滚

3.事务特性（ACID）
原子性：不可分割的整体，要么事务都成功，要么都失败
一致性：不管事务是否成功，总体数据是不会改变的
隔离性：处在事务中的线程，和其他操作的线程是隔离的，互不影响的
持久性：一旦进行了提交，则永久性的写到了数据库中

4.应用场景：
一般用在安全性较高的项目，例如金融项目，商城项目等

7.权限管理

场景：一般在公司给你分配一个非管理员的账户，可能只负责查询或添加

创建一个zs的用户
create user 'zs' IDENTIFIED by '123';

用户授权
GRANT ALL ON mydb1.account TO 'zs';

撤销用户权限
REVOKE ALL on mydb1.account from 'zs';

删除用户
drop user 'zs';

也可以进行图形化用户管理操作

8.视图

创建视图
create view myview as 
select employee_id,first_name,last_name,salary from t_employees;
查询视图
select * from myview where employee_id='101';
修改视图
ALTER VIEW myview
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;
删除视图
drop view myview;
- 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
- 如果视图包含以下结构中的一种，则视图不可更新（查询操作不更新）
  - 聚合函数的结果
  - DISTINCT 去重后的结果
  - GROUP BY 分组后的结果
  - HAVING 筛选过滤后的结果
  - UNION、UNION ALL 联合后的结果