Basic knowledge of MySQL operations

Incidentally, I will be in the back of the whole set of Css3,php,mysql development Notes pack to Baidu Cloud, there is a need to go directly to the Baidu cloud download, so that after you develop can directly turn the notes without Baidu search so troublesome.

Note Link: Http://pan.baidu.com/s/1qYdQdKK Password: pvj2

MySQL Database operations

When the data table can not successfully output Chinese, garbled, enter: Set names ' GBK ';

First, CREATE Database

Createdata database Db_user;

Second, view the database show databases

Show databases;

Third, select Database

Use Db_user;

Iv. Deleting a database drop databases

Drop Db_user;

MySQL data table operations

Create a table of data tables

Create Table tb_admin<

Id int Auto_increment primary KEY,

User varchar (+) is not NULL,

Password varchar (+) NOT NULL,

Createtime datetime);

Second, view the table structure show columns or describe

Show columns from tb_admin to Db_user;

Desc tb_admin User;

Iii. Modifying the table structure ALTER TABLE

Alter Table Teacher Modify name varchar (+) not null;

Alter Table teacher Modify birthday datetime after name;

Iv. Renaming tables Rename table

Rename table Db_admin to Db_ad;

V. Delete Table drop tables

Drop table db_admin;

MySQL statement operations

Inserting record insert

Insert into Tb_admin (user,password,email,createtime)

->values (' Tsoft ', ' 111 ', ' [email protected] ', ' 2014-1-1 ');

Second, query database record Select

Select *from tb_admin; Querying all data in a data table

Select User,pass from Tb_mrbook; Querying data for user and pass columns in a data table

The B_mrbook.bookname=tb_bookinfo.bookname joins the table Tb_mrbook and Tb_bookinfo, called the equivalent connection.

Select Tb_mrbook.id,tb_mrbook.bookname,author,price from Tb_mrbook,tb_bookinfo

Wheret b_mrbook.bookname=tb_bookinfo.bookname and Tb_bookinfo.bookname= ' PHP development Real-combat treasure ';

1. The query condition applies a WHERE condition statement

Select *from tb_mrbook where type= ' php ';

2.Group by grouping results

Group by words allow you to divide the data into different groups, enabling you to group queries on records.

Select bookname,avg, type from Tb_mrbook group by type;

3.Distinct Remove duplicate rows from the result

You can use the DISTINCT keyword to remove duplicate rows from the result.

Select distinct type from tb_mrbook;

4.Order by sorting results

Use order by to sort the results of a query in ascending or descending order. By default, Roder by prints the results in ascending order, and if you want to sort by descending, you can do so by using Desc.

Select *from tb_mrbook ORDER by ID limit 3; (This is ascending)

Select *from tb_mrbook ORDER BY id desc limit 3; (This is descending)

Note: When sorting columns that have null values in Han, the null values appear first if they are in ascending order, and if they are in descending order, the null value appears at the end.

5.Like Fuzzy Query

Like is a more common comparison operator, which enables fuzzy queries. It has two wildcard characters: "%" and an underscore (_). "%" can match one or more characters, while "_" matches only one character.

Select *from Tb_mrbook where bookname like (' _h% ');

6.Concat Union Multi-column

Use the CONCAT function to combine multiple fields to form a total string.

Select Id,concat (BookName, ":", price) as Info,type from Tb_mrbook;

7.Limit Qualifying Result Row count

The limit sentence can limit the number of records in the query result and control the number of rows it outputs.

Query the Tb_mrbook table, starting with number 1 (that is, the 2nd record) to query 4 records. Select *from tb_mrbook where ID limit 1, 4;

8. Using functions and expressions

In MySQL, you can also use an expression to calculate a fragmented value as the result of the output. An expression can also contain functions.

Select sum (price) as Total,type from Tb_mrbook Group by type;

Calculate the price after the book hits 80 percent: SELECT *, (price*0.8) as ' 80% ' from Tb_mrbook;

Third, change the record update

Set sentence expenditure the columns to be modified and the values they give, where the clause is optional and, if given, which row in the record should be updated, otherwise all record lines will be updated.

Select *from tb_admin where user= ' Tsoft ';

Note: When updating, it is important to ensure that the WHERE clause is correct, and once the WHERE clause is faulted, all changed data will be destroyed.

Iv. Deleting records Delete

If some data has lost meaning or an error occurs, you need to delete it, and you can use the DELETE statement.

Delete from Tb_admin where user= ' Tsoft ';

Attention:

① The statement is executed, if no where condition is specified, all records are deleted, and if a where condition is specified, it is deleted according to the specified criteria.

② in practice, when performing a delete operation, the criteria for performing the deletion should generally be the ID of the data, not the value of a specific field, which avoids unnecessary errors.

Note Link: Http://pan.baidu.com/s/1qYdQdKK Password: pvj2

Basic knowledge of MySQL operations