Common SQL statements

Create Table)
1. Create a data table:
Create Table table_name (column1_name data_type [Default data_value]
[Null | not null [,...])
Note: The table_type data table contains one or more fields of the specified data type.
Default specifies the initial value of the field data_value
Null indicates whether the data value is allowed to be null. When the specified field is not null, the database system
Reject adding NULL data to this field
For example, create table customer (first_name char (50), last_name char (50 ),
Address char (50), City char (50), country char (25), birth_date date)
2. Change the data table
Alter table table_name add column column_name datatype
Note: Add a field (the syntax of a field is not deleted .)
LTER table table_name add primary key (column_name)
Note: You can change the table definition to set a field as the primary key.
Alter table table_name drop primary key (column_name)
Delete the definition of the primary key.
3. Create an index
Create unique index empno_idx on table_name (emp_no)
Note: index fields in a table to increase the query speed.
4. Delete
Drop table table_name // delete a data table
Drop table temp // Delete the temp data table
Drop index index_name // delete an index

Data manipulation language)

Common and major SQL commands:
● Select (Select data, which belongs to dql)
Data Query
Selcet * from Table // filter all databases
Selcet name, tel from Table // filter display name Tel Information
Selcet distinct dept_no from table_name // cancel duplicate part
Selcet distinct au_lname from authors where au_lname = "ringer"
Selcet full_name, salsry * 12 from table_name // full name and calculated annual salary
Selcet full_name, salsry * 12 as year_salary from table_name
// You can use the as clause to rename the field name in the iso SQL syntax standard.
Selcet * From table_name where column1 = xxx [and column2> YYY]
[Or column3 <> ZZZ]
------------------------------------------------------------------
Integrated Query:
Selcet count (*) from table_name where column_name = xxx
// Query the number of data entries that meet the condition.
Selcet sum (column1) from table_name
Note:
1. Calculate the sum. The selected field must be in the numerical form of a few digits.
2. In addition, AVG () is used to calculate the average, max (), and min () to calculate the maximum and minimum values.
Integrated Query.
------------------------------------------------------------------
Combined Query
Composite query refers to a combination of more than one table instead of a single data source.
The result is displayed in the "tables" table.
Selcet * From Table1, Table2 where table1.colum1 = table2.column1
Note:
1. query the data with the same column1 value in the two tables.
2. Of course, the fields in the two tables must be in the same data format.
3. A complex query may use many tables.
------------------------------------------------------------------
Compound Query
Selcet * From table_name1 where exists (selcet * from
Table_name2 where conditions)
Note: 1. The conditions of Where can be another query.
2. exists indicates whether or not exists.
Selcet * From table_name1 where column1 in (selcet column1
From table_name2 where conditions)
Note: 1. In is followed by a set, indicating that column1 exists in the set.
2. Select data must conform to column1.
------------------------------------------------------------------
Other queries
Selcet * From table_name1 where column1 like 'x %'
// Note: Like must echo 'x % 'to indicate a string starting with X.
Selcet * From table_name1 where column1 in ('xxx', 'yyy ',..)
// Note: In is followed by a set, indicating that column1 exists in the set.
Selcet * From table_name1 where column1 between XX and YY
// Description: Between indicates that the value of column1 is between XX and yy.
Selcet * from basic program data where Yang name like "% Xu % ";
// Filter unspecified "Xu" Data
Selcet * from table where name like "* Xu *"
// Filter data in a specific "Xu"
Selcet full_name, hire_dat, phone_exit from table_name
Where lase_name like 'Le # % 'escape '#';
// Check whether lase_name contains the string 'Le # %
Selcet full_name, hire_dat, phone_exit from table_name
Where lase_name like 'l % ';
// Lase_name like 'l % 'indicates that the first letter must start with an L letter and other strings can be arbitrary.

