data base

Basic concepts of database

1. English words of DataBase: DataBase abbreviation: DB
2. What is a database?
   Warehouse for storing and managing data
3. Characteristics of database
   1. Persistent data storage, in fact, the database is a file system
   2. Easy to store, manage and operate data
   3. A unified way is used to operate the database - SQL
4. Common database software
   • MySQL: open source free database, small database, has been acquired by Oracle. The MySQL 6. X version is also charging. Later, Sun acquired mysql, and Sun was acquired by Oracle
   • Oracle: charge for large database, products of Oracle company.
   • DB2: IBM's database products, for a fee. Often used in banking system
   • SQL Server: a medium-sized database charged by MicroSoft. C ා,. net and other languages are often used.
   • SQLite: a small embedded database, which is applied to the mobile phone, such as Android

MySQL database software

1. install
   Rookie tutorial
2. uninstall
   1. Go to mysql installation directory to find my.ini file
      Copy datadir = " "
   2. Uninstall MySQL
   3. Delete the MySQL folder under the C:/ProgramData directory
3. To configure

MySQL service startup

	1. Start the service manually
	
	2. CMD - > servicse.msc open the service window
	
	3. Open cmd with administrator
	  net start mysql: start mysql service
	  net stop mysql: close mysql service

MySQL login

1. Mysql-uroot-p password

4. Password of mysql-hip-uroot-p connection target

5. mysql --host=ip --user=root --password = password to connect to the target

MySQL exit

1. exit
2. quit

MySQL directory structure

MySQL installation directory: basedir = "D: / development / MySQL /"
		 Configuration file my.ini
 Mysql database directory: datadir="C:/ProgramData/MySQL /..."
		Databases: folders
		Table: Documents
		Data: data in files

SQL

1. What is SQL?

Structured query language: Structured Query Language
insert delete truncate update select drop   
In fact, it defines the rules for operating all relational databases.
There are different ways of database operation, called "dialect".
Mesh database:
Structure database:
Object oriented database:
Tree database:

1. SQL general syntax

3. SQL statements can be written in one or more lines, ending with semicolons.
		2. You can use spaces and indents to enhance the readability of statements.
		3. SQL statements in MySQL database are not case sensitive, and it is recommended to use uppercase keywords.
		4. 3 Notes
			 Single line comment: - comment content or ා comment content (unique to mysql) 
			 Multiline comment: / * comment*/

1. SQL classification

1) DDL(Data Definition Language) data definition language
			Used to define database objects: database, table, column, etc. Keywords: create, drop,alter, etc
 2) DML(Data Manipulation Language)
			It is used to add, delete and modify the data of tables in the database. Keywords: insert, delete, update, etc
 3) DQL(Data Query Language) data query language
			Records (data) used to query tables in a database. Keywords: select, where, etc
 4) DCL(Data Control Language)
			It is used to define the access and security level of the database, and to create users. Keywords: GRANT, REVOKE, etc

DDL: operation database, table

• Operation database: CRUD

1. C(Create): create
			 To create a database:
				 create database database name;
			 Create database, judge it does not exist, and then create: useful
				 create database if not exists database name;
			 Create database and specify character set
				 create database database name character set character set name;

			 Exercise: create a db4 database, determine if it exists, and make the character set gbk
				 create database if not exists db4 character set gbk;
2. R(Retrieve): query
			 Query the names of all databases:
				 show databases;
			 Query character set of a database: query creation statement of a database
				 show create database database name;
3. U(Update): modify
			 Modify the character set of the database
				 alter database database name character set character set name;
4. D(Delete): delete
			 Delete database
				 drop database database name;
			 Judge whether the database exists and delete it again
				 drop database if exists database name;
5. Use database
			 Query the name of the database currently in use
				 select database();
			 Use database
				 use database name; useful****

• Operation table: CRUD

