mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客- | ImapBox

mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客-

14 六月
星期日, 14 六月 2020 08:50 Last Updated on 星期日, 14 六月 2020 08:50 0 Comments

数据库的基础操作

mysql采用的同样是sql语法，mysql数据表的常用操作已经总结在这篇文章

创建新的数据库

创建数据库

create database 数据库名 （字符集） mysql> create database test1  character set utf8 collate utf8_bin;

查看数据库的字符集

show create database 数据库名 mysql> show create database test1     -> ; +----------+---------------------------------------------------------------------------------+ | Database | Create Database                                                                 | +----------+---------------------------------------------------------------------------------+ | test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ | +----------+---------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

查看所有的数据库

show databases;

删除数据库

drop database 数据库名

使用数据库

use database 数据库名字;

查看数据库里的所有表

show tables;

数据表的基础操作

创建表

mysql> create table classes     -> (     -> id int(11) primary key,     -> name varchar(22),     -> age int(11),     -> salary float     -> );

查看表结构

mysql> desc classes; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO   | PRI | NULL    | | | name   | varchar(22) | YES  | | NULL    | | | age    | int(11) | YES  | | NULL    | | | salary | float       | YES  | | NULL    | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

表中添加新字段

## alter table 表名 add 列名 类型 mysql> alter table classes add location varchar(50); Query OK, 0 rows affected (0.02 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> desc classes; +----------+-------------+------+-----+---------+-------+ | Field    | Type        | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO   | PRI | NULL    | | | name     | varchar(22) | YES  | | NULL    | | | age      | int(11) | YES  | | NULL    | | | salary   | float       | YES  | | NULL    | | | location | varchar(50) | YES  | | NULL    | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)

字段重命名

##  alert table 表名 change 原名 新名 类型及约束 mysql> alter table classes change location LOCATION varchar(50); Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0  mysql> desc classes; +----------+-------------+------+-----+---------+-------+ | Field    | Type        | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO   | PRI | NULL    | | | name     | varchar(22) | YES  | | NULL    | | | age      | int(11) | YES  | | NULL    | | | salary   | float       | YES  | | NULL    | | | LOCATION | varchar(50) | YES  | | NULL    | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)

删除字段

alter table 表名 drop 列名 
mysql> alter table test3 drop id; Query OK, 0 rows affected (0.02 sec) Records: 0  Duplicates: 0  Warnings: 0

删除表

drop table 表名

更新数据
语法：

UPDATE table_name SET column1=value1,column2=value2,...WHERE some_column=some_value; 
mysql> update classes set name='yu',salary=7200 where id=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0  
mysql> select * from classes; +----+-------+------+--------+----------+ | id | name  | age  | salary | LOCATION | +----+-------+------+--------+----------+ |  1 | zhou  |   26 |  12000 | aaa      | |  2 | wu    |   22 |   9200 | bbb      | |  3 | zheng |   20 |   8500 | ccc      | |  4 | li    |   18 |  13850 | ddd      | |  5 | yu    |   20 |   7200 | NULL     | |  6 | cao   | NULL |   NULL | NULL     | +----+-------+------+--------+----------+ 6 rows in set (0.00 sec)

插入数据

全列插入：值的顺序与表结构字段的顺序完全一一对应

insert into 表名 values (...) mysql> insert into class2 values(001,"zhao",1,488);

部分列插入：值的顺序与给出的列顺序对应

insert into 表名 (列1,列2..) values(值1,值2..) mysql>  insert into class(id,name) values(3,"sun");

全列多行插入

insert into 表名 values(...),(...)...; mysql> insert into class values(4,"cao",20,"aaa",8500),(5,"wei",18,"bbb",)7000),(6,"yang",21,"ccc",11000);

部分列多行插入

insert into 表名(列1,...) values(值1,...),(值1,...)...; mysql> insert into class(id,name) values(10,"wang"),(11,"wu"),(12,"liu");

