ToB企服应用市场:ToB评测及商务社交产业平台

标题: mysql实战案例 [打印本页]
作者: 大连全瓷种植牙齿制作中心    时间: 2022-9-2 09:18
标题: 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)
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4