MySQL8.0入门系列(一)

已于 2023-01-23 14:40:10 修改
阅读量1.9k 收藏 34
点赞数 11
分类专栏： # MySQL灬蛮魂 文章标签： mysql
于 2021-06-13 16:01:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Augenstern_QXL/article/details/117879126
版权

✍、目录

目录

名称解释命令
DDL(数据定义语言)定义和管理数据对象，如数据库，数据表等create、drop、alter
DML(数据操作语言)用于操作数据库对象中所包含的数据insert、update、delete
DQL(数据查询语言)用于查询数据库数据select
DCL(数据控制语言)用于管理数据库的语言，包括管理权限及数据更改grant、commit、rollback

1、DDL

DDL：Data Definition Language，数据定义语言，定义和管理数据对象，如数据库，数据表等

1.1、对数据库的常用操作

功能SQL
查看所有的数据库show databases;
创建数据库create database [if not exists] 数据库名;
使用数据库use 数据库
删除数据库drop database [if exists] 数据库名;
修改数据库编码alter database 数据库名 character set utf8

使用SQLyog创建数据库时，需要选择

  • 基字符集：utf8
  • 数据库排序规则：utf8_unicode_ci

在这里插入图片描述

1.2、对表结构的常用操作

1.2.1、创建表

创建表是构建一张空表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型。

中括号[]内的内容可写可不写，尖括号<>内的必须写

create table [if not exists] 表名(
	
	字段名1 类型[(宽度)] [约束条件] [comment '字段说明']
	
)[表的一些设置];

例如：

  • 创建一个school数据库
  • 创建学生表
    • 学号int
    • 姓名varchar
    • 密码varchar
    • 性别varchar
    • 出生日期datetime
    • 家庭住址varchar
    • 邮箱varchar
-- 创建数据库 school
CREATE DATABASE IF NOT EXISTS `school`;
-- 删除数据库 student
DROP DATABASE IF  EXISTS `student`;
-- 注意点:表的名称和字段尽量使用 `` 括起来
-- 创建学生表
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '主键',  		-- id 不为空，自增
  `name` VARCHAR(30) NOT NULL DEFAULT '林晓' COMMENT '姓名',	-- name 不为空，默认为林晓
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',	-- pwd 不为空，默认为123456
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',	-- sex 不为空，默认为男
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',		-- birthdat 默认为空
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',	-- address 默认为空
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',		-- email默认为空
    
  PRIMARY KEY (`id`)  									 -- 主键，一般一个表只有一个唯一的主键
)ENGINE=INNODB DEFAULT CHARSET=utf8;

1.2.2、数据类型

数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数据类型的原则是:够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。

1、数值类型
类型用途存储需求
tinyint小整数值1 byte
smallint大整数值2 bytes
mediumint大整数值3 bytes
int / integer大整数值4 bytes
bigint极大整数值8 bytes
float单精度浮点数值4 bytes
double双精度浮点数值8 bytes
decimal小数值金融计算的时候，一般是使用decimal

其中的decimal有以下用法

decimal（5，2）
  • 其中 5 为最多可以存储的十进制位数为10位，是小数点前后的位数总和
  • 2表示小数点后面的位数
2、字符串类型
类型用途大小
char定长字符串0-255 bytes
varchar变长字符串0-65535 bytes
tinyblob不超过255个字符的二进制字符串0-255 bytes
tinytext短文本字符串0-255 bytes
blob二进制形式的长文本数据0-65535 bytes
text长文本数据0-65535 bytes
mediumblob二进制形式的中等长度文本数据
mediumtext中等长度文本数据
longblob二进制形式的极大文本数据
3、日期类型
类型格式用途
dateYYYY-MM-DD日期值
timeHH:MM:SS时间值或持续时间
yearYYYY年份值
datetimeYYYY-MM-DD HH:MM:SS混合日期和时间值
timestampYYYYMMDD HHMMSS混合日期和时间值，时间戳

1.2.3、其他操作

功能SQL
查看当前数据库的所有表名称show tables;
查看指定某个表的创建语句show create table 表名;
查看表结构desc 表名
删除表drop table 表名

1.3、修改表结构

1.3.1、添加列

语法：

alter table 表名 add 列名 类型(长度)[约束]

例如：为 student 表添加一个新的字段为：系别 dept 类型为 varchar(20)

alter table student add `dept` varchar(20)

1.3.2、修改列名和类型

语法：

alter table 表名 change 旧列名 新列名 类型(长度) 约束;

例如：为 student 表的 dept 字段更换为 department varchar(30)

alter table student change `dept` department varchar(30)

1.3.3、删除列

语法：

alter table 表名 drop 列名;

例子： 删除 student 表中的 department 列

alter table student drop department;

1.3.4、修改表名

语法：

rename table 表名 to 新表名

例子： 将表 student 改名为 stu

rename table student to stu

2、DML

