百木园实战案例
1.搭建mysql服务
下载mysql
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# yum module disable mysql //禁用mysql
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck //下载一个服务端，客户端，命令行，软件包 //--nogpgche禁止进行gpgcheck
安装完后设置开机自启动
[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2022-07-25 14:34:30 CST; 8s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 70174 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p>
Process: 70022 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 70176 (mysqld)
Tasks: 27 (limit: 23457)
Memory: 300.4M
CGroup: /system.slice/mysqld.service
└─70176 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 25 14:34:28 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 25 14:34:30 localhost.localdomain systemd[1]: Started MySQL Server.
查看3306端口是否监听
[root@localhost ~]# ss -anlt | grep 3306
LISTEN 0 80 *:3306 *:*
在日志中找出密码
[root@localhost ~]# grep \"password\" /var/log/mysqld.log
2022-07-25T06:34:28.883599Z 1 [Note] A temporary password is generated for root@localhost: OFU+amdhV3Wr //临时密码
2022-07-25T06:35:33.642430Z 0 [Note] Shutting down plugin \'validate_password\'
2022-07-25T06:35:34.759663Z 0 [Note] Shutting down plugin \'sha256_password\'
2022-07-25T06:35:34.759665Z 0 [Note] Shutting down plugin \'mysql_native_password\'
使用临时密码登录MySQL
[root@localhost ~]# mysql -uroot -pOFU+amdhV3Wr //-p后可以跟密码
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 3
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登录密码
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 \'123456\';
Query OK, 0 rows affected (0.00 sec)
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> create database zxr;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zxr |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zxr;
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.01 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)
3.查看下该新建的表有无内容（用select语句）
mysql> select * from student;
Empty set (0.00 sec)
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 |
+----+-------------+------+
解析
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);
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 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
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 where name = \'lisi\';
+----+------+------+
| id | name | age |
+----+------+------+
| 7 | lisi | 50 |
+----+------+------+
1 row in set (0.00 sec)
6.以age字段降序排序
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)
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;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 9 | wangwu | 3 |
| 8 | chenshuo | 10 |
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 6 | zhangsan | 20 |
| 11 | qiuxiaotian | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 5 | zhangshan | 26 |
| 4 | sean | 28 |
| 7 | lisi | 50 |
+----+-------------+------+
11 rows in set (0.00 sec)
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;
+----+-------------+------+
| 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)
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)
mysql> select * from student where age >= 23 and age <= 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 where name = \'wangwu\';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 9 | wangwu | 100 |
+----+--------+------+
1 row in set (0.00 sec)
13.删除student中名字叫zhangshan且年龄小于等于20的记录
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)
mysql> delete from student where name = \'zhangshan\' 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 |
| 6 | zhangsan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)
来源：https://www.cnblogs.com/Their-own/p/16521432.html
本站部分图文来源于网络，如有侵权请联系删除。