主键约束

主键约束要求主键列的数据唯一，并且不允许为空

语法： 字段名 数据类型 PRIMARY KEY [默认值] -> id INT(11) PRIMARY KEY, 或 在定义完所有列之后指定主键 -> PRIMARY KEY(id)

多字段联合主键

## PRIMARY KEY [字段1，字段2，....] mysql> create table test2     -> (     -> name varchar(25),     -> de int(11),     -> salary float,     -> primary key(name,de)     -> ); Query OK, 0 rows affected (0.02 sec)  mysql> desc test2; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name   | varchar(25) | NO   | PRI | NULL    | | | de     | int(11) | NO   | PRI | NULL    | | | salary | float       | YES  | | NULL    | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

外键约束

外键用来在两个表数据之间建立连接，它可以是一列或者多列
目的是一张表insert数据时要受到另一个表的约束

## [CONSTRAINT<外键名>] FOREIGN KEY [字段名1，字段名2...] REFERENCES<主表名> 主键列1[主键列 2...] 
# 创建新表与class做约束 mysql> create table class2     -> (     -> id int primary key,     -> name varchar(22),     -> deptID int,     -> score float,     -> constraint ID foreign key(deptID) references class(id)     -> ); Query OK, 0 rows affected (0.01 sec) #class表插入数据 mysql> select * from class; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  1 | zhao | NULL | NULL     |   NULL | |  2 | qian | NULL | NULL     |   NULL | |  3 | sun  | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 3 rows in set (0.00 sec) # class2 再插入数据，class表id不存在的class2不能再写入数据 mysql> insert into class2 values(001,"zhao",1,488); Query OK, 1 row affected (0.00 sec)  mysql> insert into class2 values(002,"qian",4,375); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test1`.`class2`, CONSTRAINT `ID` FOREIGN KEY (`deptID`) REFERENCES `class` (`id`))

若表已经创建好，需要设置外键
添加约束

alter table class2 add foreign key ID(deptID) references class(id);

删除外键约束

alter table class2 drop foreign key ID;

非空约束

非空约束指字段的值不能为空
not null 是指该字段不能为空，但是可以重复;
unique 是值该字段不可以重复，但是可以为空;

字段名 数据类型 not null -> name VARCHAR(25) NOT NULL, [CONSTRATIN <约束名>] UNIQUE （<字段名>） -> CONSTRAINT STH UNIQUE(name) ## 后期添加约束 mysql> alter table test3 modify deptID int(25) unique;

其中modify的意思可以理解为重构，你甚至可以用modify来进行对其他约束的创建
例如下面：
alter table test modify deptID char(10) not null;建立非空约束
alter table test modify deptID char(10); 删除非空约束

默认约束

默认约束指定某列的默认值。

字段名 数据类型 DEFAULT 默认值 mysql> create table test3     -> (     -> id int primary key,     -> name varchar(25) not null,     -> deptID int(25) default 0001,     -> salary float     -> );      mysql> desc test3     -> ; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO   | PRI | NULL    | | | name   | varchar(25) | NO   | | NULL    | | | deptID | int(25) | YES  | | 1       | | | salary | float       | YES  | | NULL    | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

表的属性值自动增加

## 字段名 数据类型 AUTO_INCREMENT mysql> create table class2 -> (  -> id INT(11) PRIMARY KEY AUTO_INCREMENT,  -> name VARCHAR(25) NOT NULL,  -> deptId INT(11),  -> salary FLOAT  -> );  Query OK, 0 rows affected (0.00 sec) #若表已经存在 mysql> alter table class2 modify id int(11)  auto_increment; Query OK, 1 row affected (0.04 sec) Records: 1  Duplicates: 0  Warnings: 0

在表里面插入数据，不写入 id

