SQL 基础应用

最新推荐文章于 2022-12-31 11:25:25 发布
最新推荐文章于 2022-12-31 11:25:25 发布
阅读量437 收藏
点赞数
分类专栏： 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zwj1834787/article/details/107187804
版权

什么是SQL？

关系型数据库当中通用的查询语言。全名：结构化查询语言。

SQL 标准 （ANSI/ISO）

SQL-89
SQL-92
SQL-99
SQL-03

SQL 常用分类

DDL ： 数据定义语言
DCL ： 数据控制语言
DML ： 数据操作语言

SQL_MODE ?

5.7+ 之后采用的是严格模式。
作用：
为了让我们SQL在执行时更加严谨、有意义，符合常识、逻辑、符合科学等。。。
例子：
1. 日期格式： 2020-07-01 0000-00-00
2. 除法运算： 除数不能为0

查看SQL_MODE:
mysql> select @@sql_mode;
±------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+

设置SQL_MODE:
mysql> set sql_mode=’’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
±-----------+
| @@sql_mode |
±-----------+
| |
±-----------+
1 row in set (0.00 sec)

set sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
±------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+

字符集和校对规则

字符集

mysql> show charset;
utf8
utf8mb4

mysql> show variables like ‘%char%’;
±-------------------------±--------------------------------------------------------------+
| Variable_name | Value |
±-------------------------±--------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /data/app/mysql-5.7.28-linux-glibc2.12-x86_64/share/charsets/ |
±-------------------------±--------------------------------------------------------------+

utf8 和 utf8mb4 区别？
例如：
utf8不完整，emoji表情字符是不支持，utf8mb4是支持的。
根本原因是，utf8 字符最大长度为3字节，utf8mb4是4字节。

校对规则（排序规则）

mysql> show collation;
作用： 影响到了字符串的排序。

数据类型

作用： 约束存储的数据更加有意义，符合对于这个列的定义。

数字类型

	    字节量                  范围

tinyint 1 0~255 -127~128
int 4 0~2^32-1 -2^31 ~2^31-1

字符串类型

char(10)
定长类型的字符串类型。最多存储10个字符。如果存了5个,剩余空间用空格填充。

varchar(10)
变长类型的字符串类型。最多存储10个字符。如果存了5个,按需分配存储空间，另外需要1-2字节，存储字符长度。

怎么选择：一般情况下 变长字符串就用varchar，固定长度一般采用char类型

enum() ： 枚举类型
应用场景： 列中的数据，有限个数的值的时候，并且是有规律。
enum(‘bj’,‘sh’,‘tj’,…)
1 2

时间日期

DATETIME
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

二进制

json

约束和其他表属性

Primary key ： 主键约束 ，要求设置为主键的列，储值时，非空且唯一。每张表只有一个主键。
not null : 非空约束，必须录入值
unique key ： 唯一约束，不能重复值
unsigned ： 数字类型约束，无符号。

default ： 设置默认值，一般配合not null 使用
auto_increment ： 针对数字列，自动增长，一般配合主键
comment ： 列或者表进行注释

DDL 应用

作用： 数据定义语言 。

库定义

#创建库
CREATE DATABASE test CHARSET utf8mb4;
CREATE DATABASE wordpress;

#查询库
SHOW DATABASES;
SHOW CREATE DATABASE test;
SHOW CREATE DATABASE wordpress;
#修改库
ALTER DATABASE wordpress CHARSET utf8mb4;

#删除库(生产禁用！！！！)
DROP DATABASE wordpress;

表定义

#建表
USE test;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(64) NOT NULL COMMENT ‘姓名’,
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘年龄’,
gender ENUM(‘m’,‘f’,‘n’) NOT NULL DEFAULT ‘n’ COMMENT ‘性别’,
intime DATETIME NOT NULL COMMENT ‘入学时间’
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT ‘学生表’;

#查表定义
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;

