MySQL 服务（上）

最新推荐文章于 2024-11-15 10:39:35 发布

转载最新推荐文章于 2024-11-15 10:39:35 发布 · 412 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://mp.weixin.qq.com/s/LbfMSwBAVVVwqpc8yY098A

标签

#mysql #sql

开源Ops 专栏收录该内容

4 篇文章

订阅专栏

MySQL 数据库，是最常用的关系型数据库。

安装及初始化

配置YUM源

[root@node1 ~]# cat /etc/yum.repos.d/aliyun.repo
[mysql-community]
name=mysql-community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
gpgcheck=0
enabled=1

安装

[root@node1 ~]# yum install -y mysql-community-server
...略
[root@node1 ~]# systemctl start mysqld
[root@node1 ~]# systemctl enable mysqld

初始化

[root@node1 ~]# grep password /var/log/mysqld.log  # 获取临时密码
2020-07-02T09:01:45.347697Z 1 [Note] A temporary password is generated for root@localhost: vYElek9%xdHt
[root@node1 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:  # 输入临时密码

The existing password for the user account root has expired. Please set a new password.

New password:  # 输入新密码

Re-enter new password:  # 输入新密码
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :  # 跳过修改root密码

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y  # 移除匿名用户
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y  # 禁止root用户远程登陆
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y  # 移除测试数据库
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y  # 立即刷新权限表
Success.

All done!

用户管理

用root登陆

[root@node1 ~]# mysql -uroot -p
Enter password: 
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建用户

mysql> create user openstac@localhost identified by 'Openstack666!';
Query OK, 0 rows affected (0.00 sec)

“@” 符号后面localhost表示 openstac 用户只能从本机登陆

修改用户名

mysql> rename user openstac@localhost to 'openstac'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

使 openstac 用户可以从10.0.0.0/24 网段的其它主机登陆，"%" 是地址通配符

修改密码

mysql> set password for 'openstac'@'10.0.0.%' = Password('Openstack111!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

创建一个数据库

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

将userinfo数据库所有表的增、删、改、查、插入数据的权限授给openstac用户

mysql> grant create,drop,alter,select,insert on userinfo.* to 'openstac'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

从node2登陆测试

[root@node2 ~]# yum install -y mysql-community-client
[root@node2 ~]# mysql -u openstac -h 10.0.0.11 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| userinfo           |
+--------------------+
2 rows in set (0.00 sec)

常用SQL语句

删除数据库


[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| userinfo           |
+--------------------+
5 rows in set (0.00 sec)

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

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

创建数据库

mysql> create database customers default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

数据库名称customers，默认字符集utf8，校验规则utf8_general_ci

将customers的所有权限授给webadmin用户(不必提前创建该用户)

mysql> grant all privileges on customers.* to 'webadmin'@'10.0.0.%' identified by 'Web1234!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

从node2登陆，使用customers库

[root@node2 ~]# mysql -u webadmin -h 10.0.0.11 -p
Enter password:
...略
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customers          |
+--------------------+
2 rows in set (0.00 sec)

mysql> use customers;
Database changed

创建表

mysql> create table basic_info(
    -> uid int not null auto_increment primary key,
    -> name char(30) not null,
    -> sex char(6) not null default 'male' check (sex in ('male','female')),
    -> age int not null,
    -> profession varchar(60),
    -> hobby varchar(100))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

基本格式是：
create table 表名(列名类型是否可为空,列名类型是否可为空);
1）数据类型
数值类型：
bit
tinyint
smallint
int
bigint
decimal
float
double

时间类型：
date
time
year
datetime
timestamp

字符串类型：
char
varchar
text
mediumtext
longtext

二进制数据类型：
TinyBlob
Blob
MediumBlob
LongBlob

枚举类型：关键字enum

集合类型：关键字set

2）not null
不允许为空

3）auto_increment
自增列，自增列必须是索引；
用delete清空表的时候不会将自增返回原点，truncate可以；

4）primary key
主键，唯一标识一条记录，不能有重复的，不允许为空，一个表只能有一个主键，用来保证数据完整性；
相对的，一个表可以有多个外键，外键可以重复，可以为空，外键其实就是其它表的主键，用来和其它表建立联系用的；

5）default
设置默认值，插入数据的时不插入这个字段的话，会带入默认值

6）check
检查约束，即某个字段只能从设定的值中取

7）engine=innodb
默认存储引擎

8）default charset=utf8
默认字符集

查看所有表

mysql> show tables;
+---------------------+
| Tables_in_customers |
+---------------------+
| basic_info          |
+---------------------+
1 row in set (0.00 sec)

修改表结构，增加一列

mysql> alter table basic_info add address text;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看某表的表结构

mysql> desc basic_info;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| uid        | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | char(30)     | NO   |     | NULL    |                |
| sex        | char(6)      | NO   |     | male    |                |
| age        | int(11)      | NO   |     | NULL    |                |
| profession | varchar(60)  | YES  |     | NULL    |                |
| hobby      | varchar(100) | YES  |     | NULL    |                |
| address    | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

插入数据

mysql> insert into basic_info(name,age) values('LiLei',18);
Query OK, 1 row affected (0.11 sec)

mysql> insert into basic_info(name,sex,age) values('HanMM','female',17);
Query OK, 1 row affected (0.08 sec)

mysql> insert into basic_info(name,age,hobby) values('Jhon',17,'basketball');
Query OK, 1 row affected (0.01 sec)

mysql> insert into basic_info(name,sex,age,profession,hobby) values('Sarah','female',19,'student','wrestle');
Query OK, 1 row affected (0.00 sec)

mysql> insert into basic_info(name,age,address) values('Ethan',16,'Woodlands');
Query OK, 1 row affected (0.01 sec)

查询数据

mysql> select * from basic_info;  # 查询所有数据
+-----+-------+--------+-----+------------+------------+-----------+
| uid | name  | sex    | age | profession | hobby      | address   |
+-----+-------+--------+-----+------------+------------+-----------+
|   1 | LiLei | male   |  18 | NULL       | NULL       | NULL      |
|   2 | HanMM | female |  17 | NULL       | NULL       | NULL      |
|   3 | Jhon  | male   |  17 | NULL       | basketball | NULL      |
|   4 | Sarah | female |  19 | student    | wrestle    | NULL      |
|   5 | Ethan | male   |  16 | NULL       | NULL       | Woodlands |
+-----+-------+--------+-----+------------+------------+-----------+
5 rows in set (0.01 sec)

mysql> select name from basic_info where age <= 17;  # 查询所有age小于等于17的人的名字
+-------+
| name  |
+-------+
| HanMM |
| Jhon  |
| Ethan |
+-------+
3 rows in set (0.01 sec)

mysql> select name from basic_info where sex='female';  # 查询所有sex等于female的人的名字
+-------+
| name  |
+-------+
| HanMM |
| Sarah |
+-------+
2 rows in set (0.00 sec)

mysql> select name,age,sex from basic_info limit 3;  # 查询表的前3行数据的name,age和sex
+-------+-----+--------+
| name  | age | sex    |
+-------+-----+--------+
| LiLei |  18 | male   |
| HanMM |  17 | female |
| Jhon  |  17 | male   |
+-------+-----+--------+
3 rows in set (0.00 sec)

mysql> select name,sex,age from basic_info order by age asc;  # 查询name,sex和age信息，并按age从小到大排序
+-------+--------+-----+
| name  | sex    | age |
+-------+--------+-----+
| Ethan | male   |  16 |
| HanMM | female |  17 |
| Jhon  | male   |  17 |
| LiLei | male   |  18 |
| Sarah | female |  19 |
+-------+--------+-----+
5 rows in set (0.00 sec)

修改数据

mysql> update basic_info set profession='student',address='Red Hill' where name='Jhon';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from basic_info where name='Jhon';
+-----+------+------+-----+------------+------------+----------+
| uid | name | sex  | age | profession | hobby      | address  |
+-----+------+------+-----+------------+------------+----------+
|   3 | Jhon | male |  17 | student    | basketball | Red Hill |
+-----+------+------+-----+------------+------------+----------+
1 row in set (0.00 sec)

删除数据

mysql> delete from basic_info where age>18;
Query OK, 1 row affected (0.01 sec)

mysql> select uid,name from basic_info;
+-----+-------+
| uid | name  |
+-----+-------+
|   1 | LiLei |
|   2 | HanMM |
|   3 | Jhon  |
|   5 | Ethan |
+-----+-------+
4 rows in set (0.00 sec)