mysql> insert into class2 (name,score) values('li',96),('liu',63); Query OK, 2 rows affected (0.00 sec) Records: 2  Duplicates: 0  Warnings: 0  mysql> select * from class2     -> ; +----+------+--------+-------+ | id | name | deptID | score | +----+------+--------+-------+ |  1 | zhao |      1 |   488 | |  2 | li   |   NULL |    96 | |  3 | liu  |   NULL |    63 | +----+------+--------+-------+ 3 rows in set (0.00 sec)

更改表的存储引擎

## ALTER TABLE <表名> ENGINE=<更改后的存储引擎> mysql> alter table classes engine=MyISAM; Query OK, 0 rows affected (0.01 sec) Records: 0  Duplicates: 0  Warnings: 0 
mysql> show create table classesG; *************************** 1. row ***************************        Table: classes Create Table: CREATE TABLE `classes` ( `id` int(11) NOT NULL,   `name` varchar(22) COLLATE utf8_bin DEFAULT NULL,   `age` int(11) DEFAULT NULL,   `salary` float DEFAULT NULL,   `LOCATION` varchar(50) COLLATE utf8_bin DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.00 sec)

数据表的查询

基本查询

查询所有列

## select * from 表名 mysql> select * from class; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  1 | zhao | NULL | NULL     |   NULL | |  2 | qian | NULL | NULL     |   NULL | |  3 | sun  | NULL | NULL     |   NULL | |  4 | cao  |   20 | aaa      |   8500 | |  5 | wei  |   18 | bbb      |   7000 | |  6 | yang |   21 | ccc      |  11000 | | 10 | wang | NULL | NULL     |   NULL | | 11 | wu   | NULL | NULL     |   NULL | | 12 | liu  | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 9 rows in set (0.01 sec)

查询指定列

## select 列1，列2，...from 表名； mysql> select id,name from class; +----+------+ | id | name | +----+------+ |  1 | zhao | |  2 | qian | |  3 | sun  | |  4 | cao  | |  5 | wei  | |  6 | yang | | 10 | wang | | 11 | wu   | | 12 | liu  | +----+------+ 9 rows in set (0.00 sec)

条件语句查询

where后面支持多种运算符，进行条件的处理 ；
常用的运算符如下：
比较运算符 逻辑运算符 模糊查询 范围查询 空判断

比较运算符如下：
mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客-
查询class表id大于等于10的人

mysql> select * from class where id >= 10; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ | 10 | wang | NULL | NULL     |   NULL | | 11 | wu   | NULL | NULL     |   NULL | | 12 | liu  | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 3 rows in set (0.00 sec)

查询class表name是“sun”的人

mysql> select * from class where name="sun"; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  3 | sun  | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 1 row in set (0.00 sec)

逻辑运算符
and 与
or 或
not 否

查询名字是“sun”，并且id小于10

mysql> select * from class where name="sun" and id < 10; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  3 | sun  | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 1 row in set (0.00 sec)

查询名字是“cao”或年龄是18

mysql> select * from class where name="cao" or age=18; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  4 | cao  |   20 | aaa      |   8500 | |  5 | wei  |   18 | bbb      |   7000 | +----+------+------+----------+--------+ 2 rows in set (0.00 sec)

模糊查询
like
%表示任意多个任意字符
_表示一个任意字符

查询名字类似‘ya%’ 或者类似‘li%’

mysql> select * from class where name like 'ya%' or name like 'li%'; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  6 | yang |   21 | ccc      |  11000 | | 12 | liu  | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 2 rows in set (0.00 sec)

范围查询
in表示在一个非连续的范围内
between … and …表示在一个连续的范围内

查询class表id为1 3 5的人

mysql> select * from class where id in(1,3,5); +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  1 | zhao | NULL | NULL     |   NULL | |  3 | sun  | NULL | NULL     |   NULL | |  5 | wei  |   18 | bbb      |   7000 | +----+------+------+----------+--------+ 3 rows in set (0.00 sec)

查询

