数据库服务概述、构建MySQL服务、数据库基本管理、数据

2021-08-03 本文已影响0人秋天丢了李姑娘

环境准备

下载环境资料：git clone https://gitee.com/mrzhangzhg/tedu_nsd.git

配置MySQL

准备网络yum源（准备物理机或一台虚拟机作为仓库服务器）

[root@zzgrhel8 ~]# yum install -y httpd php php-mysqlnd php-xml php-json createrepo 
[root@zzgrhel8 ~]# systemctl start httpd
[root@zzgrhel8 ~]# systemctl enable httpd
[root@zzgrhel8 ~]# mkdir /var/www/html/mysql
[root@zzgrhel8 ~]# cd /linux-soft/4/mysql/
[root@zzgrhel8 ~]# tar xf mysql-5.7.17.tar -C /var/www/html/mysql/
[root@zzgrhel8 ~]# cd /var/www/html/mysql/
[root@zzgrhel8 mysql]# createrepo -d .

附：在教室上课同学创建虚拟机的新方法：

# 创建名为mysql1的虚拟机
[root@zzgrhel8 ~]# base-vm create mysql1
# 启动虚拟机
[root@zzgrhel8 ~]# virsh start mysql1
[root@zzgrhel8 ~]# virsh console mysql1
localhost login: root
Password: a
# 配置IP地址为192.168.1.11
[root@localhost ~]# eip 11
[root@localhost ~]# ifdown eth0; ifup eth0
# 修改主机名
[root@localhost ~]# hostnamectl set-hostname mysql1
# 按ctrl + ]退回到物理机

在mysql服务器上安装并启动mysql-community 5.7

[root@mysql1 ~]# vim /etc/yum.repos.d/mysql.repo
[mysql]
name=mysql5.7
baseurl=http://你主机的ip地址/mysql
enabled=1
gpgcheck=0
[root@mysql1 ~]# yum install mysql-community*
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# systemctl enable mysqld

修改mysql密码，导入案例数据库

# 启动Mysql服务时，自动生成了随机密码，写入日志mysqld.log。
# 在mysqld.log中查看生成的密码
[root@mysql1 ~]# grep -i password /var/log/mysqld.log
# 修改数据库的root用户密码为NSD2021@tedu.cn
[root@mysql1 ~]# mysqladmin -uroot -p'A8cCwrjefY(v' password NSD2021@tedu.cn

# 导入数据
# 把tedu_nsd/dbs/mysql_scripts拷贝到数据库服务器
# 在数据库服务器上导入数据
[root@mysql1 ~]# cd mysql_scripts/
[root@mysql1 mysql_scripts]# mysql -uroot -pNSD2021@tedu.cn < nsd2021_data.sql 

# 验证导入的数据
[root@mysql1 ~]# mysql -uroot -p'NSD2021@tedu.cn'
mysql> show databases;   # 查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nsd2021            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


mysql> use nsd2021;    # 切换数据库
mysql> show tables;    # 查看库中所有的表
+-------------------+
| Tables_in_nsd2021 |
+-------------------+
| departments       |
| employees         |
| salary            |
+-------------------+
3 rows in set (0.00 sec)


mysql> select count(*) from departments;   # 查看表记录的数量
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)


mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|      133 |
+----------+
1 row in set (0.00 sec)


mysql> select count(*) from salary;
+----------+
| count(*) |
+----------+
|     8055 |
+----------+
1 row in set (0.00 sec)

附：修改密码策略

策略名称	验证方式
0 or LOW	长度
1 or MEDIUM(默认)	长度；数字，小写/大写，和特殊字符
2 os STRONG	长度；数字，小写/大写和特殊字符；

mysql> show  variables like  "%password%";      //查看变量
mysql> set global validate_password_policy=0;   //修改密码策略
mysql> set global validate_password_length=6;   //修改密码长度
[root@mysql1 ~]# vim /etc/my.cnf    //永久配置
[mysqld]
validate_password_policy=0
validate_password_length=6

