sql statement summary

Using sql statements to operate the database

Create database

Syntax:

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset_name] [COLLATE collation_name]

Where charset_name is the default character set specified for the database
Collate is the default collation rule specified for the database (collation rule is a set of rules used to compare characters in the character set, which can control whether the where condition is case sensitive during select query.)

Example 1: create a database named mydb1.

CREATE  DATABASE  IF NOT EXISTS  mydb1;

Example 2: create a mydb2 database using the gbk character set.

CREATE  DATABASE  IF NOT EXISTS  mydb2 CHARACTER SET gbk;

view the database

Syntax:

# Show all databases
SHOW DATABASES
# Show database creation statements
SHOW CREATE DATABASE db_name

modify the database

Note: once the database is created successfully, its name cannot be modified

Syntax:

ALTER DATABASE db_name [CHARACTER SET charset_name] [COLLATE collation_name]

Example: view the database in the server and change the character set of mydb2 library to utf8

ALTER DATABASE mydb2 CHARACTER SET utf8

Delete database

Syntax:

DROP DATABASE [IF EXISTS] db_name

Select database

Syntax:

# Select database 
USE db_name
# Query the currently selected database 
SELECT DATABASE()

There is no command to exit the database. If you want to exit the current database and enter another data, you can directly switch to another database

Using sql statements to operate database tables

Create table

CREATE TABLE student 
(
    student_id varchar(20) PRIMARY KEY COMMENT 'Student number',
    student_name varchar(20) NOT NULL COMMENT 'Student name',
    gender char(10) NOT NULL DEFAULT 'male' COMMENT 'Gender',
    birth_day date NOT NULL COMMENT 'birthday',
    age int NOT NULL DEFAULT '0' COMMENT 'Age',
    class_id varchar(20) NOT NULL DEFAULT 'C100' COMMENT 'Class number',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT 'Mathematics achievement',
    teacher_id varchar(20) DEFAULT NULL COMMENT 'Teacher number'
)COMMENT='Student list';
--Query table structure
desc student

be careful:

It is not case sensitive, but it is best to capitalize keywords
Keyword COMMENT set comments
Except for the primary key field, it is recommended to set default values for all other fields and assign default values according to the business meaning of the field
Never update the value of the primary key
Judge whether it is null / gender is not null

String type:

Fixed length string - char(n): a string type with a fixed length. Its length is specified when creating a table. By default, it opens up a space of n characters, which is efficient.
Variable length string - varchar(n): a string type with variable length. Specify a maximum length or no length when creating a table. First judge the character length and open up the space corresponding to the length, which is inefficient and saves space.

Value type:

TINYINT: occupies 1 byte, relative to byte in java
SMALLINT: occupies 2 bytes, which is relative to short in java
Int: occupies 4 bytes, which is relative to int in java
BIGINT: 8 bytes, relative to long in java
Float: 4-byte single precision floating-point type, relative to float in java
Double: 8-byte double precision floating-point type, relative to double in java

Date type:

DATE: November 5, 2017
TIME: TIME format 'HH:MM:SS' 19:19:19
DATETIME: Date Time: November 5, 2017 19:19:19 year range: 1000 ~ 9999
TIMESTAMP: time stamp 2017-11-05 19:19:19 year range: 1970 ~ 2037

Logical type:

BIT type fields can only take two values: 0 or 1.

Field constraints

Primary key constraint: ensure that the values in the constrained columns must be unique and cannot be empty.
- Add primary key constraint (when creating table): col_name datatype primary key
- When updating a table: Alter table tabname add primary key(col_name)
- Set auto growth of primary key: col_name datatype primary key auto_increment
Unique constraint: unique ensures that the values of the constrained columns must be unique, that is, they cannot be repeated.

NULL is not equal to any value, including itself
Non NULL constraint: not null ensures that the value of the constrained column cannot be null
Foreign key constraint

Manipulation table

