mysql数据库基础，不建议使用。mysq

文章由LinuxBoy分享于2023-03-24 08:03:49热评（162）

mysql数据库基础，不建议使用。mysq

mysql 基础

mysql 基础
- DML操作
  - INSERT语句
  - SELECT语句
  - update语句
  - delete语句
  - truncate语句
- DCL操作
  - 创建授权grant
  - 查看授权
  - 取消授权REVOKE
实战案例

DML操作

INSERT语句

[root@lnh ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.38 MySQL Community Server (GPL)

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> insert into tsb(id,name,age) value(1,'zhangsan',20);
Query OK, 1 row affected (0.00 sec)  //一次插入一条记录
mysql> insert into tsb(id,name,age) values(1,'lisi',20),(2,'wangwu',21),(3,'laoliu',21),(2,'zhaoqi',null);
Query OK, 4 rows affected (0.00 sec)  //多次插入一条记录
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from tsb;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
|  2 | zhaoqi   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)
//查询读取数据

SELECT语句

mysql> select * from tsb;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
|  2 | zhaoqi   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)
//查询tsb的数据
mysql> select name  from tsb;
+----------+
| name     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| laoliu   |
| zhaoqi   |
+----------+
5 rows in set (0.01 sec)
//查看tsb的名字
mysql> select * from tsb order by age;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | zhaoqi   | NULL |
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
+----+----------+------+
5 rows in set (0.00 sec)
//查询tsb以年龄进行升序排序
mysql> select * from tsb order by age desc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
|  2 | zhaoqi   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)
//查询tsb以年龄进行降序排序
mysql> select * from tsb order by age limit 2;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | zhaoqi   | NULL |
|  1 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)
//查询tsb以年龄进行升序排序并只取前2个结果
mysql> select * from tsb order by age limit 1,2;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
+----+----------+------+
2 rows in set (0.00 sec)
//查询tsb以年龄进行升序排序并且略过第1个结果取后面的2个结果
mysql> select * from tsb where age=20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
+----+----------+------+
2 rows in set (0.00 sec)
//以这个年龄段的进行查询
mysql> select * from tsb where age=20 and name='zhangsan';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
+----+----------+------+
1 row in set (0.00 sec)
//以这个年龄段和名字进行查询
mysql> select * from tsb where age=20 or name='zhangsan';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
+----+----------+------+
2 rows in set (0.00 sec)
//以这个年龄段或名字进行查询
mysql> select * from tsb where age between 20 and 21;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
+----+----------+------+
4 rows in set (0.00 sec)
//查询这之间的
mysql> select * from tsb where age is not null;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
+----+----------+------+
4 rows in set (0.00 sec)
//年龄不等于null的
mysql> select * from tsb where age is  null;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | zhaoqi | NULL |
+----+--------+------+
1 row in set (0.00 sec)
//年龄等于这个null的
mysql> select name as '名字',id as '序列',age as '年龄' from tsb;
+----------+--------+--------+
| 名字     | 序列   | 年龄   |
+----------+--------+--------+
| zhangsan |      1 |     20 |
| lisi     |      1 |     20 |
| wangwu   |      2 |     21 |
| laoliu   |      3 |     21 |
| zhaoqi   |      2 |   NULL |
+----------+--------+--------+
5 rows in set (0.00 sec)
//临时的命名中文名
mysql> select * from tsb where name like '%s%';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
+----+----------+------+
2 rows in set (0.00 sec)
//查询这个表里面的名字首字母和结尾字母是s的
mysql> select * from tsb where name like 'z%';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  2 | zhaoqi   | NULL |
+----+----------+------+
2 rows in set (0.00 sec)
//查询这个表里面的名字首字母是z的
mysql> select * from tsb where name like '%n';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
+----+----------+------+
1 row in set (0.01 sec)
//查询这个表里面的名字字母结尾是n的

update语句

mysql> select * from tsb;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  1 | lisi     |   20 |
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
|  2 | zhaoqi   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

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

mysql> select * from tsb where name = 'zhangsan';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   30 |
+----+----------+------+
1 row in set (0.00 sec)
//修改表里面的数据

delete语句

//删除可以恢复

mysql> select * from tsb;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   30 |
|  1 | lisi     |   20 |
|  2 | wangwu   |   21 |
|  3 | laoliu   |   21 |
|  2 | zhaoqi   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> delete from tsb where id = 2;
Query OK, 2 rows affected (0.00 sec)
//删除这个id的数据
mysql> select * from tsb;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   30 |
|  1 | lisi     |   20 |
|  3 | laoliu   |   21 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> delete from tsb;
Query OK, 3 rows affected (0.00 sec)
//删除这个表的内容，但是不删除这个表
mysql> select * from tsb;
Empty set (0.00 sec)

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

truncate语句

//删除不可以恢复
| 语句类型 | 特点 |
| :-------- | :--------| :------: |
| delete | DELETE删除表内容时仅删除内容，但会保留表构
DELETE语句每次删除一行
并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间|
|truncate | 删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表 |

mysql> select * from tsb;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | lisi     |   20 |
|  3 | wangwu   |   21 |
|  4 | laoliu   |   21 |
|  5 | zhaoqi   | NULL |
|  1 | zhangsan |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> truncate tsb;
Query OK, 0 rows affected (0.00 sec)

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

mysql> desc tsb;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
//删除不可以恢复，但是也不删除表

DCL操作

