MySQL数据库管理系统基础

《MySQL数据库管理系统基础》由会员分享，可在线阅读，更多相关《MySQL数据库管理系统基础（61页珍藏版）》请在金锄头文库上搜索。

1、MySQL数据库管理系统MySQL数据数据库基本知基本知识数据数据库模型概念模型概念数据数据库服服务器管理用器管理用户的方法的方法MySQL数据数据库、数据表、数据表维护管理方法管理方法MySQL优化网站/书籍分享n n MySQLPerformanceBlogn n MySQL中文网nMySQL性能调优与架构设计n深入浅出MySQL谁在用MYSQL谁在用MYSQLMySQL数据库管理系统概述1、MySQL数据库管理系统的特点性能高效而稳定，MySQL几乎比当前可用的其他所有数据库的性能都不差，因此Yahoo、Google、Cisco、HP和NASA等都采用了它作为自己的数据库引擎。开放源代码

2、，MySQL是自由的开放源代码产品，可以在GPL下畅通使用。多用户支持，MySQL可有效地满足501000个并发用户的访问，并且在超过600个用户的限度的情况下，MySQL的性能并没有明显的下降。多线程，MySQL使用核心线程的完全多线程，这意味着可以采用多CPU体系结构。1、MySQL数据库管理系统的特点开放性，支持ANSISQL-99标准，适用于多种操作系统（如Linux、Solaris、FreeBSD、OS/2、MacOS以及Windows95/98/Me/2000/XP和WindowsNT等），可在多种体系结构（如Intelx86、Alpha、SPARC、PowerPC和IA64等）上

3、运行。广泛的应用程序支持，有C、C+、Java、Perl、PHP和Python等多种客户工具和API的支持。支持事务处理、行锁定、子查询、外键和全文检索等功能。支持大数据库处理，可对某些包含50000000个记录的数据库使用MySQL。有灵活且安全的权限和口令系统，并且允许对其他主机的认证，等等。2、MySQL操作模式命令操作界面打开命令操作界面，单击“开始”“运行”输入“mysqlurootp”,显示mysql3.数据库服务器安装有数据库管理系统的计算机服务器。管理方式：支持客户机/服务器（C/S）主要职责：管理数据库用户、维护数据库和数据表文件4.数据库用户被授权允许使用数据库、数据表的人

4、员。数据库管理员：id=root，pwd职责，维护用户账号、维护数据库和数据表。普通用户：由数据库管理员创建用户账号，并授权维护数据库，数据表。MySQL数据模型由数据库文件与数据表文件构成。数据库文件：用于管理数据表构成信息的文件，数据库文件名唯一（不重复）。数据表文件：用于管理数据记录的文件，数据表文件名唯一（不重复）。数据表由，数据表文件名+数据表结构+数据记录构成。6.常见数据类型（具体数据类型看附表）字符型（姓名，职称，地址）数值型（工资，成绩，数量）可以进行运算日期型（出生日期，毕业时间）逻辑型（只有两种状态，逻辑真和逻辑假）7.MySQL的数据存储结构MySQL数据库管理系统保存

5、在“C:/AppServ/MySQL”系统数据库MySQL，用户创建的应用数据库bookstore保存在“C:/AppServ/MySQL/Data”MySQL列类型数值数据类型数据类型说明取值范围存储空间TINYINT(M)非常小的整数-128127（-2727-1）0255（028-1）1字节SMALLINT(M)较小的整数-3276832767（-215215-1）065535（0216-1）2字节MEDIUMINT(M)中等大小的整数-83886088388607016772153字节INT(M)标准整数-21476836482147683647042949672954字节BIGINT

6、(M)大整数-922337203685477580892233730368547758070184467440737095516158字节FLOAT(M,D)单精度浮点数1.175494351E-384.402823466E384字节DOUBLE(M,D)双精度浮点数2.2250738585072014E-3081.7976931348623157E+3088字节DECIMAL(M,D)一个字符串类型的浮点数可变，其值的范围依赖于M和DM+2字节字符数据类型数据类型说明最大尺寸存储空间CHAR定长字符串MBMBVARCHAR可变长字符串MBL+1byteTINYBLOB非常小的BLOB28-1

