Basics of Database Learning __ Database

     The database is a necessary skill for the programmer, I also started the data study, looked the tutorial is older, is the Haobin sqlsever Basic course, uses the database is the sqlsever2008.

     Talking about the database, before the imitation of the medical project has begun to continue to contact the land, so also to the 3wschool to meet some common SQL command statements. Now is the system of learning again, is to consolidate and strengthen it.

     first talk about my understanding of the database, the database as the name implies is the collection of data, the concrete is composed of a sheet of data, and each data table is independent but can be linked through a variety of relationships, such as foreign key table. Sqlsever is actually the client to operate the library    if you delete the actual library from a library  , it will disappear. The database also has a lot of software, there are mysql,sqlsever and Oracle and so on.

the difference between a database and a data structure.

 * * * database is the data on the hard disk operation is the memory data of the operation * * Database installation is more troublesome, inadvertently system error caused by the database can not run, this time the reload system is more troublesome, so the important thing to say three times, the database installation should be prudent, prudent, prudent.



     There are specific sqlsever2008 installation tutorials on the web, and I don't stress it anymore.
     The next step is to introduce some of the operations of the database.
     1. Database user name additions and modifications can be added through the security of the database.
     2. Database creation directly right-click the database (new database) to create the database will be saved in two files, one is ... mdf is used to store data, and the other is ... ldf is stored operations, such as who who at what time on the database what operations, such as additions and deletions to check.
     3. The database can be added by right-clicking the database and attaching the MDF file. 4. Deletion of the database.
     If the database is deleted at Sqlsever, the actual database will also disappear.
     5. Backup of database. A. The separation method has two methods one is to right click the database to be backed up and then right click on the task click to detach, direct point separation and then the box will be ticked. The MDF and LDF files are generated and are saved in the location of the C disk by default.
     If not modified, the files that are created for the database detach database are stored there.
     B. Generate scripts tick the box of the data that needs to be modified and then all the way to the end of the next 6. A primary key in a table plays an important role in uniquely identifying data items. A primary key is generally independent of the data. Create a column meaningless number int identity represents an increase. One would ask why an int's meaningless number would be used to identify a single data. Then I can only ask you, you say a number to identify a data convenience or a string convenient.
     And generally we will set the primary key is the identity representative of the increase of the primary key, because the irregular number is difficult to remember, nor intuitive. CREATE TABLE student (student_id int primary key identity (100,5), student_name nvarchar not nu ll 

)
STUDENT_ID is the automatic growth of the primary key starting from 100 each time growth of 5
INSERT into student values (' Dick ')
This can be inserted directly because STUDENT_ID is the primary key that can be ignored directly
Deleting data in a table and inserting data can cause primary key discontinuity to increment this is not important, even if the primary key does not increment continuously, it is OK
There are also questions about the difference between a primary key and a unique key.
Primary KEY definition:
A combination of one or more fields that uniquely identifies one thing is called a primary key
1 primary keys are not nullable null values, and unique keys can.
2 primary keys are data outside of meaningless data that is used to identify data, and the unique key is data and is used to prevent data duplication.

     Primary KEY Note: The
     primary key is usually an integer, it is not recommended that
     the value of the string primary key is usually not allowed to be modified, unless this record is deleted the
     primary key does not define as an ID and to define the table name ID or table name _id
     to use the proxy primary key meaning:
     Any table, it is strongly recommended that you do not use a field that has business implications as a primary key we usually have           to add a single integer number in the table to act as the primary key field

There is also a foreign key is also very important, the foreign key is the link between the table and table, if a table has data from B table, that a table called foreign key table, foreign key in a table, B tables for the primary key table. The value of the foreign key table comes from the primary key of table B
A one-to-many relationship is the primary key of a as the foreign key of B, and the foreign key of B can be repeated
Definition of FOREIGN key:
If several of the fields in a table are from a primary key or a unique key from several other tables, then some of the fields are foreign keys
Attention:
A foreign key is usually a primary key from another table rather than a unique key, because a unique key may be null
The foreign key is not necessarily from another table, or it may be from a primary key in this table (such as a table where the employee is the primary key corresponding to the Boss field is the foreign key from its own primary key)

     6, Build Table
1, Menu
2, CREATE TABLE statement
    table name (
        field type [not Null,primary key, Auto_increment]
        ...
    ) 
    Eg:
    create TABLE test (
        ID int primary key auto_increment,
        name varchar NOT NULL,
        sex varchar (2), C12/>tel varchar (),
        remark text
    )

