MySQL数据库管理

最新推荐文章于 2024-10-08 15:41:48 发布

泪雨寄.

最新推荐文章于 2024-10-08 15:41:48 发布

阅读量917

点赞数 18

文章标签：数据库 mysql adb

本文链接：https://blog.csdn.net/L2156823687/article/details/140685519

版权

一、安装MySQL数据库

1）编写一个mysql.sh 脚本，将以下内容写入

#!bin/bash
rpm -qa | grep mariadb
yum -y remove mariadb*
#1.基础环境准备
yum -y install gcc vim wget net-tools lrzsz
#安装 MySQL 依赖的软件包
yum -y install libaio
#创建运行 MySQL 程序的用户
useradd -M -s /sbin/nologin mysql
#二进制安装
tar zxf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql
mkdir /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/data
cd /usr/local/mysql/bin
./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize
#设定配置文件
cat > /etc/my.cnf << EOF
[client]
socket=/usr/local/mysql/data/mysql.sock

[mysqld]
socket=/usr/local/mysql/data/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=2048
character-set-server=utf8
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
EOF
#将 MySQL 的可执行文件写入环境变量中
echo "export PATH=$PATH:/usr/local/mysql/bin">> /etc/profile
. /etc/profile
#配置systemctl 方式启动
cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
cat > /lib/systemd/system/mysqld.service << EOF
[Unit]
Description=mysqld
After=network.target

[Service]
Type=forking
ExecStart=/etc/rc.d/init.d/mysqld start
ExecReload=/etc/rc.d/init.d/mysqld restart
ExecStop=/etc/rc.d/init.d/mysqld stop

PrivateTmp=true

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
netstat -tunlp|grep 3306

2）重新加载环境

[root@bogon ~]# source /etc/profile

3）给脚本设置执行权限

[root@bogon ~]# chmod +x mysql.sh

4）连接MySQL数据库数据随机生成密码

