工作室主题网站,建设工程英语网站,现在用什么工具做网站好,苏州建站公司认准苏州聚尚网络文章目录 什么是CRUD#xff1f;新增#xff08;Create#xff09;单行数据 全列插入多行数据 指定列插入 查询#xff08;Retrieve#xff09;全列查询指定列查询查询字段为表达式起别名查询去重查询排序查询条件查询分页查询 修改#xff08;Update#xff09;删除新增Create单行数据 全列插入多行数据 指定列插入 查询Retrieve全列查询指定列查询查询字段为表达式起别名查询去重查询排序查询条件查询分页查询 修改Update删除Delete 什么是CRUD
CRUD即增加Create、查找Retrieve、修改Update、删除Delete四个单词的首字母缩写。
在进行下面所有操作的前提都是得选中一个数据库并且已经创建了可以用来操作的表。 默认我们现在已经创建了learning数据库在数据库中有一张student表。后续操作都基于此进行
新增Create
insert into 表名 values值值值...;注这里值的类型和个数要和表的 列的类型和个数匹配。 单行数据 全列插入
mysql insert into student values (1,zhangsan);
Query OK, 1 row affected (0.00 sec)注 在SQL中没有字符串类型所以既可以用‘ ’来引用字符串又可以使用“ ”来引用字符串还可以直接插入中文字符需要把数据库字符集改为UTF-8 多行数据 指定列插入
mysql insert into student values (1,zhangsan)2lisi,(3,wangwu);
Query OK, 3 row affected (0.00 sec) 注比一条一条插入更快 查询Retrieve
MySQL是一个客户端—服务器结构的程序显示在客户端的查询结果是一个“临时表”服务器端的数据并不是这样的组织形式。
全列查询
select* from 表名;mysql select * from student;
----------------
| id | name |
----------------
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
----------------
3 rows in set (0.00 sec) 注 通配符* 表示匹配所有的列即查询所有列把所有的数据都查询出来这是一个危险操作当数据量大的时候进行此操作的时候服务器要先读取磁盘把这些数据都查出来再通过网卡把这些数据传输给客户端由于数据量非常大极有可能把磁盘IO输入输出吃满或者网络带宽吃满。这时其他数据就无法正常返回了最直观的感受就是客户端感受到卡顿。 指定列查询
select 列名列名列名.... from 表名;mysql select id from student;
------
| id |
------
| 1 |
| 2 |
| 3 |
------
3 rows in set (0.00 sec) 注当我们查询时省去一些不必要的列时就可以节约大量的磁盘IO和网络带宽了。 查询字段为表达式
select 表达式 from 表名;演示此操作需要创建一个新的表
mysql create table exam_result (id int, name varchar(20), chinese decimal(3,1),math decimal(3,1), english decimal(3,1));
Query OK, 0 rows affected (0.01 sec)
//decimal31 表示共有三位有效数字保留一位小数。 比如32.1、10.5查看一下表结构
mysql desc exam_result;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| chinese | decimal(3,1) | YES | | NULL | |
| math | decimal(3,1) | YES | | NULL | |
| english | decimal(3,1) | YES | | NULL | |
--------------------------------------------------
5 rows in set (0.00 sec)
插入数据
mysql INSERT INTO exam_result (id,name, chinese, math, english) VALUES- (1,zhangsan, 67, 98, 56),- (2,lisi, 87.5, 78, 77),- (3,wangwu, 88, 98.5, 90),- (4,zhaoliu, 82, 84, 67),- (5,sunqi, 55.5, 85, 45),- (6,zhouba, 70, 73, 78.5),- (7,wujiu, 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
查询表内全部数据
mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
-----------------------------------------
7 rows in set (0.00 sec)
带表达式的查询让所有人的语文成绩10分
mysql select name,chinese 10 from exam_result;
-------------------------
| name | chinese 10 |
-------------------------
| zhangsan | 77.0 |
| lisi | 97.5 |
| wangwu | 98.0 |
| zhaoliu | 92.0 |
| sunqi | 65.5 |
| zhouba | 80.0 |
| wujiu | 85.0 |
-------------------------
7 rows in set (0.00 sec)
起别名查询
select 表达式 as 别名 from 表名;普通情况查询语、数、英三科总分
mysql select name, chinese math english from exam_result;
-------------------------------------
| name | chinese math english |
-------------------------------------
| zhangsan | 221.0 |
| lisi | 242.5 |
| wangwu | 276.5 |
| zhaoliu | 233.0 |
| sunqi | 185.5 |
| zhouba | 221.5 |
| wujiu | 170.0 |
-------------------------------------
7 rows in set (0.00 sec)
起别名查询语、数、英三科总分
mysql select name, chinese english math as total from exam_result;
------------------
| name | total |
------------------
| zhangsan | 221.0 |
| lisi | 242.5 |
| wangwu | 276.5 |
| zhaoliu | 233.0 |
| sunqi | 185.5 |
| zhouba | 221.5 |
| wujiu | 170.0 |
------------------
7 rows in set (0.00 sec) 注as可以写着也可以省略。 建议写着 去重查询
select distinct 列名 from 表名;演示此操作需增加相同信息
mysql insert into exam_result (name, math) values (zhangsan, 98.0);
Query OK, 1 row affected (0.00 sec)
查看当前表的全部信息
mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
-----------------------------------------
8 rows in set (0.00 sec)
进行去重查询
mysql select distinct name, math from exam_result;
-----------------
| name | math |
-----------------
| zhangsan | 98.0 |
| lisi | 78.0 |
| wangwu | 98.5 |
| zhaoliu | 84.0 |
| sunqi | 85.0 |
| zhouba | 73.0 |
| wujiu | 65.0 |
-----------------
7 rows in set (0.00 sec) 注当用distinct指定多个列时必须是这几个列的值同时相同时才会去重。 排序查询
select 列名 from 表名 order by 列名;按语文成绩升序排序
mysql select * from exam_result order by chinese;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| NULL | zhangsan | NULL | 98.0 | NULL |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
-----------------------------------------
8 rows in set (0.00 sec)
按语文成绩降序排序
mysql select * from exam_result order by chinese desc;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
-----------------------------------------
8 rows in set (0.00 sec)
order by 也可以针对别名进行排序
mysql select name, chinese math english as total from exam_result order by total desc;
------------------
| name | total |
------------------
| wangwu | 276.5 |
| lisi | 242.5 |
| zhaoliu | 233.0 |
| zhouba | 221.5 |
| zhangsan | 221.0 |
| sunqi | 185.5 |
| wujiu | 170.0 |
| zhangsan | NULL |
------------------
8 rows in set (0.00 sec)
order by 进行排序的时候还可以指定多个列进行排序 效果是先以第一列为标准进行比较如果第一列不分胜负那么继续按照第二列进行比较一次类推
mysql select * from exam_result order by math desc,chinese;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
-----------------------------------------
8 rows in set (0.00 sec) 注 升序排序末尾加asc 但默认是升序排序可以省略降序排序末尾加desc在SQL中拿NULL和其他类型进行混合计算结果仍然是NULL在select操作中如果没有使用order by 那么查询结果的顺序是不确定的。 条件查询
select* from 表名 where 条件;引入where子句对条件进行筛选即用where子句对最初的每一行查询结果进行筛选如果满足条件就把这一行放入到最终的查询结果如果不满足条件则舍弃这一行最后返回最终查询结果。 比较运算符 注 在SQL中没有 使用进行比较在SQL中NULL NULL 结果还是NUULL 相当于false NULL NULL 结果是truelike进行模糊匹配匹配过程中可以带上通配符 逻辑运算符 注 在where条件中可以使用表达式但不能使用别名and的优先级高于or在使用时注意次序或者加 基本查询查询语文成绩比英语成绩好的人
mysql select * from exam_result where chinese english;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
-----------------------------------------
5 rows in set (0.00 sec)
and / or查询
mysql select * from exam_result where chinese 80 or english 70 and math 70;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
-----------------------------------------
4 rows in set (0.00 sec)
范围查询查询语文成绩在80-90之间的人
mysql select * from exam_result where chinese 80 and chinese 90;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
-----------------------------------------
3 rows in set (0.00 sec)mysql select * from exam_result where chinese between 80 and 90;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
-----------------------------------------
3 rows in set (0.00 sec)
in 查询 查询数学成绩是58 或者59 或者98 或者99的人
mysql select * from exam_result where math in (58,59,98,99);
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
-----------------------------------------
1 rows in set (0.00 sec)mysql select * from exam_result where math 58 or math 59 or math 98 or math 99;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
-----------------------------------------
1 rows in set (0.00 sec)
模糊查询like 不一定完全相同只要有一部分匹配即可。
mysql select * from exam_result where name like w%;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 3 | wangwu | 88 | 98.5 | 90 |
| 7 | wujiu | 75 | 65 | 30 |
-----------------------------------------
2 rows in set (0.00 sec) 注 % 可以替代任意个字符_ 可以替代任意一个字符 NULL的查询
mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
-----------------------------------------
8 rows in set (0.00 sec)mysql select * from exam_result where chinese NULL;
Empty set (0.00 sec)mysql select * from exam_result where chinese NULL;
--------------------------------------
| id | name | chinese | math | english |
--------------------------------------
| NULL |zhangsan| NULL | 98.0 | NULL |
--------------------------------------
1 row in set (0.00 sec)mysql select * from exam_result where chinese is NULL;
--------------------------------------
| id | name | chinese | math | english |
--------------------------------------
| NULL |zhangsan| NULL | 98.0 | NULL |
--------------------------------------
1 row in set (0.00 sec) 注 直接使用 来进行匹配是不能正确进行筛选的使用 可以正确和NULL匹配使用 is NULL也可以正确和NULL匹配 分页查询
select 列名 from 表名 limit N offset M;
select 列名 from 表名 limit M,N;N返回结果的条数 M跳过M条结果再开始返回 从M条开始查询 最多返回N条结果
mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
-----------------------------------------
8 rows in set (0.00 sec)mysql select * from exam_result limit 3;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
-----------------------------------------
3 rows in set (0.00 sec)mysql select * from exam_result limit 3 offset 3;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
-----------------------------------------
3 rows in set (0.00 sec) 注select* 这样的操作容易把数据库搞挂了除了select*外其他的查询操作只要你返回的结果足够多都有可能把数据库搞挂即使你加上了where子句进行筛选但是返回的结果仍然可能很多。最保险的办法就是加上limit 修改Update
update 表名 set 列名 值..... where 条件;把lisi 的数学成绩修改为80分
mysql update exam_result set math 80 where name lisi;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 80.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
-----------------------------------------
8 rows in set (0.00 sec) 注 update后面的where条件很重要。加上条件表示修改符合条件某些行不加条件表示修改所有行。 删除Delete
delete from 表名 where 条件;删除zhangsan的信息
mysql delete from exam_result where name zhangsan;
Query OK, 1 row affected (0.00 sec)mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 2 | lisi | 87.5 | 80.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 70.0 | 60.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
-----------------------------------------
6 rows in set (0.00 sec) 注 delete后面的 where 条件很重要。加上条件表示删除符合条件某些行不加条件表示删除表中的全部信息。delete from 表名表示删除表内的所有信息但是表还在。 drop table 表名表示删除整个表表也不存在了。
