关系数据库入门

2020-01-13 本文已影响0人人生茫茫好似荒野

关系数据库概述

数据持久化 - 将数据保存到能够长久保存数据的存储介质中，在掉电的情况下数据也不会丢失。

将数据写入到文件；缺点：不方便取出数据和查找数据。
用Excel表格；缺点：不方便查找和修改数据。
数据库管理系统

数据库 - 数据的集散地（仓库）

优点：不仅可以保存数据，更重要的是能够很好的管理数据，方便将来对数据的检索，大多数的数据库都能够保证数据的一致性、完整性并减少数据的冗余
数据库发展史 - 网状数据库、层次数据库、关系数据库、NoSQL数据库。

1970s，IBM的研究员E.F.Codd在Communication of the ACM上发表了名为A Relational Model of Data for Large Shared Data Banks的论文，提出了关系模型的概念，奠定了关系模型的理论基础。后来Codd又陆续发表多篇文章，论述了范式理论和衡量关系系统的12条标准，用数学理论奠定了关系数据库的基础。

关系数据库的特点

理论基础：集合论和关系代数。
具体表象：用二维表（行 - 记录列 - 字段）组织数据。
理论基础：集合论和关系代数。

MySQL简介

MySQL最早是由瑞典的MySQL AB公司开发的一个开放源码的关系数据库管理系统，该公司于2008年被昇阳微系统公司（Sun Microsystems）收购。在2009年，甲骨文公司（Oracle）收购昇阳微系统公司，因此在这之后MySQL成为了Oracle旗下产品。

MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用于中小型网站开发。随着MySQL的不断成熟，它也逐渐被应用于更多大规模网站和应用，比如维基百科、谷歌（Google）、脸书（Facebook）、淘宝网等网站都使用了MySQL来提供数据持久化服务。

甲骨文公司收购后昇阳微系统公司，大幅调涨MySQL商业版的售价，且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展，因此导致自由软件社区对于Oracle是否还会持续支持MySQL社区版（MySQL的各个发行版本中唯一免费的版本）有所担忧，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB（以他女儿的名字命名的数据库）。有许多原来使用MySQL数据库的公司（例如：维基百科）已经陆续完成了从MySQL数据库到MariaDB数据库的迁移。

安装和配置（在CentOS7 Linux环境下）

MySQL有一个分支版本名叫MariaDB，它们的底层库是冲突的，要安装MySQL,必须先保证系统中没有MariaDB相关的文件。如果系统上有MariaDB相关的文件，需要先移除MariaDB相关的文件。

yum list installed | grep mariadb | awk '{print $1}' | xargs yum erase -y

下载

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

解归档

tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

接下来可以按照如下所示的顺序用RPM（Redhat Package Manager）工具安装MySQL。


rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm

接下来启动MySQL服务器

启动服务器：

systemctl start mysqld / service mysqld start

关闭服务器：

systemctl stop mysqld / service mysqld stop

重启服务器：

systemctl restart mysqld

查看运行状态和网络状态


systemctl status mysqld

netstat -ntlp | grep mysql

在日志文件中找出root用户的临时密码

cat /var/log/mysqld.log | grep "A temporary password"

通过MySQL客户端工具连接服务器

mysql -uroot -p

输入临时密码进入，此时不能做任何事情，因为MySQL默认必须修改密码之后才能操作数据库：

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

这里有个问题，新密码设置的时候如果设置的过于简单会报错；原因是因为MySQL有密码设置的规范，具体是与validate_password_policy的值有关，可以降低密码强度。

密码强度设为最低，长度为6，就可以修改密码了


set global validate_password_policy=0;

set global validate_password_length=6;

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

此时的root用户是没有权限的，接下来创建远程连接用户并赋予用户权限


create user 'root'@'%' identified by '123456';

grant all privileges on *.* to 'root'@'%' with grant option;

刷新权限

flush privileges;

常用命令

查看服务器版本

select version();

查看当前所有数据库

show databases;

