03 SQL基础应用

MySQL内置的功能

world.sql 下载路径：https://dev.mysql.com/doc/index-other.html

  1. 连接数据流
  • -u
  • -p
  • -S
  • -h
  • -P
  • -e
  • <
    例子：
  1）mysql -uroot -p -S /tmp/mysql.sock
  2）mysql -uroot -p -h10.0.0.1 -P3306
  3）-e
    root@n37-081-120:/etc/systemd/system# mysql -uroot -p -e "show databases;"
    Enter password: 
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | wordpress          |
    +--------------------+
    root@n37-081-120:/etc/systemd/system#
  4）< 恢复数据
root@n37-081-120:~# mysql -uroot -p123 < /root/world.sql
  1. 内置命令
    help 打印mysql的帮助
\c  ctrl+c               结束上个命令运行
\q quit; exit; ctrl+d    退出mysql
\G                       格式化结果
source                   恢复备份数据

SQL 基础应用

  1. SQL介绍
    结构化的查询语言
    是关系型数据库通用的命令，遵循SQL92的标准（SQL_MODE）
  2. SQL常用种类
    DDL 数据定义语言
    DCL 数据控制语言
    DML 数据操作语言
    DQL 数据查询语言
  3. SQL引入 - 数据库的逻辑结构
  • 库
    • 库名
    • 库属性：字符集，排序规则
  • 表
    • 表名
    • 表属性：存储引擎类型，字符集，排序规则
    • 列名
    • 列属性：数据类型，约束，其他属性
    • 数据行
  1. 字符集（charset）
    相当于MySQL的密码本（编码表）
    mysql> show charset;
    utf8 #3个字节
    utf8mb4（建议）#4个字节，支持emoji
  2. 排序规则：collation
    mysql> show collation;
    对于英文字符串的，大小写的敏感
  3. 数据类型介绍
  • 数字
    • 整数
    • 浮点数
  • 字符串
    • char(100)
      定长字符串类型，不管字符串长度多长，都立即分配100个字符长度的存储空间，未占满的空间使用“空格”填充。
    • varchar(100)
      变长字符串类型，每次存储数据之前，都要先判断一下长度，按需分配磁盘空间。会单独申请一个字符长度的空间存储字符长度（前提：少于255，如果超过255以上的字符串长度，会占用两个存储空间）。
      如何选择这两个数据类型？
      1.少于255个字符长度，定长的列值，选择char
      2.多于255字符长度，变长的字符串，可以选择varchar
    • enum
      枚举数据类型
      address enum('sz', 'sh', 'bj'......)
  • 时间
    • 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

  • 二进制


    数字.png

    时间.png

DDL的应用

  • 库的定义
-- 创建数据库
CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;

--  查看库情况
SHOW DATABASES;
SHOW CREATE DATABASE zabbix;

-- 删除数据库
DROP DATABASE oldguo;

-- 修改数据库字符集
-- 注意：一定是从小往大改，比如utf8 --> utf8mb4
-- 目标字符集一定是源字符集的严格超级
SHOW CREATE DATABASE wordpress;
CREATE DATABASE oldguo;
SHOW CREATE DATABASE oldguo;
ALTER DATABASE oldguo CHARSET utf8mb4;
  • 库的定义规范
-- 1.库名使用小写字符
-- 2.库名不能以数字开头
-- 3.不能是数据库内部的关键字
-- 4.建库必须设置字符集
  • DDL-表定义
-- 建表
表名，列名，列属性，表属性
-- 列属性
PRIMARY KEY     : 主键约束，表中只能有一个主键，非空且唯一。
NOT NULL            : 非空约束，不允许空值，必填项。
UNIQUE KEY      : 唯一键约束，不允许重复值。
DEFAULT             : 一般配合 NOT NULL 一起使用。
UNSIGNED            : 无符号，一般是配合数字列，非负数。
COMMENT             : 注释。
AUTO_INCREMENT  : 自增长的列。

CREATE TABLE stu (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(255) 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 DEFAULT NOW() COMMENT '入学时间'
) ENGINE =  INNODB CHARSET = utf8mb4;

