表的增删改查

最新推荐文章于 2024-02-28 17:31:21 发布
最新推荐文章于 2024-02-28 17:31:21 发布
阅读量453 收藏 3
点赞数 1
分类专栏： MYSQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41446512/article/details/125121293
版权

先创建一张student表

Database changed
mysql> create table student(
    -> id int unsigned primary key auto_increment comment '主键',
    -> sn int not null unique comment '学号',
    -> name varchar(20) not null,
    -> qq varchar(16)
    -> )engine=InnoDB default charset=utf8;
mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sn    | int(11)          | NO   | UNI | NULL    |                |
| name  | varchar(20)      | NO   |     | NULL    |                |
| qq    | varchar(16)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

一、增（Create）

语法（括号内的都可以省略）：

INSERT [INTO] table_name
[(column [, column] …)]
VALUES (value_list) [, (value_list)] …
value_list: value, [, value] …

1.单行插入

insert student(sn,name,qq) values (123,'猪八戒','123456');
Query OK, 1 row affected (0.01 sec)

mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
+----+-----+-----------+--------+

2.多行插入

mysql> insert student(sn,name,qq) values (136,'孙悟空','123987'),(145,'沙僧','')598764mysq','123987'),(145,'沙僧','256489');
mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
|  2 | 136 | 孙悟空    | 123987 |
|  3 | 145 | 沙僧      | 256489 |
+----+-----+-----------+--------+

3.指定列插入

mysql> insert student(sn,name) values (142,'唐三藏');
Query OK, 1 row affected (0.01 sec)

mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
|  2 | 136 | 孙悟空    | 123987 |
|  3 | 145 | 沙僧      | 256489 |
|  4 | 142 | 唐三藏    | NULL   |
+----+-----+-----------+--------+
4 rows in set (0.00 sec)

4.插入否则更新

表中已经存在的，更新信息，不存在则插入
（1）已经存在

mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
|  2 | 136 | 孙悟空    | 123987 |
|  3 | 145 | 沙僧      | 256489 |
|  4 | 142 | 唐三藏    | NULL   |
+----+-----+-----------+--------+
4 rows in set (0.00 sec)

mysql> insert into student values (1,123,'猪悟能','2468135');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into student values (1,123,'猪悟能','2468135') ON duplicate key update name='猪悟能',qq='2468135';
Query OK, 2 rows affected (0.00 sec)

mysql> select *from student;
+----+-----+-----------+---------+
| id | sn  | name      | qq      |
+----+-----+-----------+---------+
|  1 | 123 | 猪悟能    | 2468135 |
|  2 | 136 | 孙悟空    | 123987  |
|  3 | 145 | 沙僧      | 256489  |
|  4 | 142 | 唐三藏    | NULL    |
+----+-----+-----------+---------+
4 rows in set (0.00 sec)

（2）不存在

mysql> select *from student;
+----+-----+-----------+---------+
| id | sn  | name      | qq      |
+----+-----+-----------+---------+
|  1 | 123 | 猪悟能    | 2468135 |
|  2 | 136 | 孙悟空    | 123987  |
|  3 | 145 | 沙僧      | 256489  |
|  4 | 142 | 唐三藏    | NULL    |
+----+-----+-----------+---------+
4 rows in set (0.00 sec)

mysql> insert into student values (10,150,'猪悟能1','3578192') ON duplicate key update namme='猪悟能1',qq='3578192';
Query OK, 1 row affected (0.01 sec)

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 沙僧       | 256489  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

注意：
– 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，并且数据已经被更新

通过 MySQL 函数获取受到影响的数据行数

SELECT ROW_COUNT();

5.替换

– 主键 或者 唯一键 没有冲突，则直接插入；
– 主键 或者 唯一键 如果冲突，则删除后再插入

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 沙僧       | 256489  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

mysql> replace into values (3,145,'蜘蛛精','980567');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values (3,145,'蜘蛛精','980567')' at line 1
mysql> replace into student values (3,145,'蜘蛛精','980567');
Query OK, 2 rows affected (0.00 sec)

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

注意：
– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，删除后重新插入

二、删（Delete）

1.删除数据

（1）删除孙悟空的考试成绩

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  2 | 孙悟空    |     174 |   90 |      77 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.00 sec)

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)

