【MySQL 数据库】1、MySQL 的 DDL、DML、DQL 语句

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 【MySQL 数据库】1、MySQL 的 DDL、DML、DQL 语句


一、MySQL 应该掌握哪些知识点 ?

🎄 (1) MySQL 基础篇(初级工程师)

  • ① MySQL 基础概念
  • ② SQL 语句
  • ③ 函数
  • ④ 约束
  • ⑤ 多表查询
  • ⑥ 事务

🎄 (2) MySQL 进阶篇(中级工程师)

  • ① 存储引擎
  • ② 索引
  • ③ SQL 优化
  • ④ 视图、存储过程、触发器
  • ⑤ 锁
  • ⑥ InnoDB 核心
  • ⑦ MySQL 管理

🎄 (3) 运维篇(高级工程师)

  • ① 日志
  • ② 主从复制
  • ③ 分库分表
  • ④ 读写分离

二、数据库相关概念

✏️ 数据库:存储数据的仓库(仓库中的数据是被有组织的存储的)【Database】

✏️ 数据库管理系统:操纵和管理数据库的大型软件【Database Management System】

✏️ SQL:操纵关系型数据库的编程语言(定义了一套操纵关系型数据库的同一标准)【Structured Query Language】

三、主流关系型数据库管理系统

📝 Oracle(非开源)

📝 MySQL(社区版是开源的)

📝 Sql Server(微软研发)

📝 PostgreSQL

📝 IBM Db2

📝 SQLite(安卓使用)

四、关系型数据库

🎄 关系型数据库(RDBMS):由多张相互关联的二维组成的数据库

五、SQL 语句的分类

📌 ① DDL(Data Definition Language)

数据定义语言【用于定义数据库字段 …】

📌 ② DML(Data Manipulation Language)

数据操作语言【用于对数据库表中的数据进行

📌 ③ DQL(Data Query Language)

数据查询语言【用于询数据库中表的记录】

📌 ④ DCL(Data Control Language)

数据控制语言【用于创建数据库用户、控制数据库的访问权限】

六、DDL

🎄 Data Definition Language:数据定义语言

(1) 数据库操作

查询:

🎼 ① 查询所有数据库:

SHOW DATABASES;

🎼 ② 查询当前数据库

SELECT DATABASE();

🎼 ③ 创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name;

IF NOT EXISTS 表示 只有当数据库不存在的时候才创建,避免了下图的错误

CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARSET = utf8mb4

✏️ utf8mb4 字符集可存储 4 个字节的数据(可存储表情包)

✏️ utf8 字符集只能存储 3 个字节的数据

✏️ 推荐使用 utf8mb4

📌 DEFAULT CHARSET 用于设置数据的字符集

🎼 ④ 删除数据库

DROP DATABASE [IF EXISTS] db_name

📌 IF EXISTS 只有存在才删除

🎼 ⑤ 使用数据库

USE db_name

(2) 表操作

🎄 ① 查询当前数据库的所有表

SHOW TABLES

🎄 ② 查询表结构

DESC table_name

🎄 ③ 查询指定表的建表语句

SHOW CREATE TABLE table_name

🎄 ④ 创建表

