mysql basic practice

  • Mysql
    1. Configuration
    *Mysql service startup
    1. Manual
    2.cmd – > services.msc open the service window
    3. Open cmd with administrator

    • net start mysql: start the mysql service

      • net stop mysql: close the mysql service
    • Mysql login

      1. mysql -u root -p 12456
        2.mysql -u root -p
        3.mysql -hip -u root -p password of connection target
        4.mysql --host=ip --user=root --password = password of leveling target
    • Mysql exit
      1.exit
      2.quit

    • Mysql directory structure
      1.Mysql installation directory
      *Configuration file my.ini
      2.Mysql data directory
      *Several concepts
      *Databases: folders
      *Tables: files
      *Data

SQL

 1.What is? SQL?
	Structured Query Language: Structured query language
	In fact, it defines the principles for operating all relational databases. Each database operation mode has different places, which is called "dialect"
	
2.SQL general syntax
	1.SQL Statements can be written on one or more lines, ending with a semicolon.
	2.You can use spaces and indents to improve the readability of statements
	3.Mysql Database SQL Statements are not case sensitive, and uppercase is recommended for keywords
	4.3 Kinds of notes
		* Single-Line Comments : -- Note content or # Note Content 
		* Multiline comment: /* notes */
		
3.SQL Classification of
	1.DDL(Operation database and table)
	2.DML(Data in query table)
	3.DQL(Data in addition, deletion and modification table)
	4.DCL(to grant authorization)

DDL: operation database and table

1.Operation database: CRUD
	1.C(Create): establish
		* Create database
			* create database Database name;
		* Create a database, judge whether it exists, and then create it
			* create database if not exists Database name;
		* establish db1,Determine whether it exists and specify the end of the character set gbk
			* create database if not exists db1 character set gbk;
			
	2.R(Retrieve): query
		* Query the names of all databases
			* show databases;
		* View the character set of a database
			* show create databases Database name;
			
	3.U(Updata): 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
			* drop database if exists Database name;
	5.Use database
		* Query the database currently in use
			* select database();
		* Use database
			* use Database name;
		
2. Operation table
	1.C(Create): establish
		1.grammar
			create table Table name(
				Column name 1, data type 1,
				Column name 2, data type 2,
				...
				Listing n data type n
			);
			* Note: comma is not required for the last column(,)
			* data type
				1.int: Integer type
					* age int,
				2.double: Decimal type
					* score double(5,2)
				3.data:Date, only including mm / DD / yyyy, yyyy-MM-dd
				4.datatime:Date, including month, day, hour, minute and second, yyyy-MM-dd HH:mm:ss
				5.timestamp:Timestamp type, including month, day, hour, minute and second, yyyy-MM-dd HH:mm:ss
					* If you do not assign a value to this field in the future, or assign a value of null,By default, the current system time is used for automatic assignment
				6.varchar: character string
					* name varchar(20):Name up to 20 characters
		* 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 Table name like Replicated table name;
	2.R(Retrieve): query
		* Query all table names in a database
			* show tables;
		* Query table structure
			* desc Table name;
	3.U(Updata): modify
		1.Modify table name
			* alter table Table name rename to New table name;
		2.Modify the character set of the table
			* alert table  Table name character set Character set name;
		3.Add a column
			* alter table Table name add Column name data type;
		4.Modify column name type
			* alter table Table name change New class name after column name modification new data type after modification;
			* alter table Table name modify Column name new data type;
		5.Delete column
			* alter table Table name drop Listing;
	
	4.D(Delete): delete
		* Delete table
			* drop table Table name;
			* drop table if exists Table name;

DML: adding, deleting and modifying data in the table

1. Add data:

  • Syntax:

    • insert into table name (column name 1, column name 2,...) values (data type 1, data type 2,...);

      • 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 values will be added by default.

        insert into table name values (value 1, value 2, value 3,...)

      • 3. In addition to the number type, other types need to be enclosed in quotation marks (either single or double)
        2. Delete data

            * Syntax:
        
        • delete from table name [where condition]
        • be careful:
          1. If no conditions are 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
          2.truncate table name-- Recommended, more efficient. Delete the table first, and then create an empty table exactly the same

3. Modify data
* syntax:
* update table name set column name 1 = value 1, column name 2 = value 2,... [where condition]
* Note:
1. If no conditions are added, all records in the table will be modified

DQL: querying records in a table

* select * from Table name;

DQL: query statement