Update table name: RENAME TABLE old table name TO new table name;
Update field name: ALTER TABLE table name CHANGE old field name new field name field type;
Update field type: ALTER TABLE table name MODIFY field name new field type;
Delete field default: ALTER TABLE name ALTER COLUMN field name DROP DEFAULT;
Set primary key: ALTER TABLE table name ADD PRIMARY KEY (list of primary key fields);
Delete primary key field: ALTER TABLE table name DROP PRIMARY KEY;
New fields: ALTER TABLE table name ADD COLUMN new field name field type and other attributes;
Delete field: ALTER TABLE table name DROP COLUMN field name;
Delete table: DROP TABLE name;

RENAME TABLE teacher_db TO teacher;
ALTER TABLE teacher CHANGE teacher_id id varchar(20);
ALTER TABLE teacher MODIFY id varchar(30);
ALTER TABLE teacher DROP PRIMARY KEY;
ALTER TABLE teacher ADD PRIMARY KEY(id);
ALTER TABLE teacher ALTER COLUMN teacher_name DROP DEFAULT;
ALTER TABLE teacher DROP COLUMN gender;
ALTER TABLE teacher ADD COLUMN gender varchar(10) DEFAULT 'male' COMMENT 'Gender';
SELECT * FROM teacher;

insert data
- Insert entire row of fields:
  1. INSERT INTO table name VALUES (v1,v2,v3,v4);
  - The order of values must be consistent with the order of fields in the table, which is unsafe
  1. INSERT INTO table name (field 1, field 2, field 3, field 4) VALUES (v1,v2,v3,v4);
  - In this way, one-to-one correspondence is safe
- Insert some fields: INSERT INTO table name (field 1, field 2) VALUES (v1,v2);
- Insert multiple rows at once:
  - INSERT INTO table name VALUES (v1,v2,v3,v4), ('v1,v2,v3,v4);
- Insert with other result sets:
  - INSERT INTO table 1 SELECT * FROM table 2;
- Clone table
  - SELECT * INTO teacher_bak FROM teacher;
  - CREATE TABLE teacher_bak AS SELECT * FROM teacher;
```
SELECT * FROM teacher;
INSERT INTO teacher VALUES('T100','Li Ming','male');
INSERT INTO teacher(id,teacher_name)
       VALUES('T101','Li Gang'),
             ('T102','Bruce Lee'),
             ('T103','distinguished linguist and strong supporter of language reform');
CREATE TABLE teacher_bak
AS
SELECT * FROM teacher;
SELECT * FROM teacher_bak;
```

Update data

UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]    
#Example:
UPDATE employee SET salary = 30000;

Delete data
```
DELETE FROM tab_name [WHERE where_definition]
# Example:
DELETE FROM employee WHERE name IS NULL;
```
1. WHERE is used to filter the records to be deleted. If the WHERE clause is not used, all data in the table will be deleted (delete one by one, which is inefficient).
  
  TRUNCATE TABLE table name (delete all records in the table, directly destroy the table, and then rebuild the table, which is efficient)
2. The DELETE statement cannot DELETE the value of a column. You should use the ALTER TABLE table name DROP COLUMN column name
3. The delete statement only deletes records, not the table itself. To delete a table, use the DROP TABLE table name

Query data

Query part column: SELECT field list FROM table name;
Query all columns
- SELECT * FROM table name;
- SELECT all fields list FROM table name;
Sorting returned results
- SELECT field list FROM table name ORDER BY field 1 [ASC/DESC], field 2 [ASC/DESC];
- The default is ascending ASC
duplicate removal
- SELECT DISTINCT field name FROM table name LIMIT 10
- Limit return results
  - Return the first few lines: keyword TOP, LIMIT
    - SELECT TOP 10 field list FROM table name;
    - SELECT field list FROM table name LIMIT 10;
    - It is generally used with the ORDER BY keyword to return the first few rows sorted by some fields;
    - Mysql database does not support top, only Limit
  - Return the middle lines: keyword LIMIT M OFFSET N (starting from line N, return line M records)
    - SELECT field list FROM table name LIMIT M OFFSET N;
    - SELECT field list FROM table name LIMIT N,M;
    - Generally, it is used in conjunction with the ORDER BY keyword to return a paged record according to the M-line page;
  - Return the following lines: Keywords TOP, LIMIT, ORDER BY
    - In SQL statements, there is no special writing method for returning the last few rows. Generally, it is converted to sorting by the opposite way of returning the first few rows, and then returning the first few rows. Use this method to return the last few lines in disguise.
