SQL Advanced Operations

1.UNION operator

　The union operator is used to merge the results of two or more SELECT statements, and it is important to note that each SELECT statement inside the Union must have the same number of columns, and that the columns must have similar data types and the same column order. Here is a small example of my data sheet.

That's now a joint operation of the data table, where we're combining the bookname of the two tables.

Select  from Myrank Union Select  from MyBook;

As you can see from the results bookname This column shows the combined data of the two tables, and if you look closely you will see that there should be duplicate data, and the results do not show that the duplicate data is filtered out. If you want to display duplicate data, you can add the ALL keyword after union.

Select  from Myrank Union  All Select  from MyBook;

2.select into and insert into select operator

　　With the SELECT INTO statement, we can copy information from one table to another, and the newly created table will have the same column name as the original table.

-- Copy the data from the MyBook table to the Mybookcopy table, and the Mybookcopy table does not need to be created, and the Mybookcopy table is created automatically after executing this statement Select *  into  from MyBook; -- copy columns from multiple tables into a new table Select  into  from Join  on Mybook.userid=Myuser.userid

The INSERT INTO SELECT statement copies the data from one table and then inserts it into another existing table, and the rows in the target table are unaffected.

Insert into Myrank (userId) select UserID from MyBook

3. Constraints

Constraints are used to specify the data rules in the table, and if there is a violation of the data behavior of the constraint, the behavior will be constrained to terminate. The following six constraints are available in SQL:

Not NULL: This should be familiar, just can't be empty

Unique: guarantees that each row of a column must have a unique value

PRIMARY key: The primary key, which can be understood as a combination of not null and unique, ensures that a column of this table has a unique identifier

FOREIGN key: Foreign key, which identifies this column as the primary key of another table, to guarantee referential integrity between the two tables

CHECK: Create a range of values in a column to guarantee that the values in the column meet the specified criteria

Default: Defaults for the specified column

Next is the SQL statement for these 6 operations, with explanations for each statement.

--A non-empty constraint is added when a table is created with a NOT NULL keyword after the columnCreate TableMyTable (MyIDint  not NULL, MyNamenvarchar( +) not NULL)--A unique constraint is added when you create a table with the unique keyword after the columnCreate TableMytable2 (MyIDint  not NULL UNIQUE, MyNamenvarchar( +) not NULL)--when the table is created, you can use alter to add a Uniqueness constraintAlter TableMytable2Add constraintNameuniqueUnique(MyName)--use ALTER and drop to remove uniqueness constraintsAlter TableMytable2DropNameunique--Mytable2 deleted and re-created and set myID as the primary keyCreate TableMytable2 (MyIDint  not NULL, MyNamenvarchar( +) not NULL,constraintMyprimaryPrimary Key(MyID))--and in this place, I also found that when the data table is deleted, if you immediately create a new name and field, the table will display the table just deleted, the original data is still there, just add the primary key--Delete primary KeyAlter TableMytable2Dropmyprimary--New PRIMARY KeyAlter TableMytable2Add constraintMyprimaryPrimary Key(Myid,myname)--first remove the primary key from the mytable2 above, create the Mytable3, and set the myname as the primary keyCreate TableMytable3 (MyNamenvarchar( +) not NULL PRIMARY KEY, Ageint  not NULL)--then set the Mytable2 myname as the foreign key, delete the same way as above, but also using the Drop keywordAlter TableMytable2Add constraintMyforeignForeign Key(MyName)Referencesmytable3 (myName)--Add a constraint when creating a table with the Check keyword followed by a columnCreate TableMytable3 (MyIDint  not NULL Check(MyID>0), MyNamenvarchar( +) not NULL)--New Check ConstraintAlter TableMytable3Add constraintMyCheckCheck(MyName!='hh')--Delete a check constraintAlter TableMytable3DropMyCheck--when creating a table, add the default keyword after the column, but this place SQL2008 not good enough, add a row of data if not filled myname it will show null, close and then view the FyzCreate TableMytable3 (MyIDint  not NULL Check(MyID>0), MyNamenvarchar( +)default'Fyz')--Delete the default constraintAlter TableMytable3Drop constraintdf__mytable3__myname__2b3f6f97;--new DEFAULT constraintAlter TableMytable3Add default'HHH'  forMyName;

