Review of Basic SQL syntax

Convention: Database name: Test; Table name: Tb1,tb2,tb3 ... ；

Objects: Databases: Database tables: Table columns: Column indexes: Index view: View stored procedure: procedure

One, data structure operation

Added: Create Delete: Drop view: Show modify: Alter

Database is generally not modified, database renaming: Rename...to ..., but not recommended, there is a risk of loss of data.

Workaround: When the data volume is small, create a new database and import the data from the original database into the new database.

When the amount of data is large, use the shell script to rename all tables.

Warm tip: The operation is risky, please backup first!

　　　　

Cases:

1. CREATE database test for new databases;

2, select the database use test;

3, check the database to see which databases: Show DATABASES; Displays the currently used database: SELECT database ();

4. Delete database drop test;

5. Add Table tb1 (column_name1 datetype).

6. drop table tb1;

7, modify the table name ALTER TABLE TB1 RENAME to NEW_TB1;

8. Column operations in existing tables added: ALTER TABLE TB1 ADD column_name string Delete: Alter TABLE tb1 DROP column column_name

Modify: Alter TABLE TB1 ALTER COLUMN column_name datatype (sql/ms Access)

ALTER TABLE tb1 MODIFY COLUMN column_name datatype (My sql/oracle)

Second, data operation

Add (Insert): INSERT into (to omit) table_name () Insert data table name (specify field name) (Can not specify field, data is inserted sequentially in column name) VALUES (): Inserted data

Insert table_name () values ();

Modify: Update settings: Set

UPDATE table_name SET column1=value1,column2=value2 where ...

Delete: Delete Deletes a row of data, no where Delete all line error: Delete data is calculated by row, cannot say delete a value in a row of data, if you want to do so, use update to change the value to empty

Delete from table_name where column1=value1;

　　Find: Select...from ... Go: Distinct query condition: where multiple conditional operators: add/or/between/in result set sort: order by ... ASC (Ascending)/desc (descending)

SELECT DISTINCT column_name from table_name where column_name operator value add/or column_name2 operator value2 ORDER by column_name,column_name2 Asc/desc;

　　Cases:

1, insert a piece of data to table 1

Insert TB1 VALUES ("id", "name", "Age", "Code", "Class" ...);

2. Change the school number of the person named Zhang San in table 1 to 001

Update tb1 set code= "001" where Name= ' Zhang San ';

3. Delete data older than 22 in table 1

Delete from tb1 where age>22

4, query table 1, age is equal to 22 years old and the name is called Zhang San students belong to which class and according to the class ascending

Select DISTINCT class from TB1 where age=22 add name= ' Zhang San ' order by class ASC;

Third, the query detailed

1, where conditions commonly used operators:

=,<>,<,>,<=,>=,between And,in (,,), Like,not,add,or

2, like fuzzy query commonly used wildcard characters:

% replaces one or more characters

-Male replaces one character

Any single character of [charlist] word columns

[!charist] or [^charlist] any single character not in Word columns

3. As aliases

Function: Change the result column to the desired name, identify the source of the column with an alias (from which table), and name the function result for the column

4. Functions

Max/min Maximum/Minimum value

Sum total value

Avg Average

Count Row Count

Top returns the first few of the records

GROUP BY group

Having where cannot be used with group by, with having to replace where

5, multi-table query, column name to add table name

Select Tb1.name,tb2.name,tb3.age from Tb1,tb2,tb3;

Review of Basic SQL syntax