```
SELECT student_id, student_name, birth_day, age FROM student;
SELECT student_id, student_name, birth_day, age FROM student 
ORDER BY age ASC, student_name ASC;
/*Equivalent to*/
SELECT student_id, student_name, birth_day, age FROM student 
ORDER BY 4 ASC, 2 ASC;
SELECT  student_id, student_name, birth_day, age FROM student 
ORDER BY 4 ASC, 2 ASC
LIMIT 10;
SELECT  student_id, student_name, birth_day, age FROM student 
ORDER BY 4 ASC, 2 ASC
LIMIT 10,20
```

Filter data

Use WHERE clause: SELECT field list FROM table name WHERE filter criteria;
Common operators
Filter individual values

SELECT * FROM student WHERE birth_day <= '2005-12-31';
Filter NULL values

SELECT * FROM student WHERE class_id IS NULL;
Filter collection

SELECT * FROM student WHERE birth_day BETWEEN '2005-01-31' AND '2005-12-31';

SELECT * FROM student WHERE age IN (10,11,15)

SELECT * FROM student WHERE student_id NOT IN ('S20160001','S20160002')

IN does not support wildcards
Filter data using wildcards: keyword LIKE

Symbol meaning
Percent sign% Match 0~ more than one arbitrary character
Underline_ Match 1 arbitrary character
Square brackets [], [^] Match characters in 1 character set
SELECT * FROM student WHERE student_name LIKE 'Chen%'

Escape character in Mysql\

Mysql database does not support [] and [^], Sql Server does

Notes on using wildcards:
- Do not overuse wildcards;
- If you really need to use wildcards, try not to use them at the beginning of the matching pattern;
- Pay special attention to the position of wildcards, otherwise it is likely that the returned results are inconsistent with expectations;
Combine WHERE clause
- Combine WHERE clauses with logical operators:
- For example: how to take out all male students over the age of 15 and surnamed Chen or?
  Select * from student where age > 15 and (student_name like 'Chen%' OR gender = 'male');
- In sql statements, AND and OR have the same priority
- AND, OR who executes first
- When there are many conditions, choose reasonable () to eliminate ambiguity
```
-- Knowing the teacher's ID number, want to remove all the teachers born in 70s?
-- 7-14 Bits indicate the date of birth
ALTER TABLE teacher ADD COLUMN identity_num char(18) DEFAULT '622301197012312360';
SELECT * FROM teacher WHERE identity_num REGEXP '[0-9]{6}197.[0-9]{8}';
```

Symbol	meaning
Percent sign%	Match 0~ more than one arbitrary character
Underline_	Match 1 arbitrary character
Square brackets [], [^]	Match characters in 1 character set

Create a new output field

Numeric operators: +, -, */

SELECT trans_id,stock_name,price,volume,price*volume FROM t_stock_trans_dtl;

Note: Teradata: INT / INT returns INT type
Field splicing

Example: how to display the stock name and stock code by "Hongdu Airlines (sh600316)"?
1. Access, SQL Server: use the splicing operator plus sign (+)
SELECT stock_name + '(' + stock_code + ')' FROM t_stock_trans_dtl;
1. DB2, Oracle, Teradata: two vertical bars (|) using the splicing operator
  
  SELECT stock_name || '(' || stock_code || ')' FROM t_stock_trans_dtl;
2. MySQL: use the function CONCAT()
  
  SELECT CONCAT(stock_name,'(',stock_code,')') FROM t_stock_trans_dtl;
3. Realize the splicing of numeric fields
  
  The CAST() function can convert an expression of one data type to another
  SELECT CAST('678' AS INT) + 123;
  
  The CONVERT() function converts the specified data type to another data type
  SELECT CONVERT(INT, '678') + 123;
