MySQL-DML基础

1、插入数据

查看表结构

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(64) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

插入数据

# 插入一行数据
insert into t1 values(1,'zs');
# 插入多行数据
insert into t1 values(2,'ls'),(3,'ha'),(4,'nh');
# 指定字段插入数据
insert into t1(id,name) value(5,'wxh');

2、更新数据

不加where条件指定某行数据更新，将更新所有行的字段值

update t1 set name='ww';

为了避免这种误操作，MySQL提供sql_safe_updates参数对上面更新语句抛出异常，需要开启该参数。

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+
mysql> set global sql_safe_updates=ON;

再次执行该更新语句，则抛出异常。update语句要求带有where子句，并且使用key字段

mysql> update t1 set name='ww';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

更新数据

mysql> alter table t1 add index i_id(id);
mysql> update t1 set name='zs' where id=1;

3、删除数据，也是更新数据的一种，所以也受sql_safe_updates参数影响。

delete from t1 where id=1;

在执行速度上：
truncate table > drop table > delete from table
delete： 是逐行打标记
drop: 表定义删除, 删除ibd文件(操作系统rm)
trucate: 保留表定义，清空表空间

4、查询数据

4.1 查询参数值

mysql> select @@datadir;
+------------------+
| @@datadir        |
+------------------+
| /opt/mysql/data/ |
+------------------+
mysql> select @@socket;
mysql> select @@server_id;

4.2 查询函数

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-01-12 21:40:57 |
+---------------------+

4.3 单表常用查询

# 查询表中所有数据
select * from city;
# 查询指定字段的数据
select name,CountryCode from city;
# 查询满足一定条件的数据
select * from city where CountryCode='CHN';
select * from city where population<100;
# between .... and 满足一段数值范围
select * from city where population between 100 and 100000;
# in 相关于or,满足任一条件结果都显示
select * from city where countrycode in ('CHN','USA');
# and 同时满足多个条件
select * from city where countrycode='CHN' and district='guangdong';
# or 满足任一条件结果都显示
select * from city where countrycode='CHN' or countrycode='USA';
# like 模糊查询，只针对字符串列，% 匹配任意字符
select * from city where countrycode like 'CH%';

4.4 分组查询，常配合聚合函数使用

select countrycode,count(*) from city group by countrycode;

sql_mode=only_full_group_by限制，select列中包含不是group by字段或不是聚合函数字段，则触发。

mysql> select countrycode,count(*),name from city group by countrycode;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# 调整后
mysql> select countrycode,count(*),group_concat(name) from city group by countrycode limit 3;
+-------------+----------+--------------------------------------+
| countrycode | count(*) | group_concat(name)                   |
+-------------+----------+--------------------------------------+
| ABW         |        1 | Oranjestad                           |
| AFG         |        4 | Kabul,Qandahar,Herat,Mazar-e-Sharif  |
| AGO         |        5 | Luanda,Huambo,Lobito,Benguela,Namibe |
+-------------+----------+--------------------------------------+
# 如果sql_mode去掉该限制，语句可以执行，name列只会显示其中该分组中的第一个值
mysql> select countrycode,count(*),name from city group by countrycode limit 3;
+-------------+----------+------------+
| countrycode | count(*) | name       |
+-------------+----------+------------+
| ABW         |        1 | Oranjestad |
| AFG         |        4 | Kabul      |
| AGO         |        5 | Luanda     |
+-------------+----------+------------+

4.5 having使用，类似于where，需要在group by聚合函数后过滤

select countrycode,count(*) from city group by countrycode having count(*)>200;

4.6 order by排序，默认升序，desc倒序

select countrycode,count(*) from city group by countrycode having count(*)>200 order by count(*) desc;

4.6 limit使用，limit m,n，跳过m行，显示n行

select countrycode,count(*) from city group by countrycode having count(*)>200 order by count(*) desc limit 2,3;