MySQL数据库基础——通用语法、函数、约束

StreetWolf

已于 2024-09-23 19:25:56 修改

阅读量813

点赞数 12

分类专栏： MySQL 文章标签：数据库 mysql

于 2024-09-23 19:21:27 首次发布

本文链接：https://blog.csdn.net/weixin_63461613/article/details/142213397

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言（一些废话）

版本

在撰写本博客时，所使用的

数据库为MySQL，版本8.0.37，

图形化界面工具为navicat，版本17

数据模型

看完上面这张图能理解一个数据库管理系统(DBMS)包含多个数据库，一个数据库包含多个表即可

编写数据库代码的方式

安装MySQL后，若不想用图形化界面工具的话可以WIN+R输入cmd，在cmd中输入

mysql -u root -p

回车后提示输入密码，正确输入密码后一样可以进行数据库的学习

一、通用语法

MySQL的SQL语句不区分大小写，建议关键字大写

SQL语句以分号;结尾

-- 单行注释（注意空格）

# 单行注释（注意空格）（MySQL特有）

/*多行注释*/

DDL	数据定义语言	定义数据库对象（数据库，表，字段）
DML	数据操作语言	对表中数据增删改
DQL	数据查询语言	查询表中记录
DCL	数据控制语言	创建数据库用户、控制数据库访问权限

数据定义语言

数据库操作

-- 查询所有数据库：
SHOW DATABASES;
-- 查询当前数据库：
SELECT DATABASE();
-- 创建数据库：
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
-- 删除数据库：
DROP DATABASE [ IF EXISTS ] 数据库名;
-- 使用数据库：
USE 数据库名;

例子：

SHOW DATABASES;
SELECT DATABASE();
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8mb4;
DROP DATABASE IF EXISTS test;
-- 运行下一句代码会报错，因为上面已经把这个数据库删除了
USE test;

表操作

在创建表的过程中，最后一个字段后面不要加逗号，如果注释是中文，需要加上引号

-- 查询当前数据库所有表：
SHOW TABLES;
-- 查询表结构：
DESC 表名;
-- 查询指定表的建表语句：
SHOW CREATE TABLE 表名;
-- 创建表
CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	字段3 字段3类型 [COMMENT 字段3注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];

例子：

SHOW TABLES;
DESC db;
SHOW CREATE TABLE db;
CREATE TABLE IF NOT EXISTS test(
id INT COMMENT '编号',
name VARCHAR(20) COMMENT '姓名'
) COMMENT '测试表';

修改与删除表及字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
-- 例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

-- 修改数据类型：
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-- 修改字段名和字段类型：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
-- 例：将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

-- 删除字段：
ALTER TABLE 表名 DROP 字段名;

-- 修改表名：
ALTER TABLE 表名 RENAME TO 新表名

-- 删除表：
DROP TABLE [IF EXISTS] 表名;
-- 删除表，并重新创建该表：
TRUNCATE TABLE 表名;

例子：

ALTER TABLE test ADD nickname varchar(20) COMMENT '昵称';
ALTER TABLE test MODIFY nickname varchar(25);
ALTER TABLE test CHANGE nickname username varchar(26) COMMENT '昵称';
ALTER TABLE test DROP name;
ALTER TABLE test RENAME TO testFang;
TRUNCATE TABLE testFang;
DROP TABLE IF EXISTS testFang;

数据操作语言

添加数据（插入数据）

字符串和日期类型数据应该包含在引号中

插入的数据大小应该在字段的规定范围内

-- 指定字段：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
-- 全部字段：
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 批量添加数据：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

例子：

INSERT INTO testFang (id, username) VALUES(3, '老wolf');
INSERT INTO testFang VALUES(5, '睿智阿杰');
INSERT INTO testFang (id, username) VALUES(6, '小明'), (7, '泉');
INSERT INTO testFang VALUES(11, '邹邹');

更新数据、删除数据

-- 修改数据：
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];

-- 删除数据：
DELETE FROM 表名 [ WHERE 条件 ];

例子：

UPDATE testFang SET username = 'Fang' WHERE id = 3;
DELETE FROM testFang WHERE id = 11;

数据查询语言

语法

SELECT	字段列表	基本查询
FROM	表名字段	基本查询
WHERE	条件列表	条件查询
GROUP BY	分组字段列表	分组查询
HAVING	分组后的条件列表
ORDER BY	排序字段列表	排序查询
LIMIT	分页参数	分页查询

DQL执行顺序

FROM > WHERE > GROUP BY > SELECT > ORDER BY > LIMIT

基础查询

-- 查询多个字段：
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;

-- 设置别名：
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;