Use alias: keyword AS

Select concat (stock_name, '(', stock_code, '') as' stock name and code 'FROM t_stock_trans_dtl;

Suggestion: whether there is a space in the Chinese alias or not, use quotation marks

CASE WHEN logic conversion

CASE WHEN syntax I:

CASE WHEN Conditional expression 1 THEN Result expression 1
	 WHEN Conditional expression 2 THEN Result expression 2
				...
	 WHEN Conditional expression N THEN Result expression N
	 [ELSE ELSE Result expression]
END

When there is no ELSE part, NULL is returned if all conditional expressions are not satisfied.

SELECT * FROM t_stock_trans_dtl;
SELECT trans_id, volume,
CASE WHEN volume >=3000 THEN 'Big order'
     WHEN volume <3000 and volume > 300 THEN 'mid'
     ELSE 'Small order'
END
AS 'Buying volume'
FROM t_stock_trans_dtl
WHERE opt_typ = 'purchase';

Each CASE WHEN should have an ELSE branch

CASE WHEN syntax 2:

CASE Conditional expression
	WHEN Match expression 1 THEN Result expression 1
	WHEN Match expression 2 THEN Result expression 2
	...
	WHEN Match expression N THEN Result expression N
	[ELSE ELSE Result expression]
END

The results applicable to conditional expressions are an exhaustive list. But it does not apply to the case where the result of the conditional expression is a range.

SELECT trans_id, volume,
CASE opt_typ
     WHEN 'purchase' THEN price*volume
     WHEN 'sell out' THEN 0 - price*volume
     ELSE 0
END
AS 'Transaction amount'
FROM t_stock_trans_dtl;

Nested CASE WHEN:

CASE WHEN Outer conditional expression 1 THEN (CASE WHEN Inner conditional expression 1 THEN Inner result expression 1
											...
								   WHEN Inner conditional expression M THEN Inner result expression M
								   ELSE Inner layer ELSE Result expression
							   END)
	...
	WHEN Outer conditional expression N THEN Outer result expression N
	ELSE Outer layer ELSE Result expression
END

Both syntax formats can be nested at multiple levels, but multi-level nesting is not recommended. It is recommended to nest up to two levels and format SQL statements with parentheses.

Use the function to create a new output field

Numerical function

function	effect	Example
abs(n)	Returns the absolute value of n	abs(-3) = 3
round(n,d)	Returns the rounded value of n, with d decimal places reserved	round(3.234,2) = 3.23
rand()	Returns a random number between 0 and 1	0.2511382673
pow(x,y)	Returns the y-power of x	pow(2,3) = 8
mod(m,n)	Returns the remainder of m divided by n	mod(7,3) = 1
trigonometric function	Function to realize trigonometric operation	sin(90)

SELECT ABS(volume) AS 'volume', ROUND(fee,2) AS 'fee' FROM  t_stock_trans_dtl

Text function

function	effect	Example
length(str)	Returns the length of the string str	length('ABCDE') = 5
left(str,n)	Returns the left n characters of the string str	left('ABCDE',3) = 'ABC'
right(str,n)	Returns the n characters at the right end of the string str	right('ABCDE',3) = 'CDE'
trim(str)	Delete the space on the left and right of str	trim(' ABC ')='ABC'
upper(str)	Convert string str to uppercase	upper('Abc') = 'ABC'
lower(str)	Convert string str to lowercase	lower('Abc') = 'abc'
substring(str,m,n)	Returns the n characters after the m-th bit of str string	substring('ABCDE',2,3) = 'BCD'
instr(str1,str2)	Returns the first occurrence of the string str2 in str1	instr('foobarbar', 'bar') = 4
concat(str1,str2,...)	Returns the result of string str1,str2,... Spliced in sequence	concat('ABC','DEFG','H') 'ABCDEFGH'

