《深入浅出Mysql》阅读笔记

SQL分类

• DDL（Data Definition Languages）语句：数据定义语句。关键词：create、drop、alter等
• DML（Data Manipulation Languages）语句：数据操纵语句。关键词：insert、delete、update和select等
• DCL（Data Control Languages）语句：数据控制语句。关键词：grant、revoke等

自动创建的Databases

• information_schema:主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息
• cluster:存储了系统的集群信息
• mysql:存储了系统的用户权限信息
• test:系统自动创建的测试数据库，任何用户都可以使用

DDL语句

# 查看tablename表定义
>desc tablename;
# 查看tablename表全面信息，\G表示按照字段竖直排列
>show create table tablename \G;

# 修改tablename表的name字段定义为varchar(20)
>alter table tablename modify name varchar(20);
# 增加tablename表字段age，类型为int(3)
>alter table tablename add column age int(3);
# 删除tablename表字段sex
>alter table tablename drop column sex;
# 修改tablename表的id_old字段的名称为id_new
>alter table tablename change id_old old_new;
# 修改字段排列顺序，将id_new放在age后面
>alter table tablename add id_new after age;
# 修改字段排列顺序，将id_new放在最前面，此处需要加上字段类型
>alter table tablename modify id_new int(3) first;
# 修改tablename表名为tablename_new
>alter table tablename rename tablename_new;

DML语句

# 把tablename表中的记录按照age字段排序只显示前3条，默认升序
>select * from tablename order by age limit 3;
# 把tablename表中的记录按照age、sal字段降序排序，只显示第一条到第四条
>select * from tablename order by age, sal desc limit 1, 3;

# 聚合数据，group by
# 统计tablename的总条数，用count(1)字段显示
>select count(1) from tablename;
# 统计tablename的age，显示统计结果
>select age,count(1) from tablename group by age;
# 统计tablename的age，显示统计结果，并且统计总条数
>select age,count(1) from tablename group by age with rollup;
# 统计age大于10的条数
>select age,count(1) from tablename group by age having count(1) > 10;
# 统计最大age、age总和、最小age
>select max(age), sum(age), min(age) from tablename;

# 表连接
# 以table1中的age字段等于table2中的age字段为条件，显示name和age字段，分别来自table1和table2
>select name, age from table1, table2 where table1.age=table2.age; 
# 左连接，与上面的区别在于包含所有左边表的记录（甚至右边表中没有和它匹配的记录）
>select name, age from table1 left join table2 on table1.age=table2.age;
# 右连接
>select name, age from table2 right join table1 on table1.age=table2.age;

# 子查询，括号里的查询结果再次查询
>select * from table1 where age in (select age from table2);
# 子查询记录唯一可以使用=代替in
>select * from table1 where age = (select age from table2);
# 或
>select * from table1 where age = (select age from table2 limit 1);
# 有些时候也可以用表连接来代替

子查询和表连接之间的转换主要应用在两个方面：

• Mysql4.1以前的版本不支持子查询，需要用表连接来实现子查询的功能
• 表连接在很多情况下用于优化子查询

# 记录联合查询
 >select name from table1
->union all
->select name from table2;
# 去重复记录查询
 >select name from table1
->union
->select name from teble2;

DCL语句

# 创建数据库用户user1，给db1数据库的select/insert权限，密码是123，允许从本地登陆
>grant select, insert on db1.* to 'user1'@'localhost' identified by '123'; 
# 收回user1的insert权限
>revoke insert on db1.* from 'user1'@'localhost';

常用函数

函数	功能
CANCAT(S1,S2,…Sn)	连接S1，S2，…Sn为一个字符串
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符串长的子串替换为字符串instr
LOWER(str)	将字符串str中所有字符变为小写
UPPER(str)	将字符串str中所有字符变为大写
LEFT(str,x)	返回字符串str最左边的x个字符
RIGHT(str,x)	返回字符串str最右边的x字符
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左侧空格
RTRIM(str)	去掉字符串str右侧空格
REPEAT(str,x)	返回str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去掉字符串行尾和行
SUBSTRING(str,x,y)	返回从字符串str x位置起y个字符长度的子串
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最大整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模
RAND()	返回0到1内的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date的UNIX时间戳
FROM_UNIXTIME	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时值
MINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数
IF(value,t f)	如果value是真，返回t；否则返回f
IFNULL(value1,value2)	如果value1不为空返回value1，否则返回value2
CASE WHEN [value1] THEN[result1]…ELSE[default]END	如果value1是真，返回result1，否则返回default
CASE [expr] WHEN [value1] THEN [result1]…ELSE[default]END	如果expr等于value1，返回result1，否则返回default
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登陆用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA(num)	返回数字代表的IP地址
PASSWORD(str)	返回字符串str的加密版本
MD5()	返回字符串str的MD5值

存储引擎

# 查看当前默认的存储引擎
>show variables like 'table_type';
# 查看当前数据库支持的存储引擎
>SHOW ENGINES \G
# 或
>SHOW VARIABLES LIKE 'have%';
# 更改表ai的存储引擎为InnoDB
>alter table ai engine = innodb;

数据类型的区别

CHAR与VARCHAR

>INSERT INTO table_name VALUES ('ab    ', 'ab    ')
# VARCHAR可以保留空格，CHAR忽略空格，CHAR处理速度快

BLOB与TEXT

BLOB：保存二进制数据，比如照片

TEXT：保存字符数据（长），比如文章或者日记

• 使用合成的（Synthetic）索引来提高BLOB或TEXT的查询性能。
• 减少BLOB和TEXT的使用
• 避免检索大型的BLOB或TEXT
• 把BLOB或TEXT分离到单独的表中

浮点数与定点数

浮点数：FLOAT、DOUBLE（或REAL）

定点数：DECIMAL （或NUMBERIC）

1.23456到float（8，1）–>1.2

1.25456到float（8，1）–>1.3

• 对数据精度要求比较高的应用中（比如货币）使用定点数
• 浮点数存在误差问题
• 避免使用浮点数做比较
• 注意浮点数中一些特殊值的处理

日期类型

DATE、TIME 、DATETIME、 TIMESTAMP

• 只需要记录年份，使用YEAR，而不需要使用DATE 类型。
• 记录年月日时分秒，并且记录年份比较久远，那么最好用DATETIME
• 如果是供给不同时区的用户使用，那么最好使用TIMESTAMP

字符集

# 查看所有可用的字符集的命令
>show character set;
# 可以显示所有的字符集喝该字符集默认的校对规则
>desc information_schema.character_sets; 
# 查看相关字符集的校对规则
>SHOW COLLATION LIKE 'gbk%';

在my.cnf中设置

[mysqld]
default-character-set=gbk
# 或在启动选项中指定:
mysqld --default-character-set=gbk
# 或在编译的时候指定：
./configure --with-charset=gbk

查看当前服务器的字符集和校对规则

>show variables like 'character_set_server';