#修改表定义
– 添加和删除字段
– 1. 在表中添加telnum char(11) not null unique key comment ‘手机号’
ALTER TABLE stu ADD COLUMN telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT ‘手机号’;

– 2. 在sname后添加a列
ALTER TABLE stu ADD COLUMN a INT NOT NULL COMMENT ‘测试列’ AFTER sname;

– 3. 在第一列前添加b列
ALTER TABLE stu ADD COLUMN b INT NOT NULL COMMENT ‘测试列’ FIRST ;

– 4. 删除添加的a,b列
ALTER TABLE stu DROP COLUMN a;
ALTER TABLE stu DROP COLUMN b;

– 5. 修改数据类型
ALTER TABLE stu MODIFY telnum VARCHAR(20) NOT NULL UNIQUE KEY COMMENT ‘手机号’;
ALTER TABLE stu MODIFY telnum VARCHAR(30) NOT NULL UNIQUE KEY COMMENT ‘手机号’;

– 6. 修改列名及数据类型
ALTER TABLE stu CHANGE telnum tel VARCHAR(64) NOT NULL UNIQUE KEY COMMENT ‘手机号’;

#删除表
drop table stu;

DDL语句开发规范

库： CREATE DATABASE test CHARSET utf8mb4;
1. 库名要与业务有关
2. 库名不使用大写字母、数字开头。
3. 不要使用内置关键字
4. 建库要指定字符集。
5. 生产中禁止使用删库操作。

表：
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(64) NOT NULL COMMENT ‘姓名’,
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘年龄’,
gender ENUM(‘m’,‘f’,‘n’) NOT NULL DEFAULT ‘n’ COMMENT ‘性别’,
intime DATETIME NOT NULL COMMENT ‘入学时间’
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT ‘学生表’;

  1. 表名：与业务有关,例如：wp_users，不使用大写字母、数字开头，不要太长（16以下）
  2. 设置存储引擎、字符集、表注释
  3. 表名、列名要使用内置关键字
  4. 列名要有意义，长度（16以下）
  5. 数据类型：合适的、足够的、简短
  6. 每个表要有主键，一般是自增长、无关列数字列。
  7. 每个列尽量是not null ，可以配合default
  8. 每个列要有注释
  9. 修改定义的操作，要在业务不繁忙期间去做。如果紧急可以使用pt-osc 。

DCL

grant
revoke

DML语句

作用

针对表的数据行增删改查。
##种类
insert
update
delete
select

insert 应用

USE test;
DESC student;

标准数据插入方式

INSERT student(id,NAME,age,gender,intime)
VALUES(1,‘zhangs’,18,‘M’,‘2020-07-02 08:30:00’);

SELECT * FROM student;

INSERT INTO
student(id,NAME,age,gender,intime)
VALUES
(2,‘zhang1’,18,‘M’,‘2020-07-01 08:30:00’),
(3,‘zhang2’,19,‘F’,‘2020-07-03 08:30:00’),
(4,‘zhang3’,17,‘M’,‘2020-07-05 08:30:00’),
(5,‘zhang4’,16,‘F’,‘2020-07-06 08:30:00’),
(6,‘zhang5’,15,‘M’,‘2020-07-07 08:30:00’);

#省略写法
INSERT INTO
student
VALUES
(7,‘zhang6’,19,‘M’,‘2020-06-07 08:30:00’);

#部分列录入
INSERT INTO
student(NAME,intime)
VALUES(‘ma6’,NOW());
SELECT * FROM student;

– 10.4 update 应用
UPDATE student SET NAME=‘马六’ WHERE id=8;

– 10.5 delete 应用
DELETE FROM student WHERE id=8 ;

伪删除的实现,使用update替代delete。

  1. 添加一个状态列 state
    ALTER TABLE student ADD state TINYINT NOT NULL DEFAULT 1 ;

  2. update 替代 delete
    UPDATE student SET state=0 WHERE id=9;

  3. 查询语句修改为
    SELECT * FROM student WHERE state=1;

以下三条语句的功能及区别？

