一、安装
1.1、下载到opt目录中并解压，解压后的包重命名并移动到/usr/local目录中
[root@localhost ~]# cd /opt
[root@localhost opt]# rz -E
rz waiting to receive.
[root@localhost opt]# ls
mysql-8.0.30-el7-x86_64.tar.gz rh
[root@localhost opt]# tar -xf mysql-8.0.30-el7-x86_64.tar.gz
ls
[root@localhost opt]# ls
mysql-8.0.30-el7-x86_64 mysql-8.0.30-el7-x86_64.tar.gz rh[root@localhost opt]# mv mysql-8.0.30-el7-x86_64 mysql
[root@localhost opt]# mv mysql /usr/local/1.2、创建程序用户管理并修改mysql目录和配置文件的权限
[root@localhost opt]# useradd -s /sbin/nologin mysql #程序用户管理
[root@localhost opt]# chown -R mysql:mysql /usr/local/mysql #修改权限
[root@localhost opt]# chown mysql:mysql /etc/my.cnf1.3、修改配置文件
[root@localhost opt]# cd /usr/local/mysql/bin
[root@localhost bin]# vim /etc/my.cnf[client]
port = 3306
socket=/usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION1.4、设置环境变量，申明/宣告mysql命令便于系统识别并初始化数据库
[root@localhost bin]# cd /usr/local/mysql
[root@localhost mysql]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@localhost mysql]# source /etc/profile[root@localhost mysql]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data1.5、设置系统识别，进行操作：
[root@mysql1 bin]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql1 bin]# chmod +x /etc/init.d/mysqld
[root@mysql1 bin]# systemctl daemon-reload
[root@mysql1 bin]# systemctl restart mysqld1.6、初始化数据库密码：
[root@mysql1 bin]# mysqladmin -u root -p password "自己设的密码"然后直接回车即可
1.7、进入数据库：
[root@mysql1 bin]# mysql -u root -p自己设的密码1.8、创建用户并设置密码：
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)1.9、赋予远程连接的权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)1.10、刷新生效
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)1.11、修改加密方式，可以进行远程连接
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';二、使用
2.1、查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set
2.2、查看版本，查看当前用户
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set
mysql> select user();
+-------------------+
| user() |
+-------------------+
| root@192.168.10.1 |
+-------------------+
1 row in set2.3、切换库，查看库中的表/不切换库，查看库中的表
mysql> use mysql;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
37 rows in setmysql> show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
37 rows in set2.4、查看表中的字段信息，例：查看mysql库中的user表的字段信息（不切换库查看表信息就用describe 库名.表名）
mysql> describe user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set三、SQL语句
关系型数据库专用的操作管理语句 包含类型有 DDL DML DQL DCL
DDL：用于管理数据库对象（库、表、索引 等）
创建新库，在新库中创建表并查看
mysql> create database yzds
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yzds |
+--------------------+
5 rows in setmysql> use yzds;
Database changed
mysql> create table yz402(id int,name varchar(20),sex char(2),age int, primary key(id));
Query OK, 0 rows affected
mysql> show tables;
+----------------+
| Tables_in_yzds |
+----------------+
| yz402 |
+----------------+
1 row in set
mysql> desc yz402;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set删除表和库
drop table 数据库名.表名；
drop database 数据库名；
扫一扫
804
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
