SQL Base Operations

the difference between and and or

and : You can use the AND operator under N -More conditions . An action-taking SQL statement, either a transaction or a query, must be True for all detached conditions

Or: You can use the or operator with more than N conditions . The SQL statement is going to take the action, either a transaction or a query, any one or the condition must be true separate

-- wildcard characters

_ matches a single

% multi-character match

-- null value detection

You need to use is null if you need to check if a field is empty when querying , you cannot use =null

Is null or is not null

-- antisense operator

The antisense operator (! =!<!>) is established opposite the synonym operator (= < >)

Not equal to <>

Not much more than <=.

not less than >=

-- multi-value detection

to get information about 23,25,28 years old employees

SELECT * from TableA where age=23 or age=25 or age=28 or

using the in keyword

SELECT * from TableA where age in (23,25,28)

In a constant value when used better if it is a continuous value can use the range query condition

SELECT * from TableA where age>23 and age <28 or

use between and get range this is only after sql2008.

SELECT * from TableA where age between and 28

-- query salary is less than , age between

SELECT * FROM T_employee

where fsalary<5000

and Fage between and 28

-- grouping

GROUP BY

all columns that need to be grouped must be in the list of the GROUP BY clause, and the column that is not in the clause is not placed in the column name table after the select statement.

Grouped query result sets when grouping the situation, so do not represent scattered other data, want to show other data need to use aggregate function

- Number of people of all ages

Select Fage,count (*) as number from t_employee GROUP by Fage

Having clause

For filtering post-grouping conditions

--Check The age of the population greater than 1

Select Fage,count (*) as quantity from t_employee GROUP by Fage have Count (*) >1

Attention:

There is no problem if you use count (*) after having a, but if you use an alias to query the column name will appear invalid prompt because it has not been compiled, you can use a subquery at this time

-- Limit the number of result set rows

Row_number ()

Select Row_number (Order by Fsalary), *from T_employee

-- Suppress data duplication

DISTINCT

-- string concatenation

use + number stitching in MS SQL Server

length calculated field lengths

-- Federated result set

principle: Each result set must have the same hunting,② Each result set column must be type-compatible

Union

-- function

ABS () absolute Value,power () exponent,sqrt () square root,rand () random number, Ceiling ( ) rounding up,floor() down rounding,round () rounded

GetDate () Gets the current time

-- Index

Create INDEX name on table name ( field 1, field 3, field 2)-- Create, index name remains unique

Drop INDEX index name on table name -- Delete index

-- constraints

non-null constraint Not NULL, the unique constraint is unique,

Check constraint: Checks whether the inserted data meets the criteria

PRIMARY KEY constraint, FOREIGN KEY constraint

-- Table Connection

INNER JOIN inner join joins several tables in equivalent

Select Fnumber,fprice from T_order inner joins T_customer on Fcustomerid=t_customer.fid

where T_customer.fname= ' TOM '

No equivalent connection use not equal, greater than, less than, etc. after the on clause

Cross Connect no on clause

Select ... from table name cross join table name

left Link

All records in the left table will be in the anti-theft result set, regardless of whether there are matching records in the right table

-- Query the number of each order, price, the name and age of the corresponding customer

Select O.fnumber,o.fprice,o.fcustomerid,c.fname,c.fage from T_order o

Left JOIN T_customer C

On O.fcustomerid=c.fid

Right Join

Contrary to left join

-- Sub-query

the scalar subquery in the WHERE statement

SELECT * FROM T_readerfavorite

Where Fcategoryid= (

Select FID from t_category where fname= ' stories '

)

In operator vs. subquery

-- Check out reader information for all years

SELECT * from T_reader where Fyearofjoin in (

Select fyearpublished from T_book

)

Any operator vs. subquery

=any equivalent to in operator

<>any equivalent to the not in operator

EXISTS operator : It does not match a column to check if each row matches a subquery and can be considered Exists is used to match whether the subquery result is empty.

Insert into. target Table .. SELECT * from table to check -- import data from one table to another table

-- Index

Clustered Index: The index order is the same as the physical order of the data table,and the phonetic order of the eg dictionary

non-clustered index: Index Sun Xu is different from the physical order of the data table,the radicals of eg dictionary

Index Storage is resource-intensive, so there is a limit to creating indexes, and the index takes B -Tree structure storage, when additions and deletions will generate a lot of storage fragmentation

add field : ALTER TABLE [table name] add [field name] field attribute default default value is optional parameter

Delete field : ALTER TABLE [table name] Drop field name

Modify the size of a variable-length text field : ALTER TABLE [table name] ALTER field name varchar (N)

Delete tables : drop table [table name]

Insert CREATE TABLE

Create Table#famousjaycess2 (JCvarchar( the)DEFAULT "', Occupationvarchar( -)DEFAULT 'ROCK STAT', Becamefamousint default 0, Notestext NULL)GO

according to the following table, write SQL statements; table A, table B 's UserName to correlate find table a There is no record of table B, only one select

The ID field is assumed to be associated if the query results are to be displayed userName then on UserName

1)

SELECT * from A where ID not in (SELECT ID from B)

2)

Select A.* from A left JOIN B on a.id = b.ID WHERE b.id is NULL

SQL Base Operations