1 mysql安装
mysql安装方式有三种:
源代码:编译安装
二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用
程序包管理器管理的程序包:
rpm:有两种
OS Vendor:操作系统发行商提供的
项目官方提供的
deb
//配置mysql的yum源
[root@zzg ~]# cd /usr/src/
[root@zzg src]# ls
debug kernels
[root@localhost src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
下载过程略
[root@zzg src]# ls
debug kernels mysql57-community-release-el7-10.noarch.rpm
[root@zzg src]# yum -y install mysql57-community-release-el7-10.noarch.rpm
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-
: manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Examining mysql57-community-release-el7-10.noarch.rpm: mysql57-community-release-el7-10.noarch
....
Installed:
mysql57-community-release.noarch 0:el7-10
Complete!
[root@zzg ~]# ls /etc/yum.repos.d/
mysql-community.repo mysql-community-source.repo
//安装mysql5.7
[root@zzg ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-
: manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.23-1.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client-5.7.23-1.el7.x86_64
....
Installed:
mysql-community-client.x86_64 0:5.7.23-1.el7
mysql-community-common.x86_64 0:5.7.23-1.el7
mysql-community-devel.x86_64 0:5.7.23-1.el7
mysql-community-libs.x86_64 0:5.7.23-1.el7
mysql-community-libs-compat.x86_64 0:5.7.23-1.el7
mysql-community-server.x86_64 0:5.7.23-1.el7
Replaced:
mariadb-libs.x86_64 1:5.5.56-2.el7
Complete!
2.2 mysql配置
//启动mysql
[root@zzg ~]# systemctl start mysqld
[root@zzg ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2018-08-12 23:39:33 CST; 6s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1325 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 1249 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 1327 (mysqld)
CGroup: /system.slice/mysqld.service
└─1327 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/my...
Aug 12 23:39:26 localhost.localdomain systemd[1]: Starting MySQL Server...
Aug 12 23:39:33 localhost.localdomain systemd[1]: Started MySQL Server.
//确保3306端口已经监听起来
[root@zzg ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
//在日志文件中找出临时密码
[root@zzg ~]# grep "password" /var/log/mysqld.log
2018-08-12T15:39:28.710830Z 1 [Note] A temporary password is generated for root@localhost: &vsD!YuKT7&/
//此处的临时密码为&vsD!YuKT7&/
//注意,你的密码跟这是不一样的,一定要看清楚,禁止直接复制我这里的密码
//使用获取到的临时密码登录mysql
[root@zzg ~]# mysql -uroot -p
Enter password: //此处输入密码,可以直接复制你的密码粘贴至此处,也可手动输入
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.01 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
//为避免mysql自动升级,这里需要卸载最开始安装的yum源
[root@zzg ~]# rpm -qa|grep mysql
mysql-community-server-5.7.23-1.el7.x86_64
mysql57-community-release-el7-10.noarch
mysql-community-common-5.7.23-1.el7.x86_64
mysql-community-client-5.7.23-1.el7.x86_64
mysql-community-libs-compat-5.7.23-1.el7.x86_64
mysql-community-libs-5.7.23-1.el7.x86_64
mysql-community-devel-5.7.23-1.el7.x86_64
[root@localhost ~]# yum -y remove mysql57-community-release-el7-10.noarch
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-
: manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
....
Removed:
mysql57-community-release.noarch 0:el7-10
Complete!
2.创建一个以你名字为名的数据库,并创建一张表student,该表包含三个字段(id,name,age),表结构如下:
mysql> create database zzg;
Query OK, 1 row affected (0.00 sec)
mysql> use zzg;
Database changed
mysql> create table student(id int(10),name varchar(50),age int(10));
Query OK, 0 rows affected (0.12 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(10) | 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 student(id,name,age) value (1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',null),(8,'chengshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'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 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chengshuo | 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.01 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 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 | chengshuo | 10 |
| 9 | wangwu | 3 |
+------+-------------+------+
11 rows in set (0.00 sec)
7.查询student表中年龄最小的3位同学
mysql> select * from student order by age limit 3;
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 9 | wangwu | 3 |
| 8 | chengshuo | 10 |
| 10 | qiuyi | 15 |
+------+-----------+------+
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 |
| 6 | zhangshan | 20 |
+------+-----------+------+
2 rows 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 |
| 6 | zhangshan | 20 |
+------+-----------+------+
2 rows 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.01 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> 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 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chengshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+------+-------------+------+
10 rows in set (0.00 sec)