研究院网站建设方案,网站建设实验七,俄罗斯搜索引擎yandex,ps做素材下载网站MySQL中表的增删查改#xff0c;即为CRUD#xff0c;是增加#xff08;Create#xff09;查询#xff08;Retrieve#xff09;更新#xff08;Update#xff09;删除#xff08;Delete#xff09;四个单词首字母的缩写。 目录
1.新增
1.1单行数据全列插入
1.2多行…MySQL中表的增删查改即为CRUD是增加Create查询Retrieve更新Update删除Delete四个单词首字母的缩写。 目录
1.新增
1.1单行数据全列插入
1.2多行数据指定列插入
1.3一次插入多个数据
2.查询
2.1全列查询
2.2指定列查询
2.3查询字段为表达式
2.4别名 AS
2.5去重查询 DISTINCT
2.6排序查询 ORDER BY
2.7条件查询 WHERE
//时间的插入与查询
2.8模糊查询
2.9NULL的查询
2.10分页查询LIMIT
3.修改
4.删除 1.新增
1.1单行数据全列插入 格式insert into 表名 values列名 类型列名 类型 …… 注意1 into 可以省略 2 数量必须和定义表的列的数量及顺序一致 3 SQL中引用字符串使用 或 都可以 4 若想插入中文必须保证在创建数据库的时候指定charset utf8忘记指定可以删库重建并指定。 mysql use javasql;
Database changedmysql create table student (- id int,- name varchar(20)- );
Query OK, 0 rows affected (0.05 sec)mysql show tables;
-------------------
| Tables_in_javasql |
-------------------
| student |
-------------------
1 row in set (0.00 sec)mysql desc student;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)mysql insert into student values(1,zhangsan);
Query OK, 1 row affected (0.00 sec)mysql insert student values(2,lisi);
Query OK, 1 row affected (0.00 sec)mysql insert into student values(3,王五);
Query OK, 1 row affected (0.01 sec)
1.2多行数据指定列插入 格式insert into 表名想要插入的列values列名 …… mysql insert into student (name) values (zhaoliu);
Query OK, 1 row affected (0.00 sec)
1.3一次插入多个数据 格式insert into 表名 values列名 类型列名 类型 ……),(列名 类型列名 类型 ……),(列名 类型列名 类型 ………… 注意一次插入三条数据只需一次服务器和客户端的交互分三次每次插入一条数据则需要三次交互后者的交互成本比前者高上不少类似于一次寄三件和分三次寄出所需的运费。所以最好使用一次插入多个数据。 mysql insert into student values(10,aaa),(11,bbb),(12,ccc);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
2.查询
2.1全列查询 格式select * from 表名 注意1 其中 * 为通配符 2 这个操作很危险若这个表中存了上亿个数据服务器解析SQL并执行就会读取student表的每一条数据把所有的记录都通过网络返回给客户端导致服务器一瞬间硬盘的带宽和网卡就都被吃满了后续的操作速度就会特别慢就像“卡死”了一样。 mysql create table examResult(- 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 examResult(id,name,Chinese,Math,English) values- (1,zhangsan,67,98,56),- (2,lisi,87,58,98),- (3,wangwu,88,56,90),- (4,zhaoliu,75,65,40);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql select * from examResult;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 56.0 | 90.0 |
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
----------------------------------------
4 rows in set (0.00 sec)
2.2指定列查询 格式select 列名列名……from 表名 mysql select name,Chinese from examResult;
-------------------
| name | Chinese |
-------------------
| zhangsan | 67.0 |
| lisi | 87.0 |
| wangwu | 88.0 |
| zhaoliu | 75.0 |
-------------------
4 rows in set (0.00 sec)mysql select id,English from examResult;
---------------
| id | English |
---------------
| 1 | 56.0 |
| 2 | 98.0 |
| 3 | 90.0 |
| 4 | 40.0 |
---------------
4 rows in set (0.00 sec)
2.3查询字段为表达式 格式select 列名 运算列名 运算 …… from 表名 注意1 运算只能进行数字间的四则运算不能使用字符串 2 运算完后客户端中显示的结果只是一个“临时表”也就是说select操作不管怎么写都不会影响到数据库服务器硬盘上存储的原始数据 3 临时表中的数据类型和原始数据类型不一定一致比如下面的108.0按理说类型为decimal(3,1)只能有三位有效数字但是临时表中是四位有效数字也没报错。 mysql select name,Math 10 from examResult;
---------------------
| name | Math 10 |
---------------------
| zhangsan | 108.0 |
| lisi | 68.0 |
| wangwu | 66.0 |
| zhaoliu | 75.0 |
---------------------
4 rows in set (0.00 sec)mysql select name,Math from examResult;
----------------
| name | Math |
----------------
| zhangsan | 98.0 |
| lisi | 58.0 |
| wangwu | 56.0 |
| zhaoliu | 65.0 |
----------------
4 rows in set (0.00 sec)
2.4别名 AS 格式select 列名 as 别名 from 表名 注意当查询临时表的列名和当前表达式一致时若表达式过长可读性会较差这时就需使用别名来简化表达式 mysql select name,Chinese Math English from examResult;
------------------------------------
| name | Chinese Math English |
------------------------------------
| zhangsan | 221.0 |
| lisi | 243.0 |
| wangwu | 234.0 |
| zhaoliu | 180.0 |
------------------------------------
4 rows in set (0.00 sec)mysql select name,Chinese Math English as total from examResult;
-----------------
| name | total |
-----------------
| zhangsan | 221.0 |
| lisi | 243.0 |
| wangwu | 234.0 |
| zhaoliu | 180.0 |
-----------------
4 rows in set (0.00 sec)
2.5去重查询 DISTINCT 格式select distinct 列名 from 表名 注意distinct 后面的列名也可以是多个这时要求所有的列的值都相同才算“重复”。 mysql select Math from examResult;
------
| Math |
------
| 98.0 |
| 58.0 |
| 98.0 |
| 65.0 |
------
4 rows in set (0.00 sec)mysql select distinct Math from examResult;
------
| Math |
------
| 98.0 |
| 58.0 |
| 65.0 |
------
4 rows in set (0.00 sec)
2.6排序查询 ORDER BY 格式select 列名 from 表名 order by 列名 注意1 同上面说的这还是只是在临时表上排序数 2 默认排序为升序可在语句末尾加上asc 3 若想降序,语句末尾加上descdescend与之前查看指定表的结构describe不同 4 order by 后可接多个列名表示先拿前面的列排序出现相同时再拿后面的列排序。 mysql select * from examResult;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 98.0 | 90.0 |
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
----------------------------------------
4 rows in set (0.00 sec)mysql select * from examResult order by English;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 3 | wangwu | 88.0 | 98.0 | 90.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
----------------------------------------
4 rows in set (0.04 sec)mysql select * from examResult order by English desc;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 98.0 | 90.0 |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
----------------------------------------
4 rows in set (0.00 sec)mysql select * from examResult order by Math,English;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 3 | lisi | 87.0 | 58.0 | 98.0 |
| 2 | zhaoliu | 75.0 | 65.0 | 40.0 |
| 4 | zhangsan | 67.0 | 98.0 | 56.0 |
| 1 | wangwu | 88.0 | 98.0 | 90.0 |
----------------------------------------
4 rows in set (0.00 sec)
2.7条件查询 WHERE 格式select 列名列名 …… from 表名 where 条件 注意1 条件中不能使用列的别名 2 and 的优先级大于 or但是建议使用括号手动明确优先级 SQL中支持的条件表示 关系运算符 逻辑运算符
运算符 说明 , , , 大于大于等于小于小于等于等于NULL不安全例如 NULL NULL的结果是NULL等于NULL安全例如NULL NULL的结果是TRUE!, 不等于BETWEEN a AND b范围匹配[ a, b ] 满足范围返回TRUEIN ( option, …… )如果是option中的任意一个返回TRUEIS NULL是NULLIS NOT NULL不是NULLLIKE模糊匹配如%表示任意多个 (包括0个) 任意字符_表示任意一个字符
注意SQL默认情况下对WHERE XX!NULL和WHERE XXNULL的判断会永远返回0行却不会提示语法错误 .
运算符说明AND多个条件必须都为TRUE结果才为TRUEOR任意一个条件为TRUE结果为TRUENOT条件为TRUE结果为FALSE
mysql select name,English from examResult where English 60;
-------------------
| name | English |
-------------------
| zhangsan | 56.0 |
| zhaoliu | 40.0 |
-------------------
2 rows in set (0.04 sec)mysql select name,English from examResult where Chinese English;
-------------------
| name | English |
-------------------
| zhangsan | 56.0 |
| zhaoliu | 40.0 |
-------------------
2 rows in set (0.00 sec)mysql select name,Chinese Math English as total from examResult where Chinese Math English 200;
-----------------
| name | total |
-----------------
| zhangsan | 221.0 |
| lisi | 243.0 |
| wangwu | 234.0 |
-----------------
3 rows in set (0.00 sec)mysql select name,Chinese,English from examResult where Chinese 80 and English 80;
--------------------------
| name | Chinese | English |
--------------------------
| lisi | 87.0 | 98.0 |
| wangwu | 88.0 | 90.0 |
--------------------------
2 rows in set (0.00 sec)mysql select name,Chinese,English from examResult where Chinese 80 or Math 80;
----------------------------
| name | Chinese | English |
----------------------------
| zhangsan | 67.0 | 56.0 |
| lisi | 87.0 | 98.0 |
| wangwu | 88.0 | 90.0 |
----------------------------
3 rows in set (0.00 sec)
//时间的插入与查询
mysql use javasql;
Database changedmysql insert into article values- (1,abcd,20190101113031),- (2,efgh,20190203103032),- (3,ijkl,20190304103000),- (4,NULL,20220309113020);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql select * from article where date between 2019-1-1 10:30:00 and 2019-11-10 4:02:00;
------------------------------------
| author | title | date |
------------------------------------
| 1 | abcd | 2019-01-01 11:30:31 |
| 2 | efgh | 2019-02-03 10:30:32 |
| 3 | ijkl | 2019-03-04 10:30:00 |
------------------------------------
3 rows in set (0.00 sec)mysql select * from article where title null or date 2019-01-01 00:00:00;
------------------------------------
| author | title | date |
------------------------------------
| 1 | abcd | 2019-01-01 11:30:31 |
| 2 | efgh | 2019-02-03 10:30:32 |
| 3 | ijkl | 2019-03-04 10:30:00 |
| 4 | NULL | 2022-03-09 11:30:20 |
------------------------------------
4 rows in set (0.00 sec)
2.8模糊查询 格式select * from 表名 where 条件; % 匹配0个或任意个 任意字符 _ 匹配1个 任意字符 mysql select * from examresult where name like 孙%;
--------------------------------------
| id | name | Chinese | Math | English |
--------------------------------------
| 5 | 孙权 | 67.0 | 89.0 | 72.0 |
| 6 | 孙尚香 | 56.0 | 98.0 | 59.0 |
--------------------------------------
2 rows in set (0.00 sec)mysql select * from examresult where name like 孙_;
------------------------------------
| id | name | Chinese | Math | English |
------------------------------------
| 5 | 孙权 | 67.0 | 89.0 | 72.0 |
------------------------------------
1 row in set (0.00 sec)mysql select * from examresult where name like 孙__;
--------------------------------------
| id | name | Chinese | Math | English |
--------------------------------------
| 6 | 孙尚香 | 56.0 | 98.0 | 59.0 |
--------------------------------------
1 row in set (0.00 sec)mysql select * from examresult where name like 孙___;
Empty set (0.00 sec)
2.9NULL的查询 格式select * from 表名 where Chinese is null; select * from 表名 where Chinese null; mysql select * from examresult where Chinese is null;
--------------------------------------
| id | name | Chinese | Math | English |
--------------------------------------
| 7 | 孙悟空 | NULL | NULL | NULL |
--------------------------------------
1 row in set (0.04 sec)mysql select * from examresult where Chinese null;
--------------------------------------
| id | name | Chinese | Math | English |
--------------------------------------
| 7 | 孙悟空 | NULL | NULL | NULL |
--------------------------------------
1 row in set (0.00 sec)
2.10分页查询LIMIT 格式select * from 表名 limit M offset N; 表示一次查询从第N条数据开始最多获取到M条记录 mysql select * from examresult limit 4;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 56.0 | 90.0 |
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
----------------------------------------
4 rows in set (0.00 sec)mysql select * from examresult limit 4 offset 3;
---------------------------------------
| id | name | Chinese | Math | English |
---------------------------------------
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
| 5 | 孙权 | 67.0 | 89.0 | 72.0 |
| 6 | 孙尚香 | 56.0 | 98.0 | 59.0 |
| 7 | 孙悟空 | NULL | NULL | NULL |
---------------------------------------
4 rows in set (0.00 sec)
3.修改 格式update 表名 set 列名 M where 条件; 将满足条件的列的值改为M。后面不加where条件则会对所有数据进行操作很危险 注意此处的修改是修改MySQL服务器持久生效。 mysql select * from examresult;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 56.0 | 90.0 |
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
| 5 | 孙权 | 67.0 | 89.0 | 72.0 |
| 6 | 孙尚香 | 56.0 | 98.0 | 59.0 |
----------------------------------------
6 rows in set (0.00 sec)mysql update examresult set Math 66 where name wangwu;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql update examresult set Chinese 80,English 90 where name 孙尚香;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from examresult;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 66.0 | 90.0 |
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
| 5 | 孙权 | 67.0 | 89.0 | 72.0 |
| 6 | 孙尚香 | 80.0 | 98.0 | 90.0 |
----------------------------------------
6 rows in set (0.00 sec)
4.删除 格式delete from 表名 where 条件 后面不加where条件则会删除表内所有数据很危险 mysql select * from examresult;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 66.0 | 90.0 |
| 4 | zhaoliu | 75.0 | 65.0 | 40.0 |
----------------------------------------
7 rows in set (0.00 sec)mysql delete from examresult where name zhaoliu;
Query OK, 1 row affected (0.04 sec)mysql select * from examresult;
----------------------------------------
| id | name | Chinese | Math | English |
----------------------------------------
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 66.0 | 90.0 |
----------------------------------------
6 rows in set (0.00 sec)