-- 去除重复记录：
SELECT DISTINCT 字段列表 FROM 表名;

例子：

SELECT username FROM testFang;
SELECT * FROM testFang;

SELECT id AS user_id FROM testFang;
SELECT username name FROM testFang;

SELECT DISTINCT username FROM testFang;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN...AND...	在某个含最大最小值的范围内
IN(...)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配
IS NULL	NULL

模糊匹配中：_匹配单个字符，%匹配任意个字符

AND或&&	且
OR或\|\|	或
NOT或!	非

例子：

注意，之前的表名是testFang，这里我将表名更换为了tg方便书写

SELECT * FROM tg WHERE id = 3;
SELECT * FROM tg WHERE id > 5;

SELECT * FROM tg WHERE id IS NULL OR id = '';
SELECT * FROM tg WHERE id;
SELECT * FROM tg WHERE id IS NOT NULL;

SELECT * FROM tg WHERE id != 7;
SELECT * FROM tg WHERE id BETWEEN 3 AND 7;
SELECT * FROM tg WHERE id < 20 and username != '睿智阿杰';

SELECT * FROM tg WHERE id = 3 OR id = 5 OR id = 7;
SELECT * FROM tg WHERE id in(6, 5, 7);

SELECT * FROM tg WHERE username LIKE '__';

SELECT * FROM tg WHERE username LIKE '%杰';

聚合查询（聚合函数）

count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：

SELECT 聚合函数(字段列表) FROM 表名;

例子：

SELECT count(id) FROM tg;
SELECT max(id) FROM tg;

说明：为了之后的示例能够更为方便，我创建了一个数据库(streetwolf)、数据表(identity)和一些数据(如下)

ID	NAME	AGE	PROVINCE	GENDER	CARDID	PHONE
1	方方	21	广东	男	523333	1234567
2	阿杰	22	青海	男	203633	2345678
3	松神	20	广东	男	102777	3456789
4	铁	21	广东	女	203999	4567853
5	邹邹	19	海南	女	322111	6325978

CREATE DATABASE IF NOT EXISTS streetwolf;

CREATE TABLE IF NOT EXISTS identity(
id INT COMMENT '编号',
name VARCHAR(16) COMMENT '姓名',
age INT COMMENT '年龄',
province VARCHAR(8) COMMENT '省份',
gender VARCHAR(3) COMMENT '性别',
cardid INT COMMENT '卡号',
phone INT COMMENT '电话'
)COMMENT '身份信息表';

INSERT INTO identity VALUES
(1,'方方',21,'广东','男',523333,1234567),
(2,'阿杰',22,'青海','男',203633,2345678),
(3,'松神',20,'广东','男',102777,3456789),
(4,'铁',21,'广东','女',203999,4567853),
(5,'邹邹',19,'海南','女',322111,6325978),
(6,'小明',24,'山西','男',NULL,3652186)

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ]

	作用时间	能否判断聚合函数
where	分组之前进行过滤，不满足where条件不参与分组	不能
having	分组后对结果进行过滤	能

执行顺序：where、聚合函数、having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

-- 根据性别分组（仅分组）
SELECT count(*) FROM identity GROUP BY gender;
-- 根据性别分组，显示性别
SELECT gender, count(*) FROM identity GROUP BY gender;
-- 统计男生女生的平均年龄
SELECT gender, avg(age) FROM identity GROUP BY gender;
-- 查询年龄小于21的员工来自哪些省份，数量多少
SELECT province, count(*) FROM identity WHERE age <= 21 GROUP BY province;
-- 查询满足年龄小于45且数量大于等于2的员工来自哪些省份，数量多少
SELECT province, count(*) address_count FROM identity WHERE age < 45 GROUP BY province having address_count >= 2;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：升序ASC（默认），降序DESC

例子：

SELECT * FROM identity ORDER BY age ASC, id DESC;

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数

分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT

如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

例子：

数据控制语言

管理用户

开发人员用的比较少，主要是数据库管理人员在使用

-- 查询用户：

USE mysql;
SELECT * FROM user;
-- 创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 修改用户密码：
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

-- 删除用户：
DROP USER '用户名'@'主机名';

例子：

USE mysql;

CREATE USER IF NOT EXISTS 'li'@'localhost' IDENTIFIED BY '555555';
CREATE USER IF NOT EXISTS 'fang'@'%' IDENTIFIED BY '999999';
CREATE USER IF NOT EXISTS 'liangzai' IDENTIFIED BY '111111';

ALTER USER 'fang'@'%' IDENTIFIED WITH
mysql_native_password BY '2024';

DROP USER 'liangzai';

