MySQL基础（SQL增删改查、用户权限、函数、约束等）

一个人，走巷陌

已于 2023-08-09 22:38:16 修改

阅读量1.9k

点赞数 1

分类专栏： MySQL 文章标签： mysql linux

于 2023-05-22 21:14:25 首次发布

本文链接：https://blog.csdn.net/qq_54206164/article/details/130811879

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中数据定义（DDL）、数据操作（DML）、数据查询（DQL）和数据控制（DCL）的语言，包括创建和管理数据库、表，添加、修改和删除数据，查询语法，用户管理和权限控制等方面，涵盖了数据库管理的基础操作和重要概念。

摘要由CSDN通过智能技术生成

3、创建数据库(创建student数据库)

DDL（数据定义语言）

一、数据库操作

1、查询所有数据库

show databases;

2、查询当前数据库

select database();

3、创建数据库(创建student数据库)

create database if not extists student;

#指定字符集创建teacher数据库
create database teacher default charset utf8mb4;

4、删除数据库

drop database [ if exists ] 数据库名;

5、切换数据库

use 数据库名;

二、表操作

1、查询当前数据库所有表

show tables;

2、查看指定表结构

desc 表名;

3、查询指定表的建表语句

show create table 表名 ;

4、创建表结构

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
...
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

5、添加字段

ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

6、修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

7、修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

8、删除字段

ALTER TABLE 表名 DROP 字段名;

9、修改表名

ALTER TABLE 表名 RENAME TO 新表名;

10、删除表

DROP TABLE [ IF EXISTS ] 表名;

DML（数据操作语言）

一、添加数据

1、给指定字段添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); 1

2、给全部字段添加数据

INSERT INTO 表名 VALUES (值1, 值2, ...);

3、批量添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值
1, 值2, ...) ;

二、修改数据

1、修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

三、删除数据

1、删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

DQL(数据查询语言)

一、基本语法

1、查询多个字段

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ; 

SELECT * FROM 表名 ;

2、字段设置别名

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;

SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

3、去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

二、条件查询

1、基本语法

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

2、查询条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN ... AND ...	在某个范围之内 ( 含最小、最大值 )
IN(...)	在 in 之后的列表中的值，多选一
LIKE 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
IS NULL	是null
AND 或 &&	并且 ( 多个条件同时成立 )
OR 或 \|\|	或者 ( 多个条件任意一个成立 )
NOT 或 !	非 , 不是

3、聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sun	求和

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

三、分组查询

基本

1、基本语法

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

四、排序查询

1、基本语法

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

2、排序方式

ASC : 升序(默认值)
DESC: 降序

五、分页查询

1、基本语法

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

例：select * from student limit 10;

select * from student limit (2-1) , 10;

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

DCL(数据控制语言)

一、管理用户

1、查询用户

select * from mysql.user;

2、创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

3、修改用户密码

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

4、删除用户

DROP USER '用户名'@'主机名' ;

二、权限控制

1、常用权限

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

2、查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

3、授予权限

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

4、撤销权限

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

三、函数

1 、字符串函数

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

例：

concat : 字符串拼接
```
select concat('Hello' , ' MySQL');
```
lower : 全部转小写
```
select lower('Hello');
```
upper : 全部转大写
```
select upper('Hello');
```
lpad : 左填充
```
select lpad('01', 5, '-');
```
rpad : 右填充
```
select rpad('01', 5, '-');
```
trim : 去除空格
```
select trim(' Hello MySQL ');
```
substring : 截取子字符串
```
select substring('Hello MySQL',1,5);
```

2、数值函数

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

例：

ceil：向上取整
```
select ceil(1.1);
```
floor ：向下取整
```
select floor(1.9);
```
mod ：取模
```
select mod(7,4);
```
rand：获取随机数
```
select rand();
```
round：四舍五入
```
select round(2.344,2);
```

3、日期函数

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

例：

curdate：当前日期
```
select curdate();
```
curtime：当前时间
```
select curtime();
```
now：当前日期和时间
```
select now();
```

YEAR , MONTH , DAY：当前年、月、日

select YEAR(now());
select MONTH(now());
select DAY(now());

date_add：增加指定的时间间隔

select date_add(now(), INTERVAL 70 YEAR );

datediff：获取两个日期相差的天数

select datediff('2021-10-01', '2021-12-01');

4、流程函数

函数	功能
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 默认值

例：

if
```
select if(false, 'Ok', 'Error');
```

ifnull

select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

case when then else end

需求：查询emp表的员工姓名和工作地址
(北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

SELECT
	NAME,
	(
		CASE workaddress
		WHEN '北京' THEN
			'一线城市'
		WHEN '上海' THEN
			'一线城市'
		ELSE
			'二线城市'
		END
	) AS '工作地址'
FROM
	emp;

四、约束

1、概述

约束	描述	关键字
非空约束	限制该字段的数据不能为 null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	主键是一行数据的唯一标识，要求非空且唯一	DEFAULT
检查约束	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

2、约束演示

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID 唯一标识	int	主键，并且自动增长	PRIMARY KEY, AUTO_INCREMENT
name	姓名	varchar(10)	不为空，并且唯一	NOT NULL , UNIQUE
age	年龄	int	大于 0 ，并且小于等于 120	CHECK
status	状态	char(1)	如果没有指定该值，默认为 1	DEFAULT
gender	性别	char(1)

五、多表查询

一对多(多对一)
多对多
一对一

1、分类

连接查询
内连接：相当于查询A、B交集部分数据
外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名

2、内连接

内连接查询的是两张表交集部分的数据

1). 隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

2).显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

3、外连接

1). 左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

2). 右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

4、联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

5、子查询

SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

（子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。）

六、事务

1 、事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2、控制事务一

1). 查看 / 设置事务提交方式

SELECT @@autocommit ;
SET @@autocommit = 0 ;

2). 提交事务

COMMIT;

3). 回滚事务

ROLLBACK;

3、控制事务二

1). 开启事务

START TRANSACTION 或 BEGIN ;

2). 提交事务

COMMIT;

3). 回滚事务

ROLLBACK;

4 、事务四大特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性，简称ACID

一个人，走巷陌

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
MySQL基础（SQL增删改查、用户权限、函数、约束等）

非空约束限制该字段的数据不能为nullNOT NULL唯一约束保证该字段的所有数据都是唯一、不重复的UNIQUE主键约束主键是一行数据的唯一标识，要求非空且唯一PRIMARYKEY默认约束主键是一行数据的唯一标识，要求非空且唯一DEFAULT检查约束保证字段值满足某一个条件CHECK外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性FOREIGNKEY。
复制链接

扫一扫