MySQL基本查询

插入替换

多行数据 + 指定列插入

INSERT INTO students (id, sn, name) VALUES (102, 20001, '曹孟德'), (103, 20002, '孙仲谋');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0  Warnings: 0
-- 查看插入结果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn   | name     | qq   |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏     | NULL |
| 101 | 10001 | 孙悟空     | 11111 |
| 102 | 20001 | 曹孟德     | NULL |
| 103 | 20002 | 孙仲谋     | NULL |
+-----+-------+-----------+-------+

插入否则更新

INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
Query OK, 2 rows affected (0.47 sec)
-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数

替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

表为空时replace可以当insert使用。

Retrieve-检索（储存于计算机的信息）

SELECT [DISTINCT] {* | {column [, column] ...} [FROM table_name] [WHERE ...] [ORDER BY column [ASC | DESC], ...] LIMIT ...

mysql> select name from exam_result;
+-----------+
| name |
+-----------+
| 唐三藏 |
| 孙悟空 |
| 猪悟能 |
| 曹孟德 |
| 刘玄德 |
| 孙权 |
| 宋公明 |
+-----------+

查询一列。

select 求和

mysql> select name, math,math+chinese+english from exam_result;
+-----------+------+----------------------+
| name | math | math+chinese+english |
+-----------+------+----------------------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+----------------------+

select 重命名

mysql> select name, math,math+chinese+english as total from exam_result;

as可带/可不带
+-----------+------+-------+
| name | math | total |
+-----------+------+-------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+-------+

select 去重

mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+

update

mysql> update exam_result set math=80 where name = '孙悟空';

mysql> update exam_result set math=80,chinese=70 where name = '曹孟德';

降序排序

mysql> select name ,math+chinese+english total from exam_result order by total desc;

mysql> update exam_result set math=math+30 order by chinese+english+math asc limit 3;

asc/desc

MySQL数据排序asc、desc-CSDN博客https://blog.csdn.net/qq_37923253/article/details/79688313

delete

delete from exam_result where name='孙悟空';

-- 删除整表数据 DELETE FROM for_delete; 
Query OK, 3 rows affected (0.00 sec) -- 查看删除结果 
SELECT * FROM for_delete; 
Empty set (0.00 sec)

-- 查看表结构，会有 AUTO_INCREMENT=n 项 
SHOW CREATE TABLE for_delete\G 
*************************** 1. row *************************** Table: for_delete 
Create Table: CREATE TABLE `for_delete` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)

ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

截断表

TRUNCATE [TABLE] table_name

-- 查看表结构，会有 AUTO_INCREMENT=2 项 
SHOW CREATE TABLE for_truncate\G 
*************************** 1. row *************************** Table: for_truncate 
Create Table: CREATE TABLE `for_truncate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

truncate后，AUTO_INCREMENT会被设为默认值。不会将操作记录在日志（bin log）中。

bin log用于记录历史SQL语句及数据本身，这是一种数据持久化的方式。

-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作 
TRUNCATE for_truncate; 
Query OK, 0 rows affected (0.10 sec) -- 查看删除结果 
SELECT * FROM for_truncate;

AUTO_INCREMENT=2

rename

mysql> rename table_duplicate_table to old_duplicate_table;

删除表中的重复记录，重复的数据只能有一份

将一个表的结构拷贝给另一个表

create table no_duplicate_table like duplicate_table;

insert into no_duplicate select distinct * from duplicate_table;

distinct 可用于去重操作。

count

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

mysql> select COUNT(math) as res from exam_result;
+-----+
| res |
+-----+
|   6 |
+-----+

mysql> select sum(english)/count(*) from exam_result;

mysql> select * from exam_result where english<60;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 5 | 刘玄德 | 55 | 115 | 45 |
| 7 | 宋公明 | 75 | 95 | 30 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)

avg

mysql> select avg(math) from exam_result;
+-------------------+
| avg(math) |
+-------------------+
| 98.16666666666667 |
+-------------------+

max

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
| 90 |
+--------------+