DML: Data Manipulation Language,数据操作语言，用来对数据库中表的数据记录进行更新。

2.1、数据插入

语法：

-- 向表中插入某些列
insert into 表(列名1,列名2,列名3...) values ('值1','值2','值3'...)

-- 向表中插入所有列
insert into 表 values(值1,值2,值3....)
  • 一般写插入语句，我们一定要数据和字段一一对应！
  • 字段是可以省略的，但是后面的值必须要一一对应

例子：

insert into student(sid,name,gender,age,birth,address,score) values(1001,'男',18,'2001-08-09','杭州',90)

insert into student values(1001,'男',18,'2001-08-09','杭州',90)

2.2、数据修改

语法：

update 表名 set 字段名=值,字段名=值...;

update 表名 set 字段名=值,字段名=值....where 条件;
  1. 条件，筛选的条件，如果没有指定，则会修改所有的列
  2. value ，是一个具体的值，也可以是一个变量
  3. 多个设置的属性之间，使用英文逗号隔开

例子：

-- 将所有学生的地址改为成都
update student set address = '成都'

-- 将 id 为 1004 的学生的地址改为北京
update student set address = '北京' where id = 1004

-- 将 id 为 1005 的学生的地址修改为北京,成绩修改为100
update student set address = '北京',score = 100 where id = 1005

update `student` set `name`='狂神' where id = 1;

-- 不指定条件的情况下，会改动所有表！
update `student` set `name`='长江7号'; 

-- 修改多个属性
update `student` set `name`='狂神',`email`='12123@qq.com' where id between 2 and 5;

-- 通过多个条件定位数据 
update `student` set `name`='长江7号' where `name`='狂神' and `sex`='男';
操作符含义范围结果
=等于5=6false
<>或 !=不等于5<>6true
>大于
<小于
<=小于等于
>=大于等于
between…and…在某个范围内[2,5]
and&&5>1 and 1>2false
or||5>1or 1>2true

2.3、数据删除

语法：

delete from 表名 [where 条件]

truncate table 表名
-- 或者
truncate 表名

例如：

-- 删除sid为1004的学生数据
delete from student where sid = 1004;

-- 删除表中所有数据
delete from student;

-- 清空表数据
truncate table student;
truncate student;

注意：delete 和 truncate 原理不同,delete只是删除内容,而 truncate 类似于 drop table,可以理解为事将整个表删除,然后再创建该表

delete 和 truncate 区别

  • 相同点: 都能删除数据，都不会删除表结构，但truncate速度更快
  • 不同点：
    • truncate 重新设置 自增列，计数器会归零
    • truncate 不会影响事务

3、约束

约束constraint实际上就是表中数据的限制条件。

作用：表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

约束的分类

  • 主键约束 primary key
  • 自增长约束 auto_increment
  • 非空约束 not null
  • 唯一性约束 unique
  • 默认约束 default
  • 零填充约束 zerofill
  • 外键约束 foreign key

3.1、主键约束

  • MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。

  • 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

  • 每个表最多只允许一个主键

  • 主键的关键字是：primary key

  • 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

3.1.1、添加单列主键

创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键

方式一语法：

-- 在 create table 语句中,通过 PRIMARY KEY 关键字来指定逐渐
create table 表名(

	<字段名> <数据类型> primary key
)

方式二实现：

create table empl(
	
    `id` int primary key,
    `name` varchar(20),
)

方式二语法：

-- 在定义字段之后再指定主键,语法格式如下
create table 表名(

	[constraint<约束名>] primary key [字段名]
)

方式二实现：

create table emp2(
	
    id int,
    name varchar(20),
    
    constraint pk1 primary key(id)
)

3.1.2、添加多列主键(联合主键)

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

注意：

  1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束
  2. 一张表只能有一个主键，联合主键也是一个主键

语法：

create table 表名(

	primary key(字段1,字段2....)

)

实现：

create table emp3(

	name varchar(20),
    id int,
    
    primary key(name,id)
)

3.1.3、通过修改表结构添加主键

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。

语法：

create table 表名(

);

alter table <表名> add primary key(字段列表);

实现：

-- 添加单列主键
create table emp4(
	
    id int,
    name varchar(20)

);

alter table emp4 add primary key(id);

3.1.4、删除主键约束

一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

语法：

alter table <数据表名> drop primary key;

实现：

-- 删除单列主键
alter table emp1 drop primary key;

-- 删除联合主键
alter table emp5 drop primary key;

3.2、自增长约束

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 auto_increment 属性来实现主键自增长。

语法：

字段名 数据类型 auto_increment

实现：

create table student(
	
    id int primary key auto_increment,
    name varchar(20)
);

特点：

  • 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
  • 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
  • auto_increment约束的字段必须具备 NOT NULL 属性。
  • auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等)。
  • auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

3.2.1、指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

方式一：创建表时指定

create table student(

    id int primary key auto_increment,
    name varchar(20)
)auto_increment=100;

