MySQL增删改查语句、实战案例

已于 2022-10-24 12:56:22 修改
阅读量579 收藏 2
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 1024程序员节
于 2022-07-26 17:28:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_65998623/article/details/125999505
版权

MySQL基础（续）

文章目录

1. mysql数据库操作

1.1 DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

1.1.1 INSERT语句
  • DML操作之增操作insert
//进入数据库shuai；
mysql> use shuai;
Database changed

//一次插入一条数据
mysql> insert into student (id,name,age) value (1,'tom',20);
Query OK, 1 row affected (0.03 sec)

//查看一下插入数据
mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

//一次插入多条数据
mysql> insert into student (id,name,age) values (2,'curry',33),(3,'george',32),(4,'james',36),(5,'jordan',56),(6,'durant',33),(7,'ivring',null);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   20 |
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
|  7 | ivring | NULL |
+----+--------+------+
7 rows in set (0.00 sec)

mysql>
1.1.2 SELECT语句
  • 字段column表示法
表示符代表
*所有字段
as字段别名，如col1 AS alias1 当表名很长时用别名代替
  • 条件判断语句WHERE
操作类型常用操作符
操作符>，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空
条件逻辑操作AND OR NOT
  • ORDER BY：排序，默认为升序（ASC）
ORDER BY语句意义
ORDER BY ‘column_name’根据column_name进行升序排序
ORDER BY ‘column_name’ DESC根据column_name进行降序排序
ORDER BY ’column_name’ LIMIT 2根据column_name进行升序排序 并只取前2个结果
ORDER BY ‘column_name’ LIMIT 1,2根据column_name进行升序排序 并且略过第1个结果取后面的2个结果
  • DML操作之查操作
//查看student表中所有内容
mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   20 |
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
|  7 | ivring | NULL |
+----+--------+------+
7 rows in set (0.00 sec)

//查看student表中name列内容
mysql> select name from student;
+--------+
| name   |
+--------+
| tom    |
| curry  |
| george |
| james  |
| jordan |
| durant |
| ivring |
+--------+
7 rows in set (0.00 sec)

//将student表根据年龄进行升序排序查看
mysql> select * from student order by age;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  7 | ivring | NULL |
|  1 | tom    |   20 |
|  3 | george |   32 |
|  2 | curry  |   33 |
|  6 | durant |   33 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
+----+--------+------+
7 rows in set (0.00 sec)

//根据年龄进行降序排列
mysql> select * from student order by age desc;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  5 | jordan |   56 |
|  4 | james  |   36 |
|  2 | curry  |   33 |
|  6 | durant |   33 |
|  3 | george |   32 |
|  1 | tom    |   20 |
|  7 | ivring | NULL |
+----+--------+------+
7 rows in set (0.00 sec)

//根据年龄进行升序排序并只取前2各结果
mysql> select * from student order by age limit 2;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  7 | ivring | NULL |
|  1 | tom    |   20 |
+----+--------+------+
2 rows in set (0.00 sec)

//根据年龄进行升序排序并且略过第1个结果取后面的2个结果
mysql> select * from student order by age limit 1,2;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   20 |
|  3 | george |   32 |
+----+--------+------+
2 rows in set (0.00 sec)

//查看student表中年龄大于等于32
mysql> select * from student where age >= 32;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
+----+--------+------+
5 rows in set (0.00 sec)

//查看表中年龄大于等于32并且名字为george
mysql> select * from student where age >= 32 and name= 'george'
    -> ;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  3 | george |   32 |
+----+--------+------+
1 row in set (0.00 sec)

//查看表中年龄在32至36中间的
mysql> select * from student where age between 32 and 36;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  6 | durant |   33 |
+----+--------+------+
4 rows in set (0.00 sec)

//查看表中年龄不为空值的
mysql> select * from student where age is not null;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   20 |
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
+----+--------+------+
6 rows in set (0.00 sec)

//查看表中年龄为空值的
mysql> select * from student where age is null;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  7 | ivring | NULL |
+----+--------+------+
1 row in set (0.00 sec)

mysql>
1.1.3 UPDATE语句
  • DML操作之改操作