-- How to return the stock exchange where the stock is located according to the stock code in the stock trading details?
SELECT stock_code,left(stock_code,2) FROM t_stock_trans_dtl;
SELECT stock_code,substring(stock_code,1,2) FROM t_stock_trans_dtl;

Date and time functions

function	effect	Example
current_date()	Returns the current date	'2018-02-27'
current_time()	Returns the current time	'12:34:10'
year(date)	Returns the year of the date	year('2018-02-27') = 2018
month(date)	Returns the month of the date	month('2018-02-27') = 2
day(date)	Returns the day of the date	day('2018-02-27') = 27
hour(date)	Returns the hour of the date	hour('12:40:32') = 12
minute(date)	Returns the minute of the date	minute('12:40:32') = 40
dayofyear(date)	The date returned is the day of the year	dayofyear('2018-02-27') = 58
dayofmonth(date)	The date returned is the day of the month	dayofmonth('2018-02-27') = 27
dayofweek(date)	The date returned is the day of the week (the first day is Sunday)	dayofweek('2018-02-27') = 3
date_format(date, format)	Returns the formatted result of date	date_format(current_date(),'%Y')= 2018

IF function (effect of ternary operator)
- Select if (10 < 5, 'large', 'small')

Nesting of functions

-- How to return the stock exchange where the stock is located according to the stock code in the stock trading details (capital letters are required to be returned)?
SELECT stock_code,upper(left(stock_code,2)) FROM t_stock_trans_dtl;

Summary statistics

How many students are there altogether?         SELECT COUNT(*) FROM student;
The sum of all students' math scores?  	SELECT SUM(score) FROM student;
The highest score of all students in mathematics?   SELECT MAX(score) FROM student;
What is the lowest math score for all students?	SELECT MIN(score) FROM student;
What is the average score of all students in mathematics?	SELECT AVG(score) FROM student;

Rows with NULL value are ignored in the statistical results!
It can be combined with DISTINCT to remove the duplicate

For example: SELECT COUNT(DISTINCT score) FROM student;
The fields queried with the grouping function must be the fields after GROUP BY

Group summary statistics keyword: GROUP BY
```
SELECT Grouping function, column(Request appears in GROUP BY behind)
FROM Table name
[WHERE Screening conditions]
GROUP BY List of groups
[ORDER BY clause] 
```
For example, how to get the average score of each class in Mathematics (because there are too many students, students with scores below 80 will not be calculated here) in order to compare the scores of different classes?
```
SELECT class_id,avg(score) FROM student WHERE score > 80 GROUP BY class_id;
```

-- How to count the total purchase amount of different exchanges?
SELECT left(stock_code,2) AS 'location',sum(price*volume) AS 'Turnover'
FROM t_stock_trans_dtl
GROUP BY location;

-- How to count the total transaction amount of different transaction types in different exchanges
SELECT left(stock_code,2) AS 'location',opt_typ,abs(sum(price*volume)) AS 'Turnover'
FROM t_stock_trans_dtl
GROUP BY location, opt_typ;

Keywords for filtering grouping results: GROUP BY + HAVING
- WHERE: filter rows, which is executed before GROUP BY.
- HAVING: filter groups, which is executed after GROUP BY.
- WHERE is used to filter the data in the original table, and HAVING is used to filter the grouped result set
- WHERE can be used is preferred
- The conditions for grouping functions must be placed in the HAVING clause
Group by multiple fields

Case: query the average salary of employees in each department and type of work
```
SELECT AVG(salary), department_id, job_id  
FROM employees  
GROUP BY department_id, job_id;
```

Sorting grouping result keyword: GROUP BY + ORDER BY

The collection is out of order. If you want to get a definite sorting result for each query, you must use ORDER BY

-- How to get the average math score of each class (excluding students with scores below 80 and filtering out classes with average scores below 90), and then rank them from high to low?
SELECT
class_id,avg(score)
FROM student
WHERE score > 80
GROUP BY class_id
HAVING avg(score) > 90
ORDER BY avg(score) DESC;

SELECT clause order

 A completed SELECT Statement contains several optional clauses. SELECT The statement is defined as follows:
