MySqlNote DDL DML DQL操作

`MySQL` 数据库

MySQL是一款关系型数据库

关系型数据库

基本单位是表,一个表中存储一类信息,表与表之间存在关联关系

`sql`语言(`Structured Query Language`)

数据库操作语言
也属于一种编程语言,专门用作数据库操作
分为三种语言如下

`sql`安装使用流程

官网https://dev.mysql.com/downloads/mysql/ 下载对应Windows zip

2.安装解压到你所设定的文件路径

我的在C盘 Program FIle路径

3.配置mysql环境变量

在mysql-8.0.22-winx64目录里面找到bin文件所在目录
在此电脑右击鼠标选择属性-->点击高级系统设置-->点击环境变量(N)-->在系统变量(S)中找到Path变量

--->点击Path,选择编辑(I)-->把bin目录的地址复制在里面-->点击确定,完成

4.mysql初始化和启动

使用管理员的身份运行cmd

由于配置好了环境变量直接在当前目录输入初始化MySQL命令(如果没有配置环境变量,那么直接找到bin目录输入,如下)

mysqld --initialize-insecure

作用初始化数据库,并默认root密码为空

命令执行后,在mysql安装目录会生成一个data目录,说明初始化成功
为Windows系统安装MySQL服务

命令
```
mysqld install
```
作用: 为Windows系统安装MySQL服务,默认服务名为mysql

输入命令sc query mysql,查看一下名为mysql的服务
启动MySQL服务
- 命令net start mysql
- 作用:启动MySQL服务,注意此时的"mysql"表示服务名
- 到此mysql18安装完成!
- 补充:删除服务命令sc delete mysql 卸载时使用
为root账号设置密码
- 上面已经将root账号设置为空,可以使用root用户直接登录,然后修改密码.
- 修改密码命令ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
- 为了检验是否修改成功可以刷新重新登录命令FLUSH PRIVILEGES;
- 至此设置完工之后就可以使用相关指令在命令框进行数据库的操作了,也可以下载可视化工具

`DDL` 数据(结构)定义创建表

创建删除数据

注释 --空格+内容

创建数据库

CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
eg:CREATE DATABASE IF NOT EXISTS school CHARSET utf8
如果对应school不存在,则创建新的数据库且编码使用utf8

删除数据库

DROP DATABASE 数据库名 / [IF EXISTS数据库名]
DROP DATABASE IF EXISTS school

修改字符集

ALTER DATABASE 数据库名 CHARSET 字符集名

创建表

表:是数据存储的最常见和最简单的形式,是构成关系型数据库的基本元素
每一个表都有表头和表尾表头定义表明和列名表中的被看作是文件中的记录，表中的列被看作是这些记录的字段。

设计 表
- 表名
- 表中的字段
- 字段的数据类型和长度
- 哪些约束
确定表名

数据类型

char(n) 长度为n的定长字符串,最大长度为255个字符

varchar(n) 最大长度为n的可变字符串

date 日期包含年月日

datetime 年月日十分秒

整数

类型	字节	最小值	最大值
`TINYINT`	1	-128	-127
`SMALLINT`	2	-32768	-32767
`MEDIUMINT`	3	-888608	-888607
`INT`	4	-2147483648	-2147483647
`BIGINT`	8	-9883372036854775808	-9883372036854775807

signed 有符号 unsigned 无符号 默认是有符号

浮点

语法

创建表

CREATE TABLE table_name(列名 数据类型 [约束] [默认值] [ 注释]*n)

CREATE TABLE 表名(

	number INT,
	NAME VARCHAR(5),
	gender CHAR(1),
	birthday DATE,
	phone VARCHAR(11),
	height DECIMAL(3,2),
	reg_time DATETIME
)

-- CHAR(n) 定长的字符串  固定存储n个字符,长度不够n空格补齐
-- VAECHAR(n) 可变长度的字符串 n为最大长度 如果只占了小于n的m个字符 实际占有m个空间位置

删除表

DROP TABLE [if exists ] table_name

修改表

RENAME TABLE old_table_name TO new_table_name

