MySQL基本知识

一，MySQL的元数据库

1.1.什么是元数据库

记录MySQL自身数据的数据库

1.2.有哪些元数据库

information_schema：信息数据库，保存mysql所维护的其他数据库信息，例如：数据库名，数据库的表，表栏的数据类型与访问权限等

mysql：核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

performance_schema：用于mysql的监控数据的存放

1.3.切换数据库

-- 示例 
use  mysql

二，账户管理

2.1.设置权限

命令格式

grant privileges on databasename.tablename to username@'host'
    -- privileges
        -- 指定select，update等权限，全部权限使用all
    -- databasename
        -- 指定数据库，所有数据库请使用*
    -- tablename
        -- 指定数据表，所有表请使用*
    -- username
        -- 需要赋权的用户名，@带的是Host，表示赋权操作针对那些链接，详情查看【host列说明】

使用示例

GRANT SELECT, UPDATE ON bookshop.`t_book` TO test@'%';
    -- 将bookshop数据库中的t_book表的select，update权限赋予test用户，并且不对ip地址限制
grant all on *.* to dba@'localhost'
    -- 一般用于赋予管理员最高权限，谨慎使用
grant create view on testdb.* to developer@'192.168.0.%'; 
    -- 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建视图的权限
grant show   view on testdb.* to developer@'192.168.0.%'
    -- 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予查看视图的权限
grant index on testdb.* to developer@'192.168.0.%';
    -- 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建索引的权限
grant create routine on testdb.* to developer@'192.168.0.%'
    -- 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予操作存储过程与函数的权限
grant alter  routine on testdb.* to developer@'192.168.0.%';
    -- 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建删除存储过程与函数的权限

2.2.授权用户

命令格式

grant privileges on databasename.tablename to username@'host' with grant option

示例

grant select on testdb.* to dba@localhost with grant option

2.3.查看权限

-- 查看当前用户（自己）权限
show grants

-- 查看其他 MySQL 用户权限
show grants for dba@localhost;

2.4.撤销权限

-- 收回test用户对于bookshop库中t_book表的update权限（ip不限）
REVOKE UPDATE ON bookshop.t_book FROM test@'%';

命令格式

revoke privileges on databasename.tablename from username@'host'

示例

-- 收回test用户对于bookshop库中t_book表的update权限（ip不限）
REVOKE UPDATE ON bookshop.t_book FROM test@'%';

三，MySQL引擎

3.1什么是数据库引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据， 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能

3.2.查看数据引擎

命令

show engines

• Support字段说明
• DEFAULT的为默认的引擎
• 为YES表示可以使用
• 为NO表示不能使用

3.3.MyISAM引擎

• MYISAM强调了快速读取操作
• 使用场景： 大量查询，很少修改的场景
• 存储限制：256TG
• 事务支持：不支持事务

• 3.4.memory

• 所有的数据都保存在内存中，一旦服务器重启，所有Memory存储引擎的表数据会消失但是表结构会保存下来
• 使用场景：由于易失性，可以用于存储在分析中产生的中间表
• 存储限制：取决与RAM
• 事务支持：不支持事务

3.5.InnoDB

• 后者修改快，支持事务
• 使用场景：一般事务性，均使用该引擎，用途最广，如果把握不准使用何种引擎，就使用该引擎
• 存储限制：64TG
• 事务支持：支持事务

3.6.ARCHIVE

• 只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入，支持在自增id上建立索引
• 使用场景：在日志和数据采集的时候可以使用
• 特点
• Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%
• 不支持索引（自增id列除外）
• 支持insert，select操作，但不支持delete，update操作

四，数据库命令

4.1.创建

命令格式

create database 数据库名

create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;

    -- 默认的数据库编码集：utf8
    -- collate表示校验规则为utf8_general_ci
    -- 常用排序类型
        -- utf8_general_cs（区分大小写）
        -- utf8_genera_ci（不区分大小写）

4.2.查看

show databases

4.3.删除

drop database 数据库名
-- 危险操作，先备份

五，建表与约束

5.1.建表

命令格式

CREATE TABLE 表名称(
        列名称1 数据类型 NOT NULL,
        列名称2 数据类型,
        列名称3 数据类型,
        unique(列名称1[,列名称2,...,列名称N])
    )

示例

create table t_student
(
   sid                  int not null comment '学号',
   sname                varchar(60) not null comment '姓名',
   sex                  tinyint not null default 1 comment '性别：1男， 2女',
   age                  tinyint not null comment ' 年龄',
   icard                varchar(18) not null comment '身份证，唯一约束',
   primary key (sid),
   unique key AK_Key_2 (icard)
) comment '学生信息表';

5.2.主键约束

-- PRIMARY KEY
-- 增加主键
    ALTER TABLE 表名 ADD PRIMARY KEY(主键名称)
-- 删除主键
    ALTER TABLE 表名 DROP PRIMARY KEY

5.3.非空约束

 sid                  INT NOT NULL COMMENT '学号',

5.4.外键约束

DROP TABLE IF EXISTS t_score;
CREATE TABLE t_score
(
   id                   INT NOT NULL COMMENT '记录流水号',
   sid                  INT NOT NULL COMMENT '学号',
   cid                  INT NOT NULL COMMENT '课程ID',
   score                FLOAT COMMENT '成绩',
   PRIMARY KEY (id),
   FOREIGN KEY (sid) REFERENCES t_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT,
   UNIQUE KEY AK_Key_2 (sid, cid)
);
说明： sid为本表的外键，关联t_student表中的的sid主键，ON DELETE RESTRICT ON UPDATE RESTRICT说明在本表有数据的情况下，主表的关联键不能删除或更新。

增加外键

-- 增加外键
ALTER TABLE 表名 ADD FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称)

删除外键

-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名

5.5.唯一约束

-- UNIQUE KEY 约束名 (字段)
-- 创建唯一约束
    ALTER TABLE 表名 ADD UNIQUE(列名称1[,列名称2,..])
    create unique index UserNameIndex on 't_user' ('username')
-- 删除唯一约束
    ALTER TABLE 表名 DROP INDEX 唯一约束缩影名称

5.6.默认值约束

default