基本内容:
内容 | 具体 |
---|---|
MySQL入门 | 基础的增删改查和数据聚合基本操作等 |
SQL登录: cmd–> “mysql -uroot -proot”
SQL规范:
- 关键字大写,数据库名、数据表名、列名小写
- 写成多行,增加可读性
- 注释:单行注释:#注释1 --注释2 多行注释:/* 注释3 */
一、SQL语言分类:
(一)、DDl(数据定义语言):
定义数据库的对象,如数据表、视图、索引等。
-
数据库操作:
-
创建数据库
CREATE DATABASE <database_name> USE <database_name>
-
查看数据库
SHOW database
-
修改数据库
ALTER DATABASE <database_name> CHARACTER SET 字符集 [ COLLATE 排序规则]
-
删除数据库
DROP DATABASE <database_name>
-
-
操作表
-
数据类型: 浮点数float(M,D) M-存储的共有M位数 D-小数点后有D位数
定点数decimal(M,D) 相较于浮点数更准确,多用于计算
date 1000-01-01到9999-12-31
datatime 1000-01-01 00:00:00 到9999-12-31 23:59:59
timestamp 1970-01-01 00:00:01.000000 到2038-1-19 03:14:07.999999 存储的时间与时区有关
-
创建表
CREATE TABLE 表名 ( 字段名1 数据类型 约束, 字段名2 数据类型 约束, ... 字段名n 数据类型 约束, ); * 查看表 ```sql SHOW tables #查看所有表 DESC 表名 #查看表结构
-
删除表
DROP table 表名
-
(二)、DML (数据操作语言):
对表中数据进行增、删、改操作
-
插入
INSERT INTO 表名
-
蠕虫复制
CREATE TABLE 表2 LIKE 表1 #创建一张表结构和表1一样的表2 INSERT INTO 表2 SELECT * FROM 表1 #将表1数据追加到表2
-
更新表记录
UPDATE 表名 SET 字段1=值1
-
删除记录
DELETE FROM 表名
(三)、DQL(数据查询语言):
查询数据库中的数据。
SELECT [ DISTINCT ] 字段1 AS 别名 FROM 表名 #DISTINCT去重
- 条件查询(在where后面):
比较运算,逻辑运算(&&、|| 、!),指定范围查询(in,between and(闭区间))、模糊查询(like):通配符%0代表个或多个、_代表1个,为空查询(is null)
- 排序:
SELECT * FROM 表名 ORDER BY 排序规则 [DESC|ASC]
- 分组:
SELECT 字段 FROM 表名 GROUP BY 分组字段 [HAVING 条件]
注:对数据进行过滤时机不同,having是在分组后,where是在分组前;having语句可以用聚合函数。
- 单行函数: 行计算
- 数值函数
函数 | abs() | ceil(x) | floor(x) | rand() | round(x) |
---|---|---|---|---|---|
说明 | 绝对值 | >=x的最小整数(向上取整) | <=x的最大整数(向下取整) | 0-1间的随机数 | 离x最近的整数(四舍五入) |
- 字符串函数
函数 | concat(s1…sn) | locate(s1,1) | lower(s) | upper(s) | replace(s,s1,s2) | substr(s,b,len) | trim(s) | reverse(s) |
---|---|---|---|---|---|---|---|---|
说明 | 多个字符串合并 | 查询子串位置(起始) | 转为小写字母 | 转为大写字母 | 用s2代替s中的s1 | 从s的b位置截取长度为len的字符串 | 去除字符串s开始和结尾处的空格 | 反转字符串 |
- 时间日期函数
函数 | curdate() | sysdate() | curtime() | month(date) | week(date) | day(date) | date_add(date,INTERVAL expr type) |
---|---|---|---|---|---|---|---|
说明 | 年月日 | 实时时间 年月日时分秒 | 时分秒 | 返回日期的月份 | 返回日期是一年中的第几个星期 | 返回日期中的日值 | date是日期表达式,expr时间间隔,type1 |
- 流程控制函数
函数 | if(con,e1,e2) | ifnull(e1,e2) |
---|---|---|
说明 | con条件为true时返回e1,为false返回e2 | e1不为null返回e1,为null时返回e2 |
- 聚合函数: 列计算
函数 | count() | sum() | avg() | max()和min() |
---|---|---|---|---|
说明 | 统计个数,忽略为null的行 | 数值和 | 平均分 | 最大值和最小值 |
- limit: 限制查询条数
SELECT 字段 FROM 表名 GROUP BY 分组字段 [HAVING 条件] [ORDER BY 条件][LIMIT offset,len ]
offset:偏移量,跳过的记录条数为0时可以不写;len:记录条数
(四)、DCL(数据控制语言):
一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以,我们应该可以根据不同的项目建立不同的用户,分配不同的权限来管理和维护数据库。用户和权限的管理可以通过DCL(Data Control Language)来实现。
- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
参数说明:
(1)username:自己想要创建的用户名
(2)host:可以连接到这个数据的主机地址,如果想要任意主机都可以连接到这台服务器的话,可以使用通配符%
(3)password:用户登录的密码
- 编辑用户
创建好用户后,如果要修改用户密码的话可以进行下面的操作
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
例如
SET PASSWORD FOR 'zs'@'196.168.64.12' = PASSWORD("123456");
如果是当前登录用户可以使用下面的命令
SET PASSWORD = PASSWORD("123456");
- 删除用户
如果想删除创建的用户可以使用下面的命令
drop user '用户名'@'ip';
- 授权
命令
GRANT privileges ON databasename.tablename TO 'username'@'host'
注意:
(1)privileges:用户的操作权限,如SELECT,INSERT,UPDATE,DELETE等,如果要授予所的权限则使用ALL
(2)databasename:数据库名
(3)tablename:数据库表,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
(4)username:要授权的用户
(5)host:ip
例子
- 给用户zs,授予数据库test1里面的用户表user增删改查的权限
命令
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
注意:
(1)privileges:用户的操作权限,如SELECT,INSERT,UPDATE,DELETE等,如果要授予所的权限则使用ALL
(2)databasename:数据库名
(3)tablename:数据库表,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
(4)username:要授权的用户
(5)host:ip
例子
- 给用户zs,授予数据库test1里面的用户表user查改的权限后,想撤销该权限
REVOKE SELECT,UPDATE ON test1.user TO 'ZS'@'192.169.12.12';
(五)、数据库事务
什么是数据库事务?
数据库事务是访问并可能操作各种数据项的一个数据库操作(包括读、写)序列,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。
例如:银行转账。核心操作包括更新一个账户减去转账金额,更新另一个账户加上转账金额,要求要同时成功或同时失败。
事务的目的就是提供三个方法1、失败恢复方法 2、保存一致性方法 3、操作隔离的方法
事务的四个特性(ACID)
1、原子性(Actomicity)
表示一个事务内的所有操作是一个整体,是一个不可分割的工作单位,要么全部成功,要么全部失败。
2、一致性(Consistency)
事务前后数据的完整性必须保持一致。
3、隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
4、持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
操作事务
-
自动提交事务
MySQL的每一条 DML(增删改)语句都是一个单独的事务,MSQL默认情况下,在执行每条语句时都会自动开启一个事务,执行完毕自动提交事务。
-
手动提交事务
SQL语句 | 描述 |
---|---|
start ransaction; | 开启事务 |
commit; | 提交事务 |
rollback; | 回滚事务 |
事务原理:
事务开启之后,所有的操作都会临时保存到事务日志,事务日志只有在得到commit 命令才会同步到数据表中,其他任何情况都会清空事务日(rollback,断开连接)
事务并发异常: 事务并发操作指的是多个客户端操作同一个数据库的同一张表。
异常 | 描述 |
---|---|
回滚丢失 | 手动提交数据时提交了rollback造成了数据丢失 |
覆盖更新 | 两个事务提交时间不同导致后一个覆盖了前一个 |
脏读 | 一个事务读取了另一个事务尚未提交的数据 |
不可重复读 | 一个事务对同一行数据执行了两次或以上的查询,数据却不同 |
幻读 | 一个事务对同一行数据执行了两次或以上的查询,数据的数量却不同 |
当然这些并发的异常可以说是由数据库的隔离级别引起的,或者说通过调整数据库的隔离级
别可以防止以上并发异常的发生。
隔离级别
数据库的事务隔离级别主要包括:读未提交(Read Uncomitted)、读已提交(Read Committed)、可重复读(Repeatable Read)、串行化(Serializable)。隔离级别越高,数据库的效率就越低。
MySQL 数据库 InnoDB存储引擎(默认)的默认隔离级别是 Repeatable Read
Oracle 数据库默认的隔离级别是 Read Committed。
各种隔离级别能解决哪些并发异常:
隔离级别\并发异常 | 回滚丢失 | 脏读 | 不可重复读 | 覆盖丢失 | 幻读 |
---|---|---|---|---|---|
读未提交(Read Uncomitted) | ✖ | ✖ | ✖ | ✖ | ✖ |
读已提交(Read Committed) | ✔ | ✔ | ✖ | ✖ | ✖ |
可重复读(Repeatable Read) | ✔ | ✔ | ✔ | ✔ | ✖ |
串行化(Serializable) | ✔ | ✔ | ✔ | ✔ | ✔ |
-
隔离级别查看和修改
查看
select @@transaction_isolation
修改
set @@session transaction isolation level 隔离级别 #修改隔离级别,只是修改当前会话的隔离级别。如果打开另外一个会话查看,隔离级別没有改变。
二、约束
约束的作用: 对表中的数器进行进一步的限制,保证数据的正确性、有效性和完整性。约束都是加在列上的,对一列的数举进行限制。
数据库约束 主要包括:主键约束、非空约束、唯一约束、默认约束(缺省约束)、外键约束、检查约束(MySQL不支持检查约束)
-
主键约束:唯一、非空
主键自增长 auto_increment
ALTER TABLE 表名 MODIFY 主键列名 数据类型 auto_increment
修改初始值
ALTER TABLE 表名 AUTOINCREMENT=起始值
-
truncate:格式化表
TRUNCATE TABLE 表名
-
非空约束(not null)
-
唯一约束(unique):唯一、可以为空
-
缺省(默认)约束(default)
-
外键约束:一张表的某个字段引用另一张表的主键
主表:约束别人的表;从表:被约束的表
CREATE TABLE 从表名(
...,
CONSTRAINT 外键约束名 FOREIGN KEY 从表的外键名REFERENCES 参照的主键名
)
级联操作:主从表同时操作
三、表关系
一对一
一对多
多对多 需要额外创建一张关系表
四、多表查询
- 连接查询:
- 内连接:隐式
where a表字段=b表字段
,显示a表 join b表 on a表字段=b表字段
- 外连接:左连接
左表LEFT OUTER JOIN 右表 ON 条件
,右连接左表 RIGHT OUTER JOIN 右表 ON 条件
-
子查询:
概念: 一条select语句作为另一条select语法的一部分。
子查询在主查询前执行一次。子查询的结果被用作主查询的条件。
子查询包括单行子查询、多行子查询、多列子查询。
- 单行子查询:结果是一个值单行运算符 “=”、“!=”等比较运算符。
- 多行子查询:结果是单列多行;多行运算符“in”、“any”、“all”配合比较运算符使用。
- 多列子查询:将子查询语句放在SELECT后面,WHERE之前。
五、数据库备份与还原
备份
-
命令
mysqldump -u用户名 -p密码 数据库 > 文件路径/文件名.sql
-
Navicat
导出表结构和数据
再选择路径保存相应路径。
还原
-
命令
SOURCE 路径名
-
Navicat
先创建一个空的数据库。
六、索引
索引(Index)是帮助MySQL高效获取数据的数据结构。索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
分类
普通索引、唯一索引和组合索引(多列索引)。
1. 普通索引
-
特点:普通索引使用没有限制,允许在所有数据类型上创建,可以有重复值和空值。一个索引只包含单个列,一个表可以有多个单列索引。
-
创建:
a.建表时创建
INDEX 索引 (列名)
示例:
CREATE TABLE student( id int PRIMARY KEY,name varchar (20), age int, INDEX name _indx (name)
测试:
SHOW INDEX FROM student
b.建表时创建
ALTER TABLE 表名 ADD INDEX 索引名(列名)
示例:
create table student2( id int primary key, name yarchar (20), age int ) ALTER TABLE student2 ADD INDEX name indx(name)
c. 直接创建
语法:
CREATE INDEX 索引名 ON 表名(列名)
2. 唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须是唯一的,创建方法和普通索引类似。
3. 组合索引
同时在多个列上添加索引。当查询时,条件是添加了索引的列的组合,可以提高查询效率。平时用的 SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。
a、建表时创建
INDEX 索引名(列名1...,列名n)
创建索引条件:(1)主键自动建立唯一索引。(2)频繁作为查询条件的字段应该创建索引。(3)查询中与其它表关联的字段,外键关系建立索引。(4)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。(5)普通/组合素引的选择问题,在高并发下倾向创建组合索引。
索引使用注意事项:
1、索引不会包含有 NULL 值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。Is null和is not null 无法使用索引。
2、使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个 CHAR(255)的列,如果在前 10 个或 20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/0操作
3、索引列排序
MvSQL查询只使用一个索引,因此如果 where 子句中已经使用了索引的话,那么 order by中的列是不会使用索引的。因此数据库默认排序在符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4、like 语句操作
一般情况下不鼓励使用 like操作,如果非使用不可,如何使用也是一个问题。“%aaa%"不会使用索引而like “aaa%”可以使用索引。
5、不要在列上进行运算
6、MySQL 使用不等于(!=或者<>)时无法使用索引会导致全表扫描
在 MySQL,的查询语句的查询条件中如果使用了不等于,那么将索引失效,进行全表扫描方式
查询。
7、最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。Is null和is not null 无法使用索引。
2、使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个 CHAR(255)的列,如果在前 10 个或 20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/0操作
3、索引列排序
MvSQL查询只使用一个索引,因此如果 where 子句中已经使用了索引的话,那么 order by中的列是不会使用索引的。因此数据库默认排序在符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4、like 语句操作
一般情况下不鼓励使用 like操作,如果非使用不可,如何使用也是一个问题。“%aaa%"不会使用索引而like “aaa%”可以使用索引。
5、不要在列上进行运算
6、MySQL 使用不等于(!=或者<>)时无法使用索引会导致全表扫描
在 MySQL,的查询语句的查询条件中如果使用了不等于,那么将索引失效,进行全表扫描方式
查询。
7、最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
type可取值:microsecond、second、minute、hour、day、week、month、quarter、year等。 ↩︎