SQL语句分类
DDL数据定义语言:用来定义数据库对象,数据库,表,列等、关键字:create,alter,drop
DCL数据库控制语言:用来定义数据库的访问权限和安全级别,以及创建用户
DML数据操作语言:用来对数据库表中的记录进行更新,关键字:insert,delete,update等
DQL数据查询语言用来查询数据库中表的记录。关键字:select,from,where等
数据库类型
类型名称 | 说明 |
---|---|
int | 整数类型 |
double | 小数类型 |
decimal | 指定整数位与小数长度的小数类型 (5,2)代表一共5位数,小数占2位 |
date | 日期类型,格式位yyyy-MM-dd,包含年月日,不包含时分秒 |
datetime | 日期类型,格式为YYYY-MM-DD HH:MM:SS,包含年月日时分秒 |
timestamp | 日期类型,时间戳 |
varchar | 文本类型,M为0-65535之间的整数,varchar(100)可变长度,常用 |
int | 文本类型,M为0-65535之间的整数 char(100)长度固定为100 |
数据库操作
- 创建数据库
格式一:create database 数据库名; 默认编码表utf8
格式二:create database 数据库名 character set gbk - 使用数据库
格式:use 数据库名 - 查看某个数据库的定义信息
格式:show create database 数据库名 - 查看所有数据库
格式:show databases - 查看正在使用的数据库
格式:select database(); - 删除数据库
格式:drop database 数据库名称 - 修改数据库编码集
格式:alter database 数据库名称 character set 编码表; - 创建表
格式:create table 表名 字段名称(多个元素逗号隔开) - 删除表
格式:drop table 表名 - 插入数据/记录
格式:insert into 表名(字段1,字段2) values(依次给每个值赋值)
格式2:insert into 表名 values(依次给每个值赋值) - 修改数据/记录
格式:update 表名 set 表里字段名=新值 - 删除数据/记录
格式:delete from 表名 - 查询记录
格式:select * from 表名 - 查看表结构
格式:desc 表名
面试题:delete和truncate删除表中所有数据的区别?
1.DELETE
DML语言,可以回退(可以将数据找回)
可以有条件的删除 delete from 表名 where 条件
如果主键自增长,记录id,添加数据时,id继续(之前删除的最后一条记录的id+1)
2.TRUNCATE TABLE
DDL语言
无法回退(不可以将数据找回)
删除速度比delete块 truncate table 表名
如果主键自增长,不记录id,添加数据时,id重新开始
SQL约束
什么是约束?
约束,其实就是一种限制条件,让你不能超出这个这个控制范围。而数据库中的约束,就是指 表中的数据内容,不能胡乱填写,必须按照要求填写,好保证数据完整性与安全性。
主键约束:不能为空,唯一;一个表只能有一个主键。作为主键的字段不能使用具有业务含义的字段做为主键。常用id作为主键,创建表时id字段后边添加;关键字:primary key
自动增长auto_increment
唯一约束:特点:不能重复,可以为空;关键字:unique
非空约束:不能为空,可以重复;关键字:not null
默认约束:用于指定字段默认值;关键字:default
mysql查询
-
简单查询
select 字段名1,字段名2 from 表名 where 条件;
select * from 表名;(*代表的是表中的所有字段)
起别名:
格式: 列名 AS ‘别名’
注意:
①as可以省略
②’ ‘:也可以省略,但是如果别名中有单引号,’ '不能省略了 -
查询所有价格(重复的只有一个)
select price as ‘商品价格’ from product;(结果有重复)
select distinct price as ‘商品价格’ from product;(distinct关键字去重) -
SELECT后边可以跟表达式
select price ‘涨价前’ ,price+10 ‘涨价后’ from 表名; -
条件查询
①比较运算符 BETWEEN…AND… 显示在某一区间的值(含头含尾)
②IN 显示在列表中的值(),例:WHERE 居民 IN(‘北京’,‘上海’)查询所有住在北京和上海的居民
③LIKE模糊查询 ‘1% ‘以1开头的模糊查询;’%1’以1结束的;’%1%'中间包含1的
④IS NULL 判断是否为空 (WHERE 字段 is null )查询为空的数据-------- (not is null)查询不为空的数据
⑤and多个条件同时成立,并且的关系
⑥or 多个条件任一成立,或者的关系
⑦not 不成立 ,例:where not(salary>100)
⑧order by 排序,默认是升序(desc是降序) 表名 order by salary;按照工资排序 -
分页查询
分页查询需要那些内容?
a. 总记录数:totalCount
b. 每页显示的记录数量:pageSize
c. 总页数:totalPage
d. 当前页码:currentPage
e.起始索引:startIndex
动态计算:select * from 表名 limit((页数-1)×条数,条数) -
聚合函数
max最大值
min最小值
sum求和
avg平均数
count总记录数
group by分组
注:需要分组的口诀
①查询结果中出现了混合列,包括聚合列与非聚合列
②通常按照非聚合列分组
③where 里不能用别名,where后边不能写聚合函数,having后边可以写聚合函数 -
SQL语句执行顺序
from–>where–>group by–>having–>select–>order by
事务
- 什么是事务?
保证一系列操作以后,SQL语句要么全成功,要么全失败,增删改多用事务
1)原子性:把多条SQL看做一个原子密不可分,要么全成功,要么全失败
2)一致性:保证数据守恒,将来广泛应用到分布式系统里
3)隔离性:mysql数据库可以支持高并发,可能会有数据安全的隐患,也有复杂的隔离级别
4)持久性:对数据库的操作,不再次修改,将产生持久影响
----->隔离级别:read uncommitted 读未提交:性能好,安全差
read commited 读已提交:是oracle数据库默认级别,性能差,安全
repeatable read 可重复读:是mysql数据库默认级别,性能差,安全特好
serializable 串行化:性能非常差,但是安全相当好
字段约束
- 默认约束–>default
字段添加了默认约束,从此字段值就有了默认值 - 检查约束–>check(age<100 and age>10)
只有满足了条件才可以插入数据 - 外键约束–> foreign key 当前表的主键名 references 对方表的主键
使用外键 情况1:字表的主键必须取自于主表的主键值
情况2:主表的记录,在子表中没有被使用才可以被删除
多表联查
- 笛卡尔积
select * from 表1,表2 - 连接查询(两种)
①内连接
inner join:取两张表的交集
②外连接
left join:取左表的所有和右表满足条件的,不满足条件的都是null
right join:取右表的所有和左表满足条件的,不满足条件的都是null
注:连接查询的效率,小表驱动大表
a. 把结构简单,或者数据较少的表放在from 后边
b. 因为from会对小表进行全表扫描,然后去判断表关系,右表只查满足条件的数据 - 子查询
a. 也叫嵌套查询,执行效率低,慎用,记录少可以用,多了建议其他方式代替
b. 返回结果为一个用等号,结果为多个用in
c. 子查询也可以查同一张表
索引
- 创建索引
创建单值索引:一个索引一列
create index 索引名字 on 表名(字段名)
创建唯一索引
create unique index 索引名 on 表名(字段名)
创建复合索引
create index 索引名 on 表名 (字段1,字段2) - 使用索引,发起sql语句
select * from 表名 where ename=‘Jack’ - 查看索引
show index from 表名 - 检查sql的执行效率(观察有没有使用索引)观察possible keys
explain select * from dept where dname = ‘Jack’ - 复合索引需要遵循最左原则,否则会让符合索引失效
create index eindex on 表名 (ename,job,deptno)
1)需要包含ename,否则查询时,复合索引会失效 - 删除索引
alter table 表名 drop index 索引名
视图
什么是视图?
视图是一张虚拟表,封装了底层与数据表的接口,相当于一张表或多张表的数据结果集。是一种允许用户通过视图访问数据的安全机制。
- 创建视图:create view 视图名 as select 查询语句
好处:提高了sql的复用性+屏蔽了业务表的复杂性+数据共享(数据共享的意思是对视图操作,原表也会改变)
坏处:是一张单独的表,存了业务表的数据,造成了数据重复,无法优化