MySQL CREATE database, create data table

Write in front

MySQL database was used in the project, and MySQL was not used before, and today we learned the common syntax of MySQL, which is very similar to SQL Server syntax. It's pretty simple to use.

An example

1. Create a database named school.

1. Create a Student information table: Student ID (self-increment, primary key), name, age, gender, telephone, place of origin, enrollment time, class ID (foreign key).

2. Create a Student score table: Score ID (self-increment, primary key), account, score, student ID (foreign key), creation time.

3. Create a Student Class table: Class ID (primary key, self-increment), class name.

Creating Tables and databases

#如果存在数据库School, it is deleted. Otherwise, create the databaseDropDatabaseIfExists' School '; #创建数据库CreateDatabase' School ';Use' School '; #如果存在数据表, delete, otherwise createDropTableIfExists' Tb_class '; #创建一个学生班级表: Class ID (primary key, self-increment), class name.CreateTable' Tb_class ' (' ID ')int11)NotNull auto_incrementPrimaryKey, ' Name 'varchar32)NotNull);DropTableIfExiststb_student; #创建一个学生信息表: Student ID (self-increment, primary key), name, age, gender, enrollment time, class ID (foreign key).CreateTable' Tb_student ' (' ID ')int11)NotNull auto_incrementPrimaryKey, ' Name 'varchar32)NotNull, ' age 'IntDefault0,Check (' Age '>0and ' age '<=100), ' Gender ' BooleanDefault0,Check (' Gender '=0or ' gender '=1), ' Date 'DatetimeDefaultNow ()); #创建一个学生成绩表: Score id (self-increment, primary key), account, score, student ID (foreign key), creation time.DropTableIfExists' Tb_score ';CreateTable' Tb_score ' (' ID ')int11)NotNull auto_incrementPRIMARYKey, ' Course 'varcharnot nullforeign key (' Stuid ') references "Tb_student" (' id '));         

Querying the database created

show databases;

View table Structure

Use School;  Desc tb_student; 

Results

The field that modifies the Student information table is date createdate.

Use School;  datetime; 

Add the Student phone field after the student information sheet name.

Use School;  varchar (all) after ' name ';  

Add the field ClassID to the table tb_student and set it to a foreign key.

Use School;  int (null;  References Tb_class (' id ');    

Summarize

Creating a database and creating a data table It's easy to learn if you've used SQL Server. After that, you will learn the additions and deletions in the data sheet.

MySQL CREATE database, create data table