MySQL Tutorial 1th Chapter Database Design

E-r Design

Many students are learning SQL statements, it is very difficult, it is because you are learning a database you do not know, the table in the database is not designed by you, the relationship between table and table you do not understand. So before learning SQL statements, let's introduce the database design.

The following examples illustrate database design:

Schools need to develop a system to record information about students, courses and grades. How is the database designed?

This involves two entities, student tables, and courses, which are entity tables.

What is the relationship between these tables? , students to test results, scores recorded in the score table.

A student can take multiple courses and the relationship is 1 to many.

Database Design Examples

designing Databases and Tables

Install MySQL

Run the apt-get Update command to ensure that your package list is up-to-date.

[Email protected]:~# apt-get Update

Installing the MySQL Service

[Email protected]:~ #apt-get Install Mysql-server

Set MySQL to allow remote connections

You need to change the configuration file and create a remote connection for the MySQL user.

1. Change the configuration file to allow remote connection to MySQL

The default MySQL only allows local connections, and the configuration file needs to be changed.

[Email protected]:~# vi/etc/mysql/my.cnf

Comment out the red section.

Restart MySQL Service

[Email protected]:~#/etc/init.d/mysql Restart

Turn off the firewall or open port 3306

[Email protected]:~# UFW Disable

2. Create a remote administrator

MySQL login account includes user name and computer name, such as [email protected] and [email protected] is two MySQL users, have different permissions and passwords.

Log in to MySQL

mysql> use MySQL; Switch database

View User table

Select Host,user,password from user;

Create a new MySQL administrator root to connect MySQL on the 192.168.80.% network segment,

The following command creates and authorizes the ' root ' @ ' 192.168.80.% ' user, all privileges represents all permissions, *. * represents all tables for all databases, identified by represents the user's password, with Grant option means that the user can also authorize other users.

Mysql> Grant all privileges on * * to ' root ' @ ' 192.168.80.% ' identified by ' a1! ' with GRANT option;

Run the following command to refresh the permissions

mysql> flush Privileges;

Run the following command to view, create the user

Mysql> select Host,user from user;

Connect to MySQL using administrative tools

The following operation will connect to MySQL using the [email protected]% user] created above. and create a database

Install MyManagerLiteSetup.exe on the physical machine, language select "中文版".

Prepare for MySQL learning environment

Create a table to use later in the learning process.

Copy the following code into the SQL Manager tool, check the code, press or F9 run, complete

1. Create student Tables

CREATE TABLE ' tstudent ' (

' StudentID ' varchar (not NULL),

' Sname ' varchar DEFAULT NULL,

' Sex ' char (1) DEFAULT NULL,

' CardID ' varchar DEFAULT NULL,

' Birthday ' datetime DEFAULT NULL,

' Email ' varchar (+) DEFAULT NULL,

' Class ' varchar DEFAULT NULL,

' Entertime ' datetime DEFAULT NULL

) Engine=innodb DEFAULT Charset=utf8;

2. Create a curriculum

CREATE TABLE Tsubject

(

Subjectid nvarchar (10),

Subjectname nvarchar (30),

BookName nvarchar (30),

Publisher nvarchar (20)

) Engine=innodb DEFAULT Charset=utf8;

3. Create a score table

CREATE TABLE Tscore

(

StudentID nvarchar (15),

Subjectid nvarchar (10),

Mark Decimal

) Engine=innodb DEFAULT Charset=utf8;

4. Insert Course Information

INSERT into tsubject values (' 0001 ', ' computer network ', ' groundbreaking computer network ', ' Tsinghua Press ');

INSERT into tsubject values (' 0002 ', ' data structure ', ' big talk data structure ', ' People Mail publishing house ');

INSERT into tsubject values (' 0003 ', ' Java development ', ' Java Enterprise Development ', ' e-mail publishers ')

5. Create a function This function can produce a student's name

Create function Create_name ()

RETURNS varchar (3)

Begin

DECLARE LN VARCHAR (300);

DECLARE MN VARCHAR (200);

DECLARE FN VARCHAR (200);

