MySQL

                                         MySQL

一 数据库基础

1.1 为什么要使用数据库？

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。持久化的大多数时候是将内存中的数据存储在数据库中，当然也可以存储在磁盘文件、XML数据文件中。

方便管理数据（例如：快速的检索等

1.2 什么是数据库

DB：数据库（Database）即存储数据的“仓库”。它保存了一系列有组织的数据。

DBMS：数据库管理系统（Database Management System）：是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。

目前互联网上常见的数据库管理软件有Sybase、DB2、Oracle、MySQL、Access、Visual Foxpro（面向对象型）、MS SQL Server、Informix、PostgreSQL（最符合SQL标准，但是性能差）这几种。

**MySQL是一种开放源代码的关系型数据库管理系统，开发者为瑞典MySQL AB公司。

**关系型数据库：关系数据库的表采用二维表格来存储数据，是一种按行与列排列的具有相关信息的逻辑组，它类似于Excle工作表。一个数据库可以包含任意多个数据表。表中的一行即为一条记录。数据表中的每一列称为一个字段，表是由其包含的各种字段定义的，每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和其他属性。行和列的交叉位置表示某个属性值，如“数据库原理”就是课程名称的属性值。

**SQL：结构化查询语言（Structured Query Language）。

二、 windows版MySQL安装：

 

 

三、MySQL的使用：

-- 开起MySQL服务

net start mysql;  

-- 关闭Mysql服务

net stop mysql;

-- 查看所有数据库

show databases;

-- 创建数据库

create database  databasename;

--  删除数据库

drop database   databasename;

-- 选择数据库

use  databasename;

-- 创建表

create table  tablename(字段1  字段类型，字段2  字段类型，字段3 字段类型);

-- 查看表结构

desc  tablename;

-- 删除表结构

drop table tablename;

2.  修改表结构

--  重命名表

alter  table tablename rename  新tablename;

rename table tablename to 新tablename;

-- 增加一列

alter  table  tablename  add  列名 数据类型；//默认在最后

alter  table  tablename  add  列名  数据类型   after  某一列；

alter  table  tablename  add  列名   数据类型  first ;

-- 删除列

alter table  tablename drop  列名；

-- 修改列类型

alter  table  tablename  modify 列名  数据类型 ；

alter  table  tablename  modify  列名  数据类型   after  某一列；

alter  table  tablename  modify  列名   数据类型  first ;

--  修改列名等

alter table  tablename  change  列名 新列名  数据类型；

-- 查看表的约束和索引

show    index from tablename;

show  create table  tablename;

3. 约束：

　　1. 主键约束：primary  key 

　　　　主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复

　　　　每个表有且最多只允许一个主键约束。

　　2. 唯一键： unique  key 简称 UK ;

　　　　 1. 同一个表可以有多个唯一约束

　　　　 2. 唯一约束可以是某一列的值唯一，也可以多个列组合值的唯一

　　　　 3. Mysql会给唯一约束的列上默认创建一个唯一索引

　　　　 4. 删除唯一键只能通过删除唯一索引的方式删除，删除时需要指定唯一索引名，唯一索引名就是唯一约束名一样。如何创建唯一约束未指定如果是单列，就默认为和列明相同，如果是组合列，那么默认和（）中排在第一个的列名相同。也可以自定义唯一约束名

--------

如何建立唯一性约束？

在某个列后面直接加唯一性约束	单独指定表的唯一性约束	组合列唯一性约束
CREATE TABLE t_course( cid INT PRIMARY KEY, cname VARCHAR(100) UNIQUE, description VARCHAR(200));	CREATE TABLE t_stu( sid INT PRIMARY KEY, sname VARCHAR(100), card_id CHAR(18), CONSTRAINT uk_card_id UNIQUE KEY(card_id));#其中CONSTRAINT uk_cname和KEY可以省略	CREATE TABLE t_stu_course( id INT PRIMARY KEY, sid INT, cid INT, score DOUBLE(4,1), CONSTRAINT uk_sid_cid UNIQUE KEY(sid,cid));#其中CONSTRAINT uk_sid_cid和KEY可以省略
建表后增加唯一性约束	alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名);alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名1，字段名2);
ALTER TABLE t_course ADD CONSTRAINT uk_cname UNIQUE KEY(cname);#其中CONSTRAINT uk_cname和KEY可以省略	ALTER TABLE t_stu ADD CONSTRAINT uk_card_id UNIQUE KEY(card_id);#其中CONSTRAINT uk_cname和KEY可以省略	ALTER TABLE t_stu_course ADD CONSTRAINT uk_sid_cid UNIQUE KEY(sid,cid);#其中CONSTRAINT uk_cname和KEY可以省略

