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