DECLARE Ln_n INT;

DECLARE Mn_n INT;

DECLARE Fn_n INT;

SET LN=‘李王张刘陈杨黄赵周吴徐孙朱马胡郭林何高梁郑罗宋谢唐韩曹许邓萧冯曾程蔡彭潘袁于董余苏叶吕魏蒋田杜丁沈姜范江傅钟卢汪戴崔任陆廖姚方金邱夏谭韦贾邹石熊孟秦阎薛侯雷白龙段郝孔邵史毛常万顾赖武康贺严尹钱施牛洪龚‘;

SET mn= ' Wei Gangyong spring Chrysanthemum Yi June Feng Strong army ping Bao Dong Wenhui Liming zhi na yan Yun Rong garden xu Qingcong pure yu Yue Zhao ice cool wan tea feather Hinningxin fluttering ying fu new Liyun soft bamboo mist ning Xiao Huan Feng Yun phenanthrene Cold oia Keishuyi Shinlanghai you Fossengrong National sheng Learning Xiang Liang Zheng Stonehenge solid of the LAN Yuan Fushunshin kwantip Tao Chang into Kang Starlight Tian da Ann Rock in Mao into the forest and Puma first Jingzhen Zhuang will think group Hao Qing fei Naging cui ya zhi shes autumn sansa jin Dai Green Qian Ting Jiao Wan Xian Jing Ying Yao Yi Chan Yan ceruse instrument Dan Rong Eyebrow June Qin Rui Wei Ching Meng su Wei just courageous June Feng strong army ping Bao Dong Wenhui Liming zhi Yuchenliang Renponin you Fossengrong national sheng Learn xiang just hair Vuniuli fei Fushunshin kwantip Tao Chang into Kang Star Day da-an rock in Mao into the forest and Puma first to the earthquake vibration strong will think group Hao Heart State Lechaugong pine good thick qing lei min Yu Jiang Shuhao Liang Stonehenge solid round John Lamberhon words if Ming Peng present Witchkelen Xiang Xu Peng ze Chen Shi to build home to the tree inflammation when Thai Shengxiung June Crown policy Teng Nan Banyan wind and Air Hong ';

SET fn= ' Wei just courageous June Yun Lianjin ring snow rong Love sister Xia Xiang month Ying Yuan Yan Rui van Jia Jia Jong Qin zhen Guidi Ye Shi just hair Wulilin round John Lamberhon say if the Ming Peng present Witchkelen Xiang Xu Chen Shi build home-induced tree inflammation Dehe Jiang Shuhao lu ya Crystal Yue Hwa Qiao Mei Jie Xin litchi think Heart State Cheng Lechaugong pine good thick qing lei people friends Jade Ping red Ah Ling fragrance yan Choi Lan Fengjie Meshujuan the Thai Shengxiong Total June crown Policy Teng nan Banyan Wind Hong Feng strong army ping Bao Dong Wenhui Liming zhi Yuchenliang Renponin Fossengrong National sheng Learn xiang just hair Vuniuli fly bin Fushunshin kwantip Tao Chang into Kang star Day da ' an Yan Jin Lin Jian and Puma first to the earthquake vibration strong will think group Hao Heart State Lechaugong pine good thick qing lei min Yu Jiang Shuhao Liang Zheng Stonehenge fixed round John Lamberhon said if the Ming friend bin Present Witchkelen Xiang Xu Peng ze Chen Shi to build home to the tree inflammation when the Thai Shengxiung June Crown policy Teng Nan Banyan wind and Air Hong ';

SET ln_n=char_length (LN);

SET mn_n=char_length (MN);

SET fn_n=char_length (FN);

Return Concat (SUBSTRING (Ln,ceil (rand () *ln_n), 1), SUBSTRING (Mn,ceil (rand () *mn_n), 1), SUBSTRING (Fn,ceil (rand () *fn_n ), 1));

End

6. Create a stored procedure that adds students

CREATE PROCEDURE addstudent (in num int)

Begin

declare i int;

Set I=1;

Delete from tstudent;

