莱州市建设局网站,新农村基础设施建设网站,免费网站建站有哪些,用图片设置网站首页E-R设计很多同学在学SQL语句时#xff0c;觉得非常困难#xff0c;那是因为你在学一个你根本不了解的数据库#xff0c;数据库中的表不是你设计的#xff0c;表与表之间的关系你不明白。因此在学SQL语句之前#xff0c;先介绍一下数据库设计。下面举例说明数据库设计…E-R设计很多同学在学SQL语句时觉得非常困难那是因为你在学一个你根本不了解的数据库数据库中的表不是你设计的表与表之间的关系你不明白。因此在学SQL语句之前先介绍一下数据库设计。下面举例说明数据库设计学校需要开发一个系统记录有学生、课程和成绩信息。数据库如何设计这里面涉及到两个实体学生表、课程这些表为实体表。这些表之间有什么关系呢、学生考试出成绩成绩记录在成绩表。一个学生可以参加多门课程关系是1对多。数据库设计实例设计数据库和表安装mySQL运行apt-get update命令以确保您的软件包列表是最新的。rootubuntuServer:~# apt-get update安装mySQL服务rootubuntuServer:~#apt-get install mysql-server设置mySQL允许远程连接需要更改配置文件和创建远程连接的mySQL用户。1. 更改配置文件允许远程连接mySQL默认mySQL只允许本地连接需要更改配置文件。rootubuntuServer:~# vi /etc/mysql/my.cnf注释掉红色部分。重启mySQL服务rootubuntuServer:~# /etc/init.d/mysql restart关闭防火墙或打开3306端口rootubuntuServer:~# ufw disable2. 创建远程管理员mySQL的登录账户包括用户名和计算机名组成比如root127.0.0.1和rootlocalhost是两个mySQL用户有不同的权限和密码。登录mySQLmysql use mysql; 切换数据库查看user表Select host,user,password from user;创建新mySQL管理员root在192.168.80.%网段连接mySQL以下命令创建并授权root192.168.80.%用户all privileges代表所有权限*.*代表所有数据库所有表identified by 代表该用户的密码with grant option代表该用户还可以授权其他用户。mysql grant all privileges on *.* to root192.168.80.% identified by a1! with grant option;运行以下命令刷新权限mysql flush privileges;运行以下命令查看创建的用户mysql select host,user from user;使用管理工具连接mySQL以下操作连接将会使用以上创建的root192.168.80.%用户连接mySQL。并创建数据库在物理机上安装MyManagerLiteSetup.exe语言选择“English”。准备mySQL学习环境创建以后学习过程中用到的表。将以下代码复制到SQL Manager工具中选中代码按或F9运行完成1. 创建学生表CREATE TABLE TStudent (StudentID varchar(15) NOT NULL,Sname varchar(10) DEFAULT NULL,sex char(1) DEFAULT NULL,cardID varchar(20) DEFAULT NULL,Birthday datetime DEFAULT NULL,Email varchar(40) DEFAULT NULL,Class varchar(20) DEFAULT NULL,enterTime datetime DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETutf8;2. 创建课程表create table TSubject(subJectID nvarchar(10),subJectName nvarchar(30),BookName nvarchar(30),Publisher nvarchar(20))ENGINEInnoDB DEFAULT CHARSETutf8;3. 创建分数表create table TScore(StudentID nvarchar(15),subJectID nvarchar(10),mark decimal)ENGINEInnoDB DEFAULT CHARSETutf8;4. 插入课程信息insert into TSubject values (0001,计算机网络,奠基计算机网络,清华出版社);insert into TSubject values (0002,数据结构,大话数据结构,人邮出版社);insert into TSubject values (0003,JAVA开发,JAVA企业级开发,人邮出版社)5. 创建函数 该函数能够产生学生姓名create function create_name()RETURNS varchar(3)beginDECLARE 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伟刚勇春菊毅俊峰强军平保东文辉力明永健世广志瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥新利筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒义兴良海山仁波宁贵福生龙元全国胜学祥亮政谦亨奇固之岚苑富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪清飞彬娜静淑惠珠翠雅芝妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦素伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘;SET FN伟刚勇毅俊云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧才发武丽琳轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德河哲江超浩璐娅琦晶裕华慧巧美婕馨影荔枝思心邦承乐绍功松善厚庆磊民友玉萍红娥玲芬芳燕彩兰凤洁梅秀娟英行时泰盛雄琛钧冠策腾楠榕风航弘峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘;SET LN_NCHAR_LENGTH(LN);SET MN_NCHAR_LENGTH(MN);SET FN_NCHAR_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));end6. 创建添加学生的存储过程create procedure addStudent(in num int)begindeclare i int;set i1;delete from TStudent;while numi doinsert TStudent values (LPAD(convert(i,char(5)),5,0),create_name(),if(ceil(rand()*10)%20,男,女),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()*3) when 1 then 网络与网站开发 when 2 then JAVA ELSE NET END,NOW());set ii1;end while;select * from TStudent;end7. 创建汉字转拼音的函数用来产生用户用户的邮箱创建汉字转拼音的函数使用的表CREATE TABLE pinyin (letter char(1) NOT NULL,chinese char(1) NOT NULL,PRIMARY KEY (letter)) ENGINEMyISAM DEFAULT CHARSETgbk;插入数据INSERT INTO pinyin VALUES (A,驁),(B,簿),(C,錯),(D,鵽),(E,樲),(F,鰒),(G,腂),(H,夻),(J,攈),(K,穒),(L,鱳),(M,旀),(N,桛),(O,漚),(P,曝),(Q,囕),(R,鶸),(S,蜶),(T,籜),(W,鶩),(X,鑂),(Y,韻),(Z,咗);创建汉字转拼音的函数CREATE FUNCTION PINYIN(str CHAR(255))RETURNS char(255)BEGINDECLARE 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 DOSET aChar SUBSTRING(str,pos,1);SET hexCode HEX(aChar);IF hexCode 8140 AND hexCode FEA0 THENSELECT letter into firstCharFROM pinyinWHERE chinese aCharLIMIT 1;ELSESET firstChar aChar;END IF;SET pinyin CONCAT(pinyin,firstChar);SET pos pos 1;END WHILE;RETURN UPPER(pinyin);END8. 创建插入学生成绩的存储过程 学生成绩在50-100分之间create procedure fillSore()beginDECLARE St_Num INT;DECLARE Sb_Num INT;DECLARE i1 INT;DECLARE i2 INT;set i11;set i21;delete from TScore;select count(*) into St_Num from TStudent;select count(*) into Sb_Num from TSubject;while St_Numi1 doset i21;while Sb_Numi2 doinsert TScore values(LPAD(convert(i1,char(5)),5,0),LPAD(convert(i2,char(4)),4,0),ceil(50rand()*50));set i2i21;END WHILE;set i1i11;END WHILE;end9. 插入1000个学生call addStudent(1000)select * from TStudent10. 插入成绩call fillSore()11. 查看三张表连接的结果select a.*,b.*,c.* from TStudent a join TScore b on a.studentidb.studentid join TSubject c on b.subjectidc.subjectid设置mySQL字符集支持中文的字符集是utf8该设置可以更改mySQL配置文件进行全局设置也可以针对数据库设置也可以针对表设置也可以针对列设置。字符集更改后新插入的数据生效对以前不生效。练习更改MySQL的默认字符集直接修改 my.cnf增加一行内容然后重启 MySQL使之全局生效影响新建的所有数据库。default-character-set utf8重启mySQL服务rootubuntuServer:~# /etc/init.d/mysql restart查看全局默认字符集查看支持的所有字符集mysql show character set;练习为一个数据库指定字符集指定数据库字符集后该数据库下创建的表默认都采用该库指定的字符集。1、创建数据库时指定其字符集CREATE DATABASE db DEFAULT CHARSET UTF8;2、或者采用 ALTER 语法来转换字段的字符集ALTER DATABASE db DEFAULT CHARSET UTF8;使用图形管理界面也可以为数据库指定字符集练习为 一个数据表指定字符集指定数据表字符集后该数据表里的所有字符型字段默认都使用该表指定的字符集。1、创建数据表时指定其字符集CREATE TABLE tbl(......) ENGINE MyISAM DEFAULT CHARSET UTF8;2、或者采用 ALTER 语法来转换字段的字符集ALTER TABLE tbl CONVERT TO CHARACTER SET utf8;3、采用其他客户端工具来设定例如 Navicat/MySQL Front/PhpMyAdmin练习为一个字段指定字符集尽管数据表创建时已经指定默认字符集了但是该表里面的字段还是可以指定自己的字符集的。1、创建数据表时指定其字符集CREATE TABLE tbl(......name CHAR(20) CHARACTER SET UTF8,......) ENGINE MyISAM DEFAULT CHARSET UTF8;2、或者采用 ALTER 语法来转换字段的字符集ALTER TABLE tbl CHANGE name name CHAR(20) CHARACTER SET utf8;3、采用其他客户端工具来设定例如 Navicat/MySQL Front/PhpMyAdmin练习设定客户端字符集为客户端连接指定字符集最好服务器的字符集一致。设置putty字符集查看姓名是乱码点击“应用”。MySQL存储引擎MySQL存储引擎概述插件式存储引擎是MySQL数据库最重要的特性之一用户可以根据应用的需要选择如何存储和索引数据库是否使用事物等。mySQL默认支持多种存储引擎以适应不同领域的数据库应用需要。用户可以通过选择使用不同的存储引擎提高应用的效率提供灵活的存储用户设置可以按照自己的需要定制和使用自己的存储引擎以实现最大程度的可定制性。MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE其中InnoDB提供事务安全表其他存储引擎都是非事务安全表。MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键但其访问速度快对事务完整性没有要求。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。MySQL支持外键存储引擎只有InnoDB在创建外键的时候要求附表必须有对应的索引子表在创建外键的时候也会自动创建对应的索引。存储引擎各自的一些特点上面提到的四种存储引擎都有各自适用的环境这取决于它们独有的一些特征。主要体现在性能、事务、并发控制、参照完整性、缓存、 故障恢复备份及回存等几个方面目前比较普及的存储引擎是MyISAM和InnoDB.而MyISAM又是绝大部分Web应用的首选。MyISAM与InnoDB的主要的不同点在于性能和事务控制上。MyISAM是早期ISAM(Indexed Sequential Access Method,我现在用的MySQL5.0已经不支持ISAM了)的扩展实现ISAM被设计为适合处理读频率远大于写频率这样一种情况因此ISAM以及后来的MyISAM都没有考虑对事物的支持不需要事务记录ISAM的查询效率相当可观而且内存占用很少。MyISAM在继承了这类优点的同时与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制提供了表级锁。而且由于MyISAM是每张表使用各自独立的存储文件(MYD数据文件和MYI索引文件)使得备份及恢复十分方便(拷贝覆盖即可)而且还支持在线恢复。所以如果你的应用是不需要事务不支持外键。处理的只是基本的CRUD(增删改查)操作那么MyISAM是不二选择。1. 设置mysql的默认存储引擎编辑my.cnf配置文件在服务器端配置信息[mysqld]下面添加default-storage-engine MyISAM即可设置mysql数据库的默认引擎为MyISAM2. 指定表的存储引擎如create table t_innodb( id int(3))engine innodb;然后使用mysql show table status like t_innodb;查看表的详细信息。3. 修改表的存储引擎ALTER TABLE t_name ENGINE innodb;练习更改默认存储引擎和表的存储引擎1. 更改默认存储引擎查看默认存储引擎更改默认存储引擎编辑配置文件在[mysql]下添加default-storage-engine InnoDB再次查看默认引擎发现已经被更改重启mySQL服务2. 更表的存储引擎双击某个表可以更改表的存储引擎使用命令更改mysql use schoolDB;mysql alter table TStudent engineInnoDB;使用管理工具更改InnoDB存擎的特点1. 自动增长列自动增长列可以送给插入但是插入的如果是空或者为0则实际插入的值是自动增长后的值。创建一个表指定自动增长列存储引擎innoDB。create table au(studentid int not null auto_increment,name varchar(10),primary key(studentid))engineinnodb CHARACTER SET UTF8,插入记录有空值且自增列也没按顺序insert au values (1,韩立刚),(3,韩立辉),(2,张京),(null,杨帅)可以看到自增列的值select * from au外键约束MySQL支持外键存储引擎只有InnoDB在创建外键的时候要求父表必须有对应的索引子表在创建外键的时候也会自动创建索引在删除更新父表时对子表进行相应的操作包括restrict、cascade、set null和no action使用InnoDB存储引擎数据按主键顺序存放如果不创建索引数据以插入顺序存放create table au1(studentid int,name varchar(10),primary key(studentid))engineinnodbinsert au1 values (1,韩立刚),(3,韩立辉),(2,张京),(8,杨帅),(6,杨柳青)select * from au1创建数据库和表练习创建数据库也可使用命令创建数据库输入以下命令 create database SchoolDB; 选中执行练习创建表选中刚才创建的数据库 执行创建表的语句创建学生表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 table TSubject(subJectID nvarchar(10),subJectName nvarchar(30),BookName nvarchar(30),Publisher nvarchar(20))创建分数表create table TScore(StudentID nvarchar(15),subJectID nvarchar(10),mark decimal)练习使用图形界面产生表的SQL语句使用图形界面可以产生创建、删除、更改对象的SQL语句。使用mySQL帮助查看mySQL帮助如果你不知道帮助能够提供什么?输入? contents可以查看可用的帮助输入? Data definition查看创建表的语法快速查阅帮助在实际应用当中如果需要快速查看某项语法时可以使用关键字进行快速查询。比如想知道show命令都能看到些什么东西可以使用如下命令。查看数据库查看创建数据库的命令查看创建表的命令
