Mysql Basics

Mysql Database software

1. Install

• See MySQL Basics. PDF

2. The unloading

1. Go to the mysql installation directory and find the my.ini file
   • Copy the datadir = “C: / ProgramData/MySQL/MySQL Server 5.5 / Data/”
2. Uninstall MySQL
3. Delete the MySQL folder in the C:/ProgramData directory.

Configuration of 3.

MySQL service start

1. Manually.
2. CMD –> services. MSC Opens the service window
3. Open CMD with an administrator
   • Net start mysql: starts the mysql service
   • Net stop mysql: stops the mysql service

MySQL login

1. Mysql -uroot -p Password
2. Mysql-hip-uroot -p Specifies the password of the connection target
3. Mysql –host= IP –user=root –password= connect target password

MySQL exit

1. exit
2. quit

• MySQL directory structure
  1. Basedir =”D:/develop/MySQL/”
     • Configuration file my.ini
  2. MySQL Data directory: datadir = “C: / ProgramData/MySQL/MySQL Server 5.5 / Data/”
     • Several concepts
       • Database: Folders
       • Table: file
       • Data: Data

SQL

1. What is SQL?

Structured Query Language (Structured Query Language) defines the rules that operate on all relational databases. Each database operates in a different way, called a “dialect.”

2.SQL general syntax

Basic grammar

1. SQL statements can be written on single or multiple lines, ending with a semicolon. 2. Use Spaces and indents to improve the readability of the statement. 3. The SQL statements of the MySQL database are case-insensitive. You are advised to use uppercase keywords. 4. 3 annotations
   • Single-line comments: — Comment content or # comment content (mysql specific)
   • Multi-line comments: /* Comments */

classification

1. Classification of SQL
   1. Data Definition Language (DDL) Data Definition Language

   Used to define database objects: databases, tables, columns, etc. Key words: Create, DROP,alter, etc. 2) Data Manipulation Language (DML) Is used to add, delete, or alter the Data of tables in a database. Keywords: INSERT, delete, update, etc. 3) Data Query Language (DQL) The Data Query Language is used to Query the records (Data) of tables in the database. 4) Data Control Language (DCL) is used to define access permissions and security levels of databases and create users. Key words: GRANT, REVOKE, etc

DDL: Operates databases, tables

1. The library operation

C(CREATE): creates a database

Create database: *create databaseDatabase name; * Create a database, check that it does not exist, and then create: *create database if not existsDatabase name; * Create database and specify character set *create databaseDatabase namecharacter setCharacter set name; * Exercise: create db4 database, check whether exists, and specify GBK * character setcreate database if not exists db4 character set gbk;
   		
Copy the code

R(Retrieve): Queries, database information

Query all database names: *show databases; SQL > select * from character set; SQL > select * from character setshow create databaseDatabase name;Copy the code

U(Update): changes the database character set

* Alter database character set *alter databaseDatabase namecharacter setCharacter set name;Copy the code

D(Delete): deletes a database

* Delete database *drop databaseDatabase name; * Check whether the database exists, then delete *drop database if existsDatabase name;Copy the code

Using a database

* Query the name of the database currently in use *select database(a); * Use database *useDatabase name;Copy the code

2. The operating table

C (Create) : creating a table

Grammar:

create tableTable name (column name1The data type1And the column name2The data type2. Column name n Data type n); * Database type: 1. Int: Integer type * age int, 2. Double: score double(5,2) 3. Date: date, yyyy-mm-dd 4. Timestamp: timestamp: timestamp contains yyyy-MM- DD HH: MM :ss 5. Timestamp: timestamp contains yyyy-MM- DD HH: MM :ss * If no value or null is assigned to this field, the current system time is used by default. Varchar: string * name VARCHAR (20): name contains a maximum of 20 characters * ZHANGsan contains 8 characters and 3 charactersCopy the code

example

* create a tablecreate table student(
				id int.name varchar(32),
				age int ,
				score double(4.1),
				birthday date,
				insert_time timestamp); * Copy table: *create tableThe name of the tablelikeThe name of the table being copied;Copy the code

####R(Retrieve) : Queries database tables

* Query all table names in a database *show tables; * Query table structure * desc table name;Copy the code

# # # # U (Update) : modify the table

Modify the name of the table

alter tableThe name of the tablerename toThe new name of the tableCopy the code

Modify the table character set

alter tableThe name of the tablecharacter setCharacter set name;Copy the code

