How to design the database and grant permissions?

In the database, we can do many things, such as granting permissions to users, designing the database, etc. Today we will introduce how to design a database and grant permissions.

Permissions and Design Database

User Management

Use SQLyog to create users and grant permissions Demonstration

Basic commands

/* 用户和权限管理 */ ------------------
用户信息表：mysql.user

-- 刷新权限
FLUSH PRIVILEGES
 
-- 增加用户  CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
    - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
    - 只能创建用户，不能赋予权限。
    - 用户名，注意引号：如 'user_name'@'192.168.1.1'
    - 密码也需引号，纯数字密码也要加引号
    - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户  RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码

-- 删除用户  DROP USER kuangshen2
DROP USER 用户名

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
    - all privileges 表示所有权限
    - *.* 表示所有库的所有表
    - 库名.表名 表示某库下面的某表

-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
    -- 查看当前用户权限
    SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

Permission explanation

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限


/* 表维护 */

-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

MySQL backup

Database backup necessity

• Ensure that important data is not lost

• Data transfer

MySQL database Backup method

• mysqldump backup tool

• Database management tools, such as SQLyog

• Copy the database directly Files and related configuration files

mysqldump client

Function:

• Dump database

• Collect the database for backup

• Transfer the data to another SQL server, not necessarily MySQL server

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source  备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

Standardized database design

Why database design is needed

When the database is more complex, we need to design the database

Poor database design:

• Data redundancy, waste of storage space

• Data update and insertion Exception

• Poor program performance

Good database design:

• Save data storage space

• Can ensure data integrity

• Facilitate the development of database application systems

Database design in the software project development cycle:

• Requirements analysis stage: analyze the customer’s business and data processing needs

• Summary design stage: Design the E-R model diagram of the database and confirm that the required information is correct and complete.

Design database steps

• Collect information

  • Communicate and hold discussions with relevant personnel of the system to fully understand user needs and understand the tasks that the database needs to complete.

• Identifies the entity [Entity]

• • Identifies the key object or entity to be managed by the database. The entity is generally a noun

• Identifies the detailed information that each entity needs to store [Attribute]

• Identifies the relationship between entities [Relationship]

Three major paradigms

Question: Why is data normalization needed?

Problems caused by non-standard table design:

• Duplicate information

• Update exception

• Insertion exception

  • Unable to represent information correctly

• Delete exception

  • Lost valid information

Three major paradigms

First Normal Form (1st NF)

The goal of the first normal form is Ensure the atomicity of each column. If each column is the smallest data unit that cannot be subdivided, it satisfies the first normal form

Second normal form (2nd NF)

The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to satisfy the second normal form (2NF), the first normal form (1NF) must first be satisfied.

The second normal form requires each table to describe only one thing

Third normal form (3rd NF)

If a relationship satisfies the second normal form , and other columns except the primary key are not transitively dependent on the primary key column, then the third normal form is satisfied.

The third normal form needs to ensure that each column of data in the data table is directly related to the primary key, and cannot be indirectly related. .

The relationship between normalization and performance

To meet certain business goals, database performance is more important than normalizing the database

While data is normalized, it is necessary to Comprehensive consideration of database performance

By adding additional fields to a given table to significantly reduce the time required to search for information

By inserting calculated columns into a given table , to facilitate query

Related learning recommendations:mysql tutorial(video)

The above is the detailed content of How to design the database and grant permissions?. For more information, please follow other related articles on the PHP Chinese website!