MySQL基础篇
MySQL单机安装
安装MySQL
#下载rpm文件
wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
#执行rpm源文件
rpm -ivh mysql-community-release-el6-5.noarch.rpm
#执行安装文件
yum install mysql-community-server
启动MySQL
systemctl start mysqld
卸载MySQL
yum remove -y mysql mysql-libs mysql-common #卸载mysql
rm -rf /var/lib/mysql #删除mysql下的数据文件
rm /etc/my.cnf #删除mysql配置文件
yum remove -y mysql-community-release-el6-5.noarch #删除组件
设置root用户密码
/usr/bin/mysqladmin -u root password 'root'
#没有密码 有原来的密码则加
/usr/bin/mysqladmin -u root -p '123' password 'root'
登录MySQL
mysql -uroot -proot
配置MySQL
vim /etc/my.cnf
[mysqld]
# MySQL设置大小写不敏感:默认:区分表名的大小写,不区分列名的大小写 # 0:大小写敏感 1:大小写不敏感
lower_case_table_names=1
# 默认字符集
character-set-server=utf8
MySQL远程连接授权
grant 权限 on 数据库对象 to 用户
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;--刷新权限
- 命令说明:
- ALL PRIVILEGES :表示授予所有的权限,此处可以指定具体的授权权限。
*.*
表示所有库中的所有表- ‘root’@’%’ : root是数据库的用户名,%表示是任意ip地址,可以指定具体ip地址。
- IDENTIFIED BY ‘mypassword’ :mypassword是数据库的密码
关闭linux的防火墙
systemctl stop firewalld(默认)
systemctl disable firewalld.service(设置开启不启动)
DDL
库
create database 数据库名;
create database 数据库名 character set 字符集;
show databases;
show create database 数据库名;#查看某个数据库的定义的信息:
drop database 数据库名称;
use 数据库名;
表
CREATE TABLE `goods` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`group_id` bigint(20) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
show tables;#查看数据库中的所有表:
desc 表名;#查看表结构:
drop table 表名;
alter table 表名 add 列名 类型(长度) 约束; --添加列.
alter table 表名 modify 列名 类型(长度) 约束; --修改列的类型长度及约束.
alter table 表名 change 旧列名 新列名 类型(长度) 约束; --修改列名.
alter table 表名 drop 列名; --删除列.
rename table 表名 to 新表名; --修改表名
alter table 表名 character set 字符集; --修改表的字符集
DML
insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..); -- 向表中插入某些列
insert into 表 values (值1,值2,值3..); --向表中插入所有列
update 表名 set 字段名=值,字段名=值 where 条件;
delete from 表名 [where 条件];
DQL
排序
select cn.cn_name,cn.en_name ,cn.maintainer
from code_repo.code_new as cn
where
cn.cn_name like "%ms%" or
cn.en_name like "%ms%" or
cn.maintainer like "%ms%"
order by
case
when cn.cn_name like "%ms%" then length(REPLACE(cn.cn_name,'ms',''))/length(cn.cn_name)
when cn.en_name like "%ms%" then length(REPLACE(cn.en_name,'ms',''))/length(cn.en_name)
when cn.maintainer like "%ms%" then length(REPLACE(cn.maintainer,'ms',''))/length(cn.maintainer)
end
#比如说 abcms 在经历 替换以后 变成 abc 比值为 3/5
#以及 amsdsms 在经历 替换以后 变成 ads 比值为 3/7
#可以发现如果结果越小,表示被替换的值就越多,也就是匹配度越高,那么按照order by的升序排列,就会在越前面被显示出来
ASC(升序) DESC(降序)
聚合函数
sum():求某一列的和
avg():求某一列的平均值
max():求某一列的最大值
min():求某一列的最小值
count():求某一列的元素个数
分组
select cid,avg(price) from product group by cid having avg(price)>60;
#聚合函数只能出现select语句中或者having语句中,一定不能出现在where语句中
子查询
LEFT JOIN #主表在它左边
RIGHT JOIN
INNER JOIN #内连接也叫等值连接,不区分主表
CROSS JOIN #交叉连接也叫笛卡尔积连接
LEFT JOIN
on和where条件的区别
- on条件是生成临时表时的条件,无论on的条件是否为真,左表的数据都会返回,只是右表数据这是都会变成null,这是Left Join的特性(Right Join同)
- where是在临时表创建完后根据条件进行筛选
INNER JOIN
Inner Join不像Left Join或Right Join一定要返回左表/右表的所有数据,而是根据指定关系进行表连接得到临时表,最后无论是对左表还是右表进行筛选,on … and …和 on … where …都会对临时表进行筛选
尽量用on过滤掉条件
SQL解析顺序
SELECT DISTINCT < select_list >
FROM< left_table > < join_type >
JOIN < right_table >
ON < join_condition >
WHERE< where_condition >
GROUP BY < group_by_list >
HAVING< having_condition >
ORDER BY < order_by_condition >
LIMIT < limit_number >
顺序如下
-- 行过滤
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table> 第二步和第三步会循环执行
WHERE <where_condition> 第四步会循环执行,多个条件的执行顺序是从左往右的。
GROUP BY <group_by_list>
HAVING <having_condition> --列过滤
SELECT 分组之后才会执行SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition> --排序
LIMIT <limit_number> -- MySQL附加 limit是MySQL的独有语法。
数据类型
数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
基本架构
索引
创建索引
- 单列索引之普通索引
CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
- 单列索引之唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length))
alter table table_name add unique index index_name(column)
- 单列索引之全文索引
CREATE FULLTEXT INDEX index_name ON table(column(length))
alter table table_name add fulltext index_name(column)
- 组合索引
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))
删除索引
DROP INDEX index_name ON table
查看索引
SHOW INDEX FROM table_name
B+树
执行计划
explain select * from tuser;
#展示出来的列
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
- type
#依次从好到差
system #表中只有一行数据或者是空表
const #使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const explain select * from tuser where id=1
eq_ref #关键字:连接字段主键或者唯一性索引 explain select a.id from tuser a left join tdep b on a.dep=b.id;
ref #针对非唯一性索引,使用等值(=)查询非主键。或者是使用了最左前缀规则索引的查询 explain select * from tuser where dep=1;
fulltext
ref_or_null
unique_subquery #用于where中的in形式子查询,子查询返回不重复值唯一值
index_subquery #用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询
range #索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中
index_merge #表示查询使用了两个以上的索引,最后取交集或者并集,常见and
index #条件是出现在索引树中的节点的。可能没有完全匹配索引。
ALL
- extra
using filesort #排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中
using index #查询时不需要回表查询,直接通过索引就可以获取查询的数据
using temporary #表示使用了临时表存储中间结果 MySQL在对查询结果order by和group by时使用临时表
using where #表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。
索引注意事项
- 最佳左前缀法则
- 不要在索引上做计算
- 索引字段使用like不以通配符开头
- 索引字段字符串要加单引号,也就是说类型要写对,否则相当于在索引上做了一次类型转换,导致索引失效
- 索引字段不要使用or