（2）删除总分前三名的同学

mysql> select name,chinese+math+english total from exam_result order by total;
+-----------+-------+
| name      | total |
+-----------+-------+
| 刘玄德    |   270 |
| 宋公明    |   275 |
| 唐三藏    |   288 |
| 孙权      |   291 |
| 曹孟德    |   297 |
| 猪悟能    |   364 |
+-----------+-------+
6 rows in set (0.00 sec)

mysql> delete from exam_result order by chinese+math+english limit 3;
Query OK, 3 rows affected (0.01 sec)

mysql> select name,chinese+math+english total from exam_result order by total;
+-----------+-------+
| name      | total |
+-----------+-------+
| 孙权      |   291 |
| 曹孟德    |   297 |
| 猪悟能    |   364 |
+-----------+-------+
3 rows in set (0.00 sec)

2.清空数据

（1）删表，继续插入数据仍会继续增长（自增的累加值不会清空）

mysql> select *from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)

mysql> select *from for_delete;
Empty set (0.00 sec)
mysql> insert into for_delete (name) values ('x'),('y'),('z');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | x    |
|  5 | y    |
|  6 | z    |
+----+------+
3 rows in set (0.00 sec)

（2）截断表（truncate）：会重置 AUTO_INCREMENT 项

mysql> select *from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | x    |
|  5 | y    |
|  6 | z    |
+----+------+
3 rows in set (0.00 sec)

mysql> delete from for_delete;
Query OK, 3 rows affected (0.00 sec)

mysql> select *from for_delete;
Empty set (0.00 sec)

mysql> truncate for_delete;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into for_delete (name) values ('x'),('y'),('z');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | x    |
|  2 | y    |
|  3 | z    |
+----+------+
3 rows in set (0.00 sec)

三、查(Retrieve)

先创建表

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.08 sec)

mysql> -- 插入测试数据
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.00 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> desc exam_result;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)      | NO   |     | NULL    |                |
| chinese | float            | YES  |     | 0       |                |
| math    | float            | YES  |     | 0       |                |
| english | float            | YES  |     | 0       |                |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

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.select列

（1）全列查询

利用*进行全列查询，不建议，数据越多，传输的数据量越大

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)

（2）限制列查询

mysql> select *from exam_result limit 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)

（3）指定列查询

Ⅰ、指定列

mysql> select name,math,id from exam_result;
+-----------+------+----+
| name      | math | id |
+-----------+------+----+
| 唐三藏    |   98 |  1 |
| 孙悟空    |   78 |  2 |
| 猪悟能    |   98 |  3 |
| 曹孟德    |   84 |  4 |
| 刘玄德    |   85 |  5 |
| 孙权      |   73 |  6 |
| 宋公明    |   65 |  7 |
+-----------+------+----+
7 rows in set (0.00 sec)

Ⅱ、自定义的列

mysql> select name,math,1+1 from exam_result;
+-----------+------+-----+
| name      | math | 1+1 |
+-----------+------+-----+
| 唐三藏    |   98 |   2 |
| 孙悟空    |   78 |   2 |
| 猪悟能    |   98 |   2 |
| 曹孟德    |   84 |   2 |
| 刘玄德    |   85 |   2 |
| 孙权      |   73 |   2 |
| 宋公明    |   65 |   2 |
+-----------+------+-----+
7 rows in set (0.00 sec)

求总成绩

mysql> select name,math,math+chinese+english from exam_result;
+-----------+------+----------------------+
| name      | math | math+chinese+english |
+-----------+------+----------------------+
| 唐三藏    |   98 |                  221 |
| 孙悟空    |   78 |                  242 |
| 猪悟能    |   98 |                  276 |
| 曹孟德    |   84 |                  233 |
| 刘玄德    |   85 |                  185 |
| 孙权      |   73 |                  221 |
| 宋公明    |   65 |                  170 |
+-----------+------+----------------------+
7 rows in set (0.00 sec)

（4）为查询结果指定别名（as可省略）

