TSQL Statements and CRUD (20161016)

Morning

TSQL Statement

1. Create a database

Create Database test2;

2. Deleting a database

Drop database test2;

3. Create a table

CREATE TABLE Ceshi

(

IDS int Auto_increment primary KEY,

UID varchar (20),

Name varchar (20),

Class varchar (20),

Foreign key (Class) references Class (code)

);

CREATE TABLE Class

(

Code varchar (primary key),

Name varchar (NOT NULL)

);

* * Self-growth auto_increment

* * PRIMARY KEY PRIMARY key

* * FOREIGN key FOREIGN key (column name) references Main Table name (column name)

* * non-null NOT NULL

4. Delete a table

drop table Ceshi;

Attention:

1. The type contains the length of the parentheses after the type, and the parentheses write the length

2. Complete the previous column with a comma

3. The last column do not write a comma

4. Add a semicolon after each SQL statement is finished

5. If there is a foreign key relationship, first create the primary table

To create a table:

CREATE TABLE Class

(

Code varchar (primary key),

Name varchar (20)

);

CREATE TABLE Student

(

Code varchar (primary key),

Name varchar (20),

Sex bit,

Age int,

Class varchar (20),

Foreign key (Class) references Class (code)

);

CREATE TABLE Kecheng

(

Code varchar (primary key),

Name varchar (20)

);

CREATE TABLE Teacher

(

Code varchar (primary key),

Name varchar (20)

);

CREATE TABLE Chengji

(

IDS int Auto_increment primary KEY,

SCODE varchar (20),

Kcode varchar (20),

degree float,

Foreign KEY (SCODE) references student (code),

Foreign KEY (Kcode) references Kecheng (code)

);

CREATE TABLE Tkecheng

(

IDS int Auto_increment primary KEY,

Tcode varchar (20),

Kcode varchar (20),

Foreign KEY (Kcode) references Kecheng (code),

Foreign KEY (Tcode) references teacher (code)

);

Lecture Notes:

1. Main Table cannot be deleted, delete Main table from table first
2. If you run it repeatedly two times, it will appear

[ERR] 1050-table ' class ' already exists

This is because duplicate columns have been added, and if there is a problem viewing the table after saving,

Afternoon:

Crud

1. Annotation syntax:--,#

2. The suffix is. sql files are database query files

3. Save the query when the code is saved (TSQL statement), after the successful execution of the query, the table has been established

4. There's a name in the database that's called the field row.

CRUD Operations:

Create Creation (ADD)

Read Reads

Update modification

Delete Deletes

1. Add Data

INSERT into Info values (' p009 ', ' Zhang San ', 1, ' n001 ', ' 2016-8-30 12:9:8 ');

Add data to a specific column

Insert into Info (code,name) VALUES (' p010 ', ' John Doe ');

Handling of self-growing columns

INSERT into family values (' ', ' p001 ', ' data ', ' T001 ', ' data ', 1);

Insert into table name values (value)

2. Delete data

Delete all data

Delete from family

Delete a specific data

Delete from Info where code= ' p001 '

Delete from table name where condition

3. Modify the data

Modify all data

Update Info set name= ' Xu Yepeng '

Modify specific data

Update Info set name= ' LV Yongle ' where code= ' P002 '

Modify multiple columns

Update Info set name= ' LV Yongle ', sex=1 where code= ' p003 '

Update table name set the content to be modified where condition

4. Read data

(1) Simple read, query all columns (*) All rows (no add condition)

SELECT * FROM Info

(2) reading a specific column

Select Code,name from Info

(3) Conditional query

SELECT * from Info where code= ' p003 '

(4) Multi-criteria Query

SELECT * from Info where code= ' p003 ' or nation= ' n002 ' #或的关系

SELECT * from Info where sex=0 and nation= ' n002 ' #与的关系

(5) keyword query (fuzzy query)

Check all cars that contain Audi

SELECT * from car where name like '% Audi% '; #百分号% represents any number of characters

Check all the cars that start with ' crown '

SELECT * from car where name like ' Crown% ';

Query car Name The second character is ' horse '

SELECT * from car where name like ' _ Ma% '; #下划线_代表任意一个字符

(6) Sort query

SELECT * from Car order by powers #默认升序排列

SELECT * from car ORDER by powers Desc #升序asc Descending desc

Rank by brand in ascending order, and then

SELECT * FROM car ORDER BY brand,price Desc

Lecture Notes:

1. The error may occur after a repeat run, because the table after the execution code is run repeatedly may not be the original in the table

Extracurricular supplement:

MySQL add columns, modify columns, delete columns

ALTER table: Add, modify, delete table columns, constraints, and other table definitions.

View column: Desc table name;

Modify table name: ALTER TABLE T_book Rename to BBB;

Add column: ALTER TABLE name add column name varchar (30);

Delete column: ALTER TABLE table name drop column name;

Modify Column name Mysql:alter table BBB change nnnnn (original column name) HH (modified column name) int (type);

Modify the column name Sqlserver:exec sp_rename ' t_student.name ', ' nn ', ' column ';

Modify the Column name Oracle:lter table BBB Rename column nnnnn to HH int;

Modify Column properties: ALTER TABLE T_book modify name varchar (22);

Sp_rename:sqlserver built-in stored procedures, with the definition of modified tables.

MySQL Modify and delete data records

MySQL database believe that many people have been exposed to the MySQL database in the operation of the time, someone would like to delete or modify some data records in the MySQL database.

MySQL database believe that many people have been exposed to the MySQL database in the operation of the time, someone would like to delete or modify some data records in the MySQL database. The DELETE and UPDATE statements allow us to do this.

modifying records with Update

UPDATE Tbl_name Set the column to change

WHERE to update the record

The WHERE clause here is optional, so every record in the table is updated if it is not specified.

For example, in the Pet table, we find that the sex of the pet Whistler is not specified, so we can modify this record as follows:

Mysql> Update pet Set sex= ' F ' where name= "Whistler";

Delete Records with delete

The DELETE statement has the following format:

Delete from Tbl_name WHERE to delete records

The WHERE clause specifies which records should be deleted. It is optional, but if not selected, all records will be deleted. This means that the simplest DELETE statement is also the most dangerous.

This query clears all the contents of the table. Must be careful!

To delete a specific record, use the WHERE clause to select the record you want to delete. This is similar to the WHERE clause in the SELECT statement.

Mysql> Delete from pet where name= "Whistler";

You can empty the entire table with the following statement:

Mysql>delete from Pet;

Homework:

CREATE TABLE Class

(

Code varchar (primary key),

Name varchar (NOT NULL)

);

CREATE TABLE Ceshi

(

IDS int Auto_increment primary KEY,

UID varchar (20),

Class varchar (20),

Foreign key (Class) references Class (code)

);

Modifying a test column

#alter table ceshi Add column name varchar (20);

#alter table ceshi drop column uid;

ALTER TABLE Ceshi change name sex bit;

TSQL Statements and CRUD (20161016)