SQL Common Statements

SQL Common Statements

First, view the database

1. Show all databases

Command: show databases; (Note: There is a last s)

2. Create a database

Command: Create DATABASE "name";
3. View the currently used database
Command: Select database ();

Second, the table to operate

A , before you create a table, and when you create a table

1, if you need to work on a table of data, you need to first enter this database

Command: Use "database name"

2. Display the data table of the current database

Command: Show tables;

3, Build the table (the field and the field are separated by commas, the last field is not a comma, the field is followed by the property of the field,

Fields ( Field The properties are: Type,null,key,default,extra )

Command: "table name" of CREATE TABLE (
ID int (4) NOT null primary keyauto_increment,
Name Char (a) is not NULL,
Sex int (4) NOT null default ' 0 ',
Degreedouble (16,2)

);

4. View detailed information about the table structure

Describe "table name";

B , create a good table, modify the information of the table

1. Add a new field or column to the table

Command: ALTER TABLE "table_name" Add "new_column_name" "Field property 1, field attribute 2,....";

2. Delete a field or column from a table

Command: ALTER TABLE "table_name" drop "column_name";

3. Modify the fields or columns in the table

Command: ALTER TABLE "table_name" Change "old_column_name" "New_column_name" "Field property 1, field attribute 2,....";

4, modify the name of the table

Command: ALTER TABLE "table_name" rename "New_table_name";

Third, insert data into the created table

1. Insert the complete data into the table (the values of each row are separated by commas, the strings are enclosed in single quotation marks; the lines are separated by parentheses and commas)

Command: INSERT INTO "table_name" values (the value of the first row), (the value of the second row),...... (The value of the last row);

2. Insert some data into the table

Command: INSERT INTO "table_name" (column 1, column 2, ...) Values (the value of the corresponding column in the first row), (the value of the corresponding column in the second row),...... (The value of the last row corresponds to the column);

Iv. viewing data in a table

A , select a simple query

1. View all the data in the table

Command: SELECT * FROM "table_name";

2. View data for a specified field or column in a table

Command: Select "Col1,col2,..." from "table_name";

3. Remove duplicate data from the data you are querying

Command: SELECT DISTINCT "col_name" from "table_name";

B , select Where to query conditions

1 , single condition

Command: SELECT * FROM "table_name" where "col_name" "element character" "Value";

2. Multiple conditions

Command: SELECT * FROM "table_name" where "col_name1" "element character" "Value" "Or/and" "col_name1" "element character" "Value";

3. Attention

Operator is:=,>,<,>=,<=,between,like;

4, when the wildcard character is between

Command: SELECT * FROM "table_name" where "col_name1" between "value 1" and "Value 2";

5, when the wildcard character is like

Command:................

6, when the value of the field is NULL, instead: "col" "Is/is not" "null";

C , select order by for query Lookup (Order in where after the clause)

1.order by lookup

command:SELECT * FROM "table_name" "WHERE clause" ORDER BY "col1" "Asc/desc", "col2" "Asc/desc" ....;

Note: If you do not write ASC or DESC, the default ASC;

limit limits for D,select queries

1 , Limit Statement

command:SELECT * FROM "table_name" "WHERE clause" ORDER BY "offset," "ROWCOUNT";

V. INSERT INTO and select Mixed use

1. Insert Select all the results from one table into another table

Command: INSERT INTO "table_name1" select "Col1,col2,.. "From" table_name2 ";

2. Inserts all the results of a select query from one table into a column specified in another table

Command: INSERT INTO "table_name1" (Col1,col2,... ) Select "Col1,col2,.. "From" table_name2 ";

V. Data modifications in the table

1. Modify individual data

Command: Update "table_name" set "column name" = "xx value" "WHERE clause"

2. Modify multiple data

Command: Update "table_name" set "column name 1" = "xx value", "column Name 2" = "xx value" ... "WHERE clause"

SQL Common Statements