MySQL数据库的应用 

  1 数据结构模型
  2 数据结构模型主要有：
  3 层次模型
  4 网状结构
  5 关系模型
  6 关系模型：
  7 二维关系：row，column
  8 数据库管理系统：DBMS
  9 关系：Relational，RDBMS
 10 SQL语句有三种类型：
 11 DDL：Data Defination Language，数据定义语言
 12 DML：Data Manipulation Language，数据操纵语言
 13 DCL：Data Control Language，数据控制语言
 14  
 15 SQL语句类型
 16 对应操作
 17 DDL
 18 CREATE：创建
 19 DROP：删除
 20 ALTER：修改
 21 DML
 22 INSERT：向表中插入数据
 23 DELETE：删除表中数据
 24 UPDATE：更新表中数据
 25 SELECT：查询表中数据
 26 DCL
 27 GRANT：授权
 28 REVOKE：移除授权
 29 安装数据库，并设置开机自启
 30 [root@localhost ~]# yum -y install mariadb*
 31 [root@localhost ~]# systemctl enable --now mariadb
 32 [root@localhost ~]# ss -antl （确保数据库端口启动）
 33 State Recv-Q Send-Q  Local Address:Port  Peer Address:Port 
 34 LISTEN0      128           0.0.0.0:22         0.0.0.0:*    
 35 LISTEN0      80            0.0.0.0:3306       0.0.0.0:*    
 36 LISTEN0      128              [::]:22            [::]:* 
 37 进入数据库并修改密码
 38 [root@localhost ~]# mysql -uroot -p
 39 MariaDB [(none)]> set password = password('990304');
 40 Query OK, 0 rows affected (0.005 sec)
 41 服务器监听的两种socket地址
 42 
 43 socket类型
 44 说明
 45 ip socket
 46 默认监听在tcp的3306端口，支持远程通信
 47 unix sock
 48 监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）
 49 仅支持本地通信
 50 server地址只能是：localhost，127.0.0.1
 51 创建数据表 
 52 MariaDB [(none)]> create database if not exists wangming;
 53 Query OK, 0 rows affected, 1 warning (0.001 sec)
 54 查看当前的数据库
 55 MariaDB [(none)]> show databases;
 56 +--------------------+
 57 | Database           |
 58 +--------------------+
 59 | information_schema |
 60 | mysql              |
 61 | performance_schema |
 62 | wang               |
 63 | wangming           |
 64 | wangmingge         |
 65 | wangqi             |
 66 | wangqingge         |
 67 +--------------------+
 68 8 rows in set (0.002 sec)
 69 删除数据库
 70 MariaDB [(none)]> drop database if exists wangming;
 71 Query OK, 0 rows affected (0.008 sec)
 72 MariaDB [(none)]> drop database if exists wangmingge;
 73 Query OK, 0 rows affected (0.001 sec)
 74 MariaDB [(none)]> drop database if exists wangqingge ;
 75 Query OK, 0 rows affected (0.001 sec)
 76 MariaDB [(none)]> drop database if exists wangqi ;
 77 Query OK, 2 rows affected (0.052 sec)
 78 MariaDB [(none)]> show databases;
 79 +--------------------+
 80 | Database           |
 81 +--------------------+
 82 | information_schema |
 83 | mysql              |
 84 | performance_schema |
 85 | wang               |
 86 +--------------------+
 87 4 rows in set (0.001 sec)
 88 创建数据库，并在数据库里创建数据表
 89 MariaDB [(none)]> create database wangming;
 90 Query OK, 1 row affected (0.001 sec)
 91 MariaDB [(none)]> use wangming;
 92 Database changed
 93 MariaDB [wangming]> create table wangming (id int not null,name varchar(100) not null,age tinyint);
 94 Query OK, 0 rows affected (0.041 sec)
 95 MariaDB [wangming]> show tables;
 96 +--------------------+
 97 | Tables_in_wangming |
 98 +--------------------+
 99 | wangming           |
