Database - MySQL - Syntax

1. 数据库语言

数据库 CRUD 增删改查

DDL: Data Definition Language, 定义, CREATE/DROP/ALTER/VIEW/INDEX/SYNC/CLUSTER
DML: Data Management Language，管理, INSERT/UPDATE/DELETE
DQL: Data Query Language，查询， SELECT... FROM... WHERE
DCL: Data ControlLanguage，控制, GRANT/ROLLBACK/COMMIT/REVOKE

操作数据库 > 操作数据库中的表 > 操作数据库中表的数据

mysql关键词不区分大小写

2. 数据库操作 - DDL

操作数据库：创建数据库 /删除数据库/使用数据库/查看数据库 CREATE/DROP/USE/SHOW

CREATE DATABASE IF NOT EXISTS westos;

DROP DATABASE IF EXISTS hello;

USE `school`;

SHOW DATABASES;

数据库的列类型 MySQL中数据类型介绍 - myxlp - 博客园 (cnblogs.com)

3. 数据库表操作 - DDL

操作数据库表：创建表/删除表/修改表 CREATE/DROP/ALTER

创建

CREATE TABLE [IF NOT EXISTS] `表名` (

`字段名`  列类型  [属性]  [索引]  [注释],
`字段名`  列类型  [属性]  [索引]  [注释]
) [表类型]  [字符集设置]  [注释]

CREATE TABLE IF NOT EXISTS `student` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

删除表

DROP TABLE IF EXISTS teacher1

修改表

--修改表名：ALTER TABLE 旧表名 RENAME AS 新表名

ALTER TABLE teacher RENAME AS teacher1

--增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性

ALTER TABLE teacher1 ADD age INT(11)

--修改表的字段（重命名，修改约束）
ALTER TABLE teacher1 MODIFY age VARCHAR(11) --修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1)--字段重命名

--删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teaacher1 DROP age1

外键

-- -- 方式一：创建表的时候创建<br>CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT NOT NULL COMMENT '学生的年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 方式一：创建表成功之后，添加外键约束
CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT NOT NULL COMMENT '学生的年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上的操作都是物理外键，数据库时候的外键，不建议使用。避免数据库过多，造成困扰。

Best Practices: 数据库单纯，使用程序实现软外键功能。

4. 数据管理语言 - DML

4.1 Insert, update, delete

Insert

insert into 表明([字段名1， 字段2， 字段3]) values('值1'), ('值2'), ('值3'), ...

INSERT INTO `student` VALUES(5,'李四','aaaa','男','2000-1-1',1,'南京','z@z.com');

update

UPDATE 表名 set column_name = value[colnum_name = value......] where [条件]

UPDATE `student` SET `name`='zzz', `email`='bb@cc.com' WHERE id=1;

delete

delete from (表名) [where 条件]

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

-- 清空student表
DELETE FROM `student`   
TRUNCATE student   -- 冲洗设置自增列

5. 数据查询语言 - DQL

Select完整语法

SELECT [all | distinct]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][...]]}
from table_name [as table_alias]
    [left | right | inner join table_name2]        -- 联合查询
    [where ...]        -- 指定结果需满足的条件
    [group by ...]    -- 指定结果按照哪几个字段来分组
    [having]        -- 过滤分组的记录必须满足的次要条件
    [order by]        -- 指定查询记录按一个或多个条件排序
    [limit {[offser,]row_count | row_countOFFSET offset}]    -- 指定查询的记录从哪条至哪条

5.1 一般表达式

语法：SELECT 字段... FROM 表

可以起别名，用AS关键字。语法：字段名 as 别名 表名 as 别名

-- 查询全部的学生 SELECT 字段 FROM 表 
SELECT * FROM student

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表其别名
SELECT `studentno` AS 学号,`studentname` AS 学生名字 FROM student AS s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名：', StudentName) AS 新名字 FROM student

5.1.1 去重: DISTINCT

5.1.2 select 表达式

