MySQL database basic topic is coming!

Hee-hee, this term I learned the fundamentals of MySQL database, and then I have sorted out some simple questions that are useful for MySQL database, I hope you can help!

Say nothing, go dry!

Table structure management

1. Objectives

1. Creation and deletion of MySQL database
2. Create, modify, delete tables in MySQL database
3. Understanding and Application of Data Types in MySQL Database
4. Application of Index in MySQL Database

2. Process

1. Create a database named "Short Name _db" with the following code format

CREATE DATABASE XXX_db

2. Modify the character set of the database you created to be gbk with the following code format

ALTER DATABASE Database Name CHARSET = character set

3. Create the following tables in your own database:

(1), Teacher table

Field name	data type	length	constraint condition
Teacher_no (Teacher's number)	varchar	10	Primary Key Constraint
Teacher_name (name of teacher)	varchar	10	Non-empty constraint
Teacher_contact	varchar	20	Non-empty constraint

Grammatical structure:

CREATE TABLE Table Name
(
	Field name data type (length)[constraint condition]
	Field name data type (length)[constraint condition]
	Field name data type (length)[constraint condition]
	......
)

The code is as follows:

CREATE TABLE Teacher_no
(
	Teacher_no VARCHER(10) PRIMARY KEY,
	Teacher_name VARCHER(10) NOT NULL,
	Teacher_contact VARCHER(20) NOT NULL,
)

(2) calsses table:

Field name	data type	length	constraint condition
class_no (shift number)	int		Primary key constraints, autogrowth
class_name (class name)	varchar	20	Nonnull Constraint, Uniqueness Constraint
Department_name (department name)	varchar	20	Non-empty constraint

The code is as follows:

CREATE TABLE classes
(
	class_no INT AUTO_INCREMENT,
	class_name VARCHAR(20) NOT NULL,
	Department_name VARCHAR(20) NOT NULL,
	PRIMARY KEY(class_no),
	UNIQUE(class_name)
)

(3) course table:

Field name	data type	length	constraint condition
course_no (course number)	int		Primary key constraints, autogrowth
course_name (course name)	varchar	10	Non-empty constraint
up_limit (maximum number of people)	int		Default value 60
Description (Course Description)	text		Non-empty constraint
Status (course status)	char	6	Default'Unaudited'
Teacher_no (Teacher's number)	varchar	10	Nonnull Constraint, Uniqueness Constraint, Foreign Key Constraint

The code is as follows:

CREATE TABLE course
(
		course_no INT AUTO_INCREMENT PRIMARY KEY,
		course_name VARCHAR(10) NOT NULL,
		up_limit INT DEFAULT(60),
		Description TEXT NOT NULL,
		status CHAR(6) DEFAULT'Not audited'
		Teacher_no VARCHAR(10) REFERENCES Teacher(Teacher_no),
		UNIQUE(Teacher_no)
)

(4) Student table:

Field name	data type	length	constraint condition
Student_ No (number)	char	11	Primary Key Constraint
student_name (student name)	varchar	10	Non-empty constraint
Student_contact	varchar	20	Non-empty constraint
Class_no (class number)	int		Foreign Key Constraints

The code is as follows:

CREATE TABLE Student
(
	Student_no CHAR(11) PRIMARY KEY,
	Student_name VARCHAR(10) NOT NULL,
	Student_contact VARCHAR(20) NOT NULL,
	Class_no INT REFERENCES classes(class_no)
)

(5) choose table:

Field name	data type	length	constraint condition
choose_no (serial number)	int		Primary key constraints, autogrowth
student_no (number)	char	11	Foreign Key Constraints
Course_no (course number)	int		Foreign Key Constraints
Score (Score)	tinyint unsigned
Choose_time (course selection time)	datetime		Non-empty constraint

The code is as follows:

CREATE TABLE choose
(
	choose_no INT AUTO_INCREMENT,
	student_no CHAR(11) REFERENCES Student(Student_no),
	Course_no INT REFERENCES course(course_no),
	Score TINYINT UNSIGNED,
	choose_time DATETIME NOT NULL,
	PRIMARY KEY(choose_no)
)

4. Copy a table structure (you can choose any of the five tables above)

Grammatical structure:

1. CREATE TABLE New Table Name LIKE Source Table
2. CREATE TABLE New Table Name SELECT * FROM Source Table

The code is as follows:

CREATE TABLE Student11 LIKE Student

5. Modify the table structure (take the Student table as an example)

Grammatical structure:
ALTER TABLE table name
ADD Field Name Data Type Constraints##New Field
MODIFY Field Name Data Type##Modify Field
DROP Field Name##Delete Field

Requirement 1:
Add a new gender field to the Student table, data type char (10), default value of "male"; Add an ID_to the Student table No field, data type char(10)

The code is as follows:

ALTER TABLE Student
ADD gender CHAR(10) DEFAULT'male',
ADD ID_no CHAR(10)

Requirement 2:
In the Student table, modify the field ID_ The data type of no is varchar(20)

The code is as follows:

ALTER TABLE Student
MODIFY ID_no VARCHAR(20);

Requirement 3:
In the Student table, delete the field gender

The code is as follows:

ALTER TABLE Student
DROP gender;

Requirement 4:
In the Student table, give the field ID_no add non-empty constraint

The code is as follows:

ALTER TABLE Student
ADD ID_no NOT NULL;

6. Delete the table duplicated in question 4

Syntax structure: DROP TABLE table name

The code is as follows:

DROP TABLE Student11

Delete the database you created

The code is as follows:

DROP DATABASE xx_db

These are some simple MySQL database exercises, I hope they can help everyone learn MySQL database a little!!!

As a freshman just joined the computer specialty and CSDN family, although I don't have any professional knowledge to share, I hope I can bring you something helpful!!!