mysql的安装与DDL的使用

本文主要是介绍mysql的安装与DDL的使用，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

• 安装mysqlyum包
• mysql DDL的操作
• 表操作

安装mysqlyum包

[root@YL ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-25 14:37:26--  http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.10.6.175, 2600:140a:1000:28d::2e31, 2600:140a:1000:287::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.10.6.175|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following] 
[root@YL ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm 
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]
禁用mysql
[root@YL ~]# yum module disable mysql
MySQL Connectors Community                    25 kB/s |  49 kB     00:01    
MySQL Tools Community                        342 kB/s | 651 kB     00:01    
MySQL 5.7 Community Server                   644 kB/s | 2.6 MB     00:04    
Dependencies resolved.
Nothing to do.
Complete!
安装mysql需要的服务端客户端命令和工具
[root@YL ~]# yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck
Last metadata expiration check: 0:00:28 ago on Mon 25 Jul 2022 03:00:03 PM CST.
Dependencies resolved.
===========================================================================
 Package                Arch   Version             Repository         Size
===========================================================================
Installing:
 mysql-community-client x86_64 5.7.38-1.el7        mysql57-community  28 M
 mysql-community-common x86_64 5.7.38-1.el7        mysql57-community 311 k
 mysql-community-devel  x86_64 5.7.38-1.el7        mysql57-community 4.2 M
 mysql-community-server x86_64 5.7.38-1.el7        mysql57-community 178 M
...........
  perl-macros-4:5.26.3-416.el8.x86_64                                      
  perl-parent-1:0.237-1.el8.noarch                                         
  perl-podlators-4.11-1.el8.noarch                                         
  perl-threads-1:2.21-2.el8.x86_64                                         
  perl-threads-shared-1.58-2.el8.x86_64   

设置mysql的开机自启

[root@YL ~]# systemctl  enable --now mysqld
[root@YL ~]# systemctl  status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor>
   Active: active (running) since Mon 2022-07-25 15:03:53 CST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 4325 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run>
  Process: 4276 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, sta>
 Main PID: 4328 (mysqld)
    Tasks: 27 (limit: 4743)
   Memory: 247.1M
   CGroup: /system.slice/mysqld.service
           └─4328 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/>

此时的3306端口已经开启了
[root@YL ~]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port    Peer Address:Port  Process  
LISTEN  0       128            0.0.0.0:22           0.0.0.0:*              
LISTEN  0       128               [::]:22              [::]:*              
LISTEN  0       80                   *:3306               *:*      

在日志文件中找出临时密码,修改mysql登陆密码

[root@YL ~]# grep "password" /var/log/mysqld.log
2022-07-25T07:03:51.180440Z 1 [Note] A temporary password is generated for root@localhost: ?Rp,5kU6du;p

使用获取到的临时密码登录mysql
[root@YL ~]# mysql -uroot -p？Rp,5kU6du;p
修改mysql登录密码
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by 'liuyang123!';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

为了防止mysql自动更新为8.0版本，直接删除前面安装的yum源

[root@YL ~]# rpm -e mysql57-community-release 

查看mysql的当前版本

[root@YL ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

登录mysql数据库

[root@YL ~]# mysql -uroot -pliuyang123!
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 2
Server version: 5.7.38 MySQL Community Server (GPL)

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> 

不进入数据库查看他的内容

[root@YL ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql DDL的操作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建一个名为liuyang的数据库
mysql> create database if not exists liuyang;
Query OK, 1 row affected (0.01 sec)
查看数据库
mysql> show databses;
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 'databses' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuyang            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

删除liuyang这个数据库
mysql> drop database if exists liuyang;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

表操作

mysql> create table ly(id int not null,name varchar(20),age tinyint(2));
Query OK, 0 rows affected (0.01 sec)
查看表
mysql> show tables;
+-------------------+
| Tables_in_liuyang |
+-------------------+
| ly                |
+-------------------+
1 row in set (0.00 sec)
mysql> drop table if exists ly;
Query OK, 0 rows affected>  (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)

创建用户

mysql>  CREATE USER 'liuyang'@'127.0.0.1' IDENTIFIED BY 'liuyang123!';
Query OK, 0 rows affected (0.00 sec)
[root@YL ~]# mysql -uliuyang -pliuyang123! -h 127.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.38 MySQL Community Server (GPL)

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> drop user 'liuyang'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

查看支持的所有字符集

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
...........
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
查看当前数据库支持的所有存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
查看数据库信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
不进入某数据库而列出其包含的所有表
mysql> show tables from liu;
+---------------+
| Tables_in_liu |
+---------------+
| liuyang       |
+---------------+
1 row in set (0.00 sec)
查看表结构
mysql> desc liu.liuyang;
+-------+--------------+------+-----+---------+-------+
| 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.01 sec)
查看某表的创建命令
mysql> show create table liu.liuyang;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                             |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| liuyang | CREATE TABLE `liuyang` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看某表的状态
mysql> show table status like 'liuyang'\G
*************************** 1. row ***************************
           Name: liuyang
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-07-25 21:28:00
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

这篇关于mysql的安装与DDL的使用的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！