drop table t1 ; —> 表定义+表数据（物理），全删除，磁盘空间立即删除
truncate table t1 ; —> 清空表数据（物理），立即释放磁盘空间。
delete from t1; —> 逐行删除表数据（逻辑，delete mark）。不会立即释放磁盘空间，会有碎片。

DQL 语句

select

作用：

获取用户表中的数据行。

select 独立使用

#查询系统变量（参数）
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
#替代方案：
SHOW VARIABLES;
SHOW VARIABLES LIKE ‘%trx%’;

#查询用户变量
SET @oldguo=100;
SELECT @oldguo;

#查询函数
SELECT VERSION();
SELECT USER();
SELECT NOW();
SELECT CONCAT(“hello world”)
SELECT CONCAT(USER ,"@",HOST) FROM mysql.user;

select 通用使用方法

#单表查询的语法结构
/*
select 列
from 表
where 条件
group by 条件
having 条件
order by 列
limit 条件;
*/

  1. 导入world练习库
    – https://dev.mysql.com/doc/index-other.html
    – [root@db01 ~]# mysql -uroot -p123 < world.sql

  2. 了解业务
    – 1. 查看列的信息
    USE world;
    DESC city;
    SHOW CREATE TABLE city;
    /*
    id : 主键列，自增长1-N。
    name : 城市名字
    countrycode: 城市所在国家编码(3字母，CHN、USA)
    District ：城市所在区域（省、州、县）
    Population ：城市人口
    */
    – 2. 查询表中部分数据
    SELECT * FROM city LIMIT 10;

– 3. 找开发沟通获取信息

select + from 应用

– 1. 查询全表数据(不代表生产操作) —> cat /etc/passwd
SELECT * FROM city;
– 2. 查询部分列数据 —> awk $2 $5
SELECT NAME,population FROM city;

select + from + where 应用

– 1. where 配合等值查询
– 例子： 查询中国(CHN)所有的城市信息
SELECT * FROM city
WHERE countrycode=‘CHN’;
– 例子： 查询美国(USA)所有的城市名和人口数
SELECT NAME,population FROM city
WHERE countrycode=‘USA’;

– 2. where 配合不等值查询 （> < >= <= !=）
– 例子： 查询世界上人口数据小于100人的城市信息
SELECT * FROM city
WHERE population<100;

– 3. where 配合 and or ,between and ,in 使用
– 例子：查询中国，并且人口大于500w的城市信息
SELECT * FROM city
WHERE countrycode=‘CHN’ AND population>5000000;

– 例子： 查询中国或美国的城市信息
SELECT * FROM city
WHERE countrycode=‘CHN’ OR countrycode=‘USA’;
— 等价写法
SELECT * FROM city
WHERE countrycode IN (‘CHN’,‘USA’);

– 例子： 查询人口数量在 100w-110w之间
SELECT * FROM city
WHERE population >=1000000 AND population<=1100000;
— 等价写法
SELECT * FROM city
WHERE population BETWEEN 1000000 AND 1100000;

– 4. where 配合 like应用
– 查询countrycode是 “CH” 开头的城市信息
SELECT * FROM city
WHERE countrycode LIKE ‘CH%’;

select + from + where + group by + 聚合函数 应用

– 1. 需求 ：1000人在一个广场上，要求快速统计每个省的学生数量？
– 1. 站队。分组
– 2. 数数

– 2. group by + 聚合函数的执行逻辑？
– 1. 按照group by的列进行排序+去重复
– 2. 讲其他的查询列进行聚合操作
– 3. 1+2 显示给用户

– 聚合函数种类：
/*
count() : 统计个数
sum() : 求和
avg() ：平均值
max() ：最大值
min() ：最小值
group_concat()：列转行：

*/

– 例子1 ： 统计city表，每个国家的城市个数

SELECT countrycode,COUNT(*)
FROM city
GROUP BY countrycode;

– 例子2 ： 统计city表，中国 每个省的 城市个数
SELECT district,COUNT(*)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district;