<SELECT clause> 
[<FROM clause>] 
[<WHERE clause>] 
[<GROUP BY clause>] 
[<HAVING clause>]
[<ORDER BY clause>] 
[<LIMIT clause>]   
 SELECT Clause is required. Other clauses such as WHERE Clause GROUP BY Clauses, etc. are optional.

Complete execution order of SQL Select statement:

1,from Clause assembles data from different data sources; 
2,where Clause filters record rows based on specified criteria; 
3,group by Clause divides the data into multiple groups; 
4,Use aggregation function for calculation; 
5,use having Clause filter grouping; 
6,Calculate all expressions; 
7,select Field of;
8,use order by Sort the result set.

Start - > from clause - > where clause - > group by Clause - > having clause - > order by Clause - > select clause - > limit clause - > final result

Subquery and EXISTS

Foreign key constraint

Foreign key: the key that uniquely identifies a record in other tables, notifies the database of the corresponding relationship between two table fields, and allows the database to help us maintain such a relationship is called a foreign key
Foreign key function: ensure the integrity and consistency of database data
Add foreign key: foreign key references association table (Association column)
Foreign key constraints will lead to low efficiency and inflexibility, so they are generally not used in development, but through code maintenance

Multi table design

One to many: add a column to the multi party and save the primary key of the one party as the foreign key to save the relationship between the two tables
One to one: add columns on either side and save the primary key of the other side as the foreign key to save the relationship between the two tables
Many to many: save the primary keys of two tables as foreign keys in a third-party table to save the relationship between the two tables. You can split the many to many relationship into two one to many relationships

Subqueries: queries nested in other queries

Use subquery as calculation field

-- How to query student number, student name, teacher number and teacher name at the same time?
  SELECT * FROM student;
  SELECT student_id, student_name, teacher_id,
  (
   SELECT teacher_name 
   FROM teacher
   WHERE teacher.teacher_id = student.teacher_id
  )AS teacher_name 
  FROM student;
  -- Disambiguate fields using table aliases
  SELECT student_id, student_name, teacher_id,
  (
   SELECT teacher_name 
   FROM teacher b
   WHERE b.teacher_id = a.teacher_id
  )AS teacher_name 
  FROM student a;

A sub query can only correspond to one record

Filter data using subquery (IN)

-- How to get what students the teacher surnamed Niu taught?
SELECT student_id, student_name 
FROM student
WHERE teacher_id IN (
      SELECT teacher_id 
      FROM teacher
      WHERE teacher_name like 'cattle%' 
);
-- IN This is followed by a collection

Filter data using subqueries (EXISTS)

-- How to get what students the teacher surnamed Niu taught?
SELECT student_id, student_name 
FROM student
WHERE EXISTS(
      SELECT 1 
      FROM teacher
      WHERE student.teacher_id = teacher.teacher_id
      AND teacher.teacher_name like 'cattle%' 
);
-- How to get the students taught by other teachers except the teacher surnamed Niu?
SELECT
student_id,student_name
FROM student a
WHERE NOT EXISTS ( SELECT 1 FROM teacher b
                   WHERE a.teacher_id = b.teacher_id
                   AND b.teacher_name like 'cattle%'
);

EXISTS is used to check whether a subquery will return at least one row of data. The subquery does not actually return any data, but returns the value True or False.

Combined query UNION ALL

Combined query UNION ALL
- Used to merge multiple result sets
- If the query result has duplicate data (duplicate intersection), all the data in the result set will be obtained

Combined query UNION

De duplication of consolidated results

-- Merge result sets from different tables
-- How to query all student numbers, student names, teacher numbers and teacher names at the same time?
SELECT student_id,student_name FROM student
UNION
SELECT teacher_id,teacher_name FROM teacher;

Points to note:

The number of fields in the result set to be merged must be the same.
The title of the merged result set is consistent with the first result set.
Keep the field order, field type category and field value meaning of the result set to be merged as one as possible
To.

UNION ALL mixed with UNION

