©2020-2024 ioDraw All rights reserved
<> one , About database SQL command
<>1. Create database
CREATE DATABASE Database name [ON [FILEGROUP Filegroup name ] ( NAME= Logical name of data file , FILENAME=
' route + Data file name ', SIZE= Initial size of data file , MAXSIZE= Maximum capacity of data file , FILEGROWTH= Data file automatic growth capacity , )] [LOG ON (
NAME= Log file logical name , FILENAME=' route + Log file name ', SIZE= Initial log file size , MAXSIZE= Maximum log file size , FILEGROWTH
= Log file automatic growth capacity , )] [COLLATE Database verification method name ] [FOR ATTACH]
notes ：
1. use [ ] Enclosed statement , Optional or not .
2. If only “CREATE DATABASE Database name ”,DBMS Default logical name , Attributes such as file group size .
<>2. modify the database
ALTER DATABASE Database name //1. Add a data file to the database ADD FILE( Specific file format ) [TO FILEGROUP Filegroup name ]
//2. Add a data file to the database |ADD LOG FILE( Specific file format ) //3. Delete logical file to database , And delete physical files |REMOVE FILE
File logical name //4. Specify the file to modify |MODIFY FILE( Specific file format ) //5. Add filegroups to the database |ADD FILEGROUP Filegroup name
//6. Delete filegroup from database |REMOVE FILEGROUP Filegroup name //7. Modify filegroup name , Or set read-only for filegroups , Reading and writing , Specify filegroup as default filegroup |
MODIFY FILEGROUP Filegroup name { READ_ONLY|READ_WRITE, | DEFAULT, | NAME = New filegroup name } among ,
“ Specific file format ” by : ( NAME = File logical name [,NEWNAME = New file logical name ] [,SIZE = Initial file size ] [,MAXSIZE =
Maximum file size ] [,FILEGROWTH = Automatic file growth capacity ] ) among “|” Indicates radio selection
<>3. Delete database
DROP DATABASE Database name
<>4. query data base
<>4.1 Displaying database structure with system stored procedure
Sp_helpdb [[@dbname=] 'name']
<>4.2 Displaying file information with system stored procedures
Sp_helpfile [[@filename =] 'name']
<>4.3 Display filegroup information with system storage
Sp_helpfilegroup [[@filegroupname =] 'name']
<> two , Data type introduction
<>1. numerical value
<>2. character string
<>3. Time date
<>4. Field properties of the database
<>4.1
UnSigned
Unsigned
Declared that the column cannot be negative
<>4.2
ZEROFILL
0 Filled
Use of insufficient digits 0 To fill , as int(3),5 Is 005
<>4.3
Auto_InCrement
Usually understood as self increasing , Automatically default on the basis of the previous record +1
Usually used to design unique primary keys , Must be an integer type
Start value and step size can be defined
Current table set step (AUTO_INCREMENT=100) : Affect current table only
SET @@auto_increment_increment=5 ; Affect all tables that use autoincrement ( overall situation )
<>4.4
NULL and NOT NULL
Default to NULL , That is, the value of the column is not inserted
If set to NOT NULL , The column must have a value
<>4.5
DEFAULT
default , Used to set default values
<>4.6
expand
<> three , About data table SQL command
<>1. Create data table
CREATE TABLE Table name ( Column name data type Table constraints , . . ) example ： CREATE TABLE S ( SNO VARCHAR(6), SN
NVARCHAR(10), SEX NCHAR(1) DEFAULT ' male ', )
<>2. Constraints on data tables
<>2.1 NULL/NOT NULL constraint
NULL Value is not 0, Not blank , finger “ I don't know ’,” uncertain “ Meaning of ,
NOT NULL Indicates that null values are not allowed .
<>2.2 UNIQUE constraint （ Unique constraint ）
Used to indicate that the value of a basic table must be unique on a column or a combination of columns
establish UNIQUE When constrained , The following factors need to be considered .
1. use UNIQUE Constrained fields are allowed to be NULL value , However, in order to ensure its uniqueness , At most one can appear NULL value .
2. Multiple tables are allowed in a table UNIQUE constraint
3.UNIQUE Constraints are used to force the creation of a UNIQUE Indexes , Default to nonclustered index ,UNIQUE Available for column constraints , Can also be used for table constraints .
<>2.3 PRIMARY KEY constraint （ Primary key constraint ）
Syntax format ：
CONSTRAINT S_Prim PRIMARY KEY （ Column constraint ）
PRIMARY KEY （ Table constraints ）
PRIMARY KEY Constraints are used to define the primary key of the base table , Act as a unique identifier , Its value cannot be empty .
PRIMARY KEY And UNIQUE Constraints similar , Ensure the uniqueness of the value of the basic table in the primary key column by establishing a unique index , But there are big differences between them .
1. Only one can be defined in a basic table PRIMARY KEY constraint , However, multiple can be defined UNIQUE constraint .
2. For specified as PRIMARY KEY One or more columns of the , None of these columns can appear NULL value , And for UNIQUE Constrained unique key , Is allowed to be NULL.
3. Cannot be the same column or group of columns , Existing definitions UNIQUE constraint , Also defined PRIMARY KEY constraint .
<>2.4 FOREIGN KEY constraint （ Foreign key constraint ）
Syntax format ：
NOT NULL FOREIGN KEY REFERENCES Main table name ( Column name )
example ：
example ： CREATE TABLE SC ( SNO VARCHAR(6) NOT NULL FOREIGN KEY REFERENCES S(SNO),
CNO VARCHAR(6) NOT NULL FOREIGN KEY REFERENCES C(CNO), CONSTRAINT S_C_Prim
PRIMARY KEY (SNO,CNO) ) 1. The external key must be a primary key value or a unique value in the primary table , That's why the last line SQL command 2. Secondary primary key value cannot be empty , So there are NOT
NULL
Foreign key constraint ：
Specify one or more columns as external keys , Tables that contain external keys are called slave tables ,
The primary key or unique key referenced by an external key is called the primary table ,
The system ensures that the value on the external key of the secondary table is a primary key value or a unique key in the primary table , Or null value .
<>2.5 CHECK constraint
CHECK Constraints are used to check the allowed range of field values
Syntax format ：
CHECK （ condition ）
example ： Create a SC surface , definition Score The value range of is 0~100 between . CREATE TABLE SC ( SNO VARCHAR(6) CNO VARCHAR
(6) Score NUMERIC(4, 1) CHECK (Score>=0 AND Score<=100) )
establish CHECK constraint , The following factors need to be considered
1. Multiple can be defined in a table CHECK constraint .
2. Only one can be defined per field CHECK constraint .
3. Defined on multiple fields CHECK Constraint must be a table constraint .
4. When executed INSERT, UPDATE Statement time , CHECK Constraints validate data .
<>3. Modify data table
<>3.1 ADD mode
ADD Method is used to add new columns and integrity constraints
Syntax format ： ALTER TABLE Table name ADD Column name data type ....
<>3.2 ALTER mode
ALTER Method is used to modify some columns
ALTER TABLE Table name ALTER COLUMN Column name data type ....
There are some limitations to using this method
1. Cannot change column name
2. The definition of a column with null values cannot be modified to NOT NULL constraint
3. If data already exists in the column , Cannot reduce the width of the column , Also cannot change its data type .
4. Can only be modified NULL/NOT NULL Constraints other types of constraints must be deleted before they can be modified , Then add the modified constraint definition .
<>3.3 DROP mode
DROP Method is only used to delete integrity constraint definitions
Its syntax format is ALTER TABLE< Table name > DROP CONSTRAINT< Constraint name > 【 example 】 delete S Primary key in table . ALTER TABLE S DROP
CONSTRAINTS Prim
<>4. Delete data table
Syntax format ： DROP TABLE Table name
<> four , About data table query SQL command
<>1. Data query of single relation table
<>1.1 Unconditional query
Syntax format ： SELECT Column name FROM Table name //1. * Represents all column names of the table SELECT * FORM S //2. DISTINCT Keyword is used to de duplicate rows
SELECT DISTINCT SNO FROM SC//3. AS Represents an alias , Output alias SELECT SN AS Name,SNO,AGE FROM S
<>1.2 Condition query
Operator meaning
=,>,<,>=,<= ,!=,<> Compare size
AND,OR,NOT Multiple conditions
BETWEEN AND Determine scope
IN Determine set
LIKE Character matching
IS NULL Null value Syntax format ： SELECT Column name FROM Table name WHERE condition
1. Compare size
> SELECT SNO,SCORE > FROM SC WHERE CNO='C1'
2. Multi criteria query Priority from high to low :NOT,AND,OR
example : Query salary in 1000 element ~1500 Teacher number of teachers between Yuan , Name and title . SELECT TNO, TN, Prof FROM T WHERE Sal
BETWEEN1000 AND 1500 Equivalent to SELECT TNO, TN Prof FROM T WHERE Sal>=1000 AND Sal<=
1500
3. Determine set
utilize "IN" Operation can query tuples whose attribute values belong to the specified set
【 example 】 Query elective C1 or C2 Student ID of , Course number and grade . SELECT SNO, CNo, Score FROM SC WHERE CNO IN('C1,C2)
This statement can also use logical operators “OR” realization .SELECT SNO, CNo, Sco FROM SC WHERE CNO='Cl' OR 'CNo' = C2
utilize “NOTN” You can query tuples outside a specified set . 【 example 3-30】 Query no elective C1, No elective C2 Student ID of , Course number and grade . SELECT SNO, CNo,
Score FROM SC WHERE CNo NOT IN ('C1,'C2') Equivalent to SELECT SNO, CNo, Score FROM-SC
WHERE(CNO <>'C1)AND (CNo<>'C2')
4. Partial matching query
Syntax format : < Attribute name > LIKE < string constant >
Wildcard instance
%ab%,’ab‘ Can be followed by any string
_( Underline )‘a_b’,'a’ And ’b’ There can be one character between
[ ][0-9],0~9 Characters between
[^ ][^0-9], be not in 0~9 Characters between 【 example 3-31】 Query the teacher ID and name of all teachers surnamed Zhang . The query results are as follows SELECT TNO, TN FROM T
WHERE TN LIKE' Zhang %' 【 example 3-32】 The second Chinese character in the query name is “ power ” Teacher number and name of . SELECT TNO,TN FROM T WHERE TN
LIKE’_ power %'
6. Null value query
A field without a value is said to have a null value (NUL) Usually when no value is entered for a column , The value of this column is null .
【 example 3—33】 Query the student number and corresponding course number of students without examination results SELECT SNO,CNO FROM SC WHERE SCORE IS NULL
<>1.3 Common library functions and statistical query
Function function
AVG Average by column
SUM Sum of values by column
MAX Find the maximum value in a column
MIN Find the minimum value in a column
COUNT Count the number by column value 【 example 】 Student ID is S1 Total and average scores of students . SELECT SUM(Score) As TotalScore, AVG(Score)
As AvgScore FROM SC WHERE (SNO =S1)
In the above query statement AS hinder TotalScore and AvgScore Is an alias ,
The alias appears in the query results .
Let the instance user clearly know the meaning of the query content ,
Column names are not displayed in the results .
be careful ： When querying with library functions , You usually add an alias to each item of the query ,
Otherwise, the query result will not display the column name .
【 example 3-35】 Ask for elective courses C1 Maximum score for course No , The lowest score and the difference between them . SELECT MAX (Score) AS MaxScore,MIN (Score) AS
MinScore, MAX(Score)- MIN(Score) AS Diff FROM SC WHERE (CNO ='C1') 【 example 3-36
】 Find the total number of computer students .SELECT COUNT( SNO) FROM S WHERE Dept=' computer ' 【 example 3-37】 How many departments are there in the school SELECT
COUNT(DISTINCT Dept) As DeptNum FROM S // Note add keyword DISTINCT After means to eliminate duplicate lines ,
// Computable field “Dept” Number of different values . use COUNT(*) To count the number of tuples .
<>1.4 Group query
<>1.4.1 GROUP BY clause
GROUP BY Clause can group query results in the direction of rows by attribute column or attribute column combination , Each group has the same value on the attribute column or attribute column combination .
【 example 3-40】 Query the teacher number of each teacher and the number of courses they teach . SELECT TNO, COUNT(*)AS C_Num FROM TC GROUP BY TNo
GROUP BY Clause by TNo Value grouping for , All have the same TNo Tuples of are a group , Use functions for each group COUNT Calculate , Count the number of courses each teacher teaches .
<>1.4.2 HAVING clause
If in GROUP BY After the clauses are grouped, they must be filtered according to certain conditions ,
Use HAVING clause .
【 example 3-41】 Query more than two elective courses ( Including two doors ) Student number and number of courses selected . Table connection method SELECT SNO, COUNT(*) AS SC_Num FROM
SC GROUP BYSNO HAVING ( COUNT(*)>=2 ) GROUP BY Clause by SNo Value grouping for , All have the same SNo Tuples of are a group ,
Use functions for each group COUNT Calculate , Count the number of courses each student chooses . HAVING Clause remove unsatisfied COUNT(*)>=2 Group for .
be careful ：
one ,. When in a SQL Simultaneous use in query WHERE clause , GROUP BY Clauses and HAVING Clause , The order is WHERE, GROUP BY,HAVING.
two ,WHERE And HAVING The fundamental difference between clauses lies in the difference of action objects .
1.WHERE Statement works on a basic table or view , Select the tuple that meets the condition ;
2.HAVING Clause to act on a group , Select a group that meets the criteria , Must be used in GROUP BY After Clause , but GROUP BY Clause may not HAVING clause
<>1.5 Sorting of query results
<>1.5.1 Result sorting ORDER BY clause
When sorting query results , Should use ORDER BY clause , ORDER BY Y Clause must appear after other clauses .
Sorting method can be specified ,DESC In descending order ,ASC In ascending order , The default time is that the dog recognizes the person
Technology