增加

建表

mysql> create database test_exam; Query OK, 1 row affected (0.00 sec)  mysql> use test_exam; Database changed mysql> create table exam ( -> id int, -> name varchar(20), -> chinese decimal(3,1), -> math decimal(3,1), -> english decimal(3,1) -> ); Query OK, 0 rows affected (0.02 sec) 

多行数据 全列插入

mysql> insert into exam values -> (1, '刘备', 90.1, 70.3, 40.9), -> (2, '关羽', 90.1, 60.3, 30.8), -> (3, '大乔', 99, 70.3, 30.8), -> (4, '吕布', 80.5, 50.3, 70.2), -> (5, '貂蝉', 80.5, 87.6, 90.3), -> (6, '孙尚香', 99, 12.1, 10), -> (7, '小乔', 98, 99, 4.3), -> (8, '孙权', 85, 77, 87); Query OK, 8 rows affected (0.01 sec) Records: 8  Duplicates: 0 Warnings: 0 

多行数据 指定列插入

mysql> insert into exam (id, name) values (9, '孙策'), (10, '刘禅'); Query OK, 2 rows affected (0.01 sec) Records: 2  Duplicates: 0 Warnings: 0 

查询

全列查询

• 注意:这种方式只限测试方式查询,千万不能再生产环境上的服务器执行这样的sql

mysql> select * from exam; +------+-----------+---------+------+---------+ | id   | name      | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | 刘备      | 90.1 | 70.3 | 40.9 | | 2 | 关羽      | 90.1 | 60.3 | 30.8 | | 3 | 大乔      | 99.0 | 70.3 | 30.8 | | 4 | 吕布      | 80.5 | 50.3 | 70.2 | | 5 | 貂蝉      | 80.5 | 87.6 | 90.3 | | 6 | 孙尚香    | 99.0 | 12.1 | 10.0 | | 7 | 小乔      | 98.0 | 99.0 | 4.3 | | 8 | 孙权      | 85.0 | 77.0 | 87.0 | | 9 | 孙策      | NULL | NULL | NULL | | 10 | 刘禅      | NULL | NULL | NULL | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec) 

指定列查询

mysql> select id,name from exam; +------+-----------+ | id   | name      | +------+-----------+ | 1 | 刘备      | | 2 | 关羽      | | 3 | 大乔      | | 4 | 吕布      | | 5 | 貂蝉      | | 6 | 孙尚香    | | 7 | 小乔      | | 8 | 孙权      | | 9 | 孙策      | | 10 | 刘禅      | +------+-----------+ 10 rows in set (0.00 sec) 

查询字段为表达式

mysql> select id, name, english + 10 from exam; +------+-----------+--------------+ | id   | name      | english + 10 | +------+-----------+--------------+ | 1 | 刘备      | 50.9 | | 2 | 关羽      | 40.8 | | 3 | 大乔      | 40.8 | | 4 | 吕布      | 80.2 | | 5 | 貂蝉      | 100.3 | | 6 | 孙尚香    | 20.0 | | 7 | 小乔      | 14.3 | | 8 | 孙权      | 97.0 | | 9 | 孙策      | NULL | | 10 | 刘禅      | NULL | +------+-----------+--------------+ 10 rows in set (0.00 sec)  mysql> select id, name, chinese + math + english from exam; +------+-----------+--------------------------+ | id   | name      | chinese + math + english | +------+-----------+--------------------------+ | 1 | 刘备      | 201.3 | | 2 | 关羽      | 181.2 | | 3 | 大乔      | 200.1 | | 4 | 吕布      | 201.0 | | 5 | 貂蝉      | 258.4 | | 6 | 孙尚香    | 121.1 | | 7 | 小乔      | 201.3 | | 8 | 孙权      | 249.0 | | 9 | 孙策      | NULL | | 10 | 刘禅      | NULL | +------+-----------+--------------------------+ 10 rows in set (0.00 sec) 

查询字段 名字重定义

mysql> select id, name, chinese + math + english as totle from exam; +------+-----------+-------+ | id   | name      | totle | +------+-----------+-------+ | 1 | 刘备      | 201.3 | | 2 | 关羽      | 181.2 | | 3 | 大乔      | 200.1 | | 4 | 吕布      | 201.0 | | 5 | 貂蝉      | 258.4 | | 6 | 孙尚香    | 121.1 | | 7 | 小乔      | 201.3 | | 8 | 孙权      | 249.0 | | 9 | 孙策      | NULL | | 10 | 刘禅      | NULL | +------+-----------+-------+ 10 rows in set (0.00 sec) 

去重 distinct

• 使用去重distinct时,所有的列都必须放在distinct的后面

mysql> select distinct chinese from exam; +---------+ | chinese | +---------+ | 90.1 | | 99.0 | | 80.5 | | 98.0 | | 85.0 | | NULL | +---------+ 6 rows in set (0.01 sec) 

