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 |
+--------------+