mysql实战案例

实战案例

1.搭建mysql服务

下载mysql

1. [root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
2. [root@localhost ~]# rpm -Uvh  mysql57-community-release-el7-11.noarch.rpm
3. [root@localhost ~]# yum module disable mysql  //禁用mysql
4. [root@localhost ~]# yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck                //下载一个服务端，客户端，命令行，软件包                        //--nogpgche禁止进行gpgcheck

复制代码

安装完后设置开机自启动

1. [root@localhost ~]# systemctl enable --now mysqld
2. [root@localhost ~]# systemctl status mysqld
3. ● mysqld.service - MySQL Server
4.    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
5.    Active: active (running) since Mon 2022-07-25 14:34:30 CST; 8s ago
6.      Docs: man:mysqld(8)
7.            http://dev.mysql.com/doc/refman/en/using-systemd.html
8.   Process: 70174 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p>
9.   Process: 70022 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
10. Main PID: 70176 (mysqld)
11.     Tasks: 27 (limit: 23457)
12.    Memory: 300.4M
13.    CGroup: /system.slice/mysqld.service
14.            └─70176 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
16. Jul 25 14:34:28 localhost.localdomain systemd[1]: Starting MySQL Server...
17. Jul 25 14:34:30 localhost.localdomain systemd[1]: Started MySQL Server.

复制代码

查看3306端口是否监听

1. [root@localhost ~]# ss -anlt | grep 3306
2. LISTEN 0      80                 *:3306            *:*         

复制代码

在日志中找出密码

1. [root@localhost ~]# grep "password"  /var/log/mysqld.log
2. 2022-07-25T06:34:28.883599Z 1 [Note] A temporary password is generated for root@localhost: OFU+amdhV3Wr                        //临时密码
3. 2022-07-25T06:35:33.642430Z 0 [Note] Shutting down plugin 'validate_password'
4. 2022-07-25T06:35:34.759663Z 0 [Note] Shutting down plugin 'sha256_password'
5. 2022-07-25T06:35:34.759665Z 0 [Note] Shutting down plugin 'mysql_native_password'

复制代码

使用临时密码登录MySQL

1. [root@localhost ~]# mysql -uroot -pOFU+amdhV3Wr                //-p后可以跟密码
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Welcome to the MySQL monitor.  Commands end with ; or \g.
4. Your MySQL connection id is 3
5. Server version: 5.7.38
7. Copyright (c) 2000, 2022, Oracle and/or its affiliates.
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15. mysql>                                 //看到这样的标识表示登录进去了

复制代码

修改mysql登录密码

1. mysql> set global validate_password_policy=0;
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> set global validate_password_length=1;
5. Query OK, 0 rows affected (0.00 sec)
7. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
8. Query OK, 0 rows affected (0.00 sec)

复制代码

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

1. mysql> desc student;
2. +-------+--------------+------+-----+---------+----------------+
3. | Field | Type         | Null | Key | Default | Extra          |
4. +-------+--------------+------+-----+---------+----------------+
5. | id    | int(11)      | NO   | PRI | NULL    | auto_increment |
6. | name  | varchar(100) | NO   |     | NULL    |                |
7. | age   | tinyint(4)   | YES  |     | NULL    |                |
8. +-------+--------------+------+-----+---------+----------------+
9. 3 rows in set (0.01 sec)

复制代码

解析

1. mysql> create database zxr;
2. Query OK, 1 row affected (0.00 sec)
4. mysql> show databases;
5. +--------------------+
6. | Database           |
7. +--------------------+
8. | information_schema |
9. | mysql              |
10. | performance_schema |
11. | sys                |
12. | zxr                |
13. +--------------------+
14. 5 rows in set (0.00 sec)
16. mysql> use zxr;
17. Database changed
18. mysql> create table student(id int(11) primary key auto_increment,name varchar(100) not null,age tinyint(4));
19. Query OK, 0 rows affected (0.01 sec)
21. mysql> desc student;
22. +-------+--------------+------+-----+---------+----------------+
23. | Field | Type         | Null | Key | Default | Extra          |
24. +-------+--------------+------+-----+---------+----------------+
25. | id    | int(11)      | NO   | PRI | NULL    | auto_increment |
26. | name  | varchar(100) | NO   |     | NULL    |                |
27. | age   | tinyint(4)   | YES  |     | NULL    |                |
28. +-------+--------------+------+-----+---------+----------------+
29. 3 rows in set (0.00 sec)

复制代码

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

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

复制代码

4.往新建的student表中插入数据（用insert语句），结果应如下所示：

1. +----+-------------+------+
2. | id | name        | age  |
3. +----+-------------+------+
4. |  1 | tom         |   20 |
5. |  2 | jerry       |   23 |
6. |  3 | wangqing    |   25 |
7. |  4 | sean        |   28 |
8. |  5 | zhangshan   |   26 |
9. |  6 | zhangshan   |   20 |
10. |  7 | lisi        | NULL |
11. |  8 | chenshuo    |   10 |
12. |  9 | wangwu      |    3 |
13. | 10 | qiuyi       |   15 |
14. | 11 | qiuxiaotian |   20 |
15. +----+-------------+------+

复制代码

解析

1. mysql> insert into student (name,age)values('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangsan',20),('lisi',null),('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
2. Query OK, 11 rows affected (0.00 sec)
3. Records: 11  Duplicates: 0  Warnings: 0
5. mysql> select * from student;
6. +----+-------------+------+
7. | id | name        | age  |
8. +----+-------------+------+
9. |  1 | tom         |   20 |
10. |  2 | jerry       |   23 |
11. |  3 | wangqing    |   25 |
12. |  4 | sean        |   28 |
13. |  5 | zhangshan   |   26 |
14. |  6 | zhangsan    |   20 |
15. |  7 | lisi        | NULL |
16. |  8 | chenshuo    |   10 |
17. |  9 | wangwu      |    3 |
18. | 10 | qiuyi       |   15 |
19. | 11 | qiuxiaotian |   20 |
20. +----+-------------+------+
21. 11 rows in set (0.00 sec)

复制代码

5.修改lisi的年龄为50

1. mysql> update student set age = 50 where name = 'lisi';
2. Query OK, 1 row affected (0.00 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. mysql> select * from student where name = 'lisi';
6. +----+------+------+
7. | id | name | age  |
8. +----+------+------+
9. |  7 | lisi |   50 |
10. +----+------+------+
11. 1 row in set (0.00 sec)

复制代码

6.以age字段降序排序

1. mysql> select * from student;
2. +----+-------------+------+
3. | id | name        | age  |
4. +----+-------------+------+
5. |  1 | tom         |   20 |
6. |  2 | jerry       |   23 |
7. |  3 | wangqing    |   25 |
8. |  4 | sean        |   28 |
9. |  5 | zhangshan   |   26 |
10. |  6 | zhangsan    |   20 |
11. |  7 | lisi        |   50 |
12. |  8 | chenshuo    |   10 |
13. |  9 | wangwu      |    3 |
14. | 10 | qiuyi       |   15 |
15. | 11 | qiuxiaotian |   20 |
16. +----+-------------+------+
17. 11 rows in set (0.00 sec)
19. mysql> select * from student order by age desc;
20. +----+-------------+------+
21. | id | name        | age  |
22. +----+-------------+------+
23. |  7 | lisi        |   50 |
24. |  4 | sean        |   28 |
25. |  5 | zhangshan   |   26 |
26. |  3 | wangqing    |   25 |
27. |  2 | jerry       |   23 |
28. |  1 | tom         |   20 |
29. |  6 | zhangsan    |   20 |
30. | 11 | qiuxiaotian |   20 |
31. | 10 | qiuyi       |   15 |
32. |  8 | chenshuo    |   10 |
33. |  9 | wangwu      |    3 |
34. +----+-------------+------+
35. 11 rows in set (0.00 sec)

复制代码

7.查询student表中年龄最小的3位同学跳过前2位

1. mysql> select * from student order by age;
2. +----+-------------+------+
3. | id | name        | age  |
4. +----+-------------+------+
5. |  9 | wangwu      |    3 |
6. |  8 | chenshuo    |   10 |
7. | 10 | qiuyi       |   15 |
8. |  1 | tom         |   20 |
9. |  6 | zhangsan    |   20 |
10. | 11 | qiuxiaotian |   20 |
11. |  2 | jerry       |   23 |
12. |  3 | wangqing    |   25 |
13. |  5 | zhangshan   |   26 |
14. |  4 | sean        |   28 |
15. |  7 | lisi        |   50 |
16. +----+-------------+------+
17. 11 rows in set (0.00 sec)
19. mysql> select * from student order by age limit 2,3;
20. +----+-------------+------+
21. | id | name        | age  |
22. +----+-------------+------+
23. | 10 | qiuyi       |   15 |
24. |  1 | tom         |   20 |
25. | 11 | qiuxiaotian |   20 |
26. +----+-------------+------+
27. 3 rows in set (0.00 sec)

复制代码

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

1. mysql> select * from student order by age desc;
2. +----+-------------+------+
3. | id | name        | age  |
4. +----+-------------+------+
5. |  7 | lisi        |   50 |
6. |  4 | sean        |   28 |
7. |  5 | zhangshan   |   26 |
8. |  3 | wangqing    |   25 |
9. |  2 | jerry       |   23 |
10. |  1 | tom         |   20 |
11. |  6 | zhangsan    |   20 |
12. | 11 | qiuxiaotian |   20 |
13. | 10 | qiuyi       |   15 |
14. |  8 | chenshuo    |   10 |
15. |  9 | wangwu      |    3 |
16. +----+-------------+------+
17. 11 rows in set (0.00 sec)
19. mysql> select * from student order by age desc limit 4;
20. +----+-----------+------+
21. | id | name      | age  |
22. +----+-----------+------+
23. |  7 | lisi      |   50 |
24. |  4 | sean      |   28 |
25. |  5 | zhangshan |   26 |
26. |  3 | wangqing  |   25 |
27. +----+-----------+------+
28. 4 rows in set (0.00 sec)

复制代码

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

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

复制代码

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

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

复制代码

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

1. mysql> select * from student where age between 23 and 30;
2. +----+-----------+------+
3. | id | name      | age  |
4. +----+-----------+------+
5. |  2 | jerry     |   23 |
6. |  3 | wangqing  |   25 |
7. |  4 | sean      |   28 |
8. |  5 | zhangshan |   26 |
9. +----+-----------+------+
10. 4 rows in set (0.00 sec)
12. mysql> select * from student where age >= 23 and age <= 30;
13. +----+-----------+------+
14. | id | name      | age  |
15. +----+-----------+------+
16. |  2 | jerry     |   23 |
17. |  3 | wangqing  |   25 |
18. |  4 | sean      |   28 |
19. |  5 | zhangshan |   26 |
20. +----+-----------+------+
21. 4 rows in set (0.00 sec)

复制代码

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

1. mysql> update student set age =100 where name = 'wangwu';
2. Query OK, 1 row affected (0.00 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. mysql> select * from student where name = 'wangwu';
6. +----+--------+------+
7. | id | name   | age  |
8. +----+--------+------+
9. |  9 | wangwu |  100 |
10. +----+--------+------+
11. 1 row in set (0.00 sec)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！