MySQL学习小结——初级篇

一、列类型

数值型
  整型
    TINYINT
        TINYINT(M) UNSIGNED ZEROFILL
        UNSIGNED:无符号，影响存储范围
        M:代表宽度，(在ZEROFILL时才有意义)
        ZEROFILL:零填充，(如果某列是ZEROFILL，默认就是UNSIGNED)
    SMALLINT
    MEDIUMINT
    INT
    BIGINT
  小数型
    浮点型
      FLOAT(M,D)
          FLOAT(M,D)
          M:精度(总位数，不包含点)
          D:标度(小数位)
          MySQL比较特别，可以指定FLOAT的小数位，SQL SERVER和ORACLE都不支持，也应当是不能指定的
          e.g. FLOAT(6,2) -9999.99 9999.99
          关于5的进与舍???
    定点型,更精确
      DECIMAL(M,D)
字符型
  CHAR
      CHAR(M)，定长，M代表宽度，即：可容纳的字符数，0<=M<=255
      实存字符N(N<=M)，则实占M个字符，空间利用率=N/M<=100%
      如果不够M个字符，右侧用空格补齐，当取出时将尾部空格去除，即若存储内容尾部有空格则会丢失--可用CONTACT(columnName,'!')查看
  VARCHAR
      VARCHAR(M)，变长，M也代表宽度，但此处代表可容纳的字节数，0<=M<=65535(ASCII)，若为UTF8,则在65535/3=21845
      实存字符N(N<M)，则实占N个字符+(1-2)字节，空间利用率=N/(N+(1-2))<100%
  TEXT
      文本类型，存储大段文本，不支持全文索引，不能添加默认值
[以下两个不符合关系型数据库的设计原则]
  ENUM
  SET
时间型
  YEAR
      1个字节，表示1901-2155[保留了0000,错误时的选择]
      如果输入2位，'00-69'表示2000-2069，'70-99'表示1970-1999
  DATE
      日期类型，表示'1000-01-01'-=-'9999-12-31'
  TIME
      时间类型，表示'-838:59:59'-=-'+838:59:59'，因为还可以表示两个事件之间的时间间隔
  DATETIME
      日期时间类型，'1000-01-01 00:00:00'-'9999-12-31 23:59:59'
      注意：在开发中，很少用DATETIME来表示一个需要精确到秒的列
      原因：虽然方便查看，但不方便计算，因此使用INT来表示
            时间戳(是从1970-01-01 00:00:00到当前的秒数)

二、SELECT的5种子句(WHERE, GROUP BY, HAVING, ORDER BY, LIMIT)

WHERE
比较运算符
<, <=, =, >=, >, !=
IN, BETWEEN
逻辑运算符
AND, OR, NOT
模糊查询
LIKE (% => 通配任意字符; _ => 通配单个字符)
e.g. 1、... WHERE column_name LIKE 'STH%';
     2、... WHERE column_name LIKE 'STH_';

GROUP BY
配合5个统计函数使用
MAX, MIN, SUM, AVG, COUNT

HAVING
与WHERE对表进行查询不同，HAVING是对查询结果进行查询

;;;;;;;;;;;
; MySQL试题
CREATE TABLE stu (
name CHAR(4) DEFAULT '',
course CHAR(4) DEFAULT '',
score TINYINT UNSIGNED DEFAULT 0
) ENGINE INNODB CHARSET UTF8;
;;;;;;;;;;;
INSERT INTO stu VALUES
('张三','数学',90),
('张三','语文',50),
('张三','地理',40),
('李四','语文',55),
('李四','政治',45),
('王五','政治',30);
; 试查询两门及两门以上不及格同学的平均分
; 1、不用子查询
SELECT name,AVG(score),SUM(score<60) AS fail_num FROM stu GROUP BY name HAVING fail_num>=2;
; 2、利用子查询
SELECT name,AVG(score) FROM stu WHERE name IN (SELECT name FROM (select name,count(*) as fail_num from stu where score<60 group by name having fail_num>=2) AS tmp) GROUP BY name;
;;;;;;;;;;;

ORDER BY
ASC(DEFAULT), DESC
e.g. 1、... ORDER BY 字段1 [ASC/DESC], 字段2 [ASC/DESC]...

