第一天：Mysql数据库

一、Mysql的安装

１、打开下载的mysql安装文件mysql-5.0.27-win32.zip，双击解压缩，运行“setup.exe”。

 

２、选择安装类型，有“Typical（默认）”、“Complete（完全）”、“Custom（用户自定义）”三个选项，选择“Custom”，按“next”键继续。

３、在“DeveloperComponents（开发者部分）”上左键单击，选择“This feature,

and allsubfeatures, will be installed on local hard drive.”，即“此部分，及下属子部分内容，全部安装在本地硬盘上”。在上面的“MySQL Server（mysql服务器）”、“Client Programs（mysql客户端程序）”、“Documentation（文档）”也如此操作，以保证安装所有文件。点选“Change...”，手动指定安装目录。

 

４、填上安装目录，我的是“F:\Server\MySQL\MySQLServer 5.0”，也建议不要放在与操作系统同一分区，这样可以防止系统备份还原的时候，数据被清空。按“OK”继续。

确认一下先前的设置，如果有误，按“Back”返回重做。按“Install”开始安装。

 

 

 

５、正在安装中，请稍候，直到出现下面的界面

这里是询问你是否要注册一个mysql.com的账号，或是使用已有的账号登陆mysql.com，一般不需要了，点选“Skip Sign-Up”，按“Next”略过此步骤。继续则完成MYSQL的安装。

 

二、MYSQL的配置

１、安装完成了，出现如下界面将进入mysql配置向导。

 

２、选择配置方式，“DetailedConfiguration（手动精确配置）”、“Standard Configuration（标准配置）”，我们选择“Detailed Configuration”，方便熟悉配置过程。

３、选择服务器类型，“DeveloperMachine（开发测试类，mysql占用很少资源）”、“Server Machine（服务器类型，mysql占用较多资源）”、“Dedicated MySQL Server Machine（专门的数据库服务器，mysql占用所有可用资源）”

 

 

４、选择mysql数据库的大致用途，“MultifunctionalDatabase（通用多功能型，好）”、“TransactionalDatabase Only（服务器类型，专注于事务处理，一般）”、“Non-Transactional Database Only（非事务处理型，较简单，主要做一些监控、记数用，对MyISAM数据类型的支持仅限于non-transactional），按“Next”继续。

５、选择网站并发连接数，同时连接的数目，“DecisionSupport(DSS)/OLAP（20个左右）”、“Online Transaction Processing(OLTP)（500个左右）”、“Manual Setting（手动设置，自己输一个数）”。

６、是否启用TCP/IP连接，设定端口，如果不启用，就只能在自己的机器上访问mysql数据库了，在这个页面上，您还可以选择“启用标准模式”（Enable Strict Mode），这样MySQL就不会允许细小的语法错误。如果是新手，建议您取消标准模式以减少麻烦。但熟悉MySQL以后，尽量使用标准模式，因为它可以降低有害数据进入数据库的可能性。按“Next”继续

７、就是对mysql默认数据库语言编码进行设置（重要），一般选UTF-8，按 “Next”继续。

８、选择是否将mysql安装为windows服务，还可以指定ServiceName（服务标识名称），是否将mysql的bin目录加入到WindowsPATH（加入后，就可以直接使用bin下的文件，而不用指出目录名，比如连接，“mysql.exe-uusername -ppassword;”就可以了，不用指出mysql.exe的完整地址，很方便），我这里全部打上了勾，ServiceName不变。按“Next”继续。

９、询问是否要修改默认root用户（超级管理）的密码。“Enable root access from remotemachines（是否允许root用户在其它的机器上登陆，如果要安全，就不要勾上，如果要方便，就勾上它）”。最后“Create An Anonymous Account（新建一个匿名用户，匿名用户可以连接数据库，不能操作数据，包括查询）”，一般就不用勾了，设置完毕，按“Next”继续。

 

 

１０、确认设置无误，按“Execute”使设置生效，即完成MYSQL的安装和配置。

 

注意：设置完毕，按“Finish”后有一个比较常见的错误，就是不能“Startservice”，一般出现在以前有安装mysql的服务器上，解决的办法，先保证以前安装的mysql服务器彻底卸载掉了；不行的话，检查是否按上面一步所说，之前的密码是否有修改，照上面的操作；如果依然不行，将mysql安装目录下的data文件夹备份，然后删除，在安装完成后，将安装生成的 data文件夹删除，备份的data文件夹移回来，再重启mysql服务就可以了，这种情况下，可能需要将数据库检查一下，然后修复一次，防止数据出错。