100 +--------------------+
101 1 row in set (0.000 sec)
102 删除数据表
103 MariaDB [wangming]> drop table wangming;
104 Query OK, 0 rows affected (0.054 sec)
105 MariaDB [wangming]> show tables;
106 Empty set (0.000 sec)
107 修改数据表的格式，增加数据表字段。
108 MariaDB [wangming]> desc wangming;
109 +-------+--------------+------+-----+---------+-------+
110 | Field | Type         | Null | Key | Default | Extra |
111 +-------+--------------+------+-----+---------+-------+
112 | id    | int(11)      | NO   |     | NULL    |       |
113 | name  | varchar(100) | NO   |     | NULL    |       |
114 | age   | tinyint(4)   | YES  |     | NULL    |       |
115 +-------+--------------+------+-----+---------+-------+
116 3 rows in set (0.006 sec)
117 MariaDB [wangming]> alter table wangming add class varchar(20);
118 Query OK, 0 rows affected (0.028 sec)
119 Records: 0  Duplicates: 0  Warnings: 0
120 MariaDB [wangming]> desc wangming;
121 +-------+--------------+------+-----+---------+-------+
122 | Field | Type         | Null | Key | Default | Extra |
123 +-------+--------------+------+-----+---------+-------+
124 | id    | int(11)      | NO   |     | NULL    |       |
125 | name  | varchar(100) | NO   |     | NULL    |       |
126 | age   | tinyint(4)   | YES  |     | NULL    |       |
127 | class | varchar(20)  | YES  |     | NULL    |       |
128 +-------+--------------+------+-----+---------+-------+
129 4 rows in set (0.002 sec)
130 删除数据表中字段，删除age
131 MariaDB [wangming]> alter table wangming drop age;
132 Query OK, 0 rows affected (0.043 sec)
133 Records: 0  Duplicates: 0  Warnings: 0
134 MariaDB [wangming]> desc wangming
135     -> ;
136 +-------+--------------+------+-----+---------+-------+
137 | Field | Type         | Null | Key | Default | Extra |
138 +-------+--------------+------+-----+---------+-------+
139 | id    | int(11)      | NO   |     | NULL    |       |
140 | name  | varchar(100) | NO   |     | NULL    |       |
141 | class | varchar(20)  | YES  |     | NULL    |       |
142 +-------+--------------+------+-----+---------+-------+
143 3 rows in set (0.003 sec)
144 查看命令SHOW
145 mysql> SHOW CHARACTER SET;      //查看支持的所有字符集
146 mysql> SHOW ENGINES;        //查看当前数据库支持的所有存储引擎
147 mysql> SHOW DATABASES;          //查看数据库信息
148 mysql> SHOW TABLES FROM （表明);         //不进入某数据库而列出其包含的所有表
149 //查看表结构 mysql> DESC （表明）
150 查看表的创建命令
151 mysql>SHOW CREATE TABLE （表明);
152 //查看某表的状态
153 mysql> use（表明）；
154 mysql> SHOW TABLE STATUS LIK '（表明）'\G   
155 /获取命令使用帮助
156 //语法：HELP keyword;
157 mysql> HELP CREATE TABLE;       //获取创建表的帮助
  1 DCL操作
  2 创建授权grant
  3 **权限类型(priv_type)**
  4 | 权限类型 | 代表什么？ |
  5 | --- | --- |
  6 | ALL |    所有权限 |
  7 | SELECT | 读取内容的权限 |
  8 | INSERT | 插入内容的权限 |
  9 | UPDATE | 更新内容的权限 |
 10 | DELETE | 删除内容的权限 |
 11 |
 12 **指定要操作的对象db_name.table_name**
 13 
 14 | 表示方式 | 意义 |
 15 | --- | --- |
 16 | *.*      | 所有库的所有表 |
 17 | db_name | 指定库的所有表 |
 18 | db_name.table_name | 指定库的指定表 ||     |
 19 查看数据库是否启动
 20 [root@localhost ~]# ss -antl
 21 State Recv-Q Send-Q  Local Address:Port  Peer Address:Port 
 22 LISTEN0      128           0.0.0.0:22         0.0.0.0:*    
 23 LISTEN0      80            0.0.0.0:3306       0.0.0.0:*    
 24 LISTEN0      128              [::]:22            [::]:*   |     |
 25 授权方式（大写部分是不可更改的）
 26 //授权wangqing用户在数据库本机上登录访问所有数据库
 27  mysql> GRANT ALL ON *.* TO 'wangqing'@'localhost' IDENTIFIED BY 'wangqing123!'; 
 28  Query OK, 0 rows affected, 1 warning (0.00 sec) 
 29  mysql> GRANT ALL ON *.* TO 'wangqing'@'127.0.0.1' IDENTIFIED BY 'wangqing123!'; 
 30  Query OK, 0 rows affected, 1 warning (0.00 sec) 
 31  //授权wangqing用户在172.16.12.129上远程登录访问wangqingge数据库 
 32  mysql> GRANT ALL ON wangqingge.* TO 'wangqing'@'172.16.12.129' IDENTIFIED BY 'wangqing123!';
 33   Query OK, 0 rows affected, 1 warning (0.00 sec) 
 34   //授权wangqing用户在所有位置上远程登录访问wangqingge数据库
 35   mysql> GRANT ALL ON *.* TO 'wangqing'@'%' IDENTIFIED BY 'wangqing123!'; 
 36   Query OK, 0 rows affected, 1 warning (0.00 sec)
 37 创建授权
 38 [root@localhost ~]# mysql -uroot -p990304
 39 MariaDB [(none)]> grant all on *.* to 'root'@'192.168.24.1' identified by '990304';
 40 Query OK, 0 rows affected (0.002 sec)
 41 查看指定用户的授权
 42 MariaDB [(none)]> show grants for 'root'@'192.168.24.1'
 43     -> ;
 44 +-------------------------------------------------------------------------------------------------------------------------+
 45 | Grants for root@192.168.24.1                                                                                            |
 46 +-------------------------------------------------------------------------------------------------------------------------+
 47 | GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.24.1' IDENTIFIED BY PASSWORD '*23AF288E5ABD0A7F77F9E51F2D452E28F1EA7B9C' |
 48 +-------------------------------------------------------------------------------------------------------------------------+
 49 1 row in set (0.000 sec)
 50 取消全部授权
 51 MariaDB [(none)]> revoke all on *.* from 'root'@'192.168.24.1';
 52 Query OK, 0 rows affected (0.000 sec)
 53 
 54 **注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：**
 55 
 56 *   GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
 57 *   对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
 58 * mysql> FLUSH PRIVILEGES;
 59 
 60 DML操作
 61

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个结果		 
 75 
 76 
 77 增加数据表内容
 78 MariaDB [wangqi]> INSERT INTO wangming (id,name,age) VALUE (12,'tm',22),(2,'www',23),(3,'wer',34),(4,'zc',18),(5,'ls',22);
 79 MariaDB [wangqi]> select * from wangming ;
 80 +----+------+------+
 81 | id | name | age  |
 82 +----+------+------+
 83 | 12 | tm   |   22 |
 84 |  2 | www  |   23 |
 85 |  3 | wer  |   34 |
 86 |  4 | zc   |   18 |
 87 |  5 | ls   |   22 |
 88 +----+------+------+
 89 5 rows in set (0.001 sec)
 90 **改数据表的内容**update语句
 91 MariaDB [wangqi]> select * from wangming ;
 92 +----+------+------+
 93 | id | name | age  |
 94 +----+------+------+
 95 | 12 | tm   |   22 |
 96 |  2 | www  |   23 |
 97 |  3 | wer  |   34 |
 98 |  4 | zc   |   18 |
 99 |  5 | ls   |   22 |
