MySQL入门

最新推荐文章于 2024-11-01 14:31:36 发布

汪汪miao~

最新推荐文章于 2024-11-01 14:31:36 发布

阅读量1.2k

点赞数 22

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

本文链接：https://blog.csdn.net/qq_45559559/article/details/137185669

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1. 数据库相关概念

数据库：存储数据的仓库，数据是有组织的进行存储。
数据库管理系统：操作和管理数据库的大型软件。
SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。
关系型数据库：建立在关系模型的基础上，由多张互相连接的二维表组成的数据库。

2. SQL

2.1 SQL的通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格或缩进增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释：
单行注释：- -
多行注释：/* * /

2.2 SQL分类

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

2.2.1 DDL

数据库操作

查询
查询所有数据库：SHOW DATABASES;
查询当前数据库：SELECT DATABASES();

创建
CREATE DATABASES [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则] ;

删除
DROP DATABASES [IF EXISTS] 数据库名；

使用
USE 数据库名；

表操作

查询：
查询当前数据库所有的表：SHOW TABLES;
查询表结构：DESC 表名；
查询指定表的建表语句：SHOW CREATE TABLE 表名；

创建
CREATE TABLE 表名 (
字段类型 [COMMENT 注释]，
. . . . .
) ;
示例：创建一张员工表：
create table emp (
id int comment ‘编号’,
workid varchar(10) comment ‘工号’,
name varchar(10) comment ‘姓名’,
gender char(1) comment ‘性别’,
age tinyint unsigned commet ‘年龄’,
idcard char(18) comment ‘身份证号’,
joindate data commment ‘入职时间’
) comment ‘员工表’ ;

MySQL数据类型：

修改
添加字段：
ALTER TABLE 表名 ADD 字段名类型(长度) [comment 注释] [约束];
示例：为emp增加一个新的字段“昵称” 为nickname，类型为varchar(20)
alert 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 ‘用户名’;
删除字段：
ALERT TABLE 表名 DROP 字段名;
示例：将emp表的字段username删除
alert table emp drop username;
修改表名：
ALERT TABLE 表名 RENAME TO 新表名;
示例：将emp表修改为employee
alert table emp raname to employee;

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

2.2.2 DML

添加数据

给指定字段添加数据：
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, …);

修改数据
UPDATE 表名 SET 字段名1 = 值1，字段名名2 = 值2，… [WHERE 条件];

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

2.2.3 DQL

基本查询

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

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

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

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表；
条件：
大于 >
大于等于 >=
小于 <
小于等于 <=
等于 =
不等于 <> 或！=
BETWEEN … AND … 在某个范围之内
IN(…) 在in之后的列表中的值，多选一
LIKE占位符模糊匹配(_匹配当字符，%匹配任意个字符)
IS NULL 是NULL
IS NOT NULL 不是NULL
AND 或 && 并且(多个条件同时成立)
OR 或 || 或者(多个条件任意一个成立)
NOT 或！非，不是

聚合函数

聚合函数是将一列数据作为一个整体进行纵向计算
统计数量 COUNT
最大值 MAX
最小值 MIN
平均值 AVG
求和 SUM

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

分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]
WHERE 与 HAVING 区别：
执行时机不同：WHERE 是分组之前进行过滤，不满足WHERE条件不参与分组，HAVING是分组之后对结果进行过滤
判断条件不同：WHERE 不能对聚合函数进行判断而HAVING可以

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2 ;
排序方式
ASC：升序(默认值)
DESC：降序

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
起始索引从0开始，起始索引=(查询页码 - 1)*每页记录数

DQL的执行顺序：

3. 函数

3.1字符串函数

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

3.2 数值函数

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

3.3 日期函数

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

3.4 流程函数

IF(value，t，f)：如果value为true则返回t，否则返回f
IFNULL(value1,value2)：如果vlaue1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default ] END：
如果val1为true，返回res1，… 否则返回default默认值
示例：查询所有成绩，其中数学成绩85及以上为优秀，65及以上为及格，其他为不及格。
 select 
     id,
 	name,
 	case when math >= 85 then '优秀' when math >= 60 '及格' else '不及格',
 	english,
 	chinese,
 from score;
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END：如果expr的值等于val1，返回res1，…否则返回default默认值。
示例：查询emp表的员工姓名和工作地址（北京/上海 --> 一线城市，其他 --> 二线城市）
select 
name ,
case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end;
from emp;

4. 约束

约束时作用于表中字段上的规则，用于限制存储在表中的数据。

4.1 约束的分类

非空约束：NOT NULL
限制该字段的数据不能为null

唯一约束：UNIQUE
保证该字段的所有数据都是唯一、不重复的

主键约束：PRIMARY KEY
主键是一行数据的唯一标识，要求非空且唯一

默认约束：DEFAULT
保存数据时如果未指定该字段的值则采用默认值

检查约束：CHECK
保证字段值满足某一个条件

外键约束：FOREIGN KEY
用来让两张表的数据之间建立连接，保证数据的一致性和完整性

示例：
在这里插入图片描述

create table user (
    id int primary key auto_increment comment '主键',
	name varchar(10) not null unique comment '姓名',
	age int check(age > 0 && age <=120) comment '年龄',
	status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

4.2 外键约束

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

添加外键：
CREATE TABLE 表名(
	字段名   数据类型,
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERNCES 主表(主表列名);
删除外键：
ALERT TABLE 表名 DROP FOREIGN KEY 外键字段名;
删除/更新行为：
NO ACTION : 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
RESTRICT：当父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
CASCADE：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
SET NULL：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为NULL(首先要求该外键允许取NULL)
SET DEFAULT：父表变更时，子表将外键列设置成一个默认值

示例：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERNCES 主表(主表列名) ON UPDATE ON DELETE CASCADE;

5. 多表查询

5.1 多表关系

一对多：
在多的一方建立外键，指向一的一方的主键。

多对多：
建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键。

一对一：
多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，提高操作效率。
在任意一方中加入外键，关联另外一方的主键，并且设置外键为唯一的

5.2 多表查询概述

笛卡尔积：两个集合：A 和 B 的所有组合情况。
在多表查询时，需要消除无效的笛卡尔积。

多表查询的分类：
连接查询：
内连接：相当于查询A、B交际部分数据

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

子查询：

5.3 内连接

隐式内连接：
select 字段列表 from 表1，表2 where 条件…;
显示内连接：
select 字段列表 from 表1 [inner] join 表2 on 连接条件…;

5.4 外连接

左外连接：
select 字段列表 from 表1 left [outer] join 表2 on 条件…；
相当于查询表1(左表)的所有数据包含表1 和表2 交集部分的数据
右外连接：
select 字段列表 from 表1 right [outer] join 表2 on 条件…;

5.5 自连接

5.6 联合查询

使用 union 或 union all ，把多次查询的结果合并起来，形成一个新的查询结果集，对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all ：会将全部的数据直接合并在一起，

union：会对合并之后的数据去重。

语法：
select 字段列表 from 表A
union [all]
select 字段列表 from 表B；

5.7 子查询

SQL语句中嵌套select语句

语法：select * from t1 where colum1 = (select colume1 from t2);
分类：
根据查询结果分：
标量字查询：子查询结果为单个值；
列字查询：子查询结果为一列；
行字查询：子查询结果为一行；
表子查询：子查询结果为多行；

根据位置分：
where之后
from 之后
select之后