二、数据库的使用

1、打开window“服务”

win+r，“运行”-》service.msc

2、启动和关闭（必须以管理员身份运行cmd命令窗口）

开启数据库服务：net start mysql

关闭数据库服务：net stop mysql

3、登录

mysql -uroot -p

4.退出数据库

exit;

quit;

q;

5、查看数据库的版本号和当前日期

select version(),current_date;

6、将mysql用作一个简单的计算器

select sin(pi()/4),(4+1)*5;

7、‘\c’表示取消命令，它切换回到mysql>，开始接受一个新的命令

8、查询当前使用的用户

select user();

三、SQL语句的分类

1、数据定义语言，简称DDL

用来定义数据库对象：数据库database，表table，列column等。

关键字：创建create，修改alter，删除drop等

2、数据库操作语言，简称DML

关键字：插入：insert，删除delete，更新update等

3、数据查询语言，简称DQL

关键字：select，from，where等

4、数据库控制语言，简称DCL

四、对数据库的操作

1、创建一个库

create database 库名

查看MySQL支持的所有字符集

show character set;

创建带有编码的

create database 库名 character set 编码

查看编码

show create database web08_01;

2、查看数据库

查看数据库服务器中的所有数据库：show databases;

查看某个数据库的定义的信息：show create database 数据库名；

3、删除一个库

drop database 库名

4、使用库

use 库名

5、查看当前正在操作的库

select database();

五、对数据库表的操作

1、创建一张表

create table 表名(

    字段名 类型(长度) [约束],

    字段名 类型(长度) [约束],

);

字符类型：varchar(n)

单表约束：

    主键约束：primary key，要求被修饰的字段：唯一和非空

    唯一约束：unique，要求被修饰的字段：唯一

    非空约束：not null，要求被修饰的字段：非空

    自动增长：auto_increment

2、查看数据库表

创建完成后，我们可以查看数据库表

show tables;

查看表的结构

desc 表名

3、删除一张表

drop table 表名

4、修改表

4.1添加一列

alter table 表名 add 列名 类型（长度）[约束];    -----修改表添加列

4.2修改列的类型（长度、约束）

alter table 表名 modify 要修改的字段名 类型（长度）[约束]

4.3修改列的列名

alter table 表名 change 旧列名 新列名 类型（长度）[约束]

4.4、删除表的列

alter table 表名 drop 列名

4.5、修改表名

rename table 表名 to 新表名

4.6、修改表的字符集

alter table 表名 character set 编码

4.7、修改字段的排列位置

alter table 表名 modify 列名1 数据类型 first|after 列名2

4.8查看当前编码

show create table tbl_user;

4.9装载数据

load data local infile 'path/1.txt' into table sort lines terminated by '\r\n';

4.10、字段类型

4.11、比较运算符

=、<=、>=、<>(!=)、<=>、>、<、is null、is not null、least、greatest、between、and、isnull、in、not in、like、regexp

三、对数据库记录进行操作（修改）

1、插入操作

insert into 表名（列名1，列名2，列名3）values（值1，值2，值3....）

insert into 表名 values(值1，值2，值3....)

注意：

    1、列名数与values后面的值的个数相等

    2、列的顺序与插入的值得顺序一致

    3、列名的类型与插入的值要一致

    4、插入值的时候不能超过最大长度

    5、值如果是字符串或者日期需要加引号‘’（一般是单引号）

1.1插入数据中文乱码问题解决办法

方式一【不建议】

    直接修改数据库安装目录里面的my.ini文件的第57行

方式二：

    set names gbk;

2、更新表记录

2.1不带条件的

update 表名 set 字段名=值，字段名=值，字段名=值....

它会将该列的所有记录都更改

2.2带条件的

update 表名 set 字段名=值，字段名=值，字段名=值....where 条件

3.删除表记录

3.1带条件的

delete from 表名 where 条件

注意：删除后，uid不会重置！

3.2不带条件的

先准备数据

insert into tbl_user values(null,'王五'，'666');

删除操作

delete from 表名

3.3例题

说说delete与truncate的区别

delete删除的时候是一条一条的删除记录，它配合事务，可以将删除的数据找回。

truncate删除，它是将整个表摧毁，然后再创建一张一模一样的表。它删除的数据无法找回

delete操作演示：

truncate演示操作

注意：delete删除，uid不会重置！而使用truncate操作，uid会重置

4.查询操作