mysql> select * from class where id between 3 and 5; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  3 | sun  | NULL | NULL     |   NULL | |  4 | cao  |   20 | aaa      |   8500 | |  5 | wei  |   18 | bbb      |   7000 | +----+------+------+----------+--------+ 3 rows in set (0.00 sec)

空判断
查询出来条件里的null的项

mysql> select * from class where age is null; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  1 | zhao | NULL | NULL     |   NULL | |  2 | qian | NULL | NULL     |   NULL | |  3 | sun  | NULL | NULL     |   NULL | | 10 | wang | NULL | NULL     |   NULL | | 11 | wu   | NULL | NULL     |   NULL | | 12 | liu  | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 6 rows in set (0.00 sec)

也可以反过来查询呐

mysql> select * from class where age is not null; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  4 | cao  |   20 | aaa      |   8500 | |  5 | wei  |   18 | bbb      |   7000 | |  6 | yang |   21 | ccc      |  11000 | +----+------+------+----------+--------+ 3 rows in set (0.00 sec)

查询排序

将行数据按照列1进行排序，如果某些行 列1 的值相同时，则按照 列2 排序，以此类推
asc从小到大排列，即升序
desc从大到小排序，即降序
默认按照列值从小到大排列（即as升序）

语法使用

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

升序

mysql> select * from classes order by age asc; +----+-------+------+--------+----------+ | id | name  | age  | salary | LOCATION | +----+-------+------+--------+----------+ |  6 | cao   | NULL |   NULL | NULL     | |  4 | li    |   18 |  13850 | ddd      | |  3 | zheng |   20 |   8500 | ccc      | |  5 | yu    |   20 |   7200 | NULL     | |  2 | wu    |   22 |   9200 | bbb      | |  1 | zhou  |   26 |  12000 | aaa      | +----+-------+------+--------+----------+ 6 rows in set (0.00 sec)

降序

mysql> select * from classes order by salary desc; +----+-------+------+--------+----------+ | id | name  | age  | salary | LOCATION | +----+-------+------+--------+----------+ |  4 | li    |   18 |  13850 | ddd      | |  1 | zhou  |   26 |  12000 | aaa      | |  2 | wu    |   22 |   9200 | bbb      | |  3 | zheng |   20 |   8500 | ccc      | |  5 | yu    |   20 |   7200 | NULL     | |  6 | cao   | NULL |   NULL | NULL     | +----+-------+------+--------+----------+ 6 rows in set (0.00 sec)

limit限制查询

使用limit限制查询结果的数量 select返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用limit关键字，语法格式如下：

limit [位置偏移量] 行数

从第5行开始，并且只查询1条记录

mysql> select * from classes limit 5,1; +----+------+------+--------+----------+ | id | name | age  | salary | LOCATION | +----+------+------+--------+----------+ |  6 | cao  | NULL |   NULL | NULL     | +----+------+------+--------+----------+ 1 row in set (0.00 sec)

聚合函数

总数

count(*) 表示计算总行数，括号中写星与列名，结果是相同的

mysql> select count(*) from classes; +----------+ | count(*) | +----------+ |        6 | +----------+

最大值

max(列) 表示求此列的最大值

mysql> select max(salary) from classes; +-------------+ | max(salary) | +-------------+ |       13850 | +-------------+ 1 row in set (0.00 sec)

最小值

mysql> select min(salary) from classes; +-------------+ | min(salary) | +-------------+ |        7200 | +-------------+ 1 row in set (0.00 sec)

求和

sum(列) 表示求此列的和

mysql> select sum(salary) from classes; +-------------+ | sum(salary) | +-------------+ |       50750 | +-------------+ 1 row in set (0.00 sec)

平均工资

mysql> select sum(salary)/count(*) from classes; +----------------------+ | sum(salary)/count(*) | +----------------------+ |    8458.333333333334 | +----------------------+ 1 row in set (0.00 sec)

