概述
数据库:
DataBase(DB)存储数据的仓库,实际就是一些文件,文件中存储了特定格式的数据。
数据库管理系统:
DataBaseManagementSystem(DBMS)专门用来管理数据库。
常见数据库管理系统:
Mysql、Oracle等
SQL:
格式化查询语言,通过编写SQL语句,然后DBMS执行SQL语句来进行数据库管理(通用标准语言)
关系:
DBMS–执行–>SQL -->操作–>DB
SQL语句分类
- DQL:数据查询语言(带有select关键字)
- DML:数据库操作语言(对表中数据进行增删改) insert/delete/update
- DDL:数据定义语言(主要操作表的结构)create/drop/alter
- TCL:事务控制语言(事务提交:commit; // 事务回滚:rollback;…)
- DCL:数据控制语言(授权grant // 撤销权限revoke…)
数据类型
- int//integer大整数值(4bytes)
- float单精度浮点数值(4bytes)
- double双精度浮点数值(8bytes)
- date日期值(now()获取当前日期)
- time时间值或持续时间
- year年份值
- char定长字符串(0-255bytes) name char(10)–10个字符空间(固定) 存储性能高浪费空间
- varchar变长字符串(0-65535bytes) name varchar(10)–最高10个字符空间(看实际)
处理函数
- lower(列名)转换小写
- upper()大写
- substr(字符串,启示下标(从1开始),截取长度)
- length()取长度
- trim()去空格
- str_to_date(‘字符串日期’,‘日期格式(%Y-%m-%d %h:%i:%s)’) 字符串转日期
- date_format(日期类型数据,‘日期格式’) data类型转换成格式化的varchar字符串
- format(数字.‘格式’)数字格式化
- round(数,保留小数个数)四舍五入
- rand()生成随机数
- ifnull(数据,被当做哪个值)数据为null时会改成其他值
Mysql
- mysql数据库启动时,服务占有默认端口号为3306
- 计算机->管理->服务可以选择Mysql启动关闭 // 在命令行中net start/stop 服务名称
- mysql本地登录: mysql -uroot -p密码 // 退出: exit
- 导入数据source 路径(.sql文件)
- 字符串用单引号,虽然在mysql可以用双引号,但在oracle中不行
常用命令
- 退出mysql:exit
- 查看mysql中的数据库:show databases;
- 使用某个数据库:use 数据库名称;
- 查看当前使用的数据库:select database();
- 创建数据库:create database 数据库名称;
- 删除数据库:drop database 数据库名称;
- 快速复制表:create table mytable as select * from yourtable;
表
Tables–数据库中的最基本单元,有行(数据/记录)与列(字段)显示直观
聚合函数
概念:将一列数据作为一个整体,进行纵向计算
语法:select 聚合函数名(列名) from 表;(null值不参与所有聚合函数运算)
- count()统计数量(一般选不为null的列)
- max()最大值
- min()最小值
- sum()求和
- avg()平均值
DDL
DDL操作数据库
- 查询–show databases;
- 创建–create database 数据库名称;
- 创建(条件)–create database if not exists 数据库名称;
- 删除–drop database if exists 数据库名称;
- 查看当前使用数据库–select database();
- 使用数据库–use 数据库名称;
DDL操作表
- 查询–show tables;
- 查询表结构–desc 表名称;
- 创建–create table 表名(
字段名1 数据类型1,
字段名2 数据类型2,
); - 删除表–drop table if exists 表名;
- 修改表名–alter table 表名 rename to 新表名;
- 添加一列–alter table 表名 add 列名 数据类型;
- 修改数据类型–alter table 表名 modify 列名 新数据类型;
- 修改列名及数据类型–alter table 表名 change 列名 新列名 新数据类型;
- 删除列–alter table 表名 drop 列名;
DML
添加数据
- 指定列添加–insert into 表名(列名1,列名2,…) values(值1,值2,…);
- 全部列添加–insert into 表名 values(值1,值2,…);
- 批量添加–insert into 表名(列1,列2,…) values(值1,值2,…),(值1,值2,…);
修改数据
- update 表名 set 列名1=值1,列名2=值2,…[where 条件];(不加条件则所有数据都改)
删除数据
- delete from 表名 [where 条件];
DQL
查询语法
//顺序不可改变
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组后条件
order by
排序字段
limit
分页限定
基础查询
- 查询多个字段–select 字段列表 from 表名;
- 查询所有数据–select * from 表名;
- 去除重复记录–select distinct 字段列表 from 表名;(distinct放在所有字段最前面,字段联合再去除)
- 起别名–as(可省略);
条件查询
- 语法–select 字段列表 from 表名 where 条件列表;
- 大于> // 小于< // 大于等于>= // 不等于<>或!=
- 在某个范围(包含)between…and… // 在其中in(…)
- 占位符(_单个任意字符 // %多个任意字符)
- 是null(is null) // 不是null(is not null)
- 并且(and或&&) // 或者(or或||) //非(not或!)
模糊查询
- like ‘占位符’
排序查询
- 语法–select 字段列表 from 表名 order by 排序字段名1 [排序方式1],排序字段名2 [排序方式2]…;
- asc升序排列(默认) // desc降序排列
- select * from stu order by math desc,english asc; //数学一样时再按英语升序排列
分组查询
分组后,查询的字段为聚合函数和分组字段,查询其他字段无意义
- 语法–select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];
where与having区别 - where在分组前限定,不满足where条件不参与分组,having在分组后进行过滤
- where不能对聚合函数进行判断,having可以
- 执行顺序:where>聚合函数>having(优先选择where)
实例:
select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>2;//查询男女同学各自数学平均分及人数,其中分数低于70不参与分组,分组后人数大于2
分页查询
- 语法–select 字段列表 from 表名 limit 起始索引(从0开始),查询条目数;
- 计算公式:起始索引 = (当前页码-1)*每页显示的条数
- select * from stu limit 6,3; //每页显示3条数据,查询第三页数据
连接查询
子查询
select语句中嵌套select语句,被嵌套的select语句被称为子查询(当做新表)
- 语法
- 单行单列–select 字段列表 from 表 where 字段名 = (子查询);
- 多行单列–select 字段列表 from 表 where 字段名 in (字符串);
- 多行多列–select 字段列表 from (子查询) where 条件;
- 实例:
select
t.*,s.grade
from
select (job,avg(sal) as avgsal from emp group by job) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
约束
概念:作用于列上的规则,用于限制加入表的数据
分类:
- 非空约束:列中所有数据不能有null值
- 唯一约束:列中数据各不相同
- 主键约束:主键是一行数据的唯一标识,要求非空且唯一
- 检查约束:列中的值满足某一条件
- 默认约束:未指定的值采用默认值
- 外键约束:外键用来让两个表的数据建立连接,保证数据的一致性和完整性
非空约束
创建表时添加非空约束:
create table 表名(
列名 数据类型 not null,
…
);
建完表后添加非空约束:
alter table 表名 modify 字段名 数据类型 not null;
删除约束:
alter table 表名 modify 字段名 数据类型;
唯一约束
创建表时:
create table 表名(
列名 数据类型 unique [auto_increment],//auto_increment:当不指定值时自动增长
…
);
unique(列名1,列名2)表示列1与列2联合后唯一
create table 表名(
列名 数据类型,
…
[constraint] [约束名称] unique(列名)
);
建完表后:
alter table 表名 modify 字段名 数据类型 unique;
主键约束
创建表时:
create table 表名(
列名 数据类型 primary key(主键字段),
…
);
- 主键字段的每个值叫做主键值
- 某字段被not null和unique同时约束时,改字段自动变成主键约束(oracle不同)
- primary key(列名1,列名2)表示列1与列2联合后唯一且不为空(复合主键,不建议使用,一张表只能一个主键)
外键约束
创建表时:
create table 表名(
列名 数据类型,
…
[constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);
建完表后:
alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主列表名称);
删除约束:
alter table 表名 drop foreign key 外键名称;
连接
内连接
- 单表内连接时将单表看做多张表
隐式内连接:select 字段列表 from 表1,表2… where 条件;
显示内连接:select 字段列表 from 表1 [inner] join 表2 on 连接条件 where 筛选条件;
实例:
select e.name,d.dname from emp e join dept d on e.deptno = d.deptno;
外连接
左外连接:select 字段列表 from 表1 left [outer] join 表2 on 条件;(1表所有数据及交集)
右外连接:select 字段列表 from 表1 right [outer] join 表2 on 条件;(2表所有数据及交集)
[outer]看着可读性强可省略
多表连接
语法:select … from a join b on a/b连接条件 join c on a/c连接条件…
事物(重点)
- 一个事物是一个完整的业务逻辑(操作序列),包含一组数据库操作命令,事物把所有命令作为整体,数据库命令同时成功/失败
- 只有DML语句和事物有关(insert delete update)
- 事物执行过程中,每一条DML操作都会记录到“事务性活动的日志文件”中
- mysql默认情况下自动提交事务(每执行一条DML语句则提交一次),start transaction关闭自动提交
- select @@autocommit 查看事物的默认提交方式(set @@auutocommit = 0(手动提交)/1(自动))
- select @@tx_isolation 查看隔离级别
- set global transaction isolation level 隔离级别; //修改全局隔离级别
提交事物
清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中。
提交事物标志着事物的结束。并且是一种全部成功的结束。
- 语句:commit
回滚事物
将之前所有的DML操作全部撤销,并且清空事物性活动的日志文件。
回滚事物标志着事物的结束。并且是一种全部失败的结束。
- 语句:rollback(回滚到上一次任务的提交点)
特性
原子性
- 事物是最小的工作单元,不可再分。
一致性
- 在同一个事物中,所有操作必须同时成功或失败以保证数据的一致性
隔离性(操作同一张表)
- 两件事物之间有一定的隔离,隔离有4个级别
-
- 读未提交:read uncommitted(最低隔离级别) 事物A可以读取到事物B未提交的数据.(脏读现象)
-
- 开启两个命令行窗口,开启事务B当插入数据未提交时,在事物A中即可看到新插入的数据
-
- 读已提交:read committed 事物A只能读到事物B提交后的数据,不可重复读取数据
-
- 开启两个命令行窗口,开启事务B当插入数据提交后,在事物A中才可看到新插入的数据
-
- 可重复读:repeatable read(MySQL默认隔离级别) 事物A开启后,在事物A中读取数据一致(不管事物B是否修改或提交)
-
- 开启两个命令行窗口,开启事务B无论数据是否提交,在事物A中只可看到原来的数据
-
- 序列化读/串行化:serializable 线程同步(事物同步)读取数据最真实,效率最低
-
- 开启两个命令行窗口,开启事务B只有当事物B提交后,事物A才可进行操作(为事物B操作后的结果)
持久性
- 事物最终结束的一个保障。事物提交就相当于把没有保存在硬盘上的数据保存到硬盘上
索引
索引是在数据库表的字段上添加的,提高查询效率的一种机制