常用SQL语句

Data Definition Language (DDL)

DDL()
CREATE TABLE table_name (column_name1 datatype(size), column_name2 datatype(size), column_name3 datatype(size));

DROP TABLE table_name;
    
ALTER TABLE table_name ADD COLUMN column_name datatype(size);

ALTER TABLE table_name ADD primary key (column_name);

Data Definition Language (DDL)

--Retrieve all data from a table
SELECT * FROM table_name; 

Data Manipulation Language (DML)

----Insert a record into the table
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

DELETE FROM table_name WHERE condition;

e.g.

CREATE TABLE Person (
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PersonID int primary key
);

insert into Person values ("1","1","1","1",1);
update Person set LastName = "Grus", FirstName = "Jone" where PersonID=1;
select * from Person;
delete from Person where PersonID = 1;
select * from Person;

--GRANT Command to provide the user of the database with the privileges required to allow users to access and manipulate the database.

--REVOKE Command to remove permissions from any user.

What are key constraints（主键约束）?

In every table there should be one or more columns or fields that can be used to fetch data from tables. In other words, a primary key. The key constraint specifies that there should be a column, or columns, in a table that can be used to fetch data for any row. This key attribute or primary key should never be NULL or the same for two different rows of data. For example, in the student table I can use the student ID to fetch data for each of the students. No value of student ID is null, and it is unique for every row, hence it can be the key attribute.

What are domain constraints?（域约束）

Domain constraints refer to the rules defined for the values that can be stored for a certain column. For instance, you cannot store the home address of a student in the first name column. Similarly, a contact number cannot exceed ten digits.

What are referential integrity constraints?（引用完整性约束）

When a table is related to another table via a foreign key column, then the referenced column value must exist in the other table. This means, according to the student and department examples, that values should exist in the student ID column in the student table because the two tables are related via the student ID column.

In this reading, you learned more about tables in a relational database as you explored the table in terms of its structure, data types, constraints, and the role of primary and foreign keys.