create table student2(
id int,
name varchar(32),
age int,
sex char,
address varchar(32),
math double,
english double
);

insert into student2 values (1, "Ma Yun", 55, "male", "Hangzhou", 66,78);

select * from student2;

insert into student2 values (2, "Ma Huateng", 45, "male", "Shenzhen", 98,87);

insert into student2 values (3, "Ma Jingtao", 55, "male", "Hong Kong", 56,77);

insert into student2 values (4, "Liu Yan", 20, "female", "Hunan", 76,65);

insert into student2 values (5, "Liu Qing", 55, "male", "Hunan", 86,null);

insert into student2 values (6, "Andy Lau", 57, "male", "Hong Kong", 99,99);

insert into student2 values (7, "Ma De", 22, "female", "Hong Kong", 99,99);

insert into student2 values (8, "demacia", 18, "male", "Nanjing", 56,65);

select * from student2 order by math – sort method;

select * from student2 order by math Desc;

– rank according to math scores or English scores if math scores are the same
select * from student2 ORDER BY math ASC,english DESC;
1. Sort query
*Syntax: order by clause
*order by sort field 1, sort by 1, sort field 2, sort by 2
*Sort by:
*ASC: ascending, default
*DESC: descending order
*Note:
*If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same.

2.Aggregate function: perform vertical calculation by taking a column of data as a whole
	1.count: Number of calculations
		1.Generally, non empty columns are selected: primary key
		2.count(*)
	2.max:Calculate maximum	
	3.min:Calculate minimum
	4.sum:Calculation and
	5.avg: Calculate average

	Note: Calculation of aggregate function, excluding null Value.
		Solution:
			1.Select columns that do not contain non empty columns for calculation
			2.IFNULL function

select count(name) from student2;

select count(ifnull(english,0)) from student2;

select max(math) from student2;

select min(math) from student2;

select sum(math) from student2;

select sum(english) from student2;

select avg(math) from student2;

3.Group query:
	1.Syntax: group by Grouping field;
	2.be careful:
		1.The fields queried after grouping can only be grouped fields and aggregate functions**
		2.where and having What's the difference?
			1.where Limit before grouping. If the conditions are not met, you will not participate in grouping. having It is qualified after grouping. If the conditions are not met, it will not be queried
			2.where Can not be followed by aggregate function, having Then you can judge the aggregation function.

-- Grouped by gender. Group query the average scores of male and female students
select sex,avg(math) from student2 group by sex;
 
-- Grouped by gender. Query the average score and number of male and female students in groups 
select sex,avg(math),count(id) from student2 group by sex;

-- Grouped by gender. Group query the average score and number of male and female students: those with a score less than 70 will not participate in the group
select sex,avg(math),count(id) from student2 where math>70 group by sex;

-- Grouped by gender. Group query the average score and number of male and female students: those with a score less than 70 will not participate in the group.More than 2 persons
select sex,avg(math),count(id)  from student2 where math>70 group by sex having count(id)>2;
select sex,avg(math),count(id) as Number of people   from student2 where math>70 group by sex having Number of people>2;


4. Paging query
1. Syntax: the index starting from limit and the number of queries per page;
2. Formula: starting index = (current page number - 1) * number of queries per page
– 3 pieces of data per page
​ select * from student2 limit 0,3; – first page

	select * from student2 limit 3,3;	-- Page 2

	select * from student2 limit 6,3;	-- Page 3

	3.limit It's a Mysql "Dialect" of

DQL: querying records in a table

1.Syntax:
	select
		Field list
	from
		Table name list
	where
		Condition list
	group by
		Grouping field
	having 
		Conditional qualification after grouping
	order by
		sort
	limit 
		paging

2.Basic query
	1.Query of multiple fields
		select Field 1,Field 2... from Table name;
		*be careful:
			* If you query all fields, you can use*To replace the field list
	2.Remove duplication
		* distinct
	3.Calculation column
		* Generally, four operations can be used to calculate the values of some columns. (generally, only numerical calculation will be performed)
		* IFNULL(Expression 1,Expression 2): null Participate in the calculation, and the calculation results are null
			* Expression 1: which field needs to be judged as null
			* If the field is null Replacement value after
	4.Alias
		* as: as It can also be omitted

create table student3(
				id int,
				name varchar(20),
				age int,
				sex varchar(5),
				address varchar(100),
				math int,
				english int
);

