SQL Server (ii) SQL statement fuzzy query NULL processing aggregate function

(ii) SQL statement fuzzy query NULL processing aggregate function

My own study notes, reproduced please indicate the source, thank you!---sauerkraut

SQL: Structured Query Language (structured Query Language), a standard language for relational database management systems.

Sybase and Mircosoft extend the standard SQL: T-SQL (Transact-SQL);

Note: ①sql sensitive to case sensitivity depends on collation, generally not sensitive;

②sql the escape of single quotes, using two single quotation marks to denote a single quotation mark;

③sql Execution Order: 1→2→3→4

SELECT *--------------------------------3

From Users--------------------------1

where userage>20------------2

ORDER BY userage desc----4

1.SQL Statement Creation Database

CREATE Database MyDataBase

2.SQL Statement Delete Database

Drop Database MyDataBase

3.SQL statement when creating a database, set some parameter options

Create DATABASE MyDataBase

On primary

(

---Configure master data file options

Name= ' MyDataBase ',---The logical name of the main data file

Filename= ' c:/mydatabaseone.mdf ',---The actual save path to the master data file

SIZE=5MB,---Primary data file Initial size

MAXSIZE=15MB,---The maximum size of the master data file

filegrowth=20%---The amount of growth of the master data file

---Note: The last option does not end with a comma

)

Log on

(

---Options for configuring log Files

Name= ' Mydatabase_log ',---The logical name of the log file

Filename= ' c:/mydatabase_log.ldf ',---The actual save path to the log file

SIZE=5MB,---The initial size of the log file

filegrowth=20%---How log files are grown

)

4.SQL statements Create/delete tables in the corresponding database

Use database MyDataBase

CREATE TABLE Users

(

UserID int identity (primary key),---Primary key is automatically not empty, so do not use manual NOT null

usercode varchar,----Default Nullable

Usergender varchar (2),

Userage int,

Userdeptid int NOT NULL,

UserName nvarchar (a) NOT NULL---(null: null is not allowed)

---The last column without a comma

)

Use database MyDataBase

drop table Users

5.SQL Statement Inserts data----insert

Insert into Users (usercode,username) VALUES (' 1001 ', ' Tom ')

Insert into Users (' 1002 ', ' Jerry ')

① default cannot insert data into self-increment columns in tables;

② If you insert data into all the columns in the table (except for the self-increment column), you can omit the column names, and you must ensure that the insertion value order and the list column order are consistent;

③ you must insert values into the self-increment column: The function of the "AutoNumber column" of the startup table to manually insert values;

SET Identity_insert Users on

Insert into Users (userid,usercode,username) VALUES (' $ ', ' Bob ')

SET Identity_insert Users off

Note: The self-increment column will then automatically grow at 500, that is, the auto-insert self-increment column value is: 501;

④ The current database collation is not Simplified Chinese, you need to insert the simplified Chinese before adding N;

INSERT into Users values (' 1502 ', N ' Bob Hill ');

6.SQL statement Updates Data----update

Update Users set usercode= ' 1111 ', username= ' NoName '

7.SQL statement----delete/truncate Delete data

Delete from TableName where ... Delete data for the corresponding condition

TRUNCATE TABLE TableName

①delete After you delete data, insert the data from the add-in column without restoring the default, continue numbering. While truncate deletes the data, the self-increment column restores the default value;

②truncate cannot be followed by the where statement, that is, the delete data is not judged by the Where condition;

③truncate Delete Data is more efficient than delete, because delete log more detailed;

④truncate deleting data does not trigger triggers;

8. Constraint----Ensure the integrity of the data

non-null constraint: check whether a null entry is followed by the corresponding column;

① Designer: Check for null entries after the corresponding column;

②t-sql:alter table Users Alter column UserName varchar () not NULL; (To modify columns)

PRIMARY KEY constraint: (PK) PRIMARY KEY constraint is unique and not empty;

① Designer: Right-click the corresponding column flag primary key, and one PK item in the key;

②t-sql:alter table Users Add constraint pk_users_userid primary KEY (UserID);

FOREIGN KEY constraint: (FK) FOREIGN key constraint table relationship; to add to the foreign key table; When adding foreign KEY constraints, set CASCADE Update/CASCADE Delete;

① Designer: Any right click → relationship → add → table and column specifications in the settings;

②t-sql:alter table Users Add constraint fk_users_depts foreign Key (Userdeptid) references depts (deptid);

Unique constraint: (UQ) Unique constraint only allow null, but only one null value;

① Designer: Right click → index/key → Unique key

②t-sql:alter table Users Add constraint uq_users_username unique (UserName);

default constraint: (DF) Default constraint defaults;

① Designer: Select the corresponding column, set default value in the default value/binding in the Column property;