[root@bogon ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28

Copyright (c) 2000, 2019, 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>

5）更改密码，为了登录便利

mysql> set password=password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

二、使用MySQL数据库

1）查看数据库结构

1.查看当前服务器中的数据库

SHOW DATABASES 语句：用于查看当前 MySQL 服务器中包含的数据库，MySQL 的每一条操作语句都是以分号（;）结束的。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28    |
+-----------+
1 row in set (0.00 sec)

2.查看当前数据库中有哪些表

SHOW TABLES 语句：用于查看当前所在的数据库中包含的表。在操作之前，需要先使用 USE 语句切换到所使用的数据库。

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| 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                      |
+---------------------------+
31 rows in set (0.00 sec)

3.查看表结构

DESCRIBE 语句：用于显示表的结构，即组成表的各字段（列）的信息。需要指定“数据库名.表名”作为参数；若只指定表名参数，则需先通过 USE 语句切换到目标数据库。例如，执行以下操作可以查看 mysql 数据库中的 user 表的结构，与直接执行“DESCRIBE mysql.user;”语句的效果相同。

mysql>USE mysql; Database changed mysql>DESCRIBE user;
+ 	+ 	+ 	+ 	+ 	+ +
| Field	| Type	| Null | Key | Default | Extra	|
+ 	+ 	+ 	+ 	+ 	+ +

| Host	| char(60)	| NO	| PRI |	|		|
| User	| char(16)	| NO	| PRI |		|	|
| Password	| char(41)	| NO	|	|		|	|
| Select_priv	| enum('N','Y')	| NO	|	| N		|	|

2）创建及删除数据库和表

1.创建新的数据库

mysql> create database kgc;
Query OK, 1 row affected (0.00 sec)

2.创建新的表

CREATE TABLE 表名 (字段 1 名称类型, 字段 2 名称类型, …, PRIMARY KEY (主键名))

可以用use切入数据库里面创建表，也可以创建的时候写成数据库名.表名

//这里直接数据库名后面跟表名直接创建
mysql> create table kgc.kc65 (id int(11) not null, name varchar(255),age int(11));
Query OK, 0 rows affected (0.01 sec)

3.删除一个数据表

mysql> drop table kgc.kc65;
Query OK, 0 rows affected (0.00 sec)

4.删除一个数据库

mysql> drop database kgc;
Query OK, 0 rows affected (0.00 sec)

5.修改数据记录

UPDATE 表名 SET 字段名 1=字段值 1[,字段名 2=字段值 2] WHERE 条件表达式

mysql> update kgc.kc65 set age=19 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

6.删除数据记录

DELETE FROM 表名 WHERE 条件表达式

mysql> delete from kgc.kc65 where id=3;
Query OK, 1 row affected (0.00 sec)

三、数据库表高级操作

1）清空表

清空一个数据表就是删除这个表内的所有数据。前面的小节已经学习过 DELETE FROM 语句，可以删除表内的数据，除此之外还可以使用 TRUNCATE TABLE 语句实现清空表内记录。DELETE FROM 语句可以使用 WHERE 子句对删除的结果集进行过滤选择，这样更方便、更灵活。TRUNCATE TABLE 语句是删除表中所有记录数据，没法定制，灵活性上稍差。清空表的具体操作如下所示。

mysql>create table tmp like player;//通过 LIKE 方法，复制 player 表生成 tmp 表
Query OK, 0 rows affected (0.01 sec)


mysql>insert into tmp select * from player; //通过 player 表生成 tmp 表内数据记录
Query OK, 3218 rows affected (0.06 sec) Records: 3218   Duplicates: 0   Warnings: 0

mysql>delete from tmp where level >= 45;
Query OK, 7 rows affected (0.01 sec)


mysql>delete from tmp;
Query OK, 3211 rows affected (0.06 sec)


mysql>insert into tmp select * from player;
Query OK, 3218 rows affected (0.07 sec)
Records: 3218 Duplicates: 0 Warnings: 0


mysql>truncate table tmp;
Query OK, 0 rows affected (0.01 sec)


mysql>select count(*) from tmp;
+ 	+
| count(*) |
+ 	+
|	0 |
+ 	+
1 row in set (0.01 sec)

2）临时表

下面创建临时表 mytmp，然后插入数据，之后断开当前连接，最后重新连到 MySQL

查看临时是否还存在，具体操作如下所示。

mysql>select * from mytmp;	//查看mytmp 表是否存在
ERROR 1146 (42S02): Table 'test.mytmp' doesn't exist mysql>CREATE TEMPORARY TABLE `mytmp` (
->`id` int(10) NOT NULL AUTO_INCREMENT,
->`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
-> `level` int(10) NOT NULL,
->PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;	//创建临时表
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mytmp(name,level) values('aa',10);	//插入数据Query OK, 1 row affected (0.01 sec)

mysql>select * from mytmp;
+    + 	+ 	+
| id | NAME | level |
+    + 	+ 	+
|    1 | aa	|	10 |
+    + 	+ 	+
1 row in set (0.00 sec)


mysql>quit //退出当前连接Bye

mysql>select * from mytmp;//重新连接 MySQL 之后查看临时表状态
ERROR 1146 (42S02): Table 'dzm.mytmp' doesn't exist

临时表创建成功之后，使用 SHOW TABLES 命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以手动直接删除，使用 DROP TABLE 语句，具体操作如下所示。

mysql>drop table mytmp;
Query OK, 0 rows affected (0.00 sec)

3）克隆表

在 MySQL 的开发和维护过程中，会有原样拷贝某个数据表的需求。怎么样才能够快速、完整的拷贝数据表呢？先来看一下 CREATE TABLE new_tablename AS SELECT 这个语句，具体实现的 SQL 语句如下所示。

通过 LIKE 方式克隆表

首先，通过在创建表时使用 LIKE 方法，完整复制表结构。LIKE 方法可以将源表完全一样的复制生成一个新表，包括表的备注、索引、主键、存储引擎等，但是不会复制源表内数据记录。

其次，在通过 INSERT INTO...SELECT 方法，将源表内的数据写入新表内。

mysql>create table test like mytmp;//通过 LIKE 方法，复制 mytmp 表生成 test 表
Query OK, 0 rows affected (0.01 sec)


mysql>show create table test\G
*************************** 1. row *************************** Table: test
Create Table: CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`level` int(10) NOT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql>select * from test;	//LIKE 方法复制表结构，不复制数据
Empty set (0.00 sec)


mysql>insert into test select * from mytmp;//将 mytmp 表的数据写入 test 表
Query OK, 1 rows affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0


mysql> select * from test;
+    + 	+ 	+
| id | NAME | level |
+    + 	+ 	+
|    1 | aa	|	10 |
+    + 	+ 	+
3 rows in set (0.00 sec)

四、数据库用户授权

1）授予权限

GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY '密码' ]

mysql>GRANT select ON auth.* TO 'xiaoqi'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

在企业服务器的应用中，数据库与网站服务器有时候是相互独立的。因此在 MySQL 服务器中，应根据实际情况创建新的用户授权，允许授权用户从网站服务器访问数据库。通常的做法是，创建一个或几个网站专用的数据库，并授予所有权限，限制访问的来源 IP 地址。例如，执行以下操作可以新建 bdqn 数据库，并授权从 IP 地址为 192.168.4.19 的主机连接，用户名为“dbuser”，密码为“pwd@123”，允许在 bdqn 数据库中执行所有操作。

mysql>CREATE DATABASE bdqn;

Query OK, 1 row affected (0.00 sec)

mysql>GRANT all ON bdqn.* TO 'dbuser'@'192.168.4.19' IDENTIFIED BY 'pwd@123';

Query OK, 0 rows affected (0.00 sec)

2）查看权限

SHOW GRANTS FOR 用户名@来源地址

mysql>SHOW GRANTS FOR 'dbuser'@'192.168.4.19';
+ 	+
| Grants for dbuser@192.168.4.19	|
+ 	+
| GRANT USAGE ON *.* TO 'dbuser'@'192.168.4.19'	|
| GRANT ALL PRIVILEGES ON `bdqn`.* TO 'dbuser'@'192.168.4.19' |
+ 	+ 2 rows in set (0.00 sec)

3）撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

mysql>REVOKE all ON auth.* FROM 'xiaoqi'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql>SHOW GRANTS FOR 'xiaoqi'@'localhost'; //确认已撤销对auth 库的权限
+ 	+
| Grants for xiaoqi@localhost	|
+ 	+
| GRANT USAGE ON *.* TO 'xiaoqi'@'localhost'	|
+ 	+ 1 row in set (0.00 sec)