[大数据]MySQL之账号管理.建库以及四大引擎

目录

一、MySQL课程内容涉及及目的

二、账户管理

三、数据库创建管理

四、表创建

一、MySQL课程内容涉及及目的

1、mysql简介

    why？
       数据在内存中容易丢失
       数据在文件中，不便于操作

   what？
       使用完整的管理系统统一管理，易于查询
   where？
       凡是需要持久化存储数据的地方
   术语

       DB
           数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。
       DBMS
           数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器
       SQL
           结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言

2、mysql安装

my.ini

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
?
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\\tools\\mysql-5.7.23-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\\tools\\mysql-5.7.23-winx64\\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

安装MySQL（8.0的版本）

1.解压MySQL
2.修改my.ini文件
3.mysqld install
4.mysqld --initialize --console
5.net start mysql
6.mysql -P 3306 -u root -p
7.ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
8.Navicat链接测试
?
如果MySQL已经安装过，那么执行mysqld remove

安装MySQL（5.7的版本）

1.解压MySQL
2.修改my.ini文件
3.mysqld install
4.mysqld --initialize --console
5.net start mysql
6.mysql -P 3306 -u root -p
7.SET PASSWORD = PASSWORD("123456"); 
8.Navicat链接测试
?
如果MySQL已经安装过，那么执行mysqld remove

删除MySQL（这是5.5版本的卸载方式）

停止MySQL
1添加删除程序中卸载MySQL
2到安装目录删除MySQL
3删除：C:\Documents and Settings\All Users\Application Data\MySQL
    C:\ProgramData\MySQL
4查看注册表：
regedit
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services
搜索mysql，找到一律干掉！
============================
?
需要重启电脑(win xp)，直接重新安装即可

以后在公司，各个职位所具有的的操作权限：

????????项目经理------数据库而言：crud、Create、drop

????????项目组长------数据库而言：crud

????????开发人员------数据库而言：select

??????????????????????????????权限的划分，针对的是生产环境

对于系统开发而言，三个环境

????????线网、生产环境：系统开发完毕，客户使用的那个环境? Linux

????????测试环境：公司内部模拟客户现场，搭建而成的环境

????????开发环境：本地电脑? Windows

?在使用Navicat工具打开MySQL后，要注意其中本身存在的四个表不可删掉，如下图标记所示：

这些是跟我们权限相关的所要用到的表。

????????首先我们需要来看一下mysql默认数据库里面的四张表（user，db，tables_priv，columns_priv）。

1、user表（用户层权限）
?
?
因为字段太多，只截取了一部分。首先登陆的时候验证Host,User,Password(authentication_string)也就是ip,用户名，密码是否匹配，匹配登陆成功将会为登陆者分配权限，分配权限的顺序也是按照上面四张表的排列顺序进行的，举个例子，如果user表的Select_priv为Y说明他拥有所有表的查找权限，如果为N就需要到下一级db表中进行权限分配了。其中的%是通配符，代表任意的意思。
?
2、db表（数据库层权限）
?
?
来到db表之后会匹配Host,User然后会根据Db字段对应的表进行权限分配，像Select_priv这些字段对应的权限大家应该都能看出来是对应着什么权限了吧，这里不细说了（不偷懒，举个例子Select_priv，Insert_priv，Update_priv，Delete_priv，Create_priv，Drop_priv分别代表着查询，增加，更新，删除，创建，销毁）。其中Y代表这拥有此项权限，N则代表没有此项权限。
?
3、tables_priv表（表层权限）
?
?
与上面一样，这是通过Host，Db，User，Table来进行定位到表层的一个权限分配。不过它只有Table_priv和Column_priv两个字段来记录权限。
?
4、columns_priv表（字段层权限）
?
?
顾名思义，字段层权限，通过Host，Db，User，Table，Column来进行定位到字段层的一个权限分配，只有Column_priv来记录权限。

?二、账户管理

三、数据库创建管理

1.MySQL默认数据库介绍
1)information_schema：是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息；
2)mysql：核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息；
3)test：测试数据库，没有东西；
?
2.创建数据库
语法：create database 数据库名;
或者
create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;
?
注：默认的数据库编码集：utf8（即UTF-8），collate表示排序规则为utf8_general_ci
?
3.查看所有数据库
语法：show databases;
?
4.删除数据库(慎用)
语法：drop database 数据库名;

?

?

存储引擎查看
MySQL给开发者提供了查询存储引擎的功能，我这里使用的是MySQL5.1，可以使用：
SHOW ENGINES

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。

?

四、表创建

?

?????????????

?

1、数据类型介绍

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
1）字符类型：char varchar text blob
2）数值类型：int bigint float decimal
int -> int
bigint -> long
float -> 成绩
decimal -> 货币类型(精度,小数)
?
3）日期类型：date time datetime timestamp
?
date -> yyyy:MM:dd HH:mm:ss
time -> HH:mm:ss
datetime -> yyyy:MM:dd
timestamp（时间戳） -> 长整数

2、建表、删表语法

#1.表的创建 ★
?
/*
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
?
?
*/
#案例：创建表Book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
?
?
?
);
?
?
DESC book;
?
#案例：创建表author
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
DESC author;
?
?
#2.表的修改
/*
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
?
#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
?
#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
?
#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
?
#④删除列
ALTER TABLE book_author DROP COLUMN annual;
?
#⑤修改表名
ALTER TABLE author RENAME TO book_author;
?
DESC book;
?
#3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
?
#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
?
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
?
#4.表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
?
SELECT * FROM Author;
SELECT * FROM copy2;
?
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
?
#2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
?
#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
?
?
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

3、主键(自动增长)、外键、非空等约束的使用

/*
?
?
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
?
?
分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
?
?
添加约束的时机：
1.创建表时
2.修改表时
?
?
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
?
表级约束：
?
除了非空、默认，其他的都支持
?
?
主键和唯一的大对比：
?
保证唯一性 是否允许为空 ? 一个表中可以有多少个 ? 是否允许组合
主键√×至多有1个 ? ? ? ? ? √，但不推荐
唯一√√可以有多个 ? ? ? ? √，但不推荐
外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
?
?
*/
?
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
?
)
CREATE DATABASE students;
#一、创建表时添加约束
?
#1.添加列级约束
/*
语法：
?
直接在字段名和类型后面追加 约束类型即可。
?
只支持：默认、非空、主键、唯一
?
?
?
*/
?
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL UNIQUE,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
?
);
?
?
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
?
#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
?
?
#2.添加表级约束
/*
?
语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/
?
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
?
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
?
);
?
?
?
?
?
SHOW INDEX FROM stuinfo;
?
?
?
#通用的写法：★
?
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
?
);
?
?
?
#二、修改表时添加约束
?
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
?
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
?
?
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
?
#4.添加唯一
?
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
?
?
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
?
#三、修改表时删除约束
?
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
?
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
?
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
?
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
?
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
?
SHOW INDEX FROM stuinfo;

?