数据库MySQL一
数据库
数据库：保存有组织数据的容器。DBMS是为管理数据库而设计的软件管理系统，MYSQL、ORACLE等是数据库管理系统。
第一章 基础
SQL是Structured Quevy Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。
数据库的四种语法
数据查询语言DQL：基本结构是由select子句、from子句、where子句组成，select<字段名表>、from<表或视图名>、where<查询条件>。
数据操纵语言DML：插入insert、更新update、删除delete。
数据定义语言DDL：创建create、表table、视图view、索引index、同义词syn、簇cluster。
数据控制语言DCL：用来授予或回收访问数据库的某种特权，并控制数据库操纵事物发生的时间及效果，对数据库实行监视等。
授权grant
回退到某一点rollback [work] to [savepoint]，回滚rollback：使数据库状态回到上次最后提交的状态，格式为SQL->rollback
提交commit [work]：数据库的插入、删除和修改操纵时，只有当事物在提交到数据库时才算完成。事物提交前，只有操作数据库的操作者才有权看到所做的事，其他人只有在最后提交完成后才能看到。提交数据三种类型：显式提交（SQL->commit）、隐式提交(间接完成提交：alter、audit、comment、connect、create、disconnect、drop、exit、grant、noaudit、quit、revoke、rename)、自动提交（SQL->set autocommit on，在插入、修改、删除语句执行后，系统自动提交）。
命令
命令规则
标识符（数据库名）命名规则：
大小写取决于当前操作系统。
标识符的字符：
使用任意字符，数字，符号，甚至是中文。但是一些特殊的组合，例如纯数字组合，特殊符号，包括mysql是内部关键字 应该使用标识符 限定符来包裹。
表数据基本操作：增insert、删delete、改update、查select。
表操作：create、drop、alter、show、rename、add等
MYSQL命令
CREATE DATABASE NAME 创建数据库
USE DATABASE 选择数据库
DROP DATABASE NAME 直接删除数据库
SHOW DATABASES 显示可用数据库列表
SHOW TABLES 显示数据库中的表的列表
SHOW COLUMNS FROM TABLE 与 DESCRIBE TABLE 等效，显示表的字段信息（列）
SHOW GRANTS 显示授予用户的安全权限
SHOW ERRORS SHOW WARNINGS 显示服务器错误和警告信息
SQL命令
STRUCTURED QUERY LANGUAGE 结构化查询语言，一种专门用于与数据库通信的语言
不是DBMS专有的语言，很多DBMS都支持SQL，但是不同DBMS对SQL的实现不同
DBMS支持的SQL语法不能完全适用于其他DBMS
SQL语句不区分大小写
多条语句需要分号分隔，单条语句可以不用分号
通常SQL语句用大写，标识符（比如表名 列名 数据库名）用小写
创建表
CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL DEFAULT 1, PRIMARY KEY(id)) ENGINE =INNODB
create table tbl_name (列结构[列选项])[表选项];
列选项包括:
[是否为空] [Default 默认值] [是否为自动增长] [是否为主索引或唯一索引] [comment 注释] [引用定义]
表选项:
表引擎：engine|type=引擎;表字符集与校对集 charset set=字符集 collate=校对集; 注释 comment=‘注释’
创建表索引
create table 表名（属性名 数据类型[完整性约束条件]，属性名 数据类型[完整性约束条件]...
[unique | fulltext | spatial] index | key [别名] （属性名1 [（长度）] [asc | desc]））;
其中，unique是可选参数，表示索引为唯一性索引；fulltext可选参数，表示索引为全文索引；spatial可选参数，索引为空间索引；index和key参数用来指定字段为索引，两者选一，作用一样；“别名”可选参数，给创建的索引取的新名称；属性1 指定索引对应的字段的名称，该字段必须为前面定义好的字段；长度 为可选参数，指索引长度，必须是字符串类型才可以使用；asc和desc可选，升序和降序，缺省值为ASC。
删除索引
drop index <索引名>
查看
SHOW TABLES
SHOW TABLES LIKE ‘exam_%’;//模糊查看表
SHOW CREATE TALBE user;//查看表的创建信息
删除
DROP TABLE [IF EXISTS] user;
重命名
RENAME TABLE oldname1 TO newname1, oldname2 TO newname2;
修改
ALTER TABLE user [ADD | DROP | CHANGE | MODIFY]//修改表内容（列定义）
ALTER user ADD newcolumn VARCHAR(10);//新增一列
ALTER user DROP onecolumn;//删除一列
ALTER user MODIFY newcolumn VARCHAR(20);//修改列属性
ALTER user CHANGE oldcolumn newcolumn VARCHAR(30);//修改列名称
查-表数据
select [查询选项] [查询表达式(字段表达式)] [from 子句] [where 子句] [group by 子句] [having 子句] [order by 子句] [limit 子句]
[as] 别名,
dual 虚表
where 数据过滤，理解上，数据安装交叉连接连接完成后，再做数据过滤。
having
using 要求，负责连接的两个实体之间的字段名称，一致。
on在连接时，就对数据进行判断。
Order by升序|降序(asc|desc) 默认 升序，asc
limit Limit offset(偏移量,默认0,可以忽略)，row_count(查询显示记录数),从offset开始查询后面row_count行
distinct 去重
Union 结果去重,需要上线语句列数及类型一致
Union all 结果不去重. 如果需要排序order by,则各子句需要limit,否则各自排序忽略. 或查出来之后在最后加order by 即可.
索引列
SELECT username FROM user
SELECT username,password FROM user
SELECT * FROM user;//索引所有列，一般不建议使用，很耗性能
SELECT DISTINCT username FROM user;//只返回不同的username
限制结果
SELECT * FROM user LIMIT 5;//检索前5行
SELECT * FROM user LIMIT 5,5;//检索6-10行
结果排序
SELECT * FROM user ORDER BY username;//以username的字母顺序排列
SELECT * FROM user ORDER BY username,password;//如果username相同，再按password排列。
SELECT * FROM user ORDER BY username DESC;//降序排列，默认是ASC
SELECT * FROM user ORDER BY username DESC,password;//username降序排列，相同则按password升序排列
SELECT * FROM user ORDER BY username LIMIT 1;//按username升序排列，且只显示一行
SELECT * FROM user WHERE username =’zhangsan’ ORDER BY password;//叫张三的人按照密码升序排列
过滤数据
SELECT * FROM user WHERE username <> ‘zhangsan’;//不等于和 != 效果相同
SELECT * FROM user WHERE id BETWEEN 1 AND 10;//索引1到10的记录，包括1和10
SELECT * FROM user WHERE username IS NULL ;//查找列无值的行，这里的无值不等于 值为0 和 空字符串
SELECT * FROM user WHERE id=1 OR username=’zhangsan’ AND password=’666’;//查找id为1或者（名为zhangsan且密码为666）的，建议加括号
SELECT * FROM user WHERE id IN (1,2);//索引id在1-2之间，包含1和2的，id为INT。
SELECT * FROM user WHERE id NOT IN(1,2);//
通配符过滤
SELECT * FROM user WHERE username LIKE ‘zhang%’;//匹配zhang开头的username，不管后续多少字符
SELECT * FROM user WHERE username LIKE ‘zhang_’;//只能匹配一个字符
通配符匹配效率不高，通配符最好不要放在开始处
正则表达式过滤
SELECT * FROM user WHERE username REGEXP ‘.hang’;//正则表达式，匹配任意一个字符
SELECT * FROM user WHERE username REGEXP ‘san’;//匹配包含san的username
SELECT * FROM user WHERE username REGEXP ‘zhangsan|zhang’;// | 和OR 效果一样
SELECT * FROM user WHERE username REGEXP ‘[12345]san’;//匹配含1san、2san、3san…5san
[^123]123除外
[1-9],[A-Z]用-表示范围
匹配特殊字符可以采用‘\’转义
拼接字段
SELECT CONCAT(username,’(‘,password,’)’) FROM user;//将两个字段拼接到一起，查询结果是一列
SELECT CONCAT (username, ‘)’, password, ‘)’) AS up FROM user;//为拼接后的一列设置字段名
算数运算
SELECT price* count AS all FROM TABLE;//单价和数量相乘，列名为all
函数-表数据
函数可移植性不高，一个函数支持一个DBMS，但不一定支持另一个DBMS，所以函数要写好注释。
文本处理函数
LENGTH()返回串长度
LOEWR()串转换为小写
UPPER()串转换为大写
LTRIM()去掉串左边的空格
RTRIM()去掉串右边的空格
日期和时间处理函数
CURDATE()
CURTIME
NOW()//当前日期和时间
DATE()返回日期的日期部分
DATE_FORMAT()格式化的日期和时间串
HOUR()、MINUTE()、MONTH()、SECOND()、YEAR()、TIME()
日期函数注意点
SELECT * FROM TABLE WHERE date=’2017-7-22’;//DATE的类型是DATETIME类型，就会有问题，2017-07-22 16:20:11
SELECT * FROM TABLE WHERE DATE(data)=’2017-7-22’;//
时间范围可以采用DATE 函数 SELECT * FROM TABLE WHERE DATE(date) BETWEEN ‘2017-7-1’ AND ‘2017-7-31’
数组处理函数
ABS()、COS()、EXP()、MOD()、PI()、SIN()、SQRT()（平方根）、TAN()、RAND()(随机数)
聚集函数
SELECT AVG(student_score) AS AVG_PRIVE FROM score;//求平均值
SELECT COUNT(*) FROM TABLE;//表里记录数
SELECT COLUMN(name) FROM TABLE;//表里列有值的记录数，值为NULL不计数
SELECT MAX(score) FROM TABLE ;//查找最大值；最小MIN
SELECT SUM(count) FROM TABLE;//返回指定列count的和。
数据分组
SELECT AVG(score) FROM score GROUP BY class_id;//检索不同课的平均分。并且查询的字段必须是GROUP BY后的字段或者聚合函数
SELECT AVG(score) FROM score GROUP BY class_id,dep_id;//
GROUP BY在WHERE之后，在ORDER BY之前，先过滤再排序
SELECT COUNT(*) FRON TABLE GROUP BY id HAVING COUNT(8)>3;//分组后对分组再进行过滤
子查询
SELECT * FROM student WHERE class_id IN (SELECT class_id FROM teacher WHERE teacher_id=6);//子查询过滤，where里嵌套子查询
SELECT name (SELECT score FROM score WHERE student.student_id=score.student_id) AS score FROM student;
聚合分组查询
groupby ( with rollup )
Sum() Avg() Max(); Min(); Count() Group_concat()[分组结果通过,连接]
where先执行，group by 后执行
关系运算符
= > < >= <= !=
like _ % \% \_ (查%或_用\转义)
Is null is not null
Between and
In|not in (集合元素)
<=> 功能与 =一致，特别的功能在于 可以比较null值。
select null<=>null,1<=>null 返回 1 ,0
逻辑运算
And && Or || Not ! Xor
非：not !，非null 为null。
与：and &&，有0就是0，都是非零为1，存在null与非零则为null。
或：or || , null||null=null null||1=1 null||0=null
异或： xor ,有null，就是null
存储引擎
MyISAM和InnoDB对比：
MyISAM：不支持事务、不支持外键，但访问速度快，对事务完整性无要求。
InnoDB：提供具有提交、回滚和崩溃恢复能力的事务安全，但比MyISAM效率差些，且占用更多磁盘空间。
MEMORY：使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。访问非常快，默认使用HASH索引，但服务一旦关闭，表中数据就会丢掉。
实体关系
主键
唯一标识自己的一列或一组列，唯一区别表中的一行
主键不能为空
任意两行的主键值必须不同
主键不是必须的，但是建议每个表中有一个主键，这样操作管理更方便
主键值最好不更新
一个表的主键只能有一个
外键
一个表的主键指向另一个表的外键，比如说student表的主键student_id，在score表中也有，并且是score表的外键
外键保持了数据完整性和一致。比如你在student表里面修改了student_id后，则score的student_id也会联动更新。并且score表中插入的student_id必须是student表里有的
外键：如果一个实体A的某个字段id指向另一个实体B的主键id，就称这个实体的主键（A:id）是外键。
被指向的实体B，称为主实体（主表），B。
负责指向的实体，称为从实体（从表），A。
定义一个外键：从表上增加一个外键
Foreign Key (外键字段) references 主表名 （关联字段）[主表记录删除时的动作] [主表记录更新时的动作]
eg: table_a 从表，table_b 主表
CREARE table_a(privty id INT private key AUTO_INCREMENT, foreign_key_name foreign key (table_b_id) references table_b(id))
外键操作
类型:
On update
On delete
选项:
Cascade: 关联操作，如果主表被更新或删除，那么从表也会执行相应的操作。
Set null: 设置为null，表示从表不指向任何主表记录。
Restrict：拒绝主表的相关操作。
修改外键
一般为先删除再新增
eg:table_a 从表，table_b 主表
CREATE table_a(privty id INT private key AUTO_INCREMENT, foreign_key_name foreign key (table_b_id) references table_b(id) ON UPDATE SET NULL ON DELETE RESTRICT);//可以同时修改和删除
ALTER TABLE table_a DROP foreign key;
ALTER TABLE table_a ADD foreign key(table_b_id) references table_b(id) ON UPDATE RESTRICT ON DELETE CASCADE;
联结
SELECT name,score FROM score,student;//两个表进行联结，笛卡尔积，行数：score表行数*student表行数。
别名
SELECT name,score FROM score AS A, student AS B WHERE A.student_id=B.student_id;//先将两个表起别名
组合查询
利用UNION将多个SELECT语句的结果组合起来，可以理解成同一个表头的表垂直拼接在一起
每个查询必须包含相同的列数，而且字段类型要兼容。
SELECT score FROM score WHERE NAME='zhangsan' OR score BETWEEN 95 AND 100 和 SELECT score FROM score WHERE NAME='zhangsan' UNION SELECT score FROM score WHERE score BETWEEN 95 AND 100 作用等效。
上面的是单表查询，用了UNION感觉复杂了，但是如果用于不同表的查询的连接会更简单。
如果A查询查到5行，B查询查到4行，由于有重复的，会去掉相同的行，最后剩下8行，如果需要的话可以用UNION ALL
插入数据
INSERT INTO table_name (字段列表) VALUES(值列表);//没用写到的列用默认值,如果不能为空则报错
INSERT INTO user VALUES (‘zhangsan’,’666’)
INSERT INTO user (username, password) VALUES (‘zhangsan’,’666’);//
INSERT INTO user VALUES (‘zhangsan,’666’),(‘lisi’,’666’);//插入多行
INSERT INTO user VALUES (SELECT username, password FROM olduser);//检索出olduser的行然后插入到user表
REPLACE：替换
更新数据
UPDATE 表名 SET 字段=新值,… 条件
UPDATE user SET password=‘666‘,money=‘6666’ WHERE username=’zhangsan’;//更新zhangsan的密码和money
更新多行时如果中途出错，会将跟新的恢复回原来的值，如果要做到及时中途发生错误继续更新可用IGNORY关键字，UPDATE IGNORY user…
删除数据
如果想删除一个列的值，SET username=NULL就行了
如果想要删除一行，DELETE FROM user WHERE username=’zhangsan’
如果想要删除整个表的行 DETELTE FROM user，注意这个表不会删除，只是所有记录清空。TRUNCATE user，也有相同的效果，不同的是他是先删除表，然后重新建立一个表
DELETE FROM user LIMIT 1;//删除第一条
DELETE FROM user1, user2 using user1 JOIN user2 ON user1.public_column = user2.public_column WHERE xxx;//删除多表中的数据
更新表
ALTER TABLE user ADD phone CHAR(20) 添加一列
ALTER TABLE user DROP COLUMN phone 删除一列
ALTER TABLE user CONSTRAINT wai_jian FOREGIN KEY (class_id) REFERENCES class (class_id) 定义外键
ALTER TABLE user ADD PRIMARY KEY (id)添加主键
修改前做好备份，表的更改不能撤销
删除表
DROP TABLE user
重命名表
RENAME TABLE user TO users
视图
当我们查询后出现一个结果，我们可以包装成一个虚拟表，也就是视图，我们可以把他当成表使用
视图本身不包含数据，数据是从其他表检索出来
使用视图可以重用SQL，并且可以保护数据，可以授予用户部分数据权限而不是全部数据
如视图中存在分组(GROUP BY)、联结、子查询、并(UNOIN)、聚合函数(SUM/COUNT等)、计算字段、DISTINCT等都不能对视图进行更新操作
CREATE VIEW my_view AS SELECT name，score FROM student ，创建视图
DROP VIEW MY_VIEW 删除视图
存储过程？
有时候SQL也需要有IF ELSE，我们可以把多条SQL语句封装在一起形成存储过程，这样不仅简单安全而且性能也会更高
存储过程并不显示结果，只是将结果返回给你指定的变量。
过程是这样的，创建一个存储过程，使用存储过程，将参数传入，SELECT参数输出结果
参数类型 IN 传递给存储过程，OUT从存储过程传出，INOUT对存储过程传入传出。结果将返回给OUT变量
CREATE PROCEDURE pro(IN PARAM INT,OUT PARAM2 INT)BEGIN SELECT COUNT(*) FROM user WHERE id=PARAM INTO PARAM2 END; 创建存储过程
CALL PRO (666，@PARAM2)；SELECT @PARAM2；调用并且会输出PARAM2
SHOW CREATE PROCEDURE PRO显示存储过程的CREATE语句
SHOW PROCEDURE STATUS列出所有存储过程
触发器
事件发生时自动执行某些语句，在INSERT UPDATE DELETE之前之后需要做一些操作，这时候可以使用触发器
一个表最多6个触发器，插入删除更新的前后。
CREATE TRIGGER my_trigger AFTER INSERT ON user FOR EACH ROW BEGIN SELECT NEW.id END，创建名为my_trigger的触发器，在对user表，每插入一行，将id显示出来
在触发器中可以引用NEW新的虚拟表，访问插入的行。可以引用OLD虚拟表，访问被删除的行。
DROP TRIGGER my_trigger 删除触发器
只有表支持触发器，视图不支持
触发器中不能调用存储过程
事物
保证成批操作要么完全执行，要么完全不执行，维护数据的完整性。也就是要么成功要么失败。
事务可以是n条sql语句(n>=0)
不是所有数据库引擎支持事务，InnoDB引擎支持事务处理
事物特点ACID
原子性（atomicity）：某几句sql的影响，要么都发送，要么都不发送
一致性（consistency）：事物前后的数据，保持业务上的合理一致
隔离性（isolation）：在事物进行过程中，其他事物，看不到此事物的任何效果
持久性（durability）：事物一旦发生，不能取消，只能通过补偿性事物来抵消效果。
隔离级别
read uncommitted: 读未提交的事务内容，显然不符原子性，称为“脏读”。 在业务中没人这么用。.
read commited: 在一个事务进行过程中，读不到另一个进行事务的操作，但是可以读到另一个结束事务的操作影响。
repeatable read: 可重复读，即在一个事务过程中，所有信息都来自事务开始那一瞬间的信息，不受其他已提交事务的影响。(大多数的系统用此隔离级别)
serializeable 串行化，所有的事务，必须按编号顺序一个一个来执行，也就取消了冲突的可能，这样隔离级别最高,但事务相互等待的等待长。在实用也不是很多。
不可重复读：同一个事务中，对同一份数据读取的结果不一致。事务A在事务B对数据更新前进行读取，然后事务B更新提交，事务A再次读取，这时候两次读取的数据不同。
幻读：同一个事务中，同一个查询多次返回的结果不一样。事务B查询表的记录数，然后事务A对表插入一条记录，接着事务B再次查询发现记录数不同。
不可重复读和幻读的区别：都是在同一个事务中，前者是几次读取数据不同，后者是几次读取数据整体不同。
|
隔离级别 |
作用 |
|
Serializable(串行化) |
避免脏读、不可重复读、幻读 |
|
Repeatable(可重复读) |
避免脏读、不可重复读 |
|
Read committed(读已提交） |
避免脏读 |
|
Read uncommitted(读未提交) |
none |
事物操作
start transcation;
delete from user;
savepoint dele;
****
rollback dele;
commit;
transcation 事务开始
rollback 回滚到事务起点
savepoint 保留回滚位置
rollback point_name 回滚到设置的位置
使用注意
如果事务中sql正确运行，后面没有commit，结果是不会更新到数据库的，所以需要手动添加commit。
如果事务中部分sql语句出现错误，那么错误语句后面不会执行。而我们可能会认为正确操作会回滚撤销，但是实际上并没有撤销正确的操作，此时如果再无错情况下进行一次commit，之前的正确操作会生效，数据库会进行更新。
索引
为了减少查询时间，提高性能。
索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。
如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。
索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。
浙公网安备 33010602011771号