MySQL
mysql -u root -p --default-character-set=utf8mb4
You need to understand how to write SQL query statements:
1,SELECT .... 2,FROM .... 3,JOIN ... ON .... 4,WHERE ... 5,GROUP BY ... 6,HAVING ... 7,ORDER BY ...
The execution order is different from the writing order:
2,FROM 3,JOIN ... ON ... 4,WHERE ... 5,GROUP BY ... 6,HAVING ... 1,SELECT ... 7,ORDER BY ...
1, Basic database operation
1.1 user management
*1.1. 1. * login
mysql -u user -p;
*1.1. 2. * view current user
select user; -- from dual Can be omitted select user from dual;
*1.1. 3. * create new users
create user user name@localhost identified by'password';
*1.1. 4. * authorize users
grant all on *.* to user name@localhost -- Grant all permissions in the new database to the new user grant all on Database name.* to user name@localhost ;
*1.1. 5. * delete user
drop user user name@localhost
1.2. Database management
*1.2. 1. * view all databases that the current user can operate
show databases;
*1.2. 2. * create a new database
create database Database name; -- []Can be omitted create database Database name [ default character set utf8mb4 ];
*1.2. 3. * select database
use Database name; -- View current database(The selected database) select database(); No database has been selected and will be returned NULL
*1.2. 4. * export database
mysqldump -u ddy -p ddy market>d:/market.sql mysqldump -u user name -p Database name > Path name
*1.2. 5. * import database
mysql -u ddy -p market<d:/market.sql mysql -u user name -p Database name < Path name
Both 15 and 16 are executed at the command prompt
*1.2. 6. * import source
mysql> source D:\scripts\region_dept_emp.sql
17. You must select the database in mysql before executing source
*1.2. 7. * delete database
drop database Database name;
1.3 table management
*1.3. 1. * view table
show tables;
*1.3. 2. * create table
create table Table name (column name type) (width) , ... );
*1.3. 3. * delete table
drop table Table name;
*1.3. 4. * truncation table
truncate table Table name;
*1.3. 5. * add column
alter table Table name add column ( Column name type(width) , ... )
*1.3. 6. * delete column
ALTER TABLE Table name DROP COLUMN Listing
*1.3. 7. * modify column names
ALTER TABLE Table name CHANGE COLUMN Original column name new column name type(width);
1.4 data management
*1.4. 1. * add data
Add data in order
insert into Table name (column name) values (Value);
*1.4. 2. * delete data
Delete the word table record first, and then delete the main table record
DELETE FROM kfm WHERE id = 1 ;
*1.4. 3. * update data
UPDATE kfm SET name = 'tom' , birthdate='2001-05-06' WHERE id = 1 ;
1.5. Data query
1.5. 1. Inquiry
SELECT id , name , birthdate FROM kfm where condition;
1.5. 2. Sort
SELECT id , name , birthdate FROM kfm ORDER BY name ASC;
1.5. 3. Paging query
-- limit 0 ，5 0 It refers to the number of items to query, and 5 refers to the number of data to query -1 Is the last line of record SELECT * FROM kfm LIMIT 0 , 5;
1.5. 4. Propose duplicate rows in query
use DISTINCT You can eliminate the in the query results "Repeat line" mysql> SELECT DISTINCT deptno FROM emp ;
2, Constraints and advanced queries
1. Restraint
View constraints through show create table
1.1. Primary key constraint
There is often a column or a combination of multiple columns in a table, and its value can uniquely identify each row in the table. Such a column or columns are called the primary key of the table, also known as the primary key constraint.
The most prominent feature of the primary key constraint is that the values in the primary key column are not allowed to be repeated and not empty. The entity uniqueness of the table can be enforced through the primary key constraint.
alter table Table name add Writable( constraint Constraint name) primary key(Listing); ALTER TABLE Table name MODIFY Listing INT PRIMARY KEY; When creating a table, you can add the type directly after it primary key
Delete primary key constraint (no name)
MySQL > alter table name DROP PRIMARY KEY;
Self growth column
Self growing column must be a primary key column
-- Set self growth column auto_increment
1.2. Unique constraints
The value of non primary key column needs to be unique, and the unique constraint is used
Duplicate is not allowed. One can be empty
alter table Table name add constraint unique(Listing);
1.3. Default constraints
Set the default value for the value in the column. When adding records with insert, the default value will be used if no value is specified. If a value is specified, the default value is invalid
alter table Table name change column Old column name new column name data type default numerical value;
1.4 foreign key constraints
Relationship: one to one, one to many, many to many
alter table Child table name add Writable( constraint Constraint name) foreign key Child table name(Child table column name)references Main table name(Primary key column name);
Attempt to delete a foreign key constraint
ALTER TABLE table name DROP CONSTRAINT constraint name;
1.5 check constraints
Used to check whether the column value meets the requirements
alter table Table name add constraint Listing check(Inspection conditions);
2. Advanced query
2.1. Basic query
2.1. 1. Query all information
select * from Table name;
2.1. 2. Query specified column
select Column 1,Column 2,.... from Table name;
2.1. 3. Remove duplicate values from columns
select distinct Listing from Table name;
2.1. 4. Use operation
select Column 1+Column 2 from Table name;
2.1. 5. Use column aliases
select Listing as Display name from Table name;
2.2. Query criteria
2.2. 1. Comparison operator
select * from Table name where Listing>(< | >= | <= | <>and!=equally)condition;
2.2. 2. Logical operator
select * from Table name where condition and(or | not) condition;
2.2.3,between...and...
select * from Table name betweeen condition and condition;
2.2. 4. in and exists
select * FROM Table name where job IN ('project manager', 'project leader ' );
The in statement is to hash the outer table and the inner table, while the exists statement is to loop the outer table and query the inner table every loop
If the two tables of the query are of the same size, there is little difference between in and exists.
If one of the two tables is small and the other is large, the large sub query table uses exists and the small sub query table uses in.
Not in and not exists: if not in is used in the query statement, the internal and external tables are scanned for the whole table without index; The sub query of not extsts can still use the indexes on the table. Therefore, no matter which table is large, using not exists is faster than not in
2.2. 5. Fuzzy query like
Wildcard% and_ Implementation,% represents any character_ Represents any character.
-- Query the surname song select * FROM Table name where Listing like'Song Dynasty%'; -- Query to x Three words at the end select * FROM Table name where Listing like'_ _x'; -- display ename The second letter in the is the underlined employee information SELECT empno , ename FROM emp WHERE ename LIKE '_\_%' ; -- use escape To explicitly define escape characters SELECT empno , ename FROM emp WHERE ename LIKE '_$_%' ESCAPE '$' ; -- Paired single quotation marks always represent the string boundary. If a single quotation mark needs to appear in the string, you only need to write the single quotation mark twice mysql> SELECT empno , ename FROM emp WHERE ename LIKE '_''_%' ESCAPE '''' ;
2.2. 6. null query
NULL indicates that there is no data in the database, and is should be used to judge whether there is NULL in the column
select * FROM Table name where Listing is null;
2.3 sorting
order by ascending (ASC) | descending (DESC) the default is ascending
select * FROM Table name where .... order by Listing asc;
2.4 group query
According to the specified column, the same values in the column are divided into a group, which is represented by a record. After grouping, you can aggregate and query the data in each group
SELECT deptno Department number, COUNT(*) Number of people FROM emp group by deptno;
2.5 screening
Having clause is used to filter the data in each group after grouping. Compared with the where clause, which filters before grouping, the having clause filters after grouping
SELECT deptno Department number, COUNT(*) Number of people FROM emp GROUP BY deptno having Number of people > 3;
2.6. Connection query
2.6. 1. Inner connection
We will EMP Deptno = dept.deptno condition is called connection condition
We call the query that meets the connection conditions as an inner join
Non equivalent connection
-- Non equivalent connection of inner connection mysql> select empno , ename , sal from emp ;
Equivalent connection
-- Equivalent connection of inner connection mysql> -- query emp Job number, name, department number and department name of each employee in the table mysql> select * from emp ;
Two table connections
FROM Table 1 INNER JOIN Table 2 ON Table 1.Field number=Table 2.Field number
Three table connections
FROM (Table 1 INNER JOIN Table 2 ON Table 1.Field number=Table 2.Field number) INNER JOIN Table 3 ON Table 1.Field number=Table 3.Field number
Four table connections
FROM ((Table 1 INNER JOIN Table 2 ON Table 1.Field number=Table 2.Field number) INNER JOIN Table 3 ON Table 1.Field number=Table 3.Field number) INNER JOIN Table 4 ON Member.Field number=Table 4.Field number
2.6. 2. External connection
If the query result contains data that does not meet the connection conditions in addition to the data that meets the connection conditions, this query is called external connection
-- The left side of the right outer connection is not worth adding as null mysql> SELECT * FROM dept d RIGHT OUTER JOIN emp e ON e.deptno = d.deptno ;
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-1sdfo5bu-1629766404292)( https://i.loli.net/2021/08/12/Xhv5FnekjWf2sbY.png )]
The following statements are considered as non-standard writing of right connection (not supported by MySQL 8.0.25)
SELECT * FROM t_major m , t_class c WHERE m.id(+) = c.major_id ;
-- Left outer connection There is nothing worth adding on the right null mysql> SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.deptno = d.deptno ;
[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-3m4wl2pq-1629766404294)( https://i.loli.net/2021/08/13/UFAQE4GhjZbgleD.png )]
The following statements are considered as non-standard writing of left connection (not supported by MySQL 8.0.25)
SELECT * FROM t_major m , t_class c WHERE m.id = c.major_id(+) ;
-- Total external connection( MySQL I won't support it ) SELECT * FROM t_class c FULL OUTER JOIN t_major m ON c.major_id = m.id ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN t_major m ON c.major_id = m.id' at line 1
2.6. 3. Self join query
from emp e1 join emp e2 on e1.mgr=e2.empno
2.7 sub query
-- single-row subqueries : Return single row and single column
-- Multiple-column subqueries : Return single row and multiple columns (Paired comparisons were used)) SELECT * FROM emp WHERE ( deptno , job , manager ) = ( SELECT deptno , job , manager FROM emp WHERE upper(ename) = 'SMITH' ) ;
-- Multiline subquery: Return multiple rows and single column
-- Related sub query: Subquery uses external SQL Some tables or columns of (But outside SQL Tables and columns in subqueries cannot be used)
-- nested subqueries :You can continue to embed subqueries within subqueries
2.8,case
-- Use 1 for male and 2 for female
mysql> SELECT id , name , CASE
-> WHEN gender = 1 THEN 'common'
-> WHEN gender = 2 THEN 'mother'
-> ELSE 'unknown'
-> END AS gender
-> FROM t_dogs ;
+------+------+--------+
| id | name | gender |
+------+------+--------+
| 1 | Wangcai | common |
| 2 | Laifu | mother |
| 3 | Xiao Hei | unknown |
| 3 | chinese rhubarb | unknown |
+------+------+--------+
4 rows in set (0.00 sec)
3. Functions
3.1 aggregation function
Aggregate functions include sum, max, min, avg and count
Grouping required to use aggregate functions
3.2 mathematical function
round function
Round (x) x round
round(x,[y]) y refers to the number of digits reserved
floor function
Floor (x) x is rounded down. For example, floor (15.87) is rounded to 15
ceiling function
Ceiling (x) x is rounded up. For example, ceiling (15.34) is 16 after rounding
abs function
ABS (- 12) takes the absolute value
mod function
mod(x,y) takes the remainder of X divided by y
power function
power(x, y) takes the Y power of X
sqrt function
sqrt(x) takes the root of X
3.3 string function
length function
Get string length
upper/lower function
The upper function changes all characters to uppercase
The lower function changes all characters to lowercase
concat function
concat (x, y) connects two strings of X and y
instr function
instr('java web ') 6 retrieves the index of the substring in the parent string
ltrim function
ltrim('java ') remove the left space
rtrim function
rtrim('java ') remove the space on the right
trim function
trim('java ') removes the spaces at both ends
replace function
replace('hello java ',' java ',' 123 ') replace text
substr function
substr('12345 ', 2) 2345 the first parameter is the intercepted field, and the second parameter is intercepted from the first bit
3.4 date function
3.4. 1. Date formatter
| format | describe |
|---|---|
| %a | Abbreviated week name |
| %b | Abbreviated month name |
| %c | Month, value |
| %D | Days of the month with English prefix |
| %d | Day of the month, value (00-31) |
| %e | Day of the month, value (0-31) |
| %f | Microsecond |
| %H | Hours (00-23) |
| %h | Hours (01-12) |
| %I | Hours (01-12) |
| %i | Minutes, value (00-59) |
| %j | Days of the year (001-366) |
| %k | Hours (0-23) |
| %l | Hours (1-12) |
| %M | Month name |
| %m | Month, value (00-12) |
| %p | AM or PM |
| %r | Time, 12 hours (hh:mm:ss AM or PM) |
| %S | Seconds (00-59) |
| %s | Seconds (00-59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %U | Week (00-53) Sunday is the first day of the week |
| %u | Week (00-53) Monday is the first day of the week |
| %V | Week (01-53) Sunday is the first day of the week and is used with% X |
| %v | Week (01-53) Monday is the first day of the week, used with% x |
| %W | Sunday name |
| %w | Days of the week (0 = Sunday, 6 = Saturday) |
| %X | Year, where Sunday is the first day of the week, 4 digits, used with% V |
| %x | Year, where Monday is the first day of the week, 4 digits, used with% v |
| %Y | Year, 4 |
| %y | Year, 2 |
3.4. 2. Query today
/* NOW() Function returns the current date and time. TO_DAYS()The function returns the number of days between date and year 0 (date "0000-00-00"). */ SELECT * FROM cpidata WHERE TO_DAYS( Time field name ) = TO_DAYS(NOW());
3.4. 3. Query yesterday
/* NOW() Function returns the current date and time. TO_DAYS()The function returns the number of days between date and year 0 (date "0000-00-00"). */ SELECT * FROM Table name WHERE TO_DAYS( NOW( ) ) - TO_DAYS( Time field name) = 1
3.4. 4. Query this week
/* YEARWEEK()Returns the year and week DATE_FORMAT(Formatted date (formatter) date formatting function */ SELECT * FROM Table name WHERE YEARWEEK(DATE_FORMAT(Date field name,'%Y-%m-%d')) = YEARWEEK(NOW());
3.4. 5. Query last week
/* YEARWEEK()Returns the year and week DATE_FORMAT(Formatted date (formatter) date formatting function */ SELECT * FROM Table name WHERE YEARWEEK(DATE_FORMAT(Date field name,'%Y-%m-%d')) = YEARWEEK(NOW())-1;
3.4. 6. Query in recent 7 days
/* DATE_SUB() Function subtracts the specified time interval from the date DATE_SUB(Legal date expression, INTERVAL (type of time you want to add) CURDATE() Function returns the current date. */ SELECT * FROM Table name where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(Time field name)
3.4. 7. Query for nearly 30 days
/* DATE_SUB() Function subtracts the specified time interval from the date DATE_SUB(Legal date expression, INTERVAL (type of time you want to add) CURDATE() Function returns the current date. */ SELECT * FROM Table name where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(Time field name)
3.4. 8. Query current month
/* DATE_FORMAT(Formatted date (formatter) date formatting function */ SELECT * FROM Table name WHERE DATE_FORMAT( Time field name, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
3.4. 9. Query last month
/* PERIOD_DIFF(The first date, second date) function returns the difference between two dates. The results are calculated in months */ SELECT * FROM Table name WHERE PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( Time field name, '%Y%m' ) ) =1
3.4. 10. Query current quarter
/* QUARTER((date) quarter of return date: */ SELECT * FROM Table name WHERE QUARTER(Date field name)=QUARTER(NOW());
3.4. 11. Query last quarter
/* QUARTER((date) quarter of return date: DATE_SUB() Function subtracts the specified time interval from the date DATE_SUB(Legal date expression, INTERVAL (type of time you want to add) */ SELECT * FROM Table name WHERE QUARTER(Date field name)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));
3.4. 12. Query current year
/* year(Date (field name) returns the year in the date */ SELECT * FROM Table name WHERE YEAR(Date field name)=YEAR(NOW());
3.4. 13. Query last year
/* year(Date (field name) returns the year in the date DATE_SUB() Function subtracts the specified time interval from the date DATE_SUB(Legal date expression, INTERVAL (time type you want to add) */ SELECT * FROM Table name WHERE YEAR(Date field name)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
3.4. 14. Date interception function
| function | effect | function | effect |
|---|---|---|---|
| dayofweek(date) | What day of the week is date | dayofmonth(date) | date is the day of the month |
| dayofyear(date) | date is the day of the year | year(date) | Returns the year of date |
| month(date) | Returns the month of date | day(date) | Returns the date of date |
| week(date) | The date returned is the week of the year | hour(time) | Returns the number of hours of time |
| minute(time) | Returns the number of minutes in time | second(time) | Returns the number of seconds of time |
| current_timestamp() | Get current time | current_date() | Get current date |
| current_time() | Get current time | now() | Get current date + time |
| datediff(date1,date2) | Returns the number of days between two dates |
3, Business
The default engine of MySQL 8 is InnoDB, which supports transactions; Before MySQL 5.5, the default engine was MyISAM, which does not support transactions
The set of operations constituting a single logical unit of work is called a transaction. It is a set of SQL statements executed as an indivisible logical unit.
Exiting the mysql prompt means ending the transaction, terminating the session, and disconnecting; To log in to mysql again, you need to reset the transaction submission method
When a transaction starts, it starts a new transaction without committing, and the previous transaction will be committed automatically
-- Query transaction submission method(If 1 is returned, it means automatic submission) SELECT @@autocommit ;
All DDL statements will cause the transaction to be committed automatically. CREATE TABLE / ALTER TABLE / DROP TABLE / TRUNCATE TABLE
3.1 characteristics
Atomicity: all operations in a transaction either occur or do not occur
Consistency: transactions change the database from one consistent state to the next
Isolation: the execution of a transaction should not be disturbed by other transactions
Durability persistence: once a transaction is committed, the result is persistent
3.2. Control statement
Start the transaction through begin and start transaction. The transaction submission method is manual by default, and the session submission method is automatic (on the premise of no change)
-- Start transaction BEGIN begin; start transaction ; -- use commit It means that the previous transaction is completed and a new transaction is opened commit;
-- Commit transaction COMMIT commit;
-- Rollback transaction: This is to undo the deleted data rollback ; rollback to
3.3. Transaction submission mode
-- Turn on transaction auto commit set autocommit = 1 ;
-- Turn off transaction auto commit set autocommit = 0 ;
3.4 isolation level
The isolation level of transactions that have been implicitly opened when the connection is established and the session is created is readable
Therefore, the transaction needs to be explicitly terminated so that the new transaction can use the isolation level we set
- READ UNCOMMITTED read uncommitted
- READ COMMITTED read committed
- REPEATABLE READ repeatable read
- SERIALIZABLE serialization
-- use SET TRANSACTION To set the transaction isolation level SET session TRANSACTION ISOLATION LEVEL read committed ; set @@session.transaction_idolation='red-uncommitted';
3.5. Save point
SAVEPOINT identifierName Set save point
RELEASE SAVEPOINT identifierName Release savepoint
ROLLBACK TO identifierName Rollback to savepoint
3.6 problems caused by concurrency
-
Dirty Read: one transaction reads the uncommitted data of another transaction
(for example, if the isolation level is read uncommitted, you can raise the transaction isolation level to read committed.)
-
Nonrepeatable Read cannot be read repeatedly: the data queried multiple times in the same transaction are different
Because the uncommitted data in another transaction is read (for example, the isolation level is read uncommitted)
Because the committed data in another transaction is read (for example, the isolation level is read committed)
Raise the transaction isolation level to repeatable read
- Lost Update missing update 1
- Lost Update missing Update 2
- Phantom Read
3
ZN5. Png "ALT =" transaction concurrency conflict _6isolation level "style =" zoom:50%; "/ >
-
Dirty Read: one transaction reads the uncommitted data of another transaction
(for example, if the isolation level is read uncommitted, you can raise the transaction isolation level to read committed.)
-
Nonrepeatable Read cannot be read repeatedly: the data queried multiple times in the same transaction are different
Because the uncommitted data in another transaction is read (for example, the isolation level is read uncommitted)
Because the committed data in another transaction is read (for example, the isolation level is read committed)
Raise the transaction isolation level to repeatable read
- Lost Update missing update 1
- Lost Update missing Update 2
- Phantom Read
3