100 +----+------+------+
101 5 rows in set (0.001 sec)
102 
103 MariaDB [wangqi]> select * from wangming where name = 'ls'; 
104 +----+------+------+
105 | id | name | age  |
106 +----+------+------+
107 |  4 | ls |   22 |
108 +----+------+------+
109 1 row in set (0.002 sec)
110 修改age值
111 MariaDB [wangqi]> update wangming set age =29 where name = 'ls';
112 Query OK, 1 row affected (0.020 sec)
113 Rows matched: 1  Changed: 1  Warnings: 0
114 MariaDB [wangqi]> select * from wangming where name = 'ls';
115 +----+------+------+
116 | id | name | age  |
117 +----+------+------+
118 |  5 | ls   |   29 |
119 +----+------+------+
120 1 row in set (0.001 sec)
121 修改name值
122 MariaDB [wangqi]> update wangming set name = 'lisi' where id = '5';
123 Query OK, 1 row affected (0.006 sec)
124 Rows matched: 1  Changed: 1  Warnings: 0
125 MariaDB [wangqi]> select * from wangming where name = 'lisi';
126 +----+------+------+
127 | id | name | age  |
128 +----+------+------+
129 |  5 | lisi |   29 |
130 +----+------+------+
131 1 row in set (0.001 sec)
132 修改id值
133 MariaDB [wangqi]> update wangming set id = '10' where name= 'lisi';
134 Query OK, 1 row affected (0.020 sec)
135 Rows matched: 1  Changed: 1  Warnings: 0
136 
137 MariaDB [wangqi]> select * from wangming where name = 'lisi';
138 +----+------+------+
139 | id | name | age  |
140 +----+------+------+
141 | 10 | lisi |   29 |
142 +----+------+------+
143 1 row in set (0.000 sec)
144 
145 查询数据表内容
146 MariaDB [wangqi]> select * from wangming;
147 +----+------+------+
148 | id | name | age  |
149 +----+------+------+
150 | 12 | tm   |   22 |
151 |  2 | www  |   23 |
152 |  3 | wer  |   34 |
153 |  4 | zc   |   18 |
154 | 10 | lisi |   29 |
155 +----+------+------+
156 5 rows in set (0.000 sec)
157 
158 只查看name内容(其他关键字查看大概一样)
159 MariaDB [wangqi]> select name from wangming;
160 +------+
161 | name |
162 +------+
163 | tm   |
164 | www  |
165 | wer  |
166 | zc   |
167 | lisi |
168 +------+
169 按age值排序小到大
170 MariaDB [wangqi]> select * from wangming order by age;
171 +----+------+------+
172 | id | name | age  |
173 +----+------+------+
174 |  4 | zc   |   18 |
175 | 12 | tm   |   22 |
176 |  2 | www  |   23 |
177 | 10 | lisi |   29 |
178 |  3 | wer  |   34 |
179 +----+------+------+
180 5 rows in set (0.001 sec)
181 age值由大到小
182 MariaDB [wangqi]> select * from wangming order by age desc;
183 +----+------+------+
184 | id | name | age  |
185 +----+------+------+
186 |  3 | wer  |   34 |
187 | 10 | lisi |   29 |
188 |  2 | www  |   23 |
189 | 12 | tm   |   22 |
190 |  4 | zc   |   18 |
191 +----+------+------+
192 5 rows in set (0.001 sec)
193 取当前数据库排序最后两个值
194 MariaDB [wangqi]> select * from wangming order by age limit 2;
195 +----+------+------+
196 | id | name | age  |
197 +----+------+------+
198 |  4 | zc   |   18 |
199 | 12 | tm   |   22 |
200 +----+------+------+
201 2 rows in set (0.001 sec)
202 去掉当前排序的最后一位，拿出最后两个
203 MariaDB [wangqi]> select * from wangming order by age limit 1,2;
204 +----+------+------+
205 | id | name | age  |
206 +----+------+------+
207 | 12 | tm   |   22 |
208 |  2 | www  |   23 |
209 +----+------+------+
210 2 rows in set (0.001 sec)
211 取出大于等于20的值
212 MariaDB [wangqi]> select * from wangming where age >= 20;
213 +----+------+------+
214 | id | name | age  |
215 +----+------+------+
216 | 12 | tm   |   22 |
217 |  2 | www  |   23 |
218 |  3 | wer  |   34 |
219 | 10 | lisi |   29 |
220 +----+------+------+
221 4 rows in set (0.019 sec)
222 取出大于age20并且叫www的值
223 MariaDB [wangqi]> select * from wangming where age >=20 and name = 'www';
224 +----+------+------+
225 | id | name | age  |
226 +----+------+------+
227 |  2 | www  |   23 |
228 +----+------+------+
229 1 row in set (0.001 sec)
230 取出age的20到30之间的值
231 MariaDB [wangqi]> select * from wangming where age between 
232 20 and 30;
233 +----+------+------+
234 | id | name | age  |
235 +----+------+------+
236 | 12 | tm   |   22 |
237 |  2 | www  |   23 |
238 | 10 | lisi |   29 |
239 +----+------+------+
240 3 rows in set (0.002 sec)
241 查看age值非空值的所有值
242 MariaDB [wangqi]> select * from wangming where age is not null;
243 +----+------+------+
244 | id | name | age  |
245 +----+------+------+
246 | 12 | tm   |   22 |
247 |  2 | www  |   23 |
248 |  3 | wer  |   34 |
249 |  4 | zc   |   18 |
250 | 10 | lisi |   29 |
251 +----+------+------+
252 5 rows in set (0.001 sec)
253 查看为空值的数
254 MariaDB [wangqi]> select * from wangming where age is null;
255 Empty set (0.001 sec)
256  
257 delete语句
258 删除数据表的值
259 MariaDB [wangqi]> select * from wangming;
260 +----+------+------+
261 | id | name | age  |
262 +----+------+------+
263 | 12 | tm   |   22 |
264 |  2 | www  |   23 |
265 |  3 | wer  |   34 |
266 |  4 | zc   |   18 |
267 | 10 | lisi |   29 |
268 +----+------+------+
269 5 rows in set (0.015 sec)
270 MariaDB [wangqi]> delete from wangming where id = 10;
271 Query OK, 1 row affected (0.056 sec)
272  MariaDB [wangqi]> select * from wangming ;（//删除某条记录）
273 +----+------+------+
274 | id | name | age  |
275 +----+------+------+
276 | 12 | tm   |   22 |
277 |  2 | www  |   23 |
278 |  3 | wer  |   34 |
279 |  4 | zc   |   18 |
280 +----+------+------+
281 4 rows in set (0.006 sec)
282 MariaDB [wangqi]> delete from wangming;（删除整个工作表）
283 Query OK, 4 rows affected (0.020 sec)
284 MariaDB [wangqi]> select * from wangming;
285 Empty set (0.000 sec)
286 MariaDB [wangqi]> desc wangming;
287 +-------+--------------+------+-----+---------+-------+
288 | Field | Type         | Null | Key | Default | Extra |
289 +-------+--------------+------+-----+---------+-------+
290 | id    | int(11)      | NO   |     | NULL    |       |
291 | name  | varchar(100) | NO   |     | NULL    |       |
292 | age   | tinyint(4)   | YES  |     | NULL    |       |
293 +-------+--------------+------+-----+---------+-------+
294 3 rows in set (0.018 sec)
295 另一种删除方式
296 truncate语句
297 MariaDB [wangqi]> select * from wangqi;
298 +----+------+------+
299 | id | name | age  |
300 +----+------+------+
301 |  1 | tom  |   20 |
302 +----+------+------+
303 1 row in set (0.000 sec)
304 MariaDB [wangqi]> truncate wangqi;
305 Query OK, 0 rows affected (0.057 sec)
306 MariaDB [wangqi]> select * from wangqi ;
307 Empty set (0.005 sec)
308 MariaDB [wangqi]> desc wangqi;
309 +-------+--------------+------+-----+---------+-------+
310 | Field | Type         | Null | Key | Default | Extra |
311 +-------+--------------+------+-----+---------+-------+
312 | id    | int(11)      | NO   |     | NULL    |       |
313 | name  | varchar(100) | NO   |     | NULL    |       |
314 | age   | tinyint(4)   | YES  |     | NULL    |       |
315 +-------+--------------+------+-----+---------+-------+
316 3 rows in set (0.001 sec)
317 truncate与delete的区别：
318