-- 建表规范
-- 1. 表名小写字母，不能数字开头，
-- 2.不能是保留字符，使用和业务有关的表名
-- 3.选择合适的数据类型及长度
-- 4.每个列设置 NOT NULL + DEFAULT ，对于数据-填充，对于字符使用有效字符串
-- 5.每个列设置注释
-- 6.表设置存储引擎和字符集
-- 7.主键列尽量是无关列数字列，最好是自增长
-- 8.ENUM类型不要保存数字，只能是字符串类型

-- 查询建表信息
SHOW TABLES;
SHOW CREATE TABLE stu;
-- 创建一个表结构一样的表
CREATE TABLE test LIKE stu;
-- 删表
DROP TABLE test;
-- 修改表
-- 在stu表中添加qq列
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
-- 在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
-- 在id列前加一个新列num
ALTER TABLE stu ADD num INT NOT NULL UNIQUE  COMMENT '身份证' FIRST;
DESC stu;
-- 把刚才添加的列都删掉（危险）
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
-- 修改sname数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';
-- 将gender改为sex，数据类型改为CHAR类型
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';
DESC stu;

DCL

grant
revoke

DML

  • insert
-- DML
-- insert
DESC stu;

-- 最偷懒
INSERT stu VALUES(1, 'zs', 18, 'm', NOW());
SELECT * FROM stu;
-- 最规范
INSERT INTO stu(id, sname, age, sex, intime)
VALUES (2, 'ls', 19, 'f', NOW())
-- 针对性录入数据
INSERT INTO stu(sname, age, sex)
VALUES ('w5', 11, 'm');

-- 一次性录入多行
INSERT INTO stu(sname, age, sex)
VALUES 
('aa', 11, 'm'),
('bb', 12, 'f'),
('cc', 13, 'm');
  • update
  • delete

DQL

  • select
  1. select单独使用的情况
mysql> select  @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> select  @@basedir;
+---------------------+
| @@basedir           |
+---------------------+
| /application/mysql/ |
+---------------------+
1 row in set (0.00 sec)

mysql> select  @@datadir;
+---------------------+
| @@datadir           |
+---------------------+
| /data00/mysql/data/ |
+---------------------+
1 row in set (0.00 sec)

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

mysql> select  @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2025-12-01 14:51:54 |
+---------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use oldguo;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| oldguo     |
+------------+
1 row in set (0.00 sec)

mysql>
  1. select 通用语法 *****
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit

示例

  • SELECT 配合 FROM 子句使用
-- select 列,列,列 from 表
-- 例子
-- 1. 查询表中所有的信息
USE world;
DESC users;
SELECT id, username, password, email, nickname, register_time, last_login_time FROM users;
-- 或者
SELECT * FROM users;
-- 2. 查询表中username和nicktname的值
SELECT username,nickname FROM users;
  • SELECT 配合 WHERE 子句使用
-- select 列,列,列 from 表 where 过滤条件
-- where 等值条件查询
-- 例子
-- 1. 查询获得学习小白称号的用户名和邮箱
SELECT username, email FROM users WHERE  nickname = '学习小白';
  • where 配合比较判断符查询（> < >= <=）
-- 例子
-- 1. ID号小于2的用户名和邮箱
SELECT username, email FROM users WHERE id < 2;
  • where 配合逻辑连接符（and or）
-- 1. 查询获得学习小白称号的前5注册的用户名和邮箱
SELECT username, email FROM users WHERE nickname = '学习小白' AND id <=5;
-- 2. 查询获得学习小白称号和单词达人称号的用户名和邮箱
SELECT username, email FROM users WHERE nickname = '学习小白' OR nickname = '单词达人';
-- 3. 查询第2-5位之间注册的用户名和邮箱
SELECT username, email FROM users WHERE id > 2 AND id < 5;
SELECT username, email FROM users WHERE id >= 2 AND id <= 5;
SELECT  username, email FROM users WHERE id BETWEEN 2 AND 5;
  • where 配合like 子句模糊查询
