MySQL-基础篇
数据库概念
来源黑马
数据模型
关系型数据库
概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库
而所谓的二维表,是指由行和列组成的表。MySQL、Oracle、DB2、SQLServer都属于关系型数据库,都是基于二维表存储数据的。
特点:使用表存储数据,格式统一,便于维护;使用SQL语言操作,标准统一,使用方便
数据模型
- 我们可以通过MySQL客户端连接数据库管理系统DBMS,然后通过DBMS操作数据库。
- 可以使用SQL语句,通过数据库管理系统操作数据库,以及操作数据库中的表结构及数据。
- 一个数据库服务器中可以创建多个数据库,一个数据库中也可以包含多张表,而一张表中又可以包含多行记录
SQL
全称 Structured Query Language,结构化查询语言。操作关系型数据库的编程语言,定义了
一套操作关系型数据库统一标准 。
SQL通用语言
- SQL语句可以单行或多行书写,以分号结尾
- SQL语句可以使用空格、缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释:
- 单行注释:– 注释内容 或 # 注释内容
- 多行注释:/* 注释内容 */
SQL分类
SQL语句,根据其功能,主要分为四类:DDL、DML、DQL、DCL。
DDL
Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段) 。
数据库操作
查询所有数据库
show databases ;
查询当前数据库
select database() ;
创建数据库
`create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序
规则 ] ; `
在同一个数据库中不能创建两个名称相同的数据库,否则会报错
可以通过IF NOT EXISTS
来解决这个问题
CREATE DATEBASE IF NOT EXISTS itcast;
创建数据库并指定字符集
CREATE DATEBASE itheima DEFAULT CHARSET utf8mb4;
删除数据库
drop database [ if exists ] 数据库名 ;
切换数据库
use 数据库名 ;
表操作
查询创建
查询当前数据库所有表
show tables;
查询指定表结构
desc 表名
查询指定表的建表语句
show create table 表名 ;
创建表结构
1 | CREATE TABLE 表名( |
数据类型
主要分为三类:数值类型、字符串类型、日期时间类型。
数值类型
字符串类型
日期类型
修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
修改数据类型
修改字段名和字段类型
删除字段
删除表名
删除
删除表
删除指定表,并重新创建
DML
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。
- 添加字段INSERT
- 修改字段UPDATE
- 删除字段DELETE
添加数据
给指定字段添加数据
修改数据
删除数据
DQL
基本语法
基础查询
条件查询
聚合函数
分组查询
排序查询
分页查询
案例
执行顺序
DCL
管理用户
查询用户
select * from mysql.user
Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
删除用户
DROP USER '用户名'@'主机名' ;
注意事项:
- 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
- 主机名可以使用 % 通配。
- 这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用
权限控制
查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意事项:
- 多个权限之间,使用逗号分隔
- 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。
函数
字符串函数
数值函数
日期函数
流程函数
约束
概述
约束条件 | 说明 |
---|---|
PRIMARY KEY | 主键约束用于唯一标识对应的记录 |
FOREIGN KEY | 外键约束 |
NOT NULL | 非空约束 |
UNIQUE | 唯一性约束 |
DEFAULT | 默认值约束,用于设置字段的默认值 |
主键约束
主键约束(primary key)用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为null,主键的值是不可以重复的。
一张表最大只能有一个主键,但可以是复合主键
字段名 数据类型 primary key;
外键约束
介绍
外键:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
外键约束即FOREIGN KEY常用于多张表之间的约束
用于定义主表和从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是null
语法
1 | -- 在创建数据表时语法如下: |
删除、更新
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
非空约束
非空约束(NOT NULL)指的是字段的值不能为空
字段名 数据类型 NOT NULL
1 | create table student02( |
默认值约束
默认值约束即DEFAULT用于给数据表中的字段指定默认值,即当在表中插入一条新记录时若未给该字段赋值,那么,数据库系统会自动为这个字段插人默认值
字段名 数据类型 DEFAULT 默认值;
1 | create table student03( |
唯一性约束
唯一性约束即UNIQUE用于保证数据表中字段的唯一性,即表中字段的值不能重复出现
1 | create table student04( |
使用细节:
- 如果没有指定not null,则unique字段可以有多个null
- 一张表可以有多个unique字段
Check约束
用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间,如果不在该范围内就会提示出错
注:mysql5.7不支持check,只做语法校验,但不会生效;mysql8.0支持
1 | CREATE TABLE t23 ( |
多表查询
多表关系
多表查询概述
内连接
内连接查询的是两张表交集部分的数据。是不含null的
隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
显示内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
inner可以不写
外连接
外连接分为两种,分别是:左外连接 和 右外连接。
左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。
自连接
自连接查询
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。
联合查询
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
1 | SELECT 字段列表 |
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
示例:
1 | select * |
- union all查询出来的结果,仅仅进行简单的合并,并未去重
- union 联合查询,会对查询出来的结果进行去重处理
子查询
三表联查
案例
事务
事务简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。典型案例是银行转账
默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐式的提交事务。
事务操作
使用的表:
1 | DROP TABLE if EXISTS account; |
未控制事务
控制事务一
查看事务提交方式
SELECT @@autocommit ;
设置事务提交方式:1为自动提交 0为手动提交
SET @@autocommit = 0 ;
提交事务
COMMIT
回滚事务
ROLLBACK
上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
控制事务二
开启事务
START TRANSACTION 或 BEGIN ;
提交事务
COMMIT
回滚事务
ROLLBACK
事务四大特性ACID
- 原子性Atomicity:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性Consistency:事务完成时,必须使所有的数据都保持一致状态
- 隔离性Isolation:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性Durability:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
并发事务问题
脏读:一个事务读到另外一个事务还没有提交的数据
不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在, 好像出现了幻影
事务隔离级别
为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable | × | × | √ |
Serializable | × | × | × |
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
1 | SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } |
事务隔离级别越高,数据越安全,但是性能越低