SELECT * FROM USER;

权限控制

常用权限如下表：

ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
ALTER	修改表
UPDATE	修改数据
DELETE	删除数据
DROP	删除数据库/表/视图
CREATE	创建数据库/表

-- 查询权限：
SHOW GRANTS FOR '用户名'@'主机名';

-- 授予权限：
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

-- 撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

多个权限用逗号分隔。授权时，数据库名和表名用*进行通配，代表所有。

二、函数

字符串函数

CONCAT(s1, s2, …, sn)	字符串拼接，将s1, s2, …, sn拼接成一个字符串
LOWER(str)	将字符串全部转为小写
UPPER(str)	将字符串全部转为大写
LPAD(str, n, pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str, n, pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)	返回从字符串str从start位置起的len个长度的字符串

-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小写
SELECT LOWER('Hello');
-- 大写
SELECT UPPER('Hello');
-- 左填充
SELECT LPAD('01', 5, '-');
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片（起始索引为1）
SELECT SUBSTRING('Hello World', 1, 5);

例子：

将不足两位的ID补充为两位，如1号补充为01，2号补充为02。

USE streetwolf;
UPDATE identity set id = LPAD(id,2,'0');

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x, y)	返回x/y的模
RAND()	返回0到1内的随机数
ROUND(x, y)	求参数x的四舍五入值，保留y位小数

日期函数

CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

例子：

流程函数

IF(value, t, f)	如果value为true，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END	如果expr的值等于val1，返回res1，… 否则返回default默认值

例子：

SELECT IF(true,'1','2');
SELECT IF(false,'3','4');

显示结果分别为1，4

SELECT IFNULL('ohyeah','default');
SELECT IFNULL('','default');
SELECT IFNULL(null, 'default');

显示结果分别为ohyeah，（什么也没有），default

SELECT
  name,
  (CASE WHEN province = '广东' OR province = '海南' OR province = '广西' THEN '华南分公司' WHEN province = '山西' THEN '华北分公司' ELSE '其他分公司' END)
  FROM identity;

三、约束

常用约束

主键	PRIMARY KEY
自动增长	AUTO_INCREMENT
不为空	NOT NULL
唯一	UNIQUE
逻辑条件	CHECK
默认值	DEFAULT

例子：

CREATE TABLE score(
  id INT PRIMARY KEY,
  os INT CHECK(os > 0 AND os < 100),
  status CHAR(1) DEFAULT '1',
  test_day DATE
) COMMENT '考核表';

INSERT INTO score VALUES(7, 90, 2, NULL);

上面这个成功运行

INSERT INTO score VALUES(8, 102, 2, NULL);

上面这个运行失败，因为os的值不符合条件

例子2：如果有自动递增的情况，可以直接忽略自动递增的值

CREATE TABLE IF NOT EXISTS area(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
classify VARCHAR(4) NOT NULL COMMENT '分类'
) COMMENT '情况表';
INSERT INTO area(id, classify) VALUES (NULL, '地级单位'), (NULL, '县级单位'), (NULL, '镇街单位');

外键约束

外键的概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

如上图，员工表的外键dept_id关联的是部门表的主键id，从图中可以看出金庸是总经办的，剩下四个是研发部的。员工表是部门表的子表。

-- 法一（创建表的时候添加外键）
CREATE TABLE 表名(
	字段名 字段类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
-- 法二（表创建完再添加）
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

创建表的时候增加外键或创建完表再增加外键。

ALTER TABLE score ADD CONSTRAINT fk_id FOREIGN KEY (status) REFERENCES area(id);

发现不能运行，发现原来score中的status列是char(1)类型，而area中的id是INT类型，类型不同，遂使用下面这行代码改变了score表中status列的类型后再尝试了一遍上面的代码，发现运行成功。

ALTER TABLE score MODIFY status INT;

在结束试验后我输入了下面这行代码，发现报错为引用的列和被引用的列数据类型不兼容。证实了自己的猜想。（报错信息为：3780 - Referencing column 'status' and referenced column 'id' in foreign key constraint 'fk_id' are incompatible.）

ALTER TABLE score MODIFY status char(1);

删除外键：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

例子：

ALTER TABLE score DROP FOREIGN KEY fk_id;

删除/更新行为

（默认行为） NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致）
（默认行为）RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致）
☆CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
☆SET NULL	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）
SET DEFAULT	父表有变更时，子表将外键设为一个默认值（Innodb不支持）

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为

简单理解为：在原本的语句上增加 ON UPDATE 行为 ON DELETE 行为

写到一万字之后卡顿严重，基础篇的多表查询与事务另开一个笔记。