mysql> select name,math,math+chinese+english from exam_result;
+-----------+------+----------------------+
| name      | math | math+chinese+english |
+-----------+------+----------------------+
| 唐三藏    |   98 |                  221 |
| 孙悟空    |   78 |                  242 |
| 猪悟能    |   98 |                  276 |
| 曹孟德    |   84 |                  233 |
| 刘玄德    |   85 |                  185 |
| 孙权      |   73 |                  221 |
| 宋公明    |   65 |                  170 |
+-----------+------+----------------------+
7 rows in set (0.00 sec)

mysql> select name,math,math+chinese+english as total from exam_result;
+-----------+------+-------+
| name      | math | total |
+-----------+------+-------+
| 唐三藏    |   98 |   221 |
| 孙悟空    |   78 |   242 |
| 猪悟能    |   98 |   276 |
| 曹孟德    |   84 |   233 |
| 刘玄德    |   85 |   185 |
| 孙权      |   73 |   221 |
| 宋公明    |   65 |   170 |
+-----------+------+-------+
7 rows in set (0.00 sec)


mysql> select name,math,math+chinese+english total from exam_result;
+-----------+------+-------+
| name      | math | total |
+-----------+------+-------+
| 唐三藏    |   98 |   221 |
| 孙悟空    |   78 |   242 |
| 猪悟能    |   98 |   276 |
| 曹孟德    |   84 |   233 |
| 刘玄德    |   85 |   185 |
| 孙权      |   73 |   221 |
| 宋公明    |   65 |   170 |
+-----------+------+-------+
7 rows in set (0.00 sec)

mysql> select name,math,math+chinese+english 总分 from exam_result;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 唐三藏    |   98 |    221 |
| 孙悟空    |   78 |    242 |
| 猪悟能    |   98 |    276 |
| 曹孟德    |   84 |    233 |
| 刘玄德    |   85 |    185 |
| 孙权      |   73 |    221 |
| 宋公明    |   65 |    170 |
+-----------+------+--------+
7 rows in set (0.00 sec)

（5）结果去重（distinct）

mysql> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   98 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
7 rows in set (0.00 sec)

mysql> select distinct math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
6 rows in set (0.00 sec)

2.where

(1)运算符

运算符说明
<，> ,>=, <=大于，大于等于，小于，小于等于
=等于，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)

（2）查询实例

a.范围between and
mysql> select name,math from exam_result where math between 80 and 90;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 刘玄德    |   85 |
+-----------+------+
2 rows in set (0.00 sec)
b.in/or的使用(数学成绩为58或59或98或99的名字及数学成绩)
mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

mysql> select name,math from exam_result where math in(58,59,98,99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)
c.查询数学成绩为98的名字
mysql> select name,math from exam_result where math=98;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

mysql> select name,math from exam_result where math<=>98;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)
d.查询姓孙的
mysql> select name from exam_result where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)
e.查询两个字的名字且姓孙
mysql> select name from exam_result where  name like '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.00 sec)
f.查询语文成绩好于英语成绩的同学
mysql> select name,chinese,english from exam_result where chinese > english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)
g.查询语文成绩好于英语成绩再加30的同学
mysql> select name,chinese,english from exam_result where chinese > english+30;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
1 row in set (0.00 sec)
h.总分在200以下的同学

where后续的字句本身在select期间要进行作为条件筛选

mysql> select name,math+chinese+english as total from exam_result where math+chinese+engliish< 200;
+-----------+-------+
| name      | total |
+-----------+-------+
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
2 rows in set (0.00 sec)

错误写法：

mysql> select name,math+chinese+english as total from exam_result where total < 200;
i.语文成绩>80且不姓孙的同学
mysql> select name,chinese from exam_result where chinese > 80;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

mysql> select name,chinese from exam_result where chinese > 80 and name like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
+-----------+---------+
1 row in set (0.00 sec)

mysql> select name,chinese from exam_result where chinese > 80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.00 sec)
j.孙某同学，否则总成绩>200且语文成绩<数学成绩且英语成绩>80
mysql> select name,english,math,chinese from exam_result where name like '孙_';
+--------+---------+------+---------+
| name   | english | math | chinese |
+--------+---------+------+---------+
| 孙权   |      78 |   73 |      70 |
+--------+---------+------+---------+
1 row in set (0.00 sec)

mysql> select name,english,math,chinese,english+math+chinese as total from exam_result wheere name like '孙_';
+--------+---------+------+---------+-------+
| name   | english | math | chinese | total |
+--------+---------+------+---------+-------+
| 孙权   |      78 |   73 |      70 |   221 |
+--------+---------+------+---------+-------+
1 row in set (0.00 sec)

