数据库语句大全

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 联合后的结果