切换到指定数据库

use 数据库名;

查看当前数据库下所有表

show tables;

查看当前使用的数据库

select database();

查看表的结构

desc 表名;

查看当前用户

select user();

查看当前时间

select now();

显示数据库的创建和数据表的创建


show create database 数据库名;

show create table 数据表名;

获取帮助


? contents;

? functions;

? numeric functions;

? round;

? data types;

? longblob;

SQL基本语法

我们通常可以将SQL分为三类：DDL（数据定义语言）、DML（数据操作语言）和DCL（数据控制语言）。

DDL主要用于创建（create）、删除（drop）、修改（alter）数据库中的对象，比如创建、删除和修改二维表；

DML主要负责插入数据（insert）、删除数据（delete）、更新数据（update）和查询（select）；

DCL通常用于授予权限（grant）和召回权限（revoke）。

说明：SQL是不区分大小写的语言；每条SQL语句必须以分号结束

DDL - 数据定义语言


-- 0. 删除数据库: drop database 数据库名;

DROP DATABASE school;  -- 直接删除指定数据库

DROP DATABASE if EXISTS school;  -- 如果指定的数据库存在就删除数据库

-- 1.创建数据库: create database 数据库名;

create database school;  -- 直接创建指定数据库

CREATE database if not EXISTS school; 

-- 当指定数据库不存在的时候才创建数据库

create database if not EXISTS school default charset utf8; 

-- 创建数据库的时候设置字符集编码方式为utf8,让数据库支持中文数据的存储

-- 2.使用/切换/数据库: use 数据库名;

use school;

-- 3.新建表: create table if not exists 表名(字段名1 类型1, 字段2 类型2,...);

-- 注意： a. 表名一般需要加前缀't'或者'tb'   

b.字段用来确定表中要存储哪些数据，字段名随便命名但是不能是关键字 

c.数据类型必须是MySQL支持的数据类型

-- 常用数据类型: int-整数, char(size)-定长字符串, varchar(size)-不定长字符串, text-字符串, bit-布尔, date-日期

CREATE TABLE if not EXISTS t_student(stuid int, stuname varchar(20), gender bit, birth date); 

-- 新建表并且添加约束: create table if not exists 表名(字段名1 类型1 约束1, 字段2 类型2 约束2,...);

-- 常见约束: not null - 不为空,  default  - 设置默认值,  unique - 值唯一,  primary key - 主键约束 ， foregin key - 外键约束

-- 主键约束:  主键的值可以确定列表中唯一一条记录(通过一个主键值可以找到表中的唯一一条记录)

-- 注意: auto_increment只针对主键有效,并且主键的类型是整型;

CREATE TABLE if not EXISTS t_student

(

stuid int not null auto_increment,

stuname varchar(20) not null,

gender bit DEFAULT 1,

birth date,

PRIMARY KEY(stuid)  -- 将字段stuid作为当前表的主键（设置主键可以间接约束这个字段的值是唯一的）

);

-- 4.删除表: DROP TABLE if EXISTS 表名;

DROP TABLE if EXISTS t_student; 

-- 清空表中的数据: TRUNCATE TABLE 表名;

TRUNCATE TABLE t_student;

-- 5.修改表

-- 5.1 添加列: alter TABLE 表名 add COLUMN 字段名 字段类型 约束;

alter TABLE t_student add COLUMN score FLOAT(8,2) DEFAULT 0;

alter TABLE t_student add COLUMN addr VARCHAR(100);

-- 5.2 删除列: alter TABLE 表名 drop COLUMN 字段名;

alter TABLE t_student drop COLUMN gender;

-- 5.3 修改列: alter TABLE 表名 modify 字段名 字段类型 约束; （不重命名）

--    修改列: alter TABLE 表名 change 原字段名 新字段名 字段类型 约束; （重命名）

--                =============补充==============

-- 常见数据类型:

-- varchar(size) - 不定长字符串，size决定的是最大长度

-- char(size) - 定长字符

