数据库理念和mysql语句应用

 MySQL数据表类型

1、在创建一个新的MySQL数据表时，可以为他设置一个类型

MySQL支持多种数据表类型，各自具有各自的特点和属性，其中最重要的3种是：MyISAM,InnoDB,HEAP(MEMORY)

使用show engines可以显示可用的数据库表类型./configure --with-innodb prefix=/my 

2、常见的show命令

• show databases ---- 显示所有的数据库

• show tables ---------- 显示指定数据库下所有的表

• desc tablename ----- 显示表的结构

• show table status --- 显示表的状态信息

3、创建不同类型的表

• 在创建表时指定表的类型： create table tbl_test (id int) engine= InnoDB

• 如果在创建表时，没有指定具体类型，默认为 MyISAM

• 也可以使用 / etc / my.cnf 的 msqld 节的 default-table-type= InnoDB 修改默认类型 .

• MyISAM 数据表类型的特点是成熟，稳定和易于管理 , 只要没有特殊要求，应该选择这种

• 可以把 InnoDB 看做 MyIASM 的一种更新换代产品

• InnoDB 增加了几种新功能

– 事物

– 数据库锁定机制

– 外键约束

– 崩溃恢复

• HEAP 数据表数据只存在内存中 , 主要用于充当临时表

MySQL数据类型

• 每个表中至少有一个数据列，用户必须他定义一个适当的数据类型

• MySQL 中的数据类型如下 :

– 数值类型

– 日期与时间类型

– 字符串类型

– 二进制数据类型

– 其他数据类型

1、数值类型

• 整数

– tinyint,1 个字节

– smallint , 2 个字节

– Mediumint , 3 个字节

– int,integer,4 个字节

– bigint , 8 个字节

– serial 是 bigint auto_increment not null primary key 的缩写

• 浮点数

– float( m,d ), 4 个字节

– double( m,d ), 8 个字节

– real( m,d ) 是 double( m,d ) 的同义词

• 定点数

– decimal( p,s ), 数字个数不限 (p<=65,s<=30)

2、日期与时间类型

• date  //2010-12-12 

• time  //21:32:32

• datetime// sysdate ()

3、字符串类型

• char(n) // 固定长度的字符串  n<=255

• varchar(n) // 可变长度的字符串 n<=6535

• text // 可变长度的字符串 ,2 16 -1

• longtext // 可变长度的字符串 ,2 32 -1

4、二进制数据类型

• bit(n) // 二进制数据， n 是 2 进制位的个数 ,n<=64

• blob // 可变长度的二进制数据 ,2 16 -1

• longblob // 可变长度的二进制数据 ,2 32 -1

5、其它数据类型

• enum // 枚举数据

create table tbl_emp(id int,sex enum('f','m'));

• set// 集合数据

create table tbl_emp(

  id int,

  favi set('game','reader','basketball')

);

数据库设计概论

• 一切数据库应用程序的第一阶段工作是对数据库进行设计

• 数据库设计的好坏对应用程序的执行问题将产生深远的影响

1、一个好的数据库设计方案应该满足以下几项要求:

–   数据表里没有重复冗余的数据

–   数据表中没有 address1,address2,… 等数据列

–   全体数据表的空间占用量越小越好

–   使用频率高的查询都能以简单高效的方式执行。

2、起个好名字

• MySQL 对数据列不区分大小写，但是对数据库与数据表的名字却区分，所以在起名统一模式• 数据库，数据表，数据列的名字最多 64 个字符

• 在名字中避免使用特殊字符

• 名字要有意义 , 如 tbl_author,authname

3、具体设计工作中的技巧

• 用最短的时间把杂乱无章的数据组织为一些井井有条的数据并不容易

–   从一批数量相对较小的测试性数据入手尝试着把他们纳入一个或者多个表

–   在第一次尝试时不要是 MySQL, 可以使用 Excel 或者 Word 等