##### add a column

alter tableThe name of the tableaddColumn name data type;Copy the code

##### Change the column name and type

alter tableThe name of the tablechangeColumn name new column type new data type;alter tableThe name of the tablemodifyColumn name new data type;Copy the code

# # # # # to delete columns

alter tableThe name of the tabledropThe column name.Copy the code

D (Delete) : Delete table

* drop tableThe name of the table. *drop table  if existsThe name of the table.Copy the code

DML: Add, delete, or modify data in a table

Add data:

* Syntax: *insert intoTable name (column name1And the column name2. The column n)values(value1And the value2. The value of n); * Note: 1. Column names and values must correspond one by one. 2. If no column name is defined after the table name, values are added to all columns by defaultinsert intoThe name of the tablevalues(value1And the value2. The value of n); 3. Use quotation marks (even or odd) for all types except numeric onesCopy the code

Delete data:

* Syntax: *delete fromThe table name [where* Note:1.If no condition is added, all records in the table are deleted.2.If you want to delete all records1. delete fromThe name of the table.-- Not recommended. Delete operations are performed as many times as there are records
			2. TRUNCATE TABLEThe name of the table.Drop the table and create the same table.
Copy the code

Modify data:

* Syntax: *updateThe name of the tablesetThe column name1= value1And the column name2= value2. [whereConditions]; * Note: 1. If no condition is added, all entries in the table will be modified.Copy the code

DQL: queries records in a table

grammar

* select * fromThe name of the table. 1. Grammar:selectField listfromThe table listwhereCondition listgroup byThe grouping fieldhavingConditions after groupingorder byThe sortinglimitPaging limitedCopy the code

Based on the query

##### Query multiple fields

1. Query multiple fieldsselectThe field name1The field name2..fromThe name of the table. * Note: * If all fields are queried, * can be used instead of the list of fields.Copy the code

Remove duplicate

2. distinct
Copy the code

Computed columns

3. Calculate columns * In general, you can use four operations to calculate the values of some columns. * ifNULL (expression 1, expression 2) : the operation in which null is involved and the result is null * Expression 1: Which field is required to determine whether the field is null * The replacement value if the field is null. 4. Alias: * as: as can also be omittedCopy the code

Conditions of the query

* >, <, <=, >=, =, <> * BETWEEN... AND * (set) IN * LIKE: fuzzy query * placeholder: * _ : single any character * % : multiple any character * * AND or && * or IS NULL or | | * not or!Copy the code

case

			Query age > 20

			SELECT * FROM student WHERE age > 20;
			
			SELECT * FROM student WHERE age >= 20;
			
			Query age = 20
			SELECT * FROM student WHERE age = 20;
			
			-- Query age not equal to 20
			SELECT * FROM student WHEREage ! =20;
			SELECT * FROM student WHERE age <> 20;
			
			The query age is greater than or equal to 20 and less than or equal to 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;
			
			Select * from age 22, age 18, age 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 is null
			SELECT * FROM student WHERE english = NULL; -- No. Null values cannot be used with = (! =) judgment
			
			SELECT * FROM student WHERE english IS NULL;
			
			-- Query English score not null
			SELECT * FROM student WHERE english  IS NOT NULL;
			Select * from horse; like
			SELECT * FROM student WHERE NAME LIKE 'ma %';
			Select * from person where the second word is personified
			SELECT * FROM student WHERE NAME LIKE "_ the %";
			Select * from person whose name is 3 characters
			SELECT * FROM student WHERE NAME LIKE '_';
			-- Query for people whose names contain des
			SELECT * FROM student WHERE NAME LIKE '% % DE';
Copy the code

Supplementary DQL

1. Sort the query

Order by (1) order by (2) order by (1) order by (2) * Order: * ASC: ascending, default. * DESC: descending order. * Note: * If there are more than one sort condition, the second condition will only be judged if the condition value of the current edge is the same.Copy the code

2. Aggregate function

2. Aggregate function: vertical calculation of a column of data as a whole. Count (*) 2. Max: calculates the maximum value 3. Min: calculates the minimum value 4. Sum: calculates and 5. Solution: 1. Select columns that do not contain non-empty columns for calculation. 2Copy the code

3. Group query

1. Syntax: group by group; SQL > select * from 'where' and 'having'; 1. Where is qualified before grouping. If the conditions are not met, the group is not included. Having will not be queried if the result is not satisfied. 2. Where can not be followed by aggregating functions, having can judge aggregating functions.-- By gender. Query the average scores of male and female students respectively

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

      -- By gender. Query the average score and number of male and female students respectively

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

      -- By gender. Query the average score of male and female students respectively, the number of requirements: the score is less than 70, not to participate in the group
      SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

      -- By gender. Query the average score of male and female students respectively, the number of requirements: scores below 70, not to participate in the group, after the group. There are more than two people
      SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

      SELECT sex , AVG(math),COUNT(idThe number of)FROM student WHERE math > 70 GROUP BY sex HAVINGThe number >2;
Copy the code

4. Paging query

1. Syntax: start index, number of queries per page; 2. Formula: Start index = (current page number -1) * number of items displayed per page-- Displays 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 3Limit is a MySQL" dialect"Copy the code

The constraint

* Concept: The data in the table is qualified to ensure the correctness, validity and completeness of the data. * category: 1. Primary key constraint: primary key; 2. Foreign key constraint: foreign key * Non-null constraint: not null. The value cannot be null. CREATE TABLE stu(id INT, NAME VARCHAR(20) NOT NULL -- NAME is NOT NULL); 2. After the TABLE is created, add a non-null constraint ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL. ALTER TABLE stu MODIFY name VARCHAR(20);Copy the code

1. Add a unique constraint when creating a tableCREATE TABLE stu(
			id INT,
			phone_number VARCHAR(20) UNIQUE -- Added unique constraints); * Note that in mysql, columns with unique constraints can have multiple NULL values 2. Delete unique constraintsALTER TABLE stu DROP INDEXphone_number; 3. After the table is created, add unique constraintsALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; * Primary key constraint: primary key. Note: 1. Meaning: Non-empty and unique 2. A table can have only one primary key. A primary key uniquely identifies a record in a table. 2. Add a primary key constraint when creating a tablecreate table stu(
			id int primary key.Add primary key constraint to id
			name varchar(20)); 3. Delete the primary keyAlter table stu modify id int;
		ALTER TABLE stu DROP PRIMARY KEY; 4. After the table is created, add primary keysALTER TABLE stu MODIFY id INT PRIMARY KEY; Concept: If a column is of numeric type, use auto_increment to increment a column. When the table is created, the primary key constraint is added and primary key auto-growth is completedcreate table stu(
			id int primary key auto_increment,Add primary key constraint to id
			name varchar(20)); 3. Delete automatic growthALTER TABLE stu MODIFY id INT; 4. Add automatic growthALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
Copy the code

Foreign key constraints

* Foreign key constraint: foreign key, let table to table relationship, so as to ensure the correctness of data. 1. When creating a table, add a foreign key * syntax: create table name (....) Constraint foreign key name Foreign key name (references); ALTER TABLE TABLE name DROP FOREIGN KEY name. ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY name REFERENCES primary TABLE name ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name 1. Add the syntax of the cascading operation: ALTER TABLE name ADD CONSTRAINT FOREIGN KEY REFERENCES ON UPDATE CASCADE ON DELETE CASCADE; 1. CASCADE UPDATE: ON UPDATE CASCADE 2. CASCADE deletion: ON DELETE CASCADECopy the code

Database design

Analysis of the

One person has only one ID card, and one ID card can only correspond to one person. One-to-many (many-to-one) : * For example, departments and employees * Analysis: There are multiple employees in a department. One employee corresponds to only one department. 3. One student can choose many courses, and one course can be chosen by many students. 1. One-to-many (many-to-one) : * For example, department and employee * Implementation: Many-to-many relationships are implemented with the help of a third intermediate table. The intermediate table contains at least two fields that are the foreign keys of the third table and point to the primary keys of the two tables. 3. One-to-one (understand) : * such as: and ID card * implementation: one-to-one relationship implementation, you can add a unique foreign key in either party to point to the other party's primary key. 3. Example -- create a tab_category table tab_category -- CID Primary key of the travel route category, automatically grow -- cname Name of the travel route category is a non-empty, unique character string of 100Copy the code

		CREATE TABLE tab_category (
			cid INT PRIMARY KEY AUTO_INCREMENT,
			cname VARCHAR(100) NOT NULL UNIQUE
		);
		Create table tab_route
		100 price price rdate date date cid foreign key, category */
		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 a user table tab_user uid primary key (uid) username length 100 (unique) password length 30 (non-empty) name Name length 100 birthday birthday sex Sex The value is a fixed-length character string. 1 telephone Mobile phone number the value is a string of 11 Email email address. The value is a string of 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 favorite table tab_favorite RID travel route ID, foreign key date Favorite time uid User ID, foreign key RID and uid cannot be the same, set compound primary key, same user cannot favorites the same route two times */
		CREATE TABLE tab_favorite (
			rid INT.- the line id
			DATE DATETIME,
			uid INT.- user id
			Create a compound primary key
			PRIMARY KEY(rid,uid), -- Federated primary key
			FOREIGN KEY (rid) REFERENCES tab_route(rid),
			FOREIGN KEY(uid) REFERENCES tab_user(uid)
		);
Copy the code

The paradigm of database design

* Concepts: Some specifications to follow when designing a database. To follow the following paradigm requirements, must first follow all the previous paradigm requirements when designing a relational database, comply with different specification requirements, design a reasonable relational database, these different specification requirements are called different paradigms, various paradigms present sub-specifications, the higher the database redundancy is smaller. At present, there are six paradigms for relational databases: first paradigms (1NF), second paradigms (2NF), third paradigms (3NF), Bas-Coad paradigms (BCNF), fourth paradigms (4NF) and fifth paradigms (5NF, also known as perfect paradigms). * Classification: 1. First Normal Form (1NF) : Each column is an indivisible atomic data item 2. Second normal Form (2NF) : On the basis of 1NF, non-code attributes must be completely dependent on the code (on the basis of 1NF, the partial function dependence of the main attribute on the main code is eliminated) * Several concepts: 1. Function dependence: A-->B, if the value of the A attribute (attribute group) can determine the value of the unique B attribute. B depends on A for example: student id --> name. 2. Complete function dependence: A-->B, if A is an attribute group, then B attribute worth determining needs to depend on all attribute values in A attribute group. For example: (student number, course name) --> score 3. Partial function dependence: A-->B, if A is an attribute group, then B attribute worth determining only needs to depend on some values in A attribute group. For example: (student id, course name) --> name 4. Transfer function dependencies: A-->B, B --> C. If the value of A attribute (attribute group) can determine the value of unique B attribute, and the value of C attribute (attribute group) can determine the value of unique C attribute, then the C transfer function is said to depend on A for example: Student id --> department name, department name --> Dean 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 codes in the table are: (Student number, course name) * Primary attribute: All attributes in the attribute group * Non-primary attribute: except the attributes in the attribute group 3. Third normal Form (3NF) : On 2NF basis, any non-primary attribute is not dependent on other non-primary attributes (transitive dependency is eliminated on 2NF basis)Copy the code

Database backup and restoration

-u username -p password database name > Save path * Restore: 1. Log in to the database 2. Create the database 3. Use the database 4. 2. Graphic tools:Copy the code

Multi-table query

Prepare the SQL

Create a 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, associated 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('Pig Eight Quit'.'male'.3600.'2010-12-02'.2);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("Tang's monk.'male'.9000.'2008-08-08'.2);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Ghost of Bones'.'woman'.5000.'2015-10-07'.3);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(Spider spirit.'woman'.4500.'2011-03-14'.1);
Copy the code

* Cartesian product: * There are two sets A, B. Take all the constituent cases of the two sets. SQL > select * from table where SQL > select * from table where SQL > select * from table where SQL > delete 1. Implicit inline join: use where condition to eliminate useless data * example:Copy the code

- Query information about all employees and corresponding departments

			SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

			Select * from employee table where name, gender The name of the department table
			SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

			SELECT 
				t1.name, -- The name of the employee form
				t1.gender,-- Gender of employee form
				t2.name -- Name of the department table
			FROM
				emp t1,
				dept t2
			WHERE 
				t1.`dept_id` = t2.`id`;
			#2. Explicit inner join:* grammar:selectField listfromThe name of the table1 [inner] joinThe name of the table2 onConditions * 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. Select * from * where *;
		# 1. Which tables to query data from
		# 2. What are the conditions
		# 3. Query which fields
Copy the code

1. Left outer link: * syntax:selectField listfrom 表1 left [outer] join 表2 onConditions; * Query all data in the left table and its intersection. * example:If the employee has a department, the department name is displayed. If the employee has no department, the department name is not displayed
				SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`; 2. Right outer join: * Syntax:selectField listfrom 表1 right [outer] join 表2 onConditions; * Query all data in the right table and its intersection. * example:SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
Copy the code

3. Sub-query: * Concept: nested query in a query, called a nested query sub-query.-- Query information about the highest paid employee
			Select * from salary where salary = 9000
			SELECT MAX(salary) FROM emp;
			
			-- 2 Query the employee information and the salary is 9000
			SELECT * FROM emp WHERE emp.`salary` = 9000;
			
			A single SQL statement does this. The subquery
			SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROMemp); 1. The result of the subquery is single row, single column: * The subquery can be used as a condition, using the operator to determine. Operators: > >= < <= = *Select * from employees whose salary is less than the average salary
				SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROMemp); 2. The result of the subquery is multi-row, single-column: * The subquery can be used as a condition, using the operator in to determineSelect 'finance' and 'marketing' from all employees
				SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department';
				SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
				- the 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, multi-column: * A subquery can participate as a virtual tableQuery employee information and department information after 2011-11-11
				- the 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'* Multiple table query exercises- department of table
			CREATE TABLE dept (
			  id INT PRIMARY KEY PRIMARY KEY.- department id
			  dname VARCHAR(50), -- Department Name
			  loc VARCHAR(50) -- Department Location
			);
			
			-- Add four departments
			INSERT INTO dept(id,dname,loc) VALUES 
			(10.'Teaching and Research Department'.'Beijing'),
			(20.'Student Department'.'Shanghai'),
			(30.'Sales Department'.'guangzhou'),
			(40.'Finance Department'.'shenzhen');
Copy the code

	-- Job list, job title, job description
			CREATE TABLE job (
			  id INT PRIMARY KEY,
			  jname VARCHAR(20),
			  description VARCHAR(50));-- Added 4 positions
			INSERT INTO job (id, jname, description) VALUES
			(1.'Chairman'.'Manage the whole company, take orders'),
			(2.'managers'.'Managing Department Staff'),
			(3.'Salesman'.'Selling products to guests'),
			(4.'clerk'.'Use Office Software');
				- the employee table
			CREATE TABLE emp (
			  id INT PRIMARY KEY.- employee 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 id
			  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 employee
			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.'Jun Yi Lu'.3.1006.'2001-02-20'.'16000.00'.'3000.00'.30),
			(1003.'Lin'.3.1006.'2001-02-22'.'12500.00'.'5000.00'.30),
			(1004."Tang's monk.2.1009.'2001-04-02'.'29750.00'.NULL.20),
			(1005.'李逵'.4.1006.'2001-09-28'.'12500.00'.'14000.00'.30),
			(1006.'sung river'.2.1009.'2001-05-01'.'28500.00'.NULL.30),
			(1007.'liu bei'.2.1009.'2001-09-01'.'24500.00'.NULL.10),
			(1008.'Pig Eight Quit'.4.1004.'2007-04-19'.'30000.00'.NULL.20),
			(1009.'Luo Guanzhong'.1.NULL.'2001-11-17'.'50000.00'.NULL.10),
			(1010.'with wu'.3.1006.'2001-09-08'.'15000.00'.'0.00'.30),
			(1011.'沙僧'.4.1004.'2007-05-23'.'11000.00'.NULL.20),
			(1012.'李逵'.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 salary
			);
			
			-- Added 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);
			
			- requirements:
			
			-- 1. Query the information of all employees. Query employee number, employee name, salary, job title, job description
			/* Analysis: 1. Employee id, employee name, salary, emP table job name, job description, job table 2 Job_id = job.id */
			SELECT 
				t1.`id`.-- Employee No.
				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 id, employee name, salary, job title, job description, department name, department location
			Emp, job dept, emP, emP, emP, emP, emP, emP, EMP, EMP Id and dept_id = dept.id */
			
			SELECT 
				t1.`id`.-- Employee No.
				t1.`ename`.-- Employee name
				t1.`salary`.Wages,
				t2.`jname`.-- Job title
				t2.`description`.-- Job Description
				t3.`dname`.-- Department Name
				t3.`loc` -- Department 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 salarygrade salarygrade 2 Salary <= salarygrade. Hisalary emp. Salary BETWEEN salarygrade  salarygrade.hisalary */
			SELECT 
				t1.ename ,
				t1.`salary`,
				t2.*
			FROM emp t1, salarygrade t2
			WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
			
			-- query employee name, salary, job name, job description, department name, department location, salary grade
			/* 解 释 : 1. Name, emP, job, dept salarygrade 2. Condition:  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. Query the department id, department name, department location, department number
			
			/* select * from dept dept; Department Size EMP Table 2. Query by group. Dept_id and query count(ID) 3. Associate the result of step 2 with the DEPT table */ using the subquery
			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 supervisors, even employees without supervisors need to query
			
			Emp * ID of the EMP table is self-associated with MGR 2. Id = emp.mgr 3. Query all data in the left table, and intersection data * use left outer join */
			/* 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`;
Copy the code

The transaction

1. Concept: * If a business operation consisting of multiple steps is managed by a transaction, these operations will either succeed or fail at the same time. 1. Start transaction:start transaction; 2. The rollback:rollback; 3. Submit:commit; 3. Examples: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. Start a transaction
		START TRANSACTION;
		-- 1. Zhang SAN Account -- 500
		
		UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
		-- 2. Li Si account +500
		-- Wrong...
		UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
		
		-- Commits the transaction after finding no problems with execution
		COMMIT;
		
		If a problem is found, roll back the transaction
		ROLLBACK; * Automatic commit: * A DML statement automatically commits a transaction. * Manual commit: * Oracle database default is manual commit transaction * need to start the transaction before committing * Change the default commit mode of transaction:SELECT @@autocommit; -- 1 indicates automatic submission. 0 indicates manual submission* Change the default submission mode:set @@autocommit = 0;

Copy the code

2. Four characteristics of transactions: 1. Atomicity: it is the smallest unit of operation that cannot be separated, and either succeeds or fails simultaneously. 2. Persistence: When a transaction is committed or rolled back, the database will persist the data. 3. Isolation: Between transactions. Independent of each other. 3. Transaction isolation level (understand) * Concept: Multiple transactions are isolated and independent from each other. However, if multiple transactions operate on the same batch of data, problems can arise that can be addressed by setting different isolation levels. * There are problems: 1. Dirty read: one transaction reads data that is not committed in another transaction 2. Non-repeatable read (virtual read) : Different data is read twice in the same transaction. 3. Phantom read: one transaction operation (DML) data table all records, another transaction added a data, the first transaction can not query its own changes. * Isolation level: 1. Read uncommitted: Read uncommitted: Dirty reads, unrepeatable reads, and magic reads. 2. Repeatable read (MySQL default) * Error: repeatable read (MySQL default) * Error: repeatable read (MySQL default) * Error: repeatable read (MySQL default) * Error: repeatable read (MySQL default) Isolation levels from small to large become more secure, but less efficient * Database query isolation level: *select@@tx_isolation; Database set isolation level: *set global transaction isolation levelLevel string; * the demo: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;
Copy the code

DCL

DML: add, delete, alter table data 3. DQL: query table data 4. DCL: manage user, authorize * DBA: manage user, authorize 1. 1. Add a user:CREATE USER 'Username'@'Host name' IDENTIFIED BY 'password'; 2. Delete user:DROP USER 'Username'@'Host name'; 3. Change the user password:UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'Username';
			UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
			
			SET PASSWORD FOR 'Username'@'Host name' = PASSWORD('New password');
			SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); Mysql > select root from user root; 1. cmd-- > net stop mysql Stop mysql service2. Start the mysql service mysqld without authentication--skip-grant-tables3. Open a CMD window, enter the mysql command, and press Enter. The login succeeds. 4.use mysql;
				5. update user set password = password('Your new password') where user = 'root'; 6. Close both Windows 7. Open task Manager and manually end mysqlD. exe process 8. Start the mysql service. 9. Use the new password to log in. 4. Query users:-- 1. Switch to mysql database
			USE myql;
			-- 2. Query the user table
			SELECT * FROM USER; * Wildcard: % indicates that you can log in to the database as a user on any host. 2. Permission management: 1. Query permission:-- Query permission
			SHOW GRANTS FOR 'Username'@'Host name';
			SHOW GRANTS FOR 'lisi'@The '%'; 2. Grant permissions:Grant permission
			grantPermissions listonDatabase name. Table nameto 'Username'@'Host name';
			Grant all privileges to a user on any table in any database
			
			GRANT ALL ON*. *TO 'zhangsan'@'localhost'; 3. Revoke permission:Revoke permission:
			revokePermissions listonDatabase name. Table namefrom 'Username'@'Host name';
			REVOKE UPDATE ON db3.`account` FROM 'lisi'@The '%';
Copy the code

oracle

SQL optimization