. NET 0 Basic Primer 09:sql must know

A: Preface

As if to go further, every programmer will encounter data storage problems sooner or later. What do we take to store the data generated by the program? For example, what does it take to store the results of our mouse games every time? Choose from the following:

1: In memory. Shortcomings, quit the game, the data is gone;

2: In the file. Good idea! Shortcomings, self-parsing text, the text into our program data, the process of parsing is called protocol. The word "protocol" sounds scary enough, in fact, it is nothing more than the data format, API interface how such things.

3: Database. Good idea! Well, the database file is actually the file on the hard disk, but the database itself has already defined the data format and API interface for us. Well, the relational database provides us with API interfaces or protocols that are SQL statements, and we use SQL statements to store and update our data. What the? Relational database is outdated, we want to object-oriented database? Well, you go ahead, our course is to learn the relational database first, or to learn SQL statements for relational databases.

Two: What is a relational database

relational database, which is based on the relational model, is used to deal with the data in the database by means of mathematical concepts and methods such as set algebra. The relationships between the various entities in the real world and the entities are represented by a relational model. The relational model was first proposed by Edgar F. Codd in 1970 and is in line with the "Cod 12 law". Although there are some criticisms of this model today, it is a traditional standard for data storage. Standard data Query Language SQL is a relational database-based language that performs retrieval and manipulation of data in relational databases. The relational model consists of three parts: relational data structure, relational operation set, and relationship integrity constraint.

So what is SQL?

Structured Query Language (structured query Language) is called SQL, Structured Query language is a database query and programming language, used for accessing data and querying, updating and managing relational database system;

Above about what is a relational database and what is the definition of SQL from the network, if there is infringement, be honored. In fact, so far we have not said why we want to learn SQL, then, there is only one reason: programmers to develop traditional applications, as long as the data storage, the most common is to use a relational database and the operation of its SQL statement. So, a typical programmer to develop an application system, in addition to mastering a programming language, SQL must also be mastered. This is our course, although it is called the basic C # 0 primer, but also to explain why SQL must know. Our game Of mice also uses SQL to store each score, and so on.

So, is there a quick way to learn SQL? Very simple, daily practice, as we learn English. After today's course we are going to build a simulation database, and then according to this simulation database, we will be a daily practice, in a daily practice, slowly proficient in SQL. TIP, want to spend a week conquering SQL? No problem, but not for the next week, we'll forget about it next week. So, the secret to being proficient in SQL is the daily must practice.

Three: SQL SERVER

Using Microsoft's platform for development, the preferred is the SQL Server database. We are currently the easiest to find (or the easiest to find on the web), with the 2005 and 2008 versions most frequently used. The latest is 2012, this time we did not ask to download the latest version. Why is it? Because the replacement of the database is not so necessary for our development, if we have no special needs. Let's say you've installed SQL Server, 2005 or 2008, and if you don't know how to install SQL Server, I recommend bing.com SQL Server 2005 installation diagram.

So, the database is installed, the first thing is to create a database to connect it? OK, take a look at our example.

Using Microsoft's platform for development, the preferred is the SQL Server database. We are currently the easiest to find (or the easiest to find on the web), with the 2005 and 2008 versions most frequently used. The latest is 2012, this time we did not ask to download the latest version. Why is it? Because the replacement of the database is not so necessary for our development, if we have no special needs. Let's say you've installed SQL Server, 2005 or 2008, and if you don't know how to install SQL Server, I recommend bing.com SQL Server 2005 installation diagram.

So, the database is installed, the first thing is to create a database to connect it? OK, take a look at our example:

Four: Video

Non-public section, please contact the best course (www.zuikc.com)

Five: Create a sample database

The next step is to create our sample database, which we'll use as our daily must-do program. Create the database Testsample as you would in the video, and then:

Click 1 "New Query", click 2, select the Testsample we created, and then in 3 space, enter the following SQL statement:

