Part of the knowledge about SQL

Create a user database with SQL This part does not speak, because I have been through the visual interface because there is no technical content (may not have encountered, the shortcomings, please Daniel added). I'm here. Non-visual interface with SQL statements

The exp used in the article is the meaning of the example.

The 1.sql data types are:

character data: char (n), varchar (n), text
Binary data: Binary (n), varbinary (n), image

Integer data: int, smallint, tinyint, bit
4 bytes 2 bytes 1 bytes 1 bits
Floating point data: float, real
8 bytes 4 bytes
Currency data: Money, smallmoney
8 bytes 4 bytes
Date Time Data: datetime, smalldatetime
8 bytes 4 bytes

of course, users can also create their own type , you can use the sp_addtype< type name, < system type > [, <null Description;].

Delete to use Sp_droptype < type name >. That's it.

2. Create a table:

CREATE TABLE [[< database name;.] < master name;] < table name > (< column name 1> < type 1> {not null∣null} [,< column name 2> < type 2>{not null∣null} ...])

Exp:

CREATE TABLE Student
(Sno char (7) Not NULL PRIMARY KEY,
sname varchar () not NULL,
Dept varchar () not NULL,
Birthday datetime NULL,
Tel varchar (+) NULL,
Email varchar () NULL UNIQUE)

3. The concept of the primary key (primary key) appears here:

The primary key is to ensure the integrity of the entity and prevent duplication of data, and the settings of the primary key should be so that the user does not feel, that is, there is no sense of existence of this, then your primary key set is good, the primary key can also be multiple (usually a single).

The general syntax is this: when you create a table above the column after adding primary key or after the column is defined primary KET (here is the name of the column can be multiple) or when using ALTER to modify the table to add

Exp:alter table STUDENT_COURSE1 Add constraint pk_student_course1 primary key (SNO,CNO)

4. External (foreign key): In a table of the primary key, in the other table is built out, of course, the external building is the primary key of the table.

The following features are available for external construction:

(1) If the primary key does not have this value at the time of insertion, it cannot be inserted.

(2) when updating, you cannot change the value that is not in the primary key table.

(3) When you delete a primary key table record, you can either cascade Delete or reject it when you select a foreign key record while the foreign key is being constructed.

(4) when updating a primary key record, there is also a selection of cascading updates and rejected executions.

Exp:

CREATE TABLE Student_course1 (
Sno Char (7) NOT NULL foreign key (SNO) references
Student (Sno) on Delete cascade,//This is the cascade delete
CNO Char (5) NOT NULL foreign key (CNO) references
Course (CNO) on Delete No action,//this is the refusal to delete
Grade decimal (5,0) null check (Grade>=0 and
GRADE<=100)//Here I introduce check, which is used to limit the scope of your column.

5. Modify the contents of the table (this does not mean manipulating the data in the table, but the properties of the table):

Add or Remove Columns (if not a primary key):

ALTER table name ADD column name Type [null][, column name type [NULL] ...] ;//Add
ALTER table name drop column name;//delete

To modify the properties of a column (if not the primary key):

ALTER TABLE name ALTER COLUMN name TYPE [NULL | Not NULL] [, column name type [NULL | Not NULL]] ...) ；

Note: A column to be changed to a non-null value (NOT NULL), requires that the column does not currently contain a null value, if the item is not selected, its default value remains the original value, if a column to change the data type, the column data must be all empty values, otherwise it cannot be changed.

Add primary key:

ALTER TABLE name add constraint pk_ table name primary KEY (this is the name of the column)

To delete a primary key:

This is more troublesome, first of all to delete the constraint, and then delete.

SELECT * from sysobjects where xtype= ' PK ' This is checking the current primary key. ALTER TABLE name drop constraint the constraint name of the primary key is OK.

To delete a table:

drop table name.

6. Building an Index

The benefit of indexing is to speed up the reading of the data, and the downside is that it can add extra physical space, and it takes a lot of time to make changes to the index when the data changes.

To add an index:

CREATE [UNIQUE] {clustered| Nonclustered} Index < indexed name > on < table name > (< column name 1>[,< column name 2>])

To delete an index:

DROP Index < table name >.< index name 1> [,< table name >.< index name 2>]

Exp:create Unique clustered Index kh_ind on course (CNO);

Drop index Course.kh_ind;

7. Update the data in the SQL table

7.1 Insertion of data

First:insert [into] < table name >[(< column name 1>,...,< column name n>)] VALUES (< constant 1>,...,< constant n>)

Second:insert into < table name >[(< column name 1>,...,< column name n>)] Subquery

7.2 Data Updates