LIMIT [OFFSET,] N
OFFSET: 偏移量，可选，DEFAULT: 0
N: 取出条目数
e.g. 1、... LIMIT 2,3

三、子查询

;;;;;;;;;;;;
; 取出每个栏目下面最贵的商品
SELECT * FROM (SELECT goods_id,cat_id,goods_name,shop_price FROM goods ORDER BY shop_price DESC) AS tmp GROUP BY tmp.cat_id;
SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE shop_price IN (SELECT MAX(shop_price) FROM goods GROUP BY cat_id);
;;;;;;;;;;;;

WHERE型
内层查询结果作为外层查询的比较条件

FROM型
内层查询结果供外层再次查询(将内层查询结果看成临时表，加 "AS tmp")

EXISTS型
把外层查询结果代人到内层，看内层是否成立
e.g. SELECT * FROM category WHERE EXISTS (SELECT * FROM goods WHERE goods.cat_id = category.cat_id);

UNION型
合并查询的结果(取select结果的并集)
对于重复的行，默认去掉
如果要不去重复，可以用UNION ALL
要求：各SELECT查出的列数一致
如果子句中用了ORDER BY LIMIT，则子句需要“()”包起来，且应放到所有子句之后
如果子句中只用了ORDER BY，没有LIMIT，则ORDER BY被语法分析器优化掉，不起作用

四、连接查询

左连接，右连接，内连接
SELECT ta.列,tb.列,... FROM
ta LEFT/RIGHT/INNER JOIN tb
ON ta.列=tb.列
WHERE ...

左连接与右连接可以相互转化
ta LEFT JOIN tb <===> tb RIGHT JOIN ta

内连接是左右连接的交集，包含了两张表中能相互匹配上的行

;;;;;;;;;;;;
SELECT matchID, t1.teamName AS hostTeamName, matchResult, t2.teamName AS guestTeamName, matchTime
FROM
match LEFT JOIN team as t1
ON match.hostTeamID = t1.teamID
      LEFT JOIN team as t2
ON match.guestTeamID = t2.teamID
WHERE matchTime BETWEEN '2006-06-01' AND '2006-07-01';
;;;;;;;;;;;;;

五、表的管理-->列的增删改

建表：
CREATE TABLE 表名 (
列1名称 列类型 [列属性] [默认值], -->列声明
列2名称 列类型 [列属性] [默认值],
列3名称 列类型 [列属性] [默认值]
) CHARSET=UTF8;

增加列：
ALTER TBALE 表名 ADD 列声明;
新增的列默认在表的最后一列，可以用AFTER来指定
ALTER TBALE 表名 ADD 列声明 AFTER 列?名称;
如果要放在第一列，则使用FIRST
ALTER TBALE 表名 ADD 列声明 FIRST;

修改列：
ALTER TABLE 表名 CHANGE 要修改的列名称 列声明;

删除列：
ALTER TABLE 表名 DROP 列名

六、视图 VIEW

视图是一张虚拟的表，没有真实的数据存在，只是与表的一种查询产生的关系。

语法：
CREATE [ALGORITHM=MERGE|TEMPTABLE|UNDEFINED] VIEW view_name AS SELECT ...;
DROP VIEW viewName;
ALTER VIEW view_name AS SELECT ...;

MERGE
视图<-- SQL1, ... WHERE id>1000;
查视图<-- SQL2, ... WHERE id<2000;
形成SQL: ... WHERE id>1000 AND id<2000 --查询-->基表

TEMPTABLE
视图<-- SQL1, WHERE id>1000;
查视图<-- SQL2, WHERE id<2000;
SQL2-->[临时表]<--SQL1<--基表

作用：
1、可以简化查询
2、可以进行权限控制，把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据
3、大数据分表时可以用到

视图与表的关系***
表的数据改变=>影响视图的结果
视图改变？=>
1、视图增删改也会影响到表
2、视图并不总是能增删改的 => 视图的数据与表的数据一一对应时，才可以修改；对于INSERT，视图必须包含表中所有没有默认值的列

七、字符集与校对集

字符集 SHOW CHARACTER SET;
客户端-->连接器-->服务器
客户端<--连接器<--服务器

