MySQL之账号管理.建库以及四大引擎

目录

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

二、账户管理

三、数据库创建管理

四、表创建

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

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

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

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 SettingsAll UsersApplication DataMySQL
    C:ProgramDataMySQL
4查看注册表：
regedit
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices
HKEY_LOCAL_MACHINESYSTEMControlSet001Services
HKEY_LOCAL_MACHINESYSTEMControlSet002Services
搜索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.表的创建 ★
•

#案例：创建表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 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、主键(自动增长)、外键、非空等约束的使用

•
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.添加表级约束

•
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)
•
);
•
•
•
#二、修改表时添加约束
•

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;