7、byteL+1byteBLOB小BLOB216-1byteL+2byteMEDIUMBLOB中等BLOB224-1byteL+3byteLONGBLOB大BLOB232-1byteL+4byteTINYTEXT非常小的文本串28-1byteL+1byteTEXT小文本串216-1byteL+2byteMEDIUMTEXT中等文本串224-1byteL+3byteLONGTEXT大文本串232-1byteL+4byteENUM枚举65535个成员1或2byteSET集合64个成员1、2、3、4或8byte日期和时间数据类型数据类型说明取值范围存储空间“零”值DATE“YYYY-MM-DD”格式1

8、000-01-01到9999-12-313字节0000-00-00TIME“HH:MM:SS”格式-838:59:59到838:59:593字节00:00:00DATETIME“YYYY-MM-DDHH:MM:SS”格式1000-01-0100:00:00到9999-12-3123:59:598字节0000-00-0000:00:00TIMESTAMP“YYYYMMDDHHMMSS”格式19700101000000到2037年某时间4字节00000000000000YEAR“YYYY”格式1901年到2155年1字节0000MySQL服务器用户管理每一个使用MySQL数据库的用户必须拥有一个合

9、法的账号（包括用户名，用户密码）和相应的权限。MySQL数据库管理员账号，用户名为root，用户密码（在安装过程中设置的密码，可以随时修改）。普通用户的账号和权限必须由数据库管理员创建和授权。登录MySQL服务器使用数据库管理员的账号登录，用户名root，用户密码。方法一，使用命令行方式登录单击“开始”“所有程序”“AppServ”“MySQLcommandlineclient”输入密码。出现mysql登录成功方法二使用“运行”登录MySQL服务器。单击“开始”“运行”命令格式：mysqlu-pMySQL服务器的用户管理MySQL服务器中内置系统数据库，数据库名为mysql，数据库中内置数据表

10、，数据表名为user。只能由数据库管理员（root）对用户账号进行维护，增加新用户、修改用户密码、删除用户账号、用户账号授权。User数据表中所有授权用户权限序号序号权限参数权限参数说明说明1Select_priv显示记录权限显示记录权限2Insert_priv插入记录权限插入记录权限3Update_priv更新记录权限更新记录权限4Delete_priv删除记录权限删除记录权限5Create_priv建立数据库、数据表权限建立数据库、数据表权限6Drop_priv删除表文件的权限删除表文件的权限7Index_priv创建索引字段权限创建索引字段权限8Alter_priv修改表结构权限修改表结

