数据库学习入门数据库基础入门.pdf

《数据库学习入门数据库基础入门.pdf》由会员分享，可在线阅读，更多相关《数据库学习入门数据库基础入门.pdf（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库学习入门数据库基础入门数据库学习入门 数据库基础入门-数据库的作用：-数据是数据库中存储的基本对象包括：文字、图形、图像、声音有组织的、可共享的数据集合-常用的 DBMS -MYSQL（oracle、Sun）oracle（oracle）SQL-Server（Microsoft)DB2（IBM）-.如何学习数据库管理系统-.程序员1、标准语言：SQL（结构化查询语言）SQL是用于访问数据库的最常用标准化语言，他是由ANSI/ISO SQL标准定义的SQL 的分类DML：数据操作语言（用于检索或者修改数据）DDL：数据定义语言（用于定义数据的结构，比如创建、修改或者删除数据库的对象）DCL：

2、数据控制语言（用于定义数据库用户的权限）DML:select:用于检索数据insert:用于增加数据到数据库update:用于从数据库中修改现存的数据delete：用于从数据库中删除数据DDL:create table/创建表alter/修改表drop table/删除表create index/创建索引drop index/删除索引create view/创建视图drop view/删除视图DCL：grant/授权revoke/撤销授权set/设置2、不同数据库的独特管理方式.DBA（Database Administrator）1、标准语言：SQL（结构化查询语言）2、不同数据库的独特管理

3、方式3、数据库的调优4、精通数据库的备份、回复机制-MYSQL 的安装与配置-安装：选择普通安装类型.配置1、选择配置类型：Detailed Configuration 2、选择服务器类型：Developer Machine 3、选择数据库的是使用情况：-多功能数据库（Multifunctional Database）-只是事务处理数据库（Transactional Database Only）-只是非事务处理数据库（Non-Transactional Database Only）4、InnoDB表空间文件存储的盘符，默认在c：5、数据库并发连接数配置：Decision support 6、M

4、YSQL 服务器端口：默认：3306 7、Enable Strict Mode：是否允许严格样式检查：取消8、选择字符编码：手工选择：gb2312，windows下中文支持9、服务器默认安装到windows 系统服务中，默认服务自启动，选择自动配置环境变量10、填写数据库管理系统的管理员密码-在 mySQL 中数据库管理员的用户名为：root 11、是否允许使用 root 用户远程登录数据库12、测试配置是否正确c：c:mysqlbinmysqlshow -MYSQL 的登录-.开始-运行-cmd .输入：mysql-uroot-p，然后输入密码.或者：mysql-uroot-p密码.退出 q

5、uit或者q -不必全在一个行内给出一个命令，较长命令可以输入到多个行中。mysql 通过寻找终止分号而不是输入行的结束来决定语句在哪儿结束-等待多行命令的下一行 等待下一行，等待以单引号开始的字符串结束如果你决定不想执行正在输入过程中的一个命令，输入c 取消它能够以大小写输入关键词，结果是等价的-基本的 SQL语句-创建数据库-create database school；查看有哪些数据库-show databases；删除数据库-drop database school；-向数据库中存储数据-1、首先进入数据库 use dbname；use 类似 quit，不需要一个分号，写也无碍use

6、必须在一个单行上给出2、查看数据库中的表 show tables；3、在数据库中创建表 create table student （name varchar（20），age int，sex char（2）；4、向表中插入数据-insert into dbname values（张三，18，男）；5、查看表中所有的数据-select*from student；只查看姓名和年龄-select name，age from student；-常用的数据类型-char（M）固定长度字符用来表示固定长度字符串，字符串的长度是1-255 VARCHAR（M）可变长度字符具有灵活性的字符数据类型，范围：1-2

7、55 处理 char 类型的数据比 varchar 类型的数据要快，有时会快50%（char 类型存储的数据的长度是声明变量时的固定长度，而不管数据的实际长度，varchar 存储的是按数据的实际长度，从而减小了数据文件的大小）int 整数 有符合的范围：-2147483648 到 2147483647，无符号范围：0 到 4294967295 -其他常用的数据类型-DATE-日期类型，显示 YYYY-MM-DD DATETIME-日期和时间的组合，显示YYYY-MM-DD HH:MM:SS TEXT/BLOB 文本和大对象TEXT可以保存字符串的长度在255-65535 字节内。BLOB 是