平均值

mysql> select avg(age) from classes; +----------+ | avg(age) | +----------+ |  21.2000 | +----------+ 1 row in set (0.01 sec)

分组查询(group by)

分组查询是对数据按照某个或多个字段进行分组，MySQL中使用group by关键字对数据进行分组，基本语法形式为：

group by 字段 创建分组

Group by 关键字通常和集合函数或group_concat一起使用，例如：
MAX()、MIN()、COUNT()、SUM()、AVG()

按年龄age分组

mysql> select age from classes group by age; +------+ | age  | +------+ | NULL | |   18 | |   20 | |   22 | |   26 | +------+ 5 rows in set (0.00 sec)

group by + group_concat()

group by + group_concat(字段名)结合一起使用，
用来查询在该分组下指定字段的集合；
例如：

mysql> select age,group_concat(name) from classes group by age; +------+--------------------+ | age  | group_concat(name) | +------+--------------------+ | NULL | cao                | |   18 | li                 | |   20 | zheng,yu           | |   22 | wu                 | |   26 | zhou               | +------+--------------------+ 5 rows in set (0.00 sec)

group by + 聚合函数

和group concat()类似，结合函数查询该分组集合的情况

mysql> select age,avg(salary) from classes group by age; +------+-------------+ | age  | avg(salary) | +------+-------------+ | NULL |        NULL | |   18 |       13850 | |   20 |        7850 | |   22 |        9200 | |   26 |       12000 | +------+-------------+ 5 rows in set (0.00 sec)  mysql> 
mysql> select age,count(*) from classes group by age; +------+----------+ | age  | count(*) | +------+----------+ | NULL |        1 | |   18 |        1 | |   20 |        2 | |   22 |        1 | |   26 |        1 | +------+----------+ 5 rows in set (0.00 sec)

group by + having

having 条件表达式：用来过滤分组结果
having作用和where类似，但having只能用于group by 而where是用来过滤表数据

如下，查询年龄分组的平均工资且平均超过10000的项

mysql> select age,avg(salary) from classes group by age having avg(salary) > 10000; +------+-------------+ | age  | avg(salary) | +------+-------------+ |   18 |       13850 | |   26 |       12000 | +------+-------------+ 2 rows in set (0.00 sec)  mysql>

group by + with rollup

with rollup的作用是：在最后新增一行，来记录当前表中该字段对应的操作结果，即统计记录数量，一般是汇总结果。

mysql> select salary,count(*) from classes group by salary with rollup; +--------+----------+ | salary | count(*) | +--------+----------+ |   NULL |        1 | |   7200 |        1 | |   8500 |        1 | |   9200 |        1 | |  12000 |        1 | |  13850 |        1 | |   NULL |        6 | +--------+----------+ 7 rows in set (0.01 sec)

连接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接。

内连接

内连接（inner join）使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新纪录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

语法：

select * from 表1 inner join 表2 on 表1.列 运算符 表2.列 
mysql> select * from class inner join classes on class.name = classes.name; +----+------+------+----------+--------+----+------+------+--------+----------+ | id | name | age  | location | salary | id | name | age  | salary | LOCATION | +----+------+------+----------+--------+----+------+------+--------+----------+ |  4 | cao  |   20 | aaa      |   8500 |  6 | cao  | NULL |   NULL | NULL     | | 11 | wu   | NULL | NULL     |   NULL |  2 | wu   |   22 |   9200 | bbb      | +----+------+------+----------+--------+----+------+------+--------+----------+ 2 rows in set (0.00 sec)

外连接

外连接查询将将查询多个表中相关联的行，返回结果中不仅包含符合连接条件的行，而且还包含左表（左外连接或左连接）、右表（右外连接或右连接），所以说，左连接和右连接都属于外连接，相比内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。
语法

select * from 表1 outer join 表2 on 表1.列 运算符 表2.列

