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

数据库基础入门数据库的作用:数据是数据库中存储的基本对象包括：文字、图形、图像、声音有组织的、可共享的数据集合常用的DBMSMYSQL (oracle、Sun)oracle (oracle)SQL-Sener (Microsoft)DB2 (IBM)查找出“西游记”的库存量select title, store from books where title=西游记；SQL运算符大于v小于 =大于等于 二小于等于！二,不等于查看数据库的信息-smysql常用函数1、查看数据库版本selectversin ()；计算机的时间是存在BIOS ()2、查看当前数据库的日期-select current_date ()；3、查看当前数据库时间-select now ()；4、查看当前连接数据库的用Pselect user () ; localhost:代表是本 机create table user(id int,name varchar(20),bir date,dea datetime);insert into user values(l,zhangsan,now(),nowO);or和and查询or (满足一个条件)and (都需要满足)查找学生EQ为80分或90分的学生 select * from stu where EQ=80 or EQ=90;查找学生EQ为90分并且性别为女的学生 select * from stu where EQ=90 and sex=女； in (x, x)返回条件中的记录与or作用相似 select * from tablcnamc where 条件 in (,); between and 返回两者之间的记录 查询年龄在20-30之间的所有学生 select * from stu where age between 20 and 3()； like与通配符(%) 一起使用，模糊查询 查找出姓张的学生select * from stu where name like 张；查询出使用163邮箱的所有学生select * from stu where email like ;查询出邮箱里面含有a的所有学生select * from 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;gro叩by对于查询出的数据结果进行分类（分组）2、将学生按性别进行分类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查看表中有几条数据select count(*) from stu;select count(distinct(sex) from stii;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 values 0；innodb会报错,mylSAM会整形默认以。填充唯一约束(UNIQUE)不允许列中的数据重复create table test(id int,unique(id);insert into test values(l);默认约束(default) create tabic tcst(id int not null dcfaultl 111111); insert into test values。；主键约束(primary key)是一个字段或一组字段(组合键)，用于唯一 标识表中的记录，它可以确保每个记录是唯一的。create table test(id int,primary key (id);insert into test valuse(l);id主键自增，减create table test (id int, name varchar (20) , primary key (id)；insert into test values。,张安)；autojncrcmcnt的特点自增值当删除某一值时，他不会自动填充，而是继续自增1create table test(id int not null auto_increment,name varchar(20),primaryr key(id);insert into test(name)values(xxx)；创建一个有合理约束的表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),primaryr key (id);insert into pccplc(namc,age,sex,email) values(弓长三28 , 男, );查询出所有学生的档案信息select * from student,school where student.daih二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.namc,a.agc,b.namc,b.addrcss from student as a, school as b where a.daih 二 b.id;外键约束(foreign key)如何创建外键foreign key淀义子中的列为夕卜键references:标记外键所要参考的父表和列on delete cascade:允许在删除父表的列的同时，删除子表的列在InnoDB中支持create datebase fbrdb;use fordb;创建父表create table schooled int not null auto_increment,name varchar(20),primary1 key(id)engine=innodb;insert into school(namc) valuesf 紫琅学院*);创建子表create table student(id int not null auto_increment,namevarchar(20),schoolid int,primary key (id),foreign key(schoolid) references school (id) on delete cascade);insert into studcnt(namc,schoolid) values张三J);E-R模型概述：设计数据库时，通常采用”实体关系模型-E-R模型软件开发流程：需求调研(设计功能，收集数据)与最终用户进行确定数据库的设计控制多余数据那么在最后，数据库设计者确定表、不同表之间的关系以及每个表之 间的关系，通常使用” E-R模型“，它将整个系统看作彼此相关的实体组 成实体：通常用于表示能够被清晰识别的人、地点、事物、对象、事件实体关系图：如果需要基于E-R模型建立数据库模型，需要标识 实体，实体的属性、以及实体之间的关系。那么通常用E-R图来表示实体之间的三种类型：1： 1 1： N或N :1 M:N数据库设计员确定的实体被转换为表，而其属性则成为相应表中的 字段（列）如何控制冗余数据：一般来说通过数据库的范式理论设计数据库的范式来控制冗余共有五个范式，一般达到第三范式即可第一范式：对于表中的每一行，必须且仅仅有唯一的行值，在一行中的每一列仅有唯一 的值并且具有原子性第二范式：要求非主键列是主键的子集，非主键列活动必须完全 依赖整个主键。主键必须有唯一性的元素，一个主键可以由一个或更多的 组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主 键。主外键关联意味着一对多的关系。第三范式：要求非主键列互不依赖第四范式：禁止主键列和非主键列一对多关系不受约束第五范式：将表分割成尽可能小的块，为了排除在表中所有的冗 余MYSQL的聚合函数1、最大值找出EQ最高的学生select name ,cq from student where cq=(select max (EQ) from student)；SELECT MAX(article) AS article FROM shop;2、最小值找出EQ最低的学生select name, min (EQ) from student；.如何学习数据库管理系统.程序员1、标准语言：SQL （结构化查询语言）SQL是用于访问数据库的最常用标准化语言，他是由ANSI/ISO SQL 标准定义的SQL的分类DML:数据操作语言（用于检索或者修改数据）DDL:数据定义语言（用于定义数据的结构，比如创建、修改或者 删除数据库的对象）DCL:数据控制语言（用于定义数据库用户的权限）DML:select:用于检索数据insert: 于增加数据到数据库叩date:用于从数据库中修改现存的数据count () 统计查询出的记录总数3、查询出学生的总条数select count (name) from student；avg () 求平均值4、查询学生EQ的平均值selectavg (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 table student drop address ；修改列的名称alter table student change name names varchar (20)；修改表的名字rename table student to stti ；MYSQL之表连接内链接(inner join):又为等值连接，因为他将两个表中的公共字段有 相等的值(记录)连接起来，这是最常用的连接。一个表引用还被称为查询显示：订单编号，顾客姓名，物品名称select cases.id,user.name,goods.name from cases,user,goods wherecases.user_id=user.id 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 oncases.user_id=user.id) inner join goods on cases.goods_id=goods.id;或select cascs.id,uscr.namc,goods.namc from user inner join(cascs inner joingoods on cascs.goods_id=goods.id) on uscr.id=cascs.uscr_id);左连接:显示sql语句中left join左边表中的所有记录，即使在left join右边的表中没有满足连接条件的数据也被显示。当满足连接条件时，leftjoin右边的表中的相应的记录与left join左边表中的相应记录结合显示。查询出：学生的编号，学生姓名，学生学校select student.name,studcnt.id,school.name from student left join school on student.school_id=school.id;右连接：与左连接相对应，他显示sql语句中right join右边表中的记 录，即使在right join左边没有的记录也被显示。当满足条件时。right join 左边表中相应记录将与right join右边的表中相应记录进行结合显示。通常 左连接和右连接显示的内容是一样的。select student.name,studcnt.id,school.name from student right join school on student.school_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.name as 物品名称from user inner join（cases inner join goods on cases.goods_id=goods.id） on user.id=cases.user_id;注意点：视图属于数据库，在默认情况下，将在当前数据库创建新视图。要想 在给定数据库中明确创建视图。创建时，应将名称指定为db_namc.vicw_namcmysqlcreate view test.v as select *from t;视图必须具有唯一的列名，不得有重复，就像基表那样。修改视图:alter view viewname as SQL;删除视图:drop view viewname;alter view case_viev as select cases.id as 编号,uscr.namc 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_schemause infdrmation_schemaselect * from views;MYSQL的事务与引擎什么是 事务(transaction)所谓事务，它是一个操作序列。这些操作要么都执行，要么都不执行, 他是一个不可分割的工作单位事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持 数据一致性mysql表类型：在mysql中有多种表的类型，但是分为两类：事务类型：InnoDB、BDB非事务类型：MYISAM、MERGE、MEMORY (HEAP)InnoDB和BDB存储引擎提供事务安全表。BDB被包含在mac支持 它的操作系统发布的mysql-max二制分发版里。InnoDB也默认被包括在 所有mysql5.1二进制分发版里InnoDB是为处理巨大数据痘时的最大性能设计。它的cpu效率可能 是任何其他基于磁盘的关系数据库引擎所有不能匹敌的。MYISAM管理非事务表。他提供高速存储和检索，以及全文搜索能力。ISAM数据表是mysql最原始的数据表，有三个文件，分别是：.frm,存放数据表的结构定义.isd,数据文件，存放数据表中的各个数据行的内容.ism,索引文件，存放数据表的所有索引信息。MYISAM是数据表是ISAM数据表的继承者，也是三个文件，分别是:.frm,结构定义文件；MYD,数据文件；MY1,索引文件。BDB数据表用两个文件来表示，分别是；.frm,结构定义文件.DB,数据表数据和索引文件INNODB:由于采用表空间的概念来管理数据表，所以他只有一个与 数据表对应.frm文件，同一目录下的其他文件表示为空间，存储数据表的 数据和索引HEAP数据表是一个存在与内存中的表，所以他的数据和索引都存在 于内存中，文件系统中只有一个m文件，及定义结构，当mysql关闭后 数据都将消失。定义表的的类型create table test (id int) engine=heap；事务表币非事务表有几大优势：更安全。即使mysql崩溃或遇到硬件问题,要么自动恢复，要么从 备份加事务日志恢复，你可以取回数据你可以合并许多语句，并用commit语句同时接受她们全部（如果 autocommit被禁止掉）你可以执行rollback来忽略你的改变（如果autocommit被禁止掉）如果更新失败，你的所有改变都变回原来。（用非事务安全表，所有 发生的改变都是永久的）事务安全存储引擎可以给那些当前用读得到许多更新的表提供更 好的部署。非事务表自身有儿大优点：非事务表自身有几大优点，因为没有事务开支，所有优点都能出现:更快需要更更少的磁盘空间执行更新需要更少的内存你可以在同一个语句中合并事务安全和非事务安全表来获得两者最 好的情况。尽管如此，在autocommit被禁止掉的事务里，变换到非事务 安全表依旧即使提交并且不会被回滚如何在innodb中实现事务：begin:/告知服务器我要开始一个事物rollback:/如果事务发生异常，那么数据回滚commit:/事务执行成功，进行数据提交mysql begin;Query OK, 0 rows affected (0.00 sec)mysql insert into testl 1 values(l /zhangs*);Query OK, 1 row affected (0.00 sec)mysql select * from testl 1;| id | name | 1 | zhangs | iI iI1 rowin ser (obo sec)mysq-v endERROR 1064 (42000)： You have an error in your SQL syn-AXJ check the manual rhar-tcorrespondsr-to your MySQL serverversion fbrrhe righr synrax ro use near -end- atJine 1mysq-v ro=backQuery OK0 rows affected (obo sec)mysv sescr * from resrl 1;Empty sc- (0.0() sec) mysq-v commk;Query OK0 rows affecs (000 sec)mysq一V sc一cct: * from -CS-11;Empty ser (0.00 sec)sq 一ffiBJ - akcr sb 一 c 3b 一 cnamc cngincllnnodb J*indelete：用于从数据库中删除数据DDL:create table 创建表alter 修改表drop table /删除表create index 创建索引drop index 删除索引create view 创建视图drop view 删除视图DCL:grant 授权revoke 撤销授权set 设置2、不同数据库的独特管理方式.DBA (Database Administrator)注意：不可以将mysql系统表比如user或host转换成innodb 类型。系统表必须为myisam类型对myisam进行事务处理-锁lock table tablename (read, write)lock table可以对表进行加锁控制，以保证用户并发访问时非事务表 的数据一致性unlock tables 释放锁mysql 之 index (索引)什么是索引？索引被用来快速找出在一个列上用一特定值的行。没有索引，mysql 不得不首先以第一条记录开始并然后读完整个表直到他找出相关的行，表 越大，花费时间越多。如果表对于查询的列有一个索引，mysql能快速到 达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。索引的作用：快速找出匹配一个where子句的行当执行连接时，从其他表检索行对特定的索引列找出max ()或min ()值mysql中的索引:索引实际上是一个分离的列表，具有一个指向全表的指针myisam表，索引被存储为分离文件innodb,索引存储为表空间的一部分mysql有四种类型的索引:主键(primary key)、唯一索引(unique)、 全文索引(full index)、普通索引(index)创建index1 create index indexname on tablename (columnname)；indexname (索引名称)tablcnamc (表名称)columnname (要创建索引的字段名称)2、alter table tablename add index indexname (columnname)；查看索引show keys from tablcnamc ；删|除 indexalter table tablename drop index indexname；full index ( myisam 支持)create tabic chat (id int, chat! text, chat2 text)；create fulltext index index_text on tablename (coll, co!2)；alter table tablename add fulltext (coll, col2)；高效使用索引：太多的索引，占用空间每次进行插入或更新时，索引都必须针对变化重新排序，会导致很多额外负担何处使用索引？K where从句中条件匹配的行select name from student where comment like “a%” ；comment域的索引就起到了作用Explain分析索引语法： explain select age from tcstl 2;Explain分析mysql中的索引：table：查询中涉及的表type：显示连接中使用了何种类型const （最好）,eq_ref, fef, range, index 和 all p（）ssiblc_kcys ： 可能用 到的索引key:实际使用的索引，可以在select语句中使用use index (indexname) 来强制使用索引，用ignore index (indexname)来强制忽略索引keyjen: 使用的索引长度，越短越好；ref(references):显示索引哪一列被使用了rows: mysql必须检查用来返请求数据的行数extra： using temporary (需要临时表来存储结果，通常发生在对不同 的列集进行order by) , usig filesort意即mysql根本 不能使用索引索引的算法：btree bitmap使用索引注意的事项；1、合理创建索引，反之会降低数据库的查询效率，反之创建的索引 会失去意义。mysql 的备份方式:backuprestore、copy/冷备份、mysqldump、 select intobackup备份myisam表的示例：use test;backup table chat to c:db_backup;只生成-frm和.myd,可以在数据库恢复后重建索引drop table chat;restore table chat from c:db_backup;copy备份1、停止mysq服务器一避免在备份的时候有用户进行数据库的访问1、复制数据库的文件夹用mysqldump备份备份库1、备份#mysqldump -uroot -p dbnamcxxxname.sql再打开查看（创建表备份的sql语句）2、恢复#mysql -uroot -p dbname 备份某个表1、备份#mysqldump -uroot -p dbname tablenamexxxnamc.sql再打开文件进行查看（创建表备份的sql语句）2、恢复#mysql -uroot -p dbname 用 select into 备份备份:mysqlselect *from tablename into outfile c:db_backuptable.dat删除表：mysqldelete from tablcnamc;恢复：mysqlload data infile c:db_backuptable.dat* into table tablename;作业：1、唯一索引2、全文索引要求：理解她们的意思及用途，以及实验3、select into 备份MYSQL其他客户端工具查看数据库中有哪些数据库mysqlshow -uroot -p查看数据库中有哪些表mysqlshow -uroot -p dbnamc查看数据库中表的结构mysqlshow -uroot -p dbname tablenamemysqlchcck客户端可以检查和修复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 hlocalhost -uroot -p password newpassword2、密码为空-mysqladmin -hlocalhost -uroot -p password 3、设置 root 用广1 远程访问密码mysqladmin -hrcmotc -uroot -p password内部设置密码：1、设置root用户本地登录密码set password for ,rootlocalhost,=password(,000000r);2、设置root用户远程登录密码set password for ,root,remote,=password(,newpassword,);加密:mysql use startDatabase changedmysql create table users (username varchar(50),password varchar(50);Query OK, 0 rows affected (0.16 sec)mysql insert into users valucsfabcd*,1123456);Query OK, 1 row affected (0.34 sec)mysql insert into users valuesf abedfg1,password(,123456r);Query OK, 1 row affected (0.00 see)mysql select * from users; select user, host, password form user；+| username | password |+4-| abed | 123456 | abedfg | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+2 rows in set (0.00 sec)系统中的权限表user表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的db表：记录各个帐号在各个数据库上的操作权限table_priv记录数据表级的操作权限columns_priv:记录数据列级的操作权限host表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受grant和revoke语句的影响设置密码-修改user表直接修改user表mysqluse mysql;mysqlupdate user set password=password(,newpassword,)whereuser二root;1、标准语言：SQL (结构化查询语言)2、不同数据库的独特管理方式3、数据库的调优4、精通数据库的备份、回复机制MYSQL的安装与配置安装：选择普通安装类型.配置1 选择配置类型：Detailed Configuration2、选择服务器类型：Developer Machine3、选择数据库的是使用情况：多功能数据库(Multifunctional Database)-只是事务处理数据库(Transactional Database Only)-只是非事务处理数据库(Non-Transactional Database Only)mysqlflush privilcgs;重新加载权限表，使修改立即生效添加用户mysql use mysqlmysql insert into user(liost,user,passvord)values(localhost,test1, password(1222222);mysql flush privileges;授权:grant all on *.* to test*localhostidentified by *222222grant select on stus.* to test*localhostidentifled by 121212grant privileges (columns)privileges表示授予的权限，columns表示作用的列(可选)on what设置权限级别：全局级、数据库级、表级、列级to account权限授予的用户:ffluser _name,host_name,这种用户名、主机名格式、 identified by password设置密码privileges 有哪些alter修改表和索引create创建数据库和表delete删除表中以有的记录drop删除数据库和表inde创建和删除索引insert向表中插入数据select检索表中的数据、update修改表中的记录file读写服务器上的数据process查看服务器中执行的线程信息或杀死线程 reload重载授权表或清空日志、主机缓存或表缓存 shutdown关闭服务器all所有权限revoke取消授权mysqlrevoke privileges(columns)on whatfrom account;注：revoke可删除权限，但不能删除帐号，即使帐号已经没有任何权 限。所以user数据表里还会有该帐号的记录，要彻底删除帐号，需使用 delete命令手工删除user表中的相关数据。示例：revoke all on *.*from,test,localhost,;delete from user wh