Mysql学习笔记

1. 查看sql基本信息
   1. \s
   2. 查看当前数据库连接、字符编码、端口号、协议、版本
2. 乱码问题：
   1. mysql有6处使用字符集:client,connection,database,results,server ,system。
   2. client是客户端使用的字符集。
   3. connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。      
   4. database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。   
   5. results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。      
   6. server是服务器安装时指定的默认字符集设定。      
   7. system是数据库系统使用的字符集设定。
3. 设置mysql的字符集为utf8后默认存储的数据，以及读取出来的数据为utf8，在中文的系统编码为gb2312插入数据会为乱码，需按一下设置set character_set_client=gb2312。设置后能存入的中文会自动转变为国标编码，但查询结果集仍为utf8，为了设置命令行显示正常需设置结果集编码为国标编码方法为set character_set_results=gd2312
4. 数据库的备份与恢复
   1. 备份执行windows命令：mysqldump
   2. 恢复时只能恢复库里的数据，无法恢复库。所以恢复前必须先创建库
5. mysql的bit 存储值限定为 0或1，位数最大为64位。例如 bit(8)的存储方式如下：
   1. 当存储值为 b'value'时，按二进制值存，比如b'11'则数据库实际为 00000011（不足8为左侧加0）
   2. 当存储值为 value时，自动转换成二进制值存，比如 11则数据实际为01111000 （11的二进制值）
   3. value可以是字符或字符串，但如果转换成二进制位数超过限制，无法存储。
6. mysql数据类型

分类	数据类型	说明
数值类型	BIT(M) TINYINT [UNSIGNED] [ZEROFILL] BOOL，BOOLEAN SMALLINT [UNSIGNED] [ZEROFILL] INT [UNSIGNED] [ZEROFILL] BIGINT [UNSIGNED] [ZEROFILL] FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] Decimal(M,D)	位类型。M指定位数，默认值1，范围1-64 带符号的范围是-128到127。无符号0到255。 使用0或1表示真或假 2的16次方 2的32次方 2的64次方 M指定显示长度，d指定小数位数 表示比float精度更大的小数 定点数，存储货币等精度要求高的数据
文本、二进制类型	CHAR(size) char(20) VARCHAR(size) varchar(20) BLOB LONGBLOB TEXT(clob) LONGTEXT(longclob)	固定长度字符串 可变长度字符串 二进制数据 大文本
时间日期	DATE/DATETIME/TimeStamp	日期类型(YYYY-MM-DD) (YYYY-MM-DD HH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间

7. MySQL的四种BLOB类型,为二进制文件存储类型，比如声音、图片等； 
   1. TinyBlob  最大 255B
   2. Blob  最大 65K 
   3. MediumBlob  最大 16M 
   4. LongBlob  最大 4G
8. Delete 删除：truncate和delete都可以删除表数据，truncate是批量全删，且返回0，delete可以带where条件删除，且返回删除条数。无where的delete与truncate功能一样，并将自增字段数还原为1；
9. Select 查询：
   1. distinct：查询去重数据 select distinct name from student;
   2. as:使用别名 select chinese as '语文',english as '英语' from student;
10. where条件表达式：select chinese+english+math as total from student;
    1. where条件：select name,chinese,english,math from student where english>90;
    2. wehere表达式 between定义区间值：select name,chinese,english,math from student where english between 80 and 90;
    3. where表达式in定义列表值：select name,math from student where math in (80,90,91);
11. where 表达式LIKE与%
    1. %表示多个字符 %AA为以AA结尾，AA%为以AA开头，%AA%包含AA
    2. _表示单个字符，与%用法相同。二者一起配合使用吧如 _a%标识以单个字符+a开头
12. order by 排序：select * from student order by math desc;
    1. order by 支持列名以及重命名的列名
    2. asc升序，desc降序
13. count 合计函数：select count(*) from student
    1. count只统计有数据的行
    2. count(*)、count(列名)、count(表达式)
14. sum求和函数：select sum(chinese),sum(math),sum(english) from student;
15. avg求平均函数：select avg(chinese) from student;
16. max/min函数：select max(math),min(math) from student;
17. group by 与having：select name,sum(math),sum(english),sum(chinese) from student group by name;
    1. group by 按某列分组，组合函数使用时则分别按组进行函数运算；
    2. 与sum组合使用则分组汇总，与avg则分组求平均，与count则计算每组数量
    3. 常与having组合使用，having与where类似，但having可以和sum组合使用
18. 日期与时间函数

ADDTIME (date2 ,time_interval )	将time_interval加到date2
CURRENT_DATE ( )	当前日期
CURRENT_TIME ( )	当前时间
CURRENT_TIMESTAMP ( )	当前时间戳
DATE (datetime )	返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type )	在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )	在date2上减去一个时间
DATEDIFF (date1 ,date2 )	两个日期差
NOW ( )	当前时间
YEAR|Month|DATE (datetime )	年月日

19. 字符函数

CHARSET(str)	返回字串字符集
CONCAT (string2 [,... ])	连接字串
INSTR (string ,substring )	返回substring在string中出现的位置,没有返回0
UCASE (string2 )	转换成大写
LCASE (string2 )	转换成小写
LEFT (string2 ,length )	从string2中的左边起取length个字符
LENGTH (string )	string长度
REPLACE (str ,search_str ,replace_str )	在str中用replace_str替换search_str
STRCMP (string1 ,string2 )	逐字符比较两字串大小,
SUBSTRING (str , position [,length ])	从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 ) trim	去除前端空格或后端空格

20. 数字函数

ABS (number2 )	绝对值
BIN (decimal_number )	十进制转二进制
CEILING (number2 )	向上取整
CONV(number2,from_base,to_base)	进制转换
FLOOR (number2 )	向下取整
FORMAT (number,decimal_places )	保留小数位数
HEX (DecimalNumber )	转十六进制
LEAST (number , number2 [,..])	求最小值
MOD (numerator ,denominator )	求余
RAND([seed])	RAND([seed])

21. 定义主键约束
    1. primary key:不允许为空，不允许重复
    2. 主键可以为多个方法为：primary key（列1，列2）
    3. 删除主键：alter table tablename drop primary key ;
22. 定义主键自动增长 auto_increment
23. 定义唯一约束 unique
24. 定义非空约束 not null
25. 各个约束之间直接空格隔开，无需“,”
26. 定义外键约束
    1. 定义方法，单独成行 constraint ordersid_FK foreign key(ordersid) references orders(id)
    2. 添加方法：alter table 表名 add constraint 外键名 foreign key(字段名) references 外键表(外键表字段名);
    3. 删除方法：alter table wife drop foreign key 外键名;
    4. 外键值约束 on delete/on update 可设置与外键表值的关联关系
       1. restrict(约束):当在父表（即外键的来源表）中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
       2. no action:意思同restrict.即如果存在从数据，不允许删除/更新主数据。
       3. cascade(级联):当在父表（即外键的来源表）中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录。
       4. set null:当在父表（即外键的来源表）中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）
27. 表设计模式
    1. 一对一： A表主键为B表的主键的外键
    2. 一对多：A表主键为B表某个字段的外键
    3. 多对多：A表、B表、中间表，且中间表有两个外键，分别关联A表和B表
    4. 自链表：同一张表，某字段的外键为该表中的其他一个字段，常作为树目录结构
    5. 外键必须为主键，否则无法添加