• 假设有如下杂乱无章数据 :

Michael Kofler:Linux,7th edition.Addison-Wesley 2004. Michael Kofler,David Kramer:Definitive Guide to Excel VBA ,Second Edition.Apress 2003. Robert Orfali,Dan Harkey,Jeri Edwards:Client/Server Survival Guide. Addison-Wesley 1997. Tobias Ratschiller,Till Gerken:Web Application Development With PHP4.0.New Riders 2000. 初次尝试  •数据库理论家早总结出一个通用的方案,只需一步一步把三个范式的规则应用到自己的数据库上就可以了.

 第一范式

•表达相同内容的数据列必须消除

•每个数据记录必须用一个主键来标识

 第二范式

•函数依赖主键数据列出现重复必须消除(再创建一个表来存放他们)

•拆分形成的数据表必须用外键关联起来

 

 再次第二范式

 

第三范式 

•传递依赖主键数据列必须消除(再创建一个数据表存放他们)

•拆分形成的数据表必须用外键关联起来

 

 

 简单的SQL操作

主要内容

1、简单查询 2、对查询结果排序 3、筛选数据记录

4、统计与分组 5、修改数据

 简单查询

•SELECT * FROM tablename是最简单的数据库查询命令。

•*的含义为：本次查询操作涉及指定表的所有数据

 •有时候，并不需要查看数据的具体内容，而只想确定数据表中有多少行记录,可以使用一些查询

//select count(*) from publishers

//select count(pubId) from publishers

 •如果数据表中的某个列有完全相同的内容，而读者只想知道内容不重复的数据记录有多少个，就就必须使用distinct关键字。

•select count(distinct publid) from titles;

•有时候，并不需要看到数据表的全部数据，这时需要使用在select命令后面明确写出现在要查询的数据列的名字(而不是*)

•select publname from publishers

•不仅可以对查询结果中的数据列个数加以限制，还可以对查询结果中数据记录的个数加以限制.•select titleid,title from titles limit 2

•select titleid,title from titles limit 2,2

•2，2 从第二个开始显示2条记录

对查询结果排序

•如果没有设置，select命令就不会对它们返回的查询结果进行排序。

•如果想查询结果按照某种顺序排序，就必须明确地使用一个order by column子命令做出必要的设定。

 select * from authors order by authname;

select * from authors order by authname desc;

select * from authors order by authname,authid;

筛选数据结构

•很多时候，用户感兴趣的不是数据表的全部数据，而是满足某一种某几种条件的记录,这类条件要用where关键字来给出。

select * from authors where authname>='M';

运算符

• 关系运算符

   =, <=, >=, >,  <, <>, !=

• 逻辑运算符

and ,or,not

• 算术运算符

   +,-,*,/,%

• 其他运算符

  is ,like,in, between ...and…

应用实例

•select * from authors where authname>='M‘ or authname<=‘M’;

•select * from authors where authname like '%er%';

•select * from authors where authid in (2,7,12);

•select publid ,title from titles where publid is not null;

表达式与别名

• 在 select 后面可以使用表达式

• 可以取别名

select count(publid)+1 as count from publishers;

统计

• 有时我们并不想要数据表的详细信息，而只要统计信息，就需要用到统计函数 :

  sum,avg,min,max,count

  select count(year) from titles;

  select min(year) from titles;

  select max(year) from titles;

  select avg(year) from titles;

分组统计与筛选

• select publid , min(year) from titles group by publid ;

•

• select publid , count(*) from titles group by publid having count(*)>=4;

修改数据

•修改数据主要讲解insert/update/delete命令

•insert 语法

INSERT INTO tablename(columnA,columnB…)

VALUES(vA,vB…)

INSERT INTO tablename

VALUES(vA,vB…)

• update 语法

  UPDATE tablename

  SET columnA = vA,columnB=vB…

  [WHERE]

• delete 语法

  DELETE FROM tablename [WHERE]