Shulou(Shulou.com)05/31 Report--

Editor to share with you how to design the database and grant permissions, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article. Let's learn about it!

There are many things we can do in the database, such as granting permissions to users, designing the database, and so on.

Permissions and design database

User management

Use SQLyog to create users and grant permissions to demo

Basic command

/ * user and rights management * /-user information table: mysql.user-- refresh permission FLUSH PRIVILEGES-add user CREATE USER kuangshen IDENTIFIED BY '123456'CREATE USER username IDENTIFIED BY [PASSWORD] password (string)-you must have global CREATE USER permission or INSERT permission for mysql database. -you can only create users, not grant permissions. -user name, pay attention to quotation marks: for example, 'user_name'@'192.168.1.1'-password also needs quotation marks, pure numeric password also needs quotation marks-to specify password in plain text, ignore the PASSWORD keyword. To specify the password as the mixed value returned by the PASSWORD () function Need to include the keyword PASSWORD-- rename user RENAME USER kuangshen TO kuangshen2RENAME USER old_user TO new_user-- set password SET PASSWORD = PASSWORD ('password')-set password for current user SET PASSWORD FOR username = PASSWORD ('password')-set password for specified user-delete user DROP USER kuangshen2DROP USER username-assign permissions / add user GRANT permission list ON table name TO user name [IDENTIFIED BY [PASSWORD] 'password']-all privileges indicates all permissions-*. * represents all table-library names of all libraries. Table name indicates a table under a library-- View permissions SHOW GRANTS FOR root@localhost;SHOW GRANTS FOR user name-- View current user rights SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER ();-- revoke permissions REVOKE permissions list ON table name FROM user name, GRANT OPTION FROM user name-- revoke all permissions.

Authority interpretation

-- permission list ALL [PRIVILEGES]-- set all simple permissions ALTER except GRANT OPTION-- allow the use of ALTER TABLEALTER ROUTINE-- change or cancel the stored subroutine CREATE-- allow the use of CREATE TABLECREATE ROUTINE-- create the stored subroutine CREATE TEMPORARY TABLES-- allow the use of CREATE TEMPORARY TABLECREATE USER-- allow the use of CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES. CREATE VIEW-- allow the use of CREATE VIEWDELETE-- allow the use of DELETEDROP-- allow the use of DROP TABLEEXECUTE-- allow users to run stored subroutines FILE-- allow the use of SELECT...INTO OUTFILE and LOAD DATA INFILEINDEX-- allow the use of CREATE INDEX and DROP INDEXINSERT-- allow the use of INSERTLOCK TABLES-- allow you to use tables that have SELECT permissions Using LOCK TABLESPROCESS-- SHOW FULL PROCESSLISTREFERENCES allowed-- RELOAD not implemented-- allowing FLUSHREPLICATION CLIENT-- allowing users to ask for the address of a slave server or master server REPLICATION SLAVE-- for replicating secondary servers (reading binary log events from the master server) SELECT-- allowing SELECTSHOW DATABASES-- displaying all database SHOW VIEW -- allow for SHOW CREATE VIEWSHUTDOWN-- allow for mysqladmin shutdownSUPER-- allow for CHANGE MASTER KILL, PURGE MASTER LOGS and SET GLOBAL statements Mysqladmin debug command Allows you to connect (once), even if the max_connections has been reached. UPDATE-- allows the use of UPDATEUSAGE-- synonymous with "no permissions"-- allows permissions to be granted / * Table maintenance * /-- Distribution of keywords for parsing and storing tables ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table names.-- check one or more tables for errors CHECK TABLE tbl_name [, tbl_name]. [option]... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}-defragment the data file OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]... MySQL backup

Necessity of database backup

Ensure that important data is not lost

Data transfer

Backup method of MySQL Database

Mysqldump backup tool

Database management tools such as SQLyog

Copy database files and related configuration files directly

Mysqldump client

Function:

Dump database

Collect the database for backup

Transfer data to another SQL server, not necessarily MySQL server

-- Export 1. Export a table-mysqldump-uroot-p123456 school student > D:/a.sql mysqldump-u user name-p password library name table name > file name (D:/a.sql) 2. Export multiple tables-mysqldump-uroot-p123456 school student result > D:/a.sql mysqldump-u user name-p password library name Table 1 Table 2 Table 3 > File name (D:/a.sql) 3. Export all tables-mysqldump-uroot-p123456 school > D:/a.sql mysqldump-u username-p password library name > file name (D:/a.sql) 4. Export a library-mysqldump-uroot-p123456-B school > D:/a.sql mysqldump-u user name-p password-B library name > file name (D:/a.sql) can-w carry backup conditions-import 1. In the case of logging in to mysql:-- source D:/a.sql source backup file 2. In the case of not logging in, mysql-u user name-p password library name < backup file normalization database design Why database design is needed

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

Poor database design:

Data redundancy, waste of storage space

Exceptions for data updates and inserts

Poor program performance

Good database design:

Save storage space for data

Can guarantee the integrity of the data.

It is convenient to develop database application system.

Database design in the software project development cycle:

Requirements analysis phase: analyzing customer's business and data processing needs

Summary design phase: design the Emurr model diagram of the database to confirm the correctness and completeness of the requirement information.

Steps for designing a database

Collect information

Communicate with the relevant personnel of the system, have a discussion, fully understand the needs of users and understand the tasks to be completed in the database.

Identify entity [Entity]

Identify the key objects or entities to be managed by the database. Entities are generally nouns.

Identify the details that each entity needs to store [Attribute]

Identify relationships between entities [Relationship]

Three paradigms

Question: why do you need data normalization?

Problems caused by non-standard table design:

Information repetition

Update exception

Insert exception

Unable to represent information correctly

Delete exception

Loss of valid information

Three paradigms

The first normal form (1st NF)

The goal of the first normal form is to ensure the atomicity of each column. If each column is the smallest data unit that can no longer be separated, 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 meet the second normal form (2NF) must first meet the first normal form (1NF).

The second paradigm requires each table to describe only one thing.

The third normal form (3rd NF)

If a relation satisfies the second normal form and none of the columns except the primary key is transitive dependent on the primary key column, then the third normal form is satisfied.

The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly.

The relationship between normalization and performance

In order to meet certain business goals, database performance is more important than standardized database

While standardizing the data, we should comprehensively consider the performance of the database.

Greatly reduce the time it takes to search for information by adding additional fields to a given table

Facilitate queries by inserting calculated columns in a given table

The above is all the contents of the article "how to design the database and grant permissions". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.