mysql> select name,english,math,chinese,english+math+chinese as total from exam_result where name like '孙_' or (english+math+chinese>200 and chinese < math and english > 80);
+-----------+---------+------+---------+-------+
| name      | english | math | chinese | total |
+-----------+---------+------+---------+-------+
| 猪悟能    |      90 |   98 |      88 |   276 |
| 孙权      |      78 |   73 |      70 |   221 |
+-----------+---------+------+---------+-------+
2 rows in set (0.00 sec)
k.NULL表查询
mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

mysql> select name from student where qq<=>NULL;
+-----------+
| name      |
+-----------+
| 唐三藏    |
+-----------+
1 row in set (0.00 sec)

mysql> select name from student where not qq<=>NULL;
+------------+
| name       |
+------------+
| 猪悟能     |
| 孙悟空     |
| 蜘蛛精     |
| 猪悟能1    |
+------------+
4 rows in set (0.00 sec)

mysql> select name from student where not qq is not NULL;
+-----------+
| name      |
+-----------+
| 唐三藏    |
+-----------+
1 row in set (0.00 sec)

mysql> select name from student where  qq is not NULL;
+------------+
| name       |
+------------+
| 猪悟能     |
| 孙悟空     |
| 蜘蛛精     |
| 猪悟能1    |
+------------+
4 rows in set (0.00 sec)

mysql> select name from student where  qq is  NULL;
+-----------+
| name      |
+-----------+
| 唐三藏    |
+-----------+
1 row in set (0.00 sec)

3.结果排序

语法：

– ASC 为升序（从小到大）
– DESC 为降序（从大到小）
– 默认为 ASC
SELECT … FROM table_name [WHERE …]
ORDER BY column [ASC|DESC], […];

（1） 同学及数学成绩，按数学成绩升序显示

mysql> select name,math from exam_result order by math;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)

mysql> select name,math from exam_result order by math asc;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)

mysql> select name,math from exam_result order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
| 刘玄德    |   85 |
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
| 宋公明    |   65 |
+-----------+------+
7 rows in set (0.00 sec)

（2） 同学及 qq 号，按 qq 号排序显示

mysql> select *from student where sn is not null;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

mysql> select *from student where sn is not null order by qq;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  4 | 142 | 唐三藏     | NULL    |
|  2 | 136 | 孙悟空     | 123987  |
|  1 | 123 | 猪悟能     | 2468135 |
| 10 | 150 | 猪悟能1    | 3578192 |
|  3 | 145 | 蜘蛛精     | 980567  |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

（3）查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

mysql> select name,chinese,math,english from exam_result;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 唐三藏    |      67 |   98 |      56 |
| 孙悟空    |      87 |   78 |      77 |
| 猪悟能    |      88 |   98 |      90 |
| 曹孟德    |      82 |   84 |      67 |
| 刘玄德    |      55 |   85 |      45 |
| 孙权      |      70 |   73 |      78 |
| 宋公明    |      75 |   65 |      30 |
+-----------+---------+------+---------+
7 rows in set (0.00 sec)

mysql> select name,chinese,math,english from exam_result order by math,english,chinese;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 宋公明    |      75 |   65 |      30 |
| 孙权      |      70 |   73 |      78 |
| 孙悟空    |      87 |   78 |      77 |
| 曹孟德    |      82 |   84 |      67 |
| 刘玄德    |      55 |   85 |      45 |
| 唐三藏    |      67 |   98 |      56 |
| 猪悟能    |      88 |   98 |      90 |
+-----------+---------+------+---------+
7 rows in set (0.00 sec)

mysql> select name,chinese,math,english from exam_result order by math desc,english asc,chinese asc;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 唐三藏    |      67 |   98 |      56 |
| 猪悟能    |      88 |   98 |      90 |
| 刘玄德    |      55 |   85 |      45 |
| 曹孟德    |      82 |   84 |      67 |
| 孙悟空    |      87 |   78 |      77 |
| 孙权      |      70 |   73 |      78 |
| 宋公明    |      75 |   65 |      30 |
+-----------+---------+------+---------+
7 rows in set (0.00 sec)