– 例子3 ： 统计city表，每个国家的总人口数
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode;

– 例子4 ： 统计city表，中国 每个省的总人口数
SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district;

– 例子5 ： 统计city表，中国 每个省的 城市个数 ，所有城市名
SELECT district,COUNT(*) ,GROUP_CONCAT(NAME)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district;

select + from + where + group by + 聚合函数 + having 应用

– 例子： 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息
SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
having SUM(population)>5000000;

select + from + where + group by + 聚合函数 + having +order by

– 例子： 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口排序输出。

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
order by SUM(population);

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) desc ;

select + from + where + group by + 聚合函数 + having +order by + limit

– 例子： 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口从大倒小排序输出
– 只显示前5名。

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) desc
limit 5 offset 0 ;

– 只显示6-10名。
SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5 offset 5;

多表连接查询

– 1. 预备工作
CREATE DATABASE school CHARSET utf8;
USE school
CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(20) NOT NULL COMMENT ‘姓名’,
sage TINYINT UNSIGNED NOT NULL COMMENT ‘年龄’,
ssex ENUM(‘f’,‘m’) NOT NULL DEFAULT ‘m’ COMMENT ‘性别’
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT ‘课程编号’,
cname VARCHAR(20) NOT NULL COMMENT ‘课程名字’,
tno INT NOT NULL COMMENT ‘教师编号’
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT ‘学号’,
cno INT NOT NULL COMMENT ‘课程编号’,
score INT NOT NULL DEFAULT 0 COMMENT ‘成绩’
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT ‘教师编号’,
tname VARCHAR(20) NOT NULL COMMENT ‘教师名字’
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(1,‘zhang3’,18,‘m’),
(2,‘zhang4’,18,‘m’),
(3,‘li4’,18,‘m’),
(4,‘wang5’,19,‘f’),
(5,‘zh4’,18,‘m’),
(6,‘zhao4’,18,‘m’),
(7,‘ma6’,19,‘f’),
(8,‘oldboy’,20,‘m’),
(9,‘oldgirl’,20,‘f’),
(10,‘oldp’,25,‘m’);

INSERT INTO teacher(tno,tname) VALUES
(101,‘oldboy’),
(102,‘hesw’),
(103,‘oldguo’)
(104,‘alex’);

INSERT INTO course(cno,cname,tno)
VALUES
(1001,‘linux’,101),
(1002,‘python’,102),
(1003,‘mysql’,103)
(1004,‘go’,105);

INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

– 2. 多表连接的类型
– 笛卡尔乘积
mysql> select * from teacher , course;

±----±-------±-----±-------±----+
| tno | tname | cno | cname | tno |
±----±-------±-----±-------±----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1001 | linux | 101 |
| 103 | oldguo | 1001 | linux | 101 |
| 104 | alex | 1001 | linux | 101 |
| 101 | oldboy | 1002 | python | 102 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1002 | python | 102 |
| 104 | alex | 1002 | python | 102 |
| 101 | oldboy | 1003 | mysql | 103 |
| 102 | hesw | 1003 | mysql | 103 |
| 103 | oldguo | 1003 | mysql | 103 |
| 104 | alex | 1003 | mysql | 103 |
| 101 | oldboy | 1004 | go | 105 |
| 102 | hesw | 1004 | go | 105 |
| 103 | oldguo | 1004 | go | 105 |
| 104 | alex | 1004 | go | 105 |
±----±-------±-----±-------±----+

– 内连接 （取交集）
SQL92:
mysql> select *from teacher , course where teacher.tno=course.tno;
SQL99:
mysql> select *from teacher join course on teacher.tno=course.tno;
±----±-------±-----±-------±----+
| tno | tname | cno | cname | tno |
±----±-------±-----±-------±----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
±----±-------±-----±-------±----+

– 外连接 （左、右）