While Num>=i do

Insert Tstudent VALUES (

Lpad (CONVERT (I,char (5)), 5, ' 0 '),

Create_name (),

if (Ceil (rand () *10)%2=0, ' Male ', ' female '),

Rpad (Convert ceil (rand () *1000000000000000000), char (18)), 18, ' 0 '),

Concat (' 198 ', CONVERT (Ceil (rand () *10), char (1)), '-', Lpad (CONVERT (Ceil (rand () *12), char (2)), 2, ' 0 '), '-', Lpad ( CONVERT (Ceil (rand () *30), char (2)), 2, ' 0 ')),

Concat (PINYIN (sname), ' @hotmail. com '),

Case Ceil (rand ()) while 1 then ' web and Web development ' when 2 Then ' JAVA ' ELSE ' NET ' END,

Now ());

Set i=i+1;

End while;

SELECT * from Tstudent;

End

7. Create a function of Chinese pinyin to generate the user's mailbox

Table used to create the function of Chinese pinyin

CREATE TABLE ' Pinyin ' (

' Letter ' char (1) is not NULL,

' Chinese ' char (1) Not NULL,

PRIMARY KEY (' letter ')

) Engine=myisam DEFAULT CHARSET=GBK;

Inserting data

INSERT into ' Pinyin ' VALUES (' A ', ' 驁 '), (' B ', ' book '), (' C ', ' wrong '), (' D ', ' 鵽 '), (' E ', ' 樲 '), (' F ', ' 鰒 '), (' G ', ' hiker '),

(' H ', ' inceѕt '), (' J ', ' 攈 '), (' K ', ' 穒 '), (' L ', ' 鱳 '), (' M ', ' temperature '), (' N ', ' 桛 '), (' O ', ' 漚 '), (' P ', ' exposure '), (' Q ', ' 囕 '), (' R ', ' 鶸 '),

(' S ', ' 蜶 '), (' T ', ' 籜 '), (' W ', ' clamoring '), (' X ', ' 鑂 '), (' Y ', ' Wan Leng '), (' Z ', ' n ');

Functions for creating Chinese characters to Pinyin

CREATE FUNCTION PINYIN (str CHAR (255))

RETURNS Char (255)

BEGIN

DECLARE Hexcode char (4);

DECLARE Pinyin varchar (255);

DECLARE Firstchar char (1);

DECLARE Achar char (1);

DECLARE POS int;

DECLARE Strlength int;

SET Pinyin = ';

SET strlength = Char_length (LTRIM (RTRIM (str)));

SET pos = 1;

SET @str = (CONVERT (str USING GBK));

While POS <= strlength do

SET @aChar = SUBSTRING (@str, pos,1);

SET Hexcode = HEX (@aChar);

IF hexcode >= "8140" and Hexcode <= "FEA0" Then

SELECT Letter to Firstchar

from pinyin

WHERE Chinese >= @aChar

LIMIT 1;

ELSE

SET Firstchar = @aChar;

END IF;

SET Pinyin = CONCAT (Pinyin,firstchar);

SET pos = pos + 1;

END while;

RETURN UPPER (pinyin);

END

8. Create a stored procedure that inserts student scores between 50-100 and a student's score

CREATE PROCEDURE Fillsore ()

Begin

DECLARE St_num INT;

DECLARE Sb_num INT;

DECLARE I1 INT;

DECLARE I2 INT;

Set i1=1;

Set i2=1;

Delete from Tscore;

Select COUNT (*) into st_num from Tstudent;

Select COUNT (*) into sb_num from Tsubject;

While St_num>=i1 do

Set i2=1;

While Sb_num>=i2 do

Insert Tscore values

(Lpad (CONVERT (I1,char (5)), 5, ' 0 '), Lpad (CONVERT (I2,char (4)), 4, ' 0 '), Ceil (50+rand () *50));

Set i2=i2+1;

END while;

Set i1=i1+1;

END while;

End

9. Insert 1000 students

Call Addstudent (1000)

SELECT * FROM Tstudent

10. Insert Scores

Call Fillsore ()