1. C(Create): create
			1. syntax:
				create table table name(
					Column name 1 data type 1 constraint,
					Column name 2 data type 2 constraint,
					....
					Column name n data type N constraint
				);
				 Note: the last column does not need to be comma (,)
				 Database type:
					1. int: integer type
						 age int,
					2. double: decimal type
						 Score double (5,2) 5 is the number of digits on both sides of the decimal point
					3. date: date, including only yyyy MM DD
					4. datetime: date, including yyyy MM DD HH: mm: SS
					5. timestamp: time error type, including yyyy MM DD HH: mm: SS	
						 If you do not assign a value to this field in the future, or the value is null, the current system time will be used by default to assign the value automatically

					6. varchar: string variable character
						 
						 name varchar(20): 20 characters maximum
						 zhangsan 8 characters three 2 characters				
					7. char: immutable character
			Create table
				create table student(
					id int,
					name varchar(32),
					age int ,
					score double(4,1),
					birthday date,
					insert_time timestamp
				);
			Copy table:
				 create table name like the copied table name;	  	
2. R(Retrieve): query
			 Query all table names in a database
				 show tables;
			 Query table structure
				 desc table name;
3. U(Update): modify
			1. Modify table name
				alter table name rename to new table name;
			2. Modify the character set of the table
				alter table table name character set character set name;
			3. Add a column
				alter table name add column name data type;
			4. Modify column name type
				alter table name change column name new column new data type;
				alter table name modify column name new data type;
			5. delete columns
				alter table table name drop column name;
4. D(Delete): delete
			 drop table table name;
			 Drop table if exists table name;

Client graphical tool: SQLYog

• DML: adding, deleting and modifying data in the table

1. Add data:
		 Syntax:
			 insert into table name (column name 1, column name 2,... Column name n) values (value 1, value 2,... Value n), (value 1, value 2,... Value n);
		 Be careful:
			1. The column name and value should correspond one by one.
			2. If no column name is defined after the table name, all columns will be added with values by default
				insert into table name values (value 1, value 2,... Value n);
			3. Except for the number type, other types need to be enclosed in quotation marks (single and double can be used)
			4. When adding, type int, and code is normal (SQL is weak type language)
2. Delete data:
		 Syntax:
			delete from table name [where condition]
		 Be careful:
			1. If no condition is added, all records in the table will be deleted.
			2. If you want to delete all records
				1. delete from table name; -- not recommended. How many records will be deleted--
				When deleting data, the primary key auto increase record is still in use. The next auto increase is based on the last record
				2. TRUNCATE TABLE table name; -- recommended. For higher efficiency, delete the table first, and then create the same table.
				The auto increase record has been cleared. The next time you add data, you should start from 1
			Primary key auto grow; ID 1 2 3... 10 1 2 3
 3. Modify data:
		 Syntax:
			 update table name set column name 1 = value 1, column name 2 = value 2,... [where condition];

		 Be careful:
			1. If no conditions are added, all records in the table will be modified.
			2. The condition is usually the only key in the table;

DQL: querying records in tables

select * from table name

• grammar

select field list from table name list
 where condition list	
group by group field	
Conditions after having grouping
 order by
 Limit paging limit		

• Basic query

1. Query of multiple fields
			select field name 1, field name 2... from table name;
			 Be careful:
				 If you query all fields, you can use to override the field list.
	
2. Remove the repetition:
			 distinct
 3. calculation column
			 Generally, four operations can be used to calculate the values of some columns. (generally only numerical calculation will be carried out)
			 Ifnull (expression1, expression2): null participates in the operation, and the evaluation results are all null
				 Expression 1: which field needs to judge whether it is null
				 Expression 2: the replacement value if the field is null.
4. Starting alias:
			 As: as can also be omitted

• Conditional query

1. where clause followed by condition
		2. operator
			 > ,< ,<= ,>= ,=(==) ,<>(!=)
			 BETWEEN...AND       10~ 30   
			 In (set) corresponds to multiple data
			 LIKE: fuzzy query
				 Placeholder:
					 _: single arbitrary character
					 %: multiple arbitrary characters
			 IS NULL is null
			 IS NOT NULL is not empty
			 And and
			 Or or
			 not non

SQL query case

-- Query age is more than 20 years old

SELECT * FROM student WHERE age > 20;

SELECT * FROM student WHERE age >= 20;

-- Query age equal to 20
SELECT * FROM student WHERE age = 20;

-- Query age is not equal to 20 years old
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

