MySQL basic statement and MySQL statement

MySQL basic statement and MySQL statement

Create a table

If it is a keyword, use the key below the back quotation mark ''esc

Create table [if not exists] table Name (
Field 1 Data Type [constraints, indexes, comments],
Field 2 Data Type [constraints, indexes, comments],
Field 3 data type [constraints, indexes, annotations]
) [Table type] [Table character set] [comment]

Signed and unsigned types

Signed type: Negative values can be obtained.
Unsigned type: the default value is 0! 0 -- type Length

ZEROFILL attribute: if the number of digits is not enough, use zero to complete it!

If a numeric field specifies the ZEROFILL attribute,
The UNSIGNED attribute is automatically added!

Create a student table

Create table if not exists student (
StudentNo INT (4) not null primary key comment 'student ID ',
LoginPwd VARCHAR (20) not null comment 'Password ',
StudentName VARCHAR (50) not null comment 'name ',
Sex CHAR (2) not null default 'male' COMMENT 'gender ',
GradeID INT (4) unsigned comment 'grade number ',
Phone VARCHAR (50) COMMENT 'phone ',
Address VARCHAR (255) DEFAULT 'address unknown 'comment' address ',
BornDate datetime comment 'birthday ',
Email VARCHAR (50) COMMENT 'email account ',
IdentityCard VARCHAR (18) unique key comment 'id card No'
) COMMENT = 'student table ';

Modify Table Data

-- Modify the table name and change the student table name to stu.
Alter table student rename as stu;

-- Adding a field to the table is not empty and unique
Alter table student ADD wechat VARCHAR (20) unique key;

-- Modify the field length in the table to 50
Alter table student MODIFY wechat VARCHAR (50 );

-- Delete fields in the table
Alter table student DROP wechat;

-- Modify the name of the studentName field to stuName.
Alter table student CHANGE 'name' stuName VARCHAR (20 );

Create subject)

Create table if not exists 'subobject '(
SubjectNo INT (4) AUTO_INCREMENT primary key comment 'course number ',
SubjectName VARCHAR (50) COMMENT 'course name ',
ClassHour INT (4) COMMENT 'hours ',
GradeID INT (4) COMMENT 'grade number'
) COMMENT = 'cortana 'charset = 'utf8 ';

Problems encountered
AUTO_INCREMENT must be used together with primary key!

The ID column is an auto-increment column!
The primary key is unique!

Create grade Table

Create table if not exists grade (
GradeID INT (4) COMMENT 'grade number ',
GradeName VARCHAR (10) COMMENT 'grade name'
) COMMENT = 'grade table ';

Add primary key syntax
Alter table name add constraint primary key name
Primary key table name (primary key field );

-- Set the id of the grade Table to the primary key.
Alter table grade add constraint pk_grade_gradeID primary key (gradeID );

Create external table

Create table if not exists result (
StudentNo INT (4) not null primary key comment 'student ID ',
SubjectNo INT (4) not null comment 'course number ',
ExamDate datetime not null comment 'test date ',
StudentResult INT (4) not null comment 'test result'
) COMMENT = 'character table 'charset = 'utf8' engine = InnoDB;

 

 

Foreign key Syntax:
Alter table name add constraint foreign key name
Foreign key (foreign key field)
REFERENCES join table name (join field );

 

Relationship between creating a student table and a grade Table

The student belongs to a certain grade.

The foreign key must be created on the slave table!

Alter table student add constraint fk_student_grade
Foreign key (gradeID)
REFERENCES grade (gradeID );

Problem: The premise is that a primary-foreign key relationship has been established.

If there are three grades, the numbers are 1 2 3
The relationship between the three students is as follows:
1st students, Grade 1
2nd students, Grade 2
3rd students, Grade 2

01. Is there a student whose grade number is 4 in this case ??
The master table has no data! The slave table cannot be created!

02. Can I delete Grade 2 or Grade 1 tables ???
Cannot be deleted! Because the table has associated data!

03. What if we have to delete the grade ??
Delete the associated data from the table first, and then delete the data in the master table!

Joint primary key
Set multiple columns to the primary key!

-- Set the joint primary key for the tables Table
Alter table result add primary key pk_result (studentno, subjectno, examdate );

 

Two storage engines commonly used in mysql Databases

MyISAM InnoDB
Transaction processing is not supported
Foreign key constraints are not supported
Full-text indexing is not supported

Use Cases:

MyISAM: transactions cannot be used, and the space is small. It is suitable for query!
InnoDB: Applicable to addition, deletion, and modification, with high security! Concurrent transaction processing!

-- Query the current default storage engine
Show variables like 'Storage _ engine % ';
-- Modify the storage engine
-- Find the my. ini file under the installation path.
-- Add default-storage-engine = InnoDB

Database Table storage location

InnoDB files
*. Frm: table structure definition file
*. Ibd: Data File

MyISAM files
*. Frm: table structure definition file
*. MYD: Data File
*. MYI: Index File