-- text - 不限长度(最大是255个字符)

-- int/tinyint - 整型 (-128~127)

-- FLOAT(size,d)/DOUBLE(size,d)  - 小数，size-总长度， d-小数部分长度

-- bit - 布尔，只有0或1

-- date/datetime/time - 值可以是时间函数的结果，也可以时间字符串；计算或者是比较的时候内部是按时间处理的

DML - 数据操作语言


-- 1.增(添加数据/记录)

-- 1.1插入数据/记录: insert into 表名 values(值1, 值2, 值3,....)  - 依次给指定表中的字段赋值

INSERT into t_student VALUES(100, '张三', 0, '2019-9-23');

-- 1.2插入数据/记录: insert into 表名(字段名1,字段名2,...) values(值1, 值2,...) -以指定的顺序给指定的字段赋值 

-- 一次插入一条记录

INSERT into t_student(stuname, birth) VALUES("小花", date(now())); 

-- 一次插入多条记录

INSERT into t_student(stuname, birth) VALUES

("小花", date(now())),

('小明', '2018-9-8'),

('路飞', '1999-12-16'),

('佐助', '2000-10-12');

-- 值的问题: sql中是数字对应的值直接写，字符串需要使用引号引起来，bit类型的值只有0或者1, 时间可以用内容是满足时间格式字符串也可以是通过时间函数获取的值

-- 时间函数: now() - 当前时间  date(now()) - 当前日期  year(now()) - 当前年  month(now()) - 当前月 .... 

-- 2.删（删除数据/记录）

-- delete from 表名;    - 删除指定表中所有记录

DELETE FROM t_student;

-- delete from 表名 where 条件语句;    - 删除满足条件的记录 

-- SQL中的条件语句: =(判断是否相等), <>(不等于,和python中的!=功能一样), >, <, >=, <=

DELETE FROM t_student WHERE stuid=100;   

-- 删除t_student表中stuid的值等于100的记录 

DELETE FROM t_student WHERE stuname='小花'; 

-- 删除t_student表中stuname的值等于'小花'的记录

DELETE FROM t_student WHERE stuid<108; 

-- 删除t_student表中stuid的值小于108的记录 

-- 3.改(修改数据/记录)

-- update 表名 set 字段1=新值1, 字段2=新值2,...;  - 将指定表中所有行的指定列/字段的值赋值为新值

UPDATE t_student set birth='1999-10-1', gender=1;

-- update 表名 set 字段1=新值1, 字段2=新值2,... where 条件语句;  -将表中满足条件的行中指定字段的值赋值为新值 

UPDATE t_student set gender=0 WHERE stuname='小花';

-- 通配符%: 表示任意个数的任意字符（包括0个）

UPDATE t_student set birth='2000-01-01' WHERE stuname LIKE '小%';

-- 修改stuname是以'小'开头的行对应的birth的值

UPDATE t_student set birth='2111-01-01' WHERE stuname LIKE '%小%';

-- 统配符_: 表示一个任意字符

UPDATE t_student set birth='2444-01-01' WHERE stuname LIKE '小_';    --  修改stuname只有两个字符，并且第一个字符是‘小’对应的行的birth的值

-- 注意: 通配符只针对字符串有效! 

-- 4.查(获取数据)

-- 4.1直接查询

-- select * from 表名;          - 获取指定表中所有行和所有的列(所有数据)

SELECT * FROM t_student;

-- select 字段名1,字段名2,... from 表名;  - 获取指定表中所有行指定的列

SELECT stuname,stuid FROM t_student; 

-- select * from 表名 where 条件;        - 获取指定表中所有满足条件的行所有列的数据 

SELECT * FROM t_student WHERE stuid>115;   

-- 4.2列重命名

-- select 字段1 as 新字段1, 字段2 as 新字段2,... from 表名;

-- 注意： 这儿的as可以省略

SELECT stuid as '学号', stuname, gender as '性别' FROM t_student;   

-- 对查询结果中的stuid和gender字段进行重命名