11、构权限9File_priv读写服务器上文件的权限读写服务器上文件的权限10Grant_priv授权其他用户权限授权其他用户权限11References_priv维护外键权限维护外键权限12Show_db_priv浏览服务器上数据库权限浏览服务器上数据库权限13Lock_tables_priv创建删除表锁权限创建删除表锁权限MySQL命令格式命令关键词；分号表示命令结束并提交1）打开数据库Use;例：usemysql;打开mysql数据库2）增加新用户的账号到mysql数据库user表Insertinto数据表名(主机名,用户名,用户密码,权限1,权限2,)values(主机名值,用户名值,用户

12、密码值,权限1值,权限2值,);3）激活数据Flushprivileges;1.增加新的用户(掌握）创建新的用户账号包括用户名、用户密码、用户权限。打开mysql数据库插入新的用户账号数据到user数据表激活新创建的用户账号命令格式：mysqlusemysql；mysqlinsertintomysql.user(主机名称,用户名称,用户密码,权限1,权限2,)values(host,user,password,select_priv,)；mysqlflushprivileges;例题usemysql；insertintomysql.user(Host,User,Password,ssl_cip

13、her,x509_issuer,x509_subject)values(localhost,“mysqladmin2,password(passwd),);flushprivileges；例题用户名booktest,用户密码333333,拥有select,insert,update,delete,create,drop,index,alter权限。mysqlusemysql；mysqlinsertintomysql.user(host,user,password,select_priv,insert_priv,update_priv,delete_privi,create_priv,drop_

14、priv,index_priv,alter_priv)values(localhost,booktest,password(333333),y,y,y,y,y,y,y,y)；mysqlflushprivileges；2.修改用户权限（理解）命令格式：updateset权限参数名称=权限值where；mysqlusemysql；mysqlupdatemysql.userset权限名1=权限值1，权限名2=权限值2where；mysqlflushprivileges；例题修改bookuser用户的index_priv和alter_priv为ymysqlusemysql；mysqlupdatemys

15、ql.usersetindex_priv=y，alter_priv=ywhereuser=bookuser；mysqlflushprivileges；修改用户密码（理解）命令格式：updatesetpassword=password(新密码)where；例题修改mysqladmin2用户的密码mysqlusemysql；mysqlupdatemysql.usersetpassword=password(“222222”)whereuser=“mysqladmin2”；mysqlflushprivileges；3.删除用户账号命令格式：（掌握）deletefromwheremysqlusemys

16、ql;mysqldeletefrommysql.userwhere；mysqlflushprivileges；例题删除mysqladmin2用户账号mysqlusemysql；mysqldeletefrommysql.userwhereuser=mysqladmin2；mysqlflushprivileges；MySQL数据管理维护数据库：显示数据库，建立数据库，删除数据库，打开数据库。维护数据表：建立数据表，显示数据表文件名，显示数据表结构，修改数据表结构，删除数据表文件，更换数据表名。维护数据表的数据（记录）：增加记录，删除记录，修改记录，选取记录。维护MySQL数据库(掌握）1.显示数据

17、库(MySQL数据库是系统数据库)显示当前服务器中所有的数据库。命令格式：showdatabases；2.建立数据库在当前服务器中创建一个新的数据库。命令格式：createdatabase；3.删除数据库删除当前服务器中的一个数据库。命令格式：dropdatabase；4.打开数据库打开当前服务器中的一个数据库。命令格式：use；维护MySQL数据表数据表=数据表名、数据表结构、数据记录。定义数据表结构（字段名、字段类型、宽度、辅助项）数据表维护工作：1.创建一个新的数据表、2.显示存在的数据表、3.显示一个数据表的结构、4.修改数据表结构、5.删除数据表、6.更换数据表名。维护MySQL数据

18、表显示数据表名。（掌握）showtables；显示数据表结构。（掌握）describe；修改、增加、删除字段名称、字段类型（理解）修改字段命令格式：altertable数据表名change原字段名新字段名；修改字段名例题：altertablememberschange性别会员性别char(2);修改字段类型例题：altertablememberschange工作单位工作单位char(20);维护MySQL数据表增加字段命令格式：（理解）altertable数据表名add字段名字段类型；增加字段例题：altertablemembersadd单位电话char(12);删除字段命令格式：（理解）al

19、tertable数据表名drop字段名；删除字段例题：altertablemembersdrop单位电话;维护MySQL数据表删除数据表。（掌握）droptableifexists数据表名；直接删除数据表例题：droptablemembers;删除members数据表如果数据表存在就删除数据表：droptableifexistsmembers;如果members数据表存在就删除members数据表更换数据表名。（理解）renametable数据表名to新数据表名；更换数据表名例题：renametablememberstomembersx;将数据表名为members的更换为membersx维护M

20、ySQL数据表1.建立新的数据表，定义表文件名、字段名、字段类型、字段宽度、设置字段属性。命令格式：（掌握）createtable（,，）；字段i定义内容：字段名，类型，宽度，非空修饰符，default修饰符，auto_increment修饰符。create字段约束NULL和NOTNULL修饰符：NULL允许该字段为空值，NOTNULL该字段不允许为空值。DEFAULT修饰符：在插入记录时自动插入一个默认值。AUTO_INCREMENT修饰符：只适用于INT类型字段，是步长为一的自动增量修饰。建立数据表例题：建立数据表（student）createdatabasestudent;usestud

21、ent;DROPTABLEuser;CREATETABLEuser(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(30) NOTNULL,passwordVARCHAR(32) NOTNULL,ageINTNOTNULL,sexVARCHAR(2)DEFAULT男,birthday DATE);建立数据表创建数据表方法二：批处理方法将创建数据表的命令写入一个文本文件，扩展名为sql。文件名为：w.sql文件位置：c:appservmysqldatausestudent;droptableifexistsmembers;createtablemembers(

22、身份证号char(18)notnull,会员密码char(6)notnull,会员姓名char(10)notnull,联系电话varchar(20)notnull,注册时间datetime);运行批处理文件，在mysql命令行窗口输入按以下格式输入命令sourcemysqlsourcec:appservmysqldataw.sql索引索引-索引的作用：提高搜索速度，减少查询时间。方式一createtable创建索引（掌握）createtable(字段1定义,字段n定义,index索引名称(字段1,字段2,),unique索引名称(字段1,字段2,);方式二createuniqueindex添加

23、索引createindex索引名on数据表(字段1,字段2);createuniqueindex索引名on数据表(字段1,字段2);unique选项表示唯一索引createtable方式索引例题createtablestudent( 学号char(8)notnull, 姓名char(12)notnull, 性别char(2)default男, 出生年月datenotnulldefault1978-01-08, 专业char(6)notnulldefault100001, indexstudsp(专业), uniquestudid(学号);索引createindex方式索引例题createtab

24、lestudent(学号char(8)notnull,姓名char(12)notnull,性别char(2)notnulldefault男,出生年月datenotnulldefault1978-01-08,专业char(6)notnulldefault100001);createindexstudsponstudent(专业);createuniqueindexstudidonstudent(学号);主键主键（掌握）主键字段值（键值）非空且不重，可以多字段组合主键，一个数据表中主键只能有一个。方式一createtable创建索引createtable(字段1定义,字段n定义,primaryke

25、y索引名称(字段1,字段2,);方式二altertable添加索引altertable数据表addprimarykey索引名(字段1,字段2);主键createtable方式创建主键例题createtablestudent(学号char(8)notnull,姓名char(12)notnull,性别char(2)notnulldefault男,出生年月datenotnulldefault1978-01-08,专业char(6)notnulldefault100001,primarykeystudxh(学号);主键altertable方式添加主键例题createtablestudent(学号cha

26、r(8)notnull,姓名char(12)notnull,性别char(2)notnulldefault男,出生年月datenotnulldefault1978-01-08,专业char(6)notnulldefault100001);altertablestudentaddprimarykeystudxh(学号);外键外键（掌握）通过外键在两个数据表中建立关联，可以是一对一关联，也可以是一对多关联。该关联在关联表中的数据构成参照完整性。定义外键格式：1.使用index索引项名(索引字段)创建索引。2.使用foreignkey(索引字段)references主键数据表名(主键索引字段)。外键

27、createtable方式创建外键例题createtabletitle(职称代码char(4)notnullprimarykey,职称名称varchar(20)notnull)type=innodb;createtablemembers(身份证号char(18)notnull,性别char(2)notnull, 出生日期date notnull,工作单位varchar(24)notnull,单位地址varchar(24)notnull,邮政编码char(6)notnull,职称代码char(4)notnull,primarykey(身份证号),index(职称代码),foreignkey(职称

28、代码)referencestitle(职称代码)type=innodb;外键altertable方式添加外键例题createtabletitle(职称代码char(4)notnullprimarykey,职称名称varchar(20)notnull)type=innodb;createtablemembers(身份证号char(18)notnull,性别char(2)notnull, 出生日期date notnull,工作单位varchar(24)notnull,单位地址varchar(24)notnull,邮政编码char(6)notnull,职称代码char(4)notnull,prima

29、rykey(身份证号),index(职称代码)type=innodb;altertable数据表名addforeignkey(职称代码)referencestitle(职称代码)ondeletecascadeonupdatecascade;删除索引，主键，外键（掌握）删除索引命令格式：dropindex索引名称on数据表名；删除主键命令格式：altertable数据表名dropprimarykey；删除外键命令格式：altertable数据表名dropforeignkey外键标识；一般情况通过删除表实现删除外键（外键标识可以使用“showcreatetable数据表名”得到，信息中的const

30、raint选项值）删除索引、主键、外键例题createtabletitle(职称代码char(4)notnullprimarykey,职称名称varchar(20)notnull)type=innodb;createtablemembers(身份证号char(18)notnull,性别char(2)notnull, 出生日期date notnull,工作单位varchar(24)notnull,单位地址varchar(24)notnull,邮政编码char(6)notnull,职称代码char(4)notnull,primarykey(身份证号),index(职称代码),foreignkey(

31、职称代码)referencestitle(职称代码)type=innodb;删除索引：dropindex索引名称on数据表名；dropindex职称代码onmembers;删除主键：altertable数据表名dropprimarykey；altertablemembersdropprimarykey；删除外键：altertable数据表名dropforeignkey外键标识；altertablemembersdropforeignkeymembers_ibfk_1;外键标识使用showcreatetablemembers;的constraint得到members_ibfk_1外键参照完整性（

32、理解）外键表外键与参照表主键建立数据关联，该关联构成表间约束（参照完整性）。外键定义中的ondeletecascade约束、onupdatecascade约束。ondeletecascade约束关联表同步删除。onupdatecascade约束关联表同步更新。维护数据表记录-增加记录1.增加记录（掌握）insertinto数据表名(字段名1,字段名n)values(数值1，数值n)；字段i顺序任意，字段i与数值i一一对应。增加记录例题：insertintomembersx(身份证号,性别,出生日期,工作单位)values(,男,1978-01-03,中国人民大学)；注意：增加记录时主键字段或U

33、NIQUE索引字段不能重复外键字段值在关联表中主键值必须已经存在删除记录2.删除记录（掌握）命令格式1：删除数据表中全部记录。deletefrom数据表名；删除数据表中全部记录例题：deletefrommembersx；命令格式2：删除数据表中符合条件的记录。deletefrom数据表名where条件表达式删除数据表中符合条件的记录例题：deletefrommemberswhere身份证号=修改记录3.修改记录（掌握）命令格式1：修改数据表中全部记录指定字段的值updateset,字段名n=数值n字段i顺序任意，字段i与数值i一一对应，同时修改多个字段时set只使用一个。修改全部记录指定字段值

34、例题：updatememberset会员密码=999999；命令格式2：修改数据表中符合条件的记录指定字段的值updateset,字段名n=数值nwhere修改身份证号为的会员密码为888888，会员姓名为赵华例题：updatememberset会员密码=888888，会员姓名=赵华where身份证号=；选取数据表记录（掌握）选取数据表中指定字段，指定记录的结果，并对输出结果进行重组。selectfromwhereorderby字段名asc|descgroupby字段名表：显示获取结果from数据表名表：数据源where条件表达式：获取数据条件orderby字段名：按字段进行升序asc或降序d

35、esc排序groupby字段名：按字段进行分组例题：select身份证号,性别,year(curdate()-year(出生日期)as年龄frommemberswhere性别=女orderby年龄desc；选取数据表记录例题：select身份证号,性别frommembersgroupby性别asc；例题：like%、_选项select身份证号,会员姓名,会员密码frommemberwhere会员姓名like张%;显示member数据表姓张的人员信息。select身份证号,会员姓名,会员密码frommemberwhere会员姓名like张_;显示member数据表姓名2个字姓张的人员信息。例题：

36、like%选项select身份证号,会员姓名,会员密码frommemberwhere会员密码5555and会员姓名notlike张%;显示member数据表不姓张并且会员密码5555的人员信息。选取数据表记录选取输出来自于两个以上的数据表的数据集合，定义输出条件和输出的字段或表达式。输出的字段必须指明所属数据表，同时还要指明数据表之间的关联。使用数据表别名(asmb)。例题：显示member数据表的身份证号、会员姓名、会员密码select身份证号,会员姓名,会员密码frommember;显示members数据表的身份证号,性别,出生日期。select身份证号,性别,出生日期frommember

37、s;selectmb.身份证号,会员姓名,会员密码,性别,出生日期frommemberasmb,memberswheremb.身份证号=members.身份证号;选取数据表记录选取输出来自于五个数据表的数据集合。输出同名字段必须指明所属数据表，必须用and分隔定义表间关联。例题：输出订单号(sell)、书名(book)、单价(book)、身份证号(member)、会员姓名(member)、性别(members)、职称名称(title)。select订单号,书名,单价,member.身份证号,会员姓名,性别,职称名称frombook,member,title,members,sellwheres

38、ell.身份证号=member.身份证号andsell.图书编号=book.图书编号andmembers.身份证号=member.身份证号andmembers.职称代码=title.职称代码;选取数据表记录选取输出来自于五个数据表的数据集合。输出同名字段必须指明所属数据表，必须用and分隔定义表间关联。例题：输出订单号(sell)、书名(book)、单价(book)、身份证号(member)、会员姓名(member)、性别(members)、职称名称(title)。选取条件，张宇、赵成新、李来群三人购买的图书清单。select订单号,书名,单价,member.身份证号,会员姓名,性别,职称名称

39、frombook,member,title,members,sellwhere(sell.身份证号=member.身份证号andsell.图书编号=book.图书编号andmembers.身份证号=member.身份证号andmembers.职称代码=title.职称代码)and会员姓名in(张宇,赵成新,李来群);选取数据表记录选取输出来自于五个数据表的数据集合。输出同名字段必须指明所属数据表，必须用and分隔定义表间关联。例题：性别(members),sum(订购册数(sell),sum(单价(book)*订购册数(sell),sum(单价*订购册数)/sum(订购册数)as平均价格，按姓

40、别分组并计算男女性购书的册数、金额、平均价格。select性别,sum(订购册数),sum(单价*订购册数),sum(单价*订购册数)/sum(订购册数)as平均价格frommember,book,members,sellwhere(members.身份证号=member.身份证号andsell.身份证号=member.身份证号andsell.图书编号=book.图书编号)groupby性别;选取数据表记录使用子查询作为查询条件，获取符合条件的记录和指定部分字段作为输出结果。命令格式：selectfromwhereorderbyasc|descgroupby例题：身份证号，性别，出生日期，输出

41、条件是出生日期最大（年龄最小，使用max()函数）。select性别,性别,出生日期frommemberswhere出生日期=(selectmax(出生日期)frommembersgroupby性别having性别=女);选取数据表记录例题：选取身份证号，性别，职称代码，输出条件是正高级职称（职称代码最右边第一位数是1）使用right函数。select身份证号,性别,职称代码frommemberswhere职称代码in(select职称代码frommemberswhereright(职称代码,1)=1);例题：选取会员姓名，性别，职称名称，输出条件是职称为医生系列或者是正高级职称（职称代码最左

42、边第一位数是3）使用left函数和right函数。select身份证号,性别,职称代码frommemberswhere职称代码in(select职称代码frommemberswhereleft(职称代码,1)=3orright(职称代码)=1);选取数据表记录将一个数据表中的记录添加到另一个结构相同的数据表中，复制数据表。命令格式：insertinto(字段名表)selectfrom例题：将members数据表性别为女的记录添加到memberc数据表中。insertintomemberc(身份证号,性别,出生日期,工作单位,单位地址,邮政编码,职称代码)select身份证号,性别,出生日期,工作单位,单位地址,邮政编码,职称代码frommemberswhere性别=女;