-- 例子：
-- 1. 查询注册时间为2025的用户信息
SELECT * FROM users WHERE register_time LIKE '2025%';
-- 注意：不要出现类似于 %2025%，前后都有百分号的语句，因为不走索引，性能极差
-- 如果业务中有大量需求，用“ES”来替代
  • where 配合in 语句
-- 例子：查询获得学习小白称号和单词达人称号的用户信息
SELECT username, email FROM users WHERE nickname = '学习小白' OR nickname = '单词达人';
SELECT username, email FROM users WHERE nickname IN ('学习小白', '单词达人');
  • SELECT 配合 GROUP BY + 聚合函数应用
-- 常用聚合函数介绍
max(), min(), avg(), count(), sum(), group_concat()
-- GROUP BY
-- 将某列中有共同条件的数据行，分成一组，再进行聚合函数操作
-- 例子：
-- 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 CountryCode, District,SUM(Population) FROM city
WHERE CountryCode = 'CHN' GROUP BY District;
-- 5. 统计中国每个省城市的个数
SELECT  CountryCode, District, COUNT(Name) FROM city
WHERE CountryCode = 'CHN' GROUP BY District;
-- 6. 统计中国每个省城市的名字列表
SELECT CountryCode, District, GROUP_CONCAT(Name) FROM city
WHERE CountryCode = 'CHN' GROUP BY District;
-- 7. 拼接省份，城市
SELECT CONCAT(District,":", GROUP_CONCAT(name)) FROM city
WHERE CountryCode = 'CHN' GROUP BY District;
  • SELECT 配合 having应用
-- 例子
-- 1. 统计所有国家的总人口数量，将总人口数大于1亿的过滤出来
SELECT CountryCode, SUM(Population) FROM city
GROUP BY CountryCode
HAVING SUM(Population) > 100000000;
  • 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子句
-- 例子
-- 1. 统计所有国家的总人口数量，将总人口数大于5000w的过滤出来，并且按照从大到小顺序排列，只显示前三名
SELECT CountryCode, SUM(Population) FROM city
GROUP BY CountryCode
HAVING SUM(Population) > 50000000
ORDER BY SUM(Population) DESC LIMIT 3;

SELECT CountryCode, SUM(Population) FROM city
GROUP BY CountryCode
HAVING SUM(Population) > 50000000
ORDER BY SUM(Population) DESC LIMIT 3,3;

SELECT CountryCode, SUM(Population) FROM city
GROUP BY CountryCode
HAVING SUM(Population) > 50000000
ORDER BY SUM(Population) DESC LIMIT 3 OFFSET 3;

LIMIT M,N  -- 跳过M行，显示一共N行
LIMIT Y OFFSET X    -- 跳过X行，显示一共Y行
  • 练习
-- 1. 统计中国每个省的总人口数，只打印总人口数小于100w的
SELECT CountryCode,SUM(Population)  FROM city
WHERE CountryCode = 'CHN'
GROUP BY district
HAVING SUM(Population) < 1000000;
-- 2. 查看中国所有的城市，并按人口数进行排序（从大到小）
SELECT * FROM city WHERE CountryCode = 'CHN' ORDER BY  Population DESC;
-- 3.统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT District,SUM(Population) FROM city
WHERE CountryCode = 'CHN'
GROUP BY District
ORDER BY SUM(Population) DESC;
-- 4. 统计中国，每个省的总人口，找出总人口大于500w的，并按总人口从大到小排序，只显示前三名
SELECT District,SUM(Population) FROM city
WHERE CountryCode = 'CHN'
GROUP BY District
HAVING SUM(Population) > 5000000
ORDER BY SUM(Population) DESC LIMIT 3;
  • union和union all
  • 作用：多个结果集合并查询的功能
    union 和 union all 的区别：
    union all 不做去重
    union 做去重操作
-- 查询中国或者美国的城市信息
SELECT * FROM city WHERE CountryCode = 'CHN' OR CountryCode = 'USA';

SELECT * FROM city WHERE CountryCode = 'CHN'
UNION ALL
SELECT * FROM city WHERE CountryCode = 'USA';
  • 多表连接查询（内连接）
  • 作用
    单表数据不能满足查询需求，使用多表连接查询
