Mysql
文章目录
前言
开始之前我们首先要弄清楚三个概念,数据库,数据库管理系统,SQL;
什么是数据库?
数据库:
- 英文单词DataBase,简称DB。按照一定格式存储数据的一些文件的组合。
- 顾名思义:存储数据的仓库,实际上就是一堆文件。这些文件中存储了具有特定格式的数据。
什么是数据库管理系统?
数据库管理系统:
-
英文单词DataBaseManagement,简称DBMS。数据库管理系统是专门用来管理数据库中数据的,数据库管理系统可以对数据库当中的数据进行增删改查。
-
常见的数据库管理系统:
MySQL、Oracle、MS SqlServer、DB2、sybase等…
什么是SQL?
- SQL:结构化查询语言
- 程序员需要学习SQL语句,程序员通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中数据的增删改查操作。
- SQL是一套标准,程序员主要学习的就是SQL语句,这个SQL在mysql中可以使用,同时在Oracle中也可以使用,在DB2中也可以使用。
三者间的关系是什么?
- DBMS–执行–> SQL --操作–> DB
- 先安装数据库管理系统MySQL,然后学习SQL语句怎么写,编写SQL语句之后,DBMS对SQL语句进行执行,最终来完成数据库的数据管理。
一、数据库概述
数据库的基本单元
- 数据库当中最基本的单元是表:table 数据库当中是以表格的形式表示数据的。
- 任何一张表都有行和列:
行(row):被称为数据/记录。
列(column):被称为字段。
每一个字段都有:字段名、数据类型、约束等属性。
字段名可以理解,是一个普通的名字,见名知意就行。
数据类型:字符串,数字,日期等,
约束也有很多,其中一个叫做唯一性约束,这种约束添加之后,该字段中的数据不能重复。后面会进行详细讲解
SQL语句的分类
- DQL:数据查询语言(凡是带有select关键字的都是查询语句)
select… - DML:数据操作语言(凡是对表当中的数据进行增删改的都是DML)
insert 增
delete 删
update 改
这个主要是操作表中的数据data。 - DDL:数据定义语言,凡是带有create、drop、alter的都是DDL。DDL主要操作的是表的结构。不是表中的数据。
create:新建,等同于增
drop:删除
alter:修改
这个增删改和DML不同,这个主要是对表结构进行操作。 - TCL:事务控制语言
包括:
事务提交:commit;
事务回滚:rollback; - DCL:是数据控制语言。
例如:授权grant、撤销权限revoke…
演示表,以下无特殊说明用以下三张表演示
二、常用命令
-
查看表结构
desc 表名;
-
查看mysql版本
select version();
-
创建数据库
create database 数据库名称;
-
使用数据库
use 数据库名称
-
终止一条语句
如果想要终止一条正在编写的语句,可键入\c。
-
退出 mysql
可使用\q、QUIT 或 EXIT: -
查看表的创建语句
show create table 表名;
三、简单查询
- 简单的查询
查询一个字段:select 列名 from 表名;
查询多个字段:select 列名,列名 from 表名;
查询全部字段:select * from 表名;select 所有列名 from 表名;
字段也可以进行算术运算
比如计算工资*12:select sal *12 from emp;
也可以给字段起别名:select sal *12 as ysal from emp;as可以省略
- 条件查询
条件查询需要用到 where 语句,where 必须放到 from 语句表的后面
select 列名 from 表名 where …
首先来了解一下运算符,mysql支持一下运算符
一下随意举例其他依样画葫芦。
等号操作符
<>和!=操作符
between…and …
and 的优先级大于or
like模糊查询
like 支持%和_匹配,下划线表示一个字符,%表示任意个数字符
Like 中的表达式必须放到单引号中|双引号中:
查询名字包含字母a的员工
查询名字第二个字母是a的
四、排序(order by)
- 单一字段排序
desc 表示降序;asc表示升序,如果不指定默认是升序
按照员工工资降序select * from emp order by sal desc;
- 多个字段排序
如果出现两个排序字段值相等,这时就可以采用多个字段排序
select * from emp order by sal,ename;先按工资排序,如果工资一样,就按名字的字典序排序
select * from emp order by sal asc,ename desc;按照工资的升序排,如果工资一样,就按名字的字典序降序排
- 使用字段的位置来排序(不建议使用)
select * from emp order by 6;
按照第六个字段排序,记住MySQL下标都是从1开始
五、数据处理函数/单行处理函数
- lower函数(把字母都转为小写)
- upper函数(将字母转换为大写)
- substr 截取字符串(substr(ename,1,1))
- length函数(取长度)
查询名字长度为5的员工
select * from emp where length(ename)=5;
- trim函数(去除首尾空格)
- str_to_date(日期转字符串)
str_to_date 可以将字符串转换成日期,具体格式 str_to_date (字符串,匹配格式)
select * from emp where HIREDATE=str_to_date(‘02-20-1981’,’%m-%d-%Y’);
- date_format(日期转字符串)
select empno, ename, date_format(hiredate, ‘%Y-%m-%d %H:%i:%s’) as hiredate from emp;
这里说一下另一个函数now() 获得当前时间。
还有时间的表达
%Y:代表 4 位的年份
%y:代表 2 位的年份
%m:代表月, 格式为(01……12)
%c:代表月, 格式为(1……12)
%H:代表小时,格式为(00……23)
%h: 代表小时,格式为(01……12)
%i: 代表分钟, 格式为(00……59)
%r:代表 时间,格式为 12 小时(hh:mm:ss [AP]M)
%T:代表 时间,格式为 24 小时(hh:mm:ss)
%S:代表 秒,格式为(00……59)
%s:代表 秒,格式为(00……59)
- format函数(设置千分位)
select empno, ename, Format(sal, 0) from emp;
- round函数(四舍五入)
0表示在个位四舍五入。依次推 1表示保留小数点后一位,-1表示保留到十位。
select round(sal,-3) from emp;在千位四舍五入
- rand函数(生成随机数)
select * from emp order by rand() limit 2;随机抽取两条记录
- case … when … then ……else …end
如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%
select e.,sal ,case job when ‘salesman’ then sal1.1 when ‘clerk’ then sal*1.2 else sal end as new_sal from emp e;
- ifnull函数
如果 comm 为 null 就替换为 0
六、分组查询、分组函数/聚合函数/多行处理函数
-
分组查询主要涉及到两个子句,分别是:group by 和 having
表按照group by后面字段进行分组
having对分组数据进行筛选 -
分组函数
count 取得记录数
sum 求和
avg 取平均
max 取最大的数
min 取最小的数 -
首先讲编译器编译顺序
①首先执行 where 语句过滤原始数据
②执行 group by 进行分组
③执行 having 对分组数据进行操作
④执行 select 选出数据
⑤执行 order by 排序
注意:分组函数自动忽略空值,不需要手动的加 where 条件排除空值。
注意:分组函数不能直接使用在 where 关键字后面。因为编译器先进行where再分组,在where后面使用分组函数会报错
注意:select查询字段只能是分组的字段或者分组函数
-
count函数(取得记录数)
查询各部门员工数量
-
sum 求和
求各部门员工工资的总和
-
avg 取平均
求各部门的平均工资
-
max 取最大的数
求各部门最高工资
-
min 取最小的数
求各部门最低工资
-
组合使用
求平均工资大于2500的部门
注意:能用where先进行判断的先用where,因为where效率较高
七、连接查询
-
连接查询:也可以叫跨表查询,需要关联多个表进行查
连接分类: -
内链接
表 1 inner join 表 2 on 关联条件
做连接查询的时候一定要写上关联条件
inner 可以省略
查询各个员工所属部门名称 -
外连接
①、左外连接
表 1 left outer join 表 2 on 关联条件
做连接查询的时候一定要写上关联条件
outer 可以省略
②、右外连接
表 1 right outer join 表 2 on 关联条件
做连接查询的时候一定要写上关联条件
outer 可以省略
③、左外连接(左连接)和右外连接(右连接)的区别:
左连接以左面的表为准和右边的表比较,和左表相等的不相等都会显示出来,右表符合条件的显示,不符合条件的不显示,右连接恰恰相反
查询各个员工对应上级
加入不使用外连接King将被忽略
八、子查询
- 在 where 语句中使用子查询,也就是在 where 语句中加入 select 语句
取得工资大于平均薪水的员
- 在 from 语句中使用子查询,可以将该子查询看做一张表
查询各个部门的平均薪水所属等级,需要显示部门编号,平均薪水,等级编号
- 在 select 语句中使用子查询
求员工所属的部门名称
九、union、limit 的使用
- union
用or效果也是一样的 - limit
取工资前五的员工
工资第四的员工
注意:limit是从0开始数的
十、表
创建表
- 语法
create table tableName(
字段名 数据类型(长度),
…………………
字段名 数据类型(长度),
);
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3)
)
- 常用数据类型
- 增加/删除/修改表结构(不常用)
采用 alter table 来增加/删除/修改表结构,不影响表中的数据
增:alter table 表名 add 字段名 数据类型(长度);
改:alter table 表名 modify 字段名 数据类型(长度);
删:alter table 表名 drop 字段名;
添加、修改和删(对数据进行操作)
-
添加操作insert
语法:insert into 表名(字段,。。。。) values(值,…………)
注意:未说明值,默认值为null -
修改操作update
语法:update 表名 set 字段名称 =需要修改的值 , 字段名称 =需要修改的值 where ……
注意where后面必须写,不然将修改全部数据
-
删除操作delete
语法:delete from 表名 where… …
十一、约束
创建表加入约束
常见的约束
a) 非空约束,not null
b) 唯一约束,unique
c) 主键约束,primary key
d) 外键约束,foreign key
e) 自定义检查约束,check(不建议使用)(在 mysql 中现在还不支持)
非空约束,not null
非空约束,针对某个字段设置其值不为空,如:学生的姓名不能为空
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30),
classes_id int(3)
)
insert into t_student(student_id, birthday, email, classes_id)
values
(1002, '1988-01-01', 'qqq@163.com', 10)
以上错误为加入的学生姓名为空。
唯一约束,unique
唯一性约束,它可以使某个字段的值不能重复,如:email 不能重复:
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) unique,
classes_id int(3)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
以上插入了重复的 email,所以出现了“违反唯一约束错误”,所以 unique 起作用了
同样可以为唯一约束起个约束名
- 我们可以查看一下约束
mysql> use information_schema;
mysql> select * from table_constraints where table_name = 't_student';
关于约束名称可以到 table_constraints 中查询
以上约束的名称我们也可以自定义。
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3) ,
constraint email_unique unique(email)/*表级约束*/
)
主键约束,primary key
每个表应该具有主键,主键可以标识记录的唯一性,主键分为单一主键和复合(联合)主键,单一主键是由一个字段构成的,复合(联合)主键是由多个字段构成的
drop table if exists t_student;
create table t_student()
student_id int(10) primary key,/*列级约束*/
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
向以上表中加入学号为 1001 的两条记录,出现如下错误,因为加入了主键约束
我们也可以通过表级约束为约束起个名称:
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3),
CONSTRAINT p_id PRIMARY key (student_id)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
外键约束,foreign key
外键主要是维护表之间的关系的,主要是为了保证参照完整性,如果表中的某个字段为外键字段,那么该字段的值必须来源于参照的表的主键,如:emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。
建立学生和班级表之间的连接
首先建立班级表 t_classes
drop table if exists t_classes;
create table t_classes(
classes_id int(3),
classes_name varchar(40),
constraint pk_classes_id primary key(classes_id)
)
在 t_student 中加入外键约束
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3),
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
)
向 t_student 中加入数据
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
出现错误,因为在班级表中不存在班级编号为 10 班级,外键约束起到了作用
存在外键的表就是子表,参照的表就是父表,所以存在一个父子关系,也就是主从关系,主表就是班级表,从表就是
学生表
以上成功的插入了学生信息,当时 classes_id 没有值,这样会影响参照完整性,所以我们建议将外键字段设置为非空
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3) not null,
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id))
insert into t_student(student_id, student_name, sex, birthday, email, cla
sses_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);
再次插入班级编号为 null 的数据
添加数据到班级表,添加数据到学生表,删除班级数据,将会出现如下错误:
insert into t_classes (classes_id,classes_name) values (10,'366');
insert into t_student(
student_id, student_name, sex, birthday, email, classes_id
) values(
1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10
)
mysql> update t_classes set classes_id = 20 where classes_name = '366';
因为子表(t_student)存在一个外键 classes_id,它参照了父表(t_classes)中的主键,所以
先删除子表中的引用记录,再修改父表中的数据。
我们也可以采取以下措施 级联更新。
mysql> delete from t_classes where classes_id = 10;
因为子表(t_student)存在一个外键 classes_id,它参照了父表(t_classes)中的主键,所以
先删除父表,那么将会影响子表的参照完整性,所以正确的做法是,先删除子表中的数据,
再删除父表中的数据,采用 drop table 也不行,必须先 drop 子表,再 drop 父表
我们也可以采取以下措施 级联删除。
增加/删除/修改表约束
删除约束
将 t_student
- 删除外键约束:alter table 表名 drop foreign key 外键(区分大小写);
alter table t_student drop foreign key fk_classes_id; - 删除主键约束:alter table 表名 drop primary key ;
alter table t_student drop primary key; - 删除约束约束:alter table 表名 drop key 约束名称 ;
alter table t drop key uk;
添加约束
将 t_student 中的约束
- 添加外键约束:alter table 从表 add constraint 约束名称 foreign key 从表(外键字段) references 主表(主键字段);
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references
t_classes(classes_id); - 添加主键约束:alter table 表 add constraint 约束名称 primary key 表(主键字段);
alter table t_student add constraint pk primary key(student_id); - 添加唯一性约束:alter table 表 add constraint 约束名称 unique 表(字段);73 / 103
alter table t_student add constraint uk unique(email);
修改约束,其实就是修改字段
alter table t_student modify student_name varchar(30) unique;
mysql 对有些约束的修改时不支持,所以我们可以先删除,再添加
十二、事务
- 事务可以保证多个操作原子性,要么全成功,要么全失败。对于数据库来说事务保证批量的 DML 要么全成功,要么全失败。
- 事务具有四个特征 ACID
a) 原子性(Atomicity)
整个事务中的所有操作,必须作为一个单元全部完成(或全部取消)。
b) 一致性(Consistency)
在事务开始之前与结束之后,数据库都保持一致状态。
c) 隔离性(Isolation)
一个事务不会影响其他事务的运行。
d) 持久性(Durability)
在事务完成以后,该事务对数据库所作的更改将持久地保存在数据库之中,并不会被回滚。 - 事务中存在一些概念:
a) 事务(Transaction):一批操作(一组 DML)
b) 开启事务(Start Transaction)
c) 回滚事务(rollback)
d) 提交事务(commit)
e) SET AUTOCOMMIT:禁用或启用事务的自动提交模式
事务的隔离级别
隔离级别
• 事务的隔离级别决定了事务之间可见的级别。
• 当多个客户端并发地访问同一个表时,可能出现下面的一致性问题:
1、 脏读取(Dirty Read)
一个事务开始读取了某行数据,但是另外一个事务已经更新了此数据但没有能够及时提交,这就出现了脏读取。
2、 不可重复读(Non-repeatable Read)
在同一个事务中,同一个读操作对同一个数据的前后两次读取产生了不同的结果,这就是不可重复读。
3、 幻像读(Phantom Read)
幻像读是指在同一个事务中以前没有的行,由于其他事务的提交而出现的新行。
四个隔离级别
• InnoDB 实现了四个隔离级别,用以控制事务所做的修改,并将修改通告至其它并发的事务:
– 读未提交(READ UMCOMMITTED)
允许一个事务可以看到其他事务未提交的修改。
– 读已提交(READ COMMITTED)
允许一个事务只能看到其他事务已经提交的修改,未提交的修改是不可见的。
– 可重复读(REPEATABLE READ)
确保如果在一个事务中执行两次相同的 SELECT 语句,都能得到相同的结果,不管其他事务是否提交这些修改。(银
行总账)
该隔离级别为 InnoDB 的缺省设置。
– 串行化(SERIALIZABLE) 【序列化】
将一个事务与其他事务完全地隔离。
数据库备份与恢复
数据库备份
- 备份一个数据库
mysqldump -uroot -p 数据库名字 > 备份位置 - 备份全部数据库
mysqldump -uroot -p --all-database > 备份位置
数据库恢复
-
未登录数据库
mysql -uroot -p 数据库名字 < 备份文件位置
注:数据库已存在 -
登录数据库
source 备份文件位置
注:若文件是整个数据库,则不需要use数据库,若是单个数据库则需use数据库