insert into student3(id,name,age,sex,address,math,english) values(1,"Jack Ma",55,"male","Hangzhou",66,78),(2,"pony ",45,"female","Shenzhen",98,87),(3,"Ma Jingtao",55,"male","Hong Kong",56,77),(4,"Liuyan",20,"female","Hunan",76,65),(5,"Liu Qing",20,"male","Hunan",86,null),(6,"Lau Andy",57,"male","Hong Kong",99,99),(7,"Madder",22,"female","Hong Kong",99,99),(8,"Demacia",18,"male","Nanjing",56,65);

select * from student3;

-- Query name and age
select name,age from student3;

select address from student3;

-- Remove duplicate result sets
select distinct address from student3;
select name,address from student3;

-- calculation math and english Sum of scores
select name,math,english,math+english from student3;
-- If so null Participate in the calculation, and the calculation results are null
select name,math,english,math+ifnull(english,0) from student3;

-- Alias
select name,math,english,math+IFNULL(english,0) as grade from student3;

3. Condition query
	1.where Sentence followed by condition
	2.operator
		* >,<,<=,>=,=,<>
		* BETWEEN ... AND
		* IN(aggregate)
		* LIKE: Fuzzy query
			* Placeholder:
				* _:Single arbitrary character
				* %:Multiple arbitrary characters (can be 0 or arbitrary)
		* IS NULL
		* and or  &&
		* or  or  ||
		* not  Or!
 -- Query who are older than or equal to 20
select * from student3 where age>=20;
 
 -- Query people 20 or older
 select * from student3 where age = 20;
 
 -- Query people whose age is not equal to 20
 
select * from student3 where age!=20;
select * from student3 where age<>20;

-- Query people aged 20 or older and 30 or younger
select * from student3 where age>=20 && age<=30;
select * from student3 where age>=20 and age<=30;  -- Recommended use
select * from student3 where age between 20 and 30;

-- Query the information of age 22, 19 and 25
select * from student3 where age=22 or age = 19 or age = 25;-- Recommended use
select * from student3 where age=22 || age=19 || age=25;
select * from student3 where age in (22,19,25);

-- Query for students without English scores
select * from student3 where english = null;  -- The statement is wrong. null out of commission=(!=)judge
select * from student3 where english is null;

-- Query English score is not null
select * from student3 where english is not null;

-- What are the students surnamed MA in the class?
select * from student3 where name like "horse%";
-- The second word in the query name is the person
select * from student3 where name like "_turn%";

-- A person whose name is three words
select * from student3 where name like "___";

-- Query the person whose name contains De
select * from student3 where name like "%virtue%";

constraint

* Concept: define the data in the table. Ensure the correctness, effectiveness and integrity of data.
* Classification:
	1.Primary key constraint: primary key
	2.Non empty constraint: not null
	3.Unique constraint: unique
	4.Foreign key constraints: foreign key

* Non empty constraint: not null
	1. Add constraints when creating tables
		create table student04(
		id int,
		name varchar(20) not null -- name Is not empty
	);
	2. After creating the table, add a non empty constraint
	alter table student04 modify name varchar(20) not null;
	3. delete name Non NULL constraint
	alter table student04 modify name varchar(20);

* Unique constraint: unique,The value cannot be repeated
-- Add unique constraints when creating tables
	create table student04(
	id int,
	phone_number varchar(20) unique -- Added unique constraint
);

select * from student04;

insert into student04(id,phone_number) values(1,'1111');
insert into student04(id,phone_number) values(2,'1112');

* 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. A primary key is the unique identifier of a record in a table	
	2. When creating a table, add a primary key constraint
		create table student05(
			id int primary key,  -- Add a primary key constraint to a primary key
			name varchar(20) not null  
		);

	3.  Delete primary key
	alter table student05 drop primary key;

	insert into student05(id,name) values(1,'Ice Black Tea');
	insert into student05(id,name) values(2,'Green Tea');

	4. After creating the table, add the primary key
	alter table student05 modify id int primary key;

	5. Automatic growth
		1. Concept: if a column is numeric, use auto_increment Can be used to complete the automatic growth of value
		2. When creating a table, add a primary key constraint and complete the primary key growth
	create table student06(
		id int primary key auto_increment, -- to id Add primary key constraint
		name varchar(20) not null
		);

	-- Delete auto growth
	alter table student06 modify id int;

	-- Add auto growth
	alter table student06 modify id int auto_increment;

	select * from student06;

	insert into student06 values (null,'aaa');

