入门Mysql（弱鸡待更）

数据库常识

Mysql数据结构初解

页是承装行的容器，也是mysql写入提取出磁盘的最小单位。而区是向磁盘申请连续数据块，这样页与页之间保持连续，可以实现连续读。段用来给表空间划分功能，分为数据段，索引段，回滚段等。表空间是一张表所有数据的集合，是表的代表。
所以从大到小分别是：表空间，段，区，页

关系型和非关系型数据库

sql数据库：以严格的行列存储数据，通过外键联系表和表的关系
mysql，oracle，sql server

nosql数据库：以对象形式存储数据，关系由对象的属性决定
redis，MongoDB

数据库组成

数据库(硬件)->数据库管理系统(mysql)->数据库应用软件(b站)

SQL语句分类

类别	功能	命令
DDL	管理数据结构	CREATE,ALTER,DROP
DML	管理结构中的详细数据	INSERT,DELETE,UPDATE
DCL	管理数据库	GRANT,COMMIT,ROOLBACK
DQL	查询数据	SELECT

Mysql使用前须知

mysql建表前要给库和表选择字符集和排序规则防止乱码
engine默认为INNODB
mysql尽量用bin下载，否则卸载困难
字段名和表名不区分大小写，应在两边加入`,这样就算字段名和关键字同名时也可以被区别开来

语句

命令行

连接mysql：mysql -uusername -ppassword
修改密码:
刷新：flush privilege
显示：describe

DDL

数据库
增（create）删（drop）用（use）查（show）

DDL必知字段常识

字段类型
数值：int float double decimal（字符串形式浮点数）
字符串：varchar 0-65535字节 text（2^16-1字节）保存大文本
时间日期 datetime(YYYY-MM-DD HH:mm:ss) timestamp(时间戳 1970.1.1到现在的毫秒数) year

字段属性：
unsigned：无符号，必须是整数，必须非负
zerofill：不足位数用0填充
自增：必须是整数，常用于主键
not null:规定该字段不能为零
comment'字段名':用于给该字段取别名

创建数据表基本格式：

CREATE TABLE 表名(
`字段名` 字段类型 字段属性,
...,
primary key(`字段名`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除数据表基本格式：

DROP TABLE IF EXISTS `表名`
/*删除时为了避免错误应优先判断是否存在*/

查看数据表信息

SHOW TABLE STATUS FROM `数据库名` WHERE NAME='表名'

改变字段名或类型

--无法避开字段类型时更改字段名
Alter `表名` CHANGE `旧字段名``新字段名` 字段类型

修改外键

1

ALTER TABLE `外键的表`
ADD FOREIGN KEY (`外键字段`)
REFERENCES `主键的表`(`主键字段`)

2
SQLYog：在具体表/栏目/任意字段右键/管理列/外部键可加
根据名/外键字段/主键数据库/主键表/主键填好

DML

插入记录

INSERT INTO `表名`(可省`字段名`,...) VALUES(当前记录字段值,...),(...),...

插入datetime方法:按照格式输入后加引号转成字符串，如'2013-12-11 09:00:00'

修改记录

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

DQL(重点)

总体格式（必须按该顺序）

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

简单查询

SELECT * FROM `表名`
SELECT `字段名` AS `显示字段名` FROM `表名`
select `字段名`+1 FROM `表名`

Distinct

用于开头，将完全相同的搜索记录去除（一般用于搜索少量字段时）

where查询

> >= < <= = !=
BETWEEN...AND... IN(...) 
LIKE % #0到多个字符   
	 _ #1个字符
IS NULL   IS NOT NULL
--多条件时：
AND OR NOT

联表查询

SELECT *
FROM `student` AS s
LEFT/RIGHT/INNER JOIN `subject` r
ON r.StudentNo=s.StudentNo

搜索的字段值为null或空，也会根据此填上相应字段
LEFT：将左表所有含StudentNo值的记录显现
RIGHT：将所有右表记录显现
INNER：左右两边都必须有StudentNo属性

注意"and"条件
left/right join table on key1=key2 and table.field = value 先筛从表，再连主表
inner join on table on key1=key2 and table.field = value 先连从表，再筛

自连接

SELECT p.`categoryName` AS '父',s.categoryName AS '子'
FROM `category` p,`category` s
WHERE p.categoryid=s.pid

当同张表以表中某些记录值作为筛选条件时用该方法
理解：根据where条件先合出一张表，然后再通过搜索字段缩短表

联表与自连接

mysql倾向于联表查询的优化，所以大数据量尽量用联表查询

ORDER BY

尾部加入asc用于升序排序
尾部加入desc用于降序排序

在排序中相同关键值的记录可以再根据排序调整顺序，如下实现两次排序

ORDER BY StudentResult ASC,StudentNo DESC

order后的组无法进行where筛选，一般使用having()进行筛选

分页

LIMIT 开始记录序号(从0开始),页面容量

函数

常用函数

###常用函数
 SELECT NOW();   /*获取当前日期和时间*/
  -- 获取年月日,时分秒
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());
  SELECT VERSION();  /*版本*/
 SELECT USER();     /*用户*/

聚合函数

count(1)/ count(*)：计算记录数，不忽略null字段，有区别
count(字段)：计算拥有该字段的记录数，忽略null字段

AVG()：平均值
MIN()：最小值
MAX()：最大值
SUM()：总和

GROUP BY 字段名:控制聚合函数的范围而非整体聚合,具体示范如下
HAVING :对于聚合后列表进行筛选，where无法对聚合后的字段进行筛选
--以下可以获得不同科目的平均分
SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` s
ON s.SubjectNo = r.SubjectNo
GROUP BY s.subjectNo
HAVING(平均分>80)

MD5函数

MD5：一种常用加密方法，将一个字符串加密成一个固定的字符串

 UPDATE `tab` SET pwd = MD5(pwd) where `id` = 1;
 INSERT INTO `tab` VALUES(2,'user',MD5('123456'));
 SELECT * FROM `tab` WHERE `pwd`=MD5('123456');

三大范式

第一范式

每个字段值都不再能分割

第二范式

当主键为多值联合时，每一字段值都和完整的主键直接相关，意思是说主键中不能出现可有可无的部分

第三范式

所有的非主属性不依赖于其他的非主属性，这是为了防止数据冗余。

注意

阿里：“每条sql联表查询不应超过三张表”，在真正工作场景中，往往因为对效率的要求而偏离三大范式，所以三大范式只是一个参考