语法：

    select [distinct] * |列名，列名 from 表名[where条件]

4.1简单查询 

1.查询所有商品

select * from product;

2.查询商品名和商品价格

select pname,price from product;

3.查询所有商品信息使用表别名

select * from product as p;

4.查询商品名，使用列别名

select pname as p from product

5.去掉重复值（按照价格）

select distinct(price) from product

6.将所有的商品的价格+10进行显示

select pname,price+10 from product;

4.2条件查询

1.查询商品名称为“左慈”的商品信息

select * from product where pname='左慈';

2.查询价格>60元的所有商品信息

select * from product where price>60;

3.查询商品名称含有“士”字的商品信息

select * from product where pname like '%士%';

4.查询商品id在(3,6,9)范围内的所有商品信息

select * from product where pid in(3,6,9);

5.查询商品名称含有“士”字并且id为6的商品信息

select * from product where pname like '%士%' and pid=6;

6、找出正好包含5个字符的名字

select * from pet where name like '_____';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

7.查询id为2或者6的商品信息

select * from product where pid=2 or pid=6;

4.3排序

1.查询所有的商品，按价格进行排序（asc升序、desc降序）

select * from product order by price asc;

select * from product order by price desc;

2.查询名称有“士”的商品信息并且按照价格降序排序

select * from product where pname like '%士%' order by price desc;

4.4聚合函数

常用的聚合函数：sum()求和，avg()平均，max()最大值，min()最小值，count()计数

1.获得所有商品的价格的总和

select sum(price) from product;

2.获得所有商品的平均价格

select avg(price) from product;

3.获得所有商品的个数

select count(*) from product;

4.5分组操作

1.添加分类id(alter table product add cid varchar(32);)

2.初始化数据

update product set cid='1';

update product set cid='2' where pid in(5,6,7);

1.根据cid字段分组，分组后统计商品的个数

select cid,count(*)from product group by cid;

2.根据cid分组，分组统计每组商品的平均价格，并且平均价格大于20000元

select cid,avg(price)from product group by cid having avg(price)>20000

3、从第3条记录开始显示，显示3条记录

select * from sort limit 2,3;

("2"表示的是从第三条记录开始，是从0开始计数的，"3"表示查询3条记录)

4.6、使用函数查询

1、日期函数

计算每个宠物的年龄

select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age from pet

year()：提取日期的年部分

right()：提取日期的MM-DD（日历年）部分的最右面5个字符

+----------+------------+------------+------+

| name     | birth      | CURDATE()  | age  |

+----------+------------+------------+------+

| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |

| Claws    | 1994-03-17 | 2003-08-19 |    9 |

| Buffy    | 1989-05-13 | 2003-08-19 |   14 |

| Fang     | 1990-08-27 | 2003-08-19 |   12 |

| Bowser   | 1989-08-31 | 2003-08-19 |   13 |

| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |

| Whistler | 1997-12-09 | 2003-08-19 |    5 |

| Slim     | 1996-04-29 | 2003-08-19 |    7 |

| Puffball | 1999-03-30 | 2003-08-19 |    4 |

+----------+------------+------------+------+

4.6使用正则表达式查询

1、字符‘^’：匹配以特定字符或者字符串开头的文本

找出以"s"开头的名字

select * from sort where sname regexp '^s';

2、字符‘$’：匹配以特定字符或者字符串结尾的文本

找出以"ao"结尾的名字

select * from sort where sname regexp 'ao$';

3、字符‘.’：匹配任意一个字符

找出名字中带有"n"的

select * from sort where sname regexp '.n.';

4、星号‘*’：匹配前面的字符任意多次，包括0次

select * from sort where sname regexp '^bn*';

5、加号‘+’：匹配前面的字符至少一次

select * from sort where sname regexp '^di+';

6、字符‘|’：匹配多个字符串，多个字符串之间使用分隔符‘|’隔开

select * from sort where sname regexp 'di|nn|ao';

7、方括号[]：指定一个字符集，只匹配其中任何一个字符，即为所查找的文本

select * from sort where sname regexp '[nf]';

8、字符串{n,}：表示至少匹配n次前面的字符

select * from sort where sname regexp 'd{2,}';

9、字符串{n,m}：表示匹配前面的字符串不少于n次，不多于m次

4.7查询总结

select 一般后面的内容都是要查询的字段

from 要查询的表

where 查询条件

group by 分组条件

having 分组后带有条件只能使用having

order by 排序字段它必须放到最后面asc|desc

六、多表操作

1、外键