* Foreign key constraints: foreign key
	1. When creating a table, you can add foreign keys to make the relationship between the table and the table, so as to ensure the correctness of the data
		* 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 employee drop foreign key  The name of the foreign key;
	3. Add foreign keys after creating tables
		alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id);

create table emp( 
	id int primary key auto_increment,  
	name varchar(20) not null,
	age int,
	dep_name varchar(30),	-- Department name
	dep_location varchar(30)	-- Department address
);

select * from emp;

-- Add data
insert into emp(name,age,dep_name,dep_location) values('Zhang San',20,'R & D department','Guangzhou'),
('Li Si',21,'R & D department','Guangzhou'),('Wang Wu',20,'R & D department','Guangzhou'),('Lao Wang',20,'Sales Department','Shenzhen')
,('king',22,'Sales Department','Shenzhen'),('Xiao Wang',20,'Sales Department','Shenzhen');

select * from emp;
-- Data redundancy

-- Solution: split into 2 tables
-- Create department table(id,dep_name,dep_location)

--One party, main table
create table department(
	id int primary key auto_increment,
	dep_name varchar(20),
	dep_location varchar(20)
);

-- Create employee table(id,name,age,dep_id)
-- Multi party, from table

4. Cascade operation
	1. Add cascaded actions:
		Syntax: alter table Table name  add constraint Foreign key name  foreign  key (Foreign key field name)  references  Main table name(Main table column name)  on updata cascade  on delete  cascade;
	
	2. Classification:
		1. Cascaded updates: ON UPDATE   CASCADE
		2. Cascade deletion:  ON DELETE CASCADE

Database design

1. Relationship between multiple tables
	1. Classification:
		1. One to one (understanding):
			* Such as: person and ID card
			* Analysis: a person has only one ID card, and one ID card can only correspond to one person
		2. One to many (many to one):	
			* E.g. departments and employees
			* Analysis: a department has multiple employees, and an employee can only correspond to one department
		3. Many to many: 
			* E.g. students and courses
			* Analysis: a student can choose multiple courses, and a course can also be selected by many students

	2. Implementation relationship:
		1.  One to many (many to one):
			* E.g. departments and employees
			* Implementation method: establish the primary key of the party whose foreign key points to one in more than one party
		2. Many to many:
			* E.g. students and courses
			* Implementation method: the implementation of many to many relationship requires the help of the intermediate table in Chapter 3. The intermediate table contains at least two fields, which are used as the foreign keys of the third sheet and point to the primary keys of the two tables respectively.

		3. One to one (understanding):
			* Such as: person and ID card
			* Implementation method: one-to-one relationship is implemented. You can add a unique foreign key on either side to point to the key on the other side
