Detailed description of MySQL design and command line mode, and detailed description of mysql

Detailed description of MySQL design and command line mode, and detailed description of mysql

Detailed description of MySQL design and creation in Command Line Mode

Series of articles:

Detailed description of MySQL design and creation in Command Line Mode

How C ++ uses MySQL APIs to connect to and operate database instances

1. Data Table Design

In the MySQL database management system (DBMS), the type of data fields defined in MySQL is very important for your database optimization. MySQL supports multiple types, which can be roughly divided into three types: numerical value, date/time, and string (character) type.

The following uses the database used in the Course Selection Management System of students familiar to the university as an example to design the corresponding data tables. There are three main tables: Student table, Course table and Course Selection table.

Student table design:

Field)	Type)	Null)	Key)	Default Value)	Others (Extra)
Student ID (studentNo)	VARCHAR (12)	N	PRI	NULL
Name)	VARCHAR (12)	N	N	NULL
School)	VARCHAR (12)	N	N	NULL
Grade)	VARCHAR (12)	N	N	NULL
Professional (major)	VARCHAR (12)	N	N	NULL
Gender)	Boolean	N	N	NULL

Curriculum Design:

Field)	Type)	Null)	Key)	Default Value)	Others (Extra)
Course No. (courseNo)	VARCHAR (10)	N	PRI	NULL
Course name (courseName)	VARCHAR (10)	N	N	NULL
Lesson (hour)	TINYINT Unsigned	N	N	NULL
Course credit)	TINYINT Unsigned	N	N	2

Course Selection table design:

Field)	Type)	Null)	Key)	Default Value)	Others (Extra)
Id)	INT Unsigned	N	PRI	NULL	Auto_increment
Student ID (studentNo)	VARCHAR (12)	N	MUL	NULL
Course No. (courseNo)	VARCHAR (10)	N	MUL	NULL
Course Selection time)	TIMESTAMP	N	N	CURRENT_TIMESTAMP

For the above three data tables, you will find:

(1) when defining a string (character) type for data, MySQL must specify the maximum number of characters (TypeName (M) in parentheses after the type name ), here M refers to the number of characters, rather than the number of bytes occupied by data. For example, varchar (12) indicates that the number of characters stored cannot exceed 12. If the number of characters exceeds 12, the characters are truncated. If the number of characters is less than 12, the storage space is used for storage, this saves storage space.

(2) When designing the course selection table, we add a foreign key constraint to associate the two tables to ensure data consistency and implement cascade operations.

(3) MySQL has three keys and one Index: Primary Key (Primary Key), Unique Key (Unique Key), Foreign Key (Foreign Key), and Index (Index ).

The differences between the four are as follows:

Definition:

Primary Key: uniquely identifies a record. It cannot be repeated and cannot be empty.
Foreign Key (Foreign Key): The Foreign Key of the table is the primary Key of the other table. Foreign keys can be repeated or null.
Unique Key: uniquely identifies a record. It cannot be repeated and can be empty.
Index: this field can have record of duplicate values and null values. If it is a unique Index, there can be no duplicate records or null values.

Purpose:

Primary Key: Used to ensure data integrity.
Foreign key: used to establish connections with other tables to ensure data consistency and cascade operations.
Unique key: Used to prevent duplication during data insertion.
Index: Improves the query sorting speed.

Quantity:

Primary Key: one table can only have one column as the primary key.
Foreign key: A table can have multiple columns that are foreign keys.
Unique key: A table can have multiple columns as unique keys.
Index: A table can have multiple columns as indexes.

It can be seen that the index and the unique key are very similar. The difference between the two is that the function is different. The index is used to increase the query speed, and the unique key is used for the unique constraint. Of course, if the created index is a unique index, it can also play a unique role. In specific implementation of MySQL, indexes require data structures and extra disk space to store indexes. Keys (primary keys, foreign keys, and unique keys) are logical implementations and constraints, there is a fundamental difference between the two.

In addition, MySQL indexes include: normal Index, unique index, full text INDEX, and composite Index. Here, do not confuse the unique key and unique index! Do not confuse indexes with the other three keys.

Note that, although the key and index are essentially different, the index is also created when we create a primary key or unique key, mySQL and Oracle both do this. If you do not understand this, it is easy to confuse the index and primary key with the unique key.

A unique key can act as a unique constraint. Of course, a primary key can also act as a unique constraint. Of course, we can directly add a unique constraint to the columns of the specified data table without creating a unique key or primary key. Unique constraints ensure that the values of the specified Column cannot be repeated.

