September 14, 2021 - September 23, 2021
SQL分类
1.数据查询语言(DQL)
数据查询语言(Data Query Language, DQL)是SQL语言中,负责进行数据查询而不会对数据本身进行修改的语句,这是最基本的SQL语句。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有FROM,WHERE,GROUP BY,HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。
2.数据定义语言(DDL)
数据定义语言 (Data Definition Language, DDL) 是SQL语言集中,负责数据结构定义与数据库对象定义的语言,由CREATE、ALTER与DROP三个语法所组成,最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始,现在被纳入 SQL 指令中作为其中一个子集。
3.数据操纵语言(DML)
数据操纵语言(Data Manipulation Language, DML)是SQL语言中,负责对数据库对象运行数据访问工作的指令集,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除。
4.数据控制语言(DCL)
数据控制语言 (Data Control Language) 在SQL语言中,是一种可对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主,GRANT为授权语句,对应的REVOKE是撤销授权语句。
单行处理函数
-
lower:转为小写
-
upper:转为大写
-
substr:截断
格式1: substr(string string, int a, int b);
a 截取字符串的开始位置(注:当a等于0或1时,都是从第一位开始截取) 3、b 要截取的字符串的长度
格式2:substr(string string, int a) ;
从第a个字符开始截取后面所有的字符串。
-
ifnull
空处理,IFNULL(key, alt_value),key:指定的字段或者值,alt_value:key表达式为 NULL 时返回的值
-
concat:字符串拼接
-
length:长度
-
str_to_date:将字符串varchar类型转为date类型
用法:str_to_date(‘字符串日期’,‘日期格式’)
mysql的日期格式:%Y:年;%m:月;%d:日;%h:时;%i:分;%s:秒
注意:如果日期格式是%Y-%m-%d的格式,则可以直接省略。
-
date_format:将date类型转为具有一定格式的varchar字符串类型
date_format(日期类型数据,‘日期格式’)。sql可以自动将数据库中的date类型转换成varchar类型。并且采用的格式是mysql默认的日期格式:%Y-%m-%d
-
round:四舍五入
-
rand:随机数
-
format:数字格式化,format(数字,‘格式’)
聚合函数
- sum:总和
- count:数量
- avg:平均
- max:最
- min:最小
特别说明点
- null:相当于不存在
- 分组函数中自动忽略掉null比如求和时包含null时自动不计算。
- count(*):统计表当中的总行数,只要行数中有一个值则count++。
- count(具体字段):表示统计该字段下所有不为null的总数。
执行顺序
- 语句顺序:select … from table where … group by …having … order by … limit …
- 执行顺序:①from ②where ③group by ④having ⑤select ⑥order by
- 执行顺序:从表中查询数据,先经过where进行筛选出有价值的数据,对这些有价值的数据进行分组,分组之后进行having进一步筛选。select查询出来,最后排序输出指定条数。
- 分组函数不能用在分组函数之后:如 select * from table where s>min(ss); 虽然没有写group by,那么默认分成了一组。因为where在group by之前执行的,所以不能这样写。
group by
特别说明点
sql语句中,有group by时,select后面只能跟上参加分组的字段,以及分组函数,其他的一律不能跟。因为分组时已经对某个字段进行了分组,如果在select后另加某个字段,就没有意义。
where和having
优化策略:优先使用where,where用不到了再用having
举例:
找出各部门最高的薪资,要求最高薪资大于3000的?
用where更恰当:select deptno,max(sal) from emp where sal>3000 group by deptno
找出各部门平均薪资,要求平均薪资大于2500的?
用having更恰当:select deptno,avg(sal) from emp group by deptno having avg(sal)>2500
distinct
作用:去重
举例:
select distinct job,deptno from emp;
特殊说明
- distinct:只能出现在所有字段的前面
- 出现在多个字段之前, 表示两个字段联合起来去重。
连接查询
笛卡尔积
- 当两张表进行查询时,没有任何的查询条件时,最终查询的结果条数,是两张表条数的乘积。
- 如何避免笛卡尔积:减少连接次数
内连接
-
关键字:inner join on,inner可省略
-
语法:select * from table_a a join table_b b on a.a_id = b.b_id
-
返回的结果集
两表的交集
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rtak8JlM-1632374560870)(https://s3-us-west-2.amazonaws.com/secure.notion-static.com/aabbb34a-8d93-4cf0-b35b-60265381f420/Untitled.png)]
- 分类
- 等值连接:select * from table_a a join table_b b on a.a_id = b.b_id
- 非等值连接:select * from table_a a join table_b b on a.a_sal between b.sal and b.hsal;
- 自连接:
外连接
- 左外连接(又称左连接)
- 将join关键字左边的表作为主表,捎带查询右边的表
- 关键字:left join /left outer join
- 语句:select * from table_a a left join table_b b on a.a_id = b.b_id
- 右外连接(又称右连接)
- 将join关键字右边的表作为主表,捎带查询左边的表
- 关键字:right join / right outer join
- 语句:select * from table_a a right join table_b b on a.a_id = b.b_id
内连接和外连接的区别
内连接:完成能够匹配上这个条件的数据查询出来。
外连接:不仅仅查出能够匹配上这个条件的数据查询出来,左连接是将左边的表中所有的记录都查出来,右边的表只匹配符合条件的数据行。右连接则与之相反。
子查询
语句:select …(select). from …(select). where …(select).
where 字句的
一步一步来写。
from字句
from后面的子查询,可以将子查询的查询结果当做一张临时表。
union
union的效率要高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的增加。
但是union可以减少匹配次数,在减少匹配的次数的情况下,还可以完成两个结果集的拼接。
举例:
a表记录是10条,b条记录是10条, c条记录是10条。则匹配次数:101010=1000
而union,a连接b表:1010=100,a表连接c表:1010=100,union的总连接书:100+100=200
相当于union将乘法变成了加法。
union使用注意事项
-
在使用时union合并结果集时,要求两个结果集的列数相同。
举例:下面的sql是正确的。假如一个返回是ename,job,一个返回ename,则会报错。
select ename,job from emp where job = 'it' union select ename,sal from emp where job = 'sale'
-
mysql可以,oracle不可以。要求:结果集合合并时列和列的数据类型也要一致。
limit
作用:分页查询
用法:
完整用法:limit startIndex,length ,即length 0,5 取前五条
缺省用法:limit 5 直接取前五条
常用数据类型
varchar
可变长度字符串,可根据实际的数据长度动态分配空间,可节省空间。
char
定长,固定分配空间。
int
数字中的整型,等同于java中的int。
bigint
数字中的整型,等同于java中的long。
float
单精度浮点型数据
double
双精度浮点型数据
date
短日期类型,只包含年月日信息。
datetime
长日期类型,包含年月日时分秒信息。
now():带有时分秒的日期
表相关的操作
创建表
删除表
drop table if exists 表名;
插入数据(DML)
insert into 表名(字段名1,字段名2,字段名3) values(1,‘xx’,‘23’);
插入多条记录:
insert into 表名(字段名1,字段名2,字段名3) values (xx,xx,xx),(xx,xx,xx);
更新数据
update 表名 set 字段名1 = 值,字段名2 =值 where 条件;
删除数据
delete from 表名 where 条件;
注意:没有where,则删除整个表的数据。
快速复制表
create table 表名 as select * from 表名;
将查询结果插入一张表
create table 表名 as select 字段名1,字段名2 from 表名 where 字段名=xxx;
insert into 表名 select * from 表名;
快速删除表的数据
-
delete
表中的数据被删除,但是这个数据在硬盘上的真实存储空间不会被释放。
删除的缺点:删除效率非常低;
删除的优点:支持回滚,可以恢复数据。
-
truncate
删除效率高,物理删除
优点:快速;缺点:不支持回滚。
truncate table dept_bank;
-
drop
删除的是表
-
大表非常大,上亿条数据如何删除
delete删除:可能需要1h执行才能删完,效率很低
truncate删除,只需要不到1s的时间就删除结束。
但truncate删除之前,必须仔细询问客户是否真的要删除。
但truncate删除之前,必须仔细询问客户是否真的要删除。
对表结构进行增删改
-
删除字段:ALTER TABLE testalter_tbl DROP i;
-
新增字段:ALTER TABLE testalter_tbl ADD i INT;
-
修改字段类型及名称:
ALTER TABLE testalter_tbl MODIFY c VARCHAR(10);
ALTER TABLE testalter_tbl CHANGE i j BIGINT;
-
指定字段 j 为 NOT NULL 且默认值为100 :
ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
-
修改字段默认值:ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
-
修改表名:ALTER TABLE testalter_tbl RENAME TO alter_tbl;
-
特别说明点:
删除表,先删子,后删父。
创建表,先创建父,再创建子。
约束
在mysql中,如果一个字段被not null 和unique同时约束,则该字段自动变成主键字段。而oracle不是。
表级约束主要是给多个字段联合起来添加约束。
-
非空约束:not null
只有列级约束,没有表级约束。
-
唯一约束:unique
字段不能重复,可为null。联合唯一索引:unique(字段1,字段2)
-
主键约束:primary key
外键约束:foreign key;
检查约束:check ,mysql不支持,oracle支持
主键
- 主键:not null + unique,即不能为空,也不能重复。
- 主键只能有1个。
- 建议使用:int,bigint,char。
- 不建议使用:varchar做主键,主键值一般都是数字,一般 都是定长的。
- 主键分类:单一主键,复合主键,自然主键,业务主键
- 自然主键:主键值是个自然数,和业务没关系。
- 业务主键:主键值和业务关系密切。
- 一般建议使用自然主键,因为业务一旦发生变化,可能会影响主键值。
外键
子表中的外键引用的父表中的某个字段,被引用的字段不一定是主键,但至少具有unique约束。
外键也可以为null
存储引擎
存储引擎是mysql的特有术语。实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎,表存储数据的方式不同。
mysql默认的存储引擎是InnoDB,默认的字符编码方式是utf8。
如何查看mysql支持哪些存储引擎的命令:show engines \G
mysql> show engines \G
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
mysql支持九大存储引擎,不同版本支持的存储引擎不同。