MySQL Union table query, using Phpstudy

I. Meanings and differences of inner coupling, outer coupling, left junction and right Junction
The planned (Join) junction in the SQL standard is broadly divided into the following four types:
1. Inner junction: Those records that have a junction relationship in two tables that match those of the join relationship form the junction of the recordset.
2. Outer coupling: It is divided into the outer left and the outer right junction.
The left junction A, B table means that all the records in table A and the joined fields in table B match the junction field of Table A to the record set formed by those records of the join condition, and note that the final record assembly includes all records of Table A.
The results of the right junction A, B table and the left junction B, A are the same, and the final record assembly includes all records of Table B. Specific as follows:

Select Lqf_user.id,lqf_user.username,lqf_job.id,lqf_job.username from Lqf_user left Join lqf_job on lqf_user.id=lqf_ Job.id

And

Select lqf_user.id,lqf_user.username,lqf_job.id,lqf_job.username from Lqf_user right Join lqf_job on lqf_job.id=lqf_ User.ID;

3. Full junction: Remove all records from the fields that exist in the join relationship in two tables to form the junction of the recordset.
4. No coupling: There is no use of the coupling function, there is also a self-coupling argument.

The difference between inner and outer joins is that the inner join will remove all non-conforming records, while the outer joins retain some of them. The difference between a left and right junction is that if you use a left junction B then all the records in a will remain in the result, and at this point in B only records that meet the junction condition are found, and the right junction is the opposite.

2. Outer left Junction

The result of this query would be to keep a record of all the junction fields in Table A, and if none of the field records in the B table corresponding to them were left blank, the result would be as follows:

Select Lqf_job.id,lqf_job.username from Lqf_user left Join lqf_job on lqf_user.id=lqf_job.id;

3. Outer right Junction

The records have been recorded as standard by Lqf_job.

Select lqf_job.id,lqf_job.username from Lqf_user right Join lqf_job on lqf_user.id=lqf_job.id

Select lqf_job.id,lqf_job.username,lqf_job.jobname from Lqf_user right Join lqf_job on Lqf_user.id=lqf_job.id and lqf_ User.username=lqf_job.username;

Three Some of the parameters used in a table query

1.USING (column_list):
The function is to facilitate the writing of multiple correspondence of the connection, in most cases the using statement can be replaced with an on statement, such as the following example:
Select lqf_job.id,lqf_job.username,lqf_job.jobname from Lqf_user right Join lqf_job using (id,username), which acts as
Select lqf_job.id,lqf_job.username,lqf_job.jobname from Lqf_user right Join lqf_job on Lqf_user.id=lqf_job.id and lqf_ User.username=lqf_job.username;

2.straight_join:
Since MySQL will be read into the left table when the table is joined by default, when this parameter is used, MySQL will read into the right table first, this is a MySQL built-in optimization parameters, you should use in certain circumstances, such as has confirmed that the right table in the number of records, after filtering can greatly improve the query speed.

Select lqf_job.id,lqf_job.username,lqf_job.jobname from Lqf_user straight_join lqf_job on Lqf_user.id=lqf_job.id and Lqf_user.username=lqf_job.username;

Query the full information of the two tables:

Select * from Lqf_user left joins Lqf_job on Lqf_user.id=lqf_job.id;

Other: Basic syntax of the database:

#显示数据库
show databases;

#判断是否存在数据库wpj1105, delete some words first
Drop database if exists student;

#创建数据库
Create database student;

#删除数据库
drop database student;

#使用该数据库
Use class;

#显示数据库中的表
Show tables;

#先判断表是否存在, there is a first delete
drop table if exists student1;

#创建表
CREATE TABLE Student (
ID int auto_increment PRIMARY key,
Name varchar (50),
Sex varchar (20),
Date varchar (50),
Content varchar (100)
) default Charset=utf8;

#删除表
drop table student;

#查看表的结构
describe student; #可以简写为desc student;

#插入数据
INSERT into student values (null, ' dd ', ' Male ', ' 1998-10-2 ', ' ... '), (null, ' BB ', ' female ', ' 1889-03-6 ', ' ... '), (null, ' cc ', ' female ', ' 1899-03-6 ', ' ... '), (null, ' cc ', ' Male ', ' 1894-03-6 ', ' ... ');//Multiple data
INSERT into student values (null, ' AA ', ' female ', ' 1889-03-6 ', ' ... ');//Insert Single data

#查询表中的数据

# and and
SELECT * FROM student where date> ' 1989-1-2 ' and date< ' 1998-12-1 ';

# or OR

SELECT * FROM student where date< ' 1899-11-2 ' or date> ' 1990-12-1 ';

#between

SELECT * from student where date between ' 1890-1-2 ' and ' 1994-12-1 ';

#in query to develop data within a collection
SELECT * from student where ID in (1,3,5);

#排序 ASC Ascending desc Descending

ORDER by statement

The order BY statement is used to sort the result set based on the specified column.

The order BY statement sorts records by default in ascending order.

If you want to sort records in descending order, you can use the DESC keyword.

SELECT * FROM student ORDER by id DESC;

#分组查询 #聚合函数
Select Max (ID), name,sex from student group by sex;

Select min (date) from student;

Select AVG (ID) as ' averaging ' from student;

Select COUNT (*) from student; #统计表中总数

Select count (Sex) from student; #统计表中性别总数 If there is a piece of data in which sex is empty, it is not counted ~

Select SUM (ID) from student;

#查询第i条以后到第j条的数据 (excluding article i)

#修改表的名字
#格式: ALTER TABLE tbl_name Rename to New_name
ALTER TABLE C Rename to A;

#表结构修改
CREATE TABLE Test
(
ID int NOT NULL auto_increment primary key, #设定主键
Name varchar () NOT NULL default ' NoName ', #设定默认值
department_id int NOT NULL,
position_id int NOT NULL,
Unique (department_id,position_id) #设定唯一值
);

#向表中增加一个字段 (column)
#格式: ALTER TABLE student add type_id int;/alter table student Add (type_id int);
ALTER TABLE student add type_id varchar (20);

#修改表中某个字段的名字
ALTER TABLE tablename change columnname newcolumnname type; #修改一个表的字段名
ALTER TABLE student change name uname varchar (50);

SELECT * from student;

#表student Add Column Test
ALTER TABLE student Add (test char (10));

#表student Modify Column Test
ALTER TABLE student Modify Test char (a) not null;
#表student Remove Column Test
ALTER TABLE student drop column test;

MySQL Union table query, using Phpstudy