Therefore, we should not chew on the above concepts. Instead, we should understand the role of each KEY based on functions and use it in any scenarios.

For example, when a primary key constraint is required, we can create a primary key for a column;

When a unique constraint is required for one or more columns, we create a unique key or constraint;
When we need to speed up the query, we will create corresponding types of indexes.

We have not referred to any authoritative materials for the understanding of the four, so please be skeptical and accept them.

2. database creation

After installing mysql, we need to build our own database. The following describes how to create a database.
In MySQL mode, run the "status" command to view the MySQL version. The MySQL version is 5.6.30 MySQL Community Server (GPL ).

Mysql commands are case-insensitive for mysql cases. The table name of the data table is not divided in windows, but in linux. The field names of data tables are not separated in windows and linux.

(1) log on to the mysql DBMS

Enter the following command in the Linux Command Line:

mysql -hlocalhost -uroot -p123456

Note: Specify the host address, user, and user password for logging on to mysql in sequence.

(2) Use show to view the databases on the current mysql Server

show databases;

Note: When you use the mysql command in mysql mode, each statement must end with a semicolon. However, use [DatabaseName] does not need to be used. It may be a small bug in mysql. Similar to the command for quit and exit to exit mysql, a semicolon is not required. (If you like, you can use a semicolon to terminate such a statement ).

(3) create a database

mysql> CREATE DATABASE StudentCourse;

(4) use the use statement to access the database

mysql>use StudentCourse;

(5) create a data table student

mysql> create table student( studentNo varchar(12) not null, name varchar(12) not null, school varchar(12) not null, grade varchar(12) not null, major varchar(12) not null, gender boolean not null, primary key(studentNo))engine=MyISAM default charset=utf8;

Observe the preceding table creation statements and pay attention to the following points:

(A) boolean is implemented in MySQL Based on tinyint (1). Here 1 refers to the shortest length of data display.
In fact, tinyint (1) is another value that can be inserted between-128 and 127. In mysql, the data type is defined by the data type (m). The meaning of the number m in different data types is different. The integer system has limited the value range. tinyint occupies one byte and int occupies four bytes. Therefore, m after an integer is not the length of the data, but the minimum length of the data displayed (the length is the number of characters ).

Tinyint (1) here 1 represents the minimum display of one character. Tinyint (2) here 2 represents the shortest display of two characters, but here setting m is meaningless, you also need to specify what to fill when the data is less than the length of m, for example, zerofill (indicating Zero Filling ). Set tinyint (2) zerofill. When you insert 1, it will display 01. Set tinyint (4) zerofill. When you insert 1, it will display 0001.

(B) Note that the engine can be used to specify the engine. If the engine statement is omitted, the default engine (MYISAM) is used ). MYSQL supports three engines: ISAM, MYISAM, and HEAP. The other two types of INNODB and BERKLEY (BDB) are also frequently used.

(C) Set the default charset to indicate the encoding method of the mysql DATA table. If the encoding method is not displayed, the default encoding method of the data table is latin1. You can also run the following command to view the data table encoding method:

show create table student;

To convert the data table encoding format, run the following command:

alter table student convert to character set utf8;

To view the encoding format of the current database:

Mysql> status; # Or show variables like 'character % ';

To verify whether the created data table is created as expected, run the following command:

# View the show columns from student; # or directly use describedescribe student;

(6) create a data table course

mysql> create table course( studentNo varchar(12) not null primary key, courseNo varchar(10) not null, hour tinyint unsigned not null, credit tinyint unsigned not null default 2 )engine=MYISAM default charset=utf8;

You can also use describe to view table information:

(7) create a data table courseSelection

mysql> create table courseSelection( id int unsigned not null auto_increment primary key, studentNo varchar(12) not null, courseNo varchar(10) not null, time timestamp not null default CURRENT_TIMESTAMP, FOREIGN KEY(studentNo) REFERENCES student(ISBN) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY(courseNo) REFERENCES course(courseNo) ON UPDATE CASCADE ON DELETE CASCADE)engine=MYISAM default charset=utf8 AUTO_INCREMENT=0;

View table information:

(8) other operation commands on databases and data tables

Delete database:

Mysql> drop database name;

Delete A data table:

Mysql> drop table name;

Clear records in the table:

Mysql> delete from table name;

References:

[1] http://www.runoob.com/mysql/mysql-data-types.html
[2] viewing, creating, and changing databases and tables in Mysql
[3] Why does tinyint (1) Insert 99 in php? Search
[4] MySQL Engine
[5] Four keys in MySQL
[6] SQL primary key and foreign key constraints
[7] What are the differences between unique indexes and unique constraints?

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!