排序 order by

• asc为升序 desc为降序 什么都不加 默认asc

-- 查询同学及总分，由高到低 mysql> select id, name, chinese + math + english as totle from exam order by totle desc; +------+-----------+-------+ | id   | name      | totle | +------+-----------+-------+ | 5 | 貂蝉      | 258.4 | | 8 | 孙权      | 249.0 | | 1 | 刘备      | 201.3 | | 7 | 小乔      | 201.3 | | 4 | 吕布      | 201.0 | | 3 | 大乔      | 200.1 | | 2 | 关羽      | 181.2 | | 6 | 孙尚香    | 121.1 | | 9 | 孙策      | NULL | | 10 | 刘禅      | NULL | +------+-----------+-------+ 10 rows in set (0.00 sec) -- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示 mysql> select * from exam order by chinese desc, math desc, english desc; +------+-----------+---------+------+---------+ | id   | name      | chinese | math | english | +------+-----------+---------+------+---------+ | 3 | 大乔      | 99.0 | 70.3 | 30.8 | | 6 | 孙尚香    | 99.0 | 12.1 | 10.0 | | 7 | 小乔      | 98.0 | 99.0 | 4.3 | | 1 | 刘备      | 90.1 | 70.3 | 40.9 | | 2 | 关羽      | 90.1 | 60.3 | 30.8 | | 8 | 孙权      | 85.0 | 77.0 | 87.0 | | 5 | 貂蝉      | 80.5 | 87.6 | 90.3 | | 4 | 吕布      | 80.5 | 50.3 | 70.2 | | 9 | 孙策      | NULL | NULL | NULL | | 10 | 刘禅      | NULL | NULL | NULL | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec) 

条件查询

运算符

• >, >=, <, <= 大于，大于等于，小于，小于等于

• = 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL

• <=> 等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)

• !=, <> 不等于

• BETWEEN a0 AND a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)

• IN (option, ...) 如果是 option 中的任意一个，返回TRUE(1)

• IS NULL 是 NULL

• IS NOT NULL 不是 NULL

• LIKE模糊匹配 % 表示任意多个（包括 0 个）任意字符； _ 表示任意一个字符

• AND 多个条件必须都为 TRUE(1)，结果才是 TRUE(1)

• OR 任意一个条件为 TRUE(1), 结果为 TRUE(1)

• NOT 条件为 TRUE(1)，结果为 FALSE(0)

• 注意:

1. where条件可以使用表达式,但是不能使用别名
2. and 的优先级高于 or 在同时使用时,需要使用小括号()包裹优先执行的部分

比较查询

-- 查询英语不及格的同学及英语成绩 ( < 60 ) mysql> select id, name, english from exam where english < 60; +------+-----------+---------+ | id   | name      | english | +------+-----------+---------+ | 1 | 刘备      | 40.9 | | 2 | 关羽      | 30.8 | | 3 | 大乔      | 30.8 | | 6 | 孙尚香    | 10.0 | | 7 | 小乔      | 4.3 | +------+-----------+---------+ 5 rows in set (0.00 sec) -- 查询语文成绩好于英语成绩的同学 mysql> select id, name, chinese, english from exam where chinese > english; +------+-----------+---------+---------+ | id   | name      | chinese | english | +------+-----------+---------+---------+ | 1 | 刘备      | 90.1 | 40.9 | | 2 | 关羽      | 90.1 | 30.8 | | 3 | 大乔      | 99.0 | 30.8 | | 4 | 吕布      | 80.5 | 70.2 | | 6 | 孙尚香    | 99.0 | 10.0 | | 7 | 小乔      | 98.0 | 4.3 | +------+-----------+---------+---------+ 6 rows in set (0.00 sec) -- 查询总分在 200 分以下的同学 mysql> select id, name, chinese + math + english as totle from exam where chinese + math + english < 200; +------+-----------+-------+ | id   | name      | totle | +------+-----------+-------+ | 2 | 关羽      | 181.2 | | 6 | 孙尚香    | 121.1 | +------+-----------+-------+ 2 rows in set (0.00 sec) 

and与or

-- 查询语文成绩大于80分，且英语成绩大于80分的同学 mysql> select id, name, chinese, english from exam where chinese > 80 and english > 80; +------+--------+---------+---------+ | id   | name   | chinese | english | +------+--------+---------+---------+ | 5 | 貂蝉   | 80.5 | 90.3 | | 8 | 孙权   | 85.0 | 87.0 | +------+--------+---------+---------+ 2 rows in set (0.00 sec) -- 查询语文成绩大于80分，或英语成绩大于80分的同学 mysql> select id, name, chinese, english from exam where chinese > 80 or english > 80; +------+-----------+---------+---------+ | id   | name      | chinese | english | +------+-----------+---------+---------+ | 1 | 刘备      | 90.1 | 40.9 | | 2 | 关羽      | 90.1 | 30.8 | | 3 | 大乔      | 99.0 | 30.8 | | 4 | 吕布      | 80.5 | 70.2 | | 5 | 貂蝉      | 80.5 | 90.3 | | 6 | 孙尚香    | 99.0 | 10.0 | | 7 | 小乔      | 98.0 | 4.3 | | 8 | 孙权      | 85.0 | 87.0 | +------+-----------+---------+---------+ 8 rows in set (0.00 sec) 