配置phpMyAdmin

安装

# 在mysql服务器上安装httpd并启动
[root@mysql1 ~]# yum install -y httpd php php-mysqlnd php-xml php-json
[root@mysql1 ~]# systemctl start httpd
[root@mysql1 ~]# systemctl enable httpd

# 部署phpMyAdmin，通过web页面管理mysql数据库
[root@mysql1 ~]# tar xf phpMyAdmin-2.11.11-all-languages.tar.gz 
[root@mysql1 ~]# mv phpMyAdmin-2.11.11-all-languages /var/www/html/mysqladmin
[root@mysql1 ~]# cd /var/www/html/mysqladmin
[root@mysql1 mysqladmin]# cp config.sample.inc.php config.inc.php   # 创建配置文件
[root@mysql1 mysqladmin]# vim config.inc.php
$cfg['blowfish_secret'] = 'tedu.cn';   # 随便加一些字符

访问http://主机ip地址/mysqladmin。用户名和密码是登陆mysql的root及密码。

案例数据库说明

数据库名为nsd2021，共有三张表
departments表：部门表，共有8个部门

字段	类型	说明
dept_id	int(4)	部门号
dept_name	varchar(20)	部门名

employees表：员工表，共有133位员工，属于不同部门

字段	类型	说明
employee_id	int	员工号
name	varchar()	姓名
birth_date	date	生日
hire_date	date	入职日期
phone_number	char(11)	电话号码
email	varchar(30)	email地址
dept_id	int	所在部门编号

salary表：工资表，记录自2015年以来的工资

字段	类型	说明
id	int	行号
date	date	发工资日期
employee_id	int	员工编号
basic	int	基本工资
bonus	int	奖金

三张表的关系：
- 部门表departments与员工表employees之间有外键约束关系，employees表的的dept_id字段必须出现在departments表中
- 员工表employees和工资表salary表之间有外键约束关系，salary表的employee_id必须出现在employees表中

image

SQL语句基础

常用MySQL命令

mysql> SHOW DATABASES;
# 查看所有数据库
mysql> USE nsd2021;
# 切换指定数据库
mysql> SHOW TABLES;
# 查看当前库中所有的表
mysql> DESC departments;
# 查看表结构
mysql> SELECT DATABASE();
# 查看当前所处的数据库
mysql> SELECT USER();
# 查看当前登陆用户
mysql> SELECT VERSION();
# 查看版本
[root@mysql1 ~]# mysql --version
[root@mysql1 ~]# mysql -V

语法规范

不区分大小写，但建议关键字大写，表名、列名小写
每条命令最好用分号结尾，当然啦，用\g结尾也可以，用\G可以改变查看的方式
每条命令根据需要，可以进行缩进或换行(最好是关键字单独占一行)，如：

mysql> SELECT
    -> name, email
    -> FROM
    -> employees;

注释

单行注释

mysql> # select * from departments
mysql> -- select * from departments

多行注释

mysql> /*
   /*> SELECT
   /*> *
   /*> FROM
   /*> departments;
   /*> */

SQL语句分类

数据查询语言（Data Query Language, DQL）

负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。
数据定义语言 (Data Definition Language, DDL)

负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成
数据操纵语言（Data Manipulation Language, DML）

负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。
数据控制语言 (Data Control Language)

它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。

数据查询语言DQL

基础查询

SELECT 查询的字段列表 FROM 表;

查询的字段列表可以是字段、常量、表达式、函数等

mysql> select dept_name from departments;
# 查单个字段
mysql> select name, email from employees;
# 查多个字段
mysql> select * from departments;
# 查所有字段
mysql> select date, employee_id, basic+bonus from salary;
# 使用表达式
mysql> select 100;
# 查询常量
mysql> select 10+5;
# 查询表达式
mysql> select version();
# 查询函数
mysql> select count(*) from salary;
# 查询函数，统计salary共有多少行记录