-- Query age ≥ 20 ≤ 30

SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- Query the information of the age of 22, 18 and 25
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

-- Query English score as null
SELECT * FROM student WHERE english = NULL; -- Wrong. null Value cannot be used = (!=) judge

SELECT * FROM student WHERE english IS NULL;

-- Query English score is not null
SELECT * FROM student WHERE english  IS NOT NULL;



-- What are the people with the surname ma? like
SELECT * FROM student WHERE NAME LIKE 'Horse%';
-- The second word of the name is the person

SELECT * FROM student WHERE NAME LIKE "_turn%";

-- People whose name is three words
SELECT * FROM student WHERE NAME LIKE '___';


-- Inquire the person whose name contains virtue
SELECT * FROM student WHERE NAME LIKE '%Virtue%';

DQL: query statement

1. Sort query

2. Sorting Query
 Syntax: order by clause
	 order by sort field 1 sort method 1, sort field 2 sort method 2

 Sort by:
	 ASC: ascending, default.
	 DESC: descending.

 Be careful:
	 If there are multiple sorting conditions, the second condition will be determined only when the condition values of the current edge are the same.

1. Aggregate function: take a column of data as a whole and perform vertical calculation.

1. count(): count
	1. Generally select a non empty column: primary key
	2. count()
2. max(): calculate the maximum value
 3. min(): calculate the minimum value
 4. sum(): Calculation and
 5. avg(): calculate the average value

A column of data: the represented field is put into ()
 Note: the calculation of aggregate function excludes null value.
	Solution:
		1. Select no non empty columns for calculation
		2. IFNULL function

1. Group query:

1. Syntax:group by Group fields;
2. Be careful:
1. Fields queried after grouping: grouping fields, aggregation functions
2. where and having The difference?
	1. where Limit before grouping, and do not participate in grouping if the conditions are not met.havingLimit after grouping. If the result is not satisfied, it will not be queried
	2. where It cannot be followed by an aggregate function,havingThe aggregation function can be judged.

-- Group by gender. Check the average scores of male and female students respectively

SELECT sex , AVG(math) FROM student GROUP BY sex;

-- Group by gender. Check the average scores of male and female students respectively,Number

SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;

--  Group by gender. Check the average scores of male and female students respectively,Number requirement: people with score lower than 70 will not participate in grouping
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

--  Group by gender. Check the average scores of male and female students respectively,Number requirement: people with score lower than 70 will not participate in grouping,After grouping. More than 2 people
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

SELECT sex , AVG(math),COUNT(id) Number FROM student WHERE math > 70 GROUP BY sex HAVING Number > 2;

1. Paging query

1. Syntax: index starting from limit, number of queries per page;
2. Formula: starting index = (current page number - 1) number of items displayed per page
	--3 records per page 

	SELECT * FROM student LIMIT 0,3; -- page 1
	
	SELECT * FROM student LIMIT 3,3; -- page 2
	
	SELECT * FROM student LIMIT 6,3; -- page 3

3. limit is a MySQL "dialect"

4. The first index and the second offset of the parameter after the limit
 5. limit is followed by only one parameter to limit the number of display records

constraint

Concept: limit the data in the table to ensure the correctness, validity and integrity of the data.

• classification

1. Primary key constraint: the primary key guarantees to query with this column of fields to find unique data
 2. Non NULL constraint: not null ensures that the column data cannot be null
 3. Unique constraint: unique ensures the uniqueness of this column of data
 4. Foreign key constraint: foreign key designs multiple tables, and the data of slave tables depends on the main table;
5. Default constraint: default 1 if there is no data in this column, there is a default value; if timestamp is null when adding data, the current system time is default

• Non empty constraint: not null, the value of a column cannot be null

1. Add constraints when creating tables
	CREATE TABLE stu(
		id INT,
		NAME VARCHAR(20) NOT NULL -- name Not empty
	);
2. After creating the table, add a non empty constraint
	ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

3. delete name Non empty constraint of
	ALTER TABLE stu MODIFY NAME VARCHAR(20);

• Unique constraint: unique, the value of a column cannot be repeated