SELECT VERSION()-- 查询系统版本（函数）
SELECT 100 * 3 - 1 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学院考试成绩 + 1分查看
SELECT `studentno`,`studentResult`+1 AS '提分后' FROM result

5.2 Where

精确查询

运算符	语法	描述
and &&	a and b, a&&b	逻辑与
or \|\|	a or b , a\|\| b	逻辑或
Not ！	not a, ! a	逻辑非

SELECT `studentno` ,`studentresult` FROM result
 
-- 查询考试成绩在 95~100分之间
SELECT `studentno` ,`studentresult` FROM result
WHERE `studentresult` >= 95 AND `studentresult` <= 100
 
-- 模糊查询（区间）
SELECT `studentno` ,`studentresult` FROM result
WHERE `studentresult` BETWEEN 95 AND 100
 
-- 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE `studentno`!=1000
 
-- != not
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno` = 1000

模糊查询

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符为not null，结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
Like	a like b	SQL匹配，如果a匹配b，则结果为真
In	a in (a1, a2, a3...)	假设a在a1，或者a2... 其中的某一个值，结果为真

-- ====================模糊查询=============
-- 查询姓张的同学
-- like结合 %（代表0到任意个字符） _（一个字符）
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张%'

-- 查询姓张的同学，名字后面只有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张_'


-- 查询姓张的同学，名字后面只有两个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张__'


-- 查询姓张的同学，名字后面只有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张_'

-- 查询名字中间有嘉字的同学， %嘉%
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%嘉%'

-- =========in =======
-- 查询1001,1002,1003号学院
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(1001,1002,1003);

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('北京朝阳','广东深圳');

-- ===== null not null =====

-- 查询地址为空的学生 null ''
SELECT `studentno`,`studentname` FROM `student`
WHERE address = '' OR address IS NULL

-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

-- 查询有出生日期的同学 为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL

5.3 连表查询 SQL Join

-- ========== 联表查询 join ==========

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*
1. 分析需求，分析查询的字段来自哪些表（连接查询）
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的studentno = 成绩表 studentno
*/

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentno = r.studentno

-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s    -- 这里用空格代替了AS
RIGHT JOIN result r
ON s.studentno = r.`studentno`

-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s    -- 这里用空格代替了AS
LEFT JOIN result r
ON s.studentno = r.`studentno`
/*
  如果关键子使用where的话，则会两表所有信息，效果如inner join
  left join 左连接：展示左表所有数据，右表符合on条件的数据，右表不符合的则为空显示。
  right join 右连接：展示右表所有数据，左表符合on条件的数据，左表不符合的则为空显示。
*/

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s    -- 这里用空格代替了AS
LEFT JOIN result r
ON s.studentno = r.`studentno`
WHERE `studentresult` IS NULL

-- 查询了参加考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`studentname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`

-- 我要查询哪些数据select...
-- 从那几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

-- from a left join b
-- from a right join b

5.4 自连接

一张表拆为两张一样的表即可

SELECT a.`categoryName` AS FATHER, b.`categoryName` AS CHILD 
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`;

5.5 分页和排序

排序

-- 查询的结果根据成绩降序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
RIGHT JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC

分页

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
RIGHT JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC
LIMIT 0,5

5.6 子查询

SELECT `studentno`,studentname FROM student WHERE studentno IN(
  SELECT studentno FROM result WHERE studentresult>80 AND subjectno = (
    SELECT subjectno FROM `subject` WHERE subjectname  = '高等数学-2'
  )
)

6 MySQL 常用函数

6.1 常用函数

MySQL 函数 (w3schools.cn)