Last Default constraint I've been around for almost a whole morning. Originally I was in accordance with the rookie tutorial written, the results found that it is not possible, Baidu to the world wide and found that rookie is actually replicating the world wide, and Baidu basically the first two pages are all those who copy rookie or no specific examples. Well, first of all, I'm going to delete the default constraint, I've given the constraint a name before, and you can delete the constraint directly. In the default constraint here I was directly in the creation of the table, the use of the rookie on the drop default can not, and finally know if I create a table when the constraint will have a DEFAULT constraint, the database system will automatically give this constraint named, you can use the following method to get the name of the constraint:

SelectC.name fromsysconstraints aInner Joinsyscolumns b onA.colid=B.colidInner Joinsysobjects C onA.constid=c.idwherea.ID=object_id('Mytable3')  andB.name='MyName'

get the name before you delete it. The second problem is that I want to modify the constraint, and finally I know on the Microsoft Official website that if you want to modify the default constraint, you must first delete the existing constraint and then set the new constraint . I don't know if it's a version issue, rookie on the alter TABLE Persons ALTER COLUMN city DROP DEFAULT, but it doesn't work on my SQL 2008, Finally see a predecessor use Add to add success.

4.create index, auto increment, and null operations

When data tables have a lot of data, we can use the index to find data faster without reading the entire table. We can create an index in a table that is not visible and can only take advantage of accelerated search/query. But using indexes also has drawbacks, updating a table that contains an index takes more time than updating a table with no indexes, because the index itself needs to be updated. Because the index has this disadvantage, it is generally preferable to use the index only on columns that are often searched. After I hit the index on the tutorial and found that I didn't say how to use the index, I would go into the index after I hit the example on the tutorial. Auto Increment can be seen as a self-increment from the name, which guarantees that a unique number is generated when the new record is inserted into the table.

--to create an index, you can use duplicate valuesCreate IndexMyidindex onMytable3 (myId);--create does not allow duplicate values, an index can only correspond to this unique rowCreate Unique IndexMyuniqueindex onMytable3 (myId);--use drop to delete indexes, tables, databases, etc.Drop IndexMyidindex onMytable3;Drop IndexMytable3.myuniqueindex;--Tip: If we need to clear the table data without deleting the tables themselvestruncate TableMyrank;--ALTER TABLE can be used to add, delete, and modify columns in an existing tableAlter TableMyBookAddBookothernvarchar( +);Alter TableMyBookAlter columnBookothernvarchar( -);Alter TableMyBookDrop columnBookother;--set the Auto-increment primary key field, the identity is the key to set the primary key, the first 1 means starting from 1, the second 1 means that each record is incremented by 1Create Tablemytable4 (MyIdint Identity(1,1)Primary Key, MyNamenvarchar( -))

Null represents unknown data, in SQL we cannot use "=" to determine whether it is null and must be judged by using is null and is not NULL. In addition, sometimes we need to perform arithmetic operations on a column of data, but some of the data in this column is still null, so the operation cannot be completed. Since there is a need, there must be a solution, and SQL has a IsNull function that sets the value of a null column to a default value. Here is the SQL statement:

Insert  intoMytable4Values('hh');Insert  intoMytable4 (myname)Values(NULL);--find rows that are empty and not emptySelect *  fromMytable4whereMyName is NULL;Select *  fromMytable4whereMyName is  not NULL;--The isnull () function so that if the myname of a row is null, the output is FyzSelectMyId,ISNULL(MyName,'Fyz') asMyaname fromMytable4;

SQL Advanced Operations