This is the third day of my participation in Gwen Challenge

1 Creating a database

Syntax: CREATE DATABASE < name >;

Example: CREATE DATABASE student;

2 Table creation

Grammar:

CREATE TABLE < TABLE name > (< column name > < data type > < constraint required by this column >, < column name > < data type > < constraint required by this column >,.. < constraint of this TABLE >, < constraint of this TABLE >,...) ;Copy the code

Example:

CREATE TABLE product(
     stu_id CHAR(10) NOT NULL, 
     stu_name VARCHAR(50) NOT NULL, 
     stu_age INT,  
     PRIMARY KEY(stu_id)
 )  ;


Copy the code

3 Naming Rules

  1. Database naming conventions

It consists of 26 case-sensitive letters and numbers from 0 to 9, plus the underscore ‘_’.

  1. Table naming conventions

(1) Use 26 letters and the natural numbers 0-9 (usually not needed) with the underscore ‘_’.

(2) Do not use keywords

(3) The table name should not be too long

  1. Field naming conventions

(1) Use 26 letters and the natural numbers from 0 to 9 (generally not used) plus the underscore ‘_’.

(2) static use of database keywords.

(3) Field names generally use nouns or verb-object phrases.

4 Table deletion and update

  • DROP TABLE < TABLE name >;

  • DROP TABLE student; DROP TABLE student

  • ALTER TABLE < TABLE name > ADD COLUMN < COLUMN definition >;

  • Add a stu_NAME column that can store a 10-bit string

ALTER TABLE student ADD COLUMN stu_name CHAR(10);

  • ALTER TABLE < table_name > DROP COLUMN < table_name >;

  • ALTER TABLE student DROP COLUMN STU_name ALTER TABLE student DROP COLUMN stu_name

!!!!!!!!! An ALTER TABLE statement, like a DROP TABLE statement, cannot be restored after execution

5 Data update

~1 Modify data

UPDATE < table name >

SET < column 1> =< expression 1>[,< column 2>=< expression 2>…

[WHERE < condition >];

Example: Change the English score of student number “A001” to 88

UPDATE STUDENT
SET ENGLISH = 89
WHERE SNO='a001';
Copy the code

~2 Insert data

INSERT INTO < table name > (表 1, 表 2, 表 3) VALUES (1, 2, 3...) ;

Example: Insert the new Student (A089,19, male, CS) into the Student table

INSERT 
INTO Student
VALUES('a089'.19.'male'.'cs');
Copy the code

In principle, an INSERT statement inserts a row of data. When inserting multiple rows, you typically need to loop through INSERT statements a corresponding number of times. In fact, many RDBMSS support multiple rows of data being inserted at once

INSERT INTO productins VALUES ('4'.'hole punch'.'Office supplies'.600.320.'2010-09-11'),
('5'.'T shirt'.'clothes'.3000.2490, NULL),
('7'.'the sword'.'kitchen'.2000.2700.'2020-09-20');  
Copy the code

~3 Delete data

DELETE FROM < table name > [WHERE < condition >];Copy the code

Example: Delete all information from student table

DELETE 
FROM S;
Copy the code