MySQL command integration (II)

De duplication of query results

select distinct ... 
// Add the distinct keyword after select

be careful

The distinct keyword can only appear first of all fields

join query

classification

According to grammar

1. SQL92
2. SQL99

According to the connection mode of the table

1. Internal connection: equivalent connection, non equivalent connection, self connection
2. External connection: left external connection, right external connection
3. Full connection

Note: Cartesian product will appear if there is no condition limit in table join query

About table aliases

select e.ename, d.dname from emp e, dept d;

Advantages: high execution efficiency and good readability

Avoid Cartesian product

Adding conditions for filtering, although the Cartesian product phenomenon is avoided, the query times are unchanged

// SQL92 syntax (not recommended)

select
	e.ename, d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

SQL99 syntax

// Clearer structure
select
	Data name 1, Data name 2...
from
	Table name 1
join
	Table name 2
on
	Connection conditions of data in Table 1 and data in Table 2
where
	Other filter conditions

Equivalent connection of inner connection

Features: the condition is equal quantity relationship

// SQL99 (recommended)
select
	e.ename, d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;
	
// inner can be omitted
select
	e.ename, d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;

Non equivalent connection of inner connection

// inner can be omitted
select
	e.ename, e.sal, s.grade
from
	emp e
inner join
	salgrade s
on
	sal between s.losal and s.hisal; 

Self connection

Features: a table can be regarded as two tables, connecting itself

select
	a.ename yuangong, b.ename lingdao
from
	emp a
join
	emp b
on 
	a.mgr = b.empno;

External connection

Difference between internal connection and external connection:

1. Internal connection: there is no primary or secondary connection in the table. All the checked contents can be found accordingly
2. External connection: tables can be divided into primary and secondary tables. After the data in the primary table is found, the secondary table will be checked. If there is no value in the secondary table, NULL will be automatically drawn to match it

External connection classification:

1. Left outer join (left join): indicates that the left table is the main table
2. Right outer connection (right connection): indicates that the table on the right is the main table

// The left connection needs to be added before the join
// The right connection needs to be added before the join
// outer can be omitted
select
	a.ename yuangong, b.ename lingdao
from
	emp a
left outer join
	emp b
on 
	a.mgr = b.empno;

External connection features: all data in the main table can be found unconditionally

Multi table continuous query

...
	A
join
	B
on
	...
join
	C
on
 	...

It means that AB connects the tables first, then AC connects them, and the tables connected first can be seen as a whole

Subquery

Subqueries, that is, nested queries, can be used in select, from, where

Union (add query result sets)

Query statement 1 union Query statement 2

Limit (key)

limit can perform paging queries. limit is unique to MySQL, and Oracle also has a similar mechanism called rownum

limit startIndex, length
starIndex 	Indicates that the starting position is the first data starting from 0
length		Indicates the number of fetched data

Paging display algorithm

pageSize records are displayed on each page:
Page: (pageNo - 1) * pageSize, pageSize
pageSize: how many records are displayed per page
pageNo: what page is displayed

Execution sequence

select			5
	...
from			1
	...
where			2
	...
group by		3
	...
having			4
	...
order by		6
	...
limit			7
	...;

Create table

1. grammar

   create table Table name(
   	Field name 1 data type,
   	Field name 2 data type,
   	....
   );
   

2. data type

   data type	meaning
   int	Integer type (int in java)
   bigint	Long integer (long in java)
   float	Floating point type (float double in java)
   char	Fixed length string
   varchar	Variable length string (StringBuffer/StringBuilder)
   date	Date type (java.sql.Date type)
   BLOB	Binary large objects (storing pictures and videos) (corresponding to objects in java)
   CLOB	Character large object (storing large text) (corresponding to object in java)

Note: when creating a table, t is usually used_ Or tbl_ start

Delete table

1. grammar

   // currency
   drop table Table name;
   
   // oracle does not support this approach
   drop table if exists Table name;
   

Copy table

1. grammar

   create table Table name as select sentence;
   

   Create the queried table

insert data

1. grammar

   insert into Table name(Data name 1, Data name 2...) values(Value 1, Value 2...),(Value 1, Value 2...);
   

   Note: the number of fields is the same as the number of values, and the data type should be the same

   The inserted data can only be modified through update

2. Put the queried table into the existing table

   insert into Table name already exists select sentence;
   

Modify data

1. grammar

   update Table name set Data name 1 = Value 1, Data name 2 = Value 2... where condition;
   

   If there are no conditions, the data of the whole table will be updated

Delete data

1. grammar

   delete from Table name where condition;
   
   // The data table for deleting a large table is truncated and cannot be rolled back
   truncate table Table name;
   

Constraint

1. objective

   When creating a table, you can add corresponding constraints to the fields of the table to ensure the legitimacy, effectiveness and integrity of the data in the table

2. Common constraints

   constraint	meaning
   Non NULL constraint (not null)	The field of the constraint cannot be NULL
   unique constraint	Constrained fields cannot be duplicate
   primary key constraint	The fields of the constraint cannot be NULL or duplicate
   foreign key constraint	(FK for short)
   check constraints	Oracle has a check constraint, MySQL does not

3. use

   create table Table name(
   	Field name 1 data type constraint type,
   	Field name 2 data type,
   	....
   );
   

There is a term for adding, deleting, modifying and querying: CRUD operation
Create Retrieve Update Delete