现在我们两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表product”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系

外键特点：

    从表外键的值是对主表主键的引用

    从表外键类型，必须与主表主键类型一致

声明外键约束

    语法：alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名)                  references 主表 (主表的主键)；

          [外键名称] 用于删除外键约束的，一般建议“_fk”结尾

          alter table 从表 drop foreign key 外键名称

使用外键目的：

    保证数据完整性

alter table product add foreign key(category_id) references category(cid);

alter table product add constraint product_fk foreign key(category_id) references category(cid);

删除外键

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

2、表与表之间的关系

2.1、一对多关系

    常见实例：客户和订单，分类和商品，部门和员工

    一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键

建表语句

#创建数据库

create database day09_db;

#使用数据库

use day09_db;

#创建分类表

create table category(

    cid varchar(32) primary key, #主表的主键

    cname varchar(100)   #分类名称

);

#商品表

create table product(

    pid varchar(32) primary key,

    name varchar(40),

    price double 

);

#添加外键字段 category_id

alter table product add column category_id varchar(32);

#添加约束

alter table product add constraint product_fk foreign key(category_id) references category(cid);

总结：

    从表不能够添加（更新），主表中不存在的数据

    主表不能够删除（更新），从表中已经使用的数据

2.2多对多关系：

    常见实例：学生和课程，商品和订单，人和角色

    多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向

    各自一方的主键。（就是将一个多对多拆分成两个一对多。）

两张表分别都是主表，第三张表为从表，提供两个字段，都是外键

#商品表

create table product(

    pid varchar(32) primary key,

    name varchar(40),

    price double

);

#订单表

create table orders(

    oid varchar(32) primary key,

    totalprice double    #总计

);

#订单项表

create table orderitem(

    oid varchar(50),--订单id

    pid varchar(50) --商品id

);

#联合主键（可省略）

alter table orderitem add primary key (oid,pid);

#---订单表和订单项表的主外键关系

alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);

#---商品表和订单项表的主外键关系

alter table orderitem add constraint orderitem_product_fk foreign key (pid) references products(pid);

3、多表查询操作

3.1、交叉连接查询（基本上不会使用-得到的是两个表的乘积）

    语法：select * from A,B;

3.2内连接查询（使用的关键字inner join --inner可以省略）

    隐式内连接：select * from A,B where 条件

    显示内连接：select * from A inner join B on 条件

3.3外连接查询（使用的关键字outer join --outer可以省略）

    左外连接：left outer join

        select * from A left outer join B on 条件；

    右外连接：right outer join

        select * from A right outer join B on 条件;

3.4、查询例题

#1.查询哪些分类的商品已经上架

select * from category,product where cid=category_id;

select * from category c,product p where c.cid=p.category_id;

#隐式内连接

select distinct cname from category c,product p where c.cid=p.category_id;

#内连接

select distinct cname from category c inner join product p on c.cid=p.category_id;

#2.查询所有分类上架商品的个数

#左外连接

select cname,count(category_id) from category c left outer join product p on c.cid=p.category_id group by cname;

3.5、子查询

一条select语句结果作为另一条select语句一部分（查询条件，查询结果，表等）。

#子查询，查询“化妆品”分类上架商品详情

#隐式内连接

select * from product p,category c where p.category_id=c.cid and c.cname='化妆品';

#子查询

select * from product where category_id=(select cid from category where cname='化妆品');

注意：

    从表外键不能添加主表中不存在的记录。

    主表不能删除从表中已经引用的记录。

七、存储引擎

存储引擎：也叫表类型，指的是数据表的存储机制，索引方案等配套相关功能。不同的引擎，由于处理方式不同，会带来不同的功能或者相应优化，根据实际需求选择合理的引擎。

选择表的存储引擎的标准：

    是否需要事务支持

    是否支持外键

    并发量的要求

    错误恢复的处理

    是否支持某些具体功能

最常用的存储引擎：

    MyISAM：分开保存、支持索引压缩、是表级锁、不支持事务，外键、保存表中具体的行数

    InnoDB：保存到表空间、索引和数据是绑定保存的，不压缩，体积大、很多时候是行级锁，并发               高、不支持FULLTEXT类型的索引（新版本的InnoDB也支持），支持事务，外键，数据完整             性约束强、不保存表中具体行数

7.1、查看数据库支持的引擎

show engines;

7.2、查看当前所使用的存储引擎

show create table 表名

7.3、更改表的存储引擎

alter table 表名 engine=更改后的存储引擎名