mysql数据库基础
mysql 基础
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)