11. View the results of the three-sheet connection

Select a.*,b.*,c.* from Tstudent a joins Tscore B on A.studentid=b.studentid joins Tsubject C on B.subjectid=c.subjectid

Setting the MySQL character set

The Chinese-enabled character set is UTF8, which can change the MySQL configuration file for global settings or for database settings or for table settings or for columns. After the character set changes, the newly inserted data takes effect, not previously.

Exercise: Change the default character set for MySQL

Directly modify the MY.CNF, add a line of content, and then restart MySQL to make it effective globally, affecting all new databases.

Default-character-set = UTF8

Restart MySQL Service

[Email protected]:~#/etc/init.d/mysql Restart

View the global default character set

To view all supported character sets

Mysql> show Character set;

Exercise: Specifying a character set for a database

After you specify the database character set, the tables created under the database use the character set specified by the library by default.

1. When creating a database, specify its character set

CREATE DATABASE db DEFAULT CHARSET UTF8;

2, or use ALTER syntax to convert a field's character set

ALTER DATABASE db DEFAULT CHARSET UTF8;

You can also specify a character set for a database using the graphical management interface

Exercise: Specifying a character set for a data table

When you specify a data table character set, all character fields in the datasheet use the character set specified by the table by default.

1. When creating a data table, specify its character set

CREATE TABLE TBL

(

......

) ENGINE = MyISAM DEFAULT CHARSET UTF8;

2, or use ALTER syntax to convert a field's character set

ALTER TABLE tbl CONVERT to CHARACTER SET UTF8;

3, using other client tools to set, such as Navicat/mysql front/phpmyadmin

Exercise: Specifying a character set for a field

Although the default character set is specified when the data table is created, the fields inside the table can also specify their own character set.

1. When creating a data table, specify its character set

CREATE TABLE TBL

(

......

Name CHAR (CHARACTER) SET UTF8,

......

) ENGINE = MyISAM DEFAULT CHARSET UTF8;

2, or use ALTER syntax to convert a field's character set

ALTER TABLE tbl Change name name CHAR (CHARACTER) SET UTF8;

3, using other client tools to set, such as Navicat/mysql front/phpmyadmin

Exercise: Setting the client character set

Specifies the character set for client connections, preferably the character set of the server.

Setting the Putty character set

View name is garbled

Click "Apply".

MySQL Storage engine

MySQL Storage engine overview

Plug-in storage engine is one of the most important features of MySQL database, users can choose how to store and index database, use things and so on according to the needs of the application. MySQL supports a variety of storage engines by default to suit the needs of different domains of database applications. Users can use different storage engines to improve the efficiency of their applications, provide flexible storage, and user settings to customize and use their own storage engine to achieve maximum customization, as needed.

The common storage engine for MySQL is MyISAM, InnoDB, memory, MERGE, where InnoDB provides transaction security tables, and other storage engines are non-transactional security tables.

MyISAM is the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, but it has fast access and no requirement for transactional integrity.

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared with the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes. MySQL supports the foreign key storage engine only InnoDB, when creating foreign keys, requires that the schedule must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.

Some features of the storage engine

The four storage engines mentioned above have their respective environments, depending on their unique characteristics. Mainly embodied in performance, transaction, concurrency control, referential integrity, caching, failure recovery, backup and back-up, and several other aspects

At present, the more popular storage engine is MyISAM and InnoDB. And MyISAM is the first choice for most Web applications. The main difference between MyISAM and InnoDB is in performance and transaction control.