范围查询

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩 mysql> select id, name, chinese from exam where chinese between 80 and 90; +------+--------+---------+ | id   | name   | chinese | +------+--------+---------+ | 4 | 吕布   | 80.5 | | 5 | 貂蝉   | 80.5 | | 8 | 孙权   | 85.0 | +------+--------+---------+ 3 rows in set (0.00 sec) 

in 查询

-- 查询数学成绩是 99.0 或者 60.3 或者 70.3 的同学及数学成绩 mysql> select name, math from exam where math in (99.0, 60.3, 70.3); +--------+------+ | name   | math | +--------+------+ | 刘备   | 70.3 | | 关羽   | 60.3 | | 大乔   | 70.3 | | 小乔   | 99.0 | +--------+------+ 4 rows in set (0.00 sec) 

模糊查询 like

-- % 匹配任意多个（包括 0 个）字符 mysql> select id, name from exam where name like '孙%'; +------+-----------+ | id   | name      | +------+-----------+ | 6 | 孙尚香    | | 8 | 孙权      | | 9 | 孙策      | +------+-----------+ 3 rows in set (0.00 sec) -- _ 匹配严格的一个任意字符 mysql> select id, name from exam where name like '孙__'; +------+-----------+ | id   | name      | +------+-----------+ | 6 | 孙尚香    | +------+-----------+ 1 row in set (0.00 sec)  mysql> select id, name from exam where name like '孙_'; +------+--------+ | id   | name   | +------+--------+ | 8 | 孙权   | | 9 | 孙策   | +------+--------+ 2 rows in set (0.00 sec) -- 语文成绩以9开头并且姓为孙 mysql> select id, name, chinese from exam where name like '孙%' and chinese like '9%'; +------+-----------+---------+ | id   | name      | chinese | +------+-----------+---------+ | 6 | 孙尚香    | 99.0 | +------+-----------+---------+ 1 row in set (0.00 sec) 

null查询

-- 查成绩为null的同学 mysql> select id, name, chinese + math + english as totle from exam where chinese + math + english <=> null; +------+--------+-------+ | id   | name   | totle | +------+--------+-------+ | 9 | 孙策   | NULL | | 10 | 刘禅   | NULL | +------+--------+-------+ 2 rows in set (0.00 sec) 

分页查询

-- 查询成绩前三 mysql> select * from exam order by chinese + math + english desc limit 3; +------+--------+---------+------+---------+ | id   | name   | chinese | math | english | +------+--------+---------+------+---------+ | 5 | 貂蝉   | 80.5 | 87.6 | 90.3 | | 8 | 孙权   | 85.0 | 77.0 | 87.0 | | 1 | 刘备   | 90.1 | 70.3 | 40.9 | +------+--------+---------+------+---------+ 3 rows in set (0.00 sec) -- 查询成绩4~6 -- offset 可以想成数组下标一样 mysql> select * from exam order by chinese + math + english desc limit 3 offset 3; +------+--------+---------+------+---------+ | id   | name   | chinese | math | english | +------+--------+---------+------+---------+ | 7 | 小乔   | 98.0 | 99.0 | 4.3 | | 4 | 吕布   | 80.5 | 50.3 | 70.2 | | 3 | 大乔   | 99.0 | 70.3 | 30.8 | +------+--------+---------+------+---------+ 3 rows in set (0.00 sec) -- 查询  limit  或者 offset  过大也不会报错 mysql> select * from exam order by chinese + math + english desc limit 300 offset 300; Empty set (0.00 sec) 

修改

-- 将孙权同学的数学成绩变更为 80 分 -- Rows matched 表示where涉及到几行  Changed: 1表示改了几行 Warnings: 0表示出错多少 mysql> update exam set math = 80 where name = '孙权'; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1 Warnings: 0 -- 将总成绩倒数前三的 3 位同学的数学成绩加上 10 分 mysql> update exam set math = math + 10 order by chinese + math + english asc limit 3; Query OK, 1 row affected (0.00 sec) Rows matched: 3  Changed: 1 Warnings: 0 

删除

-- 删除 刘备的成绩 mysql> delete from exam where name = '刘备'; Query OK, 1 row affected (0.01 sec) -- 删除整张表的数据 mysql> delete from test_exam; Query OK, 0 rows affected (0.00 sec)