MySQL Database notes

Although the SELECT statement can detect all the data at once, but by the computer screen size limits, each time only to see the content of some rows, to see other content when the use of scroll bar. Due to network limitations, this approach is inefficient for Web applications and is almost impossible to use when the volume of data is large.

In fact, the usual way to do this is based on the size of the screen, only a few lines at a time and display, if you want to see other content, usually in the form of paging.

How do you check the contents of some rows in SQL statements? We're going to use the Limit keyword.

The following statements can be used in Kingbase:

1, query the first line of records:
Select *from Table name limit 1
2, query the nth line to the M record
SELECT * FROM table name offsetn-1 limit m-n;
3, query the first n rows of records
SELECT * from table name limit n;

You can use the following statement in MySQL:

1, query the first line of records:
Select *from Table name limit 1
2, query the nth line to the M record
SELECT * from table name limitn-1,m-n;
3, query the first n rows of records
SELECT * from table name limit n;

The above N and M can be calculated, such as "SELECT COUNT (*) from the table name" first get all the number of data, and then according to each page can display the number of items, to do a simple calculation, you can get the desired results.

Flexible use of the above statement can sometimes play an unexpected effect! For example, to find the number of students with the most elective courses and the number of classes can be used only the following statement can be achieved.

Select study number, COUNT (course number) Select the course number from the student to choose the course. Class by study number of selected courses desc limit 1;

Of course, if you just want to find out the number of students with the most elective courses will be more trouble, with the following statement

Select study number from (select study number, COUNT (course number) The number of courses selected from the Student selection course. GroupBy No.) t order by T. Number of elective courses desc limit 1;

Create a table
Create Tabel table name
CREATE TABLE Customers
(
cust_id int NOT NULL auto_increment,
--cannot be empty, increment (can pay the initial value))
Cust_name char () NOT NULL,
cust_address char () NULL,
cust_city char () NULL,
Cust_state char (5) NULL,
Cust_zip char (TEN) NULL,
Cust_country char () NULL,
Cust_contact char () NULL,
Cust_email char () NULL,

Primary KEY (CUST_ID)
--Specify PRIMARY key
) Engine=innodb;
--Engine

Combining primary keys
Primary KEY (Order_num,order_item)
--Delete Database
Drop database name
------------------------------------------------------------
CREATE TABLE Customers
(
Item_price Decimal (8,2) is not NULL,
Cust_name char () NOT NULL,
Quantity int NOT NULL default 1,
cust_address char () NULL,
cust_city char () NULL,
Cust_state char (5) NULL,
Cust_zip char (TEN) NULL,
Cust_country char () NULL,
Cust_contact char () NULL,
Cust_email char () NULL,
Primary KEY (Cust_id,quantity)
--Specify PRIMARY key
) Engine=innodb;
------------------------------------------------------------------
--Add a list of Vend_phone to the vendors table
ALTER TABLE Vendors
Add Vend_phone char (20);
--Delete the entire table
drop table customers2;
--Renaming a table
Rename table Customers2 to customers;
--Delete Columns
ALTER TABLE Vendors
Drop column Vend_phone;

Methods to modify only the data type of a column:

Can usually be written as ALTER TABLE name modify column column name new type

For example: The type of column sname in the student table is char (20), which is now modified to varchar, the SQL statement is as follows

ALTER TABLE student Modify column sname varchar (20);

Methods for modifying the data types of column names and columns at the same time:

Can usually be written as ALTER TABLE name change column old column name new column type

For example: The type of column sname in the student table is char (20), which is now modified to Stunamevarchar, and the SQL statement is as follows

ALTER TABLE student Change column sname Stunamevarchar (20);
----------------------------------------------------------------------------------------------------
--Insert Row
INSERT INTO customers
(
Cust_name,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
Cust_country,
Cust_contact,
Cust_email
)
Values
(
Null
' Pep E.lapew ',
' Main Street ',
' Los Angeles ',
' Ca ',
' 90046 ',
' USA ',
Null
Null
);
--Insert multiple lines
INSERT INTO customers
(
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
Cust_country
)
Values
(
' Pep E,lapew ',
' Main Street ',
' Los Angeles ',
' Ca ',
' 90046 ',
' USA '
),
Values
(
' M.martian ',
' Galaxy ',
' New York ',
' NY ',
' 11213 ',
' USA '
);
--table inserted into the table
INSERT INTO customers
(
cust_id,
Cust_contact,
Cust_email,
Cust_name,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
Cust_country
)
Select cust_id,
Cust_coutact,
Cust_email,
Cust_name,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
Cust_country
--From Custnew inside
From Custnew;
------------------------------------------------------------------------
Modify rows
Update Customers
Set cust_email = ' [email protected] '
Cust_name = ' The Fudds '
where cust_id = 10005;
Delete value
Update Customers
Set cust_email = null
where cust_id = 10005;
Delete a row
Delete from Customers
where cust_id = 10006;
Modify All Rows

----------------------------------------------------------------------------
--Reduce into priority
Insert Low_priority into
--Reduce the update priority level
Insert Low_priority Update
--Reduce the Delete priority level
Insert Low_priority Delete

This article from "Misty Rain Life" blog, please be sure to keep this source http://1095221645.blog.51cto.com/6507311/1431345