复制表结构

CREATE TABLE new_table_name LIKE 被复制表名

CREATE TABLE student LIKE stu;//把student的结构复制给stu;

主键:在一张表中代表唯一的一条记录,不能为空,不能重复

约束

PRIMARY KEY 设置主键约束,添加主键约束后,值不能重复,一个表中只能有一个列是主键
主键列可以唯一的表示某一行数据
mysql中主键可以设置为自动增长 AUTO_INCREMENT
NOT NULL不能为空约束
UNIQUE 唯一性约束不能重复
检查约束设置条件
注释 COMMENT + 注释

用法

CREATE TABLE student(
	number INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号主键',
	NAME VARCHAR(6) NOT NULL,
	birthday DATE,
	phone VARCHAR(11) NOT NULL UNIQUE,
	heigth DECIMAL(3,2) CHECK(heigth<3),
	reg_time DATETIME
)

执行某一个询问必须选中

`DML` 数据操作增删改

插入数据

方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
方式2: INSERT INTO 表名 set 列名1=值1,..列名;
方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);
方式4: INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配);

修改数据

UPDATE 表名 SET列名 = '新值' WHERE 条件

删除数据

DELETE FROM 表名 WHERE 条件
TRUNCATE TABLE 表名;//清空整张表

增加列

ALTER TABLE [表名] ADD [列名] [数据类型];

`DQL` 查询语句

查询指定的列

**语法 : ** SELECT [查询列表] FROM 表名
结果可以是:表格中的字段,常量,表达式,函数
查询的结果是虚拟表格,不可以操作是只读的
可以对查询结果进行算术运算(+ - * /);
**特点: **
- 查询的列表可以是:表中的字段,常量,表达式,函数
- 查询的结果是一个虚拟的表格

查询结果处理

特定列查询 select column 1, column2 form table(表名)

全部列的查询select * from TABLE (表名)

-- 使用该表来进行举例子
CREATE DATABASE IF NOT EXISTS student  CHARSET utf8

CREATE TABLE stu(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 
number INT(5) NOT NULL, 
stuname VARCHAR(10) NOT NULL, 
age INT(3) CHECK(age>18), 
birthday DATE, 
weight DOUBLE, 
opertime DATETIME
)

INSERT INTO stu (number ,stuname,birthday)VALUES(456,'cwy','2023-01-01');

算术运算符: + - * /

排除重复行:select distinct column1,column2 from table

-- 查询指定的列  * 表示查询所有列
SELECT id,number FROM stu
SELECT * FROM stu
-- 查询结果进行算术运算
SELECT id+1, number/2 FROM stu
-- 查询去重  对查询结果进行去重
SELECT DISTINCT stuname FROM stu

查询的时候可以使用函数 select也是一个函数

函数:类似于java中方法,将一组逻辑语句事先在数据库可以中定义好,可以直接调用

分类:

单行函数:如concat,length,ifull等;
分组函数:做统计使用,又称为统计函数,聚合函数,组合函数

单行函数

字符函数

length()函数:获取参数值的字节个数

SELECT stuname,LENGTH(stuname) FROM stu

char_length(列名)获取变量字符串的长度

SELECT stuname,LENGTH(stuname) FROM stu
-- 与length()用法一样

concat(str1,str2,.....)拼接字符串 str 指的是为字符串的列如果其中str含NULL结果都为NULL

instr(str ,指定字符)查找指定字符在str中首次出现的位置

ELECT INSTR(stuname,'w')  FROM stu

trim(str) 默认去除str前后的空格,str为指定的子串且来自父类字符串

lpad(str,length,字符),与 rpad(str,length,字符) 在字符左右填充length长的字符 注意length指的是字节的长度

-- lpad(str,length,字符)  在str左边填充length长度的 字符
SELECT LPAD(stuname,6,'w')  FROM stu
-- rpad 同上 在右边填充

replace(sre, 目标字符,替换字符)

-- replace(str,目标字符,替换字符) 把str中的目标字符  替换为 替换字符
SELECT REPLACE(stuname,'d','w')  FROM stu