告诉连接器客户端使用的是什么字符集：SET CHARACTER_SET_CLIENT=GBK/UTF8
告诉连接器把收到的信息转化为什么字符集：SET CHARACTER_SET_CONNECTION=GBK/UTF8
(连接器到服务器的转化自动完成？？？)
告诉连接器返回到客户端时使用什么字符集：SET CHARACTER_SET_RESULTS=GBK/UTF8
当三者一致时：SET NAMES GBK/UTF8/...

校对集 SHOW COLLATION;
校对集是排序的规则，一种字符集可以对应一种或多种校对集
以ASCII字符为例：
将a B c D升序排列
①以生活习惯排列：a B c D
②以二进制升序排列：B D a c

CREATE TABLE ta(...) CHARSET utf8 COLLATE utf8_general_ci;
声明的校对集必须是字符集合法的校对集

八、触发器 TRIGGER

监视某种情况并触发某个操作

监视范围：增、删、改
触发操作：增、删、改
应用场景：密不可分的业务逻辑，如商品下单(INSERT INTO ...)后，库存相应减少(UPDATE ...)

四要素：
监视地点(TABLE)
监视事件(INSERT/UPDATE/DELETE)
触发时间(AFTER/BEFORE)
触发事件(INSERT/UPDATE/DELETE)

语法：
CREATE TRIGGER triggerName
AFTER/BEFORE INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW (这在MySQL里是写死的，即MySQL只有行触发器)
BEGIN
SQL... (一句或多句INSERT/UPDATE/DELETE)
END;
DROP TRIGGER triggerName;
SHOW TRIGGERS;

改变语句结束符号
DELIMITER $

对于INSERT而言，新增的行用NEW表示，行中的每一列的值，用NEW.列名来表示
# 1.添加一个订单，库存相应减少
e.g.1
CREATE TRIGGER tg1
AFTER INSERT ON o
FOR EACH ROW
BEGIN
UPDATE g SET num=num-NEW.much WHERE id=NEW.gid;
END$

对于DELETE而言，被删的行用OLD表示
# 2.删除一个订单，库存相应增加
e.g.2
CREATE TRIGGER tg2
AFTER DELETE ON o
FOR EACH ROW
BEGIN
UPDATE g SET num=num+OLD.much WHERE id=OLD.gid;
END$

对于UPDATE而言，被修改的行，修改前的数据用OLD表示，修改后的数据用NEW表示
# 3.修改订单购买数量时，库存相应改变
e.g.3
CREATE TRIGGER tg3
AFTER UPDATE ON o
FOR EACH ROW
BEGIN
UPDATE g SET num=num+OLD.much-NEW.much WHERE id=OLD.gid;
END$

AFTER和BEFORE的区别
AFTER是先完成数据的增删改再进行触发，
触发的语句晚于监视的增删改，无法影响前面的增删改动作。
BEFORE是先完成触发，再增删改
触发的语句先于监视的增删改发生，有机会审查判断，修改即将发生的操作
# 4.对于所下订单进行判断，如果订单数量>5，即认为是恶意订单，强制把订单的商品数量改为5
e.g.4
CREATE TRIGGER tg4
BEFORE INSERT ON o
FOR EACH ROW
BEGIN
    IF NEW.much>5 THEN
        SET NEW.much=5;
    END IF;
    UPDATE g SET num=num-NEW.much WHERE id=NEW.gid;
END$

九、事务 TRANSACTION

存储引擎 ENGINE
Myisam,不支持事务，批量插入速度快，锁表，支持全文索引
InnoDB,支持事务，批量插入相对较慢，锁行，5.5版本以后支持全文索引

事务 TRANSACTION
ACID特性
事务是指一组操作，要么都执行成功，要么都不执行。--->原子性Atomicity
在所有的操作没有执行完毕之前，其他会话不能够看到中间改变的过程。--->隔离性Isolation
事务发生前和发生后，数据的总额依然匹配。--->一致性Consistency
事务产生的影响不能撤销。--->持久性Durability
如果出了错误，事务也不允许撤销，只能通过”补偿性事务“进行抵消

生活实例：银行转账
# 建立账户
CREATE TABLE account (
id int,
name varchar(10),
money int
) ENGINE INNODB CHARSET UTF8;


