Shulou(Shulou.com)06/01 Report--

MySQL Database Foundation (4)-- MySQL Database creation example 1, Database 1, Database creation

Create a database and specify 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 student table (student), course table (TSubject) and fractional table (TScore).

2. Create a student form 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`TStudent` DEFAULT NULL, `Class` varchar (20) DEFAULT NULL, `enterTime` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;3, create a course schedule create table TSubject (subJectID nvarchar (10), subJectName nvarchar (30), BookName nvarchar (30), Publisher nvarchar (20) ENGINE=InnoDB DEFAULT CHARSET=utf8 4. Create score table create table TScore (StudentID nvarchar (15), subJectID nvarchar (10), mark decimal) ENGINE=InnoDB DEFAULT CHARSET=utf8; II, generate database information 1, insert course schedule information insert into TSubject values ('0001Zhi Zhi' computer network', 'foundation computer network', 'Tsinghua Publishing House'); insert into TSubject values ('0002Zhi' data structure', 'big talk data structure', 'people's Post Press') Insert into TSubject values, JAVA Enterprise Development, people's Mail Publishing House, 2. Create the function create function CreateName () RETURNS varchar (3) beginDECLARE LN VARCHAR (300); DECLARE MN VARCHAR (500); DECLARE FN VARCHAR (500); DECLARE LN_N INT;DECLARE MN_N INT;DECLARE FN_N INT SET LN=' Li Wang Zhang Liu Chen Yang Huang Zhao Wu Xu Sun Zhu Ma Hu Guo Lin he Gaoliang Zheng Luo Song Xie Tang Cao Xu Deng Xiao Feng Zeng Cheng Cai Pengpan Yuan Yu Yu Su Ye Lu Wei Jiang Tian du Jiang Fan Jiang Fu Zhong Lu Wang Dai Cui Ren Lu Liao Yao Fang Jin Qiu Weijia Zou Shixiong Meng Qin Yan Xue Hou Lei Bai long Duan Kong Shao Shi Mao Chang Wan Gu Wu Kang he Yan Yin Qian Shi Niu Hong Gong SET MN=' Wei Gangyong Chunju Yi Junfeng Qiangjun Pingbaodong Wen Huili Ming Yongjian Shiguang Zhi Yun Ying Qing Jing Qing Chun Yue Zhao Bing Shuo Xin Yu Xi Ning Xin Fu Xin Li Jun soft Bamboo mist Huanxiao Feng Yun Yi Shu Yi Xingliang Haishan Renbo Ninggui Longyuan National Sheng School Heng Qi Yuan Fu Shun Zi Jie Tao Chang Cheng Kang Xing Tian Xing Mao Jin Lin you Jian he Puma Bocheng will first respect the shock and invigorate the club, Haoqing, Na Jing, Huizhu, Cui Yazhi, Yan Xi, Qiushan, Sha Jin, Daiqing, Qianting, Jiao Wan, Jin Ying, Yao Yi, Yi Bei, Yi Bei, he Dan Rong Mei, Junjun, Wei Gong Meng, Wei Gong, Yi Junfeng, strong Army, Pingbaodong, Wen Huili, Ming Yongjian, Shiguang Zhiyi, Xingliang, Haishan Renfu, Ninggui Xuexiang, National Shengli, Qingfeibin, Fushun Xinzi, Chengkangxing Guangtianda, Anyan Zhongmao. Lin Youjian and puma Bocheng will be proud of the group of people, Shaoshan, Songshan, Zhe Jiang, Chao Liang, Hengqi, Hengqi, the wheel of John Lambert said that if Ming Pengbin Liang Dong Weiqi Klun Xiang Xu Peng Chen Chen Shi to build a family to cause tree inflammation, when Tae Sheng Xiong Jun Teng Nan Hang Hong Hong' SET FN=' Wei Gangyong Yijun Yunlian Zhenhuan Xuerong Love Mei Xia Xiangyue Yingyue Yan Rui Fan Jia Jia Qiong Qin Zhen Zhen Li Guidi Ye Bi sent Wu Lilin Lunhan Langbo said if Ming Pengbin Liang Dong Weiqi Karen Xiang Xu Peng Chen Shi to build a tree Yandehe Zhe Jiang Super Hao Lu Qi Jing Hui Qiao Meijie Litchi Sixin Bang Chengle Shaogong Shan Qinglei people's Friends Jade Ping Honge Fang Fang Lan Fengjie Mei Xiujuan's heroic conduct Shi Tai Sheng Xiongchen Jun Guan Teng Nanrong Fenghang Hongfeng strong Army Ping Baodong Wen Hui Li Ming Yongjian Shiguang Zhiyi Xingliang Haishan Renbo Ningguifu Sheng long Sheng Xuexiang just launched the new force Qingfeibin Fu Shun Xinzi Jie Tao Chang Cheng Kang Xing Xing Da an Yan Mao Lin Youjian and Pu Bocheng Xianjing Zhenzheng Haoxin Bang Chengle Songshan Hou Qing Lei Min Youhe Zhe Jiang Haoliang Henggu the wheel of Hanlang Henggu If Ming Pengbin Liang Dong Weiqi Karen Xiang Xu Peng Chen Chen Shi to build a home to cause tree inflammation when Tai Sheng Xiong Jun Guan Zhi Tengnan Rong Feng Hang 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)); end3, create a stored procedure create procedure addStudent (in num int) begindeclare I int;set item1 While num > = i doinsert TStudent values (LPAD (convert (iPowerchar (5)), 5djime (4)), CreateName (), if (ceil (rand () * 10)% 2)), RPAD (convert (ceil (rand () * 10000000000000000), char (18)), 18Power0'), Concat (convert (ceil (rand () * 10) + 1980),'-', LPAD (ceil (rand () * 12), char (2)) 2case ceil (rand () * 28),'-', LPAD (ceil (rand () * 28), char (2), 2)), Concat (PINYIN (sname),'@ hotmail.com'), case ceil (rand () * 3) when 1 then 'network and website development' when 2 then 'JAVA' ELSE' NET' END, NOW ()) Set iTunes 1. End while;select * from TStudent;End

Insert 1000 student records.

Call addStudent (1000)

4. Create the function of converting Chinese characters to pinyin.

-- create a table for the function of converting Chinese characters to pinyin

CREATE TABLE `pinyin` (`letter` char (1) NOT NULL, `chinese` char (1) NOT NULL, PRIMARY KEY (`letter`)) ENGINE=MyISAM DEFAULT CHARSET=gbk

-- insert data

INSERT INTO `pinyin` VALUES ('apocrycology'), ('bauhindral'), ('Cruise pencils'), ('Dracula'), ('Dracula'), ('Fleming'), (''Ganzhuo'), ('Hafei'), (''jungle''), ('Kraft'), ('Lindsay'), (''Maure.''), (''N') ), ('Olympiad'), ('Olympiad'), ('PUBG' exposure'), ('QLY'), ('RPM'), ('SJM'), ('TLY'), ('WWH'), ('XLY'), ('YYZ'), ('ZLY') CREATE FUNCTION PINYIN (str CHAR (255)) RETURNS char (255BEGINDECLARE 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 = 1x set @ str = (CONVERT (str USING gbk)); WHILE pos = "8140" AND hexCode = @ aChar LIMIT 1; ELSE SET firstChar = @ aChar; END IF SET pinyin = CONCAT (pinyin,firstChar); SET pos = pos + 1 beginDECLARE St_Num INT;DECLARE Sb_Num INT;DECLARE end WHILE; RETURN UPPER (pinyin); END5, create the stored procedure create procedure fillScore () beginDECLARE St_Num INT;DECLARE Sb_Num INT;DECLARE i1 INT;DECLARE i2 INT;set i1 insert student grades While Sb_Num > = i2 doinsert TScore values (LPAD (convert (i1jinchar (5)), 5memento 0'), LPAD (convert (i2jingchar (4)), 4memorial0'), ceil (50+rand () * 50)); set i2memi1eEND WHILE;set i1flagi1nd end WHILE;End

Insert student scores

Call fillScore ()

III. Inspection of students' score sheets

Select a. Magic, B. C., from TStudent a join TScore b on a.studentid=b.studentid join TSubject c on b.subjectid=c.subjectid limit 50

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.