UPDATE < table name >set < column name 1>=< expression l> [,< column name 2>=< expression 2> [From < table name 1>[,< table name 2>,...]] [WHERE < conditions;]

Exp:update Student_courseset grade = 0 from Studentwhere dept = ' Computer system ' and Student.sno=student_course.sno

7.3 Data Deletion

DELETE from < table name > [From < table name 1>[,< table name 2>,...]] [WHERE condition]

Exp:delete from student_coursewhere grade is null

7.4 Data Query
SELECT < Field List of projections >
From < table list of participating queries >
[WHERE < query selection criteria >]
[GROUP by < group expression;]
[having< group query conditions;]
[Order by < sort expression > [Asc∣desc]]

Exp:select DISTINCT sname as name, dept as department name,
Year (GETDATE ())-year (birthday) as age
From student

7.5 Other

Introduce distinct this is to go heavy. Also: the wildcard characters for SQL Server are as follows:% represents any number of characters. (underscore) represents a single character. [] represents a single character within a specified range, and can be either Tan Fu or a range of characters. [^] represents a single character that is not within the specified range, and can be either a single character or a range of characters.

Here's an article that will be a good wildcard to look at:

Click to open link

7.6 Integrated

In < value table, not in < value table > using these two predicates

Exp:select Sname,ssex
From Student
WHERE sdept in (' Is ', ' MA ', ' CS ');

7.7 Null value

Is null, was not NULL cannot be replaced by =

7.8 ORDER BY

My words are used to sort the selected set.

Exp:select *
From Student
ORDER by Sdept,sage DESC;

Aggregation functions:

Count
COUNT ([distinct| ALL] *)
COUNT ([distinct| ALL] < column name >)
Calculate sum
SUM ([distinct| ALL] < column name >)
Calculate average
AVG ([distinct| ALL] < column name >)
Maximum minimum value
MAX ([distinct| ALL] < column name >)
MIN ([distinct| ALL] < column name >)

7.9 Group BY

My words are meant to be grouped.

Exp:select s#, AVG (score)
From SC
GROUP by s#

Having the function is to filter the aggregation function to get

Exp:select Sno
From SC
GROUP BY Sno
Having Count (*) >3

8. Some predicates

Any,some,all

Any meaning as long as there is one can be satisfied, some and any almost satisfied on the line, all is all satisfied

Exp:select Sname,sage
From Student
WHERE Sage < any (SELECT Sage
From Student
WHERE sdept= ' CS ')
and sdept <> ' CS '; //This symbol is not equal to the meaning

9. Connect

9.1 Internal connection (i.e. natural connection)

First:select < Field List of projections >
From < table 1> [INNER] JOIN < table 2>
on < table 1. Column 1> = < table 2. Column 2>

Second:select < Field List of projections >
From < table 1>,< table 2>
WHERE < table 1. column 1> =< table 2. Column 2>

It's a two-way grammar, but the effect is the same.

Exp:select s.*
From student S, Student_course SC, course C
WHERE S.sno=sc.sno
and Sc.cno=c.cno
and C.cname= ' Database Principles and Applications '

9.2 External connections (essentially, natural connections are used)

Divided into three kinds of left outer connection, right outer connection, whole outer connection.

The left outer connection is not restricted to the left table in the join condition, and adds the universal row on the right (all of which consist of null values);
The right outer join is not restricted to the right table in the join condition, and adds a universal row on the left (all consist of null values);
The full outer join is unrestricted for the two tables in the join condition, with the addition of a universal row on both sides (consisting entirely of null values), and the rows in all two tables are included in the result set.

The syntax for the

left outer join is:
 select < projected field list;
 FROM  < table 1>  left  JOIN < table 2
                               on  < table 1. Column 1> = < table 2. Column 2> The
Right outer join syntax is:
 select < projected field list,
 FROM  < table 1>  right  JOIN < table 2>
                               on  < table 1. Column 1> = < table 2. column 2>
The syntax for the full outer join is:
 select < projected field list;
 FROM  < table 1>  full  JOIN < table 2>
                              on  < table 1. Column 1> = < table 2. column 2>

EXP (right outer connection): SELECT Sno, Student_course.cno,cname,grade
From Student_course right JOIN course on STUDENT_COURSE.CNO=COURSE.CNO

9.3 Self-connect (connect yourself and yourself)

Exp:select S1.sno, S1.sname,s2.sno,s2.sname
From student S1 JOIN student S2 on
S1.sname=s2.sname
WHERE S1.sno<s2.sno

9.4 Cross-linking (that is, the Cartesian product operation)

First:select < Field List of projections >
From < table 1> Cross JOIN < table 2>

Second:select < Field List of projections >
from < table 1>, < table 2>

Ten exist quantifier

With the presence of quantifiers exists or not exists, if the inner query result is not empty or empty, then the outer WHERE clause returns TRUE, otherwise false values are returned.

Exp:select DISTINCT sname
From student
WHERE EXISTS
(SELECT * from Student_course
WHERE Sno=student.sno and cno= ' 10101 ')

Here is the difference between exist and in, as if the efficiency of exist is greater than in, the same place I feel is almost the use of (forgive my Chinese bad)

11 Merging result Sets

SELECT Statement 1UNION [All]select Statement 2

Note: (1) The columns involved in each query in Union must be consistent on the number, order, and type of columns. (2) The column names in the final result set are from the first query statement. (3) If the Union contains an ORDER BY clause, the final result set is sorted. (4) By default, duplicate rows are deleted in the result set unless the ALL keyword is used.

12. Create a new table when querying

Select < Project field List >into < new table >from < table list of participating queries > [WHERE < query selected conditions >] [GROUP by < group expression >] [having &L t; group query conditions >]
[Order by < sort expression > [Asc∣desc]]
Because the structure of the new table is defined by the field list > that is projected by <, the < projected field list > each column must have a name, and if it is an expression, it should be given an alias.

Part of the knowledge about SQL