7-24 Basic operations of the database

1. Delete operation

DELETE from users WHERE id = 6;

#删除数据表users中id =6 of the line

#注意, if the ID is the primary key self-increment, then the data is inserted with the ID value of the largest ID value +1, rather than supplementing the intermediate deleted id=6

2. View table structure

SELECT * from users;

#查看所有的行

3. View all Columns

SHOW COLUMNS from users;

#查看数据表users中的所有列

4. View the specified column

SELECT username, id from users;

　　

SELECT User.id,user.username from users;

#查看数据表users中的列username和id

#第二种方法适用于多表连接的情况

5. Assigning aliases

SELECT ID as userid,username as uname from users;

#查看对象的同时为其赋予别名

6.WHERE conditions

Whrer statement can be in a lot of additions and deletions of the place to do the operation

7.GROUP by statement

Poll Group Results

SELECT sex from the users GROUP by sex;

#根据sex的值进行分组显示, the result shows only the different values that the sex column has, namely 0,1,null

Use having to add grouping conditions

SELECT sex from the Users GROUP by 1 have count (ID) >2;

#结果显示为sex的情况, condition is count (ID) >2

8.ORDER by sorting the results of the query

#显示结果, sorted by ID Descending

9. Limit the number of query results returned

SELECT * from  the users ORDER by ID DESC LIMIT 2, 2;

The columns in the #显示数据表users, sorted in descending order of ID, and show only id=3 and the total of 2 rows of data, that is, the id=3,4 data value

10. Selective assignment between data tables

INSERT Test (username) SELECT username from the users WHERE age >=30;

#向数据表test中插入users表中的部分行, the users row needs to be satisfied by the,age>=30;

7-24 Basic operations of the database