mysql
mysql基础
目录
1. DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
1.1 insert语句
mysql> select * from Linux;
+----+----------+------+--------+
| id | name | age | salary |
+----+----------+------+--------+
| 1 | zhangsan | 20 | 500 |
+----+----------+------+--------+
1 row in set (0.00 sec)
mysql> insert into Linux(id,name,age,salary) values(2,'lisi',21,6000),(2,'wangwu',22,7000),(4,'haha',20,6000);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from Linux;
+----+----------+------+--------+
| id | name | age | salary |
+----+----------+------+--------+
| 1 | zhangsan | 20 | 500 |
| 2 | lisi | 21 | 6000 |
| 2 | wangwu | 22 | 7000 |
| 4 | haha | 20 | 6000 |
+----+----------+------+--------+
4 rows in set (0.00 sec)
mysql>
1.2 select语句
字段column表示法
| 标识符 | 代表什么？ |
|---|---|
| * | 所有字段 |
| as | 字段别名，如cold1 AS alias1，当表名很长时用别名代替 |
mysql> select name,id from Linux;
+----------+----+
| name | id |
+----------+----+
| zhangsan | 1 |
| lisi | 2 |
| wangwu | 2 |
| haha | 4 |
| lisi | 5 |
| wangwu | 6 |
| haha | 7 |
+----------+----+
7 rows in set (0.00 sec)
mysql> select * from Linux;
+----+----------+------+--------+
| id | name | age | salary |
+----+----------+------+--------+
| 1 | zhangsan | 20 | 500 |
| 2 | lisi | 21 | 6000 |
| 2 | wangwu | 22 | 7000 |
| 4 | haha | 20 | 6000 |
| 5 | lisi | 25 | 6000 |
| 6 | wangwu | 28 | 7000 |
| 7 | haha | 30 | 6000 |
+----+----------+------+--------+
7 rows in set (0.00 sec)
mysql>
mysql> select name as 姓名,salary as 薪资 from Linux;
+----------+--------+
| 姓名 | 薪资 |
+----------+--------+
| zhangsan | 500 |
| lisi | 6000 |
| wangwu | 7000 |
| haha | 6000 |
| lisi | 6000 |
| wangwu | 7000 |
| haha | 6000 |
+----------+--------+
7 rows in set (0.00 sec)
mysql>
条件判断语句WHERE
| 操作类型 | 常用操作符 |
|---|---|
| 操作符 | >，<，>=，<=，=，!=BETWEEN column# AND column#LIKE：模糊匹配RLIKE：基于正则表达式进行模式匹配IS NOT NULL：非空IS NULL：空 |
| 条件逻辑操作 | and or not |
mysql> select * from Linux where id = 4;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 4 | haha | 20 | 6000 |
+----+------+------+--------+
1 row in set (0.00 sec)
mysql> select * from Linux where id > 4;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 5 | lisi | 25 | 6000 |
| 6 | wangwu | 28 | 7000 |
| 7 | haha | 30 | 6000 |
+----+--------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from Linux where id >= 4;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 4 | haha | 20 | 6000 |
| 5 | lisi | 25 | 6000 |
| 6 | wangwu | 28 | 7000 |
| 7 | haha | 30 | 6000 |
+----+--------+------+--------+
4 rows in set (0.00 sec)
mysql> select * from Linux where id < 4;
+----+----------+------+--------+
| id | name | age | salary |
+----+----------+------+--------+
| 1 | zhangsan | 20 | 500 |
| 2 | lisi | 21 | 6000 |
| 2 | wangwu | 22 | 7000 |
+----+----------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from Linux where age between 21 and 28;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 2 | lisi | 21 | 6000 |
| 2 | wangwu | 22 | 7000 |
| 5 | lisi | 25 | 6000 |
| 6 | wangwu | 28 | 7000 |
+----+--------+------+--------+
4 rows in set (0.00 sec)
mysql>
mysql> select * from Linux where name like '%u';
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 2 | wangwu | 22 | 7000 |
| 6 | wangwu | 28 | 7000 |
+----+--------+------+--------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from Linux;
+----+----------+------+--------+---------+
| id | name | age | salary | company |
+----+----------+------+--------+---------+
| 1 | zhangsan | 20 | 500 | NULL |
| 2 | lisi | 21 | 6000 | NULL |
| 2 | wangwu | 22 | 7000 | NULL |
| 4 | haha | 20 | 6000 | NULL |
| 5 | lisi | 25 | 6000 | NULL |
| 6 | wangwu | 28 | 7000 | NULL |
| 7 | haha | 30 | 6000 | NULL |
+----+----------+------+--------+---------+
7 rows in set (0.00 sec)
mysql> update Linux set company = ' ' where id = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from Linux;
+----+----------+------+--------+---------+
| id | name | age | salary | company |
+----+----------+------+--------+---------+
| 1 | zhangsan | 20 | 500 | NULL |
| 2 | lisi | 21 | 6000 | NULL |
| 2 | wangwu | 22 | 7000 | NULL |
| 4 | haha | 20 | 6000 | NULL |
| 5 | lisi | 25 | 6000 | NULL |
| 6 | wangwu | 28 | 7000 | NULL |
| 7 | haha | 30 | 6000 | |
+----+----------+------+--------+---------+
7 rows in set (0.00 sec)
mysql>
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个结果 |
mysql> select * from Linux order by age;
+----+----------+------+--------+---------+
| id | name | age | salary | company |
+----+----------+------+--------+---------+
| 1 | zhangsan | 20 | 500 | NULL |
| 4 | haha | 20 | 6000 | NULL |
| 2 | lisi | 21 | 6000 | NULL |
| 2 | wangwu | 22 | 7000 | NULL |
| 5 | lisi | 25 | 6000 | NULL |
| 6 | wangwu | 28 | 7000 | NULL |
| 7 | haha | 30 | 6000 | |
+----+----------+------+--------+---------+
7 rows in set (0.00 sec)
mysql> select * from Linux order by age desc;
+----+----------+------+--------+---------+
| id | name | age | salary | company |
+----+----------+------+--------+---------+
| 7 | haha | 30 | 6000 | |
| 6 | wangwu | 28 | 7000 | NULL |
| 5 | lisi | 25 | 6000 | NULL |
| 2 | wangwu | 22 | 7000 | NULL |
| 2 | lisi | 21 | 6000 | NULL |
| 1 | zhangsan | 20 | 500 | NULL |
| 4 | haha | 20 | 6000 | NULL |
+----+----------+------+--------+---------+
7 rows in set (0.00 sec)
mysql> select * from Linux order by age limit 2;
+----+----------+------+--------+---------+
| id | name | age | salary | company |
+----+----------+------+--------+---------+
| 1 | zhangsan | 20 | 500 | NULL |
| 4 | haha | 20 | 6000 | NULL |
+----+----------+------+--------+---------+
2 rows in set (0.00 sec)
mysql> select * from Linux order by age limit 1,3;
+----+--------+------+--------+---------+
| id | name | age | salary | company |
+----+--------+------+--------+---------+
| 4 | haha | 20 | 6000 | NULL |
| 2 | lisi | 21 | 6000 | NULL |
| 2 | wangwu | 22 | 7000 | NULL |
+----+--------+------+--------+---------+
3 rows in set (0.00 sec)
mysql>
1.3update语句
mysql> select * from Linux;
+----+----------+------+--------+---------+
| id | name | age | salary | company |
+----+----------+------+--------+---------+
| 1 | zhangsan | 20 | 500 | NULL |
| 2 | lisi | 21 | 6000 | NULL |
| 2 | wangwu | 22 | 7000 | NULL |
| 4 | haha | 20 | 6000 | NULL |
| 5 | lisi | 25 | 6000 | NULL |
| 6 | wangwu | 28 | 7000 | NULL |
| 7 | haha | 30 | 6000 | |
+----+----------+------+--------+---------+
7 rows in set (0.00 sec)
mysql> update Linux set company = 'runtime.com' where name = 'haha';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from Linux;
+----+----------+------+--------+-------------+
| id | name | age | salary | company |
+----+----------+------+--------+-------------+
| 1 | zhangsan | 20 | 500 | NULL |
| 2 | lisi | 21 | 6000 | NULL |
| 2 | wangwu | 22 | 7000 | NULL |
| 4 | haha | 20 | 6000 | runtime.com |
| 5 | lisi | 25 | 6000 | NULL |
| 6 | wangwu | 28 | 7000 | NULL |
| 7 | haha | 30 | 6000 | runtime.com |
+----+----------+------+--------+-------------+
7 rows in set (0.00 sec)
mysql>
1.4 delete语句
mysql> create table sutdent(id int not null primary key auto_increment,name varchar(50) not null,age tinyint(2));
Query OK, 0 rows affected (0.01 sec)
mysql> desc sutdent;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | tinyint(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
mysql> insert sutdent(name,age) values('zhangsan',18),('lisi',18),('wangwu',20),('qq',21),('ll',22),('mm',23);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from sutdent;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 18 |
| 3 | wangwu | 20 |
| 4 | qq | 21 |
| 5 | ll | 22 |
| 6 | mm | 23 |
+----+----------+------+
6 rows in set (0.00 sec)
mysql> delete from sutdent where id =3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from sutdent;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 18 |
| 4 | qq | 21 |
| 5 | ll | 22 |
| 6 | mm | 23 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql>
1.5 truncate语句
truncate与delete区别
| 语句类型 | 特点 |
|---|---|
| delete | DELETE删除表内容时仅删除内容，但会保留表结构，DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间 |
| truncate | 删除表中所有数据，且无法恢复，表结构、约束和索引等保持不变，新添加的行计数值重置为初始值，执行速度比DELETE快，且使用的系统和事务日志资源少，通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据，不能用于加入了索引视图的表 |
2. DCL操作
2.1 创建授权grant
权限类型（priv_type）
| 权限类型 | 代表什么？ |
|---|---|
| ALL | 所有权限 |
| SELECT | 读取内容的权限 |
| INSERT | 插入内容的权限 |
| UPDATE | 更新内容的权限 |
| DELETE | 删除内容的权限 |
mysql> grant all on *.* to 'root'@'192.168.29.137' identified by 'marui';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
指定要操作的对象db_name.table_name
| 表示方法 | 意义 |
|---|---|
| * . * | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.table_name | 指定库的指定表 |
WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。
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)
mysql> show grants for 'root'@'192.168.29.137';
+--------------------------------------------------------+
| Grants for root@192.168.29.137 |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.29.137' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> flush privileges;（刷新权限）
Query OK, 0 rows affected (0.00 sec)
mysql>
2.3 取消授权revoke
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
- GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
- 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
实战案例
- 1.搭建mysql服务
[root@mr ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
URL transformed to HTTPS due to an HSTS policy
--2022-07-26 16:41:52-- https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 104.108.171.133, 2600:140b:2:59c::2e31, 2600:140b:2:5ad::2e31
Connecting to dev.mysql.com (dev.mysql.com)|104.108.171.133|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-26 16:41:53-- https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.47.207.44
Connecting to repo.mysql.com (repo.mysql.com)|23.47.207.44|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm.1’
mysql57-community 100%[=============>] 25.08K --.-KB/s in 0.1s
2022-07-26 16:41:54 (196 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm.1’ saved [25680/25680]
[root@mr ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
package mysql57-community-release-el7-11.noarch is already installed
[root@mr ~]# yum module disable mysql
Last metadata expiration check: 4:01:58 ago on Tue 26 Jul 2022 12:40:23 PM CST.
Dependencies resolved.
========================================================================
Package Architecture Version Repository Size
========================================================================
Disabling modules:
mysql
Transaction Summary
========================================================================
Is this ok [y/N]: y
Complete!
[root@mr ~]# dnf -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
Last metadata expiration check: 4:02:16 ago on Tue 26 Jul 2022 12:40:23 PM CST.
Dependencies resolved.
========================================================================
Package Arch Version Repository Size
========================================================================
Installing:
mysql-community-client x86_64 5.7.39-1.el7 mysql57-community 28 M
mysql-community-common x86_64 5.7.39-1.el7 mysql57-community 311 k
mysql-community-devel x86_64 5.7.39-1.el7 mysql57-community 4.2 M
mysql-community-server x86_64 5.7.39-1.el7 mysql57-community 178 M
Installing dependencies:
mysql-community-libs x86_64 5.7.39-1.el7 mysql57-community 2.6 M
ncurses-compat-libs x86_64 6.1-7.20180224.el8
baseos 331 k
Transaction Summary
========================================================================
Install 6 Packages
Total download size: 213 M
Installed size: 917 M
Downloading Packages:
(1/6): ncurses-compat-libs-6.1-7.201802 836 kB/s | 331 kB 00:00
(2/6): mysql-community-common-5.7.39-1. 192 kB/s | 311 kB 00:01
(3/6): mysql-community-devel-5.7.39-1.e 1.4 MB/s | 4.2 MB 00:03
(4/6): mysql-community-libs-5.7.39-1.el 1.0 MB/s | 2.6 MB 00:02
(5/6): mysql-community-client-5.7.39-1. 1.5 MB/s | 28 MB 00:18
(6/6): mysql-community-server-5.7.39-1. 3.9 MB/s | 178 MB 00:46
------------------------------------------------------------------------
Total 4.2 MB/s | 213 MB 00:50
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : mysql-community-common-5.7.39-1.el7.x86_64 1/6
Installing : mysql-community-libs-5.7.39-1.el7.x86_64 2/6
Running scriptlet: mysql-community-libs-5.7.39-1.el7.x86_64 2/6
Installing : ncurses-compat-libs-6.1-7.20180224.el8.x86_6 3/6
Installing : mysql-community-client-5.7.39-1.el7.x86_64 4/6
Running scriptlet: mysql-community-server-5.7.39-1.el7.x86_64 5/6
Installing : mysql-community-server-5.7.39-1.el7.x86_64 5/6
Running scriptlet: mysql-community-server-5.7.39-1.el7.x86_64 5/6
Installing : mysql-community-devel-5.7.39-1.el7.x86_64 6/6
Running scriptlet: mysql-community-devel-5.7.39-1.el7.x86_64 6/6
[/usr/lib/tmpfiles.d/mysql.conf:23] Line references path below legacy directory /var/run/, updating /var/run/mysqld → /run/mysqld; please update the tmpfiles.d/ drop-in file accordingly.
Verifying : ncurses-compat-libs-6.1-7.20180224.el8.x86_6 1/6
Verifying : mysql-community-client-5.7.39-1.el7.x86_64 2/6
Verifying : mysql-community-common-5.7.39-1.el7.x86_64 3/6
Verifying : mysql-community-devel-5.7.39-1.el7.x86_64 4/6
Verifying : mysql-community-libs-5.7.39-1.el7.x86_64 5/6
Verifying : mysql-community-server-5.7.39-1.el7.x86_64 6/6
Installed products updated.
Installed:
mysql-community-client-5.7.39-1.el7.x86_64
mysql-community-common-5.7.39-1.el7.x86_64
mysql-community-devel-5.7.39-1.el7.x86_64
mysql-community-libs-5.7.39-1.el7.x86_64
mysql-community-server-5.7.39-1.el7.x86_64
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
Complete!
[root@mr ~]#
[root@mr ~]# systemctl restart mysqld.service
[root@mr ~]# systemctl enable mysqld
[root@mr ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; ven>
Active: active (running) since Tue 2022-07-26 16:45:47 CST; 13s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 3097 (mysqld)
Tasks: 27 (limit: 12221)
Memory: 304.2M
CGroup: /system.slice/mysqld.service
└─3097 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysq>
Jul 26 16:45:45 mr systemd[1]: Starting MySQL Server...
Jul 26 16:45:47 mr systemd[1]: Started MySQL Server.
[root@mr ~]# ss -anltup | grep mysql
tcp LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=3097,fd=21))
[root@mr ~]#
[root@mr ~]# grep "password" /var/log/mysqld.log
2022-07-26T08:45:45.841930Z 1 [Note] A temporary password is generated for root@localhost: 2vraj;I1kz(Z
[root@mr ~]# 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> 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 'marui';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@mr ~]# rpm -e mysql57-community-release
- 2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：
mysql> create database marui;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| marui |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use marui;
Database changed
mysql> create table student(id int(11) primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.00 sec)
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.00 sec)
mysql>
- 3.查看下该新建的表有无内容（用select语句）
mysql> select * from student;
Empty set (0.00 sec)
mysql>
- 4.往新建的student表中插入数据（用insert语句），结果应如下所示：
mysql> insert into student(name,age) values('tom',20),('jerry',23),('wangqiing',25),('sean',28),('zhangsan',26),('zhangsan',20),('lisi',null),('chenshhuo',10),('wangwu',3),('qiuyi',15),('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 | zhangsan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>
- 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 where name = 'lisi';
+----+------+------+
| id | name | age |
+----+------+------+
| 7 | lisi | 50 |
+----+------+------+
1 row in set (0.00 sec)
mysql>
- 6.以age字段降序排序
mysql> select * from student order by age desc;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangsan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>
- 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)
mysql>
- 8.查询student表中年龄最大的4位同学
mysql> select * from student order by age desc limit 4;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 3 | wangqing | 25 |
+----+----------+------+
4 rows in set (0.00 sec)
- 9.查询student表中名字叫zhangshan的记录
mysql> select * from student where name = 'zhangsan';
+----+----------+------+
| id | name | age |
+----+----------+------+
| 5 | zhangsan | 26 |
+----+----------+------+
1 row in set (0.00 sec)
mysql>
- 10.查询student表中名字叫zhangshan且年龄大于20岁的记录
mysql> select * from student where name = 'zhangsan' and age > 20;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 5 | zhangsan | 26 |
+----+----------+------+
1 row in set (0.00 sec)
mysql>
- 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 | zhangsan | 26 |
+----+----------+------+
4 rows in set (0.00 sec)
mysql>
- 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)
mysql>
- 13.删除student中名字叫zhangshan且年龄小于等于20的记录
mysql> delete from student where name = 'zhangshan' and age >= 20;
Query OK, 1 rows affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 6 | zhangsan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· winform 绘制太阳，地球，月球 运作规律
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· AI 智能体引爆开源社区「GitHub 热点速览」
· 写一个简单的SQL生成工具