1.关系型数据库介绍
1.1数据库结构模型
1.2RDBMS专业名词
1.3关系型数据库的常见组件
1.4SQL语句
2.mysql安装与配置
2.1mysql 安装
2.2mysql配置
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status myaqld
Unit myaqld.service could not be found.
[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 六 2020-04-04 14:44:49 CST; 2min 37s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 6677 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 6628 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 6681 (mysqld)
CGroup: /system.slice/mysqld.service
└─6681 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
4月 04 14:43:52 localhost.localdomain systemd[1]: Starting MySQL Server...
4月 04 14:44:49 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 32 :::21 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
[root@localhost ~]# grep "password" /var/log/mysqld.log
2020-04-04T06:44:15.530430Z 1 [Note] A temporary password is generated for root@localhost: &bjGrVMxL5Dj
//此处的临时密码为：&bjGrVMxL5Dj
//每个人的临时密码都不同；
使用获取到的临时密码登录mysql
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.29
Copyright (c) 2000, 2020, 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> 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 'whb123!';
Query OK, 0 rows affected (0.78 sec)
mysql> quit
Bye
//为避免mysql自动升级，这里需要卸载最开始安装的yum源
[root@localhost ~]# rpm -qa|grep mysql
mysql57-community-release-el7-10.noarch
mysql-community-client-5.7.29-1.el7.x86_64
mysql-community-libs-5.7.29-1.el7.x86_64
mysql-community-server-5.7.29-1.el7.x86_64
mysql-community-libs-compat-5.7.29-1.el7.x86_64
mysql-community-common-5.7.29-1.el7.x86_64
mysql-community-devel-5.7.29-1.el7.x86_64
[root@localhost ~]# yum -y remove mysql57-community-release-el7-10.noarch
3.mysql的程序组成
3.1mysql工具使用
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper
[root@localhost ~]# mysql -uroot -pwhb123! -h127.0.0.1
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 7
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> quit
Bye
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
进入数据库创建lol这个数据库，对它进行创建和删除
[root@localhost ~]# mysql -uroot -pwhb123! -h127.0.0.1
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 10
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> CREATE DATABASE IF NOT EXISTS lol;
Query OK, 1 row affected (0.09 sec)
mysql> SHOW DATABASES
-> SHOW DATABASES;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW DATABASES' at line 2
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lol |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.05 sec)
mysql> DROP DATABASE IF EXISTS lol;
Query OK, 0 rows affected (0.44 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
进入lol数据库里创建abc这个表，表的字段有id，name，age
mysql> use lol;
Database changed
mysql> CREATE TABLE abc (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (1.77 sec)
mysql> SHOW TABLES;
+---------------+
| Tables_in_lol |
+---------------+
| abc |
+---------------+
1 row in set (0.00 sec)
查看abc这个表的结构
mysql> DESC lol.abc;
+-------+--------------+------+-----+---------+-------+
| 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.55 sec)
对abc这个表做删除操作
mysql> use lol;
Database changed
mysql> DROP TABLE abc;
Query OK, 0 rows affected (0.50 sec)
mysql> SHOW TABLES;
Empty set (0.00 sec)
