数据库语句大全

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.99char:固定⻓度字符串类型; 
		  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))
查询部门编号为708090的员工信息(编号,名字,薪资,部门编号) 
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
统计607090号部门的最高工资 
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;

查询表中从第四条开始,查询 10SELECT *
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 联合后的结果
Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