***SELECT***
select *(column name) from table_name(table name) where column_name operator value
ex:(host)
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
stockname like '[^F-M]%' -------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
order by 1,2 --------- byColumn number
stockname = (select stockname from stock_information where stockid = 4)
---------- Sub-query
----------- Unless it is possible to ensure that the inner select only returns the value of one row,
--------- Otherwise, an in qualifier should be used in the outer where clause
select distinct column_name form table_name -------------------------------------------------------------------------------------------------------
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
----------- group by group by group tables by rows, specifying that the column has the same value
having count(*) = 2 ----------------------------------------------------------------------------------------------------------------
select *
from table1, table2
where *= ----------------------------------------------------------------------------------------------------------------------
=* -------------------------------------------------------------------------------------------------------------------------
select stockname from table1
union [all] ----- union merges query result set, all-preserves duplicate rows
select stockname from table2
***insert***
insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
value (select Stockname , Stocknumber from Stock_table2)---value is a select statement
***update***
update table_name set Stockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
***delete***
delete from table_name where Stockid = 3
truncate table_name ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
drop table table_name --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
***alter table*** --- Modify the database table structure
alter table .table_name add column_name char(2) null .....
sp_help table_name --- Show the table has its own features
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ---- Implementation of method of deleting columns (create a new table)
alter table table_name drop constraint Stockname_default --- Delete the default constraint of Stockname
***function(/*common functions*/)***
---Statistical Function ----
AVG – Find the average value
COUNT -Statistics
MAX -- Find the maximum value
MIN -Find the minimum value
SUM -Sum
--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id
--MAX
-- Ask for the name of the employee with the highest salary
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
--STDEV()
--STDEV() function returns the standard deviation of all data in the expression
--STDEVP()
--STDEVP() function returns the population standard deviation
--VAR()
--VAR() function returns the statistical variation number of all values in the expression
--VARP()
--VARP() function returns the total number of mutants
----Arithmetic Functions---
/***Trigonometric Function***/
SIN(float_expression) --Returns the sine of the angle expressed in radians
COS(float_expression) --Returns the cosine of the angle expressed in radians
TAN(float_expression) -- Returns the tangent of the angle expressed in radians
COT(float_expression) --Returns the co-cut of the angle expressed in radians
/***Inverse trigonometric function***/
ASIN(float_expression) --Returns the angle expressed in radians of the sine of the FLOAT value
ACOS(float_expression) --Returns the angle expressed in radians of the cosine of the FLOAT value
ATAN(float_expression) --Returns the angle expressed in radians of the tangent of the FLOAT value
ATAN2(float_expression1,float_expression2)
--Return the tangent is the angle expressed in radians of float_expression1/float_expression2
DEGREES(numeric_expression)
--Convert radians to angles to return the same data type as the expression can be
--INTEGER/MONEY/REAL/FLOAT Type
RADIANS(numeric_expression)--Convert angle to radians to return the same data type as the expression can be
--INTEGER/MONEY/REAL/FLOAT Type
EXP(float_expression) --Returns the exponential value of the expression
LOG(float_expression) --Returns the natural logarithmic value of the expression
LOG10(float_expression)--Returns the logarithmic value of the expression with base 10
SQRT(float_expression) --Returns the square root of the expression
/***Approximate value function***/
CEILING(numeric_expression) --Return >=The minimum integer of the expression returns the same data type as the expression.
--INTEGER/MONEY/REAL/FLOAT Type
FLOOR(numeric_expression) --Returns the minimum integer of <= expression. The data type returned is the same as the expression.
--INTEGER/MONEY/REAL/FLOAT Type
ROUND(numeric_expression) -Returns the data returned with rounded value with integer_expression as the precision
--The same type as the expression can be INTEGER/MONEY/REAL/FLOAT type
ABS(numeric_expression) -- Return the absolute value of the expression The data type returned is the same as the expression.
--INTEGER/MONEY/REAL/FLOAT Type
SIGN(numeric_expression)--The positive and negative sign of the test parameter returns 0, zero value 1, positive number or -1, negative number returns data type
--The same as the expression can be of type INTEGER/MONEY/REAL/FLOAT
PI() --The return value is π, i.e. 3.1415926535897936
RAND([integer_expression]) -- Use optional [integer_expression] as seed to produce a random floating point number between 0-1
---String function ---
ASCII() -- The function returns the ASCII code value of the leftmost character of the character expression
CHAR()--function is used to convert ASCII code into characters
--If the ASCII code value between 0 ~ 255 is not entered, the CHAR function will return a NULL value
LOWER() -- The function converts all strings to lowercase
UPPER() -- The function converts all strings to uppercase
STR() -- The function converts numerical data into character data
LTRIM() -- The function removes the spaces at the head of the string
RTRIM() -- The function removes the space at the end of the string
LEFT(),RIGHT(),SUBSTRING() -- The function returns part of the string
CHARINDEX(),PATINDEX()--function returns the start position of a specified substring in the string.
SOUNDEX() -- The function returns a four-bit character code
--SOUNDEX function can be used to find strings with similar sounds, but the SOUNDEX function only returns 0 for both numbers and Chinese characters.
DIFFERENCE() -- The function returns the difference in the values of two character expressions returned by the SOUNDEX function
--0 The first character of the return value of the two SOUNDEX functions is different
--1 The first character of the return value of the two SOUNDEX functions is the same
--2 The first and second characters of the return value of the two SOUNDEX functions are the same
--3 The first, second and third characters of the return value of the two SOUNDEX functions are the same
--4 The return values of the two SOUNDEX functions are exactly the same
Previous page123Next pageRead the full text