int Integer The int data type can store integers from -231 (-2147483648) to 231 (2147483 647). Almost all numeric data stored in the database can use this data type. This data type occupies 4 bytes in the database
tinyint integer The tinyint data type can store integers from 0 to 255. It is useful when you only intend to store a limited number of values. This data type occupies 1 byte in the database
float approximate numeric type The float data type is an approximate numeric type used for floating point numbers. Floating point numbers are approximate because not all numbers within their range can be represented exactly. A floating point number can be any number from -1.79E+308 to 1.79E+308
bit integer bit data type is an integer, and its value can only be 0, 1 or null. This data type is used to store data with only two possible values, such as Yes or No, True or False, On or Off
char The character char data type is used to store fixed-length non-digital data of a specified length. Unicoded data. When defining a column of this type, you must specify the column length. This data type is useful when you always know the length of the data you want to store. For example, when you store data in the format of zip code plus 4 characters, you know that 10 characters will always be used. The maximum column width of this data type is 8000 characters
varchar character type The varchar data type, like the char type, is used to store non-uniform encoding character data. Unlike the char type, this data type is variable length. When defining a column of this data type, you specify the maximum length of the column. The biggest difference between it and the char data type is that the length stored is not the column length, but the length of the data.
nchar Unicoded character type The nchar data type is used to store fixed-length Unicoded character data. Unicode uses a double-byte structure to store each character instead of a single byte (as is the case in normal text). It allows a large number of extended characters. This data type can store 4000 characters, doubling the byte space used.
nvarchar Unicode character type The nvarchar data type is used as variable-length Unicode character data. This data type can store 4000 characters, doubling the byte space used.
text character type The text data type is used to store a large amount of non-uniform encoding character data. This data type can have up to 231-1 or 2 billion characters
datetime Datetime type The datetime data type is used to represent dates and times. This data type stores all date and time data from January 1, 1753 to December 31, 9999, accurate to three hundredths of a second or 3.33 milliseconds
Smalldatetime date and time type The smalldatetime data type is used to represent the date and time from January 1, 1900 to June 6, 2079, accurate to one minute
image binary data type image data type is used to store variable length Binary data, up to 231-1 or approximately 2 billion bytes
Basic query
select column1,columns2,...
from table_name
p>
Description: List all the specific column data of table_name
select *
from table_name
where column1 = ***
p>
[and column2 > yyy] [or column3 <> zzz]
Explanation:
1.'*' means all columns are listed.
2. Where is followed by a conditional expression to list the data that meets the conditions.
select column1,column2
from table_name
order by column2 [desc]
Explanation: order by specifies a certain column Sort by bit, [desc] refers to the arrangement from large to small, if not specified, it is arranged from small to large
Arrangement
Combined query
Combined query It means that the source of the queried data is not only a single table, but the result can be obtained by combining more than one
table.
select *
from table1,table2
where table1.colum1=table2.column1
Description:
1. Query the data with the same column1 value in the two tables.
2. Of course, the data format of the fields in the two tables that are compared with each other must be the same.
3. A complex query may use many tables.
Integrated query:
select count (*)
from table_name
where column_name = ***
Instructions:
Query how many data *** there are that meet the conditions.
select sum(column1)
from table_name
Instructions:
1. To calculate the sum, the selected column must be Countable number form.
2. In addition, avg() is an integrated query that calculates the average, max(), and min() to calculate the maximum and minimum values.
select column1,avg(column2)
from table_name
group by column1
having avg(column2) > *** < /p>
Explanation:
1.group by: Taking column1 as a group to calculate the average value of column2 must be together with the keywords of integrated query such as avg and sum
use.
2.having: must be used together with group by as an integration restriction.
Complex query
select *
from table_name1
where exists (
select *
from table_name2
where conditions )
Explanation:
1. The conditions of where can be another query.
2.exists here refers to the existence or absence.
select *
from table_name1
where column1 in (
select column1
from table_name2
where conditions )
Explanation:
1. in is followed by a set, indicating that column1 exists in the set.
2. The data form selected must conform to column1.
Other queries
select *
from table_name1
where column1 like 'x%'
Explanation: like must correspond to the following 'x%' to represent a string starting with x.
select *
from table_name1
where column1 in ('***','yyy',..)
Description : in is followed by a set, indicating that column1 exists in the set.
select *
from table_name1
where column1 between xx and yy
Explanation: between means that the value of column1 is between xx and yy between.
3. Change data:
update table_name
set column1='***'
where conditoins
< p>Instructions:1. Change a field to set its value to '***'.
2.conditions are the conditions to be met. If there is no where, all fields in the entire table will be changed.
4. Delete data:
delete from table_name
where conditions
Description: Delete data that meets the conditions.
Note: Regarding the comparison of dates after the where condition, different databases have different expressions. 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('2000-01-01' as date)
Or: where mydate>to_date('2000-01-01','yyyy-mm-dd')
Write in delphi:
thedate='2000-01-01';
query1.sql.add('select * from abc where mydate>cast('+'' ''+thedate+''''+' as date)');
If comparing date and time types, it is:
where mydatetime>to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss')
4. Add information:
insert into table_name (column1,column2,... )
values ??( value1, value2, ...)
Instructions:
1. If column is not specified, the system will follow the column order in the table Fill in the information.
2. The data form of the field must match 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 go through a sub Subquery fills in data from other tables.