SQL statements must be executed.

SQL statements must be executed.

Structured Query Language (SQL data type)
The information type refers to the format in which the information is stored. There are 10 types of local Interbase information types.

◆ Smallint: it is an integer of 2 bytes (an integer of 16 bits), and its value is-32768 ~ 32767
Use a small integer to save hard disk space and increase the level of information
For example, the metric item goods_no is smallint.
Goods_no smallint
◆ INTEGER: the integer of 4 bytes (the integer of 32 bits), whose value is
-2147483648 ~ 2147483647
Improve smallint's missing location for storing large integer values
If the product quota is greater than 32767, the product quota cannot be expressed as integer in smallint.
Example: goods_no integer
◆ Numeric: You can specify the integer and small number of data items.
The exact p value and the ten-in-one integer of the s size. The exact p value indicates that all values have multiple digits (digits)
Size value. "S" indicates the number of digits after a small number. If this parameter is not specified
P = 5; S = 0
Numeric and decimal can be used to store data in the 190 increment.
For example, numeric [precision [, scale]
If a small number is not specified, the internal value is 0;
If precision (full number) and small number are not specified, numeric will automatically convert it to integer
For example, the employee's salary information is an integer of 15 digits, and the two smaller digits are as follows:
Salary numeric (15, 2)
◆ Decimal: an integer or a small number can be specified for a data item.
Example: decimal [precision [, scale]
If a small number is not specified, the internal value is 0;
If precision (full number) and small number are not specified, numeric will automatically convert it to integer
For example, the employee's salary information is an integer of 15 digits, and the two smaller digits are as follows:
Salary numeric (15, 2)
◆ Double precision: 64-bit precision variable value: 1.7x10-3808 ~ 1.7x10308
◆ CHAR: string type limit with fixed length. The length is limited to 1 ~ RMB 32767
Fixed-length data type. The stored data is any character set used by the system (ASCII and ebcdic ).
Character. The length of the character must be specified when the char type is used.
In this example, the number of rows between two digits is
Dept_no char (3)
When the character data is declared as a fixed duration,
If the incoming data is smaller than the length of the expected data bit, the system automatically uses a blank character;
If the imported data is larger than the data size, the excess data will be automatically deleted.
◆ Varchar: a string that changes the data length. The length is limited to 1 ~ 32765★★★★★★(2017d_6 15_7)
To store the variable length, you must specify the maximum possible length of the bit during the announcement.
The data is smaller than the length of the second digit, and only the characters entered by the second digit are stored.
The country (job_country) of the employee is varchar (15)
Job_country varchar (15)
Character: the char character sets the disk space, but the reading efficiency is high.
Varchar
◆ Date: date type, whose value is Jan 1,100 ~ Dec 11,5941
Date Format information (including year, month, and date)
Employee employment date (hire_date)
Hire_date date default 'now 'not null
Initial Values of default preset bits
Now is a special use of INTERBASE, representing the system date, that is, the hire_date
Initial Value System date;
Not null indicates that when the bitwise adds data, the bitwise value must be specified.

◆ Float: stores floating point data, which is the same as real data (32-bit real data)
The float data type of INTERBASE can contain a maximum of 8 Valid words. When the number of words exceeds the valid value
Operator
Example 1.23456789e + 08
◆ Blob large object information type, which is usually used to store metadata, audio images, and other multi-media resources
Storage of large objects: Data-type audio (image/picture)
Example: Create Table image_product
(Filename char (12) not null primary key,
Memo bolob sub_type 1,
Bitmap bolob sub_type-1,
Exejob_bolob sub_type-2)
Interbase divides the bolob bitwise into several sub-classes (sub_type) and sub-classes
1 indicates the amount of information that is missing, and the entire number of submodels is defined by the user.
-1 indicates the bitwise sequence that can store the vertex metadata.
-2 Data Type buckets that can store images and Images

Create Table)
1. Data Table creation:
Create Table table_name (column1_name data_type [Default data_value]
[Null | not null [,...])
Description: table_type data table. This data table contains one or more buckets of the specified data type.
Default: the initial value of the specified Delimiter is data_value.
Null indicates whether the expected item value is null. When the specified dimension is not null
Reject bytes add NULL data to the specified bit
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 bit (there is no limit except a certain bit .)
LTER table table_name add primary key (column_name)
Description: change the definition of a table to set a role as the primary role.
Alter table table_name drop primary key (column_name)
Description: divide the definition of the main operator.
3. Create an index
Create unique index empno_idx on table_name (emp_no)
Description: index the locations of a table to increase the query speed.
4. Division
Drop table table_name // metadata table
Drop table temp // revoke the temp data table
Drop index index_name // delete an index

Data manipulation language)

Common and major SQL commands:
● Select (select resource, dql)
Data Query
Selcet * from Table // indicates all data records in the past
Selcet name, tel from Table // indicates the name Tel information in the response
Selcet distinct dept_no from table_name // distinct cancel duplicate part
Selcet distinct au_lname from authors where au_lname = "ringer"
Selcet full_name, salsry * 12 from table_name // full name and estimated annual salary
Selcet full_name, salsry * 12 as year_salary from table_name
// In the iso SQL statement standard, the as clause can be used to rename the alias with the alias
Selcet * From table_name where column1 = xxx [and column2> YYY]
[Or column3 <> ZZZ]
------------------------------------------------------------------
Integrated query results:
Selcet count (*) from table_name where column_name = xxx
// Check the information that meets the condition.
Selcet sum (column1) from table_name
Description:
1. Calculate the sum of sums. The selected position must be a digital font.
2. In addition, AVG () is used to calculate the average, max (), and min ().
Comprehensive query.
------------------------------------------------------------------
Combined Query quota
Combined Query refers to the combination of more than one data source, instead of a single table.
The result is displayed in the Response Table.
Selcet * From Table1, Table2 where table1.colum1 = table2.column1
Description:
1. query the information in the two tables with the same column1 value.
2. When the two tables compare with each other, their data structures must be the same.
3. There may be a lot of tables that can be used by a batch query.
------------------------------------------------------------------
Keyword checking for modularity
Selcet * From table_name1 where exists (selcet * from
Table_name2 where conditions)
Description: 1. The conditions of Where can be another query.
2. exists indicates existence and No.
Selcet * From table_name1 where column1 in (selcet column1
From table_name2 where conditions)
Description: 1. In is followed by a set, indicating that column1 exists in the set.
2. The selected dataset must comply with column1.
------------------------------------------------------------------
Other queries
Selcet * From table_name1 where column1 like 'x %'
// Description: Like must correspond to the following 'x % 'to indicate a string starting with X.
Selcet * From table_name1 where column1 in ('xxx', 'yyy ',..)
// Description: The in side 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 program basic information where the name on the where folder is like "% folder % ";
// The resource is not "Xu" in the past
Selcet * from table where name like "* Xu *"
// Submit the information in a specific "Xu"
Selcet full_name, hire_dat, phone_exit from table_name
Where lase_name like 'Le # % 'escape '#';
// Compared with the delimiter, escape checks whether lase_name contains the string of '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 its character can be any string.

// Lase_name not like 'l % 'indicates that the first letter cannot be an L letter.
// Lase_name like 'l % 'indicates that the first letter must start with an L letter and its character can be any string.
// Lase_name like 'l _ 'the last name must contain three characters. The first character must be L.
(The bottom character '_' is similar to the DOS character '? 'Character)
// Lase_name like '% ee %' indicates any string containing 'ee'
// Lase_name like '% E' the last character must be 'E'
------------------------------------------------------------------
Resource Selection
Information 7: One-Stop search
Selcet full_name, hire_date, phone_exit from table_name
Where dept_no = 600 // indicates all employees of the local communications department, such as 600 ....
Selcet full_name, hire_dat, phone_exit from table_name
Where phone_exit is null
// List the names of all employees who have no permission assignment (employees)
Selcet full_name, hire_dat, phone_exit from table_name
Where phone_exit is not null
// List all employee names with the specified phone number. <non-null value>
------------------------------------------------------------------
Multiple search criteria
Selcet full_name, phone_exit from table_name
Where phone_exit is null and hire_date> '20-Jan-1992 ';
// Employee after the date of departure and entry into the company (employee)
<Multi-data table Summary>
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 for information
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;
// The statement is more accurate than the statement used for calculation.
Selcet full_name, job_country from table_name // set merging operator operation (in clause)
Where job_country in ('italy France ') // Italian and French staff
------------------------------------------------------------------
Result of data sorting
Sort data in a single order
Selcet full_name, salary, dept_no from table_name order by dept_no
// The employee name and salary are listed in descending order of each department's representative.
Selcet column1, column2 from table_name order by column2 [DESC]
// Description: order by specifies the sorting by a certain bit. [DESC] indicates the sorting from large to small,
If not specified, it is arranged in ascending order.
Selcet * from Table order by age DESC;
// Year-round reverse sorting
Multi-region sorting data
Selcet full_name, salary, dept_no from table_name order
Dept_no, salary DESC;
// The employee ID is small to large, and the salary is large to small to indicate the names of all employees
------------------------------------------------------------------
Aggregate Function)
AVG: Average Value
Count: Numeric
Min: Minimum value
MAX: Maximum Value
Sum: Sum
Select dept_no count (salary) from table_name // specify
Select dept_no max (salary) from table_name // specify
Select dept_no max (salary) from table_name group by dept_no // positive
<Count Application>
Select count (*) from table_name where dept_no = 100
// Some employees are in part of the Contract 100
<Count (distinct) Application>
Select count (distinct dept_no) from table_name
// The company has several departments
<Count and sum Application>
Select count (*), sum (salsry) from table_name where dept_no = 100
// Number of employees and total salary on behalf of the Department: 100
<Min, Max, AVG Application>
Select min (salsry), max (salsry), AVG (salsry) from table_name
Where dept_no = 100
// The minimum salary, highest salary, and average salary for employees with 100 employees
<Group by clause Application>
Select count (*), min (salsry), max (salsry), sum (salsry)
From table_name group by dept_no
// Find the employees in all departments. The lowest salary is the highest 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 total number of employees in all departments, which is greater than the minimum salary of two employees.
------------------------------------------------------------------
● Insert (new data, mongodml)
Insert into data table name values (values 1, values 2 ,...);
Insert into table_name values ('Taiwan ', 'ntd ');
Insert into table_name (country, currency) values ('Taiwan ', 'ntd ');
// There are two counters in table_name: Country and currency.
Insert into table_name [(column_list)] Select column_list from
Another_table_name ......
// Submit multiple data items to another data table
Insert into table_name1 select * From table_name
// Add all the data in the table_name data table to table_name1
Insert into table values ("Jianzhong", "037-271135", "Miao Zhong Lu", "40 ");
Insert into table_name (column1, column2,...) values (value1, value2 ,...)
Description: 1. If the column sequence is not specified, the system will be filled with data in the order of bytes in the table.
2. the bitwise information must be consistent with the entered information.
3. table_name can also be landscape view_name.
Insert into table_name (column1, column2,...) Select
Columnx, columny,... from another_table
Description: You can also enter the information of other tables in a subquery.

