Mysql efficient programming reading abstract-basics _ MySQL

Mysql efficient programming reading abstract-basic article bitsCN.com

Mysql efficient programming reading abstract-basics

1. join multiple tables

1. inner join-the primary key of the table is connected to the foreign key, and only data with the same key value is retrieved.

Select user. name, order_basic.oid from order_basic inner join user on order_basic.uid = user. uid;

Add the table alias: select u. name, o. oid form order_basic as o inner join user as u on o. uid = u. uid;

// When there are too many domain names and the external name itself is relatively long, you can use aliases.

2. Outer join-use outer join to retrieve data that only exists in one table. It can be divided into left outer join and right outer join.

Select u. name, o. oid from user as u left outer join order_basic as o on u. uid = o. uid;

// Users without any order information will also be retrieved. -- Left outer join

Select u. name, o. oid from user as u right outer join order_basic as o on u. uid p o. uid;

// The user's order information will also be retrieved without use-right outer join

Connecting to which side, which side of the information is more comprehensive !! The same key-value records are extracted from the two tables during the internal connection. when the external connection is performed, all records of one table are retrieved as the benchmark !!

3. connections between multiple tables

Select ob. oid, ob. odate, p. pname, p. price, od. quantity, u. name from

(

(Order_basic as ob inner join order_detail as od on ob. oid = od. oid)

Inner join product as p on od. pid = p. pid

) Inner join user as u on ob. uid = u. uid;

2. search data based on other queries

1. basic subquery

Select * from product where price> (select avg (price) from product );

2. subqueries with multiple return values

Select name, address from user where uid not in (select uid from order_basic where odate = '2017-07-24 ');

// Return the user data that has not been placed on this day

3. subquery and exists operators

Select name, address from user where exists (select * from order_basic where user. uid = order_basic.uid)

// The subquery user performs the subquery action on each record of the basic table. if the data volume of the basic table is too large, the database server will be overloaded, be careful when using

III. table maintenance and transformation

1. alter table command

Alter table... modify column definition

Alter table... add columns

Alter table... change alter column name and definition

Alter table... drop delete column

Alter table visitor modify name varchar (20) --- modify the column type, but it is not recommended to modify the type if the column data already exists.

Alter table visitor add age int; a New age column is added.

Alter table visitor add age int after name add columns anywhere

Alter table visitor add age int first: add a new column at the beginning of the table

Alter table visitor modify age int after name change column position

Alter table visitor change birth birthday DATE modify the column name and type

Alter table visitor drop age delete column

2. copy and delete a table

Create table customH select * from custom; this copy copies the table structure and data at the same time.

Create table customG like custom: only copy the column structure of the table

Insert into customG select * from custom; copy data to the new table [where and limit restrictions can be added to obtain data]

Drop table customG/drop table if exists customG

BitsCN.com