truncate与delete的区别：






语句类型特点





delete
DELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间		








truncate
删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表		














332  
333  






 习题：




1、创建一个以你名字为名的数据库，并创建一张表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    |                |
+-------+--------------+------+-----+---------+----------------+
MariaDB [(none)]> create database if not exists wangming;
Query OK, 1 row affected (0.001 sec)
 MariaDB [wangming]> create table wangming (id int(11) not null,name varchar(100) not null,age tinyint(4) null);
Query OK, 0 rows affected (0.027 sec)
MariaDB [wangming]> create table wangming (id int(11) primary key not null auto_increment ,name varchar(100) not null,age tinyint(4) null);
Query OK, 0 rows affected (0.023 sec)
MariaDB [wangming]> desc wangming;
+-------+--------------+------+-----+---------+----------------+
| 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.001 sec) 
 
2、查看下该新建的表有无内容（用select语句）
MariaDB [wangming]> select * from wangming;
Empty set (0.000 sec) 
 
3、往新建的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 |
+----+-------------+------+
MariaDB [wangming]> insert into wangming(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.025 sec)
Records: 11  Duplicates: 0  Warnings: 0
MariaDB [wangming]> select * from wangming;
+----+-------------+------+
| 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.000 sec)

4、修改lisi的年龄为50
MariaDB [wangming]> update wangming set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.045 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [wangming]> select * from wangming where name ='lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi |   50 |
+----+------+------+
1 row in set (0.001 sec) 
5、以age字段降序排序
MariaDB [wangming]> select * from wangming 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.001 sec)


