SQL Learning Note (1)

SQL: Structured Query Language, full name is structrued query Language

SQL syntax:

Database tables: A database typically contains one or more tables, each identified by a name, and the table contains records (rows) with data.

SQL statements:

The 1.select:select statement is used to select data from the database, and the results are stored in a result table and become the result set

Select syntax: SELECT column_name, column_name ... From table_name Select the selected column from the TABLE_NAME table

SELECT * FROM TABLE_NAME selects all column data in the table

Example: Select Name,gender from user select name and gender two column data from a table named user

2.SELECT DISTINCT: Used to return only different values, one column in a table may have the same value, and the DISTINCT statement is used to return a unique different value

Select DISTINCT Syntax: Select DISTINCT column_name,column_name ... From table_name

3.WHERE clauses: (Filter rows)

Where clauses are used to filter out records that conform to standards

Syntax: SELECT column_name,column_name from table_name WHERE column_name operator value

Example: SELECT * from user WHERE gender= "male" selects all records in the user table that have sex as male

Text field vs Numeric Field * * *

If it is a text field that needs to be surrounded by single or double quotes, the numeric field does not need

WHERE clause operator:=,<> (not equal to), <, >,between (within a range), like (matches a pattern), in (specifies multiple possible values for a column)

4.AND &or Operator:

The and & or operators are used to filter records based on more than one condition

Syntax: SELECT * from user where age<20 and age>10 Select records with age less than 20 and greater than 10 among all users

SELECT * from user where country= ' China ' or country= ' Japan ' Select a user whose nationality is Chinese or Japanese in all users

5.ORDER by keyword:

The ORDER BY keyword is used to sort the result set by one column or multiple columns.

The order by keyword sorts records by default in ascending order. If you need to sort records in descending order, you can use the DESC keyword

Syntax: SELECT column_name,column_name from table_name ORDER by Column_name,column_name asc| DESC

Example SELECT * from the user order by age to sort all users ' ages

SELECT * The descending order of all user ages from the user order by age

6.INSERT into statement

Insert to inserts a new record into the table

Syntax: 1. No need to specify the column name to insert, simply provide the inserted value to insert INTO table_name values (value1, value2, Value3)

2. Specify the inserted column name INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...)

Example: INSERT into user (name, gender, country) VALUES ("Wang Shuai", ' Male ', ' Chinese ') inserts a new row into the user table

7.UPDATE statements

UPDATE statement for updating records in a table

Syntax: UPDATE table_name SET column1=value1,column2=value2 ... WHERE Some_columns = some_values

The WHERE clause specifies which record or records need to be updated. If you omit the WHERE clause, all the records will be updated!

Example: UPDATE user SET age=24 WHERE name= "Wangshuai"

8.DELETE statements

Delete statement to remove records from a table

Syntax: DELETE from table_name WHERE some_column=some_value

Example: DELETE from user WHERE name= "Wangshuai"

SQL Learning Note (1)