1, Database account management

1. Account creation and password modification

GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'%' IDENTIFIED BY 'zhangsan123' WITH GRANT OPTION;

use mysql;
update user set authentication_string=password('123456') where user='zhangsan';
flush privileges;

2. Create Zara account, password zara123, only with query permission

	grant select
	on t_solr_job.*
	to 'zara'@'localhost'
	identified by'zara123';

2, mysql four engines

Different storage engines have their own characteristics to meet different requirements, as shown in the following table:

InnoDB is a good choice if we want to provide the things security (ACID compatible) capabilities of submission, rollback and crash recovery capabilities, and require concurrent control

If the data table is mainly used to insert and query records, the MyISAM engine can provide high processing efficiency

If the data is only stored temporarily, the amount of data is not large, and high data security is not required, you can choose to save the data in the Memory engine, which is used as a temporary table in MySQL to store the intermediate results of the query

If there are only INSERT and SELECT operations, you can choose archive. Archive supports highly concurrent INSERT operations, but it is not transaction safe. Archive is very suitable for storing archived data. For example, archive can be used to record log information

Which engine to use needs to be selected flexibly. Multiple tables in a database can use different engines to meet various performance and actual needs. Using the appropriate storage engine will improve the performance of the entire database

3, Table management

1. Library management

1. Library creation

Syntax:
Create database [if not exists] library name;
Case: creating library Books

CREATE DATABASE IF NOT EXISTS books ;

2. Modification of Library

Syntax:
RENAME DATABASE books TO new library name;

3. Deleting a library

DROP DATABASE IF EXISTS books;

2, Table management

Create: create
Modify: alter
Deleting: drop

1. Table creation ★

Syntax:
create table table name(
Column name column type [(length) constraint],
Column name column type [(length) constraint],
Column name column type [(length) constraint],
...
Type of column name column [(length) constraint]
)

Case: create table Book

CREATE TABLE book(
	id INT,#number
	bName VARCHAR(20),#Book name
	price DOUBLE,#Price
	authorId  INT,#Author No
	publishDate DATETIME#Date of publication
);

Case: create table author

CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
)
DESC author;

2. Table modification
Syntax:
alter table table name add|drop|modify|change column column name [column type constraint];
case
① Modify column name

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

② Change the type or constraint of the column

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

③ Add new column

ALTER TABLE author ADD COLUMN annual DOUBLE; 

④ Delete column

ALTER TABLE book_author DROP COLUMN  annual;

⑤ Modify table name

ALTER TABLE author RENAME TO book_author;

*3. Deletion of table

DROP TABLE IF EXISTS book_author;
SHOW TABLES;

4. Table copying

INSERT INTO author VALUES
(1,'Haruki Murakami','Japan'),
(2,'Mo Yan','China'),
(3,'Feng Tang','China'),
(4,'Jin Yong','China');

① Copy table structure only

CREATE TABLE copy LIKE author;

② Copy table structure + data

CREATE TABLE copy2 
SELECT * FROM author;

③ Copy only part of the data

CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='China';

④ Just copy some fields

CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

4, Common constraints

1. Classification: Six constraints
① NOT NULL: non null, used to ensure that the value of this field cannot be null
Such as name, student number, etc
② DEFAULT: DEFAULT, used to ensure that the field has a DEFAULT value
Like gender

③ PRIMARY KEY: PRIMARY KEY, used to ensure that the value of this field is unique and non empty
Such as student number, employee number, etc
④ UNIQUE: UNIQUE, used to ensure that the value of this field is UNIQUE, can be empty
Such as seat number
⑤ CHECK: CHECK constraint [not supported in mysql]
Such as age and gender
⑥ FOREIGN KEY: FOREIGN KEY, used to restrict the relationship between two tables, used to ensure that the value of the field must come from the value of the associated column of the main table
Add a foreign key constraint from the table to reference the value of a column in the main table
For example, the major number of the student table, the department number of the employee table, and the work type number of the employee table

2. Add classification of constraints:

① Column level constraints:
All six constraints are syntactically supported, but foreign key constraints have no effect
② Table level constraints:
Except for non empty and default, all other functions are supported
Case 1: add column level constraints when adding tables

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#Primary key
	stuName VARCHAR(20) NOT NULL UNIQUE,#Nonempty
	gender CHAR(1) CHECK(gender='male' OR gender ='female'),#inspect
	seat INT UNIQUE,#only
	age INT DEFAULT  18,#Default constraint
	majorId INT REFERENCES major(id)#Foreign key

);

View all indexes in stuinfo, including primary key, foreign key, unique

SHOW INDEX FROM stuinfo;

Case 2: add table level constraints when adding tables

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#Primary key
	CONSTRAINT uq UNIQUE(seat),#Unique key
	CONSTRAINT ck CHECK(gender ='male' OR gender  = 'female'),#inspect
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#Foreign key
	
);

3. Add constraints when modifying tables
Syntax:
1. Add column level constraint
alter table name modify column field name segment type new constraint;

2. Add table level constraint
alter table table name add [constraint constraint name] constraint type (field name) [reference of foreign key];

Case:

# Pre built table
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;

1. Add non empty constraint

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

2. Add default constraint

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3. Add primary key
① Column level constraint

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

② Table level constraints

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4. Delete constraints when modifying tables

1. Delete non empty constraints

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2. Delete default constraint

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3. Delete primary key

ALTER TABLE stuinfo DROP PRIMARY KEY;

4. Delete unique

ALTER TABLE stuinfo DROP INDEX seat;

5. Delete foreign key

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;