B6第六章 第 6 节： MYSQL常用数据类型

B6第六章第0节讲MYSQL不用SQLServer

第六章第0节
1、SQLserver只是微软出的一个dbms，但并不是。net开发一定是SQLserver。很多公司是“。net+MySQL””。net+Oracle“进行开发
why？
缺点： SQLserver成本高，一台许可费在3W--10W；mysql社区版免费；
SQLserver不适合建立集群MySQL、oracle都很方便
SQLserver性能比MySQL。oracle差；
2、MySQL、sqlserver学习起来没有太大的区别；sql语法几乎一模一样；管理工具大同小异；ado.net链接和mysql和链接SQLserever也是几乎一模一样；

B6第六章第4节主键（业务主键和逻辑主键)和外键的概念

第六章第4节
主键有两种选用策略：业务主键和逻辑主键。业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等；逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复（身份证号重复）、不会变化（帐号升位），因此推荐用逻辑主键。

B6第六章第5节 建数据库建立表的方法

第六章第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常用数据类型
1、char 字符不够，空格来凑。（面试题）
2、smallint:2字节。
3、“小数 二进制表示”
文本
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

B6第六章 第 7 节： SQL简介（Select和Insert）

第六章 第 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的时候不用指定值。修改表结构的方法【设计表】。

B6第六章 第 8 节： 自动递增列和修改列的数据类型注意的陷阱

第六章 第 8 节： 自动递增列和修改列的数据类型注意的陷阱
1、根据表设计器，勾选自增；写语句的时候默认不写主键就会递增的增加主键的数字了
2、将允许为空的勾，都掉的话，对表中原来没有（null）数据的值，需要先填写内容应该，这样才可以保存表
3、修改列的数据类型要注意旧数据能否兼容转换为新类型；修改数据的长度的时候也是如此。
测试：Age修改为Varchar ;Name修改为Varchar(2)。

注意：写语句的时候，文件名字一定要写正确，不过大小写不用强制；
插入记录的顺序，要写正确；

 

B6第六章  第 9 节： Update数据更新

第六章 第 9 节： Update数据更新
update t_students Set Age=30
update t_students Set Age=30,Name=xiaomign
update t_students set Age=Age+5
update t_students set Age=10 where Name='ton'(有条件的更新)
update t_students set Age=10 where Name='ton' or Age<25

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、<、>、>=、<=、!=（或<>）等

B6第六章 第 10 节： Delete数据删除

第六章 第 10 节： Delete数据删除
delete from t_studens where Age>20 or Age<=10(删除指定的结果）

删除表中全部数据：DELETE FROM T_Persons。

Delete 也可以带where子句来删除一部分数据：DELETE FROM T_Persons WHERE Age > 20

Delete只是删除数据，表还在。(*)Drop table T_Persons表都没有了

B6第六章 第 11 节： 数据初始化和Select语法1

第六章 第 11 节： 数据初始化和Select语法1

T_Employees初始数据：
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);

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();

B6第六章 第 12 节： 聚合函数

第六章 第 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

 

B6第六章  第 13 节： OrderBy排序

第六章 第 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

B6第六章 第 14 节： Like模糊匹配

第六章 第 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性能较差，很容易造成全表扫描，谨慎使用。后面会讲数据库优化(索引等)，项目中做搜索用全文检索

匹配以“n”结尾的人名

B6第六章  第 15 节： null不知道

第六章 第 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、Select Name+"a" FROM T_Employees 结果是八个零和一个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 ；

B6第六章 第 16 节： limit获取部分结果集

第六章 第 16 节： limit获取部分结果集
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一定要放到所有的语句的最后。

B6第六章 第 17 节： Groupby分组查询

第六章 第 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

B6第六章 第 18 节： left join联合查询

第六章 第 18 节： left join联合查询
初始数据：
1、T_Customers 2、T_OrderTypes 3、T_Orders

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

B6第六章 第 19 节： 外键约束

第六章 第 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、有的公司不习惯建外键，而是通过程序进行数据合法性控制，对于初学者先不建议这样，都把外键加上。