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 databaseDrop Database if exists' School '; #创建数据库Create Database' School '; Use' School '; #如果存在数据表, delete, otherwise createDrop Table if exists' Tb_class '; #创建一个学生班级表: Class ID (primary key, self-increment), class name. Create Table' Tb_class ' (' ID ')int( One) not NULLAuto_incrementPrimary Key, ' Name 'varchar( +) not NULL);Drop Table if  existstb_student; #创建一个学生信息表: Student ID (self-increment, primary key), name, age, gender, enrollment time, class ID (foreign key). Create Table' tb_student ' (' ID ')int( One) not NULLAuto_incrementPrimary Key, ' Name 'varchar( +) not NULL, ' age 'int default 0,Check(' Age '>0  and' Age '<= -), ' Gender ' Booleandefault 0,Check(' Gender '=0 or' Gender '=1), ' Date 'datetime defaultNow ()); #创建一个学生成绩表: Score id (self-increment, primary key), account, score, student ID (foreign key), creation time. Drop Table if exists' Tb_score ';Create Table' Tb_score ' (' ID ')int( One) not NULLAuto_incrementPRIMARY Key, ' Course 'varchar( +) not NULL, ' score 'float(3,1) not NULL, ' Stuid 'int( One) not NULL , constraint' Fk_stuid 'Foreign 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.

1  Use School; 2 Alter Table datetime;

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

 Use School; Alter Table Add varchar (a) after ' name ';

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