方式二：指定自增字段初始值

create table student(

    id int primary key auto_increment,
    name varchar(20)
);

alter table student auto_increment=100;

delete和truncate在删除后自增列的变化

  • delete 数据之后自动增长从断点开始
  • truncate 数据之后自动增长从默认起始值开始

3.3、非空约束

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

语法：

-- 方式一🔥
<字段名><数据类型> not null;

-- 方式二
alter table 表名 modify 字段 类型 not null;

实现：

-- 方式一🔥
create table student(
	
    id int,
    name varchar(20) not null,
    address varchar(20) not null
);

-- 方式二
create table student(
	
    id int,
    name varchar(20),
    address varchar(20)
);

alter table student modify name varchar(20) not null;
alter table student modify address varchar(20) not null;

3.3.1、删除非空约束

语法：

alter table 表名 modify 字段 类型

实现：

alter table student modify name varchar(20);

alter table student modify address varchar(20);

3.4、唯一约束

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

语法：

-- 方式一
<字段名><数据类型> unique

-- 方式二
alter table 表名 add constraint 约束名 unique(列);

实现：

-- 方式一 创建表时指定

create table student(
	
    id int,
    name varchar(20),
    address varchar(20) unique -- 指定唯一约束
);


-- 方式二
create table student(
	
    id int,
    name varchar(20),
    address varchar(20)
);

alter table student add constraint unique_ad unique(address);

3.4.1、删除唯一约束

语法：

-- alter table <表名> drop index <唯一约束名>
alter table student drop index unique_ad;

3.5、默认约束

MySQL 默认值约束用来指定某列的默认值。

语法：

-- 方式一
<字段名><数据类型> default <默认值>

-- 方式二
alter table 表名 modify 列名 类型 default 默认值;

实现：

-- 方式一
create table student(
	
    id int,
    name varchar(20) default '小林' -- 指定默认约束

)；


-- 方式二
create table student(
	
    id int,
    name varchar(20) 
)；

alter table student modify name varchar(20) default '小林'

3.5.1、删除默认约束

语法：

alter table <表名> modify column <字段名><类型> default null;

实现：

alter table student modify column name varchar(20) default null;

3.6、零填充约束

  1. 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
  2. zerofill默认为int(10)
  3. 当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128 ~ +127，无符号为0~256。

实现：

create table student(

	id int zerofill,   -- 零填充约束
    name varchar(20)
);

3.6.1、删除零填充约束

语法：

alter table <表名> modify <字段><数据类型>

实现：

alter table <表名> modify <id><int>

4、DQL

4.1、基本查询

语法格式：

select [all|distinct]
	<目标列的表达式1> [别名],
	<目标列的表达式2> [别名]
from <表名或视图名> [别名],<表名或视图名> [别名]
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>]
  1. 创建数据库和表
-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb2;
USE mydb2;
-- 创建商品表：
CREATE TABLE product(
 pid INT PRIMARY KEY AUTO_INCREMENT, -- 商品编号
 pname VARCHAR(20) NOT NULL , -- 商品名字
 price DOUBLE,  -- 商品价格
 category_id VARCHAR(20) -- 商品所属分类
);
INSERT INTO product VALUES(NULL,'海尔洗衣机',5000,'c001');
INSERT INTO product VALUES(NULL,'美的冰箱',3000,'c001');
INSERT INTO product VALUES(NULL,'格力空调',5000,'c001');
INSERT INTO product VALUES(NULL,'九阳电饭煲',200,'c001');
                           
INSERT INTO product VALUES(NULL,'啄木鸟衬衣',300,'c002');
INSERT INTO product VALUES(NULL,'恒源祥西裤',800,'c002');
INSERT INTO product VALUES(NULL,'花花公子夹克',440,'c002');
INSERT INTO product VALUES(NULL,'劲霸休闲裤',266,'c002');
INSERT INTO product VALUES(NULL,'海澜之家卫衣',180,'c002');
INSERT INTO product VALUES(NULL,'杰克琼斯运动裤',430,'c002');
 
INSERT INTO product VALUES(NULL,'兰蔻面霜',300,'c003');
INSERT INTO product VALUES(NULL,'雅诗兰黛精华水',200,'c003');
INSERT INTO product VALUES(NULL,'香奈儿香水',350,'c003');
INSERT INTO product VALUES(NULL,'SK-II神仙水',350,'c003');
INSERT INTO product VALUES(NULL,'资生堂粉底液',180,'c003');
 
INSERT INTO product VALUES(NULL,'老北京方便面',56,'c004');
INSERT INTO product VALUES(NULL,'良品铺子海带丝',17,'c004');
INSERT INTO product VALUES(NULL,'三只松鼠坚果',88,NULL);

在这里插入图片描述

  1. 查询所有的商品
select * from product;
  1. 查询商品名和商品价格
