MySQL学习笔记
MySQL数据库管理
操作数据库>操作数据库中的表>操作数据库中表的数据
数据库列类型
-
数值
- tinyint：十分小的数据 1字节
- smallint：较小的数据 2字节
- mediumint：中等数据 3字节
- int：标准整数 4字节
- bigint：较大的数据 8字节
- float：单精度浮点数 4字节
- double：浮点数 8字节
- decimal：字符串形式的浮点数 金融计算使用
-
字符串
- char： 固定大小字符串 0-255
- varchar： 可变字符串 0-65535
- tinytext： 微型文本 2^8-1
- text： 文本串 2^16-1
-
时间日期
- date：YYYY-MM-DD 日期
- time：HH:mm:ss 时间
- datetime：YYYY-MM-DD HH:mm:ss 日期时间
- timestamp：1970.1.1—现在的毫秒数 时间戳
- year：年份
-
null
- 没有值，不要使用null进行运算，结果为null无意义。
数据库的字段属性
- UNSIGNED：无符号整数，不能为负。
- ZEROFILL：0填充，不足位数使用0填充。
- AUTO_RNCREMENT：自动增长。
- NOT NULL：不允许为空。
- DEFAULT：默认值。
- PRIMARY KEY：主键。
数据表引擎
- InnoDB：默认使用。
- MYISAM：早些年使用。
| InnoDB | MYISAM | |
|---|---|---|
| 事务 | 支持 | 不支持 |
| 数据行锁定 | 支持 | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 全文索引 | 不支持 | 支持 |
| 表空间大小 | 较大2倍MYISAM | 较小 |
| 优点 | 安全性高，支持事务等 | 节约空间，速度较快 |
操作数据库
-
创建数据库
CREATE DATABASE [IF NOT EXISTS] TEST; -
删除数据库
DROP [IF EXISTS] TEST; -
使用数据库
USE TEST; -
查看数据库
SHOW DATABASES;
操作数据表
-
新建数据表
CREATE TABLE `STUDENT`( `SID` INT(4) NOT NULL auto_increment COMMENT '学号', `SNAME` VARCHAR(10) NOT NULL DEFAULT'NOT' COMMENT '姓名', `SGENDER` VARCHAR(10) NOT NULL DEFAULT'MALE' COMMENT '性别', `SBIRTHDAY` DATE DEFAULT NULL COMMENT'生日', `SPRONE` BIGINT(18) DEFAULT NULL COMMENT'手机号码', PRIMARY KEY(`SID`) ); -
查看创建语句
SHOW CREATE TABLE `STUDENT` --返回结果： CREATE TABLE `student` ( `SID` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `SNAME` varchar(10) NOT NULL DEFAULT 'NOT' COMMENT '姓名', `SGENDER` varchar(10) NOT NULL DEFAULT 'MALE' COMMENT '性别', `SBIRTHDAY` date DEFAULT NULL COMMENT '生日', `SPRONE` bigint(18) DEFAULT NULL COMMENT '手机号码', PRIMARY KEY (`SID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 -
修改数据库表
--修改表名称 ALTER TABLE `STUDENT` RENAME AS `NEWSTU` --添加表字段 ALTER TABLE `STUDENT` ADD `AGE` INT(11) --修改表字段名 ALERT TABLE `STUDENT` CHANGE AGE NEWAGE --修改表字段约束 ALTER TABLE `STUDENT` MODIFY `AGE` VARCHAR(11) --删除表字段 ALTER TABLE `STUDENT` DROP `AGE` -
删除表
DROP TABLE `TEACHER` -
显示表结构
DESC `STUDENT`;
MySQL数据管理
外键
--添加外键
--需要两表之间的字段名类型等数据相同
ALTER TABLE `student`
add CONSTRAINT `fk_gid` FOREIGN KEY(`gid`) REFERENCES `grade`(`gid`);
DML语言（数据操作语言）
- insert
- update
- delete
添加
--插入一行
INSERT INTO `grade` (`gname`) VALUES ('一班');
--插入多行
INSERT INTO `grade` (`gname`) VALUES ('三班'),('四班'),('五班'),('六班');
--自动增长的不需要填写
--多行插入用 (), 隔开
修改
UPDATE `grade` SET `gname`= '十班' WHERE `gid` = 1;
删除
--删除表数据
DELETE FROM `grade` WHERE `gid` = 1;
--清空表数据
TRUNCATE `student`;
查询语言
简单查询
--1. 简单查询：
SELECT name1,name2.. FROM tablename;
--2. 去重查询：
SELECT DISTINCT name1 FROM tablename;
--3. 限制数量：
SELECT name1,name2..FROM tablename LIMIT M①;
--注：①检索M条数据
SELECT name1,name2..FROM tablename LIMIT N,M②;
--注：②从N+1开始，检索M条数据
--4. 升序查询：
SELECT name1,name2..FROM tablename ORDER BY name1③；
--注：③根据name1的升序进行排序
SELECT name1,name2..FROM tablename ORDER BY name1,name2④;
--注：④根据name1升序进行排序，当name1值相同时根据name2升序进行排序
--5. 降序查询：
SELECT name1,name2..FROM tablename ORDER BY name1 DESC⑤;
--注：⑤根据name1的降序进行排序（升降序可组合使用）
过滤查询
--1. 过滤数据：
SELECT name1,name2..FROM tablename WHERE name1=””①;
--注：①条件操作符：=等于，<>/!=不等于，<小于，>大于，<=小于等于，>=大于等于
SELECT name1,name2..FROM tablename WHERE name1 BETWEEN 1 AND 5②;
--注：②BETWEEN在指定的两个值范围之间
SELECT name1,name2..FROM tablename WHERE name1 IS NULL③;
--注：③ IS NULL空值检查
--2. 数据过滤：
SELECT name1,name2..FROM tablename WHERE name1=””AND name=””④;
--注：④在数据过滤中提供了多重过滤，AND并且，OR或者，AND计算次序更高，所以先计算OR再计算AND情况下OR部分需添加()。OR/AND中尽量使用()
SELECT name1,name2..FROM tablename WHERE name1 IN (1,2,3..)⑤;
--注：⑤IN（在…中）和OR类似，但速度更快，语句更易读
SELECT name1,name2..FROM tablename WHERE name1 NOT IN (1,2,3..)⑥;
--注：⑥NOT将会否定后面的条件，类似取集合之外的数据(<,=..无法跟在NOT后)
--3. 通配符查询LIKE：如果没有通配符LIKE和=效果一样
SELECT name1,name2.FROM tablename WHERE name1 = LIKE ‘wang%’⑦;
SELECT name1,name2 FROM tablename WHERE name1 = LIKE ‘%wang%’⑧;
SELECT name1,name2..FROM tablename WHERE name1 LIKE “_wang”⑨;
--注：⑦检索name1中wang开头的字段。
--⑧检索name1中含有wang的字段。%无法检索null值
--⑨ _代表一个字符。因为通配符搜索较慢，所以不到万不得已不要使用。
--4. 使用正则表达式REGEXP
SELECT name1 from tablename where REGEXP ‘正则表达式’;
--5. 拼接字段：Concate
SELECT Concate(name1,’(‘,name2,’)’) FROM tablename WHERE ORDER BY name1⑩;
--注：⑩查询出结果以name1(name2)出现，name2可多值查询。最后一定要进行排序
--6. 使用别名：
SELECT name1 AS (bieming) FROM tablename;
--7. 计算查询：
SELECT name1,num*sum FROM tablename;
使用函数/子查询
--1. 函数处理：MYSQL提供一些预设的函数可供使用。函数名(列名)使用经常使用函数处理时间类型BETWEEN：
SELECT name1
FROM tablename
WHERE Date(date) BETWEEN① ‘2015-09-01’ AND ‘2015-09-30’;
--注：①BETWEEN可查询出一个范围内的时间。
SELECT name1 FROM tablename WHERE Year(date)=2015 AND Month(date)=9②;
--注：②使用Year(),Month()函数还可以查询指定年份和月份。
--2. 聚集函数：和前面函数使用方法相同，针对数据列出
AVG()平均值,SUM()和,COUNT()行数,MAX()最大值,MIN()最小值
--3. 针对不同值的聚集函数：DISTINCT
SELECT AVG(DISTINCT price) FROM tablename WHERE name_id=1③;
--注：③计算表中name id为1的数据的price的去除重复值的平均值，聚集函数可组合使用
--4.分组查询/子查询：
--1. 分组查询：
SELECT name1,COUNT() FROM tablename GROUP BY name1①;
--注：①分组查询的SELECT后的字段不能乱写，一般是根据分组的字段和汇总数据。
--2. 子查询：
SELECT name1 FROM tablename WHERE name IN (SELECT ….)②;
--注：②查询是以内往外执行的，先查询出子查询的结果，之后被外面的查询使用。也可以将汇总出的子查询数据放在SELECT后。
多表查询
--1. 内链接①：有两种写法，推荐第二种。
--写法一：
SELECT name1,name2
FROM tablename1,tablename2
WHERE tablename1.id=tablename2.id;
--写法二：
SELECT name1,name2
FROM tablename1 INNER JOIN tablename2
ON tablename1.id=tablename2.id;
--：①内连接不以哪个表为主表，只返回符合条件值。
--2. 外链接:分主副表可相互转化
--左联结②：
SELECT name1,name2
FROM tablename1 LEFT OUTER JOIN tablename2
ON tablename1.id=tablename2.id;
--注：②以左表为基准，显示全部内容，右表不匹配的置为null
--右联结③：
SELECT name1,name2
FROM tablename1 RIGHT OUTER JOIN tablename2
ON tablename1.id=tablename2.id;
--注：③以右表为基准，显示全部内容，左表不匹配的置为null
--1. 组合查询：
SELECT * FROM tablename WHERE id=1;
UNION①
SELECT * FROM tablename WHERE name=”123” ORDER BY id;
--注：① UNION将一个SQL的WHERE条件分为多个SQL,排序在最后一条SQL。
--2. 全文本搜索
--首先启用全文本搜索，在创建表时启用FULLTEXT()。
--方法一：在查询语句中使用Match()②和Against()函数使用全文本搜索
SELECT 列1 FROM 表名 WHERE Match(列1) Against(‘关键字’);
--方法二：使用查询扩展，可查询出与关键字接近的字段的全文本
SELECT 列1 FROM 表名 WHERE Match(列1) Against(‘关键字’ WITH QUERY EXPANSION);
--方法三：使用布尔文本搜索,添加更多查询限制（书P128）
SELECT 列1 FROM 表名 WHERE Match(列1) Against(‘关键字 –rope*’ IN BOOLEAN MODE);
--注：②Match()中传入的值需与FULLTEXT()中定义的相同。
事务
要么都成功，要么都失败将一组SQL放在一个批次中去执行
- ACID原则
- 原子性：针对同意个事务，要么同时完成，要么同时失败（要么都成功，要么都失败）。
- 一致性：事务前后的数据完整性保持一致（转账前后总金额没有变）。
- 持久性：事务没有提交就恢复原状，提交成功就持久化到数据库了。
- 隔离性：多个用户同时操作都互不干扰，每个用户都有单独的事务。
- 脏读：一个事务读取了另一个事务还没提交的数据。
- 幻读：一个事务内读到了其他事务插入的数据，导致读取前后不一致。
- 不可重复读：一个事务内读取表中的数据，多次读取结果不同。
执行事务过程
- 关闭自动提交
- 开启一个事务
- 成功提交数据；失败就回滚至原数据
- 事务执行成功再开启自动提交
-- 模拟转账
--关闭自动提交
SET AUTOCOMMIT = 0;
--开启一个事务（一组事务）
START TRANSACTION;
--执行SQL语句
UPDATE XXX SER XXX;
UPDATE XXX SER XXX;
--提交事务
COMMIT;
--回滚事务（不成功的话回滚）
ROLLBACK;
--开启自动提交
SET AUTOCOMMIT = 0;
索引
索引是帮助MySQL高效获取数据的数据结构。
索引的分类
-
主键索引（PRIMARY KEY）
- 唯一标识
-
唯一索引（UNIQUE KEY）
- 避免重复的列
-
常规索引（KEY/INDEX）
-
全文索引（FULLTEXT）
--显示所有索引
SHOW INDEX FROM `表名`
--增加索引
ALTER TABLE `表名` ADD KEY INDEX `索引名`(`列名`)
索引在小数据量效果不大，大数据下区别十分明显。
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要索引
- 索引一般加在常用来查询的字段上
数据库备份
-
在命令行执行操作
-- 导出:mysqldump -h主机 -u用户名 -p密码 库名 表名1 表名2... >本地目录 mysqldump -hlocalhost -uroot -proot school student >D:/a.sql -- 导入:mysql -u用户名 -p密码 库名 < 文件地址 mysql -uroot -proot school < D:/a.sql
数据库设计
数据库复杂时就需要设计了
糟糕的数据库：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦，异常【屏蔽物理外键】
- 程序性能差
良好的数据库：
- 节省内存
- 保证数据完整
- 方便系统开发
三大范式
第一范式：原子性—保证每一列不可再分。
第二范式： 满足第一范式的前提下，每张表只描述一件事情。
第三范式：满足前两范式的基础上，每张表的数据都与主键直接相关，不能间接相关。
