Creating a database MySQL

1 Examples:

1. Create a database named school
2. Create a Student Information table: Learning number ID (primary key, self-increment), name, date of birth, name (foreign key), class number (foreign key), dorm area
3. Create a Class information table: Class ID (primary key, self-increment), professional name, name (foreign key), number of students, year of entry
4. Create a Department Information table: System name (primary key), number (primary key, self-increment), office location, number of people
5. Create a Student union information sheet: Learning name (primary key), year of establishment, office location, number of persons

Create tables and databases:

#如果存在数据库School, it is deleted. Otherwise, create the database
Drop database if exists ' School ';
#创建数据库
Create database ' School ';
Use ' School ';
#创建一个学校系表: Department Number (primary key, self-increment), office location, number of persons
drop table if exists ' tb_dept ';
CREATE TABLE ' tb_dept '
(
' id ' int (one) not NULL auto_increment PRIMARY key,
' Local ' varchar (+) NOT NULL,
' Dnum ' int (4) NOT NULL
);
#创建一个学生班级表: Class ID (primary key, self-increment), professional name, name (foreign key), number of persons
drop table if exists ' tb_class ';
CREATE TABLE ' Tb_class '
(
' id ' int (one) NOT NULL Auto_increment primary key,
' CName ' varchar (+) NOT NULL,
' Cdeptid ' int (one) is not NULL,
' CNum ' int (4) is not NULL,
Constraint ' fk_stuid ' foreign key (' Cdeptid ') references ' tb_dept ' (' ID ')
);
#创建一个学生信息表: Student ID (self-increment, primary key), name, age, gender, class ID (foreign key), dorm area.
drop table if exists ' tb_student ';
CREATE TABLE ' Tb_student '
(
' id ' int (one) NOT NULL Auto_increment primary key,
' SName ' varchar (+) NOT NULL,
' Age ' int the default 0,check (' Age ' >0 and ' age ' <=100),
' Gender ' boolean default 0,check (' Gender ' =0 or ' gender ' =1),
' ClassId ' int (one) is not NULL,
Constraint ' fk_stuid1 ' foreign key (' classId ') references ' tb_class ' (' ID ')
);

Language Writing Standard questions:

1. Brackets to Branch
2. The last line does not require commas
3. The FK_STUID1 value needs to be changed when the foreign key is not duplicated
4. Create a data table without foreign keys before creating a data table with foreign keys

Database created by query:

show databases;

Query table structure:

 Use School; desc tb_student;

Add data information:

#在学生姓名之后添加电话字段  Use School; Alter Table Add varchar (a) after ' SName ';

#为表tb_student添加字段classid, and set as a foreign key.   use School; Alter Table Add int (one notnull; Alter Table Add constraint Foreign Key references tb_dept (' id ');

To create a data record table:

 UseSchool;Insert  intoTb_dept (Id,local,dnum)Values (NULL,'Cy','1'),(NULL,'qy','1'),(NULL,'Zy','1');

Use school;
Insert into tb_dept (id,local,dnum) values
(NULL, ' Cy ', ' 1 '),
(null, ' ry ', ' 2 '),
(null, ' Ty ', ' 3 ');

Querying the database:

SELECT  from ORDER  by Dnum;

Creating a database MySQL