select pname,price from product;

  1. 别名查询

    1. 表别名

      select * from product as p;

    2. 列别名

      select pname as pn from product;

  2. 去掉重复值

select distinct price from product;

在这里插入图片描述

  1. 查询结果是表达式(运算查询)：将所有商品的价格+10元进行显示
select pname,price+10 from product;

在这里插入图片描述

4.2、运算符

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

例如，学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

MySQL支持4种运算符：

  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符

4.2.1、算术运算符

算术运算符说明
+加法运算
-减法运算
*乘法运算
/ 或者 DIV除法运算，返回商
% 或者 MOD求余运算，返回余数
select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;


-- 将每件商品的价格加10
select name,price+10 as new_price from product;

4.2.2、比较运算符

比较运算符说明
=等于
< 和 <=小于和小于等于
> 和 >=大于和大于等于
<=>安全的等于，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
<> 或 !=不等于
isnull 或者 is null判断一个值是否为null
is not null判断一个值是否不为null
least当有两个或多个参数时，返回最小值
greatest当有两个或多个参数时，返回最大值
between and判断一个值是否落在两个值之间
in判断一个值是in列表中的任意一个值
not in判断一个值不是in列表中的任意一个值
like通配符匹配
regexp正则表达式匹配
-- 查询商品名称为“海尔洗衣机”的商品所有信息：
select * from product where pname = '海尔洗衣机';
 
-- 查询价格为800商品
select * from product where price = 800;
 
-- 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not(price = 800);
 
-- 查询商品价格大于60元的所有商品信息
select * from product where price > 60;
 
 
-- 查询商品价格在200到1000之间所有商品
select * from product where price >= 200 and price <=1000;
select * from product where price between 200 and 1000;

4.2.3、逻辑运算符

逻辑运算符说明
not 或者 ！逻辑非
and 或者 &&逻辑与
or 或者 ||逻辑或
likea like b，SQL匹配，如果a匹配b，则结果为真
ina in(a1,a2,a3…)，假设a在a1或者a2…，则结果为真
-- 查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200,800);

-- 查询含有'裤'字的所有产品
select * from product where pname like '%裤%';

-- 查询以'海'开头的所有商品
select * from product where pname like '海%';

-- 查询第二个字为'寇'的所有商品
select * from product where pname like '_寇%';

-- 查询category_id为null的商品
select * from product where category_id is null;

-- 查询category_id不为null分类的商品
select * from product where category_id is not null;

-- 使用least求最小值
select least(10,20,30); -- 10
select least(10,null,30); -- null

-- 使用greatest求最大值
select greatest(10,20,30); -- 30
select greatest(10,null,30); -- null
  • like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
  • 注意： % _只能在like里面使用
  • in 里面是确切的集合

4.2.4、位运算符

位运算符说明
|按位或
&按位与
^按位异或
<<按位左移
>>按位右移
~按位取反，反转所有比特

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

select 3&5; -- 位与 
select 3|5; -- 位或 
select 3^5; -- 位异或 
select 3>>1; -- 位左移
select 3<<1; -- 位右移
select ~3;   -- 位取反

4.3、排序查询

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

语法：

select
	字段名1,字段名2
from 表名
order by 字段名1 [asc|desc],字段名2[asc|desc]

特点：

  • asc 代表升序，desc 代表降序，如果不写默认升序

  • order by 用于子句中可以支持单个字段，多个字段，表达式，函数，别名

  • order by 子句，放在查询语句的最后面。LIMIT子句除外

-- 使用价格排序(降序)
select * from product order by price desc;

-- 在价格排序(降序)的基础上,以分类排序(降序)
select * from product order by price desc,category_id asc;

-- 显示商品的价格(去重复),并排序(降序)
select distinct price from product order by price desc;

4.4、聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

聚合函数作用
count()统计指定列不为null的记录行数
sum()计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
min()计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
avg()计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
-- 查询商品的总条数：统计product表有多少行
select count(*) from product;

在这里插入图片描述

-- 查询价格大于200商品的总条数
select count(*) from product where price > 200;

在这里插入图片描述

-- 查询分类为'c001'的所有商品的综合
select sum(price) from product where category_id = 'c001';

在这里插入图片描述

-- 查询商品的最小价格
select min(price) from product;

-- 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';

4.4.1、null值处理

  1. count 函数对null值的处理

    如果 count 函数的参数为星号 * ,则统计所有记录的个数。而如果参数为某字段，则不统计含 null 值的记录个数

  2. sum 和 avg 函数对 null 值的处理

    这两个函数忽略 null 值的存在，就好像该条记录不存在一样

  3. max 和 min 函数对null值的处理

    max 和 min 两个函数同样忽略null值的存在。

我们可以进行测试：

-- 创建表
CREATE TABLE test_null( 
 c1 VARCHAR(20), 
 c2 INT 
);