-- At the same time, query all students aged 10 or class 1, grade 1 (the first two result sets need to remove the duplication) or male (the duplication is not removed when merging)?
SELECT * FROM student WHERE age = 10
UNION
SELECT * FROM student WHERE class_id = 'G0101'
UNION ALL
SELECT * FROM student WHERE gender = 'male';

The execution priority of UNION ALL is the same as that of UNION. Whoever comes first will execute first;
Do not use parentheses to change the execution priority;

Sorting of combined query result set

-- How to query all students aged 10 or class 1 of grade 1 at the same time (sorted by name in ascending order)?
SELECT * FROM student WHERE age = 10
UNION ALL
SELECT * FROM student WHERE class_id = 'G0101'
ORDER BY student_name;

Connection table

On Cartesian product
- Cartesian product: the product of two sets

CROSS JOIN

Use CROSS JOIN to realize the Cartesian product of two sets.

SELECT * FROM student;--3
SELECT * FROM teacher;--4
SELECT * FROM student CROSS JOIN teacher;--12=3*4

INNER JOIN

Example: how to query student number, student name, teacher number and teacher name at the same time?

SELECT * FROM student a INNER JOIN teacher b
ON a.teacher_id = b.teacher_id;

SELECT * FROM student a INNER JOIN teacher b
WHERE a.teacher_id = b.teacher_id;
-- Nonstandard writing, MySQL Unique, not supported by other databases. Not recommended!

SELECT * FROM student a , teacher b
WHERE a.teacher_id = b.teacher_id;
-- INNER Keywords can be omitted
-- Equivalent connection sql92 standard

SELECT 
  a.ename AS empName,
  b.ename AS managerName 
FROM emp a,emp b 
WHERE a.mgr = b.empno;
-- INNER Keywords can be omitted 
-- Equivalent connection sql92 standard

FULL OUTER JOIN

Example: how to query student number, student name, teacher number and teacher name at the same time?

SELECT * FROM  student a FULL OUTER JOIN teacher b
ON a.teacher_id = b.teacher_id;
-- Although the vast majority of big data databases support, but MySQL I won't support it FULL OUTER JOIN. 
-- OUTER Keywords can be omitted

Mysql can use UNION combined with LEFT JOIN and RIGHT JOIN to realize global external connection

LEFT OUTER JOIN

Example: how to query student number, student name, teacher number and teacher name at the same time?

SELECT * FROM student a LEFT OUTER JOIN teacher b
ON a.teacher_id = b.teacher_id; 
-- OUTER Keywords can be omitted

RIGHT OUTER JOIN

Example: how to query student number, student name, teacher number and teacher name at the same time?

SELECT * FROM student a RIGHT OUTER JOIN teacher b
ON a.teacher_id = b.teacher_id; 
-- OUTER Keywords can be omitted

To join a table
- Step 1: Cartesian product of the two tables involved in the connection;
- Step 2: filter the results of Cartesian product according to the connection conditions after ON;
- Step 3: supplement the data in the LEFT JOIN or RIGHT JOIN that does not meet the connection conditions (this step is not available when the INNER JOIN is associated);
- Step 4: filter the results of step 3 according to the filter conditions after WHERE;
Multi table connection: multi table connection is to connect the first table with the following tables in turn and repeat the steps of table connection!
Non unique key connection: when associating tables, the first thing to confirm is whether the association condition field is unique in the association table. In most cases, the association condition field is the primary key in the association table or a field that can uniquely determine a record. If not, it is likely that there is a problem with the association conditions of SQL. You need to carefully confirm whether they meet the requirements.

Keywords: Java Database MySQL SQL

Added by scross on Fri, 24 Dec 2021 19:55:33 +0200

Programming VIP

sql statement summary

Using sql statements to operate the database

Create database

view the database

modify the database

Delete database

Select database

Using sql statements to operate database tables

Create table

Manipulation table

Query data

Filter data

Create a new output field

Use the function to create a new output field

Summary statistics

Subquery and EXISTS

Combined query UNION ALL

Connection table

Popular Keywords