目录
一、插入数据
1、单行数据 + 全列插入
2、多行数据 + 指定列插入
3、插入否则更新
4、替换
二、读取
1、select列
2、where条件
3、结果排序
4、筛选分页结果
一、插入数据
首先创建一张表
mysql> CREATE TABLE students(
-> id int unsigned primary key auto_increment,
-> sn int not null unique comment '学号',
-> name varchar(20) not null,
-> qq varchar(20) unique
-> );
1、单行数据 + 全列插入
插入两条记录,value_list 数量必须和定义表的列的数量及顺序一致
mysql> insert into students values(100,10000,'唐三',null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(101,10001,'唐三藏','12345');
Query OK, 1 row affected (0.01 sec)
2、多行数据 + 指定列插入
mysql> insert into students (id,sn,name) values (102,10002,'孙悟空'),
(103,10003,'孙仲谋');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
3、插入否则更新
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败
mysql> insert into students (id,sn,name) values (100,10010,'唐唐');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
mysql> insert into students (id,sn,name) values (104,10001,'曹阿瞒');
ERROR 1062 (23000): Duplicate entry '10001' for key 'sn'
此时我们可以选择性的进行更新操作,
语法:
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
//ON DUPLICATE KEY 当发生重复key的时候
mysql> insert into students (id,sn,name) values (100,10010,'唐唐')
on duplicate key update sn=10010,name='唐唐';
Query OK, 2 rows affected (0.00 sec)
可以通过 MySQL 函数获取受到影响的数据行数
4、替换
mysql> replace into students (sn,name) values(10010,'曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
二、读取
创建表
mysql> CREATE TABLE exam_result (
-> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL COMMENT '同学姓名',
-> chinese float DEFAULT 0.0 COMMENT '语文成绩',
-> math float DEFAULT 0.0 COMMENT '数学成绩',
-> english float DEFAULT 0.0 COMMENT '英语成绩'
-> );
Query OK, 0 rows affected (0.03 sec)
插入数据
mysql> insert into exam_result(name,chinese,math,english) values
-> ('凯文',67,98,56),
-> ('琪亚娜',87,78,77),
-> ('芽衣',88,98,90),
-> ('板鸭',82,84,67),
-> ('瓦尔特',55,85,45),
-> ('温蒂',70,73,78),
-> ('劫哥',75,65,30);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
1、select列
(1)全列查询
说明:
SELECT * FROM exam_result;
(2)指定列查询
select id,name from exam_result;
(3)查询字段为表达式
select id,name, 100 from exam_result;
select id,name, english+10 from exam_result;
select id,name,chinese+math+english from exam_result;
(4)为查询结果指定别名
SELECT column [AS] alias_name [...] FROM table_name;
mysql> select id,name 名字,chinese+math+english 总分 from exam_result;
5(结果去重)
mysql> select distinct math from exam_result;
2、where条件
(1)英语不及格的同学及英语成绩
mysql> select name,english from exam_result where english<60;
(2)语文成绩在 [80, 90] 分的同学及语文成绩
mysql> select name,chinese from exam_result where chinese>=80 and chinese<=90;
mysql> select name,chinese from exam_result where chinese between 80 and 90;
(3) 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
mysql> select name,math from exam_result where
-> math=58
-> or math=59
-> or math=98
-> or math=99;
mysql> select name,math from exam_result where math in(58,59,98,99);
(4)找出琪某某和凯某
mysql> select name,math from exam_result where name like '琪%';
% 匹配任意多个(包括0个)任意字符
mysql> select name,math from exam_result where name like '凯_';
_ 匹配严格的一个任意字符
(5)找出语文成绩好于英语成绩的同学
mysql> select name,chinese,english from exam_result where chinese>english;
(6)总分在200以下的同学
mysql> select name, (math+chinese+english) from exam_result
where (math+chinese+english)<200;
mysql> select name, (math+chinese+english) 总分 from exam_result where (math+chinese+english)<200;
select name, (math+chinese+english) 总分 from exam_result where 总分<200;
ERROR 1054 (42S22): Unknown column '总分' in 'where clause'
分析:在 SQL 语言中,where 和 select 的执行顺序是这样的:
- 首先,根据 from 子句确定要查询的表或视图。
- 其次,根据 where 子句过滤出满足条件的记录。
- 最后,根据 select 子句选择要显示的字段和计算的结果。
因此,where 子句是在 select 子句之前执行的,这意味着 where 子句中不能使用 select 子句中定义的别名或计算字段。
(7)语文成绩好于80,并且不是琪某某和芽某
mysql> select name,chinese from exam_result where chinese>80
and name not like '琪%' and name not like '芽%';
(8)温某同学,否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select name,math,chinese,english, math+english+chinese 总分
-> from exam_result where
-> name like '温_' or(
-> chinese+math+english>200 and chinese<math and english>80
-> );
(9)null的查询
mysql> select name,qq from students where qq is not null;
mysql> SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
1 row in set (0.00 sec)
mysql> select null<=>null,null<=>1,null<=>0;
1 row in set (0.00 sec)
3、结果排序
语法:
ASC 为升序(从小到大)DESC 为降序(从大到小)默认为 ASCSELECT ... FROM table_name [WHERE ...]ORDER BY column [ASC|DESC], [...];
(1)同学及数学成绩,按数学成绩升序显示
mysql> select name ,math from exam_result order by math asc;
(2)查询同学各门成绩,依次按数学降序,英语升序,语文升序的方式显示
多字段排序,排序优先级随书写顺序
mysql> select name,math,english,chinese from exam_result
-> order by math desc,english,chinese;
(3)查询同学及总分,由高到低
mysql> select name ,math+english+chinese from exam_result
-> order by math+english+chinese desc;
(4)查询姓琪亚娜,芽衣和板鸭的数学成绩,结果按数学成绩由高到低显示
mysql> select name,math from exam_result where name like '琪%'
or name like '芽%' or name like '板%' order by math desc;
4、筛选分页结果
-- 起始下标为 0-- 从 0 开始,筛选 n 条结果SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;-- 从 s 开始,筛选 n 条结果SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
mysql> select id,name,math+english+chinese 总分
from exam_result order by id limit 3 offset 0;
mysql> select id,name,math+english+chinese 总分
from exam_result order by id limit 3 offset 3;
文章来源:https://www.uudwc.com/A/DNLyx/
mysql> select id,name,math+english+chinese 总分
from exam_result order by id limit 3 offset 6;
文章来源地址https://www.uudwc.com/A/DNLyx/