delete select语句_7、MySQL语句DML

最新推荐文章于 2021-09-18 10:20:19 发布

weixin_39611049

最新推荐文章于 2021-09-18 10:20:19 发布

阅读量306

点赞数

文章标签： delete select语句 insert into语句 insert into语句用法 mysql limit用法

本文详细介绍了SQL的基础操作，包括数据的插入、删除、更新及查询等核心功能，并深入探讨了SELECT查询的不同用法，如单表查询、多表查询、子查询及联合查询等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据插入

数据删除

数据更新

select

查询缓存：

单表查询

去重

条件运算

条件判断数值

列表

正则表达式匹配

聚合运算

别名

分组

多表查询

等值连接

自连接

左连接和右连接

子查询

IN列表

联合查询

条件判断一

条件判断二

数据插入

NSERT INTO： INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...

示例：

MariaDB [cce]> insert into cce values (1,'cce',22,'f');

MariaDB [cce]> insert into cce (Name,age) values ('cce','22');

MariaDB [cce]> insert into cce set Name='wj',age=23;

MariaDB [cce]> insert into cce values (5,'cce',22,'f'),(6,'cwj',22,'m');

注意：如果能一次性插入尽量一次性插入，因为每次插入都会导致索引更新，如果一次一次的插入会影响数据库性能

数据删除

DELETE：DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

(1) DELETE FROM tbl_name WHERE where_condition

(2) DELETE FROM tbl_name [ORDER BY ...] [LIMIT row_count]

示例：

MariaDB [cce]> delete from students where age>30;

MariaDB [cce]> delete from students order by age limit 1; #首先进行排序，然后删除前1个。

数据更新

UPDATE： UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

示例：

MariaDB [cce]> update students set age=27 where name='wj';

MariaDB [cce]> update cce set age=1 order by age limit 2; #首先进行排序，然后更新前2个。

replace #如果存在就覆盖

注意：sql_safe_updates变量可阻止不带条件的更新数据操作；

select

查询路径：

请求->查询缓存：命中后返回

请求->查询缓存->解析器->预处理器->优化器->查询执行引擎->存储引擎->缓存->响应

查询流程：

select->where->group by->having->order by->select->limit

查询缓存：

缓存：k/v

key：查询语句的hash值

value：查询语句的执行结果

如何判断缓存是否命中：

通过查询语句的哈希值判断：哈希值考虑的因素包括

查询本身、要查询数据库、客户端使用的协议版本、...

哪些查询可能不会被缓存？

查询语句中包含UDF

存储函数

用户自定义变量

临时表

mysql系统表或者是包含列级别权限的查询

有着不确定结果值的函数(now())；

查询缓存相关的服务器变量：

query_cache_limit：能够缓存的最大查询结果；(单语句结果集大小上限)

有着较大结果集的语句，显式使用SQL_NO_CACHE，以避免先缓存再移出；

query_cache_min_res_unit：内存块的最小分配单位；缓存过小的查询结果集会浪费内存空间；

较小的值会减少空间浪费，但会导致更频繁地内存分配及回收操作；

较大值的会带来空间浪费；

query_cache_size：查询缓存空间的总共可用的大小；单位是字节，必须是1024的整数倍；

query_cache_strip_comments

query_cache_type：缓存功能启用与否；

ON：启用；

OFF：禁用；

DEMAND：按需缓存，仅缓存SELECT语句中带SQL_CACHE的查询结果；

query_cache_wlock_invalidate：如果某表被其它连接锁定，是否仍然可以从查询缓存中返回查询结果；默认为OFF，表示可以；ON则表示不可以；

状态变量：

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 16759688 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 0 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

+-------------------------+----------+

命中率：

Qcache_hits/Com_select

单表查询

select

[all | distinct | distinctrow ]

[high_priority]

[straight_join]

[sql_small_result] [sql_big_result] [sql_buffer_result]

[sql_cache | sql_no_cache] [sql_calc_found_rows]

select_expr [, select_expr ...]