1. note:
	 A unique constraint can have a null value, but only one record can be null
 2. Add unique constraints when creating tables
	CREATE TABLE stu(
		id INT,
		Phone number varchar (20) unique
	);
3. Delete unique constraint
	ALTER TABLE stu DROP INDEX phone_number;
4. After the table is created, add a unique constraint
	ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

• Primary key constraint: primary key

1. Be careful:
	1. Meaning: non empty and unique
	2. A table can only have one field as primary key
	3. The primary key is the unique identification of the records in the table

2. Add a primary key constraint when creating a table
	create table stu(
		id int primary key auto_increment,-- to id Add primary key constraint
		name varchar(20)
	);

3. Delete primary key
	-- error alter table stu modify id int ;
	ALTER TABLE stu DROP PRIMARY KEY;

4. After creating the table, add the primary key
	ALTER TABLE stu MODIFY id INT PRIMARY KEY;

5. Automatic growth:
	1.  Concept: if a column is numeric, use the auto_increment It can be done automatically
	//When there is automatic growth,Failure to add data will still increase
	2. When creating the table, add the primary key constraint and complete the self growth of the primary key
	create table stu(
		id int primary key auto_increment,-- to id Add primary key constraint
		name varchar(20)
	);

	
	3. Delete auto growth
	ALTER TABLE stu MODIFY id INT;
	4. Add automatic growth
	ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

• Foreign key constraint: foreign key, which enables tables to have relationships with tables, so as to ensure the correctness of data.

1. When creating a table, you can add a foreign key
	 Syntax:
		create table table name(
			....
			Foreign key column
			constraint foreign key name foreign key (foreign key column name) references main table name (main table column name)
		);

2. Delete foreign key
	ALTER TABLE table name DROP FOREIGN KEY foreign key name;

3. After creating the table, add the foreign key
	ALTER TABLE table name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY (FOREIGN KEY field name) REFERENCES main table name (main table column name);


4. Cascade operation
	1. Add cascade operation
		Syntax: ALTER TABLE table name ADD CONSTRAINT foreign key name 
				FOREIGN KEY reference main table name (main table column name) ON UPDATE CASCADE ON DELETE CASCADE;
	2. classification:
		1. Cascade update: ON UPDATE CASCADE 
		2. Cascade deletion: ON DELETE CASCADE 

Design of database

• Relationship between multiple tables
  • classification

1. One to one (understanding): user table id username password user information table email phone ID card... User member information account bank number balance point
	 Such as: person and ID card
	 Analysis: one person has only one ID card, and one ID card can only correspond to one person
 2. One to many (many to one):
	 For example: departments and employees
	 Analysis: a department has multiple employees, and an employee can only correspond to one department
 3. Many to many:
	 For example: students and courses
	 Analysis: a student can choose many courses, and a course can also be chosen by many students

• Implementation relationship:

3. One to many (many to one):
	 For example: departments and employees
	 Implementation method: establish a foreign key on one side and point to the primary key of one side.
4. Many to many:
	 For example: students and courses
	 Implementation method: the implementation of many to many relationship requires the help of the third intermediate table. The intermediate table contains at least two fields, which are the foreign keys of the third table and point to the primary keys of the two tables respectively
 5. One to one (understanding):
	 Such as: person and ID card
	 Implementation method: one to one relationship can be implemented by adding a unique foreign key to the primary key of the other party.

• case

-- Create tourism route classification table tab_category
-- cid Primary key of tourism route classification, automatic growth
-- cname Tourism line classification name is not empty, unique, string 100
CREATE TABLE tab_category (
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

-- Create tour route table tab_route
/
rid Primary key of tourism route, automatic growth
rname Travel route name is not empty, unique, string 100
price Price
rdate Listing time, date type
cid Foreign key, classification
/
CREATE TABLE tab_route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate DATE,
	cid INT,
	FOREIGN KEY (cid) REFERENCES tab_category(cid)
);

/Create user table tab_user
uid User primary key, self growth
username User name length 100，Unique, non empty
password Password length 30，Not empty
name Real name length 100
birthday Birthday
sex Gender, fixed length string 1
telephone Phone number, string 11
email Mailbox, string length 100
/
CREATE TABLE tab_user (
	uid INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) UNIQUE NOT NULL,
	PASSWORD VARCHAR(30) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex CHAR(1) DEFAULT 'male',
	telephone VARCHAR(11),
	email VARCHAR(100)
);

