数据库概述
什么是数据库?
存储数据的仓库,其实就是一堆存储了特定格式数据的文件
什么是数据库管理系统?
数据库管理系统英文为DataBaseManagement,简称DBMS。
数据库管理系统是专门用来管理数据库中的数据的,可以对数据库中的数据进行增删改查。
程序员编写sql语句,DBMS负责执行sql语句,来实现对数据库中数据的增删改查。
数据库操作
1、mysql的启动:
services.msc
net start mysql
2、mysql的关闭:
net stop mysql
3、mysql的连接(登录):
mysql -uroot -proot
4、mysql的退出:
exit
SQL的分类
DDL:
数据定义语言,定义数据库对象:数据库,表,列等。主要操作的是表的结构。
CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE
操作数据库
1、Create:创建
创建数据库,判断是否存在:
create database if not exists 数据库名称;
创建数据库,并且指定字符集:
create database 数据库名称 character set 字符集名称;
2、Retrieve:查询
查询所有数据库的名称:
show databases;
查询某个数据库的字符集(查询某个数据库的创建语句):
show create database 数据库名称;
3、Update:修改
修改数据库的字符集:
alter database 数据库名称 character set 字符集名称;
4、Delete:删除
删除数据库:
drop database 数据库名称;
判断数据库是否存在,存在的话删除:
drop database if exists 数据库名称;
5、使用数据库
查询当前正在使用的数据库名称:
select database();
使用数据库:
use 数据库名称;
操作表
1、Create:创建
创建表:
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
...
列名n 数据类型n
);
int: 整数类型:
age int
double: 小数类型:
score double(5,2) 表示最多有五位,保留两位小数
date: 日期,只包含年月日:
datetime: 日期,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
timestamp: 时间戳类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
不赋值或者赋值为NULL,则默认使用当前系统时间自动赋值
varchar: 字符串类型:
name varchar(20) 表示姓名最大20个字符
复制表:create table 表名 like 目标表;
2、Retrieve:查询
查询某个数据库中所有的表名称:
show tables;
查询表结构:
desc 表名;
3、Update:修改
修改表名:
alter table 表名 rename to 新表名;
修改表的字符集:
alter table 表名 character set 字符集;
添加一列:
alter table 表名 add 列名 数据类型;
修改列名,字符集:
alter table 表名 change 原列名 新列名 (新)字符集;
alter table 表名 modify 列名 新字符集
删除列:
alter table 表名 drop 列名;
4、Delete:删除
删除表:
drop table if exists 表名 ;
DCL:
数据控制语言,定义数据库的访问权限和安全级别,及创建用户。
COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE
DML:
数据操作语言,对表中数据进行增删改。INSERT \ DELETE \ UPDATE
1、增加数据:
insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);
默认给所有列添加数据:
insert into 表名 values(值1,值2,...,值n);
除了数字类型,其他类型要用引号(单双都可以)
2、删除数据:
delete from 表名 [where 条件];
删除表,然后创建一个一模一样的空表:
truncate table 表名;
3、修改数据:
update 表名 set 列名1 = 值1, 列名2 = 值2, ... ,列名n = 值n [where 条件];
DQL:对表中数据进行查询
select
字段列表
from
表名
where
条件列表
group by
分组字段
having
分组之后的排序
order by
排序
limit
分页限定
1、基础语法
(1)多个字段查询:
select 字段名1, 字段名2, ... from 表名;
(2)去除重复:
select distinct 字段名1, 字段名2, ... from 表名;
(3)起别名:
select * from t_user as user where id = 1;
2、条件查询where
(1)>、 < 、<=、>=、=、<>(不等于,mysql中也可以用!=)
(2)BRTWEEN...AND
查询student表中年龄在20--30岁之间的人:
select * from student where age between 20 and 30;
(3)IN(集合)
查询student表中年龄为18,22,25岁的:
select * from student where age in(18, 22, 25);
(4)LIKE 模糊查询
* _ 表示单个任意字符
查询student表中姓名中第2个字是三的:
select * from student where name like '_三%';
查询student表中名字是三个字的人:
select * from student where name like'___';
* %表示多个任意字符(或空字符)
查询student表中姓王的人:
select * from student where name like '%王';
查询姓名中包含王的人:
select * from student where name like '%王%';
(5)IS NULL
查询没有英语成绩的人:
select * from student where english id null;
(6)AND 或者 &&
(7)OR 或者 ||
(8)NOT 或者 !
3、排序查询
语法:ORDER BY
ORDER BY 排序字段1 排序方法1,排序字段2 排序方法2 ...
排序方式:
ASC:升序 排序方式不写默认升序
DESC:降序
查询所有学生,按年龄从大到小排序:
select * from student order by age desc;
4、聚合函数:将一列数据作为一个整体,进行纵向的计算(例如求一科的总分)
(1)COUNT:计算个数
计算一共有几个人:
select count(name) from student;
(2)MAX:计算最大值
计算数学成绩的最大值:
select max(math) from student;
(3)MIN:计算最小值
计算数学成绩的最小值:
select min(math) from student;
(4)SUM:求和
求所有人数学成绩的总和:
select sum(math) from student;
(5)AVG:计算平均值
求数学成绩的平均分:
select avg(math) from student;
5、分组查询
语法:group by 分组字段
where和having的区别
where在分组之前进行限定,如果不满足条件则不参与分组
having在分组之后进行限定,如果不满足条件则不会被查询出来
where后不可以跟聚合函数,having可以进行聚合函数的判断
按照性别分组,分别查询男女同学的平均分和男女生的人数:
select sex, avg(math), count(id) from student group by sex;
6、分页查询
语法:limit 开始的索引,每页查询的条数
每页显示3条数据
select * from student limit 0,3;-- 第一页
select * from student limit 3,3;-- 第二页
约束:对表中的数据进行限定,保证数据的正确性、有效性和完整性
主键约束:primary key
主键值一般使用int、bigint、char等,不建议使用varchar。
主键约束字段非空且唯一,一张表只能有一个主键。
创建表时添加主键:
create table stu(id int primary key, name varchar(20));
删除主键约束:
alter table stu drop primary key;
创建完表后添加主键:
alter table stu modify id int primary key;
自动增长:如果某一列是数值类型的,使用 auto_increment 可以实现值的自动增长
在创建表时添加主键约束并实现主键自动增长
create table stu(
id int primary key auto_increment,
name varchar(20)
);
复合主键:
create table stu(
id int,
name varchar(20),
email varchar(20),
primary key(id, name)
);
非空约束:not null
创建表时添加非空约束:
create table stu(id int, name varchar(20) mot null;
删除非空约束:
alter table stu modify name varchar(20);
创建表后添加非空约束:
alter table stu modify name varchar(20) not null;
唯一约束:unique
创建表时添加唯一约束:
create table stu(id int, phone_number varchar(20) unique);
删除唯一约束:
alter table stu drop index phone_number;
创建表后添加唯一约束:
alter table stu modify phone_number varchar(20) unique;
外键约束:foreign key
创建表时添加外键约束
create table 表名(
...
外键列
constraint 外键名(新) foreign key(外键列名称) references 主表名称(主表列名称)
//constraint emp_dept_fk foreign key(dept_id) references dept(id)
);
删除外键:
alter table emp drop foreign key emp_dept_fk;
添加外键,并设置级联更新
alter table emp add constraint emp_dept_fk foreign key(dept_id) references dept(id) on update cascade;
存储引擎
mysql默认的存储引擎是InnoDb
设置存储引擎
create table stu(
id int primary key auto_increment,
name varchar(20)
) engine=InnoDB
常用存储引擎
1、MyISAM
它管理的表有下列主要特征:
(1)使用三个文件表示每个表
(2)格式文件:存储表结构的定义(mytable.frm)
(3)数据文件:存储表行的内容(mytable.MYD)
(4)索引文件:存储表上索引(mytable.MYI)
可被转换为压缩、只读表,来节省空间
2、InnoDB(mysql的默认存储引擎)
支持事务,支持数据库崩溃后自动恢复机制,非常安全。
它管理的表有下列主要特征:
(1)每个InnoDB表在数据库目录中以.frm格式文件表示
(2)InnoDB表空间tablespace被用于存储表的内容
(3)提供一组用来记录事务活动性的日志文件
(4)用COMMIT、SAVEPOINT及ROLLBACK支持事务处理
(5)提供全ACID兼容
(6)在mysql服务器崩溃后提供自动恢复
(7)多版本(MVCC)和行级锁定
(8)支持外键及引用的完整性,包括级联删除和更新
3、Memory
使用Memory存储引擎的表,其数据存储在内存中,且行的长度固定,速度快
它管理的表有下列主要特征:
(1)在数据库目录内,每个表均已.frm格式的文件表示
(2)表数据及索引被存储在内存中(查询快)
(3)表级锁机制
(4)不能包含TEXT和BLOB字段
多表关系
一对多(多对一)
在多的一方建立外键,指向一的一方的主键
多对多
借助中间表,中间表至少包含两个字段,这两个字段作为中间表的外键,分别指向两张主表的主键
一对一
在任意一方添加唯一外键指向另一方的主键
范式
第一范式(1NF):
每一列都是不可分的原子数据项
第二范式(2NF):
在1NF的基础上,非码属性必须完全依赖于码属性(在1NF基础上消除非主属性对主码的部分函数依赖)
第三范式(3NF):
在2NF的基础上,任何非主属性不依赖于其他非主属性(在2NF基础上,消除传递依赖)
多表查询
内连接查询
1、隐式内连接:使用where条件清除无用数据
查询所有员工信息和对应的部门信息:
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
查询员工的名称、员工性别、部门表的名称:
SELECT t1.name,t1.gender,t2.name FROM emp t1, dept t2 WHERE t1.dept_id = t2.id;
2、显式内连接:
select 字段列表 from 表名1 inner join 表名2 on 条件;
查询所有员工信息和对应的部门信息;
SELECT * FROM emp [INNER] JOIN dept ON emp.dept_id = dept.id;
外连接查询
1、左外连接:查询的是左表所有数据以及两表的交集
select 字段列表 from 表1 left [outer] join 表2 on 条件;
2、右外连接:查询的是右表所有数据以及两表的交集
select 字段列表 from 表1 right [outer] join 表2 on 条件;
子查询:查询中嵌套查询
1、子查询结果为单行单列:
子查询可以作为条件,使用运算符去判断,如:<、<=、=、>=、>
查询工资最高的员工信息:
select * from emp where emp.salary = (select max(salary) from emp);
查询员工工资小于平均工资的人:
select * from emp where emp.salary < (select avg(salary) from emp);
2、子查询结果为多行单列:
查询'财务部'和'市场部'所有员工信息:
SELECT * FROM emp WHERE emp.dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
子查询可以作为条件,使用运算符in来判断
3、子查询结果为多行多列:
子查询作为一张虚拟表
查询员工入职日期是2011-11-11之后的员工信息和部门信息:S
ELECT * FROM dept t1, (SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
事务
事务的操作:
1、事务的使用
(1)开启事务:start transaction;
(2)回滚:rollback;
(3)提交:commit;
2、MySQL数据库中事务默认自动提交
(1)事务提交的两种方式
自动提交:
mysql就是自动提交的
一条DML(增删改)语句会自动提交一次事务
手动提交:
需要先开启事务,在提交
(2)修改事务的默认提交方式:
查看事务的默认提交方式:select @@autocommit; 1代表自动提交,0代表手动提交
修改默认提交方式:set @@autocommit = 0;
事务的四大特征:
1、原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
2、持久性:当事务提交或回滚后,数据库会持久性的保存
3、隔离性:多个事务之间,相互独立
4、一致性:事务操作前后,数据总量不变
数据的隔离级别:
1、概念:多个事务之间是隔离的,相互独立的。但是如果多个事务对同一批数据进行操作,就会引发一些问题,设置不同的隔离级别可以解决
2、存在问题:
(1)脏读:一个事务读取到另一个事务中没有提交的数据
(2)不可重复读(虚读):同一个事务中,两次读取到的数据不一致
(3)幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
3、隔离级别:
(1)read uncommitted:读未提交
产生的问题:脏读,不可重复读,幻读
(2)read committed:读已提交
产生的问题:不可重复读,幻读
(3)repeatable read:可重复读
产生的问题:幻读
(4)serializable:串行化
可以解决所有的问题
注意:隔离级别从上往下越来越高,但是效率越来越低
4、查询隔离级别:
select @@tx_isolation;
5、设置隔离界别:
set global transaction isolation level 级别字符串;
索引
mysql查询方式有两种
全表扫描
根据索引检索
什么是索引?
索引相当于书的目录,是为了缩小扫描范围从而加快数据库查询速度而设计的一种机制
索引是在数据库表的字段上添加的,可以提高查询效率,一张表的一个或者多个字段都可以添加索引
使用索引的优缺点
优点:
1、可以提高数据检索效率,降低数据库的IO成本
2、通过索引列对数据进行排序效率会提升很多
缺点:
1、索引会占据磁盘空间
2、索引会降低更新表的效率。增删改表时,mysql不仅要保存数据库,还要保存或者更新对应的索引文件。
在那些列上添加索引?
1、在经常需要搜索的列上,可以加快检索效率
2、在主键列上,强制该列的唯一性和组织表中数据的排列结构。
3、在常用的连接列上,在外键列上添加索引可以加快连接速度。
4、在经常需要范围搜索(< = > between in)的列上。
5、在经常需要排序的列上。索引已经排序,这样查询可以利用索引的排序,加快排序查询时间。
6、在经常使用where的字段上,可以加快条件的判断速度。
7、要在很少使用增删改的字段上添加
创建与删除索引
//给emp表中的ename字段添加索引,起别名为emp_ename_index
create index emp_ename_index on emp(ename);
//将emp表中的emp_ename_index索引删除
drop index emp_ename_index on emp;
索引失效的情况
1、模糊查询时以%开头会使索引失效。
2、使用or时,or两边的字段都要使用索引,不然会失效。
3、使用复合索引时,要使用最左侧的列进行查找,不然索引会失效。
4、对索引列进行运算会使索引失效。
5、普通索引使用!=会使索引失效,主键索引不会失效。
6、索引列使用函数会使索引失效。