使用别名，字段名和别名之间可以用空格或关键字AS

mysql> select dept_id 部门编号, dept_name AS 部门名 from departments;
+--------------+-----------+
| 部门编号     | 部门名    |
+--------------+-----------+
|            1 | 人事部    |
|            2 | 财务部    |
|            3 | 运维部    |
|            4 | 开发部    |
|            5 | 测试部    |
|            6 | 市场部    |
|            7 | 销售部    |
|            8 | 法务部    |
+--------------+-----------+
8 rows in set (0.00 sec)

去重

mysql> select dept_id from employees;
mysql> select distinct dept_id from employees;

使用concat函数进行字符串拼接

mysql> select concat(name, '-', phone_number) from employees;

条件查询

SELECT 查询的字段列表 FROM 表 WHERE 条件;

条件运算符，与python类似，使用
- > 大于
- < 小于
- = 等于
- >= 大于等于
- <= 小于等于
- != 不等于

mysql> select * from departments where dept_id>3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
5 rows in set (0.00 sec)


mysql> select * from departments where dept_id<3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
+---------+-----------+
2 rows in set (0.00 sec)


mysql> select * from departments where dept_id=3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       3 | 运维部    |
+---------+-----------+
1 row in set (0.01 sec)


mysql> select * from departments where dept_id!=3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
7 rows in set (0.00 sec)


mysql> select * from departments where dept_id>=3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
6 rows in set (0.00 sec)


mysql> select * from departments where dept_id<=3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
+---------+-----------+
3 rows in set (0.00 sec)

逻辑运算符，and(&&)、or(||)、not(!)

mysql> select * from departments where dept_id>1 and dept_id<5;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
+---------+-----------+
3 rows in set (0.00 sec)


mysql> select * from departments where dept_id<3 or dept_id>6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
4 rows in set (0.00 sec)


mysql> select * from departments where not dept_id<=6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
2 rows in set (0.00 sec)

模糊查询
- like: 包含
- between xxx and yyy: 在xxx和yyy之间的
- in：在列表中的
- is null：为空，相当于python的None
- is not null：非空

//  %匹配0到多个任意字符
mysql> select name, email from employees where name like '张%';
+-----------+--------------------------+
| name      | email                    |
+-----------+--------------------------+
| 张秀云    | zhangxiuyun@tedu.cn      |
| 张玉英    | zhangyuying@tarena.com   |
| 张璐      | zhanglu@tarena.com       |
| 张晨      | zhangchen@tarena.com     |
| 张桂香    | zhangguixiang@tarena.com |
| 张龙      | zhanglong@tarena.com     |
| 张桂英    | zhangguiying@tarena.com  |
| 张秀兰    | zhangxiulan@tedu.cn      |
+-----------+--------------------------+
8 rows in set (0.00 sec)


# _匹配一个字符
mysql> select name, email from employees where name like '张_';
+--------+----------------------+
| name   | email                |
+--------+----------------------+
| 张璐   | zhanglu@tarena.com   |
| 张晨   | zhangchen@tarena.com |
| 张龙   | zhanglong@tarena.com |
+--------+----------------------+
3 rows in set (0.00 sec)


mysql> select name, email from employees where name like '张__';
+-----------+--------------------------+
| name      | email                    |
+-----------+--------------------------+
| 张秀云    | zhangxiuyun@tedu.cn      |
| 张玉英    | zhangyuying@tarena.com   |
| 张桂香    | zhangguixiang@tarena.com |
| 张桂英    | zhangguiying@tarena.com  |
| 张秀兰    | zhangxiulan@tedu.cn      |
+-----------+--------------------------+
5 rows in set (0.00 sec)


mysql> select * from departments where dept_id between 3 and 5;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
+---------+-----------+
3 rows in set (0.00 sec)


