MySQL数据库管理系统基础课件.ppt

1,MySQL数据库管理系统,2,MySQL数据库基本知识数据库模型概念数据库服务器管理用户的方法MySQL数据库、数据表维护管理方法,3,MySQL优化网站/书籍分享,MySQL Performance Blog MySQL 中文网MySQL性能调优与架构设计 深入浅出MySQL,4,谁在用MYSQL,5,谁在用MYSQL,6,MySQL数据库管理系统概述,1、MySQL数据库管理系统的特点性能高效而稳定，MySQL几乎比当前可用的其他所有数据库的性能都不差，因此Yahoo、Google、Cisco、HP和NASA等都采用了它作为自己的数据库引擎。开放源代码，MySQL是自由的开放源代码产品，可以在GPL下畅通使用。多用户支持，MySQL可有效地满足501000个并发用户的访问，并且在超过600个用户的限度的情况下，MySQL的性能并没有明显的下降。多线程，MySQL使用核心线程的完全多线程，这意味着可以采用多CPU体系结构。,7,1、MySQL数据库管理系统的特点开放性，支持ANSI SQL-99标准，适用于多种操作系统（如Linux、Solaris、FreeBSD、OS/2、MacOS以及Windows95/98/Me/2000/XP和Windows NT等），可在多种体系结构（如Intel x86、Alpha、SPARC、PowerPC和IA64等）上运行。广泛的应用程序支持，有C、C+、Java、Perl、PHP和Python等多种客户工具和API的支持。支持事务处理、行锁定、子查询、外键和全文检索等功能。支持大数据库处理，可对某些包含50 000 000个记录的数据库使用MySQL。有灵活且安全的权限和口令系统，并且允许对其他主机的认证，等等。,8,2、MySQL操作模式命令操作界面打开命令操作界面，单击“开始”“运行”输入“mysql uroot p”,显示mysql,9,3.数据库服务器安装有数据库管理系统的计算机服务器。管理方式：支持客户机/服务器（C/S）主要职责：管理数据库用户、维护数据库和数据表文件4.数据库用户被授权允许使用数据库、数据表的人员。数据库管理员：id=root，pwd职责，维护用户账号、维护数据库和数据表。普通用户：由数据库管理员创建用户账号，并授权维护数据库，数据表。,10,MySQL数据模型由数据库文件与数据表文件构成。数据库文件：用于管理数据表构成信息的文件，数据库文件名唯一（不重复）。数据表文件：用于管理数据记录的文件，数据表文件名唯一（不重复）。数据表由，数据表文件名+数据表结构+数据记录构成。,11,6.常见数据类型（具体数据类型看附表）字符型（姓名，职称，地址）数值型（工资，成绩，数量）可以进行运算日期型（出生日期，毕业时间）逻辑型（只有两种状态，逻辑真和逻辑假）7.MySQL的数据存储结构MySQL数据库管理系统保存在“C:/AppServ/MySQL”系统数据库MySQL，用户创建的应用数据库bookstore保存在“C:/AppServ/MySQL/Data”,12,MySQL列类型,数值数据类型,13,字符数据类型,14,日期和时间数据类型,15,MySQL服务器用户管理每一个使用MySQL数据库的用户必须拥有一个合法的账号（包括用户名，用户密码）和相应的权限。MySQL数据库管理员账号，用户名为root，用户密码（在安装过程中设置的密码，可以随时修改）。普通用户的账号和权限必须由数据库管理员创建和授权。,16,登录MySQL服务器,使用数据库管理员的账号登录，用户名root，用户密码。方法一，使用命令行方式登录单击“开始”“所有程序”“AppServ”“MySQL command line client”输入密码。出现mysql登录成功方法二 使用“运行”登录MySQL服务器。单击“开始”“运行”命令格式：mysql u-p,17,MySQL服务器的用户管理,MySQL服务器中内置系统数据库，数据库名为mysql，数据库中内置数据表，数据表名为user。只能由数据库管理员（root）对用户账号进行维护，增加新用户、修改用户密码、删除用户账号、用户账号授权。,18,User数据表中所有授权用户权限,19,MySQL命令格式,命令关键词；分号表示命令结束并提交1）打开数据库Use;例：use mysql;打开 mysql 数据库2）增加新用户的账号到mysql数据库user表Insert into 数据表名(主机名,用户名,用户密码,权限1,权限2,)values(主机名值,用户名值,用户密码值,权限1值,权限2值,);3）激活数据Flush privileges;,20,1.增加新的用户(掌握）创建新的用户账号包括用户名、用户密码、用户权限。打开mysql数据库插入新的用户账号数据到user数据表激活新创建的用户账号命令格式：mysqluse mysql；mysqlinsert into mysql.user(主机名称,用户名称,用户密码,权限1,权限2,)values(host,user,password,select_priv,)；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 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；,23,2.修改用户权限（理解）命令格式：update set 权限参数名称=权限值 where；mysqluse mysql；mysqlupdate mysql.user set 权限名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；mysqlupdate mysql.user set password=password(“222222”)where user=“mysqladmin2”；mysqlflush privileges；,26,3.删除用户账号命令格式：（掌握）delete from where mysqluse mysql;mysqldelete from mysql.user where；mysqlflush privileges；例题删除mysqladmin2用户账号mysqluse mysql；mysqldelete from mysql.user where user=mysqladmin2；mysqlflush privileges；,27,MySQL数据管理,维护数据库：显示数据库，建立数据库，删除数据库，打开数据库。维护数据表：建立数据表，显示数据表文件名，显示数据表结构，修改数据表结构，删除数据表文件，更换数据表名。维护数据表的数据（记录）：增加记录，删除记录，修改记录，选取记录。,28,维护MySQL数据库(掌握）,1.显示数据库(MySQL数据库是系统数据库)显示当前服务器中所有的数据库。命令格式：show databases；2.建立数据库在当前服务器中创建一个新的数据库。命令格式：create database；3.删除数据库删除当前服务器中的一个数据库。命令格式：drop database；4.打开数据库打开当前服务器中的一个数据库。命令格式：use；,29,维护MySQL数据表,数据表=数据表名、数据表结构、数据记录。定义数据表结构（字段名、字段类型、宽度、辅助项）数据表维护工作：1.创建一个新的数据表、2.显示存在的数据表、3.显示一个数据表的结构、4.修改数据表结构、5.删除数据表、6.更换数据表名。,30,维护MySQL数据表,显示数据表名。（掌握）show tables；显示数据表结构。（掌握）describe；修改、增加、删除字段名称、字段类型（理解）修改字段命令格式：alter table 数据表名 change 原字段名 新字段名；修改字段名例题：alter table members 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 exists 数据表名；直接删除数据表例题：drop table members;删除members数据表如果数据表存在就删除数据表：drop table if exists members;如果members数据表存在就删除members数据表更换数据表名。（理解）rename table 数据表名 to 新数据表名；更换数据表名例题：rename table members to membersx;将数据表名为members的更换为membersx,33,维护MySQL数据表,1.建立新的数据表，定义表文件名、字段名、字段类型、字段宽度、设置字段属性。命令格式：（掌握）create table（,，）；字段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(idINTAUTO_INCREMENT PRIMARY KEY,nameVARCHAR(30)NOT NULL,password VARCHAR(32)NOT NULL,ageINTNOT NULL,sexVARCHAR(2)DEFAULT 男,birthdayDATE);,35,建立数据表,创建数据表方法二：批处理方法将创建数据表的命令写入一个文本文件，扩展名为sql。文件名为：w.sql文件位置：c:appservmysqldatause student;drop table if exists members;create table members(身份证号 char(18)not null,会员密码 char(6)not null,会员姓名 char(10)not null,联系电话 varchar(20)not null,注册时间 datetime);,36,运行批处理文件，在mysql命令行窗口输入按以下格式输入命令sourcemysqlsource c:appservmysqldataw.sql,37,索引,索引-索引的作用：提高搜索速度，减少查询时间。方式一create table 创建索引（掌握）create table(字段1定义,字段n定义,index 索引名称(字段1,字段2,),unique 索引名称(字段1,字段2,);方式二create 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 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 student(学号);,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)not 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 default 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(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,外键,alter 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(身份证号),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 外键标识；一般情况通过删除表实现删除外键（外键标识可以使用“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)not 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 外键标识；alter table members drop foreign key members_ibfk_1;外键标识使用 show create table members;的constraint得到members_ibfk_1,49,外键参照完整性（理解）外键表外键与参照表主键建立数据关联，该关联构成表间约束（参照完整性）。外键定义中的on delete cascade约束、on update cascade约束。on delete cascade约束关联表同步删除。on update cascade约束关联表同步更新。,50,维护数据表记录-增加记录,1.增加记录（掌握）insert into 数据表名(字段名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：删除数据表中符合条件的记录。delete from 数据表名 where 条件表达式删除数据表中符合条件的记录例题：delete from members where 身份证号=11010219783,52,修改记录,3.修改记录（掌握）命令格式1：修改数据表中全部记录指定字段的值update set,字段名n=数值n字段i顺序任意，字段i与数值i一一对应，同时修改多个字段时set只使用一个。修改全部记录指定字段值例题：update member set 会员密码=999999；命令格式2：修改数据表中符合条件的记录指定字段的值update set,字段名n=数值n where 修改身份证号为110101198001010111的会员密码为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 身份证号,性别,year(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%选项select 身份证号,会员姓名,会员密码 from member where 会员密码5555 and 会员姓名 not like 张%;显示member数据表不姓张并且会员密码5555的人员信息。,55,选取数据表记录,选取输出来自于两个以上的数据表的数据集合，定义输出条件和输出的字段或表达式。输出的字段必须指明所属数据表，同时还要指明数据表之间的关联。使用数据表别名(as mb)。例题：显示member数据表的身份证号、会员姓名、会员密码select 身份证号,会员姓名,会员密码 from member;显示members数据表的身份证号,性别,出生日期。select 身份证号,性别,出生日期 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,member,title,members,sell where sell.身份证号=member.身份证号 and sell.图书编号=book.图书编号 and members.身份证号=member.身份证号 and members.职称代码=title.职称代码;,57,选取数据表记录,选取输出来自于五个数据表的数据集合。输出同名字段必须指明所属数据表，必须用and分隔定义表间关联。例题：输出订单号(sell)、书名(book)、单价(book)、身份证号(member)、会员姓名(member)、性别(members)、职称名称(title)。选取条件，张宇、赵成新、李来群三人购买的图书清单。select 订单号,书名,单价,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),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,选取数据表记录,使用子查询作为查询条件，获取符合条件的记录和指定部分字段作为输出结果。命令格式：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 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 性别=女;,