(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