左连接

Left join（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录

mysql> select * from class left (outer) join classes on class.name = classes.name e; +----+------+------+----------+--------+------+------+------+--------+----------+ | id | name | age  | location | salary | id | name | age  | salary | LOCATION | +----+------+------+----------+--------+------+------+------+--------+----------+ | 11 | wu   | NULL | NULL     |   NULL |    2 | wu   |   22 |   9200 | bbb      | |  4 | cao  |   20 | aaa      |   8500 |    6 | cao  | NULL |   NULL | NULL     | |  1 | zhao | NULL | NULL     |   NULL | NULL | NULL | NULL |   NULL | NULL     | |  2 | qian | NULL | NULL     |   NULL | NULL | NULL | NULL |   NULL | NULL     | |  3 | sun  | NULL | NULL     |   NULL | NULL | NULL | NULL |   NULL | NULL     | |  5 | wei  |   18 | bbb      |   7000 | NULL | NULL | NULL |   NULL | NULL     | |  6 | yang |   21 | ccc      |  11000 | NULL | NULL | NULL |   NULL | NULL     | | 10 | wang | NULL | NULL     |   NULL | NULL | NULL | NULL |   NULL | NULL     | | 12 | liu  | NULL | NULL     |   NULL | NULL | NULL | NULL |   NULL | NULL     | +----+------+------+----------+--------+------+------+------+--------+----------+ 9 rows in set (0.00 sec)

右连接

Right join（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录

mysql> mysql> select class right (outer) join classes on class.name = classes.name; +------+------+------+----------+--------+----+-------+------+--------+----------+ | id | name | age  | location | salary | id | name  | age  | salary | LOCATION | +------+------+------+----------+--------+----+-------+------+--------+----------+ |    4 | cao  |   20 | aaa      |   8500 |  6 | cao   | NULL |   NULL | NULL     | |   11 | wu   | NULL | NULL     |   NULL |  2 | wu    |   22 |   9200 | bbb      | | NULL | NULL | NULL | NULL     |   NULL |  1 | zhou  |   26 |  12000 | aaa      | | NULL | NULL | NULL | NULL     |   NULL |  3 | zheng |   20 |   8500 | ccc      | | NULL | NULL | NULL | NULL     |   NULL |  4 | li    |   18 |  13850 | ddd      | | NULL | NULL | NULL | NULL     |   NULL |  5 | yu    |   20 |   7200 | NULL     | +------+------+------+----------+--------+----+-------+------+--------+----------+ 6 rows in set (0.00 sec)

复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确
例如：

mysql> select * from class inner join classes on class.id = classes.id order by class.id desc; +----+------+------+----------+--------+----+-------+------+--------+----------+ | id | name | age  | location | salary | id | name  | age  | salary | LOCATION | +----+------+------+----------+--------+----+-------+------+--------+----------+ |  6 | yang |   21 | ccc      |  11000 |  6 | cao   | NULL |   NULL | NULL     | |  5 | wei  |   18 | bbb      |   7000 |  5 | yu    |   20 |   7200 | NULL     | |  4 | cao  |   20 | aaa      |   8500 |  4 | li    |   18 |  13850 | ddd      | |  3 | sun  | NULL | NULL     |   NULL |  3 | zheng |   20 |   8500 | ccc      | |  2 | qian | NULL | NULL     |   NULL |  2 | wu    |   22 |   9200 | bbb      | |  1 | zhao | NULL | NULL     |   NULL |  1 | zhou  |   26 |  12000 | aaa      | +----+------+------+----------+--------+----+-------+------+--------+----------+ 6 rows in set (0.00 sec)  mysql> 
mysql> select * from class inner join classes on class.id = classes.id and class.id=2; +----+------+------+----------+--------+----+------+------+--------+----------+ | id | name | age  | location | salary | id | name | age  | salary | LOCATION | +----+------+------+----------+--------+----+------+------+--------+----------+ |  2 | qian | NULL | NULL     |   NULL |  2 | wu   |   22 |   9200 | bbb      | +----+------+------+----------+--------+----+------+------+--------+----------+ 1 row in set (0.00 sec)

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从mysql4.1开始引入。在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操
作符有any（some）、all、in、exists。子查询可以添加到select、update和delete语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<”,“<=”,“>”,“>=”和“!=”等。