-- 例子
-- 1. 查询世界上小于100人的城市，所在的国家名，国土面积，城市名，人口数
SELECT CountryCode, NAME, Population FROM city WHERE Population < 100;
-- PCN  Adamstown   42
SELECT NAME,SurfaceArea  FROM country WHERE CODE = 'PCN';
-- Pitcairn 49.00
-- 多表连接查询基本语法要求
-- 1. 最核心的是，找到多张表的关联条件列：city.CountryCode = country.CODE
-- 2. 列书写时，必须是：表名.列
-- 3. 所有涉及到的查询列，都放在select后
-- 4. 将所有的过滤，分组，排序等条件按顺序写在on的后面
SELECT country.`Name`, country.SurfaceArea, city.`Name`, city.Population
FROM city JOIN country
ON city.CountryCode = country.CODE
WHERE city.Population < 100;
  • 多表连接，例子
-- 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, ":", student.sage, ":", student.ssex, ":", 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 teacher.tname, student.sname, student.sage, student.ssex, 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;
  • 别名应用
    表别名：全局调用
    列别名：可以被having、order by调用
SELECT t.tname AS '讲师名', st.sname AS '学生名', st.sage AS '学生年龄', st.ssex AS '学生性别', 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;
  1. 元数据获取

  • 元数据
    元数据是存储在“基表”中。
    通过专用的DDL语句，DCL语句进行修改
    通过专用视图和命令进行元数据的查询
    information_schema中保存了大量元数据查询的视图
    show命令是封装好功能，提供元数据查询基础功能

  • information_schema的基本应用
    TABLES 视图的应用

mysql> use information_schema;
mysql> desc TABLES;

TABLE_SCHEMA    #表所在的库名
TABLE_NAME      #表名
ENGINE          #存储引擎
TABLE_ROWS      #数据行
AVG_ROW_LENGTH  #平均行长度
INDEX_LENGTH    #索引长度
  • 例子
-- 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_DB
FROM information_schema.`TABLES`
GROUP BY TABLE_SCHEMA
ORDER BY total_DB DESC;
  • 配合 concat() 函数拼接语句或命令
-- 1. 模仿以下语句，进行数据库的分库分表备份。 mysqldump -uroot -p123 world city > /bak/world_city.sql
SELECT 
CONCAT("mysqldump -uroot -p123 ", TABLE_SCHEMA, " ", TABLE_NAME, 
"> /bak/",TABLE_SCHEMA, "_", TABLE_NAME, ".sql")
FROM information_schema.`TABLES`;
-- 2. 模仿以下语句，进行批量生成对world数据库下的所有表操作。 ALTER TABLE world.city DISCARD TABLESPACE;
SELECT 
CONCAT("ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " DISCARD TABLESPACE;")
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = 'world';
  • 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 engin 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 ''                #查看数据库整体状态信息
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

  • SQL基础应用
    MySQL内置功能 连接数据库 提供内置命令 SQL的基础应用 SQL的介绍 结构化查询语言，遵循SQL92标准（...
    麟之趾a阅读 1,760评论 0赞 0
  • MySQL-lesson03 SQL基础应用
    1、SQL的介绍 结构化查询语言 5.7 以后符合SQL92严格模式 通过sql_mode参数来控制 2、SQL的...
    noodlesbook阅读 1,468评论 0赞 0
  • 3、SQL基础应用
    1 SQL介绍 结构化查询语言5.7以后符合SQL_92标准的严格模式通过sql_mode来控制 2 SQL分类 ...
    一个反派人物阅读 1,702评论 0赞 0
  • MySQL-lesson03-SQL基础应用
    1、SQL介绍 结构化查询语言5.7 以后符合SQL92严格模式通过sql_mode参数来控制 2、常用SQL分类...
    小一_d28d阅读 2,586评论 0赞 0
  • MySQL SQL的基础应用
    SQL 基础应用及information_schema 1.SQL（结构化查询语句）介绍 SQL标准：SQL 92...
    子晋zj阅读 3,138评论 0赞 0

友情链接更多精彩内容