安装与配置
- 安装xampp（为了使用mysql数据库）：点击MySQL的start以启动mysql
- 配置环境变量（将安装目录下的bin目录加入path）
- dos窗口输入mysql -u root -p 回车，表示以root用户运行，密码为空
- 输入\s;查看使用的字符编码
- 现在是latinl，然后我们修改/bin目录下的my.ini，搜索utf8（注意这里没有-），将其改为如下所示
使用
注意：sql语句不区分大小写
在windows的dos窗口下：
- show databases：查看使用的数据库（分号表示一条语句）
- creat database db_student_sms; 创建一个数据库，名字叫db_student_sms
- show create database db_student_sms;查看编码格式
- use db_student_sms;切换到我们要使用的数据库，然后可以发现前面方括号内的None变成了我们的数据库名
- 查看数据库中的表（数据库是由表组成的）：show tables;当然，我们目前的表是空的，所以显示empty set（意为空集合）
可以使用desc+表名，查看该表的信息
-
dos中退出mysql的方法
- quit
- exit
- ctrl+c
navicat对数据库的简单操作
- 首先要已经在xampp中启动mysql
- 打开navicat，文件/新建连接/【连接名随便取，主机名、端口、用户名皆默认，密码为空】/连接测试
- 如果显示连接成功，就表明mysql已经可用，点击确定即可
- 回到主界面后，在左边已经建立的连接名上右击、新建数据库，输入数据库的名称，字符集我们选择utf8（倒数第二个就可以了），排序规则选第一个，点击确定后我们就可以看到左边的连接下多了一项我们刚才建立的数据库
新建数据库
字符编码选择
排序规则选择
我们新建的数据库
- 双击我们新建的数据库，右击“表”、新建表，然后界面如下
- 新建字段
7. 保存该表
数据类型与约束
当我们定义一个表的时候，有两个东西需要我们去考虑。
- 是数据的类型
- 是其他的约束
通过类型和约束可以保证数据的完整性
数据类型
常用的数据类型
- 整数：int、tinyint（通常用0来表示false，非0表示true）
- 小数：decimal、double。decimal表示浮点型，decimal(5,2)表示共存5位，小数占2位
- 字符串：char（字符串长度不可变）、varchar（字符串长度可变，通常使用这个）。
- 日期时间：date、time、datetime
通常，数据库中通常不存储媒体文件（视频、音频、图片等），媒体文件常存储在物理磁盘上，数据库中存储的是他们的路径
约束
- 主键：priamry key：物理存储的顺序
- 非空：not null：此字段不允许填写空值
- 唯一：unique：此字段的值不允许重复
- 默认：default：当不填写此值时会使用默认值
- 外键：foreign key：如果一张表中的某个非主键字段是另一张表中的主键，那么就把该字段称为外键。对关系字符进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存储则填写成功，是否失败并抛出异常
- 说明：虽然外键约束可以保证数据的有效性，但是在进行数据的CRUD（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用。那么数据的有效性如何保证呢？答：可以在逻辑层进行控制
用sql语句操纵数据库
点击navicat中的查询、新建查询，即可在navicat中使用sql语句操纵数据库（当然，也可以新建一个后缀为sql的文件，然后把语句写在文件中，直接把文件拿来执行）
注意：
- sql中用
--表示注释，eg：--这里是注释； - 判断是否相等用"="号，而不是"=="
- sql中null与任何值（包括自己）做相等比较，都是false，为此，sql使用了is nulll 和 is not null来对null进行判断
数据库及数据表的相关语句
create databases + 数据库名：创建数据库
查看创建数据库的语句：show create database，可以查看数据库的编码
使用制定编码创建数据库：create database + 数据库名 + charset=utf8;
删除数据库：drop database + 数据库名; 注意：如果要删除的数据库名称比较特殊，比如包含
-号，用``将其包起来查看当前所有的数据库：show databases;
使用数据库：use + 数据库名;
查看当前数据库中所有的表：show tables;
创建数据表：create table + 数据表名字 （字段 类型 约束[，字段 类型 约束]）。eg：
create table xxxx(id int, name varchar(30))，表示创建一个叫做xxxx的数据表，其中有一个id字段，该字段为int类型，还有个name字段，类型为varchar，且长度最长为30字符desc+表的名字：查看表的结构
新建数据表时给表加约束：
create table xxxx(id int unsigned primary key not full auto_increment, name varchar(30))，表明我们使用id作为主键，int类型，只能为正，且其不能为空，自增注意：1、一条sql语句可以换行写；2、创建表时，如果含有多个字段，最后一个字段后面不能加逗号；3、约束的顺序没有要求
数据的插入：insert into 数据表的名字 values(字段1的数据，字段2的数据，....)
查看表中的所有数据：select * from + 表的名字;
-
数据表的增删查改：
- 修改表-修改表的名称：rename table 旧的表名 to 新的表名
- 修改表-添加字段：alter table + 表的名字 add 列名 类型
- 修改表-修改约束：alter table 表的名字 modify 列名 类型及约束
- 修改表-修改列的名字（及约束和类型）：alter table 表名change 原名 新名 类型及约束
- 修改表-删除列：alter table 表的名字 drop 列的名字
- ps：数据库尽量少删多加
- 删除表：drop table 表名
- 查看表的创建语句：show ceate table 表的名字
- 查看当前使用数据库的名字：select database();
数据表中数据的增删查改
基本查询
- 给一个表用insert into 表的名字 values(要插入的数据) 插入数据时，如果不专门指定，默认全部插入，即要插入的数据的项数，必须和字段数对应，类型也必须对应。要插入的数据中，除非特别字段，否则也可以写null和default
- 只插入部分字段：insert into 表的名字 (字段1的名字，字段2的名字) values(字段1的值， 字段2的值)，注意，此种方法，不写的字段必须是允许为空的
- 同时插入多个数据项：insert into 表的名字 (字段1的名字，字段2的名字...) values(字段1的值，字段2的值...),(字段1的值，字段2的值)；使用此法，在插入全部字段时（即不用指定字段，一次性插入所有字段的数据）也可以，eg：insert into 表的名字 values(字段1的数据，字段2的数据，字段3的数据...)
- 数据的修改：update 表的名字 set 列1的名字=要设置的列1的值，列2的名字=要设置的列2的值
- 条件语句：where 条件，eg：
update students set gender = 1 where id=3;将id为3的记录中的性别设置为1（如果不写where语句，将把gender字段的所有值都修改为1） - *代表所有的东西，eg：
select * from students，表示查看students表中的所有数据 - 查询指定字段：select 字段1，字段2 from 表的名字 （也可以加判断语句）;
- 使用name为列或者表指定别名(as 可省)以增强可读性：eg，
select name as "姓名", gender as “性别” from students; - 物理删除某些记录：delete from 表名 where 条件;
- 物理删除整个数据表：delete from 表名;
- 逻辑删除：使用一个字段来表示这条信息已经不再使用了
- 给表起别名：
select t1.name, t1.age from students as t1;表示查询students表中的name字段和age字段。注意一旦给表起了别名，前面就不能再使用原来的名字了 - 对查询的结果去重（即：如果查询出来的结果有相同的项，只显示一次）：
select distinct gender from students,查询students表中的所有性别（如果students中男女都有的话，查询结果只会显示两个：男 女）
条件查询
与where结合使用，eg：xxxx where age > 18;
比较运算符
- 比较运算符：>、<、=、>=、<=、!=或者<>
逻辑运算符
优先级：not > and > all
- and，eg：
select * from students where age>18 and age <28 - or 和and的用法一样
- not：
select * from students where not (age>18 and gender =2);表示年龄不在18以上，且性别也不为2的
模糊查询
注意：模糊查询的效率较低
-
like的使用：查询姓名中以“小”开头的名字：
select name from students where name like "小%";- %：替换一个或多个
- _：替换一个
-
rlike：正则表达式
- eg：查询姓名以“周”开头的，以“伦”结尾的,
select name from students where name rlike "^周.*伦$";
- eg：查询姓名以“周”开头的，以“伦”结尾的,
范围查询
对于非连续：
- in：eg：查询年龄为18或20或23的。
select name,age from students where age in (18, 20, 23); - not in
对于连续：
- between...and...：eg：查询id在2-7的：
select name,id from hero where id between 2 and 7; - not between...and...：注意，not between是一个整体，而不是说对后面的between...and取反，即：不能对between...and加括号，比如以下两句，效果一样，但是语法不同
select * from students where age not between 18 and 22;
select * from students where not age between 18 and 22;
排序
-
order by 字段，加在语句的最后即可，eg：
select name,id,age from hero where id BETWEEN 2 and 6 order by age desc;按年龄降序排列所查找的数据- asc：升序，默认
- dex：降序
order by 多个字段：如果第一个字段相同，按照第二个字段，依次类推。格式：
查询语句 order by 第一个字段, 第二个字段 desc，按照第一个字段的升序排列，如果第一个字段相同，按照第二个字段的降序排列
聚合函数
什么叫函数，就是带括号的那种，只能得出一个结论
-
计算总数：sum
-
select count(*) as 男性人数 from students where gender=1：统计有多少gender=1的人，为什么是对*加函数？因为*是查询出来的数据，而那正是我们所要统计的，此处，我们还对其起了别名
-
最大值：max，注意：对于max、min和avg，必须要跟统计的字段名或表达式为参数，不能以*为参数，eg：
select avg(age) 平均年龄 from xxxx最小值：min
平均值：avg
四舍五入：round(avg(age), 2)：求年龄的平均值，并保留两位小数
分组
主要是结合聚合函数使用
- group by：eg：
select xxx from students group by yyy，按照yyy字段分组，注意，此时，xxx必须是能够标记每个组的东西，比如：如果是按照性别分组，xxx就不能为姓名。分组的特点：先将原来的数据分组，然后再根据前面的条件中组中取数据，实例：select is_delete 删除标识,count(*) 数量 from hero group by is_delete;，按照是否被逻辑删除分组，并分别统计其数量，即：此时的聚合函数是对分组里面的计算个数，而不是原表 - group_concat：案例;
select is_delete 删除标识,count(*) 数量,GROUP_CONCAT(name, "-", age) 年龄 from hero group by is_delete;按照是否被逻辑删除分组，计算每个组的人数，并列出每个组里面的姓名和年龄，并将姓名和年龄用-拼接。ps：group-concat基本上是写什么有什么 - having：对分组进行过滤，eg：
select gender, group_concat(name) from students group by gender having count(*) > 2：查询每种性别中的人数多于2个的信息
ps：where和having的区别：
- where是对原始表中的数据进行判断，而having是对查出来的结果进行条件判断`
分页
limit (start) count
注意：如果使用limit语句，它必须放在语句的最后
- 用法1：限制查询出来的数据个数：
xxxx limit 2;：对用xxxx进行查询的结果分页显示，每页只显示2个 - 用法2：
xxxx limit 2,5;：对用xxxx进行查询的结果分页显示，从结果中序号为2的开始，显示5个，注意：序号的0表示第一个
连接查询
连接查询主要想表达的意思就是多个表的关联查询
内连接查询：inner join( ... on)：查询的结果是两个表匹配到的数据。用法：select * from students inner join score，但是inner join主要是配合on才有意义
- 对于inner join，是取两个表中所有记录的交叉匹配，要两个表中都有（on中的判断内容）的才显示
- inner join常和on配合使用，此时是用on进行筛选，比如：
SELECT * from students INNER JOIN score on hero.id = score.id;，将两个表中的数据交叉匹配，但是只显示id相同的（注意：此时id有两列） - 用inner join时，也常用as（可省）给表取别名，然后用
表名.字段名取出某个字段。可用此法对于某个表只取出某个特定字段以消除2中所说的id重复显示的问题。如：SELECT h.*, s.成绩 from hero as h INNER JOIN score as s on h.id = s.id;
右连接查询：right join：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充，一般不用，在左连接中对换两个表的名字即可达到相同效果
左连接查询：left join：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
自关联
一个表中某个字段的值依赖于这个表中另一个字段的值（通常将很多数据放到一张表中）
通常，在类似行政级别中会常用到自关联
自关联的查询技巧：对于自关联的数据，我们在查询时，可以将一张表取不同的别名以“当作”不同的表来使用
子查询
在一个select语句中又嵌套了另外一个select语句，将来在使用的时候，会先执行子语句，然后将子语句的结论当作父语句的条件去进行查询
示例：select name,price from goods where price > (select avg(price) from goods);，选择出价格大于平均价格的商品
经典案例
对于有如下内容的goods表格，查找出每一类中最贵的产品的详细信息（注意：cate_name是产品类别的名字）
insert into goods values(0, 'r510vc 15.6英寸笔记本', '笔记本', '华硕', '3309', default, default);
insert into goods values(0, 'y400n 14.0英寸笔记本', '笔记本', '联想', '4999', default, default);
insert into goods values(0, 'g150th 15.6英寸笔记本', '游戏本', '雷神', '8499', default, default);
insert into goods values(0, 'x550cc 15.6英寸笔记本', '笔记本', '华硕', '2799', default, default);
insert into goods values(0, 'x240 超级本', '超级本', '联想', '4880', default, default);
insert into goods values(0, 'u330p 13.3英寸超级本', '超级本', '联想', '4299', default, default);
insert into goods values(0, 'svp1326scb 触控超级本', '超级本', '索尼', '7999', default, default);
insert into goods values(0, 'ipad mini 7.9英寸平板电脑', '平板电脑', '苹果', '1998', default, default);
insert into goods values(0, 'ipad air 9.7英寸平板电脑', '平板电脑', '苹果', '3388', default, default);
insert into goods values(0, 'ipad mini 配备 retina 显示屏', '平板电脑', '苹果', '2788', default, default);
insert into goods values(0, 'ideacentre c340 20英寸一体电脑', '台式机', '联想', '3499', default, default);
insert into goods values(0, 'vostro 3800-r1206 台式电脑', '台式机', '戴尔', '2899', default, default);
insert into goods values(0, 'imac me086ch/a 21.5 英寸一体电脑', '台式机', '苹果', '9188', default, default);
insert into goods values(0, 'at7-7414lp 台式电脑 linux', '台式机', '宏基', '3699', default, default);
insert into goods values(0, 'z220sff f4f06pa工作站', '服务器/工作站', '惠普', '4288', default, default);
insert into goods values(0, 'poweredge ii服务器', '服务器/工作站', '戴尔', '5388', default, default);
insert into goods values(0, 'mac pro专业级台式电脑', '服务器/工作站', '苹果', '28888', default, default);
insert into goods values(0, 'hmz-t3w 头戴显示设备', '笔记本电脑配件', '索尼', '6999', default, default);
insert into goods values(0, '商务双肩包', '笔记本配件', '索尼', '99', default, default);
insert into goods values(0, 'x3250 m4机架式服务器', '服务器/工作站', 'ibnm', '6888', default, default);
insert into goods values(0, '商务双肩包', '笔记本配件', '索尼', '99', default, default);
查询语句：
select * from goods inner join
(select cate_name, max(price) as max_price from goods group by cate_name) as t2 on
(goods.cate_name = t2.cate_name and(goods.price = t2.max_price));
结果如下：
数据库的设计
三范式
范式：经过研究和对使用中问题的总结，对于设计数据提出了一些规范，这些规范被称为范式（Normal Form）
-
第一范式（1NF）
强调的是列的原子性，即列不能够再分成其他几列
-
第二范式（2NF）
首先是1NF，另外包含两部分内容，一是表必须有一个主键；二是表没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分
-
第三范式（3NF）
首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖，即不能存在：非主键列A依赖于非主键列B，非主键列B依赖与主键的情况
关系型数据库：“一言不合就拆表”——解耦
E-R模型
- E表示entity（实体），设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表
- R表示relationship（关系），关系描述两个实体之间的对应规则，关系的类型包括一对一、一对多（多对一时，需要在多的表中新建一个字段）、多对多（多对多时，必须新建一个表，这个表称为聚合表）
- 关系也是一种数据，需要通过一个字段存储在表中
- 实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值
表的拆分
ps：真正的数据库是先设计，再有数据，而不是有了数据，再来拆分
将上面的googds表拆分为多个表
创建商品分类表
利用一张表的数据创建另外一张表
create table if not exists goods_cates(
-- 如果不存在goods_cates，则创建
id int unsigned primary key auto_increment,
name varchar(40) not null
);
查询goods表中商品分类
select cate_name from goods group by cate_name;
将分组结果写入到goods_cates表中
用group by 或者distinct去重
name后不能写values
-- 将后面查询的数据直接插入name字段
insert into goods_cates (name) select cate_name from goods group by cate_name;
同步表数据
通过goods_cates数据来更新goods表
update goods as g inner join goods_cates as c on g.cate_name set g.cate_name = c.id;
创建商品品牌表
通过create...select来创建数据表并且同时写入记录，一步到位（使用另一张表的数据创建表）
-- select brand_name from goods group by brand_name;
-- 在创建数据表的时候一起插入数据
-- 注意：需要对brand_name 用as起别名，否则c_name字段就没有值
create table goods_brands(
id int unsigned primary key auto_increment,
c_name varchar(40) not null) select brand_name as c_name from goods group by brand_name;
-- 最后一行中的两个c_name是必须一样
同步数据
通过goods_brands数据表来更新goods数据表
update goods g inner join goods_brands b on g.brand_name = b.c_name set g.brand_name = b.id;
修改表结构
查看goods中的cate_name和brand_name，会发现cate_name和brand_name对应的类型为varchar，但是存储的却是数字
desc goods
通过alter table语句来修改表的结构（可以同时操作多个字段）
alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;
外键
分别在goods_cates和goods_brands表中插入记录
insert into goods_cates(c_name) values('路由器'),('交换机'),('网卡');
insert into goods_brands(c_name) values('海尔'),('清华同方'),('神州');
在goods数据表中写入任意记录
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn黑白激光打印机', 12, 4, '1849');
查询所有商品的详细信息（通过内连接）
select g.id, g.name, c.c_name, b.c_name,g.price from goods as g
inner join goods_cates as c on g.cate_id = c.id
inner join goods_brands as b on g.brand_id = b.id;
发现在查询结果中出现了cate_id为12的项，但是在goods_cates表中并没有该项。这是因为此时两个表还没有关联，如何让他们有关联？——将goods.cate_id设为外键
ps：只有innodb数据库引擎支持外键约束
-- 给brand_id添加外键成功
alter table goods add foreign key (brand_id) references goods_brands(id);
-- 给brand_id添加外键约束，失败，因为已经添加了一个不存在的cate_id
alter table goods add foreign key (cate_id) references goods_cates(id);
外键的作用：保证数据的合法性
如何在创建表的时候就设置外键
create table goods(
...
foreign key(cate_id) references goods_cates(id),
...
)
但是在实际中是很少用到外键的，因为会降低效率
如何取消外键
-- 首先要获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