-- 插入数据
INSERT INTO test_null VALUES('aaa',3);
INSERT INTO test_null VALUES('bbb',3);
INSERT INTO test_null VALUES('ccc',NULL);
INSERT INTO test_null VALUES('ddd',6);

-- 测试
SELECT COUNT(*), COUNT(1), COUNT(c2) FROM test_null;

在这里插入图片描述

  • count(*) 统计所有记录的个数为4
  • count(1) 统计所有记录的个数为4
  • count(c2) 统计不统计含 null 值的记录个数为3
-- 测试
SELECT SUM(c2),MAX(c2),MIN(c2),AVG(c2) FROM test_null;

在这里插入图片描述

  • sum(c2) 忽略 null值
  • max(c2) 忽略 null值
  • min(c2) 忽略 null值
  • avg(c2) 忽略 null值

4.5、分组查询

分组查询是指使用group by字句对查询信息进行分组。

语法：

select 
	字段1,字段2 
from 表名 
group by 分组字段 
having 分组条件；

实现：

-- 统计各个分类商品的个数
select category_id,count(*) from product group by category_id;

在这里插入图片描述

如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现：

4.5.1、having

分组之后的条件筛选-having

  • 分组之后对统计结果进行筛选的话必须使用having，不能使用where
  • where子句用来筛选 FROM 子句中指定的操作所产生的行
  • group by 子句用来分组 WHERE 子句的输出。
  • having 子句用来从分组的结果中筛选行
-- 统计各个分类商品的个数,且只显示个数大于4的信息
SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

在这里插入图片描述

4.6、分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

语法：

-- 方式一：显示前n条
select 字段1,字段2 from 表名 limit n

-- 方式二：分页显示
-- m:整数，表示从第几条索引开始,计算方式 (当前页-1)*每页显示条数
-- n:整数,表示查询多少条数据
select 字段1,字段2 from 表名 limit m,n

实现：

-- 查询product表的前5条记录
select * from product limit 5;

-- 从第4条开始显示,显示5条
select * from product limit 3,5;

在这里插入图片描述

4.7、insert into select

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。

语法：

insert into Table2(field1,field2) select value1,value2 from Table1

-- 或者
insert into Table2 select * from Table1

要求目标表 Table2 必须存在

4.8、select into from

将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT 。

格式：

select value1,value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。

5、DQL语句练习一

-- 创建数据库mydb1
create database if not exists mydb1;
-- 使用数据库mydb1
use mydb1;
-- 创建学生表
create table student(
	id int,
	name varchar(20),
	gender varchar(20),
	chinese int,
	english int,
	math int

);
-- 插入数据
insert into student values(1,'张明','男',89,78,90);
insert into student values(2,'李进','男',67,68,90);
insert into student values(3,'王五','女',81,72,91);
insert into student values(4,'李一','女',89,88,88);
insert into student values(5,'李财','女',81,78,90);
insert into student values(6,'张宝','男',80,70,90);
insert into student values(7,'黄蓉','男',70,80,90);
insert into student values(8,'大宝','女',83,73,93);
insert into student values(9,'二宝','男',84,75,96);
insert into student values(10,'三宝','男',84,74,94);

在这里插入图片描述

  1. 查询表中所有学生的信息
select * from student;
  1. 查询表中所有学生的姓名和对应的英语成绩
select name,english from student;
  1. 过滤表中重复数据
-- 若 distinct 后面跟着的列,表示这一列重复的会被去掉
-- 若 distinct 后面跟着的*,表示这一行所有字段都重复的会被去掉
select distinct * from student;
  1. 统计每个学生的总分
-- 正确写法
select name,chinese + english + math as total_score from student;

-- 错误写法(注意,sum() 表示对某一列进行求和)
select name,sum() from student;
  1. 在所有学生总分数上加10分特长分
select name,(chinese + english + math + 10) as total_score from student;
  1. 使用别名表示学生分数
-- 起别名 as 可以省略
select name,chinese '语文成绩',english '英语成绩', math '数学成绩' from student;
-- 写法二
select name,chinese as '语文成绩',english  as '英语成绩', math as '数学成绩' from student;
  1. 查询英语成绩大于90分的同学
select * from student where english > 90;
  1. 查询总分大于200分的所有同学
select * from student where (chinese + english + math) > 200;

-- 错误写法(先执行from,再执行 where,所以在执行 where 时别名不存在,这样写会报错)
select *, (chinese + english + math) as total_score from student where total_score > 200;
  1. 查询英语分数在 80 - 90 之间的同学
select * from student where english between 80 and 90;


select * from student where english ≥ 80 and english ≤ 90;
  1. 查询英语分数不在 80 - 90 之间
select * from student where not (english between 80 and 90);

select * from student where  english not between 80 and 90;

select * from student where not (english ≥ 80 and english ≤ 90);

select * from student where english ＜ 80 or english ＞ 90;
  1. 查询数学分数为 89，90，91的同学
