数据库MYSQL

最新推荐文章于 2024-03-18 23:56:53 发布

原创最新推荐文章于 2024-03-18 23:56:53 发布 · 置顶 · 412 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#MYSQL

数据库语句

DDL语句（Data Definition Language）即数据库定义语言：

DDL语句包含Create、Alter、Drop等常用语句

数据库操作

查看数据库

show databases

创建数据库

create database + 数据库名（实例名）

切换/选择数据库

use + 数据库名

查看数据库中所有的表

show tables

删除数据库

drop database + 数据库名（实例名）

表操作
- 创建表

create table + 表名（字段名字段属性（英文字符长度））；

- 查看表结构

desc + 表名

- 修改表

alter table + 表名 ...

修改字段长度

alter table + 表名 +modify +列名 + 字段属性（字段长度）

增加字段

alter table + 表名 + add column +字段名字段类型(字段长度)

删除字段

alter table + 表名 drop column + 字段名

修改字段名及长度

alter table + 表名 change column 旧字段名新字段名字段属性（字段长度）

- 删除表

drop table + 表名

DML语句（Data Manipulation Language）---数据操作语言：

DML包含 Insert、Update、Delete等常用语句，增删改查操作

sql语句的dml操作主要包含对数据的操作

insert语句（数据插入）

insert into + 表名（字段一、字段二） value (v1,v2)

insert into + 表名（字段一、字段二） values (v1,v2),(v1,v2)

insert into + 表名 value (v1,v2)

update语句

update + 表名 set ...

delete语句

delete from + 表名

select语句

select * from +表名

select * from + 表名 limit 5(查询前5条数据)

5.算术表达式查询2*3的值，在emp表中

select 2*3 from emp

6.在虚拟表中查询2*3的值(dual)

select 2*3 from dual

7.在虚拟表中查询系统时间(sysdate)

select systade() from dual

8.查询并修改列别名

select ename as name from 表名

9.查询ename，sal*12+comm，注意null的计算效果

select ename,sal,comm,sal*12+IFNULL(COMM,0) from 表名

10.去重查询

select distinct 字段名 from 表名

Sql语句拓展

where条件查询

等值比较

select * from 表名 where id=10;

当需要判断字符串相等时需要将字符串用单引号引起来，所有的关系型数据库都是这样做的，但需要注意的是，如果需要将字符串的内容区分大小写，需要再wehere后面加上BINARY

函数

非等值比较

除了可以使用等值判断以外，我们可以使用其他的条件判断符号，> >= < <= <>

select sal from 表名 where sal>1000

select sal from 表名 where sal<1000

select sal from 表名 where sal>=1000

select sal from 表名 where sal<=1000

select sal from 表名 where sal<>1000

2.in语句

in 语句一般与where语句连用，where ... in .... , where ... not in.....

select * from emp where sal in(800,1250,1500,2000)

select * from emp where sal not in(800,1250,1500,2000)

3.处理日期

查询再某个时间之前或之后可以使用 > <

select * from emp where hiredate > '1980-01-01'

4. and、 or 、 not

select * from emp where DEPTNO =10 and sal >1000

select * from emp where DEPTNO = 10 OR JOB='CLERK'

select * from emp where sal NOT IN(800,1250,1500,2000)

5.模糊查询

关键字‘like’被用作模糊查询，在只知道字符串的一部分是，可以使用like进行模糊查询。‘like’通常和通配符一起使用，通配符有两个：

‘%’ 表示0或多个字符：0-N个任意字符

‘--’ 表示一个字符：1个任意字符

select * from emp where ENAME LIKE '%LA%'

select * from emp where ENAME like '_A%'

6.order by 排序排序方式有两种：asc 升序（默认升序） desc 降序

select * from emp ORDER BY ENAME asc

select * from emp ORDER BY EMPNO ASC

select * from emp ORDER BY sal ASC , deptno DESC

select * from emp ORDER BY deptno DESC , sal ASC

7.sql函数

low函数可将字符串全部转换成小写

select LOWER(ENAME) from emp

upper函数可将字符串转换成大写

select JOB, UPPER(JOB) from emp

substr字符串截取

SELECT 'helloword', SUBSTR('helloword', 3, 5) from DUAL

Ascii码

SELECT ASCII('A') from DUAL

round四舍五入

select ROUND(34.643) from DUAl --默认取整

select ROUND(34.643,2) from DUAL --小数点后面保留位数，2代表保留2位

select ROUND(12334.643,-2) from DUAL --精度可以为负数

8.date_format 将时间转换成字符串

str_to_date 将字符串转换成日期格式

