数据库基础知识
数据库客户端
MySQL作为数据库服务器来运行，任何满足mysql通信规范的软件都可以作为客户端来连接服务器。常用的图形化客户端：mysql_front，navcat和基于web的phpmyadmin，MySQL自带命令行客户端。-
命令行客户端
开启windows的命令行，启动mysql服务端进程，我们使用mysql客户端来连接服务器。如果出现命令提示如下所示，则说明没有配置境变量，导致系统找不到mysql.exe。C:\User> mysql -uroot -p 'mysql'不是内部或外部命令，也不是可运行的程序或批处理文件解决方案
配置环境变量$PATH，指定mysql\bin\目录。
每次进入mysql\bin\目录后，再运行mysql。
-
连接服务器
服务器地址(IP或域名):端口(3306) -u用户名 -p密码
C:\User\> mysql -h localhost -uUsername -pPassword-h 如果不写则默认连接localhost
进入刚安装好的MySQL数据库中，我们会发现内置了三个数据库information_schema(数据库基本信息)，mysql(用户基本信息)，performance_schema(数据库优化信息)这三个内置数据库很重要，不能随便修改或删除。
数据库入门语句
-
查看当前服务器下面有哪些库(database)
show databases; -
创建数据库
create database DBName[字符集声明,整理集声明]; -
删除数据库
drop database test2; -
修改数据库
只能修改数据库的字符集和整理集，不能修改数据库的名字 -
选择数据库
use DBName; -
查看库下的表
show tables; -
删除一张表
drop table tableName; -
修改表名
rename table oldName to newName; -
查看表结构
desc tableName; -
最简单的建表语句
create table 表名( 列1名称 列1类型, 列2名称 列2类型 ); -
为留言本建一张表
create table msg( id int, title varchar(60), name varchar(10), content varchar(1000) ); -
解决字符集的问题
默认的建表一般用utf8，我们在windows下窗口是gbk，因此需要声明字符集。
所谓建表就是声明列的过程，数据是以文件的形式放在硬盘或内存中的，不同的列类型占的空间不一样，选列的原则是够用又不浪费。
详解列类型
-
数值类型
a. 整数型：tinyint smallint mediumint int bigint
类型 字节 占位 无符号 有符号 tinyint 1 8 0 ~ 2^8-1 -2^7 ~2^7-1 smallint 2 16 0 ~ 2^16-1 -2^15 ~ 2^15 -1 mediumint 3 24 0 ~ 2^24-1 -2^23 ~ 2^23 -1 int 4 32 0 ~ 2^32-1 -2^31 ~ 2^31 -1 默认的整数类型是有符号的，如果存储无符号的数据应显示声明
tinyint(M) unsigned zerofill的含义
M : 代表宽度，对存储范围无影响，在zerofill时才有意义。
unsigned：无符号类型，对存储范围有影响，即数值非负。
zerofill：零填充(如果某列是zerofill默认就是unsigned)
M和zerofill标记的字段是仅一种显示效果，和数据的存储范围无关。
列可以声明默认值，而且推荐声明默认值
not null default 0b. 小数型：
浮点型：float(M,D)(M代表总位数，D代表小数位)
定点型：decimal(M,D)
以float(4,2)为例，有符号时表示范围-99.99 ~ 99.99，无符号时表示范围0 ~ 99.99
decimal和float类型占有固定的字节数，为4字节或8字节。M<=23时占4个字节，M>23时占8个字节decimal类型比float类型更加精确。
-
字符类型
定长类型：char(M)，M代表宽度，0<=M<=255，即可容纳的字符数。
变长类型：varchar(M)，M代表宽度，0<=M<=65535，即可容纳的字节数。(utf-8字符可容纳22000个左右)
文本类型：text，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用char和varchar来代替，text不用加默认值，加了也没用。
char类型和varchar类型的比较
char定长：M个字符，如果存的小于M个字符，实占M个字符
varchar变长：M个字符，如果存的小于M个字符，设为N，N<=M 实占N+1-2个字节
类型 宽度 可存字符 实存字符 利用率 char(M) M M i i/M <= 100% varchar(M) M M i字符+(1-2)字节 i/(i+1~2) < 100% char类型利用率可能达到100%，但是varchar类型一定达不到100%。
char类型的速度相比varchar类型会稍快一些，如果是存姓名等定长的字段，虽然空间有浪费，但是考虑速度使用char类型会更好一些。
char类型如果不够M个字符，内部用空格补齐，取出来的时候再把后面的空格去掉，所以如果内容最后有空格再次取出时会被清除。
考虑空间利用率和存储速度，像用户名，四字成语等长度比较固定的字段应该采用char类型存储，而个人简介等不是特别长的文本内容应该采用varchar类型来存储。
-
日期时间类型
a. 年类型：year，一个字节表示，范围 1901 ~ 2155 [ 0000标识错误选择 ]
如果输入两位，"00 ~ 69"表示2000 ~ 2069，"70 ~ 99"表示1970 ~ 1999
如果记得麻烦，输入的时候输入四位
b. 日期类型：date，典型格式"Y-m-d" 范围 1000-01-01 ~ 9999-12-31
c. 时间类型：time，典型格式"H:i:s" 范围 -838:59:59 ~ 838:59:59
d. 日期时间类型：datetime，典型格式"Y-m-d H:i:s" 范围 "1000-01-01 ~ 9999-12-31 23:59:59"
注意事项：在开发中，很少使用时间类型来表示一个需要精确到秒的列，原因是虽然日期时间类型能精确到秒而且方便查看，但是不方便计算。解决办法是用时间戳来表示，即使用int类型来存储时间戳。
时间戳：是1970-01-01 00:00:00到当前的秒数，一般存注册时间，商品发布时间等，并不是使用datetime而是使用时间戳，因为datetime虽然直观，但计算不够方便，而用int类型存储时间戳，方便计算，对于显示来说，也可以方便格式化。
对于类似于性别这样的字段，可以声明为tinyint，同时约定0和1分别代表什么，也可以使用枚举类型enum('男',‘女’)，但是这种设计不符合关系型数据库的列的原子性设计原则，如果某一列的内容仅具有几种值，那么应该单独设计一张表来保存这几种值。
插入数据时除了数值类型其余类型数据应该添加引号
简单的建表练习
create table php313(
id int unsigned primary key auto_increment,
name char(3) not null default '',
age tinyint unsigned not null default 0,
email varchar(30) not null default '',
tel char(11) not null default '',
salary decimal(7,2) not null default 0.0,
riqi date not null default "2012-03-13"
)charset utf8;
增删改查操作
-
insert语法
insert into 表名(列1,...列n) values(值1,...值n); insert into 表名 values(值1,...值n)； insert into 表名(列1,...列n) values(值1,...值n),(值1,...值n),(值1,...,值n); -
update语法
update 表名 set 列1=新值1,列2=新值2 where expr; -
delete语法
delete from 表名 where expr;
对where条件的理解：对满足expr的行执行相应动作，如果不加where条件，则默认对所有行执行动作。
-
select语法
查询的五种子句：where(条件查询)，group by(分组)，having(筛选)，order by(排序)，limit(限制结果条数)
-
where expression
用法：如果expression为真就将该行取出。
运用场合：各种条件查询场合，如按学号查询学生，按价格查询商品，按发布时间查询新闻等。
比较运算符：<(小于) >(大于) =(等于) <=(小于等于) >=(大于等于) !=或<>(不等于) in 在某集合内 between and 在某范围内。
逻辑运算符：NOT或!(逻辑非) OR或||(逻辑或) AND或&&(逻辑与)。
模糊查询
%：通配任意字符 _ ：通配单个字符
例子：查找"诺基亚"开头的所有商品
select goods_id,cat_id,goods_name from goods where goods_name like '诺基亚%'; select goods_id,cat_id,goods_name from goods where goods_name like '诺基亚__';- group by col1,col2,...colN
用法：把行按字段分组
运用场合：常见于统计场合，如按栏目计算帖子数，统计每个人的平均成绩等。
五个统计函数(也叫聚合函数)：max()求最大，min()求最小，sum()求总和，avg()求平均，count()求总行数。
select count(*) from goods; select cat_id,max(shop_price) from goods group by cat_id;五个聚合函数必须作用于select和from之间的字段上，只有单独使用在某一列或者配合group by使用时才有意义，否则查询出的列是不相匹配的。
要把列名当成变量来看待，可以给列，表和计算结果取别名。3.having expr
用法：对查询结果的筛选
where：针对原始表起作用，作用的时间在结果之前。
having：对查询结果其作用，作用的时间在结果之后。
例子：一张学生成绩信息表如下：
姓名 科目 成绩 张三 数学 90 张三 语文 50 张三 地理 40 李四 语文 55 李四 政治 45 王五 政治 30 查询挂科两门及两门以上的同学的平均成绩
思路：先计算所有同学的平均成绩，然后新建一个字段作为是否挂科标记，统计这个标记的和，使用条件过滤。
select name,avg(grade) as average,sum(grade < 60) as notpass from grade group by name having notpass > 2;4.order by
用法：按一个或多个字段对查询结果进行排序。
知识点在本项目案例的运用：对栏目的商品按价格由高到低或由低到高排序
知识点的运用场合描述：各种排序场合，如热点新闻，发帖状元等。
根据字段可以升序排列也可以降序排列，默认是升序排列。
"字段名 desc"来声明按降序排列，"字段名 asc"来声明按升序排列。
使用order by进行排序时，只通过一个字段有可能排不出结果，这时可以多字段排序。
order by 列1 [desc/asc],列2 [desc/asc]...5.limit [offset, ] n
用法：limit在语句的最后，起到限制条目的作用。
offset：偏移量 n：取出的条数
limit 2,3 //取出第三条到第五条offset如果不写，相当于从头开始取
针对sql语句良好的理解模型：
where表达式：将表达式放在行中，看表达式是否为真。
列：理解为变量，可以运算。
取出结果：理解为一张临时表。
子查询
where型子查询：是指把内层查询的结果当作外层查询的比较条件
#查询最新的商品 select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods ); #查询每个栏目下的最新商品 select goods_id,goods_name from goods where goods_id in (select max(godos_id) from goods group by cat_id);from型子查询：是指把内层查询结果当成临时表，供外sql再次查询。
#查询每个栏目下最新的商品 select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id; #查询挂科两门以上的平均分 select name,avg(grade) from grade where name in (select name from(select name,count(*) as notpass from grade where grade < 60 group by name having notpass >=2) as tmp) group by name;where型子查询和from型子查询的区别：当遇到两条相同记录时，from型子查询可能会只查询到其中一条记录，而where型子查询会查询出所有记录。
注意from型子查询的"as 临时表"不能丢掉。
exists型子查询：是指把外层的查询结果拿到内层，看内层的查询结果是否成立
#查询有商品的栏目 select cat_id,cat_name from category where exists(select * from goods where goods.cat_id=category.cat_id);联合查询
union的用法：合并查询结果。
作用：把两次或多次的查询结果合并起来。
要求：两次查询结果的列数一致，可以来自于一张或多张表。
推荐：查询的每一列对应的列类型一致
#查询大于5000或者小于2000元的商品 select goods_id,goods_name,shop_price from goods where shop_price > 5000 union select goods_id,goods_name,shop_price from goods where shop_price < 20; #查询反馈表和评论表的内容并合并起来 select user_name,user_email,user_content from feedback where status=1 union select user_name,email,content from comment;多次sql语句取出的列名可以不一致，此时以第一个sql取出的列名为准。如果不同的语句中取出的行有完全相同的(每个列的值都相同)，那么相同的行将会合并(去重复)。如果不去重复，可以使用union all来指定。
#查询所有的合并记录，而不去除重复数据 select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;如果子句中有order by或limit，应该用括号将子句包起来，推荐放到所有子句之后，即对最终合并的结果发挥作用。
(select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc) union (select goods_id,goods_name,shop_price from goods where cat_id=5 order by shop_price desc) order by shop_price desc;在子句中order by配合limit使用时才有意义，如果order by不配合limit使用，会被语法分析器分析时去除。
(select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price desc limit 3) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc limit 2);连接查询
连接查询步骤：先将两张表连接起来，再使用条件过滤一些数据，最后像查询一张表那样查询联合表，注意字段如果有重复应该显示声明是哪一张表的字段。
select goods_id,goods.cat_id,cat_name,goods_name,shop_price from goods left join category on goods.cat_id=category.cat_id;左连接 (left join)
select 列1,列2,...列n from tableA left join tableB on tableA.列=tableB.列 [此处表连接成一张大表，完全当成普通表来看待] where group by having等查询子句照常写右连接 (right join)
select 列1,列2,...列n from tableA right join tableB on tableA.列=tableB.列 [此处表连接成一张大表，完全当成普通表来看待] where group by having等查询子句照常写内连接 (inner join)
select 列1,列2,...列n from tableA inner join tableB on tableA.列=tableB.列 [此处表连接成一张大表，完全当成普通表来看待] where group by having等查询子句照常写左右连接以左表为准，去右表找匹配数据，找不到匹配用NULL补齐。
左连接与右连接的关系：左右连接可以相互转化，A left join B <=> B right join A。推荐把右连接转化成左连接来使用，并推荐使用左连接代替右连接，这样做兼容性更好一点。
内连接：查询左右表都有的数据，即不要左右连接中的NULL那一部分，是左右连接的交集。
如果想要查出左右连接的并集，可以用union来达到目的，目前mysql不支持外连接outer join。
-
练习题
1.取出goods表，category表，brand表连接查询得到以下字段
goods_id,goods_name,cat_id,cat_name,brand_id,brand_name
select goods_id,goods_name,category.cat_id,category.cat_name,brand.brand_id,brand.brand_name
from goods left join category on goods.cat_id=category.cat_id
left join brand on goods.brand_id=brand.brand_id;
2.面试题
Match赛程表
| 字段名称 | 字段类型 | 描述 |
|---|---|---|
| matchID | int | 主键 |
| hostTeamID | int | 主队的ID |
| guestTeamID | int | 客队的ID |
| matchResult | varchar(20) | 比赛结果，如(2,0) |
| matchTime | date | 比赛开始时间 |
Team参赛队伍表
| 字段名称 | 字段类型 | 描述 |
|---|---|---|
| teamID | int | 主键 |
| teamName | int | 队伍名称 |
Match的hostTeamID与guestTeamID都与Team中的teamID关联，查出2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出：拜仁 2:0 不来梅
select t1.tname as hname,mres,t2.tname as gname,matime from m
left join t as t1 on m.hid = t1.tid
left join t as t2
on m.gid = t2.tid
where matime between '2006-06-01' and '2006-07-01';
列的增删改
-
增加列
alter table 表名 add 列声明;增加的列默认放在表的最后一列，可以用after来声明新增的列放在哪一列的后面
alter table 表名 add 列声明 after 列名;如果新增的列放在最前面，则使用first关键字
alter table 表名 add 列声明 first; -
修改列
alter table 表名 change 列名 列声明; -
删除列
alter table 表名 drop 列名;
视图 view
-
视图的定义
视图是由查询结果形成的一张虚拟表，没有真实的数据存在，只是一种查询产生的关系。
-
视图的创建
create view 视图名 select 语句; -
视图的修改
alter table 视图名 as select 语句;
-
视图的删除
drop view 视图名; -
视图的意义
a.可以达到简化查询的目的，对于一个多步复杂查询，可以将中间结果保存为视图，再针对视图进行查询。
b.可以进行权限控制，把表的权限封闭，但是开放相应的视图权限，视图里只开发部分数据。
c.大数据分表时可以用到，比如表的行数超过200万时，就会变慢，可以把一张表的数据拆成四张表来存放，查询时可以用视图将四张表合并为一张来查询。
-
视图与表的关系
视图是表的查询结果，表的数据修改会影响视图的结果，视图的增删改也可以影响到表。但是视图并不总是能够增删改的，只有视图数据与表的数据一一对应时，视图的数据是可以修改的。对于视图的insert操作还应注意视图必须包含所有表中没有默认值的列。
-
视图的algorithm
merge：当引用视图时，引用视图的语句与定义视图的语句合并。
temptable：当引用视图时，根据视图的创建语句建立一张临时表。
undefined：未定义，系统倾向于merge模式。
merge模式与temptable模式的区别：
merge：意味着视图只是一个语句规则，当查询视图时,把查询视图的语句(比如where子句)与创建时的语句(比如where子句)等合并，分析形成一条select语句。
#创建视图的语句 create view g2 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc; #查询视图的语句 select * from g2 group by cat_id; #最终执行的语句 select goods_id,cat_id,goods_name,shop_price from goods group by cat_id order by cat_id asc,shop_price desc;temptable：根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表查询数据。通过显示指定temptable模式可以达到子查询的效果。
create algorithm=temptable view g2 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc; #最终执行两句话，首先取数据放在临时表，然后查询临时表。
字符集与校对集
1.字符集：MySQL的字符集设置非常灵活，可以设置以下几个层面：服务器默认字符集，数据库默认字符集，表默认字符集，列默认字符集。如果某一级别没有指定字符集，则继承上一级。
以表声明为utf8为例，存储在表中的数据是utf8字符集。
[图解MySQL字符设置]
1.告诉服务器客户端的编码 character_set_client
2.告诉转换器转换的编码 character_set_connection
3.查询结果使用什么编码 character_set_results
如果以上三者为同一字符集N可以使用 set names N;
产生乱码的情形：
client声明与事实不符或result声明与客户端页面不符。
丢失数据的情形：
character_set_connection和character_set_server的字符集比character_set_client小
2.校对集：校对集就是排序规则，一种字符集可以有一个或多个排序规则，utf8一般使用默认的utf8_general_ci规则，也可以使用二进制规则utf8_bin
show character set; //查看字符集
show collation; //查看校对集
show colation like 'utf8%'; //查看utf8字符集对应的校对集
触发器 trigger
-
触发器的作用
监视某种动作并触发某种动作，触发器能监视增删改三种操作并出发增删改三种操作。
-
触发器的应用场景
a. 当向一张表中添加或删除记录时，需要在相关表中进行同步操作。比如，当一个订单产生时，订单的所购的商品的库存量应改相应减少。
b. 当表上某列数据的值与其他表中的数据有联系时。比如当某客户进行欠款消费，可以在生成订单时通过设计触发器判断客户的累计欠款是否超过了最大限度。
c. 当需要对某张表进行跟踪时。比如，当有新订单产生时，需要及时通知相关人员进行处理，此时可以在订单表上设计添加触发器加以实现。
-
触发器的四个要素
a. 监视地点：table
b. 监视事件：insert/update/delete
c. 触发时间：after/before
d. 触发事件：insert/update/delete
-
触发器的使用语法
创建触发器的语法
create trigger triggerName after/before insert/update/delete on tableName for each row //这句话在MySQL数据库中是固定的 begin sql语句; //一句或多句insert/update/delete范围内 end$删除触发器的语法
drop trigger triggerName; -
触发器引用行变量的值
a. 对于insert而言，新增的行用new来表示，行中每一列用new.列名来表示。
b. 对于delete而言，删除的行用old来表示，行中每一列用old.列名来表示。
c. 对于update而言，原来的行用old来表示，行中每一列的值用old.列名来表示；新增的行用new来表示，行中每一列的值用new.列名来表示。
