Authority management

-- Create a new user
create user sinanshao identified by '123456';

-- Change Password
alter user 'sinanshao'@'%' identified by '111111';

-- Change user name
rename sinanshao to sinanshao2;

-- delete user
drop user sinanshao;

-- User authorization,Give the user all permissions
-- all privileges All permissions except authorized permissions
grant all privileges on *.* to sinanshao;

show grants for sinanshao;			-- View permissions
show grants for root@localhost;

-- Delete permissions
revoke all privileges on *.* from sinanshao;

MySql backup

Why backup

• Ensure that important data is not lost
• Facilitate data transfer

MySql backup method

• Copy physical files directly
• Using visualization tools
• Use the command line (not login mysql)

mysqldump -hlocalhost -uroot -p123.com learning student > d:\a.sql			//dump a table
mysqldump -hlocalhost -uroot -p123.com learning student student2 > d:\b.sql	//dump multiple tables
mysqldump -hlocalhost -uroot -p123.com learning > d:\b.sql					//dump database

source d:\a.sql								//Import when logging in to mysql
mysql -uroot -p123.com learning < b.sql		//Import without logging in to mysql

Standardize database design

Why database design

Poor database design:

• Data redundancy, waste of storage space
• Exceptions in data update and insertion
• Poor program performance

Good database design:

• Save data storage space
• Ensure data integrity
• Facilitate the development of database application system

To design a database

• Collect information, communicate and discuss with relevant personnel of the system, fully understand the needs of users and the tasks to be completed by the database

• Identify entity, which identifies the key object or entity to be managed in the database. Entity is generally a noun

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

• Identify relationships between entities [Relationship]

Design and test of personal blog database

1. Collect information and analyze requirements
   • User table
   • Article classification table
   • Article table
   • Comment form
   • Friendship link table
   • User defined table (some information that cannot be listed separately) key: value

-- User table
create table user(
    id int(10) primary key comment 'account',
    name varchar(50) comment 'nickname',
    password varchar(50) comment 'password',
    sex varchar(2) comment 'Gender',
    age int(3) comment'Age',
    sign varchar(200) comment 'Personal signature',
    head_portrait varchar(100) comment 'Avatar link'
);

-- Article classification table
create table category(
    id int(10) primary key comment 'Classification number',
    name varchar(10) comment 'Type name'
);

-- Article table
create table article(
    id int(10) primary key comment 'Article number',
    name varchar(20) comment 'Article title',
    category_id int(10) comment 'Category of article id',
    author_id int(10) comment 'Article Creator id',
    content text comment 'Article content',
    create_time datetime comment 'Creation time',
    update_time datetime comment 'Last modification time'
);

-- Comment form
create table comment(
    id int(20) comment 'Critical id',
    blog_id int(10) comment 'Of the article to which it belongs id',
    created_by_user_id int(10) comment 'Comment posting user id',
    content varchar(200) comment 'Comment content',
    father_id int(10) comment 'Parent comment', -- It's building a building. You need to connect yourself to query
    create_time datetime comment 'Creation time'
);

-- Link table
create table link(
    id int(10) comment 'Link number',
    string_link varchar(200) comment 'Link address'
);

1. Identification entity (implemented in each field, which has been implemented above)
2. Identify relationships between entities
   • user --> blog
   • blog --> category
   • user --> user

Three paradigms of database

Why data normalization?

Problems caused by non-conforming table design:

• Duplicate information
• Update exception
• Insert exception
  • Information cannot be represented correctly
• Delete exception
  • Missing valid information

First normal form (1NF)

Atomicity (non atomicity in transactions): ensure that each column cannot be subdivided

In this table, the two columns of family information and school information can be further divided, which do not meet the atomicity. The adjustment is as follows:

Second paradigm (2NF)

Premise: meet the first paradigm

Complete dependency (non partial dependency): each table only describes one thing, that is, each column in the table must be related to the primary key (you can see it at a glance if it is not a federated primary key), not only a part of the primary key (for a federated primary key)

The order number and product number of this table form a joint primary key, while the order time and order amount are only related to the order number, but not to the product number. They are not completely dependent. They are adjusted as follows and divided into two tables:

Third paradigm (3NF)

Premise: meet the first and second paradigms

Direct correlation (non indirect correlation): each column of data in the table is directly related to the primary key, but not indirectly

The gender and age of the head teacher in this table are not directly related to the students. The adjustment is as follows and is divided into two tables:

Normative and performance issues

The number of associated query tables shall not exceed three (Alibaba standard)

• In order to meet some business goals, database performance is more important than normalized database

• At the same time of data standardization, we should comprehensively consider the performance of the database

• Intentionally adding redundant fields to some tables. (from multi table query to single table query)

• Deliberately add some calculation columns to facilitate query