MyISAM is an early ISAM (Indexed sequential Access Method, I'm using an extended implementation of MySQL5.0 that doesn't support ISAM anymore, and ISAM is designed to handle a situation where read frequency is much larger than write frequency, so ISAM and later MyISAM do not consider support for things, do not need transaction records, ISAM query efficiency is considerable, and memory consumption is very small. MyISAM, while inheriting this kind of advantage, has provided a lot of practical new features and related tools with the times. For example, given concurrency control, table-level locks are provided. And because MyISAM is each table using its own separate storage files (myd data files and myi index files), it makes backup and recovery very convenient (copy overwrite), but also supports online recovery.

So if your application does not require transactions, foreign keys are not supported. Only the basic crud (add-and-revise) operation is handled, then MyISAM is the choice.

1. Set the default storage engine for MySQL

Edit the MY.CNF configuration file and add it under Server-side configuration information [mysqld]:

Default-storage-engine = MyISAM

The default engine for setting up the MySQL database is MyISAM

2. Specify the storage engine for the table

Such as:

CREATE TABLE T_innodb (ID int (3))

engine = InnoDB;

Then use

Mysql> Show Table status like ' T_innodb ';

View the details of the table.

3. Modify the table's storage engine

ALTER TABLE t_name ENGINE = InnoDB;

Exercise: Change the storage engine for the default storage engine and table

1. Change the default storage engine

View the default storage engine

Change the default storage engine

Editing a configuration file

Add under [MySQL]

Default-storage-engine = InnoDB

Review the default engine again to see that it has been changed

Restart MySQL Service

2. More Table Storage Engine

Double-click a table to change the storage engine for the table

Using the command to change

mysql> use SCHOOLDB;

mysql> ALTER TABLE tstudent ENGINE=INNODB;

Using Administrative tools to change

Features of InnoDB

1. Auto-Grow column

The auto-grow column can be sent to the insert, but if it is empty or 0, the actual inserted value is the automatically growing value.

Create a table that specifies the auto-grow column, the storage engine InnoDB.

CREATE TABLE AU

(

StudentID int NOT NULL auto_increment,

Name varchar (10),

Primary KEY (StudentID)

)

Engine=innodb CHARACTER SET UTF8,

Insert record with null value and self-increment column not in order

Insert AU values (1, ' Han Li just '), (3, ' Han Lihui '), (2, ' Zhangjing '), (null, ' Yang Shuai ')

You can see the value of the self-increment column

select * FROM AU

FOREIGN KEY constraints

MySQL supports the foreign key storage engine only InnoDB, when creating foreign keys, requires the parent table must have the corresponding index, the child table will create the foreign key when the index is automatically created

When you delete an updated parent table, the child tables are appropriately manipulated, including restrict, Cascade, set NULL, and no action

Using InnoDB storage engine data in primary key order

If you do not create an index, the data is stored in the insert order

CREATE TABLE Au1

(

StudentID int,

Name varchar (10),

Primary KEY (StudentID)

)

Engine=innodb

Insert AU1 VALUES (1, ' Han Li just '), (3, ' Han Lihui '), (2, ' Zhangjing '), (8, ' Yang Shuai '), (6, ' yangliuqing ')

SELECT * FROM AU1

Creating databases and Tables

Exercise: Creating a Database

You can also use commands to create a database

Enter the following command to create database schooldb; Select the Execute

Exercise: Create a table

Select the database you just created to execute the statement that created the table

Create student Tables

CREATE TABLE Tstudent

(StudentID nvarchar (15),

Sname nvarchar (10),

Sex nchar (1),

CardID nvarchar (20),

Birthday datetime,

Email nvarchar (40),

Class nvarchar (20),

Entertime datetime)

Create a curriculum

CREATE TABLE Tsubject

(

Subjectid nvarchar (10),

Subjectname nvarchar (30),

BookName nvarchar (30),

Publisher nvarchar (20)

)

Create a score table

CREATE TABLE Tscore

(

StudentID nvarchar (15),

Subjectid nvarchar (10),

Mark Decimal

)

Exercise: Generating a table's SQL statement using the graphical interface

Use the graphical interface to generate SQL statements that create, delete, and change objects.

Using MySQL Help

View MySQL Help

If you don't know what help can offer? input

? Contents

You can view the available Help

Input? Data definition

To view the syntax for creating a table

Quick Check Help

In real-world applications, you can use keywords for quick queries if you need to see a grammar quickly. For example, if you want to know what the show command can see, you can use the following command.

View Database

To view the commands for creating a database

View commands for creating tables

MySQL Tutorial 1th Chapter Database Design