欢迎访问 生活随笔！
TAG聚合

生活随笔

当前位置： 首页 >

mysql 数据库 应用_MySQL数据库的应用

发布时间：2023/12/1 60 豆豆
生活随笔 收集整理的这篇文章主要介绍了 mysql 数据库 应用_MySQL数据库的应用 小编觉得挺不错的,现在分享给大家,帮大家做个参考.

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 ifnot exists wangming;

Query OK,1 row affected (0.001sec)

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.027sec)

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.023sec)

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.001sec)2、查看下该新建的表有无内容(用select语句)

MariaDB [wangming]> select * fromwangming;

Emptyset (0.000sec)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.025sec)

Records:11 Duplicates: 0 Warnings: 0MariaDB [wangming]> select * fromwangming;+----+-------------+------+

| 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.000sec)4、修改lisi的年龄为50

MariaDB [wangming]> update wangming set age = 50 where name = 'lisi';

Query OK,1 row affected (0.045sec)

Rows matched:1 Changed: 1 Warnings: 0MariaDB [wangming]> select * from wangming where name ='lisi';+----+------+------+

| id | name | age |

+----+------+------+

| 7 | lisi | 50 |

+----+------+------+

1 row in set (0.001sec)5、以age字段降序排序

MariaDB [wangming]> select * fromwangming 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.001sec)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.001sec)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.001sec)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.001sec)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.001sec)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.012sec)11、修改wangwu的年龄为100

MariaDB [wangming]> update wangming set age = 100 where name = 'wangwu';

Query OK,1 row affected (0.023sec)

Rows matched:1 Changed: 1 Warnings: 0MariaDB [wangming]> select * from wangming where name = 'wangwu';+----+--------+------+

| id | name | age |

+----+--------+------+

| 9 | wangwu | 100 |

+----+--------+------+

1 row in set (0.001sec)12、删除student中名字叫zhangshan且年龄小于等于20的记录

MariaDB [wangming]> delete from wangming where name = 'zhangshan' and age <= 20;

Query OK,1 row affected (0.018sec)

MariaDB [wangming]> select * from wangming where name = 'zhangshan';+----+-----------+------+

| id | name | age |

+----+-----------+------+

| 5 | zhangshan | 26 |

+----+-----------+------+

1 row in set (0.002 sec)

总结

以上是生活随笔为你收集整理的mysql 数据库 应用_MySQL数据库的应用的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。