Crazy god said MySQL learning notes

If the computer is 64-bit, download it and use the 64-bit version!

Installation steps

1. Download the ZIP package.

D: Environment\mysql-5.7.19 D: Environment\mysql-5.7.19

Add environment variables: My Computer -> Properties -> Advanced -> Environment Variables

Select PATH and add the bin folder below your mysql installation fileCopy the code

4. Edit the my.ini file and pay attention to the replacement path

[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables
Copy the code

5, start CMD in admin mode, switch to bin directory in mysql, and type mysqld — install

Mysqld –initialize — insecure –user=mysql to initialize the data file

Mysql -u root -p mysql -u root -p

8. Change the root password

update mysql.user set authentication_string=password('123456') where user='root'and Host = 'localhost';
Copy the code

9. Refresh permissions

flush privileges;
Copy the code

Ini file to delete skip-grant-tables

11. Restart mysql to work normally

net stop mysql
net start mysql
Copy the code

12, the connection test appears the following results on the installation

Step by step, there is no problem in theory.

If you have installed before and now need to reinstall, be sure to clean the environment.

Okay, so we’re all set up here, because we’re new, so we’re not going to learn commands.

Here’s a tool I recommend: SQLyog.

Even with visual tools, but the basic DOS naming we still want to remember!

SQLyog

You can manually manage the MySQL database

Features: simple, easy to use, graphical

Do the following yourself using the SQLyog administration tool:

• Connect to the local MySQL database
• Create MySchool database
• • field
  • GradeID : int(11) , Primary Key (pk)
  • GradeName : varchar(50)
  • Database name MySchool
  • Create a new database table (grade)

You can see the statements for the corresponding database operations in the history.

Connecting to a Database

The MySQL command window is displayed

• Go to the installation directory \mysql\bin in the DOS command line window
• Environment variables can be set, set the environment variables, can be opened in any directory!

Mysql -h server host address -u username -p user password

Note: -p cannot be followed by a space, otherwise it will be regarded as the content of the password, resulting in login failure!

A few basic database operation commands:

update user set password=password('123456')where user='root'; Change password Flush PRIVILEGES; Update database show databases; Display all database use dbname; Open a database show tables; Describe user; Mysql > create database name; Create database use databasename; Select database exit; Exit the Mysql? Command keywords: Ask for help -- indicates a commentCopy the code

A type of DDL with the syntax:

The create table (if not the exists] ` table name ` (' field name 1 column type [properties] [index] [note], 'the field name 2 column type [properties] [index] [note], #... Column type [attribute][index][comment])[table type][table character set][comment];Copy the code

Note: Backquotes are used to distinguish MySQL reserved words from ordinary characters (the key below esc).

UnSigned

• unsigned
• Declare that the data column does not allow negative numbers.

ZEROFILL

• 0 filled
• Less than digits are filled with 0, such as int(3), and 5 with 005

Auto_InCrement

• Auto-growing, automatically increments the number of previous records by 1 for each entry (default)
• It is usually used to set the primary key and is an integer
• Start value and step size can be defined
• • Current table setting step (AUTO_INCREMENT=100) : Only the current table is affected
  • SET @@auto_increment_increment=5 ; Affect all tables that use increment (global)

NULL and NOT NULL

• The default is NULL, that is, no value is inserted for the column
• If set to NOT NULL, the column must have a value

DEFAULT

• The default
• Used to set default values
• For example, the gender field, the default is “male”, otherwise “female”; If no value is specified for this column, the default value is the value of “male”

- the target: Varchar (20) Name, gender varchar(2), date of birth (datatime), home address,email -- before creating table, CREATE TABLE IF NOT EXISTS 'student' (' id 'int(4) NOT NULL AUTO_INCREMENT COMMENT' student ', 'name' varchar(30) NOT NULL DEFAULT 'anonymous' COMMENT ',' PWD 'varchar(20) NOT NULL DEFAULT '123456' COMMENT' password ', 'sex' varchar(2) NOT NULL DEFAULT 'male' COMMENT 'gender ',' birthday 'datetime DEFAULT NULL COMMENT' birthday ', 'address' varchar(100) DEFAULT NULL COMMENT 'address ',' email 'varchar(50) DEFAULT NULL COMMENT' email ', PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET=utf8 SHOW CREATE DATABASE school; SHOW CREATE TABLE student; Table DESC student; SET sql_mode='STRICT_TRANS_TABLES';Copy the code

ALTER TABLE ALTER TABLE

ALTER TABLE old TABLE name RENAME AS new TABLE name

ALTER TABLE table_name ALTER TABLE table_name

Modify field:

• ALTER TABLE name ALTER TABLE name ALTER TABLE name
• ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE

ALTER TABLE DROP field name

Delete table

Syntax: DROP TABLE [IF EXISTS] Specifies the name of the TABLE

• IF EXISTS is optional to determine whether the data table EXISTS
• An error is thrown if a table does not exist

other

1. Use backquotes (') as identifiers (library name, table name, field name, index, alias) to avoid the same name as the keyword! Chinese can also be used as an identifier! 2. Each library directory has an option file db.opt to save the current database. 3. Comment: single line comment # Comment content Multiple line comment /* Comment content */ Single line comment -- Comment content (standard SQL comment style, requiring double dashes followed by a space (space, TAB, newline, etc.)) 4. Pattern wildcard: _ Any single character % Any number of characters, including zero single quotes, need to be escaped \' 5. , "\G", "\G", only affects the display result. The rest of the place ends with a semicolon. Delimiter Modifies the statement terminator of the current conversation. SQL not sensitive to case (keyword) 7. Clear existing statement: \cCopy the code

Foreign key concepts

If a public key is the primary key in one relationship, that public key is called the foreign key of another relationship. Thus, a foreign key represents a correlation between two relationships. Tables with a foreign key of another relationship as the primary key are called primary tables, and tables with additional keys are called secondary tables of the primary table.

In practice, the association is represented by placing values from one table into a second table, using primary key values from the first table (including compound primary key values if necessary). At this point, the property in the second table that holds these values is called a foreign key.

Foreign key role

To maintain data consistency, integrity, the main purpose is to control the data stored in the foreign key table, constraint. To form an association between two tables, a foreign key can only reference the value of a column in the outer surface or use null values.

Create a foreign key

Specify foreign key constraints when creating a table

Create a foreign key CREATE TABLE 'grade' (' grade ID' INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade ID', 'gradename' VARCHAR(50) NOT NULL COMMENT 'grade ', PRIMARY KEY (' gradeID ') ENGINE=INNODB DEFAULT CHARSET= UTF8 -- CREATE TABLE (student id, name, gender, grade, phone, address, birth date, email, ID number 'student' (' studentno 'INT(4) NOT NULL COMMENT' studentNo ', 'studentName' VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT' studentNo ', 'sex' TINYINT(1) DEFAULT '1' COMMENT 'gender ',' gradeID 'INT(10) DEFAULT NULL COMMENT' grade ', 'phoneNum' VARCHAR(50) NOT NULL COMMENT 'phone ',' address 'VARCHAR(255) DEFAULT NULL COMMENT' address ', 'borndate' DATETIME DEFAULT NULL COMMENT 'iD ',' email 'VARCHAR(50) DEFAULT NULL COMMENT' id ', 'idCard' VARCHAR(18) DEFAULT NULL COMMENT 'iD ', PRIMARY KEY (' studentno'), KEY 'FK_gradeid' (' gradeID '), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8Copy the code

Modify the table after it is built

Create a foreign key. ALTER TABLE 'student' ADD CONSTRAINT 'FK_gradeid' FOREIGN KEY (' gradeID ') REFERENCES 'grade' (`gradeid`);Copy the code

Remove the foreign key

Operation: Delete the grade table

Note: When deleting a table with a primary and foreign key relationship, delete the child table first and then the primary table

ALTER TABLE student DROP FOREIGN KEY FK_gradeid; ALTER TABLE student DROP INDEX FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeid FK_gradeidCopy the code

The INSERT command

Grammar:

INSERT INTO table name [(表 1, 表 2, 表 3,...)] VALUES(' 1',' 2',' 3')Copy the code

Note:

• Use commas (,) to separate fields or values.
• ‘Field 1, Field 2… ‘This section can be omitted, but the added values must correspond to the table structure, data column, order, and number.
• Multiple data can be inserted at the same time. Separate values with commas (,).

How to add statements using statements? INSERT INTO table name [(表 1, 表 2, 表 3,...)] INSERT INTO grade(gradename) VALUES(' grade 1',' grade 2',' grade 3'); Primary key increment, can be omitted? INSERT INTO grade VALUES (' 四 '); INSERT INTO grade VALUE (' 四 ') INSERT INTO grade VALUE (' 四 ') Column count doesn't match VALUE count at row 1 INSERT INTO grade(gradename) VALUES (' senior '); INSERT INTO grade(gradename) VALUES (' senior ');Copy the code

Practice topic

Add data to the schedule subject yourself using the INSERT statement. Use the foreign key.

The DELETE command

Grammar:

DELETE FROM table name [WHERE condition];Copy the code

Note: condition is the filter condition. If condition is not specified, all column data in the table will be deleted

DELETE FROM grade WHERE gradeID = 5Copy the code

TRUNCATE command

Function: Used to completely empty the table data, but the table structure, index, constraint, etc.

Grammar:

TRUNCATE [TABLE] table_name; TRUNCATE Grade Clear the grade tableCopy the code

Note: Different from the DELETE command

• Same: Data can be deleted without deleting table structures, but the TRUNCATE speed is faster
• Different:
• • Use TRUNCATE TABLE to reset the AUTO_INCREMENT counter
  • Use of TRUNCATE TABLE does not affect transactions

Testing:

CREATE TABLE 'test' (' id 'INT(4) NOT NULL AUTO_INCREMENT,' coll 'VARCHAR(20) NOT NULL, PRIMARY KEY (' id ') ENGINE=INNODB DEFAULT CHARSET=utf8 VALUES('row1'),('row2'),('row3'); Delete FROM test; delete FROM test; Conclusion: If no Where value is specified, all columns in the TABLE are deleted, and logs are recorded. -- Conclusion: Truncate deletes data, and self-increasing the current value restores to the initial value and starts again; No logging is done. -- Use DELETE to clean up database table data from different engines as well. -- InnoDB: the increment column starts from the initial value (because it is stored in memory, it will be lost if power is off) -- MyISAM: The increment column still starts from the last increment data base (stored in a file, will not be lost)Copy the code

DQL(Data Query Language)

• Query database data, such as a SELECT statement
• Simple single-table queries or complex and nested queries with multiple tables
• Database language is the most core, the most important statement
• Use the most frequently used statement

SELECT the grammar

SELECT [ALL | DISTINCT] {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} FROM table_name [as Table_alias] [left | right | inner join table_name2] - a federated query [WHERE]... [GROUP BY...] [HAVING] Specifies which fields to group results BY [ORDER BY...] - specifies the query record sorting by one or more conditions [LIMIT {[offset,] row_count | row_countOFFSET offset}]. -- Specifies from which records to which records to queryCopy the code

Note: the [] brackets represent optional, and the {} brackets represent required

Function: Used to retrieve records in a data table that match criteria

The search criteria can consist of one or more logical expressions, and the results are generally true or false.

Logical operator

test

-- Query that meets the conditions (WHERE) SELECT Studentno,StudentResult FROM result; SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100; SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 && StudentResult<=100; -- Fuzzy query (exact query) SELECT Studentno,StudentResult FROM result WHERE StudentResult BETWEEN 95 AND 100; Studentno,studentresult FROM result WHERE studentno! = 1000; -- Use NOT SELECT studentno,studentresult FROM result WHERE NOT studentno=1000;Copy the code

Fuzzy query: Comparison operator

Note:

• Arithmetic operations can be performed between records of numeric data types;
• Only data of the same data type can be compared;

Testing:

- fuzzy query between the and \ like \ \ in null - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = - like - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = -- query name is liu's classmate student number and name - like the combination of using wildcards: % (0 to any character) _ (one character) SELECT studentno,studentname FROM student WHERE studentname LIKE 'liu %'; Studentno,studentname FROM student WHERE studentname LIKE 'student_ '; SELECT studentname FROM student WHERE studentname LIKE' student_ '; Studentno,studentname FROM student WHERE studentname LIKE 'student__ '; studentno,studentname FROM student WHERE studentname LIKE' student__ '; SELECT studentno,studentname FROM student WHERE studentname LIKE '% student% '; -- Use escape symbol '\' -- use custom escape keyword: ESCAPE ':' -- ============================================= -- IN -- ============================================= -- SELECT studentno,studentname FROM student WHERE studentno IN (1000,1001,1002); - query address IN Beijing, nanjing, luoyang, henan students SELECT studentno, studentname, address the FROM student WHERE the address IN (' Beijing ', 'nanjing', 'henan luoyang); - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = - NULL empty - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = - Studentname =NULL SELECT studentname FROM student WHERE BornDate is NULL; SELECT studentname FROM student WHERE BornDate IS NOT NULL; SELECT studentname FROM student WHERE Address= "OR Address IS null;Copy the code

test

/ * = = = = = = = = = = = = = = sort = = = = = = = = = = = = = = = = syntax: the ORDER BY the ORDER BY statement is used to according to the specified column to sort the result set. BY default, the ORDER BY statement sorts records in ASC ascending ORDER. If you want to sort the records in descending order, use the DESC keyword. 1 * /, query the database structure all the exam results (student id student's name Course name Grades) - according to the result descending order SELECT s.s tudentno, studentname, subjectname, StudentResult the FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON r.subjectno = sub.subjectno WHERE Subjectname = 'database structure - 1' ORDER BY StudentResult DESC / * = = = = = = = = = = = = = = paging = = = = = = = = = = = = = = = = syntax: SELECT * FROM table LIMIT/offset, rows | rows offset offset benefits: (user experience, network transmission, query pressure) is derived: the first page: LIMIT 0, 5 second page: Limit 5,5 page 3: limit 10,5...... N page: Limit (pageno-1)*pageSzie,pageSzie [pageNo: page number,pageSize: number of pages displayed on a page] */ -- Display 5 data on a page SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN 'subject' sub ON R.subjectNo = sub. subjectNo WHERE SubjectName =' database structure-1 'ORDER BY StudentResult DESC, Studentno LIMIT 0,5 -- SELECT top 10 students from JAVA studentno LIMIT 0,5 -- SELECT top 10 students from JAVA studentno LIMIT 0,5 -- SELECT top 10 students from JAVA studentno LIMIT 0,5 -- SELECT top 10 students from JAVA studentno LIMIT 0,5 -- SELECT top 10 students from JAVA studentno LIMIT 0,5 s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN 'subject' sub ON r.subjectno = sub.subjectno WHERE subjectname='JAVA first year 'ORDER BY StudentResult DESC LIMIT 0,10Copy the code

-- aggregate function /*COUNT: non-empty */ SELECT COUNT(studentname) FROM student; SELECT COUNT(*) FROM student; SELECT COUNT(1) FROM student; /* recommend */ -- by definition, count(1) and count(*) represent queries on all rows. -- count(field) counts the number of occurrences of the field in the table, ignoring the case that the field is null. That is, records whose field is null are not counted. -- count(*) includes all columns, equal to the number of rows, including null columns when counting results; -- count(1) uses 1 to represent the line of code, and contains records with null fields when counting results. /* Many people think that count(1) is more efficient than count(*) because count(*) has a full table scan and count(1) can be queried against a single field. In fact, both count(1) and count(*) scan the entire table, counting all records, including those that are null, so their efficiency is almost the same. The count(field), on the other hand, counts the number of records for which the field is not null. Here are some comparisons: 1) Count (1) is faster than count(*) when there is no primary key; 2) Count (primary key) is most efficient when there is a primary key; 3) If the table has only one field, count(*) is efficient. */ SELECT SUM(StudentResult) AS SUM FROM result; SELECT AVG(StudentResult) AS average score FROM result; SELECT MAX(StudentResult) AS the highest score FROM result; SELECT MIN(StudentResult) AS the lowest score FROM result;Copy the code

Topic:

Query the average, highest, and lowest scores of different courses. Prerequisite: SELECT SubjectName,AVG(StudentResult) AS, and MAX(studentResult) AS Maximum score,MIN(StudentResult) AS minimum score FROM result AS r INNER JOIN 'subject' AS s ON R.subjectno = s.subjectno GROUP BY R.subjectno HAVING average score >80; /* where group by. Filters placed after groups use HAVING.. Because having is filtered from the previously filtered field, where is filtered directly from the > field in the data table */Copy the code

MD5 encryption

I. Introduction to MD5

MD5 is message-digest Algorithm 5 to ensure the consistency of information transmission. It is one of the hashing algorithms widely used in computers. MD5 has been widely implemented in mainstream programming languages. Computing data (such as Chinese characters) into another fixed length value is the basis of the hashing algorithm. The predecessors of MD5 are MD2, MD3 and MD4.

Second, to achieve data encryption

Create a new table, testMD5

 CREATE TABLE `testmd5` (
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8
Copy the code

Insert some data

 INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')
Copy the code

If we want to encrypt the PWD column, the syntax is:

 update testmd5 set pwd = md5(pwd);
Copy the code

If the password of a user (such as Kuangshen) is encrypted separately:

 INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
 update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';
Copy the code

Insert new data automatically encrypted

 INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
Copy the code

Query the login user information (md5 comparison is used to check the encrypted password entered by the user for comparison)

 SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');
Copy the code

The role of indexes

• Improve query speed
• Ensure that data is unique
• You can speed up the join between tables to achieve referential integrity between tables
• When using grouping and sorting clauses for data retrieval, grouping and sorting time can be significantly reduced
• Full text search field for search optimization.

classification

• Primary Key index
• Unique index
• General Index
• FullText index

The primary key index

Primary key: an attribute group that uniquely identifies a record

Features:

• The most common type of index
• Ensure that data records are unique
• Determines where specific data is recorded in the database

The only index

Purpose: Avoid duplicate values in a data column of the same table

Differences from primary key indexes

• There can be only one primary key index
• There may be multiple unique indexes

CREATE TABLE `Grade`( `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY, 'GradeName' VARCHAR(32) NOT NULL UNIQUE -- or UNIQUE KEY 'GradeID')Copy the code

Conventional index

Function: Quickly locate specific data

Note:

• The index and key keywords can both set regular indexes
• The field that should be added to the query search criteria
• Do not add too many regular indexes to interfere with data insertion, deletion, and modification operations

CREATE TABLE 'result' (-- omit some code INDEX/KEY 'ind' (' studentNo ', 'subjectNo') -- ADD ALTER TABLE 'result' ADD after creation  INDEX `ind`(`studentNo`,`subjectNo`);Copy the code

The full text indexing

Baidu search: full text index

Function: Quickly locate specific data

Note:

• Can only be used for data tables of type MyISAM
• Can only be used with CHAR, VARCHAR, TEXT data column types
• Suitable for large data sets

/* # CREATE TABLE name (1); , field name 2 Data type [integrity constraints...] [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY INDEX of [name] (field name [(length)] [ASC | DESC])); Method # 2: the CREATE INDEX creation ON the existing table CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX INDEX name ON the name of the table (field name [(length)] [ASC | DESC]); Method # 3: the ALTER TABLE in an existing TABLE create indexes on the ALTER TABLE tablename ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX INDEX name (field name [(length)] [ASC | DESC]); DROP INDEX ON table name; ALTER TABLE table_name DROP PRIMARY KEY; # SHOW INDEX FROM student; ALTER TABLE 'school'. 'student' ADD FULLTEXT INDEX 'studentname' (' studentname '); /*EXPLAIN SELECT * FROM student WHERE studentno='1000'; /* Use full-text index */ -- full-text search is done with the MATCH() function. The search string is given as an argument to against(). The search is performed regardless of alphabetic case. For each row in the table, MATCH() returns a correlation value. That is, the similarity scale between the search string and the text that records the columns specified in the MATCH() list. EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love'); MySQL 5.6, only MyISAM storage engine supports full-text indexing; MySQL 5.6, only MyISAM storage engine supports full-text indexing. MySQL 5.6 and later, MyISAM and InnoDB storage engines support full-text indexing; Full-text indexes can be created only for fields whose data types are CHAR, VARCHar, text, and their families. When testing or using full-text indexes, check to see if your version of MySQL, storage engine, and data type support full-text indexes. * /Copy the code

Extension: Test index

Build app_user table:

CREATE TABLE `app_user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 'name' varchar(50) DEFAULT 'COMMENT ',' email 'varchar(50) NOT NULL COMMENT' username ', 'phone' varchar(20) DEFAULT 'COMMENT' mobile phone number ', 'gender' tinyint(4) unsigned DEFAULT '0' COMMENT 'Gender (0: male; 1: Varchar (100) NOT NULL COMMENT 'password ',' age 'tinyint(4) DEFAULT '0' COMMENT' age ', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP, PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET= utf8MB4 COMMENT='app 'Copy the code

Batch insert data: 100w

Create a user using SQLyog and grant permissions for the demo

Basic commands

/* User and permission management */ ------------------ user information table: Mysql. User -- Refresh PRIVILEGES FLUSH PRIVILEGES -- Add user CREATE user kuangshen IDENTIFIED BY '123456' CREATE user user IDENTIFIED BY [PASSWORD] PASSWORD (string) - You must have global CREATE USER permission for the mysql database, or you must have INSERT permission. - Users can be created but cannot be assigned rights. For example, 'user_name'@'192.168.1.1' - The PASSWORD must be quoted as well as the PASSWORD for pure digits. - To specify a PASSWORD in plain text, ignore the PASSWORD keyword. To specify the PASSWORD as the mixed value returned by the PASSWORD() function, PASSWORD -- RENAME USER kuangshen TO kuangshen2 RENAME USER old_user TO new_user -- SET the PASSWORD SET PASSWORD = PASSWORD(' PASSWORD ') -- SET PASSWORD FOR USER = PASSWORD(' PASSWORD ') -- SET PASSWORD FOR USER -- DROP USER kuangshen2 DROP USER GRANT privileges ON table name TO user name [IDENTIFIED BY [PASSWORD] 'PASSWORD '] - all PRIVILEGES - *.* All tables in all libraries - The library name. Select * from root@localhost; select * from root@localhost; SHOW GRANTS FOR user name -- Check current user privileges SHOW GRANTS; Or SHOW GRANTS FOR CURRENT_USER; Or SHOW GRANTS FOR CURRENT_USER(); REVOKE ALL PRIVILEGES, GRANT OPTION FROM user nameCopy the code

Permissions to explain

-- ALL [PRIVILEGES] -- Set ALL simple PRIVILEGES except GRANT OPTION ALTER -- Allow the use of ALTER TABLE ALTER ROUTINE -- change or cancel the stored subroutine CREATE -- CREATE TABLE CREATE ROUTINE -- CREATE a stored subroutine CREATE TEMPORARY TABLES -- CREATE TEMPORARY TABLE CREATE USER -- CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES are allowed. CREATE VIEW -- Allow CREATE VIEW DELETE -- allow DELETE DROP -- allow DROP TABLE EXECUTE -- allow the user to run the stored subroutine FILE -- Allow SELECT... INTO OUTFILE and LOAD DATA INFILE INDEX -- CREATE INDEX and DROP INDEX INSERT -- INSERT LOCK TABLES -- Allow LOCK TABLES PROCESS for TABLES where you have SELECT privileges -- allow SHOW FULL PROCESSLIST REFERENCES -- not RELOAD -- Allow FLUSH REPLICATION REPLICATION SLAVE -- Used for REPLICATION SLAVE servers (read binary log events from the master server) SELECT -- use SELECT SHOW DATABASES -- SHOW VIEW -- allow SHOW CREATE VIEW SHUTDOWN -- allow mysqladmin SHUTDOWN SUPER -- allow CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL statements, mysqladmin debug Allows you to connect (once), even if max_connections has been reached. UPDATE - allows you to use the UPDATE USAGE, synonymous with the term "without permission GRANT OPTION - allowed to GRANT permission table maintenance / * * /, analysis and storage table keyword distribution ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE TABLE name... CHECK TABLE tbl_name [, tbl_name]... [option] ... Option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} - process data file fragments of OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...Copy the code

Question: Why is data normalization needed?

Substandard table design can cause problems:

• Duplicate information
• Update anomalies
• Insert the abnormal
• • Information cannot be represented correctly
• Remove abnormal
• • Loss of valid information

The three major paradigm

First Normal Form (1st NF)

The goal of the first normal form is to ensure the atomicity of each column, satisfying the first normal form if each column is the smallest non-separable unit of data

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) must first satisfy the first normal form (1NF).

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

Third Normal Form (3rd NF)

A relationship satisfies the third normal form if it satisfies the second normal form and all columns except the primary key do not pass dependent on the primary key column.

The third normal form requires that each column in the data table is directly related to the primary key, not indirectly.

The relationship between normalization and performance

To meet certain business goals, database performance is more important than canonical databases

At the same time of data normalization, the performance of database should be considered comprehensively

Significantly reduce the time needed to search for information from a given table by adding additional fields

E [tbl_name]… [option]… Option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} – sorting data file fragments of OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [tbl_name]…

MySQL > replace database with SQLyog; MySQL > replace database with SQLyog; MySQL > replace database with SQLyog * * * * mysqldump client: - database dump - collection database backups - transfers the data to another SQL server, is not necessarily the MySQL server [outside chain archived in images... (img - kwwnyH4I - 1604630594823)"Copy the code

Export —

1. Mysqldump -uroot -p123456 school student >D:/a.sql mysqldump -u -p mysqldump -d :/a.sql
2. Mysqldump -uroot -p123456 school student result >D:/a.sql mysqldump -u username -p password
3. Mysql > mysqldump -uroot -p123456 school >D:/a.sql mysqldump -u -p mysql >D:/a.sql
4. Mysqldump -uroot -p123456 -b school >D:/a.sql mysqldump -u username -p password -b database name > filename (D:/a.sql)

-w can carry backup conditions

– import

1. — source D:/a.sql source Backup file
2. Mysql -u user name -p password database name < backup file if no login is required

** ** Poor database design :** - Data redundancy, storage space waste - data update and insert exceptions - poor program performance ** Good database design :** - Save data storage space - to ensure data integrity - facilitate the development of database application system ** Database design in the development cycle of software projects :** - Requirements analysis stage: Analyze customers' business and data processing needs - Outline design stage: design e-R model diagram of the database, confirm the correctness and integrity of demand information. - Identify the key objects or entities to be managed by the database. An Entity is usually a noun. - Identify the details that each Entity needs to store Three paradigms ** Question: Why data normalization? ** Non-standard table design can cause problems: - Information duplication - update exception - Insertion exception - Incorrect representation of information - Deletion exception - loss of valid information > The Three paradigms ** First Paradigms (1st NF)** The goal of first paradigms is to ensure atomicity of each column, satisfying first paradigms if each column is the smallest non-divisible unit of data The second normal form (2NF) is based on the first normal form (1NF), that is, to satisfy the second normal form (2NF), one must first satisfy the first normal form (1NF). The second normal form requires that each table describe only one thing ** Third normal Form ** A relationship is third normal form if it satisfies the second normal form and all columns except the primary key do not pass dependent on the primary key column. The third normal form requires that each column in the data table is directly related to the primary key, not indirectly. The relationship between standardization and performance of * * * * to meet some business objectives, database performance is more important than the standardized database at the same time as the data standardization, to comprehensively consider the performance of the database By adding additional fields in a given table, to reduce the need to search information from a lot of the time required by inserting computed columns in a given table, to facilitate the queryCopy the code