/
//Create favorites
rid Tourist route id，Foreign key
date Collection time
uid user id，Foreign key
rid and uid Can't duplicate, set compound primary key, the same user can't collect the same line twice
/
CREATE TABLE tab_favorite (
	rid INT, -- line id
	DATE DATETIME,
	uid INT, -- user id
	-- Create compound primary key
	PRIMARY KEY(rid,uid), -- composite keys
	FOREIGN KEY (rid) REFERENCES tab_route(rid),
	FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

The paradigm of database design

• Concept: some specifications to follow when designing a database. To follow the requirements of the paradigms in the back, you must first follow all the requirements of the paradigms in the front

When designing a relational database, we should follow different specifications and design a reasonable relational database. These different specifications are called different paradigms. The higher the paradigms are, the smaller the redundancy of the database is.
At present, there are six paradigms of relational database: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the bath cod paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as the perfect paradigm).

• Classification:

1. First normal form (1NF): each column is an indivisible atomic data item
 2. Second normal form (2NF): on the basis of 1NF, noncode attributes must completely depend on the code (on the basis of 1NF, the partial functional dependence of noncode attributes on the main code is eliminated)
3. Third normal form (3NF): on the basis of 2NF, any non primary attribute does not depend on other non primary attributes (eliminating the transfer dependency on the basis of 2NF)

1. Function dependency: A -- > B. if the value of attribute A (attribute group) is used, the value of unique attribute B can be determined. B depends on A
	For example: Student No. --- name. (student number, course name) -- > score
 2. Full function dependency: a -- > B. If a is an attribute group, then the value of attribute B depends on all attribute values in attribute group A.
	For example: (student number, course name) -- > score
 3. Partial function dependency: A -- > B. If A is an attribute group, the value of B attribute only depends on some values in A attribute group.
	For example: (student number, course name) -- > name
 4. Transfer function dependency: A -- > b, B -- > C. if the value of the unique B attribute can be determined by the value of the A attribute (attribute group), and the value of the unique C attribute can be determined by the value of the B attribute (attribute group), then the C transfer function depends on A
	For example: Student No. --- Department name, department name -- > department head
 5. Code: if an attribute or attribute group in a table is completely dependent on all other attributes, the attribute (attribute group) is called the code of the table
	For example: the code in the table is: (student number, course name)
	 Primary attribute: all attributes in the code attribute group
	 Non primary attribute: attribute of attribute group except code

From the first paradigm to the second paradigm: take out the part of the dependency and its main attributes to form a new table, and the rest to form a table
From the second paradigm to the third paradigm: take out the attributes of transitive dependency, leave a attribute that depends on the primary attribute, and the attributes of transitive dependency form a new table

Backup and restore of database

• command line

Syntax:
 Backup: mysqldump -u username - p password database name > saved path
 Reduction:
	1. Log in to the database
	2. Create database
	3. Use database
	4. Executive documents. source file path

• Graphical tools

multi-table query

• Query statement

select
		List of column names
from
		List of table names
where....

• Prepare SQL (easy to test)

# Create department table
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('Development Department'),('Marketing Department'),('Finance Department');
# Create employee table
CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), -- Gender
	salary DOUBLE, -- wages
	join_date DATE, -- Date of entry
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) -- Foreign key, related department table(Primary key of department table)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Sun WuKong','male',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Zhu Bajie','male',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Tang Seng','male',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('White bone essence','female',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('spider goblin','female',4500,'2011-03-14',1);

• Cartesian product

 There are two sets A,B. take all the components of these two sets.
 To complete a multi table query, you need to eliminate useless data

Classification of winning queries

• Internal connection query

1. Implicit joins: UsingwhereConditional elimination of useless data
	* Example:
	-- Query all employee information and corresponding department information

	SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
	
	-- Query the name and gender of the employee table. Name of department table
	SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
	
	SELECT 
		t1.name, -- Name of employee form
		t1.gender,-- Gender of employee table
		t2.name -- Name of department table
	FROM
		emp t1,
		dept t2
	WHERE 
		t1.`dept_id` = t2.`id`;