mysql>
mysql> select * from teacher left join course on teacher.tno = course.tno;
±----±-------±-----±-------±-----+
| tno | tname | cno | cname | tno |
±----±-------±-----±-------±-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
| 104 | alex | NULL | NULL | NULL |
±----±-------±-----±-------±-----+
4 rows in set (0.00 sec)

mysql> select * from teacher right join course on teacher.tno = course.tno;
±-----±-------±-----±-------±----+
| tno | tname | cno | cname | tno |
±-----±-------±-----±-------±----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
| NULL | NULL | 1004 | go | 105 |
±-----±-------±-----±-------±----+

多表连接语法格式

select xxx
from a join b
on a.x=b.y
where
group by
having
order by
limit

select xxx
from a join b
on a.x=b.y
join c
on b.z=c.zz

核心思路：
1.找到所有相关表
2.找到所有表之间的关联关系
3.罗列其他的查询条件

多表连接例子：

— 统计学员zhang3,学习了几门课
SELECT student.sname,COUNT(*)
FROM student JOIN sc
ON student.sno =sc.sno
WHERE student.sname=‘zhang3’;

— 所有学员学习的课程门数
SELECT student.sname,COUNT(*)
FROM student JOIN sc
ON student.sno =sc.sno
GROUP BY student.sname;

— 查询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;

– 课堂练习：
— 查询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 teacher.tname;

— 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname ,AVG(sc.score) FROM
teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tname
ORDER BY AVG(sc.score);

— 查询oldguo所教的不及格的学生姓名