select * from student where math in(80,90,91);
  1. 查询数学分数不为 89，90，91的同学
select * from student where math not in(80,90,91);

select * from student where not math in(80,90,91);
  1. 查询所有姓李的学生英语成绩
select name,english from student where name like '李%';
  1. 查询数学分80并且语文分80的同学
select * from student where math = 80 and chinese = 80;
  1. 查询英语80或者总分两百的同学
select * from student where english = 80 and (chinese + math + english) = 200;
  1. 对数学成绩降序排序后输出
select * from student order by math desc;
  1. 对总分排序后输出，并且按从高到低的顺序输出
select * from student order by (chinese + math + english) desc;
  1. 对姓李的学生总分成绩排序输出
select * from student where name like '李%' order by (chinese + math + english) desc;
  1. 查询男生和女生分别有多少人，并将人数降序排序输出
select 
  gender,count(*) as total_cnt 
from 
  student 
group by 
  gender 
order by 
  total_cnt 
desc;

6、DQL语句练习二

-- 创建员工表
create table emp(
 empno int,  -- 员工编号
 ename varchar(50), -- 员工名字
 job varchar(50), -- 工作名字
 mgr int, -- 上级领导编号
 hiredate date, -- 入职日期
 sal int, -- 薪资
 comm int, -- 奖金
 deptno int  -- 部门编号

);
-- 插入数据
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',1800,NULL,20);
insert into emp values(7469,'RED','AUGENSE',7692,'1981-12-17',2800,200,30);
insert into emp values(7569,'PINK','OPKUO',7839,'1982-12-17',3800,300,40);
insert into emp values(7669,'YELLOW','WEWQS',8921,'1983-12-17',4800,NULL,50);
insert into emp values(7769,'KING','QWERQ',7902,'1984-12-17',5800,500,60);
insert into emp values(7869,'HACK','ZXCMK',7902,'1985-12-17',6800,NULL,70);
insert into emp values(7969,'JACK','SDAWD',7902,'1986-12-17',7800,600,80);
insert into emp values(7169,'ADAMS','SAAWQ',7902,'1987-12-17',8800,NULL,90);
insert into emp values(7269,'FORD','CLERK',7902,'1988-12-17',9800,NULL,10);
insert into emp values(7844,'SCOTT','CLERK',7902,'1989-12-17',1800,NULL,20);
insert into emp values(7969,'SMITH','CLERK',7902,'1980-12-17',2800,900,20);
insert into emp values(7069,'SMITH','CLERK',7902,'1981-12-17',3800,NULL,20);

在这里插入图片描述

  1. 按员工编号升序排列不在10号部门工作的员工信息
select * from emp where deptno != 10 order by empno asc;
  1. 查询姓名的第二个字母不是A，且薪资大于1000的员工信息，并按年薪降序排列
-- 年薪 = 薪资 × 12 + 奖金 ,奖金里面有 null 值存在,所以不能直接加null
-- ifnull(comm,0)  此函数的作用:如果奖金comm为null,将其看作0来处理
select 
  * 
from 
  emp 
where 
  ename 
not like 
  '_A%' 
and 
  sal > 1000 
order by 
  (sal*12+ ifnull(comm,0)) desc;
  1. 求每个部门的平均薪水(每个部门，分组查询)
select deptno,avg(sal) from emp group by deptno;
  1. 求各个部门的最高薪水
select deptno,max(sal) from emp group by deptno;
  1. 求每个部门每个岗位的最高薪水
-- 两个分组,同一个岗位且属于同一个部门分组
select deptno,job,max(sal) from emp group by deptno,job;
  1. 求平均薪水大于2000的部门编号
-- 对分组之后的结果进行筛选用 having 
-- select deptno,avg(sal) avg_sal from emp group by deptno 这查出来就是分组之后的结果
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
  1. 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 1500 order by avg_sal desc;
  1. 选择公司中有奖金的员工姓名、薪资
select * from emp where comm is not null;
  1. 查询员工最高工资和最低工资的差距
select max(sal) - min(sal) as '薪资差距' from emp;

7、DQL正则表达式

正则表达式(regular expression)描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。

MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

模式描述
^匹配输入字符串的开始位置
$匹配输入字符串的结束位置
.匹配除 “\n” 之外的任何单个字符。
[…]字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]负值字符集合。匹配未包含的任意字符。例如，'[^abc]'可以匹配 “plain” 中的 ‘p’。
p1|p2|p3匹配p1或p2或p3。例如：‘z|food’ 能匹配 “z” 或 “food”。 ‘(z|f)ood’ 则匹配 “zood” 或者 “food”
*匹配前面的子表达式零次或多次。例如：zo* 能匹配 “z” 以及 “zoo” 。* 等价于{0,}
+匹配前面的子表达式一次或多次。例如：zo+ 能匹配 “zo” 以及 “zoo”,但是不能匹配 “z”。+等价于 {1，}
{n}n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次
-- ^ 在字符串开始处进行匹配
-- 含义: abc 是否以 a 开头
select 'abc' regexp '^a';  -- 结果为1,表示结果为真