2. Explicit internal connection:
	* Syntax: select List of fields from Table name1 [inner] join Table name2 on condition
	* For example:
		* SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	
		* SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;	

3. Internal connection query:
	1. From which tables to query data
	2. What are the conditions
	3. Which fields to query

• External connection query

1. Left outer connection:
	* Syntax:select List of fields from surface1 left [outer] join surface2 on Conditions;
	* All data in the left table and its intersection are queried.
	* Example:
		-- Query all employee information. If the employee has a department, query the Department name. If there is no department, the Department name will not be displayed
		SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
2. Right outer connection:
	* Syntax:select List of fields from surface1 right [outer] join surface2 on Conditions;
	* It queries all the data in the right table and its intersection part.
	* Example:
		SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

• Subquery

* Concept: a nested query in a query is called a subquery.
	-- Query the highest paid employee information
	-- 1 Query the maximum wage of 9000
	SELECT MAX(salary) FROM emp;
	
	-- 2 Query employee information, and the salary is equal to 9000
	SELECT * FROM emp WHERE emp.`salary` = 9000;
	
	-- One article sql Just do it. Subquery
	SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

* Different sub queries
	1. The result of a subquery is single row and single column:
		* Subqueries can be used as conditions and operators to judge. Operator: > >= < <= =
		* 
		-- Query the person whose salary is less than the average salary
		SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
	2. The result of a subquery is multi row and single column:
		* Subqueries can be used as conditions, using operatorsinTo judge
		-- query'Finance Department'and'Marketing Department'All employee information
		SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department';
		SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
		-- Subquery
		SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department');

	3. The result of a subquery is multi row and multi column:
		* Subquery can participate in query as a virtual table
		-- Query employee's entry date is 2011-11-11 Future employee and department information
		-- Subquery
		SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
		WHERE t1.id = t2.dept_id;
		
		-- Common internal connection
		SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

Multi table query exercise

-- Departmental table
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- department id
  dname VARCHAR(50), -- Department name
  loc VARCHAR(50) -- Department location
);

-- Add 4 departments
INSERT INTO dept(id,dname,loc) VALUES 
(10,'Teaching and Research Department','Beijing'),
(20,'Ministry of science and Engineering','Shanghai'),
(30,'Sales Department','Guangzhou'),
(40,'Finance Department','Shenzhen');



-- Job list, job name, job description
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- Add 4 jobs
INSERT INTO job (id, jname, description) VALUES
(1, 'Chairman', 'Manage the whole company, receive orders'),
(2, 'manager', 'Management staff'),
(3, 'Salesperson', 'Selling products to customers'),
(4, 'Clerk', 'Using office software');



-- Employee list
CREATE TABLE emp (
  id INT PRIMARY KEY, -- staff id
  ename VARCHAR(50), -- Employee name
  job_id INT, -- post id
  mgr INT , -- Superior leader
  joindate DATE, -- Date of entry
  salary DECIMAL(7,2), -- wages
  bonus DECIMAL(7,2), -- bonus
  dept_id INT, -- Department No
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- Add staff
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'Sun WuKong',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'Lu Jun Yi',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'Lin Chong',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'Tang Seng',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'Li Kui',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'Song Jiang',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'Liu Bei',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'Zhu Bajie',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'Luo Guanzhong',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'Wu Yong',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'Sha Seng',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'Li Kui',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'Little white dragon',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'Guan Yu',4,1007,'2002-01-23','13000.00',NULL,10);

-- Salary scale
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- level
  losalary INT,  -- minimum wage
  hisalary INT -- Maximum wage
);

-- Add 5 salary levels
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

• demand

-- 1.Query all employee information. Query employee number, employee name, salary, job name, job description
/*
	Analysis:
		1.Employee No., employee name, salary, need to query emp table job name, job description need to query job table
		2.Query condition emp.job_id = job.id

*/
SELECT 
	t1.`id`, -- Employee number
	t1.`ename`, -- Employee name
	t1.`salary`,-- wages
	t2.`jname`, -- Job title
	t2.`description` -- Job description