mysql> select * from departments where dept_id in (1, 3, 5, 8);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       3 | 运维部    |
|       5 | 测试部    |
|       8 | 法务部    |
+---------+-----------+
4 rows in set (0.00 sec)


# 匹配部门名为空的记录
mysql> select * from departments where dept_name is null;
Empty set (0.00 sec)


# 查询部门名不为空的记录
mysql> select * from departments where dept_name is not null;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
8 rows in set (0.00 sec)

排序

SELECT 查询的字段列表 FROM 表 ORDER BY 排序列表 [asc|desc];

排序：默认升序

mysql> select name, birth_date from employees where birth_date>'19980101';
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 姚琳      | 1998-05-20 |
| 吴雪      | 1998-06-13 |
| 薄刚      | 2000-05-17 |
| 张玉英    | 1998-06-22 |
| 刘倩      | 1998-10-27 |
| 申峰      | 1999-01-13 |
| 陈勇      | 1998-02-04 |
| 厉秀云    | 1999-09-08 |
| 张桂英    | 1999-05-31 |
| 赵峰      | 1998-03-06 |
| 蒙梅      | 2000-09-01 |
| 陈欢      | 1998-07-01 |
| 马磊      | 2000-08-07 |
| 赵秀梅    | 1998-09-25 |
+-----------+------------+
14 rows in set (0.00 sec)

# 默认升序排列
mysql> select name, birth_date from employees where birth_date>'19980101' order by birth_date;
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 陈勇      | 1998-02-04 |
| 赵峰      | 1998-03-06 |
| 姚琳      | 1998-05-20 |
| 吴雪      | 1998-06-13 |
| 张玉英    | 1998-06-22 |
| 陈欢      | 1998-07-01 |
| 赵秀梅    | 1998-09-25 |
| 刘倩      | 1998-10-27 |
| 申峰      | 1999-01-13 |
| 张桂英    | 1999-05-31 |
| 厉秀云    | 1999-09-08 |
| 薄刚      | 2000-05-17 |
| 马磊      | 2000-08-07 |
| 蒙梅      | 2000-09-01 |
+-----------+------------+
14 rows in set (0.00 sec)

# 降序排列
mysql> select name, birth_date from employees where birth_date>'19980101' order by birth_date desc;
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 蒙梅      | 2000-09-01 |
| 马磊      | 2000-08-07 |
| 薄刚      | 2000-05-17 |
| 厉秀云    | 1999-09-08 |
| 张桂英    | 1999-05-31 |
| 申峰      | 1999-01-13 |
| 刘倩      | 1998-10-27 |
| 赵秀梅    | 1998-09-25 |
| 陈欢      | 1998-07-01 |
| 张玉英    | 1998-06-22 |
| 吴雪      | 1998-06-13 |
| 姚琳      | 1998-05-20 |
| 赵峰      | 1998-03-06 |
| 陈勇      | 1998-02-04 |
+-----------+------------+
14 rows in set (0.00 sec)


# 查询2015年1月10号员工工资情况
mysql> select date, employee_id, basic, bonus from salary where date='20150110';

# 查询2015年1月10号员工工资情况，以基本工资进行降序排列；如果基本工资相同，再以奖金升序排列
mysql> select date, employee_id, basic, bonus from salary where date='20150110' order by basic desc, bonus;

# 查询2015年1月10号员工工资情况，以工资总额为排序条件
mysql> select date, employee_id, basic, bonus, basic+bonus as total from salary where date='20150110' order by total;

附1

创建数据库

mysql> grant all on *.* to 'root'@'%' identified by 'NSD2021@tedu.cn';
# 授权root用户可以通过任何地址访问
mysql> create database mybbs default charset utf8mb4;
# 创建名为mybbs的数据库
mysql> use mybbs ;
mysql> create table posts( id int primary key auto_increment, title varchar(50), pub_date datetime,  content text);
# 创建名为posts的表，有四个字段，用于存储留言

配置nginx服务器