（4）查询同学及总分，并从高到低

要排序，是先把数据准备好，才可以order by，所以可以直接order by total

mysql> select name,chinese+math+english total from exam_result;
+-----------+-------+
| name      | total |
+-----------+-------+
| 唐三藏    |   221 |
| 孙悟空    |   242 |
| 猪悟能    |   276 |
| 曹孟德    |   233 |
| 刘玄德    |   185 |
| 孙权      |   221 |
| 宋公明    |   170 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> select name,chinese+math+english total from exam_result order by chinese+math+english desc;
+-----------+-------+
| name      | total |
+-----------+-------+
| 猪悟能    |   276 |
| 孙悟空    |   242 |
| 曹孟德    |   233 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> select name,chinese+math+english total from exam_result order by total desc; 
+-----------+-------+
| name      | total |
+-----------+-------+
| 猪悟能    |   276 |
| 孙悟空    |   242 |
| 曹孟德    |   233 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
7 rows in set (0.00 sec)

（5） 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

mysql> select name,math from exam_result where name like '孙%' or name like '曹%';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 孙权      |   73 |
+-----------+------+
3 rows in set (0.00 sec)


mysql> select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
3 rows in set (0.00 sec)

4.筛选分页结果

语法：

– 起始下标为 0
– 从 0 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
– 从 s 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n;
– 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;

(1)limit 3,2:从第3条记录开始，选2条(0,1,2,3……)

mysql> select *from exam_result limit 3,2;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)

（2）limit 2 offset 3:从第3条记录开始，选2条(0,1,2,3……)

mysql> select *from exam_result limit 2 offset 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)

（3）分页


mysql> select *from exam_result limit 3 offset 0;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select *from exam_result limit 3 offset 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select *from exam_result limit 3 offset 6;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

四.改（Update）

1.将孙悟空同学的数学成绩变更为 90 分（若不加条件，默认所有人数学成绩改为90）

mysql> update exam_result set math=90 where name='孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   90 |      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)

2.将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.00 sec)

mysql> update exam_result set math=60,chinese=70 where name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.00 sec)

3.将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

mysql> select name,math+english+chinese total from exam_result order by total;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   170 |
| 刘玄德    |   185 |
| 曹孟德    |   197 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 孙悟空    |   254 |
| 猪悟能    |   276 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> select name,math+english+chinese total from exam_result order by total limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   170 |
| 刘玄德    |   185 |
| 曹孟德    |   197 |
+-----------+-------+
3 rows in set (0.00 sec)

mysql> select name from exam_result order by english+math+chinese limit 3;
+-----------+
| name      |
+-----------+
| 宋公明    |
| 刘玄德    |
| 曹孟德    |
+-----------+
3 rows in set (0.00 sec)

mysql> select name,math from exam_result where math+english+chinese order by math+english+chinese limit 3;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 刘玄德    |   85 |
| 曹孟德    |   60 |
+-----------+------+
3 rows in set (0.00 sec)

mysql> update exam_result set math=math+30 where math+english+chinese order by math+english+chinese limit 3;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select name,math from exam_result where math+english+chinese order by math+english+chinese limit 3;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   95 |
| 刘玄德    |  115 |
| 唐三藏    |   98 |
+-----------+------+
3 rows in set (0.00 sec)

4.将所有同学的语文成绩更新为原来的 2 倍（更新全表的慎用）

mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      70 |
| 刘玄德    |      55 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
7 rows in set (0.00 sec)

mysql> update exam_result set chinese=chinese*2;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |     134 |
| 孙悟空    |     174 |
| 猪悟能    |     176 |
| 曹孟德    |     140 |
| 刘玄德    |     110 |
| 孙权      |     140 |
| 宋公明    |     150 |
+-----------+---------+
7 rows in set (0.00 sec)

五、插入查询结果

1.去重后的结果插入到新表
2.重命名旧表—表2，新表—旧表
3.表2中数据为旧表未去重的数据

mysql> select *from dup_t;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
|  300 | ccc  |
|  300 | ccc  |
+------+------+
9 rows in set (0.00 sec)

mysql> select distinct *from dup_t;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> create table if not exists no_dup_t like dup_t;
Query OK, 0 rows affected (0.04 sec)

