手机创建网站的软件,站长统计app软件,山西做网站建设的平台,网站定制建设哪里好文章目录 一、创建操作1.1 单行插入1.2 多行插入1.3 插入否则替换更新1.4 替换replace 二、查询操作2.1 select查询2.2 where条件判断2.3 order by排序2.4 limit筛选分页结果 三、更新操作四、删除操作4.1 删除一列4.2 删除整张表数据 五、插入查询结果 CRUD : Create(创建), R… 文章目录 一、创建操作1.1 单行插入1.2 多行插入1.3 插入否则替换更新1.4 替换replace 二、查询操作2.1 select查询2.2 where条件判断2.3 order by排序2.4 limit筛选分页结果 三、更新操作四、删除操作4.1 删除一列4.2 删除整张表数据 五、插入查询结果 CRUD : Create(创建), Retrieve(读取)Update(更新)Delete删除
先创建一张表
mysql create table if not exists stu(- id int unsigned primary key auto_increment,- sn int unsigned unique key comment 学号,- name varchar(20) not null,- qq varchar(20) unique key- );一、创建操作
1.1 单行插入
指定列单行插入
mysql insert into stu (sn, name, qq) values(123, 张三, 1161);
Query OK, 1 row affected (0.01 sec)单行全列插入
mysql insert into stu values(2, 128, 李四, 7077);
Query OK, 1 row affected (0.00 sec)into 可以省略。 mysql select * from stu;
------------------------
| id | sn | name | qq |
------------------------
| 1 | 123 | 张三 | 1161 |
| 2 | 128 | 李四 | 7077 |
------------------------
2 rows in set (0.00 sec)1.2 多行插入
可以直接一行全列插入。
mysql insert into stu values(10, 132, 王五, 2498),(11, 147, 赵六, 8088);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select * from stu;
------------------------
| id | sn | name | qq |
------------------------
| 1 | 123 | 张三 | 1161 |
| 2 | 128 | 李四 | 7077 |
| 10 | 132 | 王五 | 2498 |
| 11 | 147 | 赵六 | 8088 |
------------------------
4 rows in set (0.00 sec)也可以指定行插入一列
mysql insert into stu (sn, name, qq) values(155, 刘备, 5279),(452, 关羽, 7892);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: mysql select * from stu;
------------------------
| id | sn | name | qq |
------------------------
| 1 | 123 | 张三 | 1161 |
| 2 | 128 | 李四 | 7077 |
| 10 | 132 | 王五 | 2498 |
| 11 | 147 | 赵六 | 8088 |
| 14 | 155 | 刘备 | 5279 |
| 15 | 452 | 关羽 | 7892 |
------------------------
6 rows in set (0.00 sec)1.3 插入否则替换更新
对数据进行插入首先数据要合法相关字段没有被唯一键、主键、外键等进行约束其次如果插入数据的id已存在将会更新sn、name和qq而不是插入失败。
mysql insert into stu values (15, 452, 张飞, 7892) on duplicate key update sn452, name张飞, qq7892;
Query OK, 2 rows affected (0.00 sec)mysql select * from stu;
------------------------
| id | sn | name | qq |
------------------------
| 1 | 123 | 张三 | 1161 |
| 2 | 128 | 李四 | 7077 |
| 10 | 132 | 王五 | 2498 |
| 11 | 147 | 赵六 | 8088 |
| 14 | 155 | 刘备 | 5279 |
| 15 | 452 | 张飞 | 7892 |
------------------------
6 rows in set (0.00 sec)1.4 替换replace
主键 或者 唯一键 没有冲突则直接插入 主键 或者 唯一键 如果冲突则删除后再插入。
mysql select * from stu;
------------------------
| id | sn | name | qq |
------------------------
| 1 | 123 | 张三 | 1161 |
| 2 | 128 | 李四 | 7077 |
| 10 | 132 | 王五 | 2498 |
| 11 | 147 | 赵六 | 8088 |
| 14 | 155 | 刘备 | 5279 |
| 15 | 452 | 张飞 | 7892 |
------------------------
6 rows in set (0.00 sec)mysql replace into stu (sn, name, qq) values (258, 曹操, 5687);
Query OK, 1 row affected (0.01 sec)mysql select * from stu;
------------------------
| id | sn | name | qq |
------------------------
| 1 | 123 | 张三 | 1161 |
| 2 | 128 | 李四 | 7077 |
| 10 | 132 | 王五 | 2498 |
| 11 | 147 | 赵六 | 8088 |
| 14 | 155 | 刘备 | 5279 |
| 15 | 452 | 张飞 | 7892 |
| 16 | 258 | 曹操 | 5687 |
------------------------
7 rows in set (0.00 sec)通过id也可以看出这行数据是先删除在插入的。
二、查询操作
先创建一个期末成绩表
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: 02.1 select查询
全列查询
mysql select * from exam_result;
------------------------------------
| id | name | chinese | math | english |
------------------------------------
| 1 | 张三 | 67 | 98 | 56 |
| 2 | 李四 | 87 | 78 | 77 |
| 3 | 王五 | 88 | 98 | 90 |
| 4 | 赵六 | 82 | 84 | 67 |
| 5 | 田七 | 55 | 85 | 45 |
| 6 | 孙八 | 70 | 73 | 78 |
| 7 | 周九 | 75 | 65 | 30 |
------------------------------------
7 rows in set (0.00 sec)通常情况下不建议使用 * 进行全列查询 1、查询的列越多意味着需要传输的数据量越大 2、可能会影响到索引的使用。 指定列查询
mysql select name from exam_result;
--------
| name |
--------
| 张三 |
| 李四 |
| 王五 |
| 赵六 |
| 田七 |
| 孙八 |
| 周九 |
--------
7 rows in set (0.00 sec)mysql select name,english from exam_result;
-----------------
| name | english |
-----------------
| 张三 | 56 |
| 李四 | 77 |
| 王五 | 90 |
| 赵六 | 67 |
| 田七 | 45 |
| 孙八 | 78 |
| 周九 | 30 |
-----------------
7 rows in set (0.00 sec)查询字段为表达式
select可以计算表达式
mysql select 11;
-----
| 11 |
-----
| 2 |
-----
1 row in set (0.00 sec)所以我们可以计算总分
mysql select name, (chinese math english) from exam_result;
------------------------------------
| name | (chinese math english) |
------------------------------------
| 张三 | 221 |
| 李四 | 242 |
| 王五 | 276 |
| 赵六 | 233 |
| 田七 | 185 |
| 孙八 | 221 |
| 周九 | 170 |
------------------------------------
7 rows in set (0.00 sec)#觉得计算式太长可以用as给它重命名为total
mysql select name, (chinese math english) as total from exam_result;
---------------
| name | total |
---------------
| 张三 | 221 |
| 李四 | 242 |
| 王五 | 276 |
| 赵六 | 233 |
| 田七 | 185 |
| 孙八 | 221 |
| 周九 | 170 |
---------------
7 rows in set (0.00 sec)# as也可以省略
mysql select name 姓名, (chinese math english) 总分 from exam_result;
----------------
| 姓名 | 总分 |
----------------
| 张三 | 221 |
| 李四 | 242 |
| 王五 | 276 |
| 赵六 | 233 |
| 田七 | 185 |
| 孙八 | 221 |
| 周九 | 170 |
----------------
7 rows in set (0.00 sec)筛选筛选结果去重distinct
mysql select distinct math from exam_result;
------
| math |
------
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
------
6 rows in set (0.00 sec)2.2 where条件判断
比较运算符
运算符 说明
, , , 大于大于等于小于小于等于等于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)演练
找到英语小于60分的同学及其英语成绩
mysql select name, english from exam_result where english60;
-----------------
| name | english |
-----------------
| 张三 | 56 |
| 田七 | 45 |
| 周九 | 30 |
-----------------
3 rows in set (0.00 sec)找到语文成绩在 [80, 90] 分的同学及其语文成绩
# 和
mysql select name, chinese from exam_result where chinese80 and chinese90;
-----------------
| name | chinese |
-----------------
| 李四 | 87 |
| 王五 | 88 |
| 赵六 | 82 |
-----------------
3 rows in set (0.00 sec)# between a0 and a1
mysql select name, chinese from exam_result where chinese between 80 and 90;
-----------------
| name | chinese |
-----------------
| 李四 | 87 |
| 王五 | 88 |
| 赵六 | 82 |
-----------------
3 rows in set (0.00 sec)数学成绩是 98 或者 99 分的同学及数学成绩
# 多个or
mysql select name, math from exam_result where math98 or math99;
--------------
| name | math |
--------------
| 张三 | 98 |
| 王五 | 98 |
--------------
2 rows in set (0.00 sec)# in关键字
mysql select name, math from exam_result where math in (98, 99);
--------------
| name | math |
--------------
| 张三 | 98 |
| 王五 | 98 |
--------------
2 rows in set (0.00 sec)筛选出姓张的以及张X同学模糊匹配
先插入一行数据
mysql insert into exam_result (name, chinese, math, english) values (张翼德, 45, 98, 66);
Query OK, 1 row affected (0.01 sec)先找出所有姓张的同学
# 张%找到所有张姓同学%代表后面模糊匹配0-n个字符
mysql select name from exam_result where name like 张%;
-----------
| name |
-----------
| 张三 |
| 张翼德 |
-----------
2 rows in set (0.00 sec)接下来找到叫张某的同学
# _代表模糊匹配1个字符
mysql select name from exam_result where name like 张_;
--------
| name |
--------
| 张三 |
--------
1 row in set (0.00 sec)找到语文成绩高于英语成绩的同学
mysql select name, chinese, english from exam_result where englishchinese;
--------------------------
| name | chinese | english |
--------------------------
| 张三 | 67 | 56 |
| 李四 | 87 | 77 |
| 赵六 | 82 | 67 |
| 田七 | 55 | 45 |
| 周九 | 75 | 30 |
--------------------------
5 rows in set (0.00 sec)找到总分在200以下的同学
先看错误用法
mysql select name, chinesemathenglish total from exam_result where total 200;
ERROR 1054 (42S22): Unknown column total in where clause这主要是执行顺序的原因 1️⃣ 执行from 2️⃣ 执行where字句 3️⃣ 筛选要打印的信息
这里执行where的时候发现没有total别名所以会出错。
所以后边不能用别名
mysql select name, chinesemathenglish total from exam_result where chinesemathenglish 200;
---------------
| name | total |
---------------
| 田七 | 185 |
| 周九 | 170 |
---------------
2 rows in set (0.00 sec)找到英语成绩 80 并且不姓李的同学
mysql select name, english from exam_result where english80 and name!李%;
-----------------
| name | english |
-----------------
| 王五 | 90 |
-----------------
1 row in set (0.00 sec)NULL 的查询
mysql select * from t10;
----------------------
| name | age | gender |
----------------------
| 李四 | 20 | 男 |
| 李四 | NULL | 男 |
----------------------
2 rows in set (0.00 sec)找到age是null的人
mysql select name, age from t10 where agenull;
--------------
| name | age |
--------------
| 李四 | NULL |
--------------
1 row in set (0.00 sec)找到age不为null的人
mysql select name, age from t10 where age is not null;
--------------
| name | age |
--------------
| 李四 | 20 |
--------------
1 row in set (0.00 sec)2.3 order by排序
对于没有 order by 子句的查询返回的顺序是未定义的永远不要依赖这个顺序。
ASC 为升序从小到大
DESC 为降序从大到小
默认为 ASC
NULL 视为比任何值都小同学及数学成绩按数学成绩升序/降序显示
# 升序 ASC
mysql select name, math from exam_result order by math ASC;
-----------------
| name | math |
-----------------
| 周九 | 65 |
| 孙八 | 73 |
| 李四 | 78 |
| 赵六 | 84 |
| 田七 | 85 |
| 张三 | 98 |
| 王五 | 98 |
| 张翼德 | 98 |
-----------------
8 rows in set (0.00 sec)# 降序 DESC
mysql select name, math from exam_result order by math DESC;
-----------------
| name | math |
-----------------
| 张三 | 98 |
| 王五 | 98 |
| 张翼德 | 98 |
| 田七 | 85 |
| 赵六 | 84 |
| 李四 | 78 |
| 孙八 | 73 |
| 周九 | 65 |
-----------------
8 rows in set (0.00 sec)查询同学各门成绩依次按 数学降序英语降序语文升序的方式显示
意思是如果数学成绩是相等的话就按照英语降序的顺序排语文同理。
mysql select name, math, english, chinese from exam_result order by math desc, english desc, chinese asc;
-----------------------------------
| name | math | english | chinese |
-----------------------------------
| 王五 | 98 | 90 | 88 |
| 张翼德 | 98 | 66 | 45 |
| 张三 | 98 | 56 | 67 |
| 田七 | 85 | 45 | 55 |
| 赵六 | 84 | 67 | 82 |
| 李四 | 78 | 77 | 87 |
| 孙八 | 73 | 78 | 70 |
| 周九 | 65 | 30 | 75 |
-----------------------------------
8 rows in set (0.00 sec)查询同学的总分并且降序排序
mysql select name, chinesemathenglish as total from exam_result order by total desc;
------------------
| name | total |
------------------
| 王五 | 276 |
| 李四 | 242 |
| 赵六 | 233 |
| 张三 | 221 |
| 孙八 | 221 |
| 张翼德 | 209 |
| 田七 | 185 |
| 周九 | 170 |
------------------
8 rows in set (0.00 sec)上面的where语句是不能用别名的。但在这里可以使用别名还是因为顺序的问题。 第一优先级明确找的是哪张表exam_result 第二优先级where子句 第三优先级chinesemathenglish 总分 第四优先级order by 总分 desc要有合适的数据再进行排序 第五优先级limit数据准备好了才进行显示 查询姓张的同学或者姓李的同学数学成绩结果按数学成绩由高到低显示
mysql select name, math from exam_result where name like 张% or name like 李% order by math desc;
-----------------
| name | math |
-----------------
| 张三 | 98 |
| 张翼德 | 98 |
| 李四 | 78 |
-----------------
3 rows in set (0.00 sec)2.4 limit筛选分页结果
mysql select * from exam_result;
---------------------------------------
| id | name | chinese | math | english |
---------------------------------------
| 1 | 张三 | 67 | 98 | 56 |
| 2 | 李四 | 87 | 78 | 77 |
| 3 | 王五 | 88 | 98 | 90 |
| 4 | 赵六 | 82 | 84 | 67 |
| 5 | 田七 | 55 | 85 | 45 |
| 6 | 孙八 | 70 | 73 | 78 |
| 7 | 周九 | 75 | 65 | 30 |
| 8 | 张翼德 | 45 | 98 | 66 |
---------------------------------------
8 rows in set (0.00 sec)这样显示数据太多了。 我们想要看表的前五行
mysql select * from exam_result limit 5;
------------------------------------
| id | name | chinese | math | english |
------------------------------------
| 1 | 张三 | 67 | 98 | 56 |
| 2 | 李四 | 87 | 78 | 77 |
| 3 | 王五 | 88 | 98 | 90 |
| 4 | 赵六 | 82 | 84 | 67 |
| 5 | 田七 | 55 | 85 | 45 |
------------------------------------
5 rows in set (0.00 sec)当然也可以从中间开始截取
mysql select * from exam_result limit 1,3;
------------------------------------
| id | name | chinese | math | english |
------------------------------------
| 2 | 李四 | 87 | 78 | 77 |
| 3 | 王五 | 88 | 98 | 90 |
| 4 | 赵六 | 82 | 84 | 67 |
------------------------------------
3 rows in set (0.00 sec)# limit s offset n其中s代表步长n代表下标
mysql select * from exam_result limit 3 offset 1;
------------------------------------
| id | name | chinese | math | english |
------------------------------------
| 2 | 李四 | 87 | 78 | 77 |
| 3 | 王五 | 88 | 98 | 90 |
| 4 | 赵六 | 82 | 84 | 67 |
------------------------------------
3 rows in set (0.00 sec)这里要注意limit的本质并不是条件筛选而是显示。
三、更新操作
直接举例子
mysql select * from exam_result;
---------------------------------------
| id | name | chinese | math | english |
---------------------------------------
| 1 | 张三 | 67 | 98 | 56 |
| 2 | 李四 | 87 | 78 | 77 |
| 3 | 王五 | 88 | 98 | 90 |
| 4 | 赵六 | 82 | 84 | 67 |
| 5 | 田七 | 55 | 85 | 45 |
| 6 | 孙八 | 70 | 73 | 78 |
| 7 | 周九 | 75 | 65 | 30 |
| 8 | 张翼德 | 45 | 98 | 66 |
---------------------------------------
8 rows in set (0.00 sec)把王五的数学成绩改成80
mysql update exam_result set math80 where name王五;
Query OK, 1 row affected (0.01 sec)mysql select name, math from exam_result where name王五;
--------------
| name | math |
--------------
| 王五 | 80 |
--------------
1 row in set (0.00 sec)把李四的语文成绩改成60数学成绩改成80
mysql update exam_result set chinese60, math80 where name李四;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from exam_result where name李四;
------------------------------------
| id | name | chinese | math | english |
------------------------------------
| 2 | 李四 | 60 | 80 | 77 |
------------------------------------
1 row in set (0.00 sec)将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql update exam_result set mathmath30 order by chinesemathenglish asc limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0将所有人的语文成绩翻两倍
mysql update exam_result set chinesechinese*2;
Query OK, 8 rows affected (0.02 sec)
Rows matched: 8 Changed: 8 Warnings: 0mysql select * from exam_result;
---------------------------------------
| id | name | chinese | math | english |
---------------------------------------
| 1 | 张三 | 134 | 98 | 56 |
| 2 | 李四 | 120 | 80 | 77 |
| 3 | 王五 | 176 | 80 | 90 |
| 4 | 赵六 | 164 | 84 | 67 |
| 5 | 田七 | 110 | 115 | 45 |
| 6 | 孙八 | 140 | 73 | 78 |
| 7 | 周九 | 150 | 95 | 30 |
| 8 | 张翼德 | 90 | 128 | 66 |
---------------------------------------
8 rows in set (0.00 sec)没有where子句直接更新全表需谨慎update的搞错了危害不亚于delete。
四、删除操作
4.1 删除一列
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]删除周九的成绩
mysql select * from exam_result where name周九;
------------------------------------
| id | name | chinese | math | english |
------------------------------------
| 7 | 周九 | 150 | 95 | 30 |
------------------------------------
1 row in set (0.01 sec)mysql delete from exam_result where name周九;
Query OK, 1 row affected (0.00 sec)mysql select * from exam_result where name周九;
Empty set (0.00 sec)删除班级分数最高的成绩
mysql delete from exam_result order by chinesemathenglish desc limit 1;
Query OK, 1 row affected (0.01 sec)4.2 删除整张表数据
语法
delete from 表名这样只是情况了表的数据但是表还在。如果存在自增长约束再插入数据就会按照上一个自增最大值1。
截断表
语法
truncate [table] 表名只能对整表操作不能像 DELETE 一样针对部分数据操作 实际上 MySQL 不对数据操作所以比 DELETE 更快但是TRUNCATE在删除数据的时候并不经过真正的事物所以无法回滚 会重置 AUTO_INCREMENT 项 五、插入查询结果
删除表中的的重复记录重复的数据只能有一份
# 创建表
mysql create table duplicate_table(- id int,- name varchar(20));
Query OK, 0 rows affected (0.04 sec)# 插入数据
mysql insert into duplicate_table values- (100, a),- (100, a),- (200, b),- (200, b),- (200, b),- (300, c);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0查看表
mysql select * from duplicate_table;
------------
| id | name |
------------
| 100 | a |
| 100 | a |
| 200 | b |
| 200 | b |
| 200 | b |
| 300 | c |
------------
6 rows in set (0.00 sec)去重思路
1.创建一张属性和原表一样的空表
# 创建一张属性和duplicate_table一样的表no_duplicate_table空表
mysql create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.03 sec)查看两张表得属性发现是一样的
mysql desc no_duplicate_table;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)mysql desc duplicate_table;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)2.利用insert distinct筛选出原表中去重后的数据
# 对原表select出来的结果insert进新表中
mysql insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select * from no_duplicate_table;
------------
| id | name |
------------
| 100 | a |
| 200 | b |
| 300 | c |
------------
3 rows in set (0.00 sec)3.将原表名字修改为其他将新表数据修改为原表名字
mysql rename table duplicate_table to old_duplicate_table, no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.02 sec)mysql select * from duplicate_table;
------------
| id | name |
------------
| 100 | a |
| 200 | b |
| 300 | c |
------------
3 rows in set (0.00 sec)这里的rename改名就是单纯的改名其实就是文件名和inode的映射关系的改变。
