sql
一:数据库的基本概念:
1:数据库(DataBase)简称DB
2:数据库就是存储和管理数据的仓库
3:数据库的特点:(1)持久化存储数据,其实数据库就是一个文件系统
(2)方便存储和管理数据
(3)关系型数据库都使用统一的方式操作数据 -- SQL
二:SQL基本概念
SQL(Structured Query Language) 是结构化查询语言,这是一种所有关系型数据库的查询规范,不同的数据库都支持。
关系型数据库的优势:
1)复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
2)事务支持使得对于安全性能很高的数据访问要求得以实现。对于这两类数据库,对方的优势就是自己的弱势,反之亦然。
三:SQL分类:
1. DDL: Data Definition Language 数据定义语言,如:建库,建表
(1)-- 创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名字
(2)-- 创建数据库并指定编码
CREATE DATABASE IF NOT EXISTS 数据库名字 CHARACTER SET 编码类型
(3)-- 查看所有数据库的名字
SHOW DATABASES
(4)-- 查看数据库创建语句
SHOW CREATE DATABASE 数据库名字
(5)-- 更改数据库编码
ALTER DATABASE 数据库名字 CHARACTER SET 编码类型
(6)-- 删除数据库
DROP DATABASE IF EXISTS 数据库名字
(7)-- 选择要使用的数据库
USE 数据库名字
(8)-- 查看当前数据库中的所有表
SHOW TABLES
(9)-- 查看当前数据库中的表结构
DESC 表名
(10)-- 查看建表语句
SHOW CREATE TABLE 表名
(11)-- 创建表
CREATE TABLE student (
列名 数据类型
)
(12)-- 复制表
CREATE TABLE 新表名字 LIKE 被复制的表名字
(13)-- 删除表
DROP TABLE IF EXISTS 表名
(14)-- 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名
(15)-- 修改表的字符集
ALTER TABLE 表名 CHARACTER SET 编码类型
(16)-- 在表中添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
(17)-- 修改某一列的列名和数据类型
ALTER TABLE 表名 CHANGE 旧类名 新类名 数据类型;
(18)-- 修改一列的数据类型
ALTER TABLE 表名 MODIFY 列名 数据类型
(19)-- 删除一列
ALTER TABLE 表名 DROP 列名;
2. DML: Data Manipulation Language 数据操作语言,如对表的数据记录操作增删改
(1)在表中添加一个数据
INSERT INTO 表名 (列名1,列名2.。。) VALUE(列1的值,列2的值)
(2)删除表中的数据
DELETE FROM 表名 WHERE 条件
(3)删除表中的所有数据
TRUNCATE TABLE 表名
(4)修改一列的值
UPDATE 表名 SET 列名=值 WHERE 条件
3. DQL: Data Query Language·数据查询语言,如:对表中数据的查询操作
语法:select + 字段列表
from + 表名列表
where + 条件列表
having + 分组之后的条件
order by 排序
limit 分页操作
distinct 去重
as 起别名
运算符:<> 不等于
!= 不等于
< 小于
> 大于
= 等于
<= 小于等于
>= 大于等于
between and >=小值 并且<=大值
in 集合
like 模糊查询
is null 是null的值
and 并且
&& 并且
or 或者
|| 或者
not 不
占位符:_ 单个字符
% 多个字符
排序:order by 排序字段 ASC 升序(默认)
order by 排序字段 DESC 降序
聚合函数:count(语句) 计算个数
max(语句) 计算最大值
min(语句) 计算最小值
sum(语句) 计算和
avg(语句) 计算平均值
分组:group by 分组字段
分页:limit 开始的索引,每页查询的条数
4. DCL: Data Control Language 数据控制语言,如:对用户权限的设置
(1)创建用户语法:create user 用户名 @ 主机名 identified by 密码 主机名可以为%(任意主机)
(2)给用户授权语法: grant 权限1,权限2.... on 数据库名 .表名 to 用户名 @ 主机名
权限:select、update、insert等等
数据库名和表名可以用*代替,表示全部的
(3)撤销授权语句:revoke 权限1,权限2,... on 数据库名.表名 from 用户名 @ 主机名
(4)查看权限语法:show grants for 用户名 @ 主机名
(5)删除用户语法:drop user 用户名 @ 主机名
(6)修改管理员密码语法:mysqladmin -u旧密码 -p password 新密码 (在未登录mysql的情况下操作)
(7)修改普通用户密码语法:set password for 用户名 @ 主机名 = password(新密码) (需要在登录mysql的情况下操作,新密码需要加单引号)
四:MySQL的数据类型
整数:tinyInt 微整形
smallint 小整形
mediumint 中整形
int 整形
小数:float 单精度浮点数
double 双精度浮点型
日期:time 表示时间类型
date 表示日期
datetime 同时可以表示日期和时间
timestamp 时间戳
字符串:char 固定长度的字符串
varchar() 可变长度的字符串
大文本:tinytext 允许长度0~255字节
text 允许长度0~65535字节
mediumt 允许长度0~167772150字节
longtext 允许长度0~4294967295字节
五:数据库表的约束
约束的作用:
对表的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法添加到表内,约束在创建表时添加比较合适。
约束的种类:
主键 primary key (主键只能有一个,可以是单列或多列)
唯一 unique
非空 not null
外键 foreign key
检查约束 check(mysql不支持)
语法:
删除主键 alter table 表名 drop primary key
创表后增加主键 alter table 表名 add primary key(列名)
建表时给主键设置自动增长初始值 在建表的括号后面加上 auto_increment=初始值
建表后给主键设置自动增长初始值 altter table 表名 auto_increment=初始值
建表时设置默认值 列名 数据类型 DEFAULT 默认值
添加外键: (1)建表时加外键
constraint 外键约束名 foreign key (外键字段名 ) references 主表名(主键)
(2)已有表增加外键
alter table 从表 add constraint 外键约束名称 foreign key (外键字段名) references 主表 (主键字段名)
删除外键:later table 表名 drop foreign key 外键名
级联:正常情况下主表中的主键发生变动时从表的外键不会跟着一起变动,这样的效果不是想要的,所以就有了级联,添加级联的2个语法后主表主键变动从表的外键也会一起变
on update cascade 主表的主键修改从表的外键也一起修改
on delete cascade 主表删除列时从表也会发生变动
在从表添加外键时将级联语句加到后面即可
六:表与表之间的关系
一对多:比如部门和员工
一对一:比如员工和简历表
多对多:比如学生和选课
多对多需要建立中间表来联系,中间表至少有2个字段,分别作为外键指向各自的主键
七:三大范式(表的建立都应该遵循这三大范式)
1NF:原子性,每一列都应该不可拆分
2NF:不产生局部依赖,一张表只做一件事
3NF:表上每一列都应该直接的依赖主键,而不是通过其他列间接的依赖主键
八:多表查询
1:内连接查询(只有条件成立的数据才会展示出来)
(1):隐式内连接查询
select 列名 from 表一 ,表二 where 条件
(2):显示内连接
select 列名 from 表一 join 表二 on 条件
2:外连接查询
(1):左外连接查询(左连接左边的表所有数据都展示,右表的数据符合条件才展示出来)
select 列名 from 表一 left join 表二 on 条件
(2):右外连接查询(右连接右边的表所有数据都展示,左表的数据符合条件才展示出来)
select 列名 from 表一 right join 表二 on 条件
九:事务
1:什么是事务?
事务是一个整体,如果所有的sql语句都执行成功,那么提交,否则回滚。
2:事务操作语句:
(1)start transaction 开启事务
(2)commit 提交
(3)rollback 回滚
(4)回滚点:
设置回滚点名字:savepoint 回滚名字
回滚:rollback to 回滚名字
3:自动提交事务
mysql默认每一条单独的DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完自动提交。mysql默认开始自动提交事务。
4:查看当期是否开启自动提交事务
select @@autocommit -- @@表示全局变量 -- 0表示关闭,1表示开启
5:set @@autocommit=0 -- 设置是否自动提交事务
6:事务原理:
事务开启后,所有的操作都会临时保存到事务日志里,事务日志只有得到commit命令才会同步到数据库里,其他任何情况都会清空事务日志
7:事务特性:
(1):原子性:每个事务都是一个整体,不可拆分,十五中的所有语句要么都执行成功,要么都执行失败
(2):一致性:事务在执行数据库的状态与执行后数据库的状态保持一致。
(3):隔离性:事务与事务之间不应该相互影响,执行时保持隔离的状态。
(4):一旦事务执行成功,对数据的修改是持久的,就算关机,也是保存下来的
8:事务在操作时的理想状态:所有的事务之间保持隔离,相互不影响。因为并发操作,多个用户同时访问一个数据,可能引发并发访问的问题:
(1)脏读 一个事务读取到了另一个事务中尚未提交的数据
(2)不可重复读 一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据时一致的,这是事务update时引发的问题
(3)幻读 一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致的,这是insert和delete时引发的问题。
四种隔离级别:
(1)读未提交 隔离级别:read uncommitted 可脏读 不可重复读 可幻读
(2)读已提交 隔离级别:read committed 不可脏读 不可重复读 可幻读
(3)可重复读 隔离级别:repeatable read 不可脏读 可重复读 可幻读
(4)串行化 隔离级别:serializable 不可脏读 可重复读 不可幻读
隔离级别越高,性能越差,安全性越高
查询全局事务隔离级别:select @@tx_isolation
设置隔离级别,需要退出重新登陆才能看到隔离级别的变化:set global transaction isolation level 级别字符串