8、一个能保存二进制数据的大对象，区别是 TEXT 不区分大小写，而 BLOB 区分大小写。SQL语句的导入1、编写 SQL脚本（.sql）2、导入 mysql-uusername-p abc.sql 查看表的结构-desc tablename 修改表中的数据-update tablename set colname=value where条件删除数据-delete from teacher where age=？删除表中所有数据-delete from teacher；删除表-drop table tablename 根据条件进行过滤查找-select*from tablename where

9、条件查找出版社为“清华出版社”的所有书籍-select*from books where pub=清华出版社；查找出库存大于 50 的所有书籍-select*from books where store50；查找出“西游记”的库存量-select title，store from books where title=西游记；SQL运算符大于=大于等于 =小于等于 !=,不等于查看数据库的信息-s -mysql 常用函数-1、查看数据库版本-select versin（）；计算机的时间是存在BIOS（）2、查看当前数据库的日期-select current_date（）；3、查看当前数据库时间-

10、select now（）；4、查看当前连接数据库的用户-select user（）；localhost：代表是本机create table user(id int,name varchar(20),bir date,dea datetime);insert into user values(1,zhangsan,now(),now();or 和 and查询or（满足一个条件）and（都需要满足）查找学生 EQ为 80 分或 90 分的学生select*from stu where EQ=80 or EQ=90;查找学生 EQ为 90 分并且性别为女的学生select*from stu wher

11、e EQ=90 and sex=女;in（x，x）返回条件中的记录与or 作用相似select*from tablename where 条件 in（，）；between and返回两者之间的记录查询年龄在 20-30 之间的所有学生select*from stu where age between 20 and 30；like与通配符（%）一起使用，模糊查询查找出姓张的学生select*from stu where name like 张%；查询出使用 163 邮箱的所有学生select*from stu where email like ；查询出邮箱里面含有a 的所有学生select*fr

12、om stu where email like%a%；order by 实现排序（从小到大）-asc 将学生的年龄从高到低排列select*from stu order by age desc；（降序）将学生的年龄从低到高排列select*from stu order by age asc；（升序）数据默认为升序（从低到高）select*from stu order by age；as 为查询的列起别名1、查询所有学生只列出姓名，年龄，性别select name as 姓名,age as 年龄,sex as 性别 from stu;group by 对于查询出的数据结果进行分类（分组）2、将学

13、生按性别进行分类select*from stu group by sex；将学生按年龄进行分类select*from stu group by age；3、having 子查询：对于 where 查询出的结果再次进行查询查找出年龄大于 20 岁学生，并且在其中找出姓名等于xxx 的学生select*from stu where age 20 having name=xxx或select*from stu where age20 and name=xxx 4、distinct 过滤查询的重复型记录，只显示唯一的记录将学生性别过滤select distinct(sex)from stu;count

14、 查看表中有几条数据select count(*)from stu;select count(distinct(sex)from stu;limit 限制查询结果的输出数量同时也可以实现数据的分页查询 EQ前三名的学生select*from stu order by EQ desc limit 3;实现查询记录的分页select*from stu limit 0,3;select*from stu limit 3,3;约束-定义了表级的强制规则、数据的完整性非空约束（not null）create table test(id int not null);insert into test val

15、ues();innodb 会报错，myISAM 会整形默认以 0 填充唯一约束（UNIQUE)不允许列中的数据重复create table test(id int,unique(id);insert into test values(1);默认约束(default)create table test(id int not null default111111);insert into test values();主键约束(primary key)是一个字段或一组字段（组合键），用于唯一标识表中的记录，它可以确保每个记录是唯一的。create table test(id int,primary

16、key(id);insert into test valuse(1);id 主键自增,减create table test(id int，name varchar（20），primary key（id）)；insert into test values(1,张安)；auto_increment的特点-自增值 当删除某一值时，他不会自动填充，而是继续自增1 create table test(id int not null auto_increment,name varchar(20),primary key(id);insert into test(name)values(xxx)；创建一个有

17、合理约束的表create table people(id int not null auto_increment,name varchar(20)not null,age int not null,sex char(2)not null,pcode varchar(50),tel varchar(50),email varchar(50),primary key(id);insert into people(name,age,sex,email)values(张三，28，男，);查询出所有学生的档案信息select*from student,school where student.daih=

18、school.id;或select*from student as a,school as b where a.daih=b.id;查询所有学生档案信息只需显示：学生姓名，年龄。毕业学校，学校地址select student.name,student.age,school.name,school.addres s from student,school where student.daih=school.id;或select a.name,a.age,b.name,b.address from student as a,school as b where a.daih=b.id;外键约束(fo

19、reign key)如何创建外键foreign key:定义子中的列为外键references:标记外键所要参考的父表和列on delete cascade:允许在删除父表的列的同时，删除子表的列/在 InnoDB中支持create datebase fordb;use fordb;创建父表create table school(id int not null auto_increment,name varchar(20),primary key(id)engine=innodb;insert into school(name)values(紫琅学院);创建 子表create table st

20、udent(id int not null auto_increment,name varchar(20),schoolid int,primary key(id),foreign key(schoolid)references school(id)on delete cascade);insert into student(name,schoolid)values(张三,1);E-R模型概述：设计数据库时，通常采用 实体关系模型“-E-R 模型软件开发流程：需求调研（设计功能，收集数据）与最终用户进行确定数据库的设计控制多余数据那么在最后，数据库设计者确定表、不同表之间的关系以及每个表之间的

21、关系，通常使用”E-R 模型“，它将整个系统看作彼此相关的实体组成实体：通常用于表示能够被清晰识别的人、地点、事物、对象、事件实体关系图：如果需要基于E-R 模型建立数据库模型，需要标识实体，实体的属性、以及实体之间的关系。那么通常用E-R 图来表示实体之间的三种类型：1：1 1：N 或 N:1 M:N 数据库设计员确定的实体被转换为表，而其属性则成为相应表中的字段（列）如何控制冗余数据：一般来说通过数据库的范式理论设计数据库的范式来控制冗余共有五个范式，一般达到第三范式即可第一范式：对于表中的每一行，必须且仅仅有唯一的行值，在一行中的每一列仅有唯一的值并且具有原子性第二范式：要求 非主键列是

22、主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素，一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键 无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系。第三范式：要求非主键列互不依赖第四范式：禁止主键列和非主键列一对多关系不受约束第五 范式：将表分割成尽可能小的块，为了排除在表中所有的冗余MYSQL 的聚合函数1、最大值找出 EQ最高的学生select name,eq from student where eq=(select max（EQ）from student)；SELECT MAX(article)AS article FROM shop;2、

23、最小值找出 EQ最低的学生select name，min（EQ）from student；count（）/统计 查询出的记录总数3、查询出学生的总条数select count（name）from student；avg（）/求平均值4、查询学生 EQ的平均值select avg（EQ）from student；sum（）/求和5、查询学生 EQ的总和select sum（EQ）from stu；修改字段类型alter table student modify sex char（5）；增加列alter table student add address varchar（50）；删除列alter

24、table student drop address；修改列的名称alter table student change name names varchar（20）；修改表的名字rename table student to stu；MYSQL 之表连接内链接(inner join)：又为等值连接，因为他将两个表中的公共字段有相等的值（记录）连接起来，这是最常用的连接。一个表引用还被称为查询显示：订单编号，顾客姓名，物品名称select cases.id,user.name,goods.name from cases,user,goods where cases.user_id=user.id

25、 and cases.goods_id=goods.id;或select cases.id,user.name,goods.name from cases inner join(user,goods)on(user.id=cases.user_id and goods.id=cases.goods_id);或select cases.id,user.name,goods.name from(cases inner join user on cases.user_id=user.id)inner join goods on cases.goods_id=goods.id;或select case

26、s.id,user.name,goods.name from user inner join(cases inner join goods on cases.goods_id=goods.id)on user.id=cases.user_id);左连接:显示 sql 语句中 left join 左边表中的所有记录，即使在left join 右边的表中没有满足连接条件的数据也被显示。当满足连接条件时，left join 右边的表中的相应的记录与left join 左边表中的相应记录结合显示。查询出：学生的编号，学生姓名，学生学校select student.name,student.id,sch

27、ool.name from student left join school on student.school_id=school.id;右连接：与左连接相对应，他显示sql 语句中 right join 右边表中的记录，即使在right join 左边没有的记录也被显示。当满足条件时。right join左边表中相应记录将与right join右边的表中相应记录进行结合显示。通常左连接和右连接显示的内容是一样的。select student.name,student.id,school.name from student right join school on student.schoo

28、l_id=school.id;left join 以左边的表查询为主right join 以右边的表查询为主示例：student left join school 那么 student 就为左表。school 就为由表MYSQL 之视图视图：它可以访问一个或多个表中的数据，是从一个或多个表中派生出的数据对象（虚表）视图的特点：1、将复杂的查询简单化2、提供安全机制，它保证用户只可以看得到的数据，系统中真实的表是不可被存取的（现都支持更改数据）创建视图：create view case_view as select cases.id as 订单编号,user.name as 顾客姓名,goods

29、.name as 物品名称 from user inner join(cases inner join goods on cases.goods_id=goods.id)on user.id=cases.user_id;注意点：视图属于数据库，在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图。创建时，应将名称指定为db_name.view_name mysqlcreate view test.v as select*from t;视图必须具有唯一的列名，不得有重复，就像基表那样。修改视图:alter view viewname as SQL;删除视图：drop view

30、viewname;alter view case_view as select cases.id as 编号,user.name as 姓名,goods.name as 名称 from user inner join(cases inner join goods on cases.goods_id=goods.id)on user.id=cases.user_id;数据字典数据库的数据字典-实际上是一个数据库它记录着数据库管理系统中的其他数据库的操作mysql 数据库字典：information_schema use information_schema select*from views;-

31、MYSQL 的事务与引擎-什么是 事务（transaction）所谓事务，它是一个操作序列。这些操作要么都执行，要么都不执行，他是一个不可分割的工作单位事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性mysql 表类型：在 mysql 中有多种表的类型，但是分为两类:事务类型：InnoDB、BDB 非事务类型：MYISAM、MERGE、MEMORY（HEAP）InnoDB和 BDB 存储引擎提供事务安全表。BDB被包含在 mac支持它的操作系统发布的 mysql-max 二制分发版里。InnoDB也默认被包括在所有mysql5.1 二进制分发版里InnoDB是为处理巨大数

32、据量时的最大性能设计。它的cpu 效率可能是任何其他基于磁盘的关系数据库引擎所有不能匹敌的。MYISAM 管理非事务表。他提供高速存储和检索，以及全文搜索能力。ISAM数据表是 mysql 最原始的数据表，有三个文件，分别是：.frm，存放数据表的结构定义.isd，数据文件，存放数据表中的各个数据行的内容.ism,索引文件，存放数据表的所有索引信息。MYISAM 是数据表是 ISAM数据表的继承者，也是三个文件，分别是：.frm,结构定义文件；.MYD，数据文件；.MYI，索引文件。BDB 数据表用两个文件来表示，分别是;.frm，结构定义文件.DB，数据表数据和索引文件INNODB:由于采用

33、表空间的概念来管理数据表，所以他只有一个与数据表对应.frm 文件，同一目录下的其他文件表示为空间，存储数据表的数据和索引HEAP 数据表是一个存在与内存中的表，所以他的数据和索引都存在于内存中，文件系统中只有一个.frm 文件，及定义结构，当mysql 关闭后数据都将消失。定义表的的类型create table test（id int）engine=heap；事务表币非事务表有几大优势：更安全。即使 mysql 崩溃或遇到硬件问题，要么自动恢复，要么从备份加事务日志恢复，你可以取回数据你可以合并许多语句，并用commit 语句同时接受她们全部（如果autocommit 被禁止掉）你可以执行

34、rollback来忽略你的改变（如果autocommit 被禁止掉）如果更新失败，你的所有改变都变回原来。（用非事务安全表，所有发生的改变都是永久的）事务安全存储引擎可以给那些当前用读得到许多更新的表提供更好的部署。非事务表自身有几大优点：非事务表自身有几大优点，因为没有事务开支，所有优点都能出现：更快需要更更少的磁盘空间执行更新需要更少的内存你可以在同一个语句中合并事务安全和非事务安全表来获得两者最好的情况。尽管如此，在autocommit 被禁止掉的事务里，变换到非事务安全表依旧即使提交并且不会被回滚如何在 innodb 中实现事务：begin:/告知服务器我要开始一个事物rollback

35、:/如果事务发生异常，那么数据回滚commit:/事务执行成功，进行数据提交mysql begin;Query OK,0 rows affected(0.00 sec)mysql insert into test11 values(1,zhangs);Query OK,1 row affected(0.00 sec)mysql select*from test11;+-+-+|id|name|+-+-+|1|zhangs|+-+-+1 row in set(0.00 sec)mysql end;ERROR 1064(42000):You have an error in your SQL sy

36、ntax;check the manual that corresponds to your MySQL server version for the right syntax to use near end at line 1 mysql rollback;Query OK,0 rows affected(0.00 sec)mysql select*from test11;Empty set(0.00 sec)mysql commit;Query OK,0 rows affected(0.00 sec)mysql select*from test11;Empty set(0.00 sec)修

37、改表类型sql 语句：alter table tablename engine=innodb；注意：不可以将 mysql 系统表比如 user或host 转换成 innodb类型。系统表必须为myisam类型对 myisam进行事务处理-锁lock table tablename（read，write）lock table 可以对表进行加锁控制，以保证用户并发访问时非事务表的数据一致性unlock tables 释放锁mysql 之 index（索引）什么是索引？索引被用来快速找出在一个列上用一特定值的行。没有索引，mysql不得不首先以第一条记录开始并然后读完整个表直到他找出相关的行，表越大

38、，花费时间越多。如果表对于查询的列有一个索引，mysql 能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。索引的作用：快速找出匹配一个where 子句的行当执行连接时，从其他表检索行对特定的索引列找出max（）或 min（）值mysql 中的索引：索引实际上是一个分离的列表，具有一个指向全表的指针myisam表，索引被存储为分离文件innodb，索引存储为表空间的一部分mysql 有四种类型的索引：主键（primary key）、唯一索引（unique）、全文索引（full index）、普通索引（index）创建 index 1、create index indexname

39、on tablename（columnname）；indexname（索引名称）tablename（表名称）columnname（要创建索引的字段名称）2、alter table tablename add index indexname（columnname）；查看索引-show keys from tablename；删除 index-alter table tablename drop index indexname；full index（myisam支持）create table chat（id int，chat1 text，chat2 text）；create fulltext in

40、dex index_text on tablename（col1，col2）；alter table tablename add fulltext（col1，col2）；高效使用索引：太多的索引，占用空间每次进行插入或更新时，索引都必须针对变化重新排序，会导致很多额外负担何处使用索引？1、where从句中条件匹配的行select name from student where comment like“a%”；comment域的索引就起到了作用Explain 分析索引语法：explain select age from test12;Explain 分析 mysql 中的索引：table：查询

41、中涉及的表type：显示连接中使用了何种类型const（最好），eq_ref，fef，range，index 和 all possible_keys：可能用到的索引key：实际使用的索引，可以在select语句中使用 use index（indexname）来强制使用索引，用ignore index（indexname）来强制忽略索引 key_len：使用的索引长度，越短越好；ref(references)：显示索引哪一列被使用了rows：mysql 必须检查用来返请求数据的行数extra：using temporary（需要临时表来存储结果，通常发生在对不同的列集进行 order by），u

42、sig filesort 意即 mysql 根本 不能使用索引索引的算法：btree bitmap 使用索引注意的事项;1、合理创建索引，反之会降低数据库的查询效率，反之创建的索引会失去意义。mysql 的备份方式：backup、restore、copy/冷备份、mysqldump、select into backup 备份 myisam表的示例：use test;backup table chat to c:db_backup;只生成 .frm 和.myd,可以在数据库恢复后重建索引drop table chat;restore table chat from c:db_backup;cop

43、y 备份1、停止 mysql 服务器一避免在备份的时候有用户进行数据库的访问1、复制数据库的文件夹用 mysqldump备份备份库1、备份#mysqldump-uroot-p dbname xxxname.sql 再打开查看（创建表备份的sql 语句）2、恢复#mysql-uroot-p dbname 备份某个表1、备份#mysqldump-uroot-p dbname tablename xxxname.sql 再打开文件进行查看（创建表备份的sql 语句）2、恢复#mysql-uroot-p dbname 用 select into 备份备份：mysqlselect*from tablen

44、ame into outfile c:db_backuptable.dat 删除表：mysqldelete from tablename;恢复：mysqlload data infile c:db_backuptable.dat into table tablename;作业：1、唯一索引2、全文索引要求：理解她们的意思及用途，以及实验3、select into备份MYSQL 其他客户端工具查看数据库中有哪些数据库mysqlshow-uroot-p 查看数据库中有哪些表mysqlshow-uroot-p dbname 查看数据库中表的结构mysqlshow-uroot-p dbname tab

45、lename mysqlcheck 客户端可以检查和修复myisam表，他还可以优化和分析表1、mysqlcheck-uroot-p dbname/检测2、mysqlcheck-uroot-p-auto-repair dbname/如碰到有问题的表自动进行修复3、mysqlcheck-uroot-p-optimize/优化表MYSQL 管理方式连接方式-mysql-hlocalhost-uroot 通过本地主机，以root用户访问，无需密码验证mysql-hlocalhost 通过本地主机，匿名用户访问，无需密码验证设置密码-mysqladmin 外部：1、修改密码-mysqladmin hl

46、ocalhost-uroot-p password newpassword 2、密码为空-mysqladmin-hlocalhost-uroot-p password 3、设置 root 用户远程访问密码-mysqladmin-hremote-uroot-p password 内部设置密码：1、设置 root 用户本地登录密码set password for rootlocalhost=password(000000);2、设置 root 用户远程登录密码set password for rootremote=password(newpassword);加密：mysql use start D

47、atabase changed mysql create table users(username varchar(50),password varchar(50);Query OK,0 rows affected(0.16 sec)mysql insert into users values(abcd,123456);Query OK,1 row affected(0.34 sec)mysql insert into users values(abcdfg,password(123456);Query OK,1 row affected(0.00 sec)mysql select*from

48、users;select user，host，password form user；+-+-+|username|password|+-+-+|abcd|123456|abcdfg|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|+-+-+2 rows in set(0.00 sec)系统中的权限表user 表：记录允许连接到服务器的用户帐号信息，里面的权限是全局 级的db表：记录各个帐号在各个数据库上的操作权限table_priv记录数据表级的操作权限columns_priv：记录数据列级的操作权限host 表：配合 db 权限表对给定主机上数据库级操作权

49、限作更细致的控制。这个权限表不受grant 和 revoke 语句的影响-设置密码-修改 user 表直接修改 user 表mysqluse mysql;mysqlupdate user set password=password(newpassword)where user=root;mysqlflush privilegs;重新加载权限表，使修改立即生效添加用户mysql use mysql mysql insert into user(host,user,password)values(localhost,test,password(222222);mysql flush privile

50、ges;授权：grant all on*.*to testlocalhostidentified by 222222 grant select on stus.*to testlocalhostidentified by 121212 grant privileges（columns）privileges表示授予的权限，columns 表示作用的列（可选）on what 设置权限级别：全局级、数据库级、表级、列级to account 权限授予的用户:用user _namehost_name这种用户名、主机名格式、identified by password设置密码privileges有哪些al