6、查询student表中年龄最小的3位同学跳过前2位
MariaDB [wangming]> select * from wangming order by age limit 2,3;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
| 10 | qiuyi       |   15 |
|  1 | tom         |   20 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
3 rows in set (0.001 sec)
7、查询student表中年龄最大的4位同学
MariaDB [wangming]> select * from wangming 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.001 sec)
8、查询student表中名字叫zhangshan的记录
MariaDB [wangming]> select * from wangming where name ='zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.001 sec)

9、查询student表中名字叫zhangshan且年龄大于20岁的记录
MariaDB [wangming]> select * from wangming where name ='zhangshan' and age > 20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.001 sec)

10、查询student表中年龄在23到30之间的记录
MariaDB [wangming]> select * from wangming 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.012 sec)
11、修改wangwu的年龄为100
MariaDB [wangming]> update wangming set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.023 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [wangming]> select * from wangming where name = 'wangwu';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  9 | wangwu |  100 |
+----+--------+------+
1 row in set (0.001 sec)

12、删除student中名字叫zhangshan且年龄小于等于20的记录
MariaDB [wangming]> delete from wangming where name = 'zhangshan' and age <= 20 ;
Query OK, 1 row affected (0.018 sec)
MariaDB [wangming]> select * from wangming where name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.002 sec)




 


 




 






            

            
posted @ 
2020-12-24 16:44 
海绵堡堡 
阅读(372) 
评论(0) 
 
收藏 
举报