如何删除唯一性约束和索引？

　　alter table  tablename  drop  index  唯一性约束名：如果忘记名称，可以通过show index from  tablename;查看

**主键和唯一键的区别：

　　1. 主键是非空，唯一键允许空

　　2. 主键一个表只能有一个，唯一键可以有多个

3、外键：foreign  key, 简称 FK

 

• 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。

• 当创建外键约束时，系统默认会在所在的列上建立对应的普通**索引**。但是索引名是列名，不是外键的约束名。

• 删除外键时，关于外键列上的普通索引需要单独删除。

　　注意：

　　

• 在从表上建立外键，而且主表要先存在。

• 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键）。

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

• 一个表可以建立多个外键约束

• 从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键列的值的添加和修改。

• 当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。

n （1）默认情况下，主表和从表是严格依赖关系RESTRICT。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。

n （2）但是有一种是级联“修改、删除”：

n ON DELETE SET NULL(级联置空)：当外键设置了SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。注意从表外键字段得允许为空才行

n ON DELETE CASCADE(级联删除)：当外键设置了CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了。

n 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

l 如果要删除表，需要先删除从表，才能删除主表

 

　　如何创建外键约束：

　　-- 创建外键：

　　create table tablename(did int primary key, dname varchar(100)  not  null unique, description  varchar(200) not  null);

　　create table  t_employee(eid int primary key,ename varchar(100) not null,dept_id int,

　      foreign key(dept_id)  REFERENCES  tablename(did) on             　

　　　update cascade on delete  RESTRICT);

     　#其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE 　　

RESTRICT如果省略表示都是RESTRICT

 

　　--  一个表可以有多个外键，而且主表而且主表和从表可以是一张表

　　CREATE TABLE t_emp( eid INT PRIMARY KEY,

　　　　　　　　　　　　　ename VARCHAR(100) NOT NULL,

　　　　　　　　　　　　   manager_id INT,

　　　　　　　　　　　　   dept_id INT,

　　　　　　　　　　　　   FOREIGN KEY(dept_id) REFERENCES t_department(did)  ON UPDATE CASCADE ON DELETE RESTRICT,

　　　　　　　　　　　　   FOREIGN KEY(manager_id) REFERENCES t_emp(eid)  ON UPDATE CASCADE ON DELETE RESTRICT);

　　　　　　　　　　　　 #其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

　  --   建表后创建外键

　　alter table表名称 add  foreign key (从表字段名) references 主表名(主表被参照字段名);

　　ALTER TABLE t_emp ADD  FOREIGN KEY(dept_id) REFERENCES t_department(did) 

　　 ON UPDATE CASCADE ON DELETE RESTRICT;

　　#其中CONSTRAINT fk_emp_dept_did可以省略

　　#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

　 如何删除外键约束？

　　　alter  table 表名称  drop  foreign  key  外键约束名;

　　　alter  table  从表  drop  foreugn key  外键约束；

　　非空约束：

　　not  NULL 非空约束，规定某个字段不能为空，如果某列已经创建好，那么可以修改列语句修改：

　　　　例如：原来非空，修改为允许空

　　　　例如：原来允许空，修改为非空

　　检查约束：

　　注意：MySQL不支持check约束，但可以使用check约束，而没有任何效果；

　　　　例如：age tinyint  check(age>20) 或 sex char(2) check(sex in('男','女'))

　　Default缺省约束：

　　default: 默认值，再插入数据时某列如果没指定其他的值，那么会将默认值添加到新记录。

　　如果某列已经建好，那么可以修改列语句修改：

