Common mysql statements _ MySQL

(1) Create, delete, and basic query: display the database mysql-showdatabases; Create Database mysql-createdatabasedb; delete database mysql-dropdatabasedb; (1) Create, delete, and most basic query:

Display the database mysql-> show databases;

Create a database mysql-> create database db;

Delete the database mysql-> drop database db;

Choose mysql> use db.

Create a table mysql-> create table mytable (name varchar (20), sex (char (1), birth date );

Delete A table mysql-> drop table mytable;

Displays the table content mysql-> show tables;

Displays the table structure mysql-> describe mytable;

Update:

1. Column Operations:

Add a mysql-> alter table yourtable add name varchar (20) not to a table

Null;

Delete a field mysql-> alter table yourtable drop name;

2. Operations on rows:

Insert a record mysql-> insert into mytable values ('sumer', 'M', '2017-08-24 ');

Delete a record mysql-> delete from mytable where name = 'summer ';

Modify a record mysql-> update mytable set sex = 'vm 'where name = 'summer ';

Insert multiple records mysql-> insert into mytable select * from yourtable ;(

In this form of INSERT statements, the data value of a new row is not explicitly specified in the statement body, but a database query specified in the statement. Logical restrictions of this query:

? The query cannot contain the order by clause .? The query result should contain columns with the same number of columns in the INSERT statement, and the data type must be column-by-column compatible .)

Simple query:

1. display the column name in the query results

A. Use the as Keyword: select name as 'name' from students order by age

B. Direct representation: select name 'name' from students order by age

(2) CURD

(1). query statement:

Select username, uid from supesite. supe_userspaces where catid = '91 ';

Select T1.image from supesite. supe_spaceimages AS T1 inner join supesite. supe_spaceitems AS T2 ON T1.itemid = T2.itemid where T2.username = '". $ username." 'limit 1;

(2) Insert statement:

Insert into cdb_members (username, password) values ('$ username',' $ passwd ');

(3). Update statement:

Update vpopmail. vpopmail set pw_privilege = '1' where pw_name = 'hahaha ';

(4). Modify the table structure statement:

Alter table vpopmail add pw_haha int (10) default null;

Alter table vpopmail drop pw_haha;

Alter table haha add uid int (10) not null auto_increment, add primary key (uid );

(5). Create a table database:

Create table lian (a int, B char (10 ));

Create database jie;

(6). delete database table records:

Drop database jie;

Drop table lian;

Delete from lian where username = 'dd ';

(7) mysql backup

Mysqldump -- all-databases> all_databases. SQL

(8) mysql recovery

Mysql <all_databases. SQL

(9) create a mysql account

Mysql> grant all privileges on *. * to 'lianbinjie '@ 'localhost'

-> Identified by '20140901 ';

Mysql> grant select, update on *. * TO 'monty '@' % '(accessible TO network accounts)

-> Identified by '20140901 ';

(10) change the password of an existing account

Mysql> grant all privileges on *. * to 'lianbinjie '@ 'localhost'

-> Identified by '20140901 ';

Mysql> flush privileges;

Desc tableName; you can query the table structure;

UNION returns two query results and removes duplicates.

SQL> SELECT NAME FROM table1

UNION

Select name from table2;

Union all is the same as UNION, but it does not remove duplicate records.

INTERSECT returns the rows in the two tables. In the following example, it returns the employees in the two tables.

Input:

SQL> SELECT * FROM FOOTBALL

INTERSECT

SELECT * FROM SOFTBALL

The records returned by MINUS exist in the first table but do not exist in the second table. For example:

Input:

SQL> SELECT * FROM FOOTBALL MINUS SELECT * FROM SOFTBALL

Usage of In:

SQL> SELECT * FROM FRIENDS WHERE STATE IN ('CA', 'co', 'La ')

Numbers can also be used in the in statement:

SQL> SELECT *

2 FROM FRIENDS

3 where areacode in (100,381,204)

If you want to find records that match a certain range, for example

Input/output:

SQL> SELECT * FROM PRICE WHERE WHOLESALE 0.25 AND WHOLESALE 0.75

Or, the bewteen BETWEEN operation will include the boundary value.

SQL> SELECT * FROM PRICE WHERE WHOLESALE BETWEEN 0.25 & 0.75