//查看所有内容
mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   20 |
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
|  7 | ivring | NULL |
+----+--------+------+
7 rows in set (0.00 sec)

//用update语句将ivring年龄从null改为31
mysql> update student set age = 31 where name = 'ivring';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

//查看ivring，age从null改为31
mysql> select * from student where name = 'ivring'
    -> ;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  7 | ivring |   31 |
+----+--------+------+
1 row in set (0.00 sec)

mysql>
1.1.4 DELETE语句
  • DML操作之删操作delete
//查看所有内容
mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   20 |
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
|  7 | ivring |   31 |
+----+--------+------+
7 rows in set (0.00 sec)

//删除id为1表记录
mysql> delete from student where id = 1;
Query OK, 1 row affected (0.00 sec)

//查看已被删除
mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
|  7 | ivring |   31 |
+----+--------+------+
6 rows in set (0.00 sec)

//删除整张表的内容
mysql> delete from student;
Query OK, 6 rows affected (0.01 sec)

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

mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>
1.1.5 TRUNCATE语句
  • truncate与delete的区别：
语句类型特点
deleteDELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间
truncate删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表
  • DML操作之删操作truncate
//查看所有内容
mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | curry  |   33 |
|  3 | george |   32 |
|  4 | james  |   36 |
|  5 | jordan |   56 |
|  6 | durant |   33 |
|  7 | ivring | NULL |
+----+--------+------+
6 rows in set (0.00 sec)

//删除student表
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

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

mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

1.2 DCL操作

1.2.1 创建授权GRANT
  • 权限类型(priv_type)
权限类型代表什么？
ALL所有权限
SELECT读取内容的权限
INSERT插入内容的权限
UPDATE更新内容的权限
DELETE删除内容的权限
  • 指定要操作的对象db_name.table_name
表示方式意义
.所有库的所有表
db_name指定库的所有表
db_name.table_name指定库的指定表

WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shuai              |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//授权shuai用户在数据库本机上登录访问所有数据库
mysql> grant all on *.* to 'shuai'@'localhost' identified by 'LIshuai123!!!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on *.* to 'shuai'@'127.0.0.1' identified by 'LIshuai123!!!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权shuai用户在192.168.183.133上远程登录访问shuai数据库
mysql> grant all on shuai.* to 'shuai'@'192.168.183.133' identified by 'LIshuai123!!!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权shuai用户在所有位置上远程登录访问shuai数据库
mysql> grant all on *.* to 'shuai'@'%' identified by 'LIshuai123!!!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
1.2.2 查看授权
//查看当前登录用户的授权信息
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)


//查看指定用户shuai的授权信息
mysql> show grants for shuai;
+--------------------------------------------+
| Grants for shuai@%                         |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'shuai'@'%' |
+--------------------------------------------+
1 row in set (0.01 sec)

mysql> show grants for 'shuai'@'localhost';
+----------------------------------------------------+
| Grants for shuai@localhost                         |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'shuai'@'localhost' |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'shuai'@'127.0.0.1';
+----------------------------------------------------+
| Grants for shuai@127.0.0.1                         |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'shuai'@'127.0.0.1' |
+----------------------------------------------------+
1 row in set (0.00 sec)
1.2.3 取消授权REVOKE

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

  • GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
  • 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> revoke all on *.* from 'shuai'@'192.168.183.133';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

2. 实战案例

  1. 搭建mysql服务

    //配置mysqlyum源
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
URL transformed to HTTPS due to an HSTS policy
--2022-07-26 16:29:10--  
······
mysql57-community-r 100%[===================>]  24.95K  22.9KB/s    in 1.1s    

2022-07-26 16:29:13 (22.9 KB/s) - 'mysql57-community-release-el7-10.noarch.rpm' saved [25548/25548]

[root@localhost ~]# rpm -Uvh  mysql57-community-release-el7-10.noarch.rpm
warning: mysql57-community-release-el7-10.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-10 ################################# [100%]

//禁用mysql
[root@localhost ~]# yum  module disable mysql
Failed to set locale, defaulting to C.UTF-8
MySQL Connectors Community                      1.2 kB/s |  51 kB     00:43  
······
Is this ok [y/N]: y
Complete!