-- 判断哪些商品的名字是以海开头
select * from product where pname regexp '^海';

在这里插入图片描述

-- $ 在字符串末尾开始匹配
-- 含义: abc 是否以 a 结尾
select 'abc' regexp 'a$';  -- 结果为0,表示否

-- 判断哪些商品的名字是以水结尾
select * from product where pname regexp '水$';

-- .匹配任意单个字符(除了换行符之外)
-- 含义: abc 中是否 b 前面有一个字符
select 'abc' regexp '.b';  -- 1

select 'abc' regexp 'a.';  -- 1

-- [...]匹配括号内的任意单个字符
-- 含义: xyz 中任意单个字符是否有在 abc 中出现呢
select 'abc' regexp '[xyz]'; -- 0
select 'abc' regexp '[xaz]'; -- 1

-- [^...] 注意 ^ 符合只有在 [] 内才是取反的意思,在别的地方都是表示开始处匹配
-- 含义: abc 里面任意单个字符都没有在前面出现
select 'a' regexp '[^abc]';   -- 0
select 'x' regexp '[^abc]';   -- 1


select 'abc' regexp '[^a]';   -- 1

-- a* 匹配0个或多个a,包括空字符串。可以作为占位符使用,有没有指定字符都可以匹配到数据
select 'stab' regexp '.ta*b'; -- 1
select 'stb' regexp '.ta*b'; -- 1
select '' regexp 'a*'; -- 1

-- a+ 匹配1个或多个a,但是不包括空字符
select 'stab' regexp '.ta+b';  -- 1
select 'stb' regexp '.ta+b';   -- 0

-- a? 匹配0个或一个a(只能出现一个或者0个)

select 'stab' regexp '.ta?b';  -- 1
select 'stb' regexp '.ta?b';   -- 1
select 'staab' regexp '.ta?b';   -- 0

-- a1|a2 匹配a1或者a2