②t-sql:alter table Users Add constraint df_users_usergender default (' Male ') for usergender;

Check constraint: (CK) Check constraint range and format limits;

① Designer: Right-click →check constraint → add: expression (constraint)/name (general rule: ck_tablename_ column name);

②t-sql:alter table Users Add constraint ck_users_usergender check (usergender= ' male ' or usergender= ' female ');

ALTER TABLE Users add constraint ck_users_userage check (userage>=18 and userage<=40);

Delete constraint: ALTER TABLE Users drop constraint Df_users_usergender, ck_users_usergender (comma plus constraint to delete)

bulk Add constraint:ALTER TABLE Users add constraint ck_users_usergender check (usergender= ' male ' or usergender= ' female ')

Constraint ck_users_userage check (userage>=18 and userage<=40)

To add a constraint when creating a table:

CREATE TABLE Depts

(

DeptID int Identity (primary) key,

Deptname varchar () not null unique check (len (deptname) >2),

deptallsum int Check(deptallsum>5 and Deptallsum<20),

deptaddress varchar (+) default(' Zhongguancun, Haidian District, Beijing '),

CompanyID int NOT null foreing key references CompanyInfo (companyid) on delete CASCADE ---cascade delete

)

9.SQL Statement----Modify table structure

Delete a column: ALTER TABLE Users drop column usercode;

Add a column: Alter TABLE Users add (column) usercode varchar (50); (The default is to add columns)

Modify a column: ALTER TABLE Users alter USERCODE varchar (100);

10.distinct keyword: For the entire result set to have been detected to go to the weight, not for a column

11.order by sort

①Select * from the Users order by Userage desc ---descending order

②select * from the Users ORDER by Userage ASC ---Ascending order

③select * from the Users ORDER by Userage (ASC)---is sorted in ascending order by default

④order by must be at the end of the SQL statement;

⑤ Multi-column sort, order by Usercode Desc,userage desc

⑥order by the use of expressions to sort;

⑦order by checking out the ordered content is no longer a set; unordered content is called a set, ordered content is called a cursor, and when the queried data is used by another query, it cannot be used because order by is not a collection.

11.Top keywords: typically used with order by

Select Top 5 * from the User ORDER by userage Desc

Select Top (2*2) * from the User ORDER BY userage desc---Top followed by not a number, is an expression must be enclosed in parentheses, or it will be an error.

Select Top Percent * from the User ORDER BY userage desc---Top takes a percentage of the result set if it is not an integer, rounding up

12. Common Aggregation functions: Max (max)/min (min)/sum (sum)/count (number of bars)/avg (averaging)

Select Max (userage) from Users

Select min (userage) from Users

Select COUNT (*) from Users

Select SUM (age) from Users

Select AVG (age) from Users

Note: The ① aggregate function does not count null values; AVG does not count null values; sum considers null values to be 0;

② aggregation function Statistical grouping before aggregation, no group by the default is to find out the data into a group;

13. Conditional Query

① for in/or query, if the query is a number of consecutive numbers, it is best to use >=/<=/between...and ..., will improve efficiency; between.  and.. In between what. (closed set, containing both values)

② Fuzzy query: for string columns;

Common wildcard characters:

<1>_ (any single character): select * from the Users where UserName like ' Zhang _ '; (with a two-word) ' Zhang __ ' (with a three-word)

<2>% (matches any number of characters): select * from Users where UserName like ' sheet% ';(with an arbitrary length character) (like ' Zhang% ' and Len (UserName) =2 Equal to Like ' Zhang _ ')

<3>[] (Filter range): select * from Users where UserName like ' Zhang [0-9] sister '/' Zhang [A-z] sister '/' Zhang [0-9a-z] sister '; [A-z] The default collation is case-insensitive, so the case can be written out

<4>^ (non): select * from Users where UserName like ' Zhang [^0-9] sister ' (middle not number)/not like ' Zhang [0-9] sister ' (do not take ' Zhang [0-9] sister ' data);

Note:<1> escape character: SELECT * from the Users where UserName like '%[%]% ' with [] escape%

<2> Escape Character: SELECT * from the Users where UserName like '%/[% ' escape '/'; use Escape to specify escape character

<3> like ' a% ' can use indexes; the like '%a '/like '%a% ' can not use index efficiency relatively low;

14.null Value Processing

①sql null cannot be calculated with =/!=;

②is null/is NOT NULL;

③ any value with NULL, the result is also null;

Note: ① common error: Will truncate string or binary data: Insert data length is greater than the length of field settings;

② shortcut key: Open/Close Query Results window: ctrl+r;

③ Current system Time function: GetDate ();

SQL Server (ii) SQL statement fuzzy query NULL processing aggregate function