//安装mysql
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck
Failed to set locale, defaulting to C.UTF-8
······
 pkgconf-pkg-config-1.4.2-1.el8.x86_64                                         

Complete!

//设置开机自启
[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor pres>
   Active: active (running) since Tue 2022-07-26 16:39:34 CST; 8s ago
······

//确保端口监听
[root@localhost ~]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port     Peer Address:Port  Process  
LISTEN   0        128              0.0.0.0:22            0.0.0.0:*              
LISTEN   0        128                 [::]:22               [::]:*              
LISTEN   0        80                     *:3306                *:*  

//在日志文件中过滤临时密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-26T08:39:32.340208Z 1 [Note] A temporary password is generated for root@localhost: S8kt*-aJvt/D

//登录mysql
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

//修改密码
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql123!';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

//卸载yum源，避免自动升级
[root@localhost ~]# ls /etc/yum.repos.d/
ls.repo  mysql-community-source.repo  mysql-community.repo
[root@localhost ~]# rpm -e mysql57-community-release
[root@localhost ~]# ls /etc/yum.repos.d/
ls.repo
[root@localhost ~]#

  2. 创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

    mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

    mysql> create database lishuai;
Query OK, 1 row affected (0.01 sec)

mysql> use lishuai;
Database changed
mysql> create table student (id int not null,name varchar(100) not null,age tinyint,primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql>

  3. 查看下该新建的表有无内容（用select语句）

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

mysql>

  4. 往新建的student表中插入数据（用insert语句），结果应如下所示：

    +----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

    mysql> insert into student (id,name,age) VALUES (1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

  5. 修改lisi的年龄为50

    mysql> update student set age = 50 where name = 'lisi'
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

  6. 以age字段降序排序

    mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.00 sec)

  7. 查询student表中年龄最小的3位同学跳过前2位

    mysql> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
| 10 | qiuyi       |   15 |
|  1 | tom         |   20 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
3 rows in set (0.00 sec)

  8. 查询student表中年龄最大的4位同学

    mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | lisi      |   50 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  3 | wangqing  |   25 |
+----+-----------+------+
4 rows in set (0.00 sec)

  9. 查询student表中名字叫zhangshan的记录

    mysql> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.00 sec)

  10. 查询student表中名字叫zhangshan且年龄大于20岁的记录

    mysql> select * from student where name = 'zhangshan' and age > 20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.00 sec)

  11. 查询student表中年龄在23到30之间的记录

    mysql> select * from student where age between 23 and 30;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.00 sec)

  12. 修改wangwu的年龄为100

    mysql> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'wangwu';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  9 | wangwu |  100 |
+----+--------+------+
1 row in set (0.00 sec)

  13. 删除student中名字叫zhangshan且年龄小于等于20的记录

    mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> delete from student where name = 'zhangshan' and age <= 20;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)