e.g.
开启事务：STRAT TRANSACTION;
SQL1...
SQL2...
...
提交事务COMMIT;
回滚事务ROLLBACK;
注意1：当一个事务COMMIT/ROLLBACK之后，它就结束了。
注意2：有些语句会造成事务的隐式提交，比如START TRANSACTION;

十、数据备份与恢复

备份分为：①增量备份，②整体备份
e.g.每周日整体备份一次，周一到周六备份当天
备份工具
有第三方的收费备份工具，
有MySQL自带的工具--mysqldump

导出=>库&表，导出内容是建表语句及INSERT语句
e.g. 导出某个数据库下面的某些个/所有的表
mysqldump -uroot -proot databaseName [tableName1 tableName2 ... tableNameN] >backupPath+backupName
e.g. 以库为单位导出
mysqldump -uroot -proot -B databaseName >backupPath+backupName
e.g. 导出所有库
mysqldump -uroot -proot -A >backupPath+backupName

恢复
1、登录到mysql client
source backupPath+backupName;(database level)
①use databaseName; ②source backupPath+backupName;(table level)

2、不登录mysql client
mysql -uroot -proot <backupPath+backupName(database level)
mysql -uroot -proot databaseName <backupPath+backupName(table level)

11、索引 INDEX

概念：针对数据所建立的目录
无索引 N/2次
二叉树索引 log2N次
哈希索引 1次(理论) 注意点：碰撞性、最大区间
好处：
加快了查询速度（SELECT）
坏处：
降低了增删改的速度（INSERT/DELETE/UPDATE）
增大了表的文件大小（索引文件甚至可能比数据文件还大）

创建原则
①不过度索引
②索引条件列（WHERE后面最频繁的条件比较适宜索引）
③索引散列值，如给性别”男“、”女“加索引意义不大

索引类型：
普通索引 INDEX：仅仅是加快查询速度
唯一索引 UNIQUE：行上的值不能重复
主键索引 PRIMARY KEY：主键不能重复
    主键必唯一，但是唯一索引不一定是主键
    一张表只能有一个主键，但是可以有一个或多个唯一索引
全文索引 FULLTEXT：

查看一张表上的所有索引：
SHOW INDEX FROM tablename;

建立索引
ALTER TABLE tableName ADD INDEX/UNIQUE/FULLTEXT[索引名](列名);
ALTER TABLE tableName ADD PRIMARY KEY (列名); //不能加索引名，因为主键只有一个

删除索引
ALTER TABLE DROP INDEX 索引名;
ALTER TABLE DROP PRIMARY KEY;

全文索引
用法：
MATCH(fulltext_name) AGAINST('keyword');
停止词：
全文索引不针对非常频繁的词建立索引

在MySQL的默认情况下，对中文意义不大
因为英文有空格，标点符号来拆成单词，进而对单词进行索引
而对于中文，没有空格来隔开单词，MySQL无法识别

12、存储过程 PROCEDURE

概念类似于函数，就是把一段代码封装起来，
当要执行这一段代码的时候，可以通过调用该存储过程来实现
在封装的语句体里面，可以用IF...ELSE, SWITCH...CASE, WHILE...DO等控制结构
可以进行SQL编程

语法：
SHOW PROCEDURE STATUS;
DROP PROCEDURE procedure_name;
CALL procedure_name;

e.g.1 体会封装'SQL'
CREATE PROCEDURE p1()
BEGIN
    SELECT * FROM g;
END$

e.g.2 体会'参数与控制结构'
CREATE PROCEDURE p2(n int)
BEGIN
    SELECT * FROM g WHERE num>n;
END$

e.g.3 体会'控制结构'
CREATE PROCEDURE p3(n int,j char(1))
BEGIN
    IF j = 'h' THEN
        SELECT * FROM g WHERE num>n;
    ELSE
        SELECT * FROM g WHERE num<n;
    END IF;
END$

e.g.4 体会'循环'
# 计算1->n的和
CREATE PROCEDURE p4(n smallint)
BEGIN
    DECLARE i int;
    DECLARE s int;
    SET i = 1;
    SET s = 0;
    WHILE i <=n DO
        SET s = s + i;
        SET i = i + 1;
    END WHILE;
    SELECT s;
END$

在MySQL中，存储过程和函数的区别：
1. 名称不同
2. 存储过程没有返回值