FROM 
	emp t1, job t2
WHERE 
	t1.`job_id` = t2.`id`;



-- 2.Query employee No., employee name, salary, job name, job description, department name, Department location
/*
	Analysis:
		1. Employee No., employee name, salary emp job name, job description job department name, Department location dept
		2. Condition: emp.job_id = job.id and emp.dept_id = dept.id
*/

SELECT 
	t1.`id`, -- Employee number
	t1.`ename`, -- Employee name
	t1.`salary`,-- wages
	t2.`jname`, -- Job title
	t2.`description`, -- Job description
	t3.`dname`, -- Department name
	t3.`loc` -- Departmental position
FROM 
	emp t1, job t2,dept t3
WHERE 
	t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;
   
-- 3.Query employee name, salary, salary grade
/*
	Analysis:
		1.Employee name, salary emp salary grade salarygrade
		2.Condition EMP. Salary > = salarygrade. Losalary and EMP. Salary < = salarygrade. Hisalary
			emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/
SELECT 
	t1.ename ,
	t1.`salary`,
	t2.*
FROM emp t1, salarygrade t2
WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;



-- 4.Query employee name, salary, job name, job description, department name, Department location, salary grade
/*
	Analysis:
		1. Employee name, salary emp, job name, job description job department name, Department location, dept salary grade salarygrade
		2. Conditions: emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
			
*/
SELECT 
	t1.`ename`,
	t1.`salary`,
	t2.`jname`,
	t2.`description`,
	t3.`dname`,
	t3.`loc`,
	t4.`grade`
FROM 
	emp t1,job t2,dept t3,salarygrade t4
WHERE 
	t1.`job_id` = t2.`id` 
	AND t1.`dept_id` = t3.`id`
	AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;



-- 5.Find out the department number, department name, Department location and department number

/*
	Analysis:
		1.Department number, department name, Department location dept table. emp table of department number
		2.Use group queries. Complete grouping according to emp.dept.u ID and query count(id)
		3.Use subquery to associate the query result of step 2 with dept table
		
*/
SELECT 
	t1.`id`,t1.`dname`,t1.`loc` , t2.total
FROM 
	dept t1,
	(SELECT
		dept_id,COUNT(id) total
	FROM 
		emp
	GROUP BY dept_id) t2
WHERE t1.`id` = t2.dept_id;


-- 6.Query the names of all employees and their immediate superiors,Employees without leaders also need to query

/*
	Analysis:
		1.Name emp, name of immediate superior emp
			* emp The id and mgr of the table are self related
		2.Condition emp.id = emp.mgr
		3.Query all data in the left table, and intersection data
			* Use left outer connection query
	
*/
/*
select
	t1.ename,
	t1.mgr,
	t2.`id`,
	t2.ename
from emp t1, emp t2
where t1.mgr = t2.`id`;

*/

SELECT 
	t1.ename,
	t1.mgr,
	t2.`id`,
	t2.`ename`
FROM emp t1
LEFT JOIN emp t2
ON t1.`mgr` = t2.`id`;

affair

• Basic introduction to the transaction

1. Concept:    When there are multiple add, delete and change operations, we usually execute these steps in the method transaction;
	*  If a multi-step business operation is managed by a transaction, these operations either succeed at the same time or fail at the same time.
2. Operation:
	1. Open transaction: start transaction;
	2. Rollback:rollback;   Return to the data state before transaction opening;
	3. Submission:commit;     NamelysqlSuccessful implementation;
3. Example:
	CREATE TABLE account (
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(10),
		balance DOUBLE
	);
	-- Add data
	INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
	
	
	
	SELECT * FROM account;
	UPDATE account SET balance = 1000;
	-- Zhang San transfers 500 yuan to Li Si
	
	-- 0. Open transaction
	START TRANSACTION;
	-- 1. Zhang San account -500
	
	UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
	-- 2. Li Si account +500
	-- Error...
	UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
	
	-- No problem in execution found, commit transaction
	COMMIT;
	
	-- Problem found, rollback transaction
	ROLLBACK;