● Update (update data, mongodml)
<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 data table or updatable view name.
// Set the shard name to be updated
// Use the WHERE clause to specify the search_condition in the where clause (which can be omitted ).
The condition conforms to the information updated; the updated bit value must be compatible with the information type specified by the bit
<Update all resources>
Update table_name set salary = salary * 1.05;
// The annual growth rate of employees' wages and resources is 5% RMB
<Update specified information>
Update table_name set salary = salary * 1.1 where dept_no = 100;
// All employees on behalf of Alibaba Cloud increase their salary by 100
<Update multiple locations>
Update table_name set job_grade = 1 = salary = 11000 where emp_no = 2;
// Two employees are expected to increase their salary to 1, and their salary is increased by 110000.
Update table_name set column1 = 'xxx' where conditoins
Description:
1. Change the parameter value to 'xxx '.
2. conditions is the condition to be met. If there is no where, the whole table
All the reserved bits will be changed.
● Delete (optimize data division, optimize DML)
<Distinct the specified item>
Deleting from table_name where dept_no = 621;
// The employee ID is 621 abolished
<Distinct all resources>
Delete from table_name
Delete * From table_name //
Delete from table_name where conditions
Description: remove the required materials.
Description: If the WHERE clause is followed by a date ratio, different data types may vary.
Table types. The following are some examples:
(1) If it is an access data volume, it is: where mydate> #2000-01-01 #
(2) If it is an ORACLE data volume, it is: where mydate> cast ('2017-01-01'
As date) or: Where mydate> to_date ('1970-01-01 ', 'yyyy-mm-dd ')
Compile:
Thedate = '2017-01-01 ';
Query1. SQL. Add ('select * from ABC where
Mydate> cast ('+ ''+ thedate +'' + 'as date )');
If it is a date time type, it is:
Query1. SQL. Add ('select * from ABC
Where mydatetime> to_date ('2017-01-01 10:00:01 ', 'yyyy-mm-dd
Hh24: MI: ss ')