7, delete the table
The drop table table name;
8. Insert statement
1, Menu
2. Insert statement
Insert into table name (column name 1, column name 2 ...) values (value 1, value 2,....)
eg
INSERT into Test (Name,sex,tel,remark) VALUES (' Zhang six bogey ', ' female ', ' 251314 ', ' This is remark ')
3, INSERT statement Note:
A, values and list one by one objects
B, Value and field type matching
C, primary key cannot insert value
9. Query statement
Select Column Name 1, column name 2 from table name where condition
1. Relational operators

< = >= <= <>!
2. Logical operators
And Or not
3, Fuzzy query
Like: About equal to
_: One character
%: Any character
10, delete the statement
Delete from table name where condition
11, modify the statement
Update table name set column name = value WHERE condition
12. Removal of duplication (distinct)
SELECT distinct ress from hist
13.between...and ... Contains
#查询工资在8500到9000之间的员工信息 (two species)
SELECT * from EMP where SAR between 8500 and 9000
SELECT * from emp where sar>=8500 and sar<=9000
#6, in (Matching content list)
#查询部门号是1, 2,3 all employees in these three departments
SELECT * from EMP where did in (1,2,3)
SELECT * from emp where did=1 or did=2 or did=3
#查询主键是1, 4,7 's employee information
SELECT * from EMP where Eid in (1,4,7)
SELECT * from emp where eid=1 or eid=4 or eid=7
#7, aggregate functions (COUNT,SUM,MAX,MIN,AVG)
#1, how many employees are there in total
#select Count (*) from EMP
Select COUNT (Eid) from EMP
#select count (ename) from EMP
Select COUNT (1) from EMP
#select count (900) from EMP
#2, check the sum of all employees ' salaries
Select SUM (SAR) Payroll sum from EMP
#3, check the highest salary
Select Max (SAR) from EMP
#4, check the minimum wage
Select min (SAR) from EMP
#5, check average wage
Select SUM (SAR)/count (Eid) from EMP
Select AVG (SAR) from EMP
#5, check all employees of the highest, lowest, average wage, the sum of wages
Select Max (SAR) max_sar,min (SAR) Min_sar,avg (SAR) avg_sar,sum (SAR) Sum_sar from EMP
#8, grouping statistics
#查询每个部门的平均工资
Select Did,avg (SAR) from EMP Group by did
#查询每个部门的平均工资, only data greater than 7000 is displayed
Select Did,avg (SAR) Avg_sar from EMP Group by did has avg_sar>7000
#查询每个部门的女性员工的平均工资
Select Did,avg (SAR) from EMP where sex= ' m ' GROUP by did
#查询每个部门的平均工资, displays only data greater than 5000 and is sorted by average wage
Select Did,avg (SAR) Avg_sar from EMP Group by did has avg_sar>5000 order by Avg_sar

  
        /* Note: The grouped query is that the fields after the select must be included in the aggregate function or GROUP BY clause
            ----> Filter-----> Sort
            GROUP BY--->having---> ORDER BY/
    *

9. Multi-Table Query

    #1, subquery (another select is nested in one select)
        #查询研发部的所有员工
            #select did from dept where Dname= ' Research Department '
            #select * from EMP where  Did=4 
            SELECT * from emp where did= (
                Select did from dept where Dname= ' Research Department '
            )
        #查询研发部和市场部的所有员工
            SELECT * From the EMP where did
                    in (Select did to dept where Dname in (' Research department ', ' marketing Department ')
            )
            select * from EMP where did C13/>select did from dept where Dname= ' Research department ' or dname= ' marketing Department '
            )
     8.nvarchar () type supports internationalized variable-length characters

9. Primary key naming

The primary key is for a table, not for a field, because the primary key is unique and a table can have only one primary key.

A. General primary key naming: The name of the primary key is Pk_tablename.

B. Compound primary key: "Pk_" + "field name"

e.g.: Constraint pk_studentcourse Primary key (stud_id, cour_id)

Primary key Pk_studentcourse, contains two fields stud_id and cour_id foreign key naming

The foreign Key's name is the table name referenced by the foreign key of the table name that contains the Fk_ foreign key. Because the foreign Key's table is from the table, the upper can be written as FK from the table name _ the main table name.

11.check constraint check is a constraint on the scope of a data item

Create table SS3    Check constraint
(
    name3 nvarchar () NOT NULL constraint PK_SS3 primary KEY,
    cc int check (cc> =10 and Cc<=20)
)
insert into SS3 values (' saz ', 22)

Default is a constraint that adds the defaults

CREATE TABLE Ss4
(
    name nvarchar NOT NULL constraint PK_SS4 primary key,
    sex nchar (1) Default (' Male ')
)

13.
Add a unique key can be a null value but only one column is NULL because NULL is also a value in Sqlsever in Oracle you can have multiple null
Unique can be used with NOT NULL this is a unique key that is not empty

CREATE TABLE Ss5
(
    name nvarchar)  constraint pk_ss5 primary key,  //Give primary key name
    sex nchar (1) Default ' S ',                          //Set the default age
    int Check (age>20),                             //age data set range address
    nvarchar unique NOT NULL               //NULL only value
    xx nvarchar constraint fk_ss2_ss1 foreign key references SS1 (name)                                              //Set FOREIGN key
)
insert INTO SS5 ( name,address) VALUES (' SD ', NULL)

If insert into SS5 (do not fill in) values (all data items are filled in, even if it is set to the default value)
If you insert into SS5 (which items) values (which entries are filled in, the omitted ones are filled in null, if the unique not NULL or the primary key is ignored, the error is not allowed, because neither of these entries allows the control to be filled)

exists return TRUE or False
14. Search for employee information without work experience, request not exists
Select e.* from emp e where NOT EXISTS (
Select 1 from hist h where H.eid=e.eid
)
exists is used for large amounts of data in the small amount of
The difference between 24.Union and union all.
The former goes heavy, the latter does not go heavy

Database contains three kinds of relations: 1. One-to-one
2. A One-to-many
3. Many to many

Let's look at 21 pairs of relationships. This is a foreign key table can express the foreign key table foreign key representation of many and corresponding primary key table primary key is a
Another 3 is the most complex of the three relationships.
A lot of people can't imagine how to explain this relationship.
So you need to create a new table to show their relationship, look at the code at a glance:

CREATE TABLE Banji
(
    banji_id int primary key,
    banji_num int not null,
    banji_name nvarchar
)
CREATE TABLE Jiaoshi
(
    jiaoshi_id int primary key,
    jiaoshi_name nvarchar
)
CREATE table banji_jiaoshi_mapping
(
    banji_id int constraint fk_banji_id foreign key references Banji (banji_id),
    jiaoshi_id int foreign key Referen CES Jiaoshi (jiaoshi_id),
    kecheng nvarchar constraint pk_banji_id_jiaoshi_id
    key (primary, Jiaoshi_id,kecheng)
)

This is the class and the teacher's data table The third is the relationship between the two tables is also the table of the foreign key table.
Class ID and Teacher ID is the foreign key class ID and teacher ID and the course consists of the primary key of the table note: Writing and other primary keys are written differently constraint pk_banji_id_jiaoshi_id primary key (Banji_id,jiaoshi_ Id,kecheng)

A database diagram is also a very important thing to see the relationships between the tables. When you touch a new database you can choose this method to understand the relationship of the data as shown in the figure:

The symbol for the key in the table indicates that the field is a primary key some people ask if a table is not a single primary key why is there three primary keys in the banji__laoshi__mapping? The reason is because these three fields are grouped together as primary keys, so they are all part of the primary key. If deleting a foreign key relationship between a table and a table results in the data loss of the foreign key table, the answer is no. And the table in the diagram and the table is made up of two rings plus the key which is the primary key which is the foreign key. That's what I understand. Banji_laoshi_mapping's key points to banji meaning that some of its data is taken from Banji then it is the foreign key table, Banji is the primary key table so understanding memory is better

Problem:
First delete the primary key table or the foreign key table.
The answer is: first delete the foreign key table
If you delete the primary key table first, the error occurs because it causes the data reference for the foreign key table to fail

    To master the query:

1. Computed column 2.distinct 3.between 4.in 5.top 6.null 7.order by 8. Fuzzy Query 9. Aggregate function 10.group by 11.having 12. Connection Query
Defined
-Classification
Inner joins (most important)
Outer Joins
Full connection
Cross Connect
Self-connected
Joint
13. Nested queries

Attention:
When writing an SQL statement, it is often necessary to query for records that are not on a table, not equal to some values, and is provided in SQL Server without in to implement this functionality, but if the value in the following does not have a null value, an incorrect result is returned, that is, no result returned. So in SQL you need to pay special attention when using not in.

Once the database has been created,
To add a constraint, use the
ALTER TABLE Persons//alter and add are the focus
ADD UNIQUE (id_p)
There's also a new query in which go is a separate action meaning to perform the last step in order to perform the next sequence

There is still some knowledge not how to learn:
1. The transaction
2. Index
3. Stored Procedures
4. Cursors
5.tl_sql
6. Triggers

How the database stores the data
Field record table constraint (primary key foreign key unique key non-null check default trigger)
How a database operates on data
Insert update delete t-SQL stored procedure function trigger
Inquire:
Inner Connection (CORE)
Left and right connection
View
Transaction