[from table_references

[partition partition_list]

[where where_condition]

[group by {col_name | expr | position}

[asc | desc], ... [with rollup]]

[having where_condition]

[order by {col_name | expr | position}

[asc | desc], ...]

[limit {[offset,] row_count | row_count offset offset}]

常见用法：

select col1,col2,... from tab_name;

select col1,col2,... from tab_name where where_statement;

select col1,col2,... from tab_name where where_statement group by col_name [having having_statement];

distinct：去重；

sql_cache：显示指定缓存查询语句的结果；

sql_no_cache：显示指定不缓存查询语句的结果；

聚合计算：avg、max、min、count、sum

别名：

select col1 as alias,col2,.. from tab_name where where_statement;

where:

算数运算符：+，-，*，/

比较运算符：=，<>，!=，<=>，

and、or

is null、is not null

区间： between min and max

in：列表

like：模糊匹配，%和_

rlike或regexp

GROUP BY：根据指定的字段把查询的结果进行“分组”以用于“聚合”运算；

avg(), max(), min(), sum(), count()

HAVING：对分组聚合后的结果进行条件过滤；

ORDER BY：根据指定的字段把查询的结果进行排序；

升序：ASC

降序：DESC

LIMIT：对输出结果进行数量限制

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

去重

语法：select distinct col1,col2,... from tab_name where where_statement;

示例：

[hellodb]>select distinct Age from students;

条件运算

MariaDB [hellodb]> select * from students where ClassID is NULL; #显示特定条件为空的

条件判断数值

MariaDB [hellodb]> select * from students where Age + 1 >99;

MariaDB [hellodb]> select * from students where Age > 30 and Age <50; #显示年龄大于30小于50的

MariaDB [hellodb]> select * from students where Age=32 or Age=34; #显示年龄大于30或小于50的

MariaDB [hellodb]> select * from students where age between 30 and 50; #显示年龄大于30小于50的

列表

MariaDB [hellodb]> select * from students where Age in (32,33); #查找出32,33这个年龄的人

正则表达式匹配

MariaDB [hellodb]> select * from students where Name rlike '^D';

聚合运算

MariaDB [hellodb]> select avg(Age) from students; #求所有年龄的平均值

MariaDB [hellodb]> select sum(Age) from students;#求所有年龄的和

MariaDB [hellodb]> select min(Age) from students; #求所有年龄的最小值

MariaDB [hellodb]> select max(Age) from students; #求所有年龄的最大值

MariaDB [hellodb]> select count(Age) from students; #对所有Age字段的总数

别名

只是显示时我们能看到；

[hellodb]>select Age as old from students where StuID=1;

+-----+

| old |

+-----+

| 22 |

+-----+

分组

根据什么字段进行分组，然后使用聚合函数进行计算；

示例：

求各组平均值：select 类别, avg(数量) AS 平均值 from A group by 类别;

求各组记录数目：select 类别, count(*) AS 记录数 from A group by 类别;

# 基础分组

[hellodb]>select avg(Age) from students group by Gender;

+----------+

| avg(Age) |

+----------+

| 19.0000 |

| 33.0000 |

+----------+

2 rows in set (0.00 sec)

# 加入Gender

[hellodb]>select avg(Age),Gender from students group by Gender;

+----------+--------+

| avg(Age) | Gender |

+----------+--------+

| 19.0000 | F |

| 33.0000 | M |

+----------+--------+

# 使用having进行条件判断

[hellodb]>select avg(Age) as avg_age,Gender from students group by Gender having avg_age > 25;

+---------+--------+

| avg_age | Gender |

+---------+--------+

| 33.0000 | M |

+---------+--------+

1 row in set (0.00 sec)

MariaDB [hellodb]> select avg(Age),Gender from students group by Gender; #根据Gender字段对年龄进行分组，显示不同性别的年龄平均值

MariaDB [hellodb]> select avg(Age) as avg_age,Gender from students group by Gender having avg_age > 20; #根据Gender字段对年龄进行分组，显示不同性别的年龄平均值，然后只显示平均值大于20的结果

多表查询

内连接：

等值连接：让表之间的字段以等值的方式建立连接；

不等值连接：

自然连接

自连接

外连接：

左外连接：以左侧表为准，右侧表有的则等值链接，没有的则留空

FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col

右外连接：

FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col

等值连接

直接使用简单的sql语句进行表之间的连接

[hellodb]>select * from students as s,classes as c where s.ClassID=c.ClassID and s.Name='Xie Yanke';

+-------+-----------+-----+--------+---------+-----------+---------+----------+----------+

+-------+-----------+-----+--------+---------+-----------+---------+----------+----------+

| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 |

+-------+-----------+-----+--------+---------+-----------+---------+----------+----------+

1 row in set (0.00 sec)

MariaDB [hellodb]> select * from students,classes where students.ClassID=classes.ClassID;

MariaDB [hellodb]> select s.Name,t.Name from students as s,students as t where s.TeacherID=t.StuID; #查询同一张表中的一个字段和另一个字段相等的列，此处使用别名的方式

自连接

自连接的意思就是自己表连接自己表；

[hellodb]>select s.Name,t.Name from students as s,students as t where s.TeacherID=t.StuID;

+-------------+-------------+

| Name | Name |

+-------------+-------------+

| Shi Zhongyu | Xie Yanke |

| Shi Potian | Xi Ren |

| Xie Yanke | Xu Zhu |

| Ding Dian | Ding Dian |

| Yu Yutong | Shi Zhongyu |

+-------------+-------------+

5 rows in set (0.00 sec)

左连接和右连接

使用left|right|inner join来连接不同的表，left是指左边表会全部显示，right是指右边表会全部显示，# inner将出现NULL的一行隐藏；

左外连接

MariaDB [hellodb]> select s.Name,t.Name from students as s left join students as t on s.TeacherID=t.StuID; #以左表为准

右外连接

MariaDB [hellodb]> select s.Name,t.Name from students as s right join students as t on s.TeacherID=t.StuID; #以右表为准

# 查看students表结构

[hellodb]>desc students;

+-----------+---------------------+------+-----+---------+----------------+

+-----------+---------------------+------+-----+---------+----------------+

| Age | tinyint(3) unsigned | NO | | NULL | |

+-----------+---------------------+------+-----+---------+----------------+

6 rows in set (0.00 sec)

# 查看classes表结构

[hellodb]>desc classes;

+----------+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

# 将两张表连接起来查询

[hellodb]>select * from students as s left join classes as c on s.ClassID=c.ClassID and s.StuID < 4;

+-------+-------------+-----+--------+---------+-----------+---------+-------------+----------+

+-------+-------------+-----+--------+---------+-----------+---------+-------------+----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Emei Pai | 7 |

| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Shaolin Pai | 10 |

| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 |

+-------+-------------+-----+--------+---------+-----------+---------+-------------+----------+

3 rows in set (0.00 sec)

子查询

所谓子查询就是在查询中嵌套查询；

用于where子句中的子查询：

(1):用于比较表达式中的子查询：子查询仅能返回单个值；

(2):用户IN中的子查询：子查询可以返回多个值；

(3):用于exists中的子查询

用于from子句中的子查询：

select tab_alias,... from (select select__statement) as tab_alias where where_statement;

# 显示年龄大于平均年龄的人

[hellodb]>select Name,Age from students where Age > (select avg(Age) from students);

+--------------+-----+

| Name | Age |

+--------------+-----+

| Xie Yanke | 53 |

| Ding Dian | 32 |

| Shi Qing | 46 |

| Tian Boguang | 33 |

| Sun Dasheng | 100 |

+--------------+-----+

5 rows in set (0.00 sec)

# 显示性别为男性并且年纪大于30的人

[hellodb]>select Name,Age from (select * from students where Gender='M') as s where s.Age > 30;

+--------------+-----+

| Name | Age |

+--------------+-----+

| Xie Yanke | 53 |

| Ding Dian | 32 |

| Shi Qing | 46 |

| Tian Boguang | 33 |

| Sun Dasheng | 100 |

+--------------+-----+

5 rows in set (0.00 sec)

IN列表

MariaDB [hellodb]> select * from students where age in (select distinct age from students where Gender='F');

联合查询

所谓联合查询，实际就是将多个查询语句执行的结果联合显示

[hellodb]>select StuID,Name from students UNION select TID,Name from teachers;

+-------+---------------+

| StuID | Name |

+-------+---------------+

| 1 | Shi Zhongyu |

| 2 | Shi Potian |

| 3 | Xie Yanke |

| 4 | Ding Dian |

| 5 | Yu Yutong |

| 6 | Shi Qing |

| 7 | Xi Ren |

| 8 | Lin Daiyu |

| 9 | Ren Yingying |

| 10 | Yue Lingshan |

| 11 | Yuan Chengzhi |

| 12 | Wen Qingqing |

| 13 | Tian Boguang |

| 14 | Lu Wushuang |

| 15 | Duan Yu |

| 16 | Xu Zhu |

| 17 | Lin Chong |

| 18 | Hua Rong |

| 19 | Xue Baochai |

| 20 | Diao Chan |

| 21 | Huang Yueying |

| 22 | Xiao Qiao |

| 23 | Ma Chao |

| 24 | Xu Xian |

| 25 | Sun Dasheng |

| 1 | Song Jiang |

| 2 | Zhang Sanfeng |

| 3 | Miejue Shitai |

| 4 | Lin Chaoying |

+-------+---------------+

29 rows in set (0.00 sec)

条件判断一

-- 搜索cce表，判断如果id>2则添加字段info值为“ID大于2”否则info的字段则为“ID小于等于2”;

[cce]>select id,case when id > 2 then 'ID大于2' else 'ID小于等于2' end info,time from cce;

+------+-----------------+---------------------+

| id | info | time |

+------+-----------------+---------------------+

| 2 | ID小于等于2 | 2018-07-07 16:01:46 |

| 3 | ID大于2 | 2018-07-07 16:01:46 |

| 4 | ID大于2 | 2018-07-07 16:01:46 |

| 5 | ID大于2 | 2018-07-07 16:01:46 |

| 1 | ID小于等于2 | 2018-07-07 16:01:46 |

+------+-----------------+---------------------+

5 rows in set (0.00 sec)

条件判断二

# 新建一个Chinese字段，如果Gender的值为M那么就Chinese的值就是男，否则就是女；

[hellodb]>select `Name`,Gender,if(Gender='M',"男","女") as Chinese from students limit 5,2;

+----------+--------+---------+

| Name | Gender | Chinese |

+----------+--------+---------+

| Shi Qing | M | 男 |

| Xi Ren | F | 女 |

+----------+--------+---------+

2 rows in set (0.00 sec)