CREATE TABLE `student` (
  `id` INT(11) DEFAULT NULL COMMENT '编号',
  `name` VARCHAR(20) DEFAULT NULL COMMENT '姓名',
  `age` TINYINT(4) DEFAULT NULL COMMENT '年龄',
  `gender` CHAR(1) DEFAULT NULL COMMENT '性别'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='这是学生表'
  • ① 使用 VARCHAR 类型必须指定长度
  • ② 最后一个字段的后面不要加 ,【加了会报错】

(3) 字段的数据类型

🎄 数据库表的数据类型有三种:

① 数值类型

DECIMAL 数据类型的精度和标度:

eg:25.689 的精度是 5

eg:25.689 的标度是 3

🎄 age:tinyint unsigned

  • unsigned【年龄不会有负数】

🎄 score:总分100分,最多出现一位小数

  • score double(4,1)
  • 分数有可能是:100.1【所以是4
  • 只会有一位小数【所以是1

② 字符串类型

  • 长字符串】varchar(10):最多只能存储10个字符,会根据实际情况指定占用的空间【性能比 char 差】
  • 长字符串】char(10):最多只能存储10个字符,当存储的是1个字符的时候也是占用10字符的空间,为使用的字符用空格补位【性能比 varchar 好】

【姓名 name】用 varchar 类型比较好

【性别 gender】用 char 类型比较好

📝 varchar(n):n 表示的是最大字符数【占用的空间不固定,但占用的最大空间是固定的】

📝 char(n):n 表示的是总字符数【占用的空间是固定的】


③ 日期时间类型

(4) 创建员工表

CREATE TABLE employer   (
  id INT UNSIGNED COMMENT '编号',
  `work_no` VARCHAR(10) COMMENT '工号',
  `name` VARCHAR(10) COMMENT '姓名',
   age TINYINT(3) UNSIGNED COMMENT '年龄' ,
    `gender` CHAR(1) COMMENT '性别',
     id_number CHAR(18) COMMENT '身份证号',
     entry_date DATE COMMENT '入职时间'
) COMMENT '员工表'

(5) 修改表结构

📌 ① 往表中新增字段

为 employer 表新增【昵称】字段, 字段名为 nickname,类型为 varchar(20)

ALTER TABLE employer ADD nickname VARCHAR(20) COMMENT '昵称';

📌 ② 修改表中的字段

  • 修改字段的数据类型

把 employer 表中 id 字段的数据类型修改为 BIGINT, 也无符号

ALTER TABLE employer MODIFY id BIGINT UNSIGNED;
  • 修改字段名和数据类型

修改 employer 表中 nickname 字段的字段名为 username,类型是 varchar(30)

ALTER TABLE employer CHANGE nickname username VARCHAR(30) COMMENT '用户名'

📌 ③ 删除表中的字段

删除 employer 表中的 age 字段

ALTER TABLE employer DROP age

📌 ④ 修改表名

把 employer 表的表名修改为 staff

ALTER TABLE employer RENAME TO staff

(6) 删除某一张表

删除数据库中的表名为 emp 的表,如果存在的话

DROP TABLE IF EXISTS emp

删除指定表,并重新创建表(类似删除表中的全部数据)

七、DML

DELETE FROM table_name
TRUNCATE table_name

八、DQL

(1) 员工表

create table emp (
  id int comment '编号',
  workno varchar(10) comment '工号',
  name varchar(10) comment '姓名',
  gender char(1) comment '性别',
  age tinyint unsigned comment '年龄',
  idcard char(18) comment '身份证号',
  workaddress varchar(50) comment '工作地址',
  entrydate varchar(33) comment '入职时间'
)comment '员工表';
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

(2) distinct、as

distinct: 去除查询结果的重复项

(3) where

查询没有身份证号的员工信息

select * from emp where idcard is null;

查询有身份证号的员工信息

select * from emp where idcard is not null;

查询年龄不等于88岁的员工信息

select * from emp where age != 88;
select * from emp where age <> 88;

查询年龄在 15 到 20 岁的员工信息(包含15和20岁)

select * from emp where age between 15 and 20;
select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;

查询年龄是 18、20 或 40 的员工信息

select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in (18, 20, 40);

查询姓名为两个字的员工信息

select * from emp where name like '__' # 两个下划线

查询身份证号最后一位为 x 的员工信息

select * from emp where idcard like '%x';

(4) 聚合函数

1、统计员工数量

select count(*) 'employeeNum' from emp;
select count(id) as '员工数量' from emp;

2、统计员工平均年龄

select avg(age) '平均年龄' from emp;

3、统计员工最大年龄

select max(age) '最大年龄' from emp;

4、统计员工最小年龄

select min(age) '最小年龄' from emp;

5、统计西安地区员工的年龄之和

select sum(age) '年龄和' from emp where workaddress = '西安';

(5) group by、where 和 having 区别

分组(GROUP BY)一般要结合聚合函数一起使用。

1、根据性别分组,统计男性员工和女性员工的数量

SELECT
  gender,
  count( gender ) '人数'
FROM
  emp 
GROUP BY
  gender;

2、根据性别分组,统计男性员工和女性员工的平均年龄

SELECT
  gender,
  avg( age ) '平均年龄' 
FROM
  emp 
GROUP BY
  gender

3、查询年龄小于45岁的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址

SELECT
  count( * ) '人数',
  workaddress 
FROM
  emp 
WHERE
  age < 45 GROUP BY workaddress HAVING count( * ) >= 3;

(6) order by

1、根据年龄对公司的员工进行升序排序

select name, age from emp order by age asc;
select name, age from emp order by age;

2、根据入职时间,对员工进行降序排序

SELECT
  * 
FROM
  emp 
ORDER BY
  entrydate DESC;

3、根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序

SELECT
  * 
FROM
  emp 
ORDER BY
  age,
  entrydate DESC;

(7) 分页查询

1、查询第1页员工数据, 每页展示10条记录

select * from emp limit 0, 10;
select * from emp limit 10;

2、查询第2页员工数据, 每页展示10条记录

select * from emp limit 10, 10;

(8) exercise

select * from emp where age >= 20 && age <= 23;
select * from emp where age >= 20 and age <= 23;
select * from emp where age between 20 and 23;
select * from emp where age in (20, 21, 22, 23);
select * from emp where age = 20 or age = 21 or age = 22 or age = 23;

SELECT
  * 
FROM
  emp 
WHERE
  gender = '男' 
  AND age BETWEEN 20 
  AND 40 
  AND `name` LIKE '___';

SELECT
  gender,
  count( * ) 
FROM
  emp 
WHERE
  age < 60 
GROUP BY
  gender;

SELECT
  `name`,
  age,
  entrydate 
FROM
  emp 
WHERE
  age <= 35 
ORDER BY
  age,
  entrydate DESC;

SELECT
  * 
FROM
  emp 
WHERE
  gender = '男' 
  AND age BETWEEN 20 
  AND 40 
ORDER BY
  age,
  entrydate 
  LIMIT 5;

(9) DQL 语句的执行顺序

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
关系型数据库 MySQL 分布式数据库
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
35 2
|
21天前
|
SQL 数据库 索引
关于 SAP ABAP REPOSRC 数据库表在 HANA 中的 DDL Definition
关于 SAP ABAP REPOSRC 数据库表在 HANA 中的 DDL Definition
18 1
关于 SAP ABAP REPOSRC 数据库表在 HANA 中的 DDL Definition
|
2天前
|
SQL 存储 关系型数据库
数据库开发之mysql前言以及详细解析
数据库开发之mysql前言以及详细解析
12 0
|
17天前
|
存储 关系型数据库 MySQL
MySQL基础入门:数据库操作全攻略
MySQL基础入门:数据库操作全攻略
47 0
|
17天前
|
关系型数据库 MySQL 数据库
卸载云服务器上的 MySQL 数据库
卸载云服务器上的 MySQL 数据库
33 0
|
7天前
|
SQL 关系型数据库 MySQL
MySQL环境搭建——“MySQL数据库”
MySQL环境搭建——“MySQL数据库”
|
7天前
|
SQL NoSQL 关系型数据库
初识MySQL数据库——“MySQL数据库”
初识MySQL数据库——“MySQL数据库”
|
8天前
|
SQL 数据库 HIVE
Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
【4月更文挑战第8天】Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
19 0
|
9天前
|
关系型数据库 MySQL 数据库
数据库基础(mysql)
数据库基础(mysql)
|
10天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。
36 3