如鹏网.Net基础2 第六章：MYSQL
1、能够创建数据库表，创建字段，设置合适的字段数据类型。
2、什么是主键，主键一般用什么类型？什么是自动增长？
3、select、insert、update、delete；
4、where条件；
5、聚合函数：Max、Min、AVG、Count
6、order by；like；group by
7、null是什么？null=null吗？null!=null吗？如何判断是null？
8、如何获取第3行到第9行的数据？
9、left join联合查询；
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 0 节： 为什么这章只有一节？
SQLserver不是唯一
.net+MySQL .net+Oracle
成本和架构角度：
1.正版成本。
2.SQLServer不适合建立集群；MySQL Oracle很方便；
3.SQLServer（大）并发性能比MySQL Oracle差。
MySQL开源免费，而且比SQLServer更适合大型网站架构
如鹏网：.net+MySQL
学习角度：（安装简单）
SQLServer的兼容性很麻烦（特别在win8下面）;
MySQL绿色版；
一通百通：
Mysql,SQLServer学习起来区别很小，语法基本一模一样；管理工具大同小异，ADO.net连接和
MySQL和连接SQLServer敢几乎一样；
后面会安排一些SQLServer（安装配置，ado.net 连接SQLServer）项目
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 1 节 数据库简介
有用的数据要长期的持久化保存信息
信息写入到磁盘文件中缺点：
1.数据类型不同，结构不同
2.不利于多人的共享数据
数据库（Database）:是保存数据的仓库中，可以方便的把数据放进去，并且把数据根据各种需要取出来。
数据库管理系统（Database Management System,DBMS）:是对数据库进行管理（增删改查等）的软件。
常用的DBMS有：MySQL,Oracle,DB2,MSSQLServer等。
MYSQL是免费、开源的，因此应用最广泛。
有的场所下的“数据库”其实指是DBMS。哪个是：“你们用的是什么数据库？”，“你会用什么数据库？”，“你把数据库删了？！”
SQL：一种语法或（MSSQLServer 或mysql数据库），自己说的要标准一点。
不同的DBMS的用法大同小异，掌握一个了也就基本等于掌握了其他DBMS。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 2 节 库表列和系统的基本架构介绍
数据库：一些有关系的数据的集合
表（Table）：数据格式一致的相关数据的集合
列(Column)/字段(Filed)：每一列数据，一种类型
服务器：数据库服务器（MYSQL），网站服务器
小软件直接连接到数据库，网站浏览器会连接到网站服务器上，然后网站服务器连接到数据库服务器上的。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 3 节 MYSQL服务器的管理工具的安装
1、MYSQL官方版下载地址（不推荐初学者使用）：http://www.mysql.com/downloads/
2、如鹏网版绿色免安装版MYSQL（推荐初学者使用）：http://pan.baidu.com/s/1c0m3xIw 提取码：m9sn
绿色版MYSQL Server的安装：
1）解压到没有中文、空格的文件夹下
2）双击mysqld.exe，如果进程中有了mysqld.exe就ok了
3）上面的方式需要每次重启电脑都要手动运行，特别是如果运行在服务器上，那么不能在登录前就启动。可以注册为系统服务：以管理员身份运行命令行，cd到mysql的bin文件夹，执行“mysqld -install”；卸载服务：mysqld -remove。
如鹏绿色版MYSQL的用户名、密码都是：root
3、NaviCat lite
1）下载地址：http://www.cr173.com/soft/44816.html
2）其他下载地址：百度搜索“NaviCat lite ”
连接服务器：地址：127.0.0.1
用户名，密码
端口 3306
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 4 节 主键（业务主键和逻辑主键)和外键的概念
1.主键是数据行的唯一的标识。不会重复的列才能当主键。一个表可以没有主键，但是会非常难以处理，因此没有特殊理由表都要设定主键。
主键（唯一性）有两种选用策略：业务主键和逻辑主键。
业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等；
逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复（身份证号重复）、不会变化（帐号升位），因此推荐用逻辑主键。
建议：统一用逻辑主键做主键，肯定不会重复
2.表间的关联、外键（ForeignKey）:指向外部表主键的字段
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 5 节 建数据库建立表的方法
1、根节点点右键“新建数据库”，数据库名字取得有意义，比如“study1”、字符集建议用"UTF-8"。复习：读写编码一致就不会乱码。
2、在study1下的“表”节点下点右键“新建表”，“栏位”其实指的就是列(Column)/列，翻译的不好。Id(主键，潜规则的名称，int、不允许为空、点右键“主键”)、Name(varchar，长度为10，不允许为空)、Gender(bit，不允许为空)，保存为“T_Persons”(我习惯的表命名规则T_名词复数)
3、建表常见错误：列名/表名不要用可能的关键字、不要有空格(包括前后，一不小心输错试试)、不要有特殊字符
双击生成的表，手工插入两条数据，注意点“提交”按钮
4、MYSQL的存储引擎有很多，最常用的是InnoDB和MyISAM，MyISAM效率较高，但是不支持事务、外键约束等特性，因此一般建议用InnoDB，新版本默认也是InnoDB。怎么设：建表时“选项”→“引擎”。建好了如何看引擎：表上点右键“对象信息”。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 6 节 MYSQL常用数据类型
文本：
CHAR(*)：最多255个字节的定长字符串，它的长度必须在创建时指定(长度不够，空格来凑合)
VARCHAR(*)：最多255个字节的可变长度字符串，它的长度必须在创建时指定
TEXT：最大长度为64K字符的变长文本
TINYTEXT：最大长度为255字符的变长文本
MEDUIMTEXT：最大长度为16K字符的变长文本
LONGTEXT：最大长度为4GB字符的变长文本
整数(考虑数据取值后选择尽可能小的类型:有符号，无符号)
tinyint：1字节。有符号值：-128 到127；无符号值：0到255
smallint：2字节。有符号值：-32768 到32767；无符号值：0到65535
mediumint：3字节。
int：4字节
bigint：8字节
小数(需要指定长度和小数点，也就是显示宽度和小数位数)：
decimal：精确存储的小数，在内部用字符串存储，适合金额等要求精确的类型。别名：NUMERIC
float：4字节，单精度。会近似存储(*)，效率比decimal高。
double：8字节，双精度。会近似存储(*)，效率比decimal高。
日期时间：
DATE：4字节。范围：1000-01-01——9999-12-31
TIME：3字节。范围：-838:59:59——838:59:59
DATETIME：8字节。范围：1000-01-01 00:00:00——9999-12-31 23:59:59
二进制大数据：
TITYBLOB：最大长度为255字节
BLOB：最大长度为64KB
MEDIUMBLOB：最大长度为16MB
LONGBLOB：最大长度为4GB
在开发网站系统时，将图片，视频等大的数据单独的放在静态文件服务器上的。
通过数据库中只保存文件的路径。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 7 节 SQL简介（Select和Insert）
1、SQL语句中字符串一般用单引号。
2、SQL语句是大小写不敏感的。
3、NavCat中找到执行SQL语句的地方“查询”→“新建查询”，编写SQL后点击【运行】执行SQL语句。
补充：如果只想执行NavCat中编写的一部分代码，而不是执行全部代码，只要选中要执行的代码，点击鼠标右键，选择“运行已经选择的”即可。
4、最简单的SQL：查看一个表的全部数据：select * from T_Persons
5、简单的插入数据的SQL语句：INSERT INTO T_Persons(Id,Name,Age,Gender) VALUES(5,'Jim',20,1)
Insert语句可以省略表名后的列名，但是强烈不推荐
6、如果插入的行中有些字段的值不确定，那么Insert的时候不指定那些列即可。不“允许为空”的列在插入时不能省略
7、自动递增/自增(Auto Increment)：字段自增可以避免并发等问题，不要程序员代码控制自增。用自增字段在Insert的时候不用指定值。修改表结构的方法【设计表】。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 8 节 自动递增列和修改列的数据类型注意的陷阱
自动递增/自增(Auto Increment)：字段自增可以避免并发等问题，不要程序员代码控制自增。用自增字段在Insert的时候不用指定值。修改表结构的方法【设计表】。
修改表结构（设计表）注意
1.把“允许为空”的改成“不允许为空”，需要先给旧数据默认值（可用update语句），或删除之前没用的数据；
2.修改列的数据类型时要注意旧数据能否兼容转换为新类型，修改数据长度时也是如此。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 9 节 Update数据更新
1、更新一个列：UPDATE T_Persons Set Age=30
2、更新多个列： UPDATE T_Persons Set Age=30,Name='tom'
3、表达式：UPDATE T_Persons Set Age=Age+1
4更新一部分数据： UPDATE T_Persons Set Age=30 where Name='tom'，用where语句表示只更新Name是'tom'的行，注意SQL中等于判断用单个=，而不是==。
5、Where中还可以使用复杂的逻辑判断UPDATE T_Persons Set Age=30 where Name='tom' or Age<25，or相当于Java中的||where (Age>20 and Age<30) or(Age=80)
6、Where中可以使用的其他逻辑运算符：or、and、not、<、>、>=、<=、!=（或<>）等
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 10 节 Delete数据删除
删除表中全部数据：DELETE FROM T_Persons。
Delete 也可以带where子句来删除一部分数据：DELETE FROM T_Persons WHERE Age > 20
Delete只是删除数据，表还在。
(*)Drop table T_Persons 删除整个表（表不在了）
is null:判断是空值
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 11 节 数据初始化和Select语法1
1.数据初始化
INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('DEV001','Tom',25,8300); INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('DEV002','Jerry',28,9300.80); INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('SALES001','John',23,5000); INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('SALES002','Kerry',28,6200); INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('SALES003','Stone',22,1200); INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('HR001','Jane',23,2200.88); INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('HR002','Tina',25,5200.36); INSERT INTO T_Employees(Number,Name,Age,Salary) VALUES('IT001','Smith',28,3900); INSERT INTO T_Employees(Number,Age,Salary) VALUES('IT002',27,2800);
2.数据检索
1、简单的数据检索 ：SELECT * FROM T_Employees
2、只检索需要的列 ：SELECT Number FROM T_Employees 、SELECT Name,Age FROM T_Employees
3、列别名：SELECT Number AS 编号,Name AS 姓名,Age AS Age111 FROM T_Employees 写不写AS都行
4、计算列：SELECT Number 编号,Name 姓名,Age Age111,Age+10 十年后的年龄,1+1,now() FROM T_Employees
5、使用where检索符合条件的数据：SELECT Name FROM T_Employees WHERE Salary<5000。故事：新员工的数据检索噩梦
6、还可以检索不与任何表关联的数据：select 1+1;select now();
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 12 节 聚合函数（数据汇总）
1、SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量）
2、大于25岁的员工的最高工资 ：SELECT MAX(Salary) FROM T_Employees WHERE Age>25
3、最低工资和最高工资：SELECT MIN(Salary),MAX(Salary) FROM T_Employees
4、大于25岁的员工人数：SELECT COUNT(*) FROM T_Employees WHERE Age>25
5、全体员工的工资总和平均工资：SELECT SUM(Salary),AVG (Salary) FROM T_Employees
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 13 节 OrderBy排序
1、ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序(从小到大排列，ASC)还是降序(从大到小排列，DESC)
2、按照年龄升序排序所有员工信息的列表：SELECT * FROM T_Employees ORDER BY Age ASC
3、按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序 ：SELECT * FROM T_Employees ORDER BY Age DESC,Salary DESC
4、RDER BY子句要放到WHERE子句之后 ：SELECT * FROM T_Employees WHERE Age>23 ORDER BY Age DESC,Salary DESC
经验：在写order by age ASC,ASC升序时，必须写（明确，代码表达意思清楚）
order by 一定在where 之后
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 14 节 Like模糊匹配
通配符过滤使用LIKE 。
1、单字符匹配的通配符为半角下划线“_”，它匹配单个出现的字符。以任意字符开头，剩余部分为“erry” ：SELECT * FROM T_Employees WHERE Name LIKE '_erry'
2、多字符匹配的通配符为半角百分号“%”，它匹配任意次数（零或多个）出现的任意字符。 “k%”匹配以“k”开头、任意长度的字符串。检索姓名中包含字母“n”的员工信息 ：SELECT * FROM T_Employees WHERE Name LIKE '%n%'
3、Like性能较差，很容易造成全表扫描，谨慎使用。后面会讲数据库优化(索引等)，项目中做搜索用全文检索。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 15 节 null不知道
1、数据库中，一个列如果没有指定值，那么值就为null，数据库中的null表示“不知道”，而不是表示没有。因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”。
2、SELECT * FROM T_Employees WHERE NAME=null ； SELECT * FROM T_Employees WHERE NAME!=null ；都没有任何返回结果，因为数据库也“不知道”。
3、提问：如果T_Employees表中Name列的值是null的话，那么下面查询结果是什么？Select Name+"a" FROM T_Employees 。
答案：还是null，因为null和任何的东西做任何的运算都是null，null+"a"还是null。
select 'b'+'a' :0 select null +'a' :null
4、SQL中使用is null、is not null来进行空值判断：
SELECT * FROM T_Employees WHERE NAME is null ；
SELECT * FROM T_Employees WHERE NAME is not null ；
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 16 节 limit获取部分结果集 （只有mysql 支持）
1、LIMIT关键字用来限制返回的结果集， LIMIT放在SELECT语句的最后位置，语法为“LIMIT 首行行号，要返回的结果集的最大数目” 。比如下面的SQL语句将返回Name不为空的、按照工资降序排列的从第二行开始（行号从0开始）的最多五条记录：
SELECT * FROM T_Employees where Name is not null
ORDER BY Salary DESC
LIMIT 2,5
2、limit一定要放到所有的语句的最后。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 17 节 Groupby分组查询
1、数据分组用来将数据分为多个逻辑组，从而可以对每个组进行聚合运算。SQL语句中使用GROUP BY子句进行分组，使用方式为“GROUP BY 分组字段”。分组语句一般和聚合函数一起使用，GROUP BY子句负责将数据分成逻辑组，而聚合函数则对每一个组进行统计计算。
2、查看公司员工有哪些年龄段的：
SELECT Age FROM T_Employees GROUP BY Age
将Age相同的数据行放到一组，分组后的数据可以看作一个临时的结果集，而SELECT Age语句则取出每组的Age字段的值，这样我们就得到上表的员工年龄段表了。
3、如果SELECT语句有WHERE子句，则GROUP BY子句必须放到WHERE语句的之后。
4、GROUP BY子句将检索结果划分为多个组，每个组是所有记录的一个子集。上面的SQL例子在执行的时候数据库系统将数据分成了下面的分组：
5、分组后就可以对组内的数据采用聚合函数进行统计了：
1）计算每个分组中的员工平均工资
SELECT Age,AVG(Salary) FROM T_Employees GROUP BY Age
2）查看每个年龄段的员工的人数：
SELECT Age,COUNT(*) FROM T_Employees GROUP BY Age
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 18 节 left join联合查询
1.初始化数据
INSERT INTO T_Customers(Id,Name,Age) VALUES(1,'TOM',21);
INSERT INTO T_Customers(Id,Name,Age) VALUES(2,'MIKE',24);
INSERT INTO T_Customers(Id,Name,Age) VALUES(3,'JACK',30); INSERT INTO T_Customers(Id,Name,Age) VALUES(4,'TOM',25); INSERT INTO T_Customers(Id,Name,Age) VALUES(5,'LINDA',30); INSERT INTO T_OrderTypes(Id,Name) VALUES(1,'现货订单'); INSERT INTO T_OrderTypes(Id,Name) VALUES(2,'预订订单'); INSERT INTO T_OrderTypes(Id,Name) VALUES(3,'预购订单'); INSERT INTO T_OrderTypes(Id,Name) VALUES(4,'内部'); INSERT INTO T_Orders(Id,Number,Price,CustomerId, TypeId) VALUES(1,'K001',100,1,1); INSERT INTO T_Orders(Id,Number,Price,CustomerId, TypeId) VALUES(2,'K002',200,1,1); INSERT INTO T_Orders(Id,Number,Price,CustomerId, TypeId) VALUES(3,'T003',300,1,2); INSERT INTO T_Orders(Id,Number,Price,CustomerId, TypeId) VALUES(4,'N002',100,2,2); INSERT INTO T_Orders(Id,Number,Price,CustomerId, TypeId) VALUES(5,'N003',500,3,4); INSERT INTO T_Orders(Id,Number,Price,CustomerId, TypeId) VALUES(6,'T001',300,4,3); INSERT INTO T_Orders(Id,Number,Price,CustomerId, TypeId) VALUES(7,'T002',100,1,1);1、查询每张订单的订单号、价格、对应的客户姓名以及客户年龄
SELECT o.Number,o.Price,c.Name,c.Age
FROM T_Orders o
LEFT JOIN T_Customers c
ON o.CustomerId=c.Id
2、添加where语句(显示价格>=150元的订单)
SELECT o.Number,o.Price,o.CustomerId,
c.Name,c.Age FROM T_Orders o
LEFT JOIN T_Customers c ON o.CustomerId=c.Id
WHERE o.Price>=150
3、可以join多张表：
SELECT o.Number 订单号,o.Price 价格, c.Name 客户姓名,c.Age 客户年龄,t.Name 订单类型 FROM T_Orders o
LEFT JOIN T_Customers c ON o.CustomerId=c.Id
LEFT JOIN T_OrderTypes t ON o.TypeId=t.Id
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第 19 节 外键约束(初学时加上外键，公司看要求)
1、如果删除/更新T_Customers一行记录，那么就可能会导致T_Orders中存在CustomerId为非法值的数据，使得程序逻辑错误。一般不会更新主键Id的值，所以谈外键约束的时候只谈“删除T_Customers时”。
2、外键约束：当删除T_Customers中一条数据的时候，如何处理T_Orders等存在指向T_Customers外键的行。外键约束建立在外键字段***Id的表上(t_orders)。
3、建外键约束的方法：新建或者修改表的时候“外键”→“添加外键”。名：自动命名即可；栏位名：CustomerId；参考表：t_customers；外栏位名：Id；删除时、更新时：一般默认RESTRICT(CASCADE:删除T_Customers一行时把它的订单也删除了；SET NULL:删除T_Customers一行时把它的订单CustomerId设置为NULL；NO ACTION/RESTRICT：拒绝删除)。
4、有的公司不习惯建外键，而是通过程序进行数据合法性控制，对于初学者先不建议这样，都把外键加上。
外键在哪个表 就改哪个表的外键设置
约束策略：
删除时 和 更新时
RESTRICT:限制主键表删除（更新）,不允许直接删除父表的该值；
NO ACTION：拒绝删除
CASCADE：级连，主键表删除，对应的记录也全部删除
SET NULL：设置为NULL
扩展学习：
DDL：drop table,create table alter table....
存储过程，触发器，结束，子查询，除了left join 外的其他join