// Lase_name not like 'l % 'indicates that the first letter cannot be l letters.
// Lase_name like 'l % 'indicates that the first letter must start with an L letter and other strings can be arbitrary.
// Lase_name like 'l _ 'the last name must contain three characters. The first character is L.
(The Bottom Line Character '_' is similar to that in DOS '? 'Character)
// Lase_name like '% ee %' indicates any string containing 'ee'
// Lase_name like '% E' the last character must be 'E'
------------------------------------------------------------------
Data Filtering
Capital 7 raw materials single search
Selcet full_name, hire_date, phone_exit from table_name
Where dept_no = 600 // display all employees whose department code is 600 ....
Selcet full_name, hire_dat, phone_exit from table_name
Where phone_exit is null
// List the names of all employees without extension numbers (employees)
Selcet full_name, hire_dat, phone_exit from table_name
Where phone_exit is not null
// List all employees' names with extension numbers <non-null values>
------------------------------------------------------------------
Multiple data searches
Selcet full_name, phone_exit from table_name
Where phone_exit is null and hire_date> '20-Jan-1992 ';
// After the date of entry into the company (employee) without extension
<Multi-Table link>
Selcet full_name, job_country, currency from table_name,
Table_name1 where job_country = table_name1
Selcet full_name, job_country, currency from table_name,
Left join country on job_country = table_name1
------------------------------------------------------------------
Search data range
Selcet full_name, salary from table_name
Where salary between 100000 and 200000;
// List employees with salaries ranging from 100000 to 200000
Selcet full_name, salary from table_name
Where salary> = 100000 and salary <= 200000;
// Query language for comparison operations
Selcet full_name, job_country from table_name // set Member operation (in clause)
Where job_country in ('italy France ') // Italian and French staff
------------------------------------------------------------------
Data sorting result
Single sort data
Selcet full_name, salary, dept_no from table_name order by dept_no
// List employee names and salaries in the order of Department Codes
Selcet column1, column2 from table_name order by column2 [DESC]
// Description: order by specifies the sorting of a field. [DESC] indicates the sorting from large to small,
If not specified, it is arranged in ascending order.
Selcet * from Table order by age DESC;
// Sort by age column in reverse order
Multi-column sorting data
Selcet full_name, salary, dept_no from table_name order
Dept_no, salary DESC;
// The Department code is small to large, and the salary is large to small. All employee names are output.
------------------------------------------------------------------
Aggregate SQL function)
AVG: Average Value
Count: number of transactions
Min: Minimum value
MAX: Maximum Value
Sum: The sum of values.
Select dept_no count (salary) from table_name // incorrect
Select dept_no max (salary) from table_name // incorrect
Select dept_no max (salary) from table_name group by dept_no // correct
<Count Application>
Select count (*) from table_name where dept_no = 100
// Number of employees in the department with the total code 100
<Count (distinct) Application>
Select count (distinct dept_no) from table_name
// The company has several departments
<Count and sum Applications>
Select count (*), sum (salsry) from table_name where dept_no = 100
// Number of employees and total salary codenamed 100 of the Department
<Min, Max, AVG Applications>
Select min (salsry), max (salsry), AVG (salsry) from table_name
Where dept_no = 100
// Minimum and maximum salaries and average salaries of employees codenamed 100
<Group by clause Application>
Select count (*), min (salsry), max (salsry), sum (salsry)
From table_name group by dept_no
// Find the number of employees in all departments, the lowest salary, the highest salary, and the total salary
<Having clause Application>
Select count (*), min (salsry), max (salsry), sum (salsry)
From table_name group by dept_no having count (dept_no)> 2
// Find out the highest salary for more than two people in all departments
------------------------------------------------------------------
● Insert (new data, DML)
Insert into data table name values (Field 1, Field 2 ,...);
Insert into table_name values ('Taiwan ', 'ntd ');
Insert into table_name (country, currency) values ('Taiwan ', 'ntd ');
// Table_name has two fields: Country and currency.
Insert into table_name [(column_list)] Select column_list from
Another_table_name ......
// Copy multiple pieces of data to another data table
Insert into table_name1 select * From table_name
// Add all data in the table_name data table to table_name1
Insert into table values ("Chen Jianzhong", "037-271135", "Miao Zhong Lu", "40 ");
Insert into table_name (column1, column2,...) values (value1, value2 ,...)
Note: 1. If column is not specified, the system will fill in the data in the order of fields in the table.
2. The data form of the field must be consistent with the data filled in.
3. table_name can also be landscape view_name.
Insert into table_name (column1, column2,...) Select
Columnx, columny,... from another_table
Note: You can also enter data in other tables through a subquery.

● Update (update data, which belongs to DML)
<Allow updating existing data tables>
Update table_name set coumn_name1 = data_value1 [, coumn_name2
= Data_value2,...] Where search_condition]
// Table_name must be the name of the data table or updatable View
// Set the field name to be updated
// Use the WHERE clause to specify the search_condition in the where clause with a greater condition (which can be omitted)
The updated field value must be compatible with the data defined by the field.
<Update all data>
Update table_name set salary = salary * 1.05;
// The employee's salary is adjusted by the annual increase rate of 5%.
<Update specified data>
Update table_name set salary = salary * 1.1 where dept_no = 100;
// The salary of employees with codes 100 in all departments is increased by 1.1
<Update multiple fields>
Update table_name set job_grade = 1 = salary = 11000 where emp_no = 2;
// Employee No. 2 is promoted to 1 at Level 2, and his salary is adjusted to 110000
Update table_name set column1 = 'xxx' where conditoins
Note:
1. Change the value of a field to 'xxx '.
2. conditions is the condition to be met. If there is no where, the entire table
All fields are changed.
● Delete (delete data, which belongs to DML)
<Delete specified data>
Deleting from table_name where dept_no = 621;
// Department Code 621 abolished
<Delete all data>
Delete from table_name
Delete * From table_name // incorrect
Delete from table_name where conditions
Note: delete qualified materials.
Note: If the where condition is followed by a date comparison, different databases have different
Expression. The details are as follows:
(1) If it is an Access database, it is: where mydate> #2000-01-01 #
(2) If it is an Oracle database, it is: where mydate> cast ('2017-01-01'
As date) or: Where mydate> to_date ('1970-01-01 ', 'yyyy-mm-dd ')
Write in Delphi:
Thedate = '2017-01-01 ';
Query1. SQL. Add ('select * from ABC where
Mydate> cast ('+ thedate +' + 'as date )');
If the datetime type is compared:
Query1. SQL. Add ('select * from ABC
Where mydatetime> to_date ('2017-01-01 10:00:01 ', 'yyyy-mm-dd
Hh24: MI: ss ')