4. MySQL Default auto commit of transactions in database
	* There are two ways to commit a transaction:
		* Auto submit:
			* mysql It's auto submitted
			* One article DML(Additions and deletions)Statement automatically commits a transaction once.
		* Manual submission:
			* Oracle The database defaults to manually commit transactions
			* Transaction needs to be opened before committing
	* Modify the default commit method of transaction: not recommended
		* To view the default commit method for a transaction:SELECT @@autocommit; -- 1 On behalf of auto submit 0 on behalf of manual submit
		* To modify the default submission method: set @@autocommit = 0;

• Four characteristics of affairs (interview questions)

1. Atomicity: it is the smallest indivisible operation unit. It can either succeed at the same time or fail at the same time.
2. Persistence: when a transaction is committed or rolled back, the database will persist the data. Add, delete and change commit;   
3. Isolation: between multiple transactions. Independent of each other.
4. Consistency: before and after transaction operation, the total amount of data remains the same 1000/1000 commit 500/1500 rollback 1000/1000

• Isolation level of transactions (understanding)

* Concept: multiple transactions are isolated and independent from each other. However, if multiple transactions operate on the same batch of data, some problems will arise, which can be solved by setting different isolation levels.
* Problems:
	1. Dirty read: a transaction that reads uncommitted data from another transaction
	2. Non repeatable reading(Virtual reading): In the same transaction, the data read twice is different.
	3. Unreal reading: a transaction operation(DML)For all records in the data table, if another transaction adds a piece of data, the first transaction cannot query its own modification.
* Isolation level:
	1. read uncommitted: Read not submitted
		* Problems: dirty reading, non repeatable reading, unreal reading
	2. read committed: Read submitted( Oracle)
		* Problems: non repetition and unreal reading
	3. repeatable read: Repeatable reading( MySQL Default)
		* Problems: unreal reading
	4. serializable: Serialization    java Multi thread synchronization lock in; thread safety, low efficiency;
		* Can solve all problems

	* Note: the isolation level is getting higher and higher, but the efficiency is getting lower and lower
	* Database query isolation level:
		* select @@tx_isolation;
	* Database set isolation level:
		* set global transaction isolation level  Level string;

* Demonstration:
	set global transaction isolation level read uncommitted;
	start transaction;
	-- Transfer operation
	update account set balance = balance - 500 where id = 1;
	update account set balance = balance + 500 where id = 2;

DCL

• SQL classification

1. DDL: operation database and table
 2. DML: add, delete and modify data in the table
 3. DQL: query the data in the table
 4. DCL: manage users, authorize

• DBA: Database Administrator
• DCL: manage users, authorize

1. Managing users
1. Add user:
	* Syntax:CREATE USER 'User name'@'host name' IDENTIFIED BY 'Password';
2. Delete user:
	* Syntax: DROP USER 'User name'@'host name';
3. Change user password:
	UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'User name';
	UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
	
	SET PASSWORD FOR 'User name'@'host name' = PASSWORD('New password');
	SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

	* mysql I forgot. root User's password?
		1. cmd -- > net stop mysql Stop it mysql service
			* Administrator is required to run this cmd
		2. Start with no validation mysql Services: mysqld --skip-grant-tables
		3. Open up new cmd window,Direct input mysql Order, hit enter. You can log in successfully
		4. use mysql;
		5. update user set password = password('Your new password') where user = 'root';
		6. Close both windows
		7. Open Task Manager and end manually mysqld.exe Process
		8. start-up mysql service
		9. Sign in with the new password.
		//Reinstall!

4. Query user:
	-- 1. Switch to mysql data base
	USE myql;
	-- 2. query user surface
	SELECT * FROM USER;
	
	* Wildcard: % Indicates that the user can log in to the database on any host

• Privilege management

1. Query permission:
	-- Query authority
	SHOW GRANTS FOR 'User name'@'host name';
	SHOW GRANTS FOR 'lisi'@'%';
2. Grant permission:
	-- Grant authority
	grant Permission list on Database name.Table name to 'User name'@'host name';
	-- Grant all permissions to Zhang San's users on any database or table
	
	GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. Revoke authority:
	-- Revoke authority:
	revoke Permission list on Database name.Table name from 'User name'@'host name';
	REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';