SELECT teacher.tname,GROUP_CONCAT(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’ AND sc.score<60
GROUP BY teacher.tname ;

— 查询所有老师所教学生不及格的信息
SELECT teacher.tname,GROUP_CONCAT(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 sc.score<60
GROUP BY teacher.tname ;

别名的使用

– 表别名
SELECT a.tname,GROUP_CONCAT(d.sname) FROM
teacher AS a
JOIN course AS b
ON a.tno=b.tno
JOIN sc AS c
ON b.cno=c.cno
JOIN student AS d
ON c.sno=d.sno
WHERE c.score<60
GROUP BY a.tname ;

– 列别名
SELECT a.tname AS aa ,GROUP_CONCAT(d.sname) AS bb FROM
teacher AS a
JOIN course AS b
ON a.tno=b.tno
JOIN sc AS c
ON b.cno=c.cno
JOIN student AS d
ON c.sno=d.sno
WHERE c.score<60
GROUP BY a.tname ;

SELECT teacher.tname AS ‘老师名’ ,AVG(sc.score) AS ‘平均分’ FROM
teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tname
ORDER BY 平均分;

元数据的获取

元数据包含什么？

数据字典信息（表属性、列、列属性）、状态、系统参数、权限等。
ibdata1 、 frm 、 mysql库（权限表、状态表、统计信息） 、 P_S、SYS表

查询元数据方法

show 语句

help show
show databases;
show tables [from DB];
show create database world;
show craete table world.city;
show full processlist;
show engines;
show charset;
show collation;
show variables [like ‘%%’]
show status [like ‘%%’]
show grants for
SHOW OPEN TABLES
SHOW INDEX FROM tbl_name
SHOW MASTER STATUS
SHOW BINLOG EVENTS
SHOW RELAYLOG EVENTS
SHOW SLAVE STATUS
SHOW SLAVE HOSTS

#information_schema 视图库
每次数据库启动，自动在内存中生成的“虚拟表”（视图）。
保存了各种常用元数据查询方法的视图，只能查询不能修改和删除。

– TABLES 使用
– 1. 结构介绍
作用：存储了整个MySQL中所有表相关属性信息
desc tables;
TABLE_SCHEMA : 所在库
TABLE_NAME ： 表名
ENGINE ： 存储引擎
TABLE_ROWS ： 数据行
AVG_ROW_LENGTH ： 平均行长度
INDEX_LENGTH ： 索引的长度
DATA_FREE ： 碎片的情况

– 2. 应用案例
– 例子1： 统计MySQL所有业务库：库名、表个数、表名
select table_schema,count(*),group_concat(table_name)
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’)
group by table_schema ;

– 例子2： 统计MySQL所有业务库：库名、数据总量(单张表：TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)

select table_schema,sum(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 as sum_mb
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’)
group by table_schema ;

– 例子3:
生产案例：客户MySQL系统 经历的很多个版本 5.1 --》 5.5 —》 5.6。。。
系统中有2000-3000张表，其中有myisam、innodb两种存储引擎类型。
需求1： 查找业务库中，所有非InnoDB表

select table_schema,table_name,engine
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’;

需求2： 将所有这些表备份走
mysqldump -uroot -p123 test t1 >/data/test_t1.sql

select concat(“mysqldump -uroot -p123 “,table_schema,” “,table_name,” >/data/”,table_schema,"_",table_name,".sql")
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’
into outfile ‘/tmp/dump.sh’;

需求3: 将以上表替换为InnoDB引擎
alter table test.t1 engine=innodb;

select concat(“alter table “,table_schema,”.”,table_name," engine=innodb;")
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’
into outfile ‘/tmp/alter.sql’;

[root@db01 ~]# sh /tmp/dump.sh
[root@db01 ~]# cd /data/
[root@db01 data]# ll
-rw-r–r-- 1 root root 1741 Jul 2 18:30 test_t1.sql
-rw-r–r-- 1 root root 1741 Jul 2 18:30 world_t2.sql
[root@db01 data]# mysql -uroot -p123 </tmp/alter.sql

mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’;
Empty set (0.00 sec)

℡永恒
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL基础应用
02-26
SQL基础应用
sql高级
doulihang的博客
12-20 241
1.限制查询出数据的条数 限制查询出的数据最多为10条 select * from 表名 limit 10 2.查询出结果的前10条数据 select * from 表名 order by id limit 10 3.like运算符详解 查询用户账号以d开头的 select * from 表名 where account like "d%" 查询用户账号以d结尾的 select ...
SQL语句大全
weixin_34292959的博客
12-24 295
创建数据库 　　创建之前判断该数据库是否存在 　　if exists (select * from sysdatabases where name='databaseName') 　　drop database databaseName 　　go 　　Create DATABASE database-name 删除数据库 　　drop d...
SQL语句：limit
weixin_46740637的博客
07-16 527
Select* from 某表limit N，S 表示：从第N行的下一行开始（不包括第N行），往后检索S行数据 Select* from 某表limit N，-1 表示：从第N行的下一行开始（不包括第N行），往后检索到最后一行 Select* from 某表limit 5 offset 2 表示：检索第3、4、5、6、7行 举例： 1、检索前10行数据 Select * from A limit 10 2、检索第5-10行 ...
Mysql limit 大数据量分页优化方法
qq_42247220的博客
05-21 867
   比如：同样是取10条数据   select * from 表名 limit 10000,10  和   select * from 表名 limit 0,10  但是我们可以看出这两条的数据量还是相差蛮大的1、 数据量比较小的时候。比如：   select * from 表名 limit 10,10     可以写成如下代码：　Select * From 表名Where vid ＞=(Sel...
SELECT * FROM table LIMIT
nsjlive的博客
07-08 8280
使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，已 经为我们提供了这样一个功能。 LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。 SELECT * FROM table LIMIT [...
sql基础应用使用手册
10-26
《SQL基础应用使用手册》是一本深度解析SQL语言及其在关系型数据库中应用的专业书籍，由黄少年和陈翠娥两位作者共同编写，于2008年由电子工业出版社出版。该书共计702页，采用16开本平装形式，原价为89元，ISBN编号...
SQL语句基础教程
01-20
SQL语句基础教程旨在帮助新手和需要复习SQL的资料仓储业界老将，学习SQL基础知识和语法。 SQL指令 -------- SQL指令是SQL语言的基础，用于储存、读取、处理数据库中的资料。常用的SQL指令包括： * SELECT：从...
SQL Server 2005应用基础
12-16
SQL Server 2005应用基础，教程详细，内容很好
SQL基础教程_sql基础教程_
10-02
本书是畅销书《SQL基础教程》第2版，介绍了关系数据库以及用来操作关系数据库的SQL语言的使用方法。书中通过丰富的图示、大量示例程序和详实的操作步骤说明，让读者循序渐进地掌握SQL的基础知识和使用技巧，切实提高...
SQL的limit的作用
become a programmer
03-26 2827
看别人的代码看到了limit的使用，然后搜一搜整理一下 1.例如sql语句：select * from table limit 1,10 表示的意思是显示第1条记录到第10条记录。 2.例如sql语句：select * from table limit 3,10 表示的意思是显示第3条记录到第10条记录。 3.例如sql语句：select * from tab
基于用Hive泰坦尼克号乘客信息存储与分析
最新发布
weixin_45551538的博客
12-31 1842
基于用Hive泰坦尼克号乘客信息存储与分析
select与limit使用方法
saber_sss的博客
12-23 3万+
MySQL中，select与limit的用法说明 1.limit使用简介： limit关键字可以接受一个或者两个参数，且这个参数需是整数常量，如果两个参数，第一个表示返回记录行的偏移量，第二个表示返回记录行最大的数目；一个的数的话，默认初始记录行为0。 基本用法： SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 简...
HiveSql中limit10的坑——对大数据量的表查询加group by
攻城狮Kevin
06-03 7096
一般查询hive表中某个字段是否存在某个值，会使用如下查询语句 select * from 表名 where dt=XXXX and 字段 like '%某个值%' limit 10； 这种比较适合hive表数据量较小的情况，出现limit10，hive会默认判断此表数据量不大，只开启一个map进行查询， 如果对于大表，比如好几百G上T的数据量，这么大的数据量只开一个map一方面查询慢，另一方面，因为数据量太大容易把机器跑崩。 采用以下方式即可，加上group by，然后就会依据hive表的文件
MyBatis = MyBatis注解讲解和分页查询
人生起落，生活苦甜，坚持追求！
05-14 2415
注解开发查询，分页查询 一.mybatis注解 Mybatis除了支持在xml中书写SQL,也支持使用注解的形式编写SQL； MyBatis可以使用注解开发方式，这样我们就可以减少编写Mapper映射文件。 （1）MyBatis常用注解： * @Insert：实现新增，代替了<insert></insert> * @Update：实现更新，代替了<update></update> * @Delete：实现删除，代替了<...
MySQL中使用LIMIT进行分页的方法
云祁QI
10-30 6275
一、分页需求： 客户端通过传递start(页码)，pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下： 比如： 查询第1条到第10条的数据的sql是：select * from table limit 0,10;...
select * from tb_shop id order by id desc limit 0,1
waj89757的专栏
03-07 1万+
order by id desc是按id进行降序排列limit 0,1 是只取记录中的第一条.所以这条语句只能得到一条记录如想取前10条则 limit 0,10或limit 10如想取第10至20条则 limit 10,20
sql语句里的limit使用方法
wanghuahui的专栏
09-13 13万+
SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset    在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了上面这样一个功能。      LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须
sql中limit的用法
热门推荐
yihanzhi的博客
09-20 23万+
limit子句用于限制查询结果返回的数量。 用法：【select * from limit tableName limit i,n 】 参数： tableName : 为数据表； i : 为查询结果的索引值（默认从0开始）； n : 为查询结果返回的数量 示例： 1）查询student的数据： select * from student 2）查询第一条数据 select * from st...
SQL基础教程：入门与应用
本资源是一份全面的SQL基础教程，旨在帮助读者掌握这一强大的数据库查询语言。教程从第2章开始，首先介绍了SQL的背景，强调它是20世纪70年代由IBM开发并在1992年成为国际标准的，以SQL-92为基础。SQL作为一种数据子...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值