2. Paradigm of database design
	* Concept: some specifications to be followed when designing and database. To follow the latter paradigm requirements, you must first follow all the previous paradigm requirements
	
	When designing a relational database, we should comply with different specification requirements and design a reasonable relational database. These different specification requirements are called different paradigms. Various paradigms present sub specifications, and the higher the paradigm data
	The smaller the library redundancy.
	At present, there are six paradigms of relational database: the first paradigm(1NF),Second paradigm(2NF),Third paradigm(3NF),Bath-Codd paradigm(BCNF),The fourth paradigm(4NF)And the fifth paradigm(5NF,Perfect paradigm)

	* Classification:
		1.First paradigm(1NF): Each column is an indivisible atomic data item
		2.Second paradigm(2NF): At 1 NF On the basis of, non code attributes must be completely dependent on code(At 1 NF On this basis, the partial functional dependence of non main attributes on main codes is eliminated)
			* Several concepts:
				1. Functional dependencies: A---->B,If passed A The value of the attribute (attribute group) to determine the unique B Property, it is called B Depend on A
					E.g. student number--->full name.		(Student number, course name)-->fraction
				2.Full functional dependency: A---->B,If A Is an attribute group, then B The determination of attribute value depends on A All attribute values in the attribute
					For example: (student number, course name)---->fraction
				3.Partial functional dependencies: A---->B,If A Is an attribute group, then B Attribute value determination only depends on A Some values in the attribute group are sufficient
					For example: (student number)---->Course name)------>full name
				4.Transfer function dependency: A----->B,   B----->C.	If passed A The value of the attribute (attribute group) determines the unique value B Property value, and then through B The value of the attribute (attribute group) determines the unique value C
				Property, it is called C The transfer function depends on A
					E.g. student number ---->Department name----->Dean of Department
				5.Code: if an attribute or attribute group is completely dependent on all other attributes in a table, the attribute (attribute group) is called the code (candidate 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: the attribute of the division code attribute group
		3.Third paradigm(3NF): In 2 NF On this basis, any non primary attribute does not depend on other non primary attributes (in 2 NF (eliminate delivery dependency)

Backup and restore of database

1.  Command line:
	* Backup: mysqldump -u user name  -p Password database name> Saved path
	* Restore:
		1. Login database
		2. Create database
		3. Use database
		4. Execute the file. source File path
2. Graphical tools:

Multi table query:

* Query syntax:
	select
		Column name list
	from
		Table name list
	where...
get ready sql

– 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),
	salary DOUBLE,
	join_date date,
	dept_id int,
	FOREIGN key (dept_id)	REFERENCES dept(id)
 );
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-04',2);
insert into emp(name,gender,salary,join_date,dept_id) values('Tang Monk','male','9000','2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('Baigujing','female','5000','2015-10-17',3); 
insert into emp(name,gender,salary,join_date,dept_id) values('spider goblin','female','4500','2011-05-20',1);

* Cartesian product:
	* There are two sets A,B,Go to all the composition of the two sets
	* To complete multi table query, you need to eliminate useless data
* Classification of multi table query:
	1.Internal connection query:
		1. Implicit joins: Using where Conditions to eliminate 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`,
					t1.gender,
					t2.`name`
				FROM 
					emp t1,
					dept t2
				WHERE 
					t1.dept_id=t2.id;
		2. Show internal connections:
			* Syntax: select Field list  from Table name 1 [inner] join Table name 2  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. Query data from those tables
			2. What are the conditions
			3. Query those fields	 
	2.External connection query:
		1. Left outer connection:
			* Syntax: select * from Table name 1  left [outer] join  Table name 2  on  Conditions;
			* All data in the left table and its intersection are queried.
		2. Right outer connection:
			* Syntax: select * from Table name 1 right [outer] join Table name 2 on  Conditions;
			* All the data in the right table and its intersection are queried.
	3.Subquery:
		* Concept: nested queries in queries are called subqueries
			 -- Query the highest employee information
				 SELECT *,MAX(salary) salary FROM emp; 
 
			 -- 1 What is the highest salary? nine thousand
				 SELECT MAX(salary) FROM emp;
 
			-- 2 Query employee information, and the salary is equal to 9000
				SELECT * FROM emp WHERE salary = 9000;

			-- One sql That's it

				SELECT * FROM emp WHERE salary  =(SELECT MAX(salary) FROM emp);
		* Different situations of sub query
			1. The result of subquery is single row and single column:
				* Subqueries can be used as conditions and operators to judge. Operator:> >= < <= 
				* -- Query employees whose salary is less than the average salary
				SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
			2. The result of subquery is multi row and single column:
				* -- query'Finance'All employee information of the Department
				SELECT id FROM dept WHERE `name`='Finance Department' or name = 'Marketing Department';
				SELECT * FROM emp WHERE dept_id in (SELECT id FROM dept WHERE `name`='Finance Department' or `name`='Marketing Department') ;
			3. The result of subquery is multi row and multi column:
				* Subqueries can be queried as a virtual table.
				-- The entry date of the employee is 2011-05-20 Subsequent employee information and department information

				SELECT *  FROM emp WHERE join_date > '2011-05-20';

				SELECT id FROM dept WHERE emp.dept_id = dept.id;

				SELECT * FROM dept t1 ,(SELECT *  FROM emp WHERE join_date > '2011-05-20') t2 
				WHERE t1.id = t2.dept_id;
				-- Common internal connection
				SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND join_date >'2011-05-20';

affair

1. Basic introduction to transaction
	1. Concept:
		* If a business operation contains multiple steps and is managed by things, these business operations either succeed or fail at the same time
		* example:
		-- Create account table
		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);



		 UPDATE account set balance = 1000;
 
		 SELECT * FROM account;



		 -- Zhang San transferred 500 yuan to Li Si
		-- 0.Open transaction
			START TRANSACTION;
		-- 1.Zhang San account -500
			 UPDATE  account SET balance= 500  WHERE NAME = 'zhangsan';
 
		 -- Wrong...
		 -- 2.Li Si account +500
			 UPDATE account SET balance = 1500 WHERE `name` = 'lisi';
		-- If no problem is found in execution, commit the transaction
			  COMMIT;
 
		 -- A problem is found. Roll back the transaction
			 ROLLBACK;
	2. Operation:
		1. Open transaction: start transaction;
		2. RollBACK : rollback;
		3. Commit transaction: commit

	3. MySQL Transactions in the database are automatically committed by default
		* There are two ways to commit a transaction:
		
			* Auto submit:
				* mysql Is automatic submission
				* One DML(The addition, deletion and modification) statement will automatically commit the transaction
			* Manual submission:
				* Oracle The database is manually committed by default
				* You need to start the transaction before committing
		* To modify the default commit method of a transaction:
			* View the default commit method for transactions	SELECT @@autocommit;   -- 1 Automatic submission on behalf of		0 Submitted manually on behalf of
			* To modify the default submission method: SET @@autocommit  = 0;
2. Four characteristics of transactions:
	1. Atomicity: it is an indivisible minimum unit operation, which either succeeds or fails at the same time
	2.Persistence: after the transaction is committed or rolled back, the database will persist the data
	3.Isolation: multiple transactions are independent of each other
	4.Consistency: the total amount of data remains unchanged before and after the transaction operation
3. Isolation level of transactions (understand)
	* Concept: multiple transactions are isolated and independent of each other. However, if multiple transaction operations process the same batch of data, some problems will occur, which can be solved by designing different isolation levels
	* Problems:
		1. Dirty read: one transaction reads uncommitted data from another transaction
		2. Non repeatable read (wasted): in the same transaction, the transactions read twice are 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 the data modified by itself
	* Isolation level
		1. read uncommited:Read uncommitted
			* Problems: dirty reading, unrepeatable reading (wasted), unreal reading
		2. read commited: Read committed
			* Problems: unrepeatable reading (wasted reading) and unreal reading
		3. repeatable read:  Repeatable reading
			* The problem: unreal reading
		4. serializable: Serialization
			* Can solve all problems

		* Note: from small to large, the isolation sector is more and more safe, but less and less efficient
		* Database isolation domain:
			* set global transaction isolation level Level string;

DCL: used for management and authorization

* SQL Classification:
	1. DDL: Operations on databases and tables
	2. DML: Add, delete and modify data in the table
	3. DQL : Data in query table
	4. DCL: Manage and authorize users

* DBA: Database Administrator

* DCL: Management, authorization
	1. Manage users
		1. Add user:
			-- Create user
			CREATE user 'user name' @ 'host name' MIDDLEINT BY 'password';
		2. Delete user:
			-- delete user
			DROP USER 'user name'@'host name';
		3. Modify user password:
			: UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER  = 'user name'; 

			UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER  = 'user name'; 
			UPDATE USER SET password = password('abc') WHERE USER = 'root';

			set password for 'user name'@'host name' = password('New password');
			SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

			* mysql I forgot root User password?
				1. cmd ---> net stop mysql   stop it mysqk service
					* An administrator is required to run the cmd
				2. Start with no authentication mysql Services: mysql -skip-grant-tables
				3. Open new cmd Window, direct input mysql Command, click enter to log in
				4. use mysql;
				5. update user set password=('Your new password') where user = 'root';
				6. Close both windows
				7. Open the task manager and end it manually mysqld.exe Process of
				8. start-up mysql service  ---> net start mysql
				9 . Log in with a new password

		4. Query user:
			-- 1. switch mysql database
			use mysql;

			-- query user surface
			SELECT * FROM USER;

			* Wildcard: % Indicates that users can log in to the database on any host
	2. to grant authorization:
		1. Query authority:
			-- View permissions
			show GRANTS FOR 'user name'@'host name';
		2. Grant permissions:
			 -- Grant permissions
			GRANT Permission list ON Database name.Table name  to 'user name'@'host name';
			GRANT SELECT ON db2.account to 'lisi'@'%';

			-- Grant all permissions to user Zhang San on any database and any table
			grant all on *.* to 'zhangsan'@'localhost';
		3. Revoke permissions:
			-- Revoke permissions
			revoke Permission list on Database name.Table name from 'user name'@'host name';
			-- Revoke all permissions of Zhang San
			revoke all on *.* from 'zhangsan'@'localhost';

Tags: Database MySQL SQL

Posted on Fri, 17 Sep 2021 17:15:43 -0400 by ams53