-- 数***算
select abs(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 返回一个0到1之间的随机数
select sign(-10) -- 判断一个数的符号 0返回0，负数返回-1，正数返回1

-- 字符串函数
select char_length('无限进步')    -- 字符串长度
select concat('我','爱','d') -- 拼接字符串
select insert('我爱d',2,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
select lower('iLoveD') -- 转小写字母
select upper('iLoveD') -- 转大写字母
select instr('iloved','d') -- 返回第一次出现的子串的索引
select replace('ta说ta想要暴富','说','暴富') -- 替换出现的指定字符串
select substr('ta说ta想要暴富',7,3) -- 返回指定的子字符串 (原字符串，截取的位置，截取的长度）
select reverse('ta说ta想要暴富') -- 反转

-- 查询姓周的同学，名字 邹
select replace(studentname,'周','邹') from student 
where studentname like '周%'

-- 时间和日期（记住）
select current_date() -- 获取当前日期
select curdate() -- 获取当前日期
select now() -- 获取当前的时间
select localtime() -- 本地时间
select sysdate() -- 获取系统时间

select year(now())
SELECT month(NOW())
SELECT day(NOW())
SELECT hour(NOW())
SELECT minute(NOW())
SELECT second(NOW())

-- 系统
select system_user()
select user()
select version()

6.2 聚合函数

-- ===============聚合函数===================
-- 都能够统计 表中的数据（想查询一个表中有多少个记录，就使用这个count()）
SELECT COUNT(`studentname`) FROM student; -- Count(指定列）会忽略所有的null值
SELECT COUNT(*) FROM student; -- Count(*)，不会忽略null值，本质：计算行数
SELECT COUNT(1) FROM result; -- Count(1)，不会忽略null值

SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均数 FROM `result`
SELECT MAX(`studentresult`) AS 最高分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`

-- 查询不同课程的平均分，最高分，最低分
-- 核心： （根据不同的课程分组）
SELECT any_value(`subjectname`), AVG(`studentresult`) AS 平均分, MAX(`studentresult`) AS 最高分, MIN(`studentresult`) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
-- where 条件中不能包含聚组函数
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>80

7、事务

ACID (Atomicity, Consistency, Durability, Isolation) 原子性，一致性，持久性，隔离性

MySQL默认是开启事务自动提交的

-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务

UPDATE account SET money=money-500 WHERE `name` = 'a' -- a减500
UPDATE account SET money=money+500 WHERE `name` = 'b' -- b加500

COMMIT; -- 提交事务，就被持久化了！
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值

8、索引

索引分类：
主键索引 PRIMARY KEY --- 唯一的标识，主键不可重复
唯一索引 UNIQUE KEY --- 避免重复的列出现，可以重复，多个列都可以标识为唯一索引
常规索引 KEY/INDEX --- key关键字设置
全文索引 FullText --- 在特定的数据库引擎下，用来快速定位数据

9、MySQL编程

-- 插入100万数据

DELIMITER $$ --写函数之前

CREATE FUNCTION mock_data()
RETURN INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;

WHILE i<num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES (CONCAT('用户',i),'7507547@qq.com', CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;

END;

10. 数据管理语言 - DCL

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER d IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改制定用户密码）
SET PASSWORD FOR d = PASSWORD('111111')

-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER d TO dd

-- 用户授权 grant all privileges 全部的权限， 库.表
-- all privileges 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO dd

-- 查询权限
SHOW GRANTS FOR dd -- 查看指定用户的权限 , 这个命令在8.0.33上面不工作
SHOW GRANTS FOR root@localhost

-- root用户权限：GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` with grant option

-- 撤销权限 revoke 哪些权限， 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM dd

-- 删除用户
DROP USER dd@localhost

备份

MySQL数据库备份的方式

直接拷贝物理文件
在Sqlyog这种可视化工具中手动导出
- 在想要导出的表或者库中，右键，选择备份的表或者库
使用命令行导出 mysqldump 命令行使用

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表明 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库> 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql

# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件

mysql -u用户名 -p密码 库名<备份文件

posted @ 2023-06-27 11:11 zjfun 阅读(19) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Database - MySQL - 安装/配置

· SSM - SpringMVC - Examples - SMBMS - SQL Sentences

· MySQL

· MySQL 基础

· MySQL笔记-基础

阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现

公告

昵称： zjfun
园龄： 8年2个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六