upper(str)转大写 lower(str)转小写 str可以是字符串的列也可也是字符串但如果是字符串他会单独显示一列为str大写

-- upper()转大写  lower() 转小写 
SELECT UPPER(stuname) FROM stu -- 为字符串的列

-- upper()转大写  lower() 转小写
SELECT UPPER('dqw') FROM stu

逻辑处理

case when 条件 then 结果1 else 结果2 end;(可以有多个when)

-- 表示id为123时 为1 否则都为2
SELECT CASE WHEN   id=123 THEN 1 ELSE 2 END FROM stu;

is null SELECT CASE WHEN age IS NULL THEN 1 ELSE 2 END FROM stu; 判断age是否为null

ifnull(被检测值,默认值)函数检测是否为null如果为null返回指定值,否则返回原本值

SELECT IFNULL(age,0) FROM stu;-- 判断age是否为空 不是返回原本值 否则返回 0

if(条件,结果1,结果2) if- else 的效果

SELECT IF(age>50,0,1) FROM stu;

日期函数

now();返回当前日期

-- 获取当前日期 年月日 时分秒
SELECT NOW() FROM stu;

curdate()获取当前日期不包含时分秒 curtime() 获取当前时分秒不包含日期

可以获取指定部分 年 ,月, 日, 小时 , 分钟,秒

TEAR(日期列) MONTH(日期列) DAY(日期列) HOUR(日期列) MINUTE(日期列) SECOND(日期列)

str_to_date(字符串格式的日期,格式)将日期格式的字符转化为指定格式的日期

date_format(日期列,格式) 将日期转化为字符串

datediff(big ,small)返回两个日期相差的天数

日期格式

分组函数

功能: 用作统计使用,又称为聚合函数或统计函数或组函数

分类:sum求和 avg平均值 max最大值 min最小值 count计数(非空)

sum,avg一般用于处理数值型max min count可以处理任何类型
以上分组函数都可以忽略null值
count函数的一般使用 count(*)用作统计行数.
和分组函数一同查询的字段要求是 group by后的字段 group by必须在语句最后

- 求和函数
SELECT SUM(id) FROM stu;
-- 求平均
SELECT AVG(id) FROM stu;
-- sum avg 只可以统计 数值类型  

-- 求最大值
SELECT MAX(weight) FROM stu
-- 求最小值
SELECT MIN(weight) FROM stu
-- 统计函数
SELECT COUNT(weight>3) FROM stu;
-- 分组函数
-- group by 分组函数 后面对应的变量 要与前面出现的变量一样()
SELECT YEAR(birthday), COUNT(YEAR(birthday)) FROM stu GROUP BY YEAR(birthday);

分组函数

条件查询

where(条件) where子句紧跟from子句

语法select <结果> from<表名>where<条件>

SELECT stuname FROM stu WHERE stuname LIKE '%w%';
-- 模糊匹配  stuname 含w数据

条件中的比较

= ,!= 或<>,>,<,>=,<=
逻辑运算
- and 与
- or 或
- not 非

条件查询中模糊查询

LIKE: 是否匹配于一个模式,一般和通配符搭配使用,可以判断字符型数值或数值型

通配符: % 任意多个字符

条件查询 'w%'表示以w开头的模糊匹配 '%w'表示以w结尾的模糊匹配 '%w%'表示包含w的模糊匹配

SELECT stuname FROM stu WHERE stuname LIKE '%w%';
-- 模糊匹配  stuname 含w数据
-- 结果如上

between and 两者之间,包含临界值

In 判断某字段的值是否输入in列表中的某一项

IS NULL(为空的) 或 IS NOT NULL(不为空的)

联合语句 `UNION` `UNION ALL`

`UNION`

[SQL 语句 1]

UNION

[SQL 语句 2]

`UNION ALL`

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

注:当使用union时 MySQL会把结果集中重复的记录删除掉,而是用UNION ALL ,MySQL会把所有记录返回,其效率高于union

排序

如果想对查询的结果进行排序使用ORDER BY 子句排序