CREATE TABLE EMP
(
EMPNO INT not NULL,
Ename VARCHAR (+) DEFAULT NULL,
JOB VARCHAR (+) DEFAULT NULL,
MGR VARCHAR (+) DEFAULT NULL,
HireDate DATETIME DEFAULT NULL,
SAL int DEFAULT NULL,
COMM VARCHAR (+) DEFAULT NULL,
DEPTNO VARCHAR (8) DEFAULT NULL,
PRIMARY KEY (EMPNO)
)
--
--Dumping data for table EMP
--
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7369,
' SMITH ',
' Clerk ',
' 7902 ',
' 1980-12-17 ',
' 800 ',
Null
' 20 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7499,
' ALLEN ',
' Salesman ',
' 7698 ',
' 1981-02-20 ',
' 1600 ',
' 300 ',
' 30 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7521,
' WARD ',
' Salesman ',
' 7698 ',
' 1981-02-22 ',
' 1250 ',
' 500 ',
' 30 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7566,
' JONES ',
' MANAGER ',
' 7839 ',
' 1981-04-02 ',
' 2975 ',
Null
' 20 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7654,
' MARTIN ',
' Salesman ',
' 7698 ',
' 1981-09-28 ',
' 1250 ',
' 1400 ',
' 30 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7698,
' BLAKE ',
' MANAGER ',
' 7839 ',
' 1981-05-01 ',
' 2850 ',
Null
' 30 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7782,
' CLARK ',
' MANAGER ',
' 7839 ',
' 1981-06-09 ',
' 2450 ',
Null
' 10 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7788,
' SCOTT ',
' ANALYST ',
' 7566 ',
' 1982-12-09 ',
' 3000 ',
Null
' 20 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7839,
' KING ',
' President ',
Null
' 1981-11-17 ',
' 5000 ',
Null
' 10 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7844,
' TURNER ',
' Salesman ',
' 7698 ',
' 1981-09-08 ',
' 1500 ',
' 0 ',
' 30 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7876,
' ADAMS ',
' Clerk ',
' 7788 ',
' 1983-01-12 ',
' 1100 ',
Null
' 20 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7900,
' JAMES ',
' Clerk ',
' 7698 ',
' 1981-12-03 ',
' 950 ',
Null
' 30 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7902,
' FORD ',
' ANALYST ',
' 7566 ',
' 1981-12-03 ',
' 3000 ',
Null
' 20 '
)
INSERT into EMP
(EMPNO,
ENAME,
JOB,
MGR,
HireDate,
SAL,
COMM,
DEPTNO
)
VALUES (7934,
' MILLER ',
' Clerk ',
' 7782 ',
' 1982-01-23 ',
' 1300 ',
Null
' 10 '
)
--
--Table Dept
--
CREATE TABLE Dept
(
DEPTNO INT not NULL,
Dname VARCHAR (+) DEFAULT NULL,
LOC VARCHAR (+) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
)
INSERT into Dept
(DEPTNO, Dname, LOC)
VALUES (' Ten ', ' ACCOUNTING ', ' NEW YORK ')
INSERT into Dept
(DEPTNO, Dname, LOC)
VALUES (' + ', ' DALLAS ')
INSERT into Dept
(DEPTNO, Dname, LOC)
VALUES (' + ', ' SALES ', ' CHICAGO ')
INSERT into Dept
(DEPTNO, Dname, LOC)
VALUES (' + ', ' OPERATIONS ', ' BOSTON ')
--
--Table T1
--
CREATE TABLE T1
(
ID INT not NULL,
PRIMARY KEY (ID)
)
INSERT into T1
(ID)
VALUES (' 1 ')
--
--Table T10
--
CREATE TABLE T10
(
ID INT not NULL,
PRIMARY KEY (ID)
)
INSERT into T10
(ID)
VALUES (' 1 ')
INSERT into T10
(ID)
VALUES (' 2 ')
INSERT into T10
(ID)
VALUES (' 3 ')
INSERT into T10
(ID)
VALUES (' 4 ')
INSERT into T10
(ID)
VALUES (' 5 ')
INSERT into T10
(ID)
VALUES (' 6 ')
INSERT into T10
(ID)
VALUES (' 7 ')
INSERT into T10
(ID)
VALUES (' 8 ')
INSERT into T10
(ID)
VALUES (' 9 ')
INSERT into T10
(ID)
VALUES (' 10 ')
CREATE TABLE Emp_bonus
(
Empno INT,
Received DATETIME,
TYPE INT
)
Go
INSERT into Emp_bonus
VALUES (7934, 2005-5-17, 1)
INSERT into Emp_bonus
VALUES (7934, 2005-2-15, 2)
INSERT into Emp_bonus
VALUES (7839, 2005-2-15, 3)
INSERT into Emp_bonus
VALUES (7782, 2005-2-15, 1)

Then click "Execute" at 4, so that we can create our sample database.

Now, try emptying the text at 3, then enter:

SELECT * FROM emp

Then click on execute to see what will come out of the query?

Six: SQL must be known

Today we will introduce the simplest SQL statements, with today's introduction, we will give our game to play mice to increase the content of the database storage. They were as follows:

Select

As the name implies, is the query, the simplest query we have just learned, is to remove all the data from the database:

SELECT * FROM emp

So, the second thing you have to master is the conditional query:

SELECT * from emp where ename= ' Smith '

Insert

Insert a statement with the following actions:

INSERT INTO Dept (DEPTNO,DNAME,LOC) VALUES ($, ' testdname ', ' Testloc ')

The first parenthesis is the field of the table, and the second one in parentheses is the corresponding value.

Update

Update Dept Set dname= ' testdname2 ', loc= ' testloc2 ' where deptno=500

The UPDATE statement, we update the record that DEPTNO is 500 to the desired value.

Delete

Delete Dept where deptno=500

The above is the deletion of the statement, note Ah, delete must be careful, if not added to the conditions, it will be removed from the entire table, regret is too late.

. NET 0 Basic Primer 09:sql must know