MySQL Database Foundation (iv)--MYSQL database creation Instance

MySQL Database Basics (iv)--MYSQL database Create instance one, CREATE database 1, CREATE database

Create the database, specifying that the default character set for the database is UTF8.
Create DATABASE schooldb default character set UTF8;
To connect to the database, the client must select the UTF8 character set.
The three tables in the database are the Student table (student), the course table (Tsubject), and the score table (Tscore).

2. Create student Tables

CREATE TABLE `TStudent` (  `StudentID` varchar(15) NOT NULL,  `Sname` varchar(10) DEFAULT NULL,  `sex` char(1) DEFAULT NULL,  `cardID` varchar(20) DEFAULT NULL,  `Birthday` date DEFAULT NULL,  `Email` varchar(40) DEFAULT NULL,  `Class` varchar(20) DEFAULT NULL,  `enterTime` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Create a curriculum

create table TSubject(subJectID nvarchar(10),subJectName nvarchar(30),BookName nvarchar(30),Publisher nvarchar(20))ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Create a score table

create table TScore(StudentID nvarchar(15),subJectID nvarchar(10),mark decimal)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Second, the database information Generation 1, insert curriculum information

insert into TSubject values (‘0001‘,‘计算机网络‘,‘奠基计算机网络‘,‘清华出版社‘);insert into TSubject values (‘0002‘,‘数据结构‘,‘大话数据结构‘,‘人邮出版社‘);insert into TSubject values (‘0003‘,‘JAVA开发‘,‘JAVA企业级开发‘,‘人邮出版社‘);

2. Create a function that produces the student's name

Create function Createname () RETURNS varchar (3) begindeclare LN varchar;D eclare MN varchar ($);D eclare FN varchar ( ;D eclare 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 

3. Create a stored procedure that adds students

create procedure addStudent(in num int)begindeclare i int;set i=1;delete from TStudent;while num>=i doinsert TStudent values (       LPAD(convert(i,char(5)),5,‘0‘),       CreateName(),       if(ceil(rand()*10)%2=0,‘男‘,‘女‘),       RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,‘0‘),Concat(convert(ceil(rand()*10)+1980,char(4)),‘-‘,LPAD(convert(ceil(rand()*12),char(2)),2,‘0‘),‘-‘,LPAD(convert(ceil(rand()*28),char(2)),2,‘0‘)),       Concat(PINYIN(sname),‘@hotmail.com‘),       case ceil(rand()*3) when 1 then ‘网络与网站开发‘ when 2 then ‘JAVA‘ ELSE ‘NET‘ END,       NOW());set i=i+1;end while;select * from TStudent;End

Insert a 1000 student record.
Call Addstudent (1000);

4. The function of creating Chinese pinyin

--the table used to create the function of Chinese pinyin

CREATE TABLE `pinyin` (  `letter` char(1) NOT NULL,  `chinese` char(1) NOT NULL,  PRIMARY KEY  

--Insert 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 '); CREATE FUNCTION PINYIN (str char (255)) RETURNS char (255) Begindeclare Hexcode char (4);D eclare PINYIN varchar (255);D Eclare Firstchar char (1);D eclare Achar char (1);D eclare 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 wher    E 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

5. Create a stored procedure that inserts student scores

create procedure fillScore()beginDECLARE 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 doset i2=1;while Sb_Num>=i2 doinsert 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

Insert Student Scores
Call Fillscore ();

Third, the Student score table view

select a.*,b.*,c.* from TStudent a join TScore b on a.studentid=b.studentid join TSubject c on b.subjectid=c.subjectid limit 50;

MySQL Database Foundation (iv)--MYSQL database creation Instance