创建授权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 |
| sys                |
| tushanbu           |
+--------------------+
5 rows in set (0.01 sec)
//查看数据库
mysql> create user 'lnh'@'192.168.222.250' identified by 'xbz1';
Query OK, 0 rows affected (0.00 sec)
//创建用户
mysql>  grant all on * .* to 'lnh'@'localhost' identified by 'xbz1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on * .* to 'lnh'@'127.0.0.1' identified by 'xbz1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
//授权lnh用户在数据库本机上登录访问所有数据库
mysql> grant all on tushanbu.* to 'lnh'@'192.168.222.250' identified by 'xbz1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
//授权lnh用户在192.168.222.250上远程登录访问tushanbu数据库
mysql> grant all on * .* to 'lnh'@'%' identified by 'xbz1';
Query OK, 0 rows affected, 1 warning (0.01 sec)
//授权lnh用户在所有位置上远程登录访问tushanbu数据库
[root@lnh ~]# mysql -ulnh -pxbz1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.38 MySQL Community Server (GPL)

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> use tushanbu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
//进入新创建的数据库
mysql> create table xbz(id int not null,name varchar(10) not null,age int);
Query OK, 0 rows affected (0.05 sec)
//创建表

//与刚刚授权的数据库连接成功

//对数据库里面表进行修改

mysql> desc xbz;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| lv    | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
//可以看见结构修改成功

查看授权

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

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

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

取消授权REVOKE

在root数据库里面进行操作

mysql>  revoke all on *.* from 'lnh'@'192.68.222.250';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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

GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

实战案例

搭建mysql服务

配置mysql的yum源:
[root@lnh ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-25 17:03:13--  http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 184.29.181.90, 2600:1402:b800:1194::2e31, 2600:1402:b800:118f::2e31
Connecting to dev.mysql.com (dev.mysql.com)|184.29.181.90|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-25 17:03:13--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|184.29.181.90|: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-25 17:03:14--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 184.30.152.230
Connecting to repo.mysql.com (repo.mysql.com)|184.30.152.230|: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’

mysql57-community- 100%[================>]  25.08K   125KB/s    in 0.2s    

2022-07-25 17:03:15 (125 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]
[root@lnh ~]# 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%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]
[root@lnh ~]# 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%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]
[root@lnh ~]# dnf module disable mysql    //禁用mysql
MySQL Connectors Community                   47 kB/s |  49 kB     00:01    
MySQL Tools Community                       257 kB/s | 651 kB     00:02    
MySQL 5.7 Community Server                  817 kB/s | 2.6 MB     00:03    
Dependencies resolved.
============================================================================
 Package          Architecture    Version            Repository        Size
============================================================================
Disabling modules:
 mysql                                                                     

Transaction Summary
============================================================================

Is this ok [y/N]: y
Complete!
[root@lnh ~]# dnf -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck   //不验证合法性进行下载
[root@lnh ~]# systemctl enable --now mysqld   //设置开机自启
[root@lnh ~]# systemctl status mysqld.service //查看状态
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor >
   Active: active (running) since Mon 2022-07-25 17:36:19 CST; 12s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 67864 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run>
  Process: 67814 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, sta>
 Main PID: 67866 (mysqld)
    Tasks: 27 (limit: 12221)
   Memory: 310.2M
   CGroup: /system.slice/mysqld.service
           └─67866 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/>

Jul 25 17:36:17 lnh systemd[1]: Starting MySQL Server...
Jul 25 17:36:19 lnh systemd[1]: Started MySQL Server.
[root@lnh ~]# ss -antl   //查看3306端口是否出现
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                  *:80                  *:*              
LISTEN  0       128               [::]:22               [::]:*              
LISTEN  0       80                   *:3306                *:*              
[root@lnh ~]# grep "password" /var/log/mysqld.log 
2022-07-25T09:36:17.891633Z 1 [Note] A temporary password is generated for root@localhost: eNGwhYkrz3_(
//在日志文件中找出临时密码
[root@lnh ~]# mysql -uroot -p   //使用临时密码登录
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38

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 'xbz123';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@lnh ~]# mysql -uroot -pxbz123    //验证密码修改成功
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.38 MySQL Community Server (GPL)

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> 
[root@lnh ~]# rpm -qa |grep mysql
mysql-community-client-5.7.38-1.el7.x86_64
mysql57-community-release-el7-11.noarch
mysql-community-common-5.7.38-1.el7.x86_64
mysql-community-server-5.7.38-1.el7.x86_64
mysql-community-devel-5.7.38-1.el7.x86_64
mysql-community-libs-5.7.38-1.el7.x86_64
[root@lnh ~]# rpm -e mysql57-community-release-el7-11.noarch
//卸载这个包防止其更新

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

[root@lnh ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.7.38 MySQL Community Server (GPL)

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> create database lnh;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lnh                |
| mysql              |
| performance_schema |
| sys                |
| tushanbu           |
+--------------------+
6 rows in set (0.00 sec)

mysql> use lnh;
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.02 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)

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

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

往新建的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 | wangqiing   |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        | NULL |
|  8 | chenshhuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

修改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)

以age字段降序排序

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

查询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)

查询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 | wangqiing |   25 |
+----+-----------+------+
4 rows in set (0.00 sec)

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

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

查询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)

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

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

修改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)

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

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

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

推荐文章：

mysql数据库基础，不建议使用。mysq