-- 4.3对查询结果重新赋值(一般针对布尔数据)

-- select if(字段名,值1,值2) from 表名;    -查询指定字段，并且判断字段对应的值是0还是1，如果是1结果为值1，否则为值2

-- 注意: 这儿的if的用法是MySQL专有的

-- MySQL写法: if(字段, 新值1, 新值2)

SELECT stuname,if(gender,'男','女') as '性别' FROM t_student;   

-- 通用写法:  case 字段 when 值 then 新值1 else 新值2 end

SELECT case gender WHEN 1 THEN '男' ELSE '女' END as '性别' FROM t_student;

-- 4.4对列进行合并

-- select concat(字段1,字段2,...) from 表名;

SELECT CONCAT(stuname,stuid) as 'name_id' FROM t_student;

SELECT CONCAT(stuname,':',stuid) as 'name_id' FROM t_student;

-- 注意: 数字和字符串数据可以合并，bit类型的数据不可以合并

-- SELECT CONCAT(stuname,':',gender) as 'name_id' FROM t_student; 

-- 4.5模糊查询 - 查询的时候时候通过like条件来指定查询对象

-- sql中支持逻辑运算符and(逻辑与运算)和or(逻辑或运算)，not(逻辑非)

SELECT * FROM t_student WHERE stuname like '%飞%' or not stuid < 110; 

-- 4.6排序(先按之前的任何语法进行查询在排序)

-- select * from 表名 order by 字段;            - 对查询结果按照指定字段的值进行升序排序

-- select * from 表名 order by 字段 asc;        - 对查询结果按照指定字段的值进行升序排序

-- select * from 表名 order by 字段 desc;      - 对查询结果按照指定字段的值进行降序排序

SELECT * FROM t_student ORDER BY gender;      -- 按性别升序排序

SELECT * FROM t_student ORDER BY stuid ASC;  -- 按学号升序排序

SELECT * FROM t_student ORDER BY stuid DESC;  -- 按学号降序排序

--                  =============补充==============

-- 条件语句的写法:

-- 在SQL中可以通过 `where 条件语句`来对操作对象进行筛选 - 筛选 

-- a. 比较运算符： =, <>, >, <, >=, <=

-- 注意: 判断一个字段的值是否为空不用使用=和<>(不等与), 而是使用`is null` 和 `is not null`

SELECT addr FROM t_student;

SELECT stuname FROM t_student WHERE addr is NULL; -- 判断是否为NULL（空）

SELECT stuname FROM t_student WHERE addr<=>NULL; -- 判断是否为NULL（空）

SELECT stuname FROM t_student WHERE addr='';    -- 判断是否是空串

-- b. 逻辑运算符: and, or, not 

-- c. where 字段名  between 值1 and 值2  --  筛选指定的字段的值在值1和值2之间

SELECT stuname,birth FROM t_student WHERE birth BETWEEN '1990-1-1' AND '1999-12-31'; 

-- d. where 字段名 in 集合  -- 筛选出字段值是集合中的元素;(集合是使用括号括起来里面多个值)

SELECT * FROM t_student WHERE stuname in ('小花', '小明', '路飞'); 

-- e. like操作

DCL - 数据控制语言


-- 创建可以远程登录的root账号并为其指定口令

create user 'root'@'%' identified by '123456';

-- 删除用户  - drop user 用户名;

drop user zhangsan;

-- 为远程登录的root账号授权操作所有数据库所有对象的所有权限并允许其将权限再次赋予其他用户

grant all privileges on *.* to 'root'@'%' with grant option;

-- 创建名为hellokitty的用户并为其指定口令

create user 'hellokitty'@'%' identified by '123123';

-- 将对school数据库所有对象的所有操作权限授予hellokitty

grant all privileges on school.* to 'hellokitty'@'%';

-- 召回hellokitty对school数据库所有对象的insert/delete/update权限

revoke insert, delete, update on school.* from 'hellokitty'@'%';