1、1MySQLMySQL数据库管理系统数据库管理系统2MySQLMySQL数据库基本知识数据库基本知识数据库模型概念数据库模型概念数据库服务器管理用户的方法数据库服务器管理用户的方法MySQLMySQL数据库、数据表维护管理方法数据库、数据表维护管理方法3MySQL Performance BlogMySQL 中文网nMySQL性能调优与架构设计 n深入浅出MySQL4谁在用MYSQL5谁在用MYSQL6MySQL数据库管理系统概述1、MySQL数据库管理系统的特点性能高效而稳定，MySQL几乎比当前可用的其他所有数据库的性能都不差，因此Yahoo、Google、Cisco、HP和NASA等都采
2、用了它作为自己的数据库引擎。开放源代码，MySQL是自由的开放源代码产品，可以在GPL下畅通使用。多用户支持，MySQL可有效地满足501000个并发用户的访问，并且在超过600个用户的限度的情况下，MySQL的性能并没有明显的下降。多线程，MySQL使用核心线程的完全多线程，这意味着可以采用多CPU体系结构。71、MySQL数据库管理系统的特点开放性，支持ANSI SQL-99标准，适用于多种操作系统（如Linux、Solaris、FreeBSD、OS/2、MacOS以及Windows95/98/Me/2000/XP和Windows NT等），可在多种体系结构（如Intel x86、Alph
3、a、SPARC、PowerPC和IA64等）上运行。广泛的应用程序支持，有C、C+、Java、Perl、PHP和Python等多种客户工具和API的支持。支持事务处理、行锁定、子查询、外键和全文检索等功能。支持大数据库处理，可对某些包含50 000 000个记录的数据库使用MySQL。有灵活且安全的权限和口令系统，并且允许对其他主机的认证，等等。82、MySQL操作模式命令操作界面打开命令操作界面，单击“开始” “运行” 输入“mysql uroot p”,显示mysql93.数据库服务器安装有数据库管理系统的计算机服务器。管理方式：支持客户机/服务器（C/S）主要职责：管理数据库用户、维护数
4、据库和数据表文件4.数据库用户被授权允许使用数据库、数据表的人员。数据库管理员：id=root，pwd职责，维护用户账号、维护数据库和数据表 。普通用户：由数据库管理员创建用户账号，并授权维护数据库，数据表。10MySQL数据模型由数据库文件与数据表文件构成。数据库文件：用于管理数据表构成信息的文件，数据库文件名唯一（不重复）。数据表文件：用于管理数据记录的文件，数据表文件名唯一（不重复）。数据表由，数据表文件名+数据表结构+数据记录构成。116.常见数据类型（具体数据类型看附表）字符型（姓名，职称，地址）数值型（工资，成绩，数量）可以进行运算日期型（出生日期，毕业时间）逻辑型（只有两种状态，
5、逻辑真和逻辑假）7.MySQL的数据存储结构MySQL数据库管理系统保存在“C:/AppServ/MySQL”系统数据库MySQL，用户创建的应用数据库bookstore保存在“C:/AppServ/MySQL/Data”12MySQL列类型数值数据类型数据类型说明取值范围存储空间TINYINT(M)非常小的整数-128127（-2727-1）0255（028-1）1字节SMALLINT(M)较小的整数-3276832767（-215215-1）065535（0216-1）2字节MEDIUMINT(M)中等大小的整数-83886088388607016772153字节INT(M)标准整数-21
6、476836482147683647042949672954字节BIGINT(M)大整数-922337203685477580892233730368547758070184467440737095516158字节FLOAT(M,D)单精度浮点数1.175494351E-384.402823466E384字节DOUBLE(M,D)双精度浮点数2.2250738585072014E-3081.7976931348623157E+3088字节DECIMAL(M,D)一个字符串类型的浮点数可变，其值的范围依赖于M和DM+2字节13字符数据类型数据类型说明最大尺寸存储空间CHAR定长字符串MBMBVA
7、RCHAR可变长字符串MBL+1byteTINYBLOB非常小的BLOB28-1byteL+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或8byte14日期和时
8、间数据类型 数据类型说明取值范围存储空间“零”值DATE“YYYY-MM-DD”格式1000-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
9、年1字节000015MySQL服务器用户管理每一个使用MySQL数据库的用户必须拥有一个合法的账号（包括用户名，用户密码）和相应的权限。MySQL数据库管理员账号，用户名为root，用户密码（在安装过程中设置的密码，可以随时修改）。普通用户的账号和权限必须由数据库管理员创建和授权。16登录MySQL服务器使用数据库管理员的账号登录，用户名root，用户密码。方法一，使用命令行方式登录单击“开始” “所有程序” “AppServ” “MySQL command line client”输入密码。出现mysql登录成功方法二 使用“运行”登录MySQL服务器。单击“开始” “运行”命令格式：mys
10、ql u -p17MySQL服务器的用户管理MySQL服务器中内置系统数据库，数据库名为mysql，数据库中内置数据表，数据表名为user。只能由数据库管理员（root）对用户账号进行维护，增加新用户、修改用户密码、删除用户账号、用户账号授权。18User数据表中所有授权用户权限序号序号权限参数权限参数说明说明1Select_priv显示记录权限显示记录权限2Insert_priv插入记录权限插入记录权限3Update_priv更新记录权限更新记录权限4Delete_priv删除记录权限删除记录权限5Create_priv建立数据库、数据表权限建立数据库、数据表权限6Drop_priv删除表文
11、件的权限删除表文件的权限7Index_priv创建索引字段权限创建索引字段权限8Alter_priv修改表结构权限修改表结构权限9File_priv读写服务器上文件的权限读写服务器上文件的权限10Grant_priv授权其他用户权限授权其他用户权限11References_priv维护外键权限维护外键权限12Show_db_priv浏览服务器上数据库权限浏览服务器上数据库权限13Lock_tables_priv创建删除表锁权限创建删除表锁权限19MySQL命令格式命令关键词 ；分号表示命令结束并提交1）打开数据库Use ;例：use mysql; 打开 mysql 数据库2）增加新用户的账号到
12、mysql数据库user表Insert into 数据表名 (主机名,用户名,用户密码,权限1,权限2,) values(主机名值,用户名值,用户密码值,权限1值,权限2值, );3）激活数据Flush privileges;201.增加新的用户(掌握）创建新的用户账号包括用户名、用户密码、用户权限。打开mysql数据库插入新的用户账号数据到user数据表激活新创建的用户账号命令格式：mysqluse mysql；mysqlinsert into mysql.user(主机名称,用户名称,用户密码,权限1,权限2, ) values(host,user,password,select_priv
13、,)；mysqlflush privileges;21例题use mysql；insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values(localhost,“mysqladmin2,password(passwd),);flush privileges；22例题用户名booktest,用户密码333333,拥有select,insert, update,delete,create,drop,index,alter权限。mysqluse mysql；mysqlinsert into
14、mysql.user(host,user,password,select_priv,insert_priv,update_priv,delete_privi,create_priv,drop_priv,index_priv,alter_priv) values (localhost,booktest,password(333333) , y,y,y,y,y,y,y,y)；mysqlflush privileges；232.修改用户权限（理解）命令格式：update set 权限参数名称=权限值 where ；mysqluse mysql；mysqlupdate mysql.user set 权
15、限名1=权限值1，权限名2=权限值2 where ；mysqlflush privileges；24例题修改bookuser用户的index_priv和alter_priv为ymysqluse mysql；mysqlupdate mysql.user set index_priv=y，alter_priv=y where user=bookuser；mysqlflush privileges；25修改用户密码（理解）命令格式：update set password=password(新密码) where ；例题修改mysqladmin2用户的密码mysqluse mysql；mysqlupda
16、te mysql.user set password=password(“222222”) where user=“mysqladmin2”；mysqlflush privileges；263.删除用户账号命令格式：（掌握）delete from where mysqluse mysql;mysqldelete from mysql.user where ；mysqlflush privileges；例题删除mysqladmin2用户账号mysqluse mysql；mysqldelete from mysql.user where user=mysqladmin2；mysqlflush pr
17、ivileges；27MySQL数据管理维护数据库：显示数据库，建立数据库，删除数据库，打开数据库。维护数据表：建立数据表，显示数据表文件名，显示数据表结构，修改数据表结构，删除数据表文件，更换数据表名。维护数据表的数据（记录）：增加记录，删除记录，修改记录，选取记录。28维护MySQL数据库(掌握）1.显示数据库(MySQL数据库是系统数据库)显示当前服务器中所有的数据库。命令格式：show databases；2.建立数据库在当前服务器中创建一个新的数据库。命令格式：create database ；3.删除数据库删除当前服务器中的一个数据库。命令格式：drop database ；4.打
18、开数据库打开当前服务器中的一个数据库。命令格式：use ；29维护MySQL数据表数据表=数据表名、数据表结构、数据记录。定义数据表结构（字段名、字段类型、宽度、辅助项）数据表维护工作：1.创建一个新的数据表、2.显示存在的数据表、3.显示一个数据表的结构、4.修改数据表结构、5.删除数据表、6.更换数据表名。30维护MySQL数据表显示数据表名。（掌握）show tables；显示数据表结构。 （掌握）describe ；修改、增加、删除字段名称、字段类型（理解）修改字段命令格式：alter table 数据表名 change 原字段名 新字段名 ；修改字段名例题：alter table m
19、embers change 性别 会员性别 char(2);修改字段类型例题：alter table members change 工作单位 工作单位 char(20);31维护MySQL数据表增加字段命令格式： （理解）alter table 数据表名 add 字段名 字段类型；增加字段例题：alter table members add 单位电话 char (12);删除字段命令格式： （理解）alter table 数据表名 drop 字段名；删除字段例题：alter table members drop 单位电话;32维护MySQL数据表删除数据表。 （掌握）drop table if
20、 exists 数据表名；直接删除数据表例题：drop table members; 删除members数据表如果数据表存在就删除数据表：drop table if exists members; 如果members数据表存在就删除members数据表更换数据表名。 （理解）rename table 数据表名 to 新数据表名；更换数据表名例题：rename table members to membersx; 将数据表名为members的更换为membersx33维护MySQL数据表1.建立新的数据表，定义表文件名、字段名、字段类型、字段宽度、设置字段属性。命令格式：（掌握）create t
21、able （ , ，）；字段i定义内容：字段名，类型，宽度，非空修饰符，default修饰符，auto_increment修饰符。create 字段约束 NULL 和 NOT NULL修饰符：NULL允许该字段为空值，NOT NULL该字段不允许为空值。DEFAULT修饰符：在插入记录时自动插入一个默认值。AUTO_INCREMENT修饰符：只适用于INT类型字段，是步长为一的自动增量修饰。34建立数据表例题：建立数据表（student）create database student;use student;DROP TABLE user ;CREATE TABLE user(idINTAUT
22、O_INCREMENT PRIMARY KEY ,nameVARCHAR(30)NOT NULL ,password VARCHAR(32)NOT NULL ,ageINTNOT NULL ,sexVARCHAR(2)DEFAULT 男 ,birthday DATE ) ;35建立数据表创建数据表方法二：批处理方法将创建数据表的命令写入一个文本文件，扩展名为sql。文件名为：w.sql文件位置：c:appservmysqldatause student; drop table if exists members; create table members( 身份证号 char(18) not
23、null, 会员密码 char(6) not null, 会员姓名 char(10) not null, 联系电话 varchar(20) not null, 注册时间 datetime );36运行批处理文件，在mysql命令行窗口输入按以下格式输入命令sourcemysqlsource c:appservmysqldataw.sql37索引索引-索引的作用：提高搜索速度，减少查询时间。方式一create table 创建索引（掌握）create table (字段1定义,字段n定义, index 索引名称 (字段1,字段2 ,), unique 索引名称 (字段1,字段2,) );方式二c
24、reate uniqueindex添加索引create index 索引名 on 数据表 (字段1,字段2);create unique index 索引名 on 数据表 (字段1,字段2);unique 选项表示唯一38索引create table 方式索引例题create table student ( 学号 char(8) not null, 姓名 char(12) not null, 性别 char(2) default 男, 出生年月 date not null default 1978-01-08, 专业 char(6) not null default 100001, index
25、 studsp (专业), unique studid (学号);39索引create index 方式索引例题create table student ( 学号 char(8) not null, 姓名 char(12) not null, 性别 char(2) not null default 男, 出生年月 date not null default 1978-01-08, 专业 char(6) not null default 100001);create index studsp on student (专业);create unique index studid on studen
26、t (学号);40主键主键（掌握）主键字段值（键值）非空且不重，可以多字段组合主键，一个数据表中主键只能有一个。方式一create table 创建索引create table (字段1定义,字段n定义, primary key 索引名称 (字段1,字段2 ,);方式二alter table 添加索引alter table 数据表 add primary key 索引名 (字段1,字段2);41主键create table 方式创建主键例题create table student ( 学号 char(8) not null, 姓名 char(12) not null, 性别 char(2) n
27、ot null default 男, 出生年月 date not null default 1978-01-08, 专业 char(6) not null default 100001, primary key studxh (学号);42主键alter table 方式添加主键例题create table student ( 学号 char(8) not null, 姓名 char(12) not null, 性别 char(2) not null default 男, 出生年月 date not null default 1978-01-08, 专业 char(6) not null de
28、fault 100001);alter table student add primary key studxh (学号);43外键外键（掌握）通过外键在两个数据表中建立关联，可以是一对一关联，也可以是一对多关联。该关联在关联表中的数据构成参照完整性。定义外键格式：1.使用index 索引项名 (索引字段) 创建索引。2.使用foreign key (索引字段) references 主键数据表名 (主键索引字段)。44外键create table 方式创建外键例题create table title(职称代码 char(4) not null primary key,职称名称 varchar
29、(20) not null )type=innodb;create table members(身份证号 char(18) not null,性别 char(2) not null, 出生日期 date not null,工作单位 varchar(24) not null,单位地址 varchar(24) not null,邮政编码 char(6)not null,职称代码 char(4) not null,primary key (身份证号),index (职称代码),foreign key (职称代码) references title (职称代码) ) type=innodb;45外键a
30、lter table 方式添加外键例题create table title(职称代码 char(4) not null primary key,职称名称 varchar(20) not null)type=innodb;create table members(身份证号 char(18) not null,性别 char(2) not null, 出生日期 date not null,工作单位 varchar(24) not null,单位地址 varchar(24) not null,邮政编码 char(6)not null,职称代码 char(4) not null,primary key
31、 (身份证号),index (职称代码) ) type=innodb;alter table 数据表名 add foreign key (职称代码) references title (职称代码) on delete cascade on update cascade;46删除索引，主键，外键（掌握）删除索引命令格式：drop index 索引名称 on 数据表名；删除主键命令格式：alter table 数据表名 drop primary key；删除外键命令格式：alter table 数据表名 drop foreign key 外键标识；一般情况通过删除表实现删除外键（外键标识可以使用“
32、show create table 数据表名”得到，信息中的constraint选项值）47删除索引、主键、外键例题create table title(职称代码 char(4) not null primary key,职称名称 varchar(20) not null )type=innodb;create table members(身份证号 char(18) not null,性别 char(2) not null, 出生日期 date not null,工作单位 varchar(24) not null,单位地址 varchar(24) not null,邮政编码 char(6)no
33、t null,职称代码 char(4) not null,primary key (身份证号),index (职称代码),foreign key (职称代码) references title (职称代码) ) type=innodb;48删除索引：drop index 索引名称 on 数据表名；drop index 职称代码 on members;删除主键：alter table 数据表名 drop primary key；alter table members drop primary key；删除外键：alter table 数据表名 drop foreign key 外键标识；alte
34、r table members drop foreign key members_ibfk_1;外键标识使用 show create table members;的constraint得到members_ibfk_149外键参照完整性（理解）外键表外键与参照表主键建立数据关联，该关联构成表间约束（参照完整性）。外键定义中的on delete cascade约束、on update cascade约束。on delete cascade约束关联表同步删除。on update cascade约束关联表同步更新。50维护数据表记录-增加记录1.增加记录（掌握）insert into 数据表名 (字段
35、名1, 字段名n) values (数值1，数值n) ；字段i顺序任意，字段i与数值i一一对应。增加记录例题：insert into membersx (身份证号,性别,出生日期,工作单位) values (110102197801030561,男,1978-01-03,中国人民大学) ；注意：增加记录时主键字段或UNIQUE索引字段不能重复外键字段值在关联表中主键值必须已经存在51删除记录2.删除记录（掌握）命令格式1：删除数据表中全部记录。delete from 数据表名；删除数据表中全部记录例题：delete from membersx；命令格式2：删除数据表中符合条件的记录。delet
36、e from 数据表名 where 条件表达式删除数据表中符合条件的记录例题：delete from members where 身份证号= 1101021978352修改记录3.修改记录（掌握）命令格式1：修改数据表中全部记录指定字段的值update set , 字段名n=数值n字段i顺序任意，字段i与数值i一一对应，同时修改多个字段时set只使用一个。修改全部记录指定字段值例题：update member set 会员密码=999999；命令格式2：修改数据表中符合条件的记录指定字段的值update set , 字段名n=数值n where 修改身份证号为11010119800101011
37、1的会员密码为888888，会员姓名为赵华例题：update member set 会员密码=888888，会员姓名=赵华 where 身份证号=110101198001010111；53选取数据表记录（掌握）选取数据表中指定字段，指定记录的结果，并对输出结果进行重组。select from where order by 字段名 asc | desc group by 字段名表：显示获取结果from数据表名表：数据源where条件表达式：获取数据条件order by 字段名：按字段进行升序asc或降序desc排序group by 字段名：按字段进行分组例题：select 身份证号,性别,yea
38、r(curdate()-year(出生日期) as 年龄 from members where 性别=女 order by 年龄 desc；54选取数据表记录例题：select 身份证号,性别 from members group by 性别 asc；例题：like %、_选项select 身份证号,会员姓名,会员密码 from member where 会员姓名 like 张%;显示member数据表姓张的人员信息。select 身份证号,会员姓名,会员密码 from member where 会员姓名 like 张_;显示member数据表姓名2个字姓张的人员信息。例题： like %选项
39、select 身份证号,会员姓名,会员密码 from member where 会员密码5555 and 会员姓名 not like 张%;显示member数据表不姓张并且会员密码5555的人员信息。55选取数据表记录选取输出来自于两个以上的数据表的数据集合，定义输出条件和输出的字段或表达式。输出的字段必须指明所属数据表，同时还要指明数据表之间的关联。使用数据表别名(as mb)。例题： 显示member数据表的身份证号、会员姓名、会员密码select 身份证号,会员姓名,会员密码 from member;显示members数据表的身份证号,性别,出生日期。select 身份证号,性别,出生日
40、期 from members;select mb.身份证号,会员姓名,会员密码,性别,出生日期 from member as mb,members where mb.身份证号=members.身份证号;56选取数据表记录选取输出来自于五个数据表的数据集合。输出同名字段必须指明所属数据表，必须用and分隔定义表间关联。例题：输出订单号(sell)、书名(book)、单价(book)、身份证号(member)、会员姓名(member)、性别(members)、职称名称(title)。 select 订单号,书名,单价,member.身份证号,会员姓名,性别,职称名称 from book,membe
41、r,title,members,sell where sell.身份证号=member.身份证号 and sell.图书编号=book.图书编号 and members.身份证号=member.身份证号 and members.职称代码=title.职称代码;57选取数据表记录选取输出来自于五个数据表的数据集合。输出同名字段必须指明所属数据表，必须用and分隔定义表间关联。例题：输出订单号(sell)、书名(book)、单价(book)、身份证号(member)、会员姓名(member)、性别(members)、职称名称(title)。选取条件，张宇、赵成新、李来群三人购买的图书清单。 sel
42、ect 订单号,书名,单价,member.身份证号,会员姓名,性别,职称名称 from book,member,title,members,sell where (sell.身份证号=member.身份证号 and sell.图书编号=book.图书编号 and members.身份证号=member.身份证号 and members.职称代码=title.职称代码) and 会员姓名 in (张宇,赵成新,李来群);58选取数据表记录选取输出来自于五个数据表的数据集合。输出同名字段必须指明所属数据表，必须用and分隔定义表间关联。例题：性别(members),sum(订购册数(sell),
43、sum(单价(book)*订购册数(sell) , sum(单价*订购册数)/ sum(订购册数) as 平均价格，按姓别分组并计算男女性购书的册数、金额、平均价格。 select 性别,sum(订购册数),sum(单价*订购册数) ,sum(单价*订购册数)/sum(订购册数) as 平均价格 from member,book, members,sell where (members.身份证号=member.身份证号 and sell.身份证号=member.身份证号 and sell.图书编号=book.图书编号) group by 性别;59选取数据表记录使用子查询作为查询条件，获取符合
44、条件的记录和指定部分字段作为输出结果。命令格式：select from where order by asc|desc group by 例题：身份证号，性别，出生日期，输出条件是出生日期最大（年龄最小，使用max()函数）。 select 性别,性别,出生日期 from members where 出生日期=(select max(出生日期) from members group by 性别 having 性别=女);60选取数据表记录例题：选取身份证号，性别，职称代码，输出条件是正高级职称（职称代码最右边第一位数是1）使用right函数。 select 身份证号,性别,职称代码 from
45、members where 职称代码 in (select 职称代码 from members where right(职称代码,1)=1);例题：选取会员姓名，性别，职称名称，输出条件是职称为医生系列或者是正高级职称（职称代码最左边第一位数是3）使用left函数和right函数。 select 身份证号,性别,职称代码 from members where 职称代码 in (select 职称代码 from members where left(职称代码,1)=3 or right(职称代码)=1);61选取数据表记录将一个数据表中的记录添加到另一个结构相同的数据表中，复制数据表。命令格式：insert into (字段名表) select from 例题：将members数据表性别为女的记录添加到memberc数据表中。insert into memberc (身份证号,性别,出生日期,工作单位,单位地址,邮政编码,职称代码) select 身份证号,性别,出生日期,工作单位,单位地址,邮政编码,职称代码 from members where 性别=女;