mysql> desc no_dup_t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into no_dup_t select distinct *from dup_t;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from no_dup_t;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> rename table dup_t to dup_t_bak,no_dup_t to dup_t;
Query OK, 0 rows affected (0.03 sec)

mysql> select *from dup_t;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> select *from dup_t_bak;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
|  300 | ccc  |
|  300 | ccc  |
+------+------+
9 rows in set (0.00 sec)

六、聚合函数

1.函数

函数说明
COUNT([DISTINCT] expr)返回查询到的数据的 数量
SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义

2.案例

（1）统计多少条记录

mysql> select *from dup_t;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.01 sec)

mysql> select count(*) from dup_t;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

（2）统计qq号有多少

mysql> select count(qq) from student;
+-----------+
| count(qq) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

（3）统计数学成绩的个数（去重的）

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  6 | 孙权      |     140 |   73 |      78 |
|  8 | 孙悟空    |      87 |   98 |      95 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)

mysql> select count(distinct math) from exam_result;
+----------------------+
| count(distinct math) |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.00 sec)

（4）数学成绩求和

mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       359 |
+-----------+
1 row in set (0.00 sec)

（5）求英语成绩的平均分

mysql> select avg(english) from exam_result;
+--------------+
| avg(english) |
+--------------+
|         82.5 |
+--------------+
1 row in set (0.00 sec)

（6）找英语成绩>70分的最小值

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  6 | 孙权      |     140 |   73 |      78 |
|  8 | 孙悟空    |      87 |   98 |      95 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)

mysql> select min(english) from exam_result where english > 70;
+--------------+
| min(english) |
+--------------+
|           78 |
+--------------+
1 row in set (0.00 sec)

3.group by:对指定列进行分组查询

凡是在select后面的列名称，如果后续要进行group by分组，只要是在select中出现的原表中的列名称，也必须在group by 中出现；
group by 是一个分组函数，要筛查的数据列，要考虑分组的时候，当前分组条件相同，分组依据是什么呢？
要同步
having 后面只能是聚合函数
where是最开始筛选数据，having是分完组后的结尾工作，再筛选（对分组结果进行过滤）

