SQL进阶---第一单元(第四到第六课)、Manipulation

SQL进阶---第一单元、Manipulation

第四课、MANIPULATION Create

 

MANIPULATION Create

CREATE TABLE celebs (

        id INTEGER, 

        name TEXT, 

        age INTEGER);

This CREATE statement creates a new tablein the database named celebs. You can use the CREATEstatement anytime you want tocreate a new table from scratch.

1. CREATE TABLE is a clause that tells SQL you want to create a new table. 
2. celebs is the name of the table. 
3. (id INTEGER,name TEXT, age INTEGER) is a list of parameters definingeach column in the table and its data type. 

• id is the first column in the table. It stores values of data type INTEGER

• name is the second column in the table. It stores values of data type TEXT

• age is the third column in the table. It stores values of data type INTEGER

 

Instructions

1.Add a row to the table. In thecode editor type

• INSERTINTO celebs(id, name, age)

                                     VALUES (1,'Justin Bieber',21);

        INSERT INTOcelebs(id,name,age)

                         VALUES(1,'justin Bieber',21);

        Run a query to see results.

Database Schema

Celebs    1 rows

Id

INTEGER

Name

TEXT

Age

INTEGER

2.To view the row you justcreated, under the INSERTstatementtype

SELECT *FROM celebs;

Query Results

id

name

age

1

justin Bieber

21

 

第五课、Insert

MANIPULATION Insert

INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);

This INSERT statement inserts new rows intoa table. You can use the INSERT statement when you want to addnew records.

1. INSERT INTO is a clause that adds thespecified row or rows. 
2. celebs is the name of the table the rowis added to. 
3. (id, name,age) is a parameter identifying thecolumns that data will be inserted into. 
4. VALUES is a clause that indicates the data being inserted. 
(1, 'Justin Bieber', 21) is a parameter identifying the values being inserted.

• 1 is an integer that will be inserted into the id column

• 'Justin Bieber' is text that will be inserted into the namecolumn

• 21 is an integer that will be inserted into the age column

   

  Instructions

  1.Add three more celebs to thetable. In the code editor type:

• INSERTINTO celebs(id, name, age)

                        VALUES (2,'Beyonce Knowles',33);

  INSERTINTO celebs(id, name, age)

                        VALUES (3,'Jeremy Lin',26);

  INSERTINTO celebs(id, name, age)

                        VALUES (4,'Taylor Swift',26);

  2.Let's take a closer look at SELECT. Under the INSERTstatements type

SELECT nameFROM celebs;

 

INSERT INTO celebs (id, name, age)

VALUES (2, 'Beyonce Knowles', 33);

 

INSERT INTO celebs (id, name, age)

VALUES (3, 'Jeremy Lin', 26);

 

INSERT INTO celebs (id, name, age)

VALUES (4, 'Taylor Swift', 26);

 

SELECT name FROM celebs;

 

Query Results

name

Justin Bieber

Beyonce Knowles

Jeremy Lin

Taylor Swift

Database Schema

Celebs       4 rows

Id

INTEGER

Name

TEXT

Age

INTEGER

 

第六课、Select

MANIPULATION  Select

SELECT name FROM celebs;

SELECT statements are used to fetchdata from a database. Here, SELECT returns all data in the name column of the celebs table.

1. SELECT is a clause that indicatesthat the statement is a query. You will use SELECT every time you query data from a database. 
2. name specifies the column to query data from. 
3. FROM celebs specifies the name of the table to query data from. In thisstatement, data is queried from the celebs table. 

You can also query data fromall columns in a table with SELECT.

SELECT * FROM celebs;

* is a special wildcardcharacter that we have been using. It allows you to select every column in atable without having to name each one individually.Here, the result set containsevery column in the celebs table.

SELECT statements always return anew table called the resultset.

 

Instructions

1.Now that you know how to addrows to the table, let's edit a row. In the code editor type

UPDATE celebs

       SET age = 22WHERE id = 1;

SELECT *FROM celebs;

 

UPDATE celebs SET age = 22

                         WHEREid =1;

SELECT * FROM celebs;

 

Query Results

id

name

age

1

Justin Bieber

22

2

Beyonce Knowles

33

3

Jeremy Lin

26

4

Taylor Swift

26

Database Schema

Celebs     4 rows

Id

INTEGER

Name

TEXT

Age

INTEGER