MySQL实战案例

目录

实战案例

1.搭建mysql服务

[root@localhost ~]# 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 20:23:13--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.64.178.143, 2600:140b:2:59c::2e31, 2600:140b:2:5ad::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.64.178.143|: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 20:23:14--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.58.116.230
Connecting to repo.mysql.com (repo.mysql.com)|23.58.116.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-r 100%[===================>]  25.08K   140KB/s    in 0.2s

2022-07-26 20:23:16 (140 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]

[root@localhost ~]# 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@localhost ~]# yum module disable mysql
Last metadata expiration check: 1 day, 1:24:49 ago on Mon 25 Jul 2022 06:58:52 PM CST.
Dependencies resolved.
Nothing to do.
Complete!
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client
Last metadata expiration check: 1 day, 1:24:58 ago on Mon 25 Jul 2022 06:58:52 PM CST.
Package mysql-community-server-5.7.38-1.el7.x86_64 is already installed.
Package mysql-community-client-5.7.38-1.el7.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@localhost ~]# rpm -e mysql57-community-release
[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-25T05:31:11.960829Z 1 [Note] A temporary password is generated for root@localhost: l0edhV9ow5&?
2022-07-25T05:31:57.436140Z 2 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2022-07-25T05:32:42.740526Z 3 [Warning] Plugin validate_password reported: 'Effective value of validate_password_length is changed. New value is 4'
2022-07-25T05:32:53.210660Z 3 [Warning] Plugin validate_password reported: 'Effective value of validate_password_length is changed. New value is 4'
2022-07-25T05:34:16.165563Z 4 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2022-07-25T06:22:56.707604Z 12 [Note] Access denied for user 'wangqing'@'localhost' (using password: YES)
2022-07-25T06:25:13.908727Z 14 [Note] Access denied for user 'wangqing'@'localhost' (using password: YES)
2022-07-25T11:03:32.337297Z 0 [Note] Shutting down plugin 'validate_password'
2022-07-25T11:03:34.070969Z 0 [Note] Shutting down plugin 'sha256_password'
2022-07-25T11:03:34.070970Z 0 [Note] Shutting down plugin 'mysql_native_password'
2022-07-25T11:14:09.388080Z 0 [Note] Shutting down plugin 'validate_password'
2022-07-25T11:14:11.115309Z 0 [Note] Shutting down plugin 'sha256_password'
2022-07-25T11:14:11.115311Z 0 [Note] Shutting down plugin 'mysql_native_password'
2022-07-25T16:08:23.045576Z 0 [Note] Shutting down plugin 'validate_password'
2022-07-25T16:08:24.374605Z 0 [Note] Shutting down plugin 'sha256_password'
2022-07-25T16:08:24.374607Z 0 [Note] Shutting down plugin 'mysql_native_password'
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set password = password('123456');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set password = password('shushao123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> w
    -> ^C
mysql> ^C
mysql> exit
Bye

2.创建一个以你名字为名的数据库，并创建一张表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    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec

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

mysql> insert into student (id ,name,age)values(1,'tom',20),
(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zha
ngsan',20),(7,'lisi',null),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',1
5),(11,'qiuxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0

4.往新建的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 |
+----+-------------+------+

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;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)

6.以age字段降序排序

mysql> select * from student 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 | zhangsan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+
11 rows in set (0.00 sec)

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)

8.查询student表中年龄最大的4位同学

mysql> select * from student 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.00 sec)

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

mysql> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)

10.查询student表中名字叫zhangshan且年龄大于20岁的记录

mysql> select * from student where name = 'zhangshan' and age >=20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)

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 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.00 sec)

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;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 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)

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

mysql> delete from student where name = 'zhangsan' and age <=20;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)
posted @ 2022-07-26 21:01  舒少`  阅读(367)  评论(0)    收藏  举报