select 'a' regexp 'a|b';  -- 1
生命是有光的
关注
  • 11
    点赞
  • 踩
  • 34
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
MySQL8.0的下载安装与使用（图文教程、新手必备、手把手保姆级别、Windows版、2023年新版）
m0_63749236的博客
12-14 1万+
MySQL8.0的下载安装与使用（图文教程、新手必备、手把手保姆级别、Windows版、2023年新版）
MySQL（八）：排序与分页
weixin_52533007的博客
06-14 1676
本博主将用CSDN记录软件开发求学之路上亲身所得与所学的心得与知识，有兴趣的小伙伴可以关注博主！也许一个人独行，可以走的很快，但是一群人结伴而行，才能走的更远！ 第11至20条记录： 第21至30条记录： 分页显式公式：（当前页数-1）*每页条数，每页条数 注意：LIMIT 子句必须放在整个SELECT语句的最后！ 使用 LIMIT 的好处 约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SE
MySQL 8.0 安装、配置、启动、登录、连接、卸载教程
最新发布
weixin_74865657的博客
06-07 1911
MySQL是一个广泛使用的开源关系数据库管理系统，以其高性能、可靠性和易用性而闻名。本教程将指导您完成在不同操作系统上安装、配置、启动MySQL 8.0，以及如何安全地登录和在需要时卸载它。请记住，配置 MySQL 时，您可能需要根据实际需求调整配置文件中的设置。完成安装后，确保您的 MySQL 服务能够正常运行，并且您可以使用默认的 root 用户登录。希望本教程能够帮助您快速地设置和运行 MySQL 8.0，为您的开发工作或数据库管理任务提供支持。
MySQL 排序规则
Dablelv 的博客专栏。
09-25 1470
字符集是一组符号和编码。排序规则是一组用于比较字符集中的字符的规则。每个 MySQL 字符集可以支持一个或者多个排序规则，用于定义每个字符的比较规则，包括是否区分大小写，是否区分重音等。
三、MySQL实例初始化、设置、服务启动关闭、环境变量配置、客户端登入（一篇足以从白走到黑）
qq_52495761的博客
01-14 1473
如果电脑上可能安装多个版本mysql，请在服务名后面保留版本标识，例如：MySQL80，这样可以区别用哪个版本的mysql。
MySQL 8.0 参考手册教程pdf
09-19
此外，mysql客户端也支持批处理模式，用户可以将一系列查询保存到文本文件中，然后通过mysql命令执行这个文件，这种模式在执行大量重复性任务时非常高效。 教程的第二部分介绍了如何连接到MySQL服务器以及断开连接...
完整版 MySQL8.0从入门到精通 MySQL数据库教程 全套PPT课件资源集合 共26个章节 含全部源代码.rar
12-18
完整版 MySQL8.0从入门到精通 MySQL数据库教程 第01章 初始MySQL（共19页）.ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第02章 MySQL的安装与配置（共14页）.ppt 完整版 MySQL8.0从入门到精通 MySQL数据库...
MySQL8.0参考手册.pdf
04-25
MySQL 8.0 参考手册是 MySQL 官方提供的一份详细的参考手册，该手册涵盖了 MySQL 8.0 版本的所有方面，包括概述、安装、配置、管理、安全、性能优化、故障排除等等。下面是该手册中的一些重要知识点： 1. MySQL ...
完整版 MySQL8.0从入门到精通 MySQL数据库教程 第11章 视图（共20页）.ppt
12-18
【MySQL8.0 视图详解】 视图是MySQL数据库中的一个重要概念，它是一个虚拟的表，基于一个或多个实际的表，通过SQL查询语句创建。视图并不存储实际数据，而是根据需要动态从底层表中提取数据。在本教程的第11章中，...
最新 MySQL 8.0 从入门到实战
09-12
01:Mysql8.0介绍 02:什么是关系数据库与非关系型数据库 03:关系型数据库的特点和适用场景 04:非关系型数据库的特点和适用场景 05:关系统型数据库选型步骤 06:为项目选择适合的数据库 07:下载安装VirtualBox 08:准备...
mysql 8.0.17 安装与使用教程图解
09-09
主要介绍了mysql 8.0.17 安装与使用教程图解，本文图文并茂给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下
MySQL8排序字符集
科技互联网领域深度探索者，乐于分享与写作。
08-22 2258
utf8mb4已成为默认字符集，在MySQL 8.0.1及更高版本中将utf8mb4_0900_ai_ci作为默认排序规则。以前，utf8mb4_general_ci是默认排序规则。由于utf8mb4_0900_ai_ci排序规则现在是默认排序规则，因此默认情况下新表格可以存储基本多语言平面之外的字符。编码是一种变长的编码机制，可以用1~4个字节存储字符。，最多占用4个字节。编码，例如emoji字符或者复杂的汉字，会导致存储异常。，而是阉割版的，最长只有3个字节。MySQL 8.0排序规则默认的是。
MySQL8.0使用教程
测试之路
06-12 1622
MySQL8.0使用教程
MySQL8.0基础操作
qq_45673356的博客
10-13 1022
1启动与关闭 1、进入MySQL：启动MySQL Command Line Client（MySQL的DOS界面），直接输入安装时的密码即可。此时的提示符是：mysql> 2、退出MySQL：quit或exit 二、库操作 1、创建数据库 命令：create database <数据库名> 例如：建立一个名为clhdb的数据库 mysql> create database clhdb; 2、显示所有的数据库 命令：show databases （注意：最后有个s） mysql&
MySQL（8.0版本）
weixin_64313980的博客
07-01 781
目录：用户的管理建库建表CRUD视图执行计划MySQL面试题 前言：今天小刘给大家带来的是MySql的一些知识点，在前期我们使用过的数据库有sqlServer,和oracle，今天的MySQL与他们也是大同小异，不过会有一个高级部分：执行计划大家还没有见过，接下来我们就进入主题吧关于用户管理，小刘会结合一些企业中的使用的一些场景给大家进行一个讲解以下是在一些有规模的公司中不同开发人员的权限还有就是我们MySQL默认数据库里面的四张表（user,db,tables,columns_priv） 这四张表不能删除
【超详细】MYSQL 8.0 数据库安装与配置教程
m0_67393157的博客
03-29 5778
【超详细】MYSQL 8.0 数据库安装与配置教程 一 、写在前面的话 二、安装步骤 一、MySQL的下载与安装 MySQL8.0连接测试 二、MySQL环境变量的配置 三、结语 叮嘟！这里是小啊呜的学习课程资料整理。好记性不如烂笔头，今天也是努力进步的一天。一起加油进阶吧！ 一 、写在前面的话 最近换新电脑，有些软件需要重新安装，正好抽空博客记录一下咯~ 关于 MySQL： 是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流
Mysql8.0 安装教程
热门推荐
lhf1992826的博客
06-04 2万+
1.安装环境 ：win64    1.1 下载mysql安装包地址： https://dev.mysql.com/downloads/file/?id=4762332.安装    2.1 解压下载的ZIP压缩包 我的解压至 E:\Mysql Server\mysql-8.0.11-winx64    2.2 配置环境变量  将安装目录添加至path变量中    3.在解压目录中增加配置文件 my....
Mysql操作全集
sunzhuan158的博客
09-17 288
Mysql全集实战应用，SQL完整版
MySQL 8.0安装与远程连接指南
本文档是针对初学者的SQL入门教程，主要关注于MySQL 8.0的环境搭建和基础SQL语句的学习。在环境搭建部分，教程强调了使用最新版本MySQL 8.x的重要性，并提供了Windows、macOS和Linux三个不同操作系统下的安装步骤。...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

生命是有光的

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值