SQL基础应用
命令行连接数据库
-u：用户名
-p：密码
-S：socket文件
-h：ip
-P：端口
-e：直接执行的命令
-N: 在命令行直接运行sql时,不显示表格框
<：恢复数据
常用字符集
> show charset;
utf8：3个字节
utf8mb4：4个字节，支持emoji
常用排序规则
utf8mb4_general_ci：大小写不敏感
utf8mb4_bin：大小写敏感
库的创建和删除
#创建数据库
> CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;
#查看数据库
> SHOW DATABASES;
> SHOW CREATE DATABASE zabbix;
#删除数据库
> DROP DATABASE oldguo;
show命令
show databases; 查看数据库名
show tables; 查看表名
show create database xx; 查看建库语句
show create table xx; 查看建表语句
show processlist; 查看所有用户连接情况
show charset; 查看支持的字符集
show collation; 查看所有支持的校对规则
show grants for xx; 查看用户的权限信息
show variables like '%xx%' 查看参数信息
show engines; 查看所有支持的存储引擎类型
show index from xxx 查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs 查看二进制日志的列表信息
show binlog events in '' 查看二进制日志的事件信息
show master status ; 查看mysql当前使用二进制日志信息
show slave status\G 查看从库状态信息
show relaylog events in '' 查看中继日志的事件信息
show status like '' 查看数据库整体状态信息
增删改
- DESC
#查看表结构
DESC testdb.testtb;
- DROP
#删除指定数据库下指定表
DROP TABLE testdb.testtb;
#如果数据库存在就删掉他
DROP DATABASE IF EXISTS testdb;
- ALTER
#删除表中指定字段
ALTER TABLE testtb DROP SEX;
#更改表中字段
ALTER TABLE testdb.testtb CHANGE sex SEX varchar(1) AFTER name;
#在指定表中添加字段
ALTER TABLE testdb.testtb ADD sex varchar(1);
- INSERT
#在表中所有字段都插入数据
INSERT INTO testtb VALUE ('CCC','');
#在表中指定字段插入数据
INSERT INTO testtb (name,age) VALUE ('AAA',11),('BBB',2);
- DELETE
#指定条件删除行
DELETE FROM students WHERE Name='lee';
- UPDATE
#指定位置更新表中内容
UPDATE testtb SET name='DDD' WHERE name='AAA';
查询语句
- 优先级
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit
- 普通查询
#查询表中 name和population的值
SELECT NAME ,population FROM city;
- 条件查询
#SELECT配合WHERE 子句使用
#查询中国所有的城市名和人口数
SELECT NAME,population FROM city WHERE countrycode='CHN';
#世界上小于100人的城市名和人口数
SELECT NAME,population FROM city WHERE population<100;
- 逻辑操作符
#1. 查询中国人口数量大于1000w的城市名和人口
SELECT NAME,population FROM city WHERE countrycode='CHN' AND population>8000000;
#2. 查询中国或美国的城市名和人口数
SELECT NAME,population FROM city WHERE countrycode='CHN' OR countrycode='USA';
#3. 查询人口数量在500w到600w之间的城市名和人口数
SELECT NAME,population FROM city WHERE population>5000000 AND population<6000000;
- 模糊查询
#where 配合 like 子句 模糊查询
#查询一下contrycode中带有CH开头,城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
- in语句
#where 配合 in 语句
#查询中国或美国的城市信息
SELECT NAME,population FROM city WHERE countrycode='CHN' OR countrycode='USA';
- 分组查询
#GROUP BY
#根据指定的条件进行分组,分组后对组内的某一列的所有数据进行什么样的计算。avg()平均数，max()最大数，min()最小数，count()统计，sum()求和
#1. 统计每个国家,城市的个数
SELECT countrycode ,COUNT(id) FROM city GROUP BY countrycode;
#2. 统计每个国家的总人口数.
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;
#3. 统计每个 国家 省 的个数
SELECT countrycode,COUNT(DISTINCT district) FROM city GROUP BY countrycode;
#4. 统计中国 每个省的总人口数
SELECT district, SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ;
#5. 统计中国 每个省城市的个数
SELECT district, COUNT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district ;
#6. 统计中国 每个省城市的名字列表GROUP_CONCAT()
SELECT district, GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district ;
- 分组过滤
# HAVING
#以Gender性别分组，在students表中，分别查询男女年龄的平均值，并将平均值命名为age，显示出平均值大于20的
SELECT Avg(Age) AS age,Gender FROM students GROUP BY Gender HAVING age>20;
- 排序
#SELECT 配合 ORDER BY 子句
#1. 统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC ;
- 限制查询个数
#SELECT 配合 LIMIT 子句
#LIMIT M,N：跳过M行,显示一共N行
#LIMIT Y OFFSET X: 跳过X行,显示一共Y行
#1. 统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3 OFFSET 0;
#或
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3
- 联合查询
#union 和 union all 作用: 多个结果集合并查询的功能
#需求: 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
#改写为:
SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA';
#union 和 union all 的区别 ?
union all 不做去重复
union 会做去重操作
- 多表连接查询
#1. 统计zhang3,学习了几门课
SELECT student.sname,COUNT(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname='zhang3';
#2. 查询zhang3,学习的课程名称有哪些?
SELECT student.sname,GROUP_CONCAT(course.cname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3' GROUP BY student.sname;
#3. 查询oldguo老师教的学生名和个数.
SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' GROUP BY teacher.tname;
#4. 查询oldguo所教课程的平均分数
SELECT teacher.tname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno WHERE teacher.tname='oldguo' GROUP BY sc.cno;
#5. 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname,course.cname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno GROUP BY teacher.tname,course.cname ORDER BY AVG(sc.score)
#6. 查询oldguo所教的不及格的学生姓名
SELECT teacher.tname,student.sname,sc.score FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' AND sc.score<60
#7. 查询所有老师所教学生不及格的信息(扩展)
SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score)) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE sc.score<60 GROUP BY teacher.tno
- 别名
#表别名 : 表别名是全局调用的.
SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tno
#列别名：列别名可以被 having 和 order by 调用
SELECT t.tname as 讲师名 ,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) as 不及格的 FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tno
元数据
例子：
USE information_schema;
DESC TABLES;
-- 1. 显示所有的库和表的信息
SELECT table_schema,table_name FROM information_schema.tables;
-- 2. 以以下模式 显示所有的库和表的信息
-- world city,country,countrylanguage
SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;
-- 3. 查询所有innodb引擎的表
SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables
WHERE ENGINE='innodb';
-- 4. 统计world下的city表占用空间大小
-- 表的数据量=平均行长度*行数+索引长度
-- AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema='world' AND table_name='city';
-- 5. 统计world库数据量总大小
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';
-- 6. 统计每个库的数据量大小，并按数据量从大到小排序
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC ;
索引
- 辅助索引
提起索引列的所有值,进行排序
将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
在叶子节点中的值,都会对应存储在主键ID
- 聚集索引
mysql会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的
MySQL进行存储数据时,会按照聚集索引列值的顺序,有序存储数据行
聚集索引直接将原表数据页作为叶子节点,然后提取聚集索引列上下生成枝节点和根节点
- 语句
1.查看 SHOW {INDEX | INDEXES | KEYS}{FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
实例：SHOW INDEX IN classes;
2.创建 CREATE INDEX index_name ON tbl_name (index_col_name,...);
实例：CREEATE INDEX Name ON students(Name);
3.创建 ALTER TABLE tbl_name ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
实例：ALTER TABLE students ADD INDEX(Age);
4.删除 DROP INDEX index_name ON tbl_name
实例： DROP INDEX Age ON students;
索引应用规范
- 建立索引原则
1.建表必须有主键,一般是无关列,自增长
2.经常作为where条件列order by,group by,join on,distinct的条件
3.最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
4.列值长度较长的索引列,建议使用前缀索引
5.降低索引条目,一方面不要创建没用的索引,清理不常使用的索引
6.索引维护要避开业务繁忙期
7.小表不建索引
- 不走索引的情况
1.没有查询条件,或查询条件没有建立索引
2.查询结果集是原表中的大部分数据25%以上
3.索引本身失效,统计数据不真实
4.查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+ , - , * , / , !等)
5.隐式转换导致索引失效
6.<>,not in不走辅助索引
7.like "%aa"百分号在前面的不走索引
8.联合索引
初学linux，每学到一点东西就写一点，如有不对的地方，恳请包涵！
