2015.7.30 15th Lesson SQL (new database, create TABLE, comment, query statement, new, UPDATE, delete, union query)

1. Understand the database and create new:

1) Open the database and connect to the server.

2) service type without tube.

3) server name: Make a point "." Indicates that the server is on the local computer and, if it is hosted on someone else's server, enter the server IP address.

4) Authentication: If you are Windows authentication, you need administrator privileges. Generally hosted on someone else's server, (including normally), is SQL authentication, you need to enter the SA and password (password is the password to be entered during installation).

2. Establish the database:

When you create a database name (in various names), you follow a naming convention:

1) name in English

2) Capitalize the first letter of each word (Hump principle: Capitalize first letter)

such as: studentinfor (Student information Form)

3. New table:

Right click, name (follow the naming rules).

4. Build field (data):

In the data, each row needs a unique identity, which is the primary key. The primary key is usually called the ID.

Primary KEY = Identity =xxxid

Primary key: The unique identifier for this row of data, which is the ID we set.

1) How to set the primary key:

Right-click the black triangle in front of the item you want to set as the primary key--set the primary key

At this point we can set the primary key we need as the self-increment type.

The self-increment type is: When we insert data into this table, its value is automatically +1. (1, 2, 3: will automatically +1 downward row, 1, 2, 3, 4, 5, 6 ... ）

2) Self-increment type setting method:

5. Data type: when setting up data, be aware of the data type.

Such as:

1) Student ID, data type is shaping (int), is a number.

2) Student name, you need to set the string (nvarchar (10) to use the most)

Data type:

• Char, example: UserName char (//char) (20), field length is 20 characters/10 kanji, if only one byte, followed by nine spaces (a Chinese two bytes)
• NCHAR, example: Usertelphone nchar (+)//nchar (30), set 15 Chinese, fixed length
• varchar, example: useraddress varchar (10) Put five Chinese, grow longer, if only one byte, just put one byte
• Nvarchar, example: Userphoto nvarchar (max), variable length, can hold photos, files, etc.
• int, integer field. Example: Userage int
• float, floating-point data is approximate. Example: Userresults float, the approximate numeric data type used to represent floating-point numeric data.
• datetime, Date type. Example: Createdatetime datetime
• Bit, Boolean type. Can be set to True is male, false is schoolgirl

If the content is very much, indeterminate length (such as article), can be used: nvarchar (max)

6. Save the table:

* If you want to modify the table, save it, and then refresh it.

Hon

Q: If the table identifies 1, 2, 3, 4 ..., I delete 2, let 3, 4 automatically become 2, 3 OK?

A: Don't do this, the table in our project is associated with other tables! If you delete the 2 here, it will make the other associated table error. So the first set up, do not delete the automatic completion, it is best not to delete the logo.

7. Build a database or table with code:

New query--write--sql statements in the Code Editor (case insensitive)

1) Use the following statement to create a database called the library:

Create DATABASE Library

Go

Use Library

Go

2) Use the following statement to create a table for users, and then select the corresponding database when you set it up:

CREATE TABLE Users

(

UID int PRIMARY KEY,

UserName nvarchar () NOT NULL,

Userpwd nvarchar () NOT NULL

)

8. Delete the database:

If you right-click to delete the database, you will be prompted with an error, "Database is in use."

So you need to restart the service and then delete it.

* Restart Service method:

Control Panel--management tool--service--Find the corresponding service restart below. (or SQL Configuration Manager is also available)

In peacetime, if the database is hung, you need to restart the service.

9, add data:(generally do not use manual way, but write a program to read data)

manually add Data method : Right click on the table, select Edit the first 200 lines, the table in the input data for each item, after writing, click the Execution button, the table has data.

* If you want to change the list of items (column name) that have already been written, sometimes the error occurs after saving, at this point: tool-----------------Remove the item indicated by arrows.

10. Query statement: (SELECT)

• Select*from Products: Find out all the information in the table

• Select Productid,productname from Products: Check out all the productid,productname in the Products table

• Select Productid,productname from Products where productid=1: query out all ProductID and ProductName productid=1 inside the Products table

• select* from employee where Fname= ' Paul ' and job_id=5: Query out Fname=paul in the employee table, and job_id=5 all records

• Select*from Products where ProductID in (4,5,6): Query All information ProductID as 4,5,6

• Select*from products where unitprice>10 and unitprice<30 order by UnitPrice: query out the Products table 10<unitprice< 30 of all information, and according to the size of the UnitPrice from small to large sort

• Select*from products where UnitPrice between and the order by UnitPrice: Another way of writing above

• SELECT * from Employees where FirstName like ' A% ': Query out FirstName in Employees the first letter is the owner of A's information

• Select*from Employees where FirstName like '%a% ': Find all the information about Employees in FirstName with a in the middle

• Select*from Employees where FirstName like '%A ': The last letter of Employees in FirstName is the owner of A's information

• Select COUNT (*) from Employees: Query out all records in the Employees table

• Select min (UnitPrice) from Products: Query the lowest value of UnitPrice in the Products table

• Select Max (UnitPrice) from Products: Querying the maximum value of UnitPrice in the Products table

• Select AVG (UnitPrice) FROM Products: Query the average value of UnitPrice in the Products table

• Select SUM (UnitPrice) from Products: Query the sum of UnitPrice in the Products table

• SELECT * FROM Product where unitprice> (select AVG (UnitPrice) from products): There are subqueries to find more than the average value of goods information

• Select Top 5* from Products: Query the first five records

• SELECT DISTINCT [name] from category: Identify the name that is not duplicated in category

• Select COUNT (distinct name) from Category: Find out the number of name not duplicated in category

11. New statement: (Insert into)

• Insert into book (bookname,bookprice,bookauthors) VALUES (' C # basics ', ' + ', ' Ji ')

(insert bookname,bookprice,bookauthors The values of the three fields are: ' C # basics ', ' ' x ', ' Ji ')

12. UPDATE statement: (Update...set ... [where])

• Update book set bookprice=30 where Bookname= ' C # basics '

(Modify the Book table so that BookName is a bookprice=30 in a C # base item)

13. Delete statement (delete from ... ）

• Delete from book where bookname= ' C # basics '

(Delete all record lines in the Book table, Bookname= ' C # foundation ')

14. Joint Inquiry:

1) INNER JOIN
SELECT * FROM A inner join B on a.aid = b.bid: The two-table ID is the standard, the list of a and B are available

2) Right Join
SELECT * from A right join B on a.aid = B.bid: the B table BID is the standard, B table all shows, A table does not show null for the corresponding value

3) LEFT Join
SELECT * from A LEFT join B on a.aid = b.bid: As standard for Table a AID, B table shows all, no corresponding value in table a shows null

2015.7.30 Lesson 15th SQL (New database, create TABLE, comment, query statement, new, UPDATE, delete, union query)