咖啡调调。
关注
专栏目录
MYSQL重要知识点及增删改查SQL语句案例
08-12
本章主要说的是MYSQL数据库的增删改查操作SQL语言，各种案例语言都有，对于您的数据库查询非常有帮助，简单明了。
mysql基础二
Eternity_zzh的博客
07-26 674
mysql基础二
【MYSQL表的增删改查（进阶）】
最新发布
2303_77535762的博客
09-20 825
【MYSQL表的增删改查（进阶）】
mysql 增删查改与案例
系统运维
01-04 171
1、连接Mysql 格式：mysql-h主机地址-u用户名－p用户密码 1、连接到本机上的MYSQL。 首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql-uroot-p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。 如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL...
MySQL中增删改查的例子
lihuilin521的博客
04-19 407
MySQL中增加语句如图下列是我原本的表：增加语句：insert into depe(dnam,dma,dgae)values(“wo”,“ni”,19); 删除语句：delete from depe where did=3; 修改语句：update set depe dnam=“aini” where did=2; 查找语句：select * from 表名； ...
mysql的增删改查例子_MySQL_增删改查实例
weixin_39959126的博客
01-25 201
实现如下的学生信息管理系统：对应的表学生基本信息表实现如下的学生信息管理系统：对应的表学生基本信息表(表一)学号 姓名 性别 班级 年龄0001 mark male ...
play框架的简单增删改查
12-05
本项目是针对初学者设计的一个实战教程，主要目标是展示如何在Eclipse开发环境中使用Play框架与MySQL数据库进行基本的增删改查操作。 1. **Play框架介绍** - 架构：Play采用反应式编程模型，强调异步I/O和非阻塞...
vue搭配ajax实现数据库的增删改查,vue实战(一):利用vue与ajax实现增删改查
weixin_42525798的博客
08-05 1527
vue实战(一):利用vue与ajax实现增删改查：var getUrl='${ctx}/distributor/getRate';var saveUrl= '${ctx}/distributor/saveRate';var rate = [];var vm ;var rates ='';$.ajax({url : getUrl,async : false,dataType: 'json',suc...
MySQL数据库增删改查性能调优：实战案例分享，提升数据库操作效率
[MySQL数据库增删改查性能调优：实战案例分享，提升数据库操作效率](https://img-blog.csdnimg.cn/img_convert/019dcf34fad68a6bea31c354e88fd612.png) # 1. MySQL数据库增删改查性能调优概述 MySQL数据库作为一款...
jsp增删改查源代码
08-31
本资源"jsp增删改查源代码"提供了一个基于JSP、Java、Web和MySQL数据库的简单CRUD（Create、Read、Update、Delete）操作示例，对于初学者或者希望深入理解Web应用开发的人来说非常有价值。 首先，我们来详细解释...
【MyBatis持久层框架】配置文件实现增删改查实战案例
zhangxia_的博客
01-30 2826
我们为什么使用 MyBatis 开发呢？不难理解，MyBatis 作为一款优秀的持久层框架，支持自定义 sql，存储过程以及高级映射，它几乎免除了所有的 JBDC 代码以及设置参数和获取结果集的工作。解决了使用 JBDC 基础性的代码操作数据库时面临的 Java 代码的硬编码和操作繁琐的问题。
mysql的基本操作语句,增删改查
08-01
此文档对mysql数据库的基本操作很齐全，包括数据库的创建删除，查看等；已经对表的增删改查
JFinal+mysql增删改查实例
05-23
JFinal+mysql增删改查实例 mysql数据库，访问地址都在里面，导入即可运行！
jsp+mysql实现增删改查的简单例子
07-02
jsp+mysql实现增删改查的简单例子，可以直接导入myeclipse中运行，用tomcat服务器。
JSP页面连接mysql实现增删改查简单实例
01-14
jsp页面连接mysql实现增删改查简单小例子，非常适合初学者。
mysql初阶 增删查改操作详解(包括示例)
chara9885的博客
10-27 947
mysql初阶的
MySql数据库增删改查示例
a166244的博客
02-01 168
——————–建库语句———————————————————– create database 表名 default character set utf8 collate utf8_general_ci; ——————–建表语句———————————————————– 示例1： create table com ( id int primary k...
MySQL增删改查操作实例
weixin_43363034的博客
11-18 371
学习MySQL中对于表这种MySQL对象的增删改查操作后，列出一些实例，在此用于记录对应功能的语法细节，便于日后遗忘时查看。 需求：插入一条学生信息 1.插入一条数据 INSERT INTO t_student (name) VALUES('陆小凤') 2.插入多条数据 INSERT INTO t_student(name,email,age) VALUES('A','B@',18),('B',...
P57 DDL和DML的综合案例
weixin_54671087的博客
12-01 246
5.DDL和DML的综合案例 #5.综合案例 # 1、创建数据库test01_library CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET'utf8'; USE test01_library; # 2、创建表 books，表结构如下： /* 字段名 字段说明 数据类型 id 书编号 INT name 书名 VARCHAR(50) authors 作者 VARCHAR(100) price 价格 FL
SpringBoot+Mybatis实战：增删改查步骤详解
"这篇实战教程主要讲解如何使用SpringBoot与Mybatis进行增删改查操作。作者通过自己的学习经历和实战经验，分享了创建SpringBoot项目、配置数据库、调整项目目录结构以及编写增删改查功能的详细步骤，旨在帮助读者...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值