　　例如：原来有默认值，去除默认值

　　例如：原来没有默认值，增加默认值

　 索引:index

　　索引：索引是数据库表中一列或多列的值进行排序的一种结构。

　　索引是一个单独的、物理的数据库结构，他是某个表中一列或若干个列值的集合和相应

　　的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知，索引是要消耗数据库空间的

　　而约束是一种逻辑概念。

　　索引的好处：

　　　　加快了查询速度

　　索引的缺点：

　　　　降低了增，删，改的速度，增大了表的文件大小（索引文件甚至可能比数据文件还大）

　　MySQL提供了多种索引类型供选择：

　　　　普通索引 ：

　　　　唯一性索引：

　　　　主键索引：只有一个主键索引

　　　　全文索引：mysql5.X版本只有MyISAM存储引擎支持

　　　　FULLTEXT,并且只限于CHAR，VARCHAR和TEXT类型的列上创建

　　Mysql的索引方法：

　　HASH

　　BTREE

　　mysql中多数索引都以btree的形式保存。

　　索引的使用规则：

　　　　1. 不过度索引

　　　　2. 索引条件列（where后面最频繁的条件比较适宜索引）

　　　　3. 索引散列值，过于集中的值不要索引，例如：给性别‘男’，‘女’加索引，意义不大

　　　　--- 创建索引：

　　　　　　create index  索引名  on 表名称（colimn_name,[colimn_name......]);#最左边列最关键

　　　　-----  删除索引　　

　　　　　　alter table  表名称  drop  index  索引名；

　　自增列：AUTO_INCREMENT

　　　　例如：

　　　　关于自增长auto_increment:

　　　　　　1.整数类型的字段才可以设置自增长。

　　　　　　2.当需要产生唯一标识符或顺序值时，可设置自增长。

　　　　　　3. 一个表中最多只能有一个自增列

　　　　　　4. 自增长列必须非空

　　　　　　5. 自增长列必须是主键列或唯一键列。

　　　　　　6. InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，

　　　　　　实际插入的将是自动增长后的值。　　　

四、MySQL的逻辑架构与存储引擎：

4.1、MySQL逻辑架构：

 连接层，服务层，引擎层，存储层

4.2、Mysql几种存储引擎：

InnoDB，Myisam，Memory，Merge，NBD

4.3 、查看存储引擎：

//查看当前存储引擎

show  ENGINES;

五、MySQL的数据类型

5.1、常用的数据类型有：

整型(int)，

位类型(bit)，

浮点型（float、double、real），

定点数(decimal，numeric)，

日期时间类型（time、date、datetime、year），

字符串(char 、varchar、xxx  text)，

二进制数据（xxx  Blob,xxx binary），

枚举（enum）

5.2、特殊的NULL值

NULL特征：

1、所有的类型的值都可以是null,包括int、float等数据类型

2、空字符串“ ”，不等于null，0也不等于null，false也不等于null

3、任何运算符，判断符碰到NULL，都得NULL;

4、NUll的判断只能用is null,is not null;

5、NUll影响查询速度，一般避免使值为NULL

六、SQL

6.1、SQl的语言规范

1. mysql对于SQL语句不区分大小写，SQL语句关键字尽量大写

2. 值，除了数值型，字符串型和日期时间类型使用单引导（‘ ’）

3. 别名，尽量使用双引导（“ ”），而且不建议省略 as

4. 所有标点符号使用英文状态下的半角输入方式

5. 必须保证所有（），单引号，双引号是成对结束的

6. 可以使用（1）#单行注释 （2）--空格单行注释 （3） /* 多行注释*/

SQL:命名规则：

1. 数据库、表名不得超过30个字符，变量名限制为29个

2. 必须只能包含A-Z,a-z,0-9

3. 不能在对象名的字符间空格

4. 必须不能和用户义的其他对象重名

5. 必须保证你的字段没有和保留字、数据库系统或常用方法冲突

6. 保持字段名和类型的一致性，在命名字段并为其指定数据类型的数据类型的时候一定要保证

一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了，

注：一个语句可以分开多行写，以；或\g

　　

6.2 SQL分类：

DDL：