带any、some关键字的子查询

any和some关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
例：
先创建2个新表再插入数据

mysql> create table tb1(num1 int not null); Query OK, 0 rows affected (0.03 sec) mysql> create table tb2(num2 int not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into tb1 values(1),(5),(13),(27); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into tb2 values(6),(14),(11),(20); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0

查询tb1表里面有大于tb2的值

mysql> select num1 from tb1 where num1 > any (select num2 from tb2); +------+ | num1 | +------+ | 13 | | 27 | +------+ 2 rows in set (0.01 sec)

带all关键字的子查询

all关键字与any和some不同，使用all时需要同时满足所有内层查询的条件
查询tb1里面的值都大于tb2的值

mysql> select num1 from tb1 where num1 > all (select num2 from tb2); +------+ | num1 | +------+ | 27 | +------+ 1 row in set (0.00 sec)

正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串；
mysql中使用regexp关键字指定正则表达式的字符匹配模式.
正则选项参考如下表：
mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客-
mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客-
例子：

mysql> select * from classes where name regexp '^l'; +----+------+------+--------+----------+ | id | name | age  | salary | LOCATION | +----+------+------+--------+----------+ |  4 | li   |   18 |  13850 | ddd      | +----+------+------+--------+----------+ 1 row in set (0.00 sec) 
mysql> select * from class where name regexp 'ya.*|wa.*'; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  6 | yang |   21 | ccc      |  11000 | | 10 | wang | NULL | NULL     |   NULL | +----+------+------+----------+--------+ 2 rows in set (0.00 sec) 
mysql> select * from class where location regexp 'b{2,}'; +----+------+------+----------+--------+ | id | name | age  | location | salary | +----+------+------+----------+--------+ |  5 | wei  |   18 | bbb      |   7000 | +----+------+------+----------+--------+ 1 row in set (0.00 sec)

  • mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客- mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客- 1
  • mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客- 评论
  • mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客-
    x

    海报

    扫一扫，海报

  • mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客- mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客- 5
  • mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客- 手机看

    到微信朋友圈

    x

    扫一扫，手机阅读

  • mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客-打赏

    打赏

    mysql学习笔记（三）——数据表操作（详细）weixin43815140的博客-

    @next_stations

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

    5C币 10C币 20C币 50C币 100C币 200C币



 
 
 
 


本页所有内容来自官方网站 https://www.imapbox.com 新闻来源：互联网搜索引擎和新闻站

本网页所有图片由 ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片，下载并得到。

ImageBox 图片批量下载器 工具地址: 网页图片批量下载工具-最新版本下载

非凡下载站地址：https://www.crsky.com/soft/35838.html

本网页所有视频内容由 imoviebox边看边下-网页视频下载, iurlBox网页地址收藏管理器 下载并得到。

ImovieBox网页视频下载器 下载地址: ImovieBox网页视频下载器-最新版本下载

本文章由: imapbox邮箱云存储,邮箱网盘,ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片,imoviebox网页视频批量下载器,下载视频内容,为您提供.




文章目录

  • 全球报导 (16311)
  • 全球云计算 (29506)
  • 程序员专区 (1974)
  • Imap 全球通用标准协议 (7)
  • 全球邮局列表 (17)
  • Imapbox 系列软件 (3)
  • 产品一览 (6)
  • 事业方向 (7)
  • 联系我们 (2)

官方链接

imapbox邮箱网盘 imapbox 海外官网 iMapBox百度百科

登录