Some common SQL statements for Beginners (ii)

First, the creation of the database
Create Database
1. Create a database student
Create DATABASE Student
Create DATABASE Teacher

2. In the student database

Open Database Use database name
Use student
Two. Creation of tables
Create data table syntax
CREATE TABLE Table name
(Column name 1 type, column name 2 type,....)
Character Char varchar text specifies the length. VarChar variable-length character type
Create Data Sheet Student (student number, name, date of birth datetime, average score, scholarship)
CREATE TABLE Student
(Study number char (one) is not NULL,
Name varchar (50),
Date of birth datetime,
An average of int,
Scholarship float
)
Create data Table ' Department ' (department name, department number, department head)
CREATE TABLE Xibu
(Department name Char (10),
Department number char (2) NOT NULL,
Department Director varchar
)
Fields: Columns
Record: Line
Third, the addition of data
1. Add data to all columns in the table
Grammar:
Insert Table name
VALUES (column value 1, column value 2, ...) ）
* * Character char, value of varchar,datetime type plus ' '
Insert Student
VALUES (' 0101001 ', ' Cheng Fei ', ' 1995-10-16 ', 98,8000)
SELECT * FROM Student

' 0101002 ', ' Roisin ', ' 1995-1-6 ', 91,4000
Insert student values (' 0101002 ', ' Roisin ', ' 1995-1-6 ', 91,4000)

2. Add data to the columns in the table
Grammar:
Insert Table name (column name 1, column Name 2, ...) ）
VALUES (column value 1, column value 2, ...) ）

* * ' 0101003 ', ' Ching see Jia ', 91,4000
Insert Student
(Student number, name, average score, scholarship)
VALUES (' 0101003 ', ' Ching see ', 91,4000)
* * ' 0101002 ', ' Shi ', ' 1995-1-6 ', 88
Insert Student (school number, name, date of birth, average score)
VALUES (' 0101002 ', ' Shi ', ' 1995-1-6 ', 88)

Insert Student (student number, name, average score, scholarship)
VALUES (' 010102003 ', ' Zhang Kai ', 88.95,2000)

Insert Student (student number, name, average score, scholarship)
VALUES (' 010102004 ', ' Zhang Win ', 88,1000)
SELECT * FROM Student

Insert Student (student number, average score, name, scholarship)
VALUES (' 010102003 ', 83, ' Zhang Kai ', 600)
Four. Modification of data
[]: Available options
1. Modify all records in the table
Update table name set column name to modify = column value
Revised Scholarship for 800
Update Student Set Scholarship =800
2. Modify some of the records in the table
Update table name set column name to modify = column value
Where Condition expression
* * The average of modified Cheng Fei is divided into 80
Update student set average split =80
where name = ' Cheng Fei '
* * The scholarship for the modified process is 2000
Update Student Set Scholarship =2000
where name = ' Roisin '
Modified Ching See's date of birth for 1996-02-02 is divided by an average of 88
Update student
Set Date of birth = ' 1996-02-02 ', average =88
where name = ' ching See Jia '
Comparison operators:
Greater than > equal to >= less than or equal to <=
Not much more than!> <=
Not less than!< >=
Not equal to! =
0 scholarships for modifying data with an average score of not more than 80
Update Student Set Scholarship =0
where average divide!>80
SELECT * FROM Student
Modified the average score of more than 90 scholarships to 3600
Update Student Set Scholarship =3600
where average divide >90
Change the average score of not less than 91 scholarship to 3800
Update Student Set Scholarship =3800
where average divide!<91
All data for scholarships plus 600
Update Student Set scholarship = Scholarship +600
Modified the average birth date to be empty is divided into 60
Update student set average split =60
where birth date is null
Five Delete data
1. Delete some records
Delete table name where condition expression
SELECT * FROM Student
Delete data with an average score of not less than 90
Delete student where average split!<90
Delete student where average split >=90

Delete Scholarship not equal to 1400 of the data
Delete Student where scholarship!=1400
2. Delete all records

Delete Table name
Delete all records in the student table
Delete Student

Vi. Delete a table
DROP table Name
DROP TABLE Student

drop table Xibu

Some common SQL statements for Beginners (ii)