ORDER BY [ASC/DESC]

asc 代表的是升序 ,desc代表的是降序,如果不写,默认为升序

ORDER BY可以支持单个字段与多个字段

-- 排序 order by asc 是升序 desc是降序 
SELECT stuname ,weight FROM stu ORDER BY weight ASC

数量限制

limit子句:对查询显示结果限制数目(sql语句最末尾位置)

SELECT * FROM table LIMIT offset rows

SELECT * FROM table LIMIT 0,5

分组查询

语法:

SELECT [分组函数] ,[列](要求出现在group by的后面)

from [表]

[where 筛选条件]

group by 分组的列表 //分组

[having 分组后筛选] //

[order by 子句] //排序

注意:查询列表比较特特殊,要求是分组函数和group by后出现的字段
数据源源位置关键字

分组前的筛选原始表 group by子句前面 where

分组后筛选分组后的激活集 group by 的后面 having

	数据源	源位置	关键字
分组前的筛选	原始表	group by子句前面	where
分组后筛选	分组后的激活集	group by 的后面	having

一对多问题

函数:group_concat([列名])

数据库的设计范式

设计3范式

第一范式
- 第一范式是最基本的范式,如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库满足了第一范式
- eg:联系方式---> 邮箱,手机号 ,QQ可以分解
第二范式
- 第二范式要有主键,如果没有主键就没有唯一性,就无法找到记录(但有些表可能没有主键)
第三范式
- 确保每列都和主键列直接相关,而不是间接相关,要求一个数据库中不包含已在其他表中包含的非关键字信息
- 即在一张表关联其他多张表数据时,只需要关联主键即可

添加约束

CREATE TABLE major(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)

)

DROP TABLE stu
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	gender CHAR(1),
	phone VARCHAR(12),
	birthday DATE

)
-- 增加列(对stu)
ALTER TABLE stu ADD majorid INT;

INSERT INTO major(NAME)VALUES ("计算机");
INSERT INTO major(NAME)VALUES ("网络");
INSERT INTO major(NAME)VALUES ("数学");
INSERT INTO major(NAME)VALUES ("人工智能");

INSERT INTO stu(NAME,gender,phone,birthday,majorid)VALUES("张三","男","15691342128","1999-01-01",1);
INSERT INTO stu(NAME,gender,phone,birthday,majorid)VALUES("李四","女","15691222128","1998-01-31",1);
INSERT INTO stu(NAME,gender,phone,birthday,majorid)VALUES("王麻子","男","15691234456","2000-01-01",2);
INSERT INTO stu(NAME,gender,phone,birthday,majorid)VALUES("张六六","男","17999342128","1997-11-01",3);
INSERT INTO stu(NAME,gender,phone,birthday,majorid)VALUES("赵果果","男","15691347778","1999-01-01",4);
ALTER TABLE stu ADD CONSTRAINT fk_stu_major_majorid FOREIGN KEY (majorid) REFERENCES major(id);

屏幕截图 2023-12-22 172346

与下表关联

主键: 不能为空 唯一 检查
外键 : 在一个表中的外键是用来与另一个表的主键关联的

外键有两种情况

不加外键约束

可以任意的对表数据进行操作,即使两个表中的数据对应不上也没有问题
添加约束

表已经创建后的约束

alter table [表名1] add constraint fk_[表名1]_[表名2]_[对应列] foreign key ([对应列]) reference [表名2(表名2的主键)]

约束名规则:FK_ForeignTable_PrimaryTable_On_ForeignColumn
```
ALTER TABLE stu ADD CONSTRAINT fk_stu_major_majorid FOREIGN KEY (majorid) REFERENCES major(id);
```
创建表的时候添加约束
```
create table student(
id int not null auto_increment primary key,
num int,
name varchar(10)
majorid int,
-- 约束
CONSTRAINT 约束名 foreign key(majorid ) references major(id)
)
```
添加约束后,不能更改主表的值使另一个表的主键孤立,也不可以添加另一个不存在的主键

创建学生课程与专业的关系,建立课程与专业的关系

CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
)

