MySQL增删查改练习(基础)

非淡泊无以明志，非宁静无以致远。这篇文章主要讲述MySQL增删查改练习(基础)相关的知识，希望能为你提供帮助。
@TOC
增加建表

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: 8Duplicates: 0Warnings: 0

多行数据指定列插入

mysql> insert into exam (id, name) values (9, 孙策), (10, 刘禅); Query OK, 2 rows affected (0.01 sec) Records: 2Duplicates: 0Warnings: 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增删查改练习(基础)】

文章图片

查询字段为表达式

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)

条件查询运算符
在 selcet 的后面加上一个where 字句，后面跟上一个具体的筛选条件
语法; select 列名 from 表名 where 条件;

where条件可以使用表达式,但是不能使用别名
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
此处这里的孙%就能匹配到任意孙开头的字符串,其他的类型于这样的名字也能匹配到.
比如: 孙 ,或者 孙sadsadasda
但是类似于这种就匹配不到:aaa孙aaa,bbb孙

-- % 匹配任意多个（包括 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 是不能用 = 的, = 是无法比较的,结果是假,如果要比较空只能用 < => 比较
或者这样写也可以

文章图片

-- 查成绩为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)

分页查询

-- 查询成绩前三 0-3 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 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: 1Changed: 1Warnings: 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: 3Changed: 1Warnings: 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)