数据库语句大全
·
数据库语句大全
1.sql分类
- DDL(Data Definition Language):数据定义语⾔,⽤来定义数据库对象:库、表、列等;
- DML(Data Manipulation Language):数据操作语⾔,⽤来定义数据库记录(数据);
- DCL(Data Control Language):数据控制语⾔,⽤来定义访问权限和安全级别;
- 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 联合后的结果
更多推荐
已为社区贡献1条内容
所有评论(0)