瓴翎玲
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
单表增删查改
weixin_44540452的博客
08-09 296
增删查改 新增：myModel.S_Airport.Add(数据); 修改：myModel.Entry(数据).State = System.Data.Entity.EntityState.Modified; 删除：myModel.S_Airport.Remove(数据); 查询：int totalRows = myModel.S_Airport.Count();获取数据条数 查询： 查询的话我们一般查询都是用到layui数据表格了；所以这里涉及到了分页 我们先创建好表格；再去..
表的增删改查代码
qq_38130912的博客
01-17 1142
#include&amp;lt;stdio.h&amp;gt; #include&amp;lt;malloc.h&amp;gt; typedef struct { int data[100]; int length; }Seqlist; //创建表 void creatSeqlist(Seqlist *L) { int i, len, tmp; printf(&quot;请输入表的长度：&quot;); scanf(&
【数据库】表的增删改查（基础 ）
m0_73067372的博客
04-25 2639
所以我们不能插入三位整数，当插入三位整数的时候，实际上插入的是四位有效数字（xxx.x）。我们在插入数据的时候，将数据插入到服务器中，服务器中的数据保存在硬盘中，我们通过查询的时候，在客户端输入sql，通过请求发送给服务区，服务区解析并执行sql把查询的结果从硬盘中读取出来，通过网络响应还给客户端，❓❓❓上面说到，成绩的数据类型是decimal(3,1)不能插入3位整数，但是math+10之后，成绩成为3位数字了，有效数据为4位，那就不符合我们建表时的规定了，为什么程序没有报错，输出了结果？
【MySQL】表的增删查改
QiHai的博客
04-19 1969
本篇笔记主要记录经过DDLSQL语句实现对数据库结构的创建和表的结构的创建后，现在需要对表内所包含的数据进行操作，所以涉及到了DMLSQL语句，并且整理为CRUD：创建、读取、更新、删除。
数据库之表的增删查改（上）
weixin_62490144的博客
11-22 693
数据库增删查改的基础介绍与讲解
增删改查表格
zhuc666的博客
10-25 650
前言        bootstrap的表格样式，有类似EasyUI的表格，也有卡片式表格，放到移动端显示，各有千秋。但是BootStrap自带的表格是没有操作列的，网上的资源不少，但是都是比较单一、零碎，JS、CSS也经常给的不全，自己经过大概一个月左右的时间，把表格封装了一下,希望能分享给大家。         表格封装了3个版本，接下来给大家展示一下样式和代码。.       
增删改查表格（好看且有趣）
电脑高级开关机工程师的博客
05-05 793
Vue增删改查表格（好看且有趣）
c++实现链表增删改查
07-17
c++实现链表增删改查
ssm单表增删改查
05-04
ssm 单表增删改查 ssm 单表增删改查 ssm 单表增删改查
springboot 两表增删改查 用户表和角色表
05-18
springboot 两表增删改查 用户表和角色表
html表格增删改查
04-06
html 增删改该例子演示了利用微软.Net框架的Web服务与天气预报站点的硬件进行数据交换来实现Web天气预报的服务。
js+html写的实现表格的动态增删改查和表单验证
08-04
js+html写的实现表格的动态增删改查和表单验证
表格的增删改查
11-19
case R.id.btn_add: String msg_add = ed_msg.getText().toString(); User user_add = new User(msg_add, null); UserDao userDao_add = new UserDao(user_add, act); if (userDao_add.add() > 0) { Toast.makeText(act.getApplicationContext(), "添加成功", Toast.LENGTH_LONG).show(); } break; case R.id.btn_check: User user_check = new User(null, null); UserDao userDao_check = new UserDao(user_check, act); Cursor cursor = userDao_check.select(); String str = ""; if (cursor.moveToFirst()) { do { int d = cursor.getColumnIndex(MyDatabase.KEY_ID); String id = cursor.getString(d); int columnIndex = cursor .getColumnIndex(MyDatabase.KEY_NAME); String s = cursor.getString(columnIndex); str = str + id + "\t" + s + "\n"; } while (cursor.moveToNext()); tv_msg.setText(str); } break; case R.id.btn_amend: String msg_update = ed_msg.getText().toString(); String id = ed_id.getText().toString(); if (msg_update.equals("")) { Toast.makeText(act, "ID不能为空", Toast.LENGTH_LONG).show(); return; } User user_update = new User(msg_update, id); // int id_update = Integer.parseInt(id); UserDao userDao_update = new UserDao(user_update, act); if (userDao_update.updata() > 0) { Toast.makeText(act.getApplicationContext(), "修改成功", Toast.LENGTH_LONG).show(); } break; case R.id.btn_delete: String id_delete = ed_id.getText().toString(); User user_delete = new User(null, id_delete); if (id_delete.equals("")) { Toast.makeText(act, "ID不能为空", Toast.LENGTH_LONG).show(); return; } UserDao userDao_delete = new UserDao(user_delete, act); if (userDao_delete.delete() > 0) { Toast.makeText(act.getApplicationContext(), "删除成功", Toast.LENGTH_LONG).show(); } ;
bootstrap 带增删改查 表格
09-17
带增删改查的表格，分页、还有很多小控件。方便学习 bootstrap
vue表格增删改查
03-04
该文件主要以vue.js为主的表格渲染，数据交互。充分的表现了表格的增删改查功能，以及vue中input的使用，date日期的选择。
员工表增删改查springjdbc,springboot
10-11
所用技术 jsp + servlet + springboot + springJdbc + druid + mysql 员工列表 ID 姓名 性别 入职日期 所属部门 部门地址 操作 1 张三 男 1982-2-2 开发部 北京 修改|删除 2 李四 男 1982-2-2 开发部 北京 修改|...
Java单链表增删改查的实现
08-15
Java实现单链表的增删改查以及选择、冒泡、反转排序等功能的实现！
实现简单增删查改表单处理(html/MySQL/Node.js)
BelleLinQ的博客
02-28 1532
原生JS、html、mysql、node.js 实现增删查改前后端
mybatisplus多表增删改查
最新发布
03-10
下面是关于MyBatis-Plus多表增删改查的介绍： 1. 多表查询： MyBatis-Plus提供了多种方式进行多表查询，包括使用注解、XML配置文件和Wrapper等方式。你可以通过@TableName注解指定实体类对应的数据库表名，使用@...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值