CAST(123 AS CHAR(3) 将数字转换成字符串

cast( '123 ' as SIGNED INTEGER) 将字符串转换成数字

select DATE_FORMAT(SYSDATE(),'%Y-%m-%d') from DUAL

select STR_TO_DATE('2018/08/30 22:36:45','%Y/%m/%d %H:%i:%s') from DUAL

select cast(123 as char(5)) from dual

select cast('123' as signed integer) from dual

9.nvl函数

mysql中不支持nvl函数，支持的是ifnull函数

select comm，ifnull（comm，0） from emp

10.组函数

1.求平均值 avg

2.求最大值 max

3.求最小值 min

4.求和 max

5.求记录的数量 count

SELECT COUNT(COMM) from emp

11.group by 分组

有的时候我们需要将表中的数据进行分组，比如说我们需要计算每个部门的平均工资，这个时候必须首先将现有的数据按照部门进行分组，然后再计算每个组员工的平均薪水。Group by 正是为了解决这样的需求而设立。

having对分组进行限制

12.两表关联查询

select 字段 from 表一、表二笛卡儿积

条件关联查询：SELECT * from emp ,dept where emp.DEPTNO = dept.DEPTNO

SELECT e.*,d.DNAME from emp e , dept d WHERE e.DEPTNO=d.DEPTNO

13.多表关联查询

在关联查询的时候，左面表放在内存中用关联字段比对右面表，所以左面放小表，右面放大表，节省内存空间。

单表关联

SELECT * from emp e1,emp e2 WHERE e1.MGR = e2.EMPNO

14.join关联查询

笛卡尔积关联

SELECT * from dept d JOIN emp

Using只能做同名字段关联

SELECT * from dept d JOIN emp e ON e.DEPTNO = d.DEPTNO

On条件可以关联不同字段的名字

SELECT * from emp e JOIN dept d -- JOIN salgrade s ON e.DEPTNO=d.DEPTNO AND e.SAL>=s.losal AND e.SAL<=s.hisalon e.DEPTNO = d.DEPTNO JOIN salgrade s

ON e.SAL>=s.losal AND e.SAL<=s.hisal

左外关联和右外关联

普通关联方式关联主键出现null值的时候忽略该条数据员工表有两条数据的部门编号为null ，当普通关联的时候主键为null的数据将被忽略需求：查询出所有的员工，如果员工的部门编号不为空，则把部门信息查出，如果部门编号为空，也要把员工信息查出来，部门信息可以为Null

select * FROM emp e LEFT JOIN dept d on e.DEPTNO=d.DEPTNO

15.子查询

子查询就是查询套查询

having 和where的区别

1.where 用于全局过滤，或者关联

2.having只适用于分组并且聚合过滤，通常having groupby（avg,max,min,count,sum）.

分页查询

limit 函数

select * from emp limit 3

查询emp表中第3-6条数据

select * from emp order by empno limit 2，4

limit 第一个属性是下标值，第二个属性是长度

sql索引

1.索引介绍

通过客户号查询流水表，要在整个表中所有行所有列进行检索。

将常查询的字段维护到一张表（虚拟表），查询该字段的时候到这个虚拟表中查询到对应的表的位置

优点：查询效率高（因为查询的时候从已经去除多余的字段的小虚拟表中查询，再找到对应的数据信息，所以效率高）

缺点：增删改效率偏低（因为增删改的时候要维护表的同时还要维护以下索引）

1）普通索引

普通索引是指索引的对应字段可重复

查询效率低（因为索引中有重复值，所以查询到结果后还需继续检索，一直将全表检索完毕）

2）唯一索引

唯一索引是索引0的对应的字段不可重复

查询效率快（因为没有重复值，所以检查到结果后直接返回，不用全表检索）

2.索引操作

为emp表的empno字段创建一个索引 i_emp

创建索引的格式：创建索引索引名 on 表名（字段名（长度））

create index i_emp on emp(empno)

修改emp表，为其添加一个索引 i_emp_deptno(deptno)

格式：修改表表名增加索引索引名（索引字段）

alter table emp add index i_emp_deptno(deptno)

创建一个表 t48_transaction

字段包括：id,tranno,custom,acctno,toacctno,num并指定索引名为i_t48_transaction，字段名为tranno。

格式：创建表表名（字段属性（长度），索引索引名（字段））

create table t48_transaction(

id integer(4),

tranno varchar(30),

custom integer(20),

acctno integer(30),

toacctno integer(30),

num decimal(20,2),

index i_48_transaction(tranno)

)

删除emp表的 i_emp 索引

格式：删除索引索引名 on 表名

drop index i_emp on emp

为emp表创建唯一索引，名为i_emp_empno，字段empno

格式：创建唯一索引索引名 on 表名（字段名）

create unique index i_emp_empno on emp(empno)

修改该表方式为员工表的ename字段创建唯一索引

格式：修改表表名增加唯一索引索引名（字段）

alter table emp add unique index i_emp_ename(ename)

创建表同时指定索引

格式：创建表表名（字段属性（长度），唯一索引索引名（字段））

创建t48_custom表，字段包括：customid，cname,identity,address,phone,openbank，指定唯一索引i_t48_custom_customid，对应字段为：customid

create table t48_custom(

customid integer(20),

cname varchar(20),

identity varchar(20),

address varchar(60),

phone integer(11),

openbank varchar(60),

unique inde i_t48_custom_customid(customid)

)

视图

1.视图理解

将经常用到的数据为了便于查询，将数据结果放到视图中，便于以后的查询，注意，视图中的数据结果与原表同步

视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询。

可以通过视图插入数据，但是只能基于一个基础表进行插入，不能跨表更新数据。

通过试图修改，可能导致数据无故消失，因此:

没有特殊的理由，建议加上“WITH CHECK OPTION”命令.

2.视图操作

1.创建一个视图将emp表的部门编号为10的数据创建视图，视图名： v_emp

create view v_emp as select * from emp where deptno=10;

2.在v_emp视图中查找sal ='2965的数据

select * from v_emp where sal ='2965'

3.修改视图里的数据将sal=2965的数据将sal改为1234（注意修改视图数据后原数据表是否有变化）

update v_emp set sal=1234 where sal=2965

4视图v_emp插入一条数据empno=8001,ename=’stu1’,deptno=10（注意emp表中的变化）

insert into v_emp(empno,ename,deptno) values(8001,'stu1',10)

5.删除v_emp的一条数据，empno=8001

delect from v_emp where empno=8001

6.创建或替换视图

创建或替换视图v_dept，查询dept表中deptno为30的数据

create or replace view v_dept as select * from dept where deptno=30

创建或替换视图v_dept，查询dept表中deptno为20的数据

create or replace view v_dept as select * from dept where dept=20;

7.查看所有视图

show tables

8.查看所有视图，不报含表

show tables like 'v\_%';

9.查看视图详情

desc v_emp

sql存储过程

定义：

简单的说，就是一组sql语句集，功能强大，可以实现一些比较复杂的逻辑功能，主要应用场景是etl（数据清洗）。

sql语句需要先编译然后执行，而存储过程是就是一组为了完成功能的特定语句集，经编译后存储到数据库中，用户通过指定存储过程的名字并给定参数（如果该语句集带参数）来调用它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成，当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库种的存储过程可以看成是对编程种面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点：

增加SQL语言的功能和灵活性

存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算

标准组件式编程

存储过程被创建后，可以在程序中多次调用，而不必重写编写该存储过程的sql语句，而且数据库专业人员可以随时对改存储过程进行修改，对应用程序源代码毫无影响

较快的执行速度

如果某一操作包含大量的Transaction-SQL代码或分别多次被执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

减少网络流量

针对同一个数据库对象的操作（如查询、修改），如果这一操作所设计的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的知识该调用语句，从而大大减少网络流量并并降低了网络负载。

作为一种安全机制来充分利用

通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

存储过程规范：

create procedure name()

begin

;

commit;

end

;

1.复制表结构

create table t48_emp as select * from emp where 1=2;

2.复制表的数据

INSERT INTO t48_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno from emp

3.删除表的所有数据

delete from t48_emp/turncate t48_emp;

4.用CONCAT在虚拟表中查找aa和bb连个字符串的拼接

SELECT CONCAT('aa','bb') FROM DUAL

5.查询empno字段，前面拼接001-入职时间(年-月-日)

SELECT CONCAT('001-',hiredate,'-',empno) from t48_emp

6.查询empno字段，并在前面拼接001-入职时间(年月日)

SELECT CONCAT('001-',DATE_FORMAT(hiredate,'%Y%m%d'),'-',empno) from t48_emp

7.在虚拟表中查询系统时间

SELECT SYSDATE() FROM DUAL

8.查询emp表中的所有字段，将comm字段的null替换成0，并额外查询两个属性：eventkey，etldt

SELECT empno,ename,job,mgr,hiredate,sal,IFNULL(comm,0),deptno from emp

SELECT CONCAT('001-',DATE_FORMAT(HIREDATE,'%Y%m%d'),'-',empno) eventkey,DATE_FORMAT(SYSDATE(),'%Y%m%d%H%i%s') etldt,empno,ename,job,mgr,hiredate,sal,IFNULL(comm,0),deptno from emp

9.查询emp表中的所有字段，将comm字段的null替换成0，并额外查询两个属性：eventkey，etldt，将查询到的结果插入到t48_emp表中

INSERT INTO t48_emp(eventkey,etldt, empno,ename,job,mgr,hiredate,sal,comm,deptno)

SELECT

CONCAT('001-',DATE_FORMAT(HIREDATE,'%Y%m%d'),'-',empno) eventkey,DATE_FORMAT(SYSDATE(),'%Y%m%d%H%i%s') etldt,

empno,ename,job,mgr,hiredate,sal,IFNULL(comm,0),deptno

from emp

存储过程的使用场景：

用sql语句即可完成数据迁移清洗（etl），在真实工作工作场景中经常要用到，后期学到大数据的时候也会用到etl数据清洗。以上的sql语句不便于存储和调用，每次都要输入过长的sql语句，为了便于etl的操作，可以使用存储过程。

存储过程优化

1、eventkey要唯一，要动态生成。

2、eventkey时间是员工入职时间

3、eventkey长度加长