# 安装编译器
[root@nginx1 ~]# yum install -y gcc pcre-devel zlib-devel

# 编译安装nginx
[root@nginx1 ~]# tar xf nginx-1.12.2.tar.gz 
[root@nginx1 ~]# cd nginx-1.12.2
[root@nginx1 nginx-1.12.2]# ./configure 
[root@nginx1 nginx-1.12.2]# make && make install

# 安装并启动php-fpm
[root@nginx1 ~]# yum install -y php-fpm php-mysql
[root@nginx1 ~]# systemctl start php-fpm
[root@nginx1 ~]# systemctl enable php-fpm

# 修改配置文件
[root@nginx1 ~]# vim +65 /usr/local/nginx/conf/nginx.conf
        location ~ \.php$ {
            root           html;
            fastcgi_pass   127.0.0.1:9000;
            fastcgi_index  index.php;
        #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
            include        fastcgi.conf;
        }

# 启动nginx服务
[root@nginx1 ~]# /usr/local/nginx/sbin/nginx -t  # 语法检查
nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok
nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful

[root@nginx1 ~]# /usr/local/nginx/sbin/nginx

修改php首页

# 拷贝php_mysql_bbs目录下所有内容到nginx的文档目录
[root@nginx1 ~]# cp -r tedu_nsd/software/php_mysql_bbs/* /usr/local/nginx/html/

# 修改php页面，使其可以连接到数据库
[root@nginx1 ~]# cd /usr/local/nginx/html/
[root@nginx1 html]# vim index.php  # 只修改第2行
... ...
//以下函数的三个参数分别为：服务器地址、用户名、密码
$con = mysql_connect("localhost","root","NSD2021@tedu.cn");
... ...

# 使用浏览器访问http://web服务器地址/index.php

附2

创建练习数据库

在确保主机联网、yum可用、的前提下，运行脚本cent7_setup.sh

# 创建一个虚拟机，保证它可以连接互联网
[root@zzgrhel8 ~]# base-vm create mysql2
[root@zzgrhel8 ~]# virsh start mysql2
[root@zzgrhel8 ~]# virsh console mysql2
[root@localhost ~]# ip a s   # 查看虚拟机的IP地址

# 把脚本传到虚拟机
[root@zzgrhel8 ~]# scp -r /root/tedu_nsd/dbs/mysql_scripts/setup/ 192.168.1.137:/root

# 在虚拟机上执行脚本
[root@zzgrhel8 ~]# ssh 192.168.1.137
[root@localhost ~]# cd setup/
[root@localhost ~]# yum install -y wget
[root@localhost setup]# bash cent7_setup.sh

脚本将会：
- 创建名为tedu_db的数据库
- 数据库的表与课堂所用的表完全一样，只是数据不一样
- departments表与课上完全一样
- employees中将随机生成133个用户
- salary表将会生成2015年以来的工资数据。起始基本工资从5000到20000不等，每年涨5%；奖金为1000到10000的随机值。

数据库服务概述、构建MySQL服务、数据库基本管理、数据

环境准备

配置MySQL

附：修改密码策略

配置phpMyAdmin

案例数据库说明

SQL语句基础

常用MySQL命令

语法规范

SQL语句分类

数据查询语言DQL

基础查询

条件查询

排序

附1

创建数据库

配置nginx服务器

修改php首页

附2

创建练习数据库

猜你喜欢

热点阅读

数据库服务概述 、 构建MySQL服务 、 数据库基本管理、数据

环境准备

配置MySQL

附：修改密码策略

配置phpMyAdmin

案例数据库说明

SQL语句基础

常用MySQL命令

语法规范

SQL语句分类

数据查询语言DQL

基础查询

条件查询

排序

附1

创建数据库

配置nginx服务器

修改php首页

附2

创建练习数据库

猜你喜欢

热点阅读

数据库服务概述、构建MySQL服务、数据库基本管理、数据