《深入浅出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';