-- 添加一个学生选课表  学生和课程关系表
-- 课程信息表  多对多关系
CREATE TABLE student_course(
	number INT,
	courseid INT
	
)
-- 建立 stu 与课程 course的关系
ALTER TABLE student_course ADD CONSTRAINT fk_stu_course_number FOREIGN KEY (number) REFERENCES stu(id);
-- 建立 课程 与专业的关系
ALTER TABLE student_course ADD CONSTRAINT fk_stu_major_courseid FOREIGN KEY (courseid) REFERENCES course(id);

course:

major:

多表关联查询

关联查询,多表关联到一起查询

eg:

-- 关联查询  多表关联一起查询
-- 学号 姓名 性别 电话 专业名称  -----信息来源于两行表中
SELECT * FROM stu,major

.....

但是你会发现每个名字出现多次,这是因为关联时,没有任何限制,会产生笛卡尔乘积现象,这是我们不想要的效果

笛卡尔现象表一 m行表二 n行查询结果 = m*n行
发生原因:没有有效的连接条件
解决办法:添加有效的连接条件
连接按照功能分类:
- 内连接
- 外连接
  - 左外连接
  - 右外练级

子查询

DQL基础查询中子查询的语法规则如下：

从子查询(表子查询)

SELECT column1,column2,... 
FROM 
(SELECT column1,column2,... FROM table_name...) AS alias_name`

例如：

SELECT name,age FROM (SELECT name,age FROM users) AS u

WHERE子查询(标量子查询和列子查询)

(1) 标量子查询

语法：
SELECT column1,column2,...
FROM [table_name]
WHERE column1 = (SELECT column FROM table_name WHERE...)

例如：

SELECT name,age FROM users WHERE id = (SELECT id FROM users WHERE name='John')

(2) 列子查询

语法：
SELECT column1,column2,... 
FROM [table_name] 
WHERE column1 IN (SELECT column FROM table_name WHERE...)`

例如：

SELECT name,age FROM users WHERE id IN (SELECT id FROM orders WHERE status='completed')

HAVING子查询

与WHERE子查询类似，但放在HAVING后用于过滤分组后的结果。

语法：
SELECT column1, aggregate_function(column2) 
FROM [table_name]
GROUP BY column1 
HAVING aggregate_function(column2)
operator (SELECT column FROM table_name WHERE...)`

以上就是DQL中子查询的基本语法规则。

posted @ 2024-05-25 15:21 Yang0710 阅读(13) 评论(0) 编辑收藏举报

刷新页面返回顶部

cwyYYDS

MySqlNote DDL DML DQL操作

`MySQL` 数据库

关系型数据库

`sql`语言(`Structured Query Language`)

`sql`安装使用流程

`DDL` 数据(结构)定义创建表

创建删除数据

创建数据库

删除数据库

修改字符集

创建表

数据类型

语法

`DML` 数据操作增删改

插入数据

修改数据

删除数据

增加列

`DQL` 查询语句

查询指定的列

查询结果处理

单行函数

逻辑处理

日期函数

分组函数

条件查询

联合语句 `UNION` `UNION ALL`

数量限制

数据库的设计范式

多表关联查询

子查询

公告

cwyYYDS

MySqlNote DDL DML DQL操作

MySQL 数据库

关系型数据库

sql语言(Structured Query Language)

sql安装使用流程

DDL 数据(结构)定义 创建表

创建 删除数据

创建数据库

删除数据库

修改字符集

创建表

数据类型

语法

DML 数据操作 增删改

插入数据

修改数据

删除数据

增加列

DQL 查询语句

查询指定的列

查询结果处理

单行函数

逻辑处理

日期函数

分组函数

条件查询

联合语句 UNION UNION ALL

数量限制

数据库的设计范式

多表关联查询

子查询

公告

`MySQL` 数据库

`sql`语言(`Structured Query Language`)

`sql`安装使用流程

`DDL` 数据(结构)定义创建表

创建删除数据

`DML` 数据操作增删改

`DQL` 查询语句

联合语句 `UNION` `UNION ALL`