如鹏网学习笔记（五）MySql基础

MySQL基础

一、数据库概念
　　1，网友装备信息、论坛帖子信息、QQ好友关系信息、学籍管理系统中的学生信息等都要“持久化”的保存到一个地方，
　　　　如果通过IO写到文件中，那么会非常麻烦，而且不利于多人共享数据

　　2，我们开发大部分软件、网站都要大量用到书库，甚至开发游戏、手机App也要用到数据库，公司面试的时候数据库的考核在三分之一以上

　　3，数据库（DataBase）是保存数据的仓库，可以方便的把数据放进去，并且把数据根据各种需要取出来。
　　　　数据库管理系统（Database Management SYSTEM,DBMS）是对数据库进行管理（增删改查等）的软件，
　　　　常用的DBMS有MYSQL、Oracle、DB2、MSSQLServer等。
　　　　MYSQL是开源、免费的，因此应用最广泛

　　4，不同的DBMS用法大同小异，掌握了一个也就基本等于掌握了其他DBMS

二、数据库、表、列

　　1，Table（表）:存放数据的“书架的格子”

　　2，两种叫法：列（column）/字段（Field）

三、MYSQL环境安装

　　1，下载
　　　　http://www.mysql.com/downloads/ MySQL Enterprise Edition (commercial)、MySQL Cluster CGE (commercial)都是收费版；MySQL Community Edition 是开源免费版。

　　2，添加系统服务
　　　　以管理员身份运行命令行，cd到mysql的bin文件夹，执行“mysqlld -install”

　　　　卸载服务：mysql -remove


四、MYSQL管理工具

　　1，MYSQL管理工具用于对DBMS进行数据库创建、表创建等管理。开发人员必装。管理工具不是DBMS，DBMS不可少

　　2，管理工具有很多：Navicat、Workbench(官方)、phpMyAdmin、SQLyog、MySQL-Front，用法大同小异。
　　　　NaviCat lite是免费版，功能学习够用了。

　　3，安装后在开始菜单的“PremiumSoft”中。
　　　　主菜单“文件”→“新建连接”→“MYSQL”，“连接名”随意，“主机名或IP地址”填MYSQLServer的IP地址或者主机名，

　　　　装在本机填127.0.0.1(本地回环地址)，用户名密码都是root(生产环境中密码要改的复杂一点)。

　　4，如果连接不报错，说明MYSQLServer和管理工具都安装没有问题

五、主键（PrimaryKey）

　　1，主键就是数据行的唯一标识。不会重复的列才能当主键。一个表可以没有主键，但是会非常难以处理，
　　　　因此没有特殊理由表都要设定主键

　　2，主键有两种选用策略：
　　　　业务主键和逻辑主键

　　　　业务主键就是使用有业务意义的字段做主键，比如身份证号、银行账号等

　　　　逻辑主键是使用没有任务业务意义的字段做主键，完全是给程序看的，业务人员不会看的数据。

　　　　因为很难保证业务主键不会重复（身份证号重复）、不会变化（账号升位），因此推荐用逻辑主键


六、表间关联、外键（ForeignKey）

　　1，一张表中的一个字段是指向了另一张表的主键，这样就将两张表的信息连接在一起了

　　2，可以多张表“串联”，也可以“多对多”的在两张表之间存在一张关系表


七、建库建表

　　1，根节点点右键，新建数据库，数据库名字取得有意义，比如“study1”，字符集建议用UTF-8。

　　2，在study1下的“表”节点下右键“新建表”，“栏位”其实指的就是列。

　　　　Id（主键，潜规则的名称，int、不允许为空、点右键“主键”）
　　　　Name（nvarchar，长度为10，不允许为空）
　　　　Gender（bit，不允许为空）
　　　　保存为“T_Persons”

　　3，建表常见错误：

　　　　列名/表名不要用可能的关键字，不要有空格（包括前后）、不要有特殊字符

　　4，MYSQL的存储引擎有很多，最常用的是InnoDB和MyISAM，MyISAM效率较高，但是不支持事务、外键约束等特性，
　　　　因此一般建议用InnoDB，新版本默认也是InnoDB。

　　　　怎么设：建表时“选项”→“引擎”。建好了如何看引擎：表上点右键“对象信息”。

　　5，常用的数据类型
　　　　文本：
　　　　CHAR(*)：最多255个字节的定长字符串，它的长度必须在创建时指定
　　　　VARCHAR(*)：最多255个字节的可变长度字符串，它的长度必须在创建时指定
　　　　TEXT：最大长度为64K字符的变长文本
　　　　TINYTEXT：最大长度为255字符的变长文本
　　　　MEDUIMTEXT：最大长度为16K字符的变长文本
　　　　LONGTEXT：最大长度为4GB字符的变长文本

 

 

　　　　整数：（考虑数据取值后选择尽可能小的类型）
　　　　tinyint：1字节。有符号值：-128 到127；无符号值：0到255
　　　　smallint：1字节。有符号值：-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

八、SQL语句入门

　　1，不能总是用鼠标点来点去手动管理数据，要能够通过语言和数据库自动交流，SQL语句就是和数据库“交谈”专用的语句

　　2，SQL语句中字符串一般用单引号

　　3，SQL语句是大小写不敏感的

　　4，NavCat中找到执行SQL语句的地方“查询”——“新建查询”，编写SQL后点击“运行”执行SQL语句

　　5，最简单的SQL：

　　　　查看一个表的全部数据：select * from T_Persons

　　6，Insert
　　　　简单的插入数据的SQL语句：
　　　　insert into T_Persons (Id,Name,Age,Gender) values(5,'jim',20,1)

　　　　Insert语句可以省略表名后的列名，但是强烈不推荐

　　　　如果插入的行中有些字段的值不确定，那么Insert的时候不指定那些列即可。“不允许为空”的列在插入的时候不能省略

　　　　自动递增/自增（Auto Increment）：字段自增可以避免并发等问题，不要程序员代码控制自增。用自增字段在Insert的时候不用指定值。

　　7，修改表结构的方法：点“设计表”

　　　　1，把“允许为空”的字段修改为“不允许为空”，需要先给旧数据默认值(用Update语句)，
　　　　　　如果之前是测试数据无所谓，可以把旧数据都删除(增加Height字段)

　　　　2，修改列的数据类型要注意旧数据能否兼容转换为新类型；修改数据的长度的时候也是如此

　　8，数据更新

　　　　更新一个列：
　　　　update T_Persons Set Age=30

　　　　更新多个列：
　　　　update T_Persons s Set Age=30，Name='tom'

　　　　表达式：UPDATE T_Persons Set Age=Age+1

　　9，条件数据更新

　　　　更新一部分数据： 
　　　　UPDATE T_Persons Set Age=30 where Name='tom'，用where语句表示只更新Name是'tom'的行，
　　　　注意SQL中等于判断用单个=，而不是==。

　　　　Where中还可以使用复杂的逻辑判断UPDATE T_Persons Set Age=30 where Name='tom' or Age<25，or相当于Java中的||

　　　　where (Age>20 and Age<30) or(Age=80)

　　　　Where中可以使用的其他逻辑运算符：or、and、not、<、>、>=、<=、!=（或<>）等

　　10，数据删除
　　　　删除表中全部数据:
　　　　DELETE from T_Persons

　　　　删除表中部分数据
　　　　Delete from T_Persons where Age>20

　　　　DELETE只是删除数据，表还在，

　　11，删除表
　　　　Drop table T_Persons

九、数据检索
　　1，简单的数据检索：Select * from T_Persons

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

十、数据汇总
　　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

十一、数据排序

　　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，ORDER BY子句要放到WHERE子句之后 ：
　　　　SELECT * FROM T_Employees WHERE Age>23 ORDER BY Age DESC,Salary DESC

十二、通配符过滤

　　1，通配符过滤使用like

　　2，单字符匹配的通配符为半角下划线“_”，它匹配单个出现的字符。
　　　　以任意字符开头，剩余部分为“erry”：
　　　　Select * from T_Employees where Name like '_erry'

　　3，多字符匹配的通配符为半角百分号“%”，它匹配任意次数（0或者多个）出现的任意字符。
　　　　k% 匹配以k开头、任意长度的字符串
　　　　检索姓名中包含字母n的员工信息：
　　　　select * from T_Employees where name like '%n%'

　　4，like性能较差，很容易造成全表扫描，谨慎使用。
　　　　后面会讲数据库优化（索引等），项目中做搜索用全文索引

十三、空值处理

　　1，数据库中，一个列如果没有指定值，那么值就为null，数据库中的null表示“不知道”，而不是表示没有。
　　　　因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”

　　2，Select * from T_Employees where name = null;
　　　　select * from T_Employees where name!=null;
　　　　都没有任何返回结果，因为数据库“也不知道”
　　　　select name+"a" from T_Employees

　　3，SQL中使用is null、is not null 来进行空值判断
　　　　SELECT * FROM T_Employees WHERE NAME is null ； 
　　　　SELECT * FROM T_Employees WHERE NAME is not null ；

十四、limit

　　limit关键字用来限制返回的结果集，limit放在select语句的最后位置，
　　语法为：limit 首行行号，要返回的结果集的最大数目

　　比如下面的SQL语句将返回Name不为空的、按照工资降序排列的从第二行开始（行号从0开始）的最多五条记录
　　select 8 from T_Employees
　　where Name is not null
　　order by Salary desc
　　limit 2,5

　　注意：limit一定要放到所有的语句的最后

　　使用场景：开发网站、开发手机程序的时候分页用的非常多

十五、group by

　　1，数据分组用来将数据分为多个逻辑组，从而可以对每个组进行聚合运算。
　　　　SQL语句中使用group by子句进行分组，使用方式为“group by 分组字段”。
　　　　分组一般和集合函数一起使用，group by子句负责将数据分成逻辑组，而聚合函数则对每个组进行统计计算

　　2，查看公司员工有哪些年龄段的：
　　　　select Age from T_Employees group by Age

　　3，将Age相同的数据行放到一组，分组后的数据可以看作一个临时的结果集，而SELECT Age语句则取出每组的Age字段的值，
　　　　这样我们就得到上表的员工年龄段表了。

　　4，如果SELECT语句有WHERE子句，则GROUP BY子句必须放到WHERE语句的之后

　　5，group by子句将检索结果划分为多个组，每个组是所有记录的一个子集。

十六、group by与聚合函数

　　1，分组后就可以对组内的数据采用聚合函数进行统计了；

　　　　计算每个分组中员工的平均工资：
　　　　select Age,avg(salary) from T_Employees
　　　　group by Age

　　　　查看每个年龄段的员工的人数：
　　　　select Age,count(*) from T_Employees 
　　　　group by Age

十七、join
　　1，真是的业务系统中，各个表之间都存在这种联系，很少存在不与其他表存在关联关系的表，
　　　　而在实现业务功能的时候也经常需要从多个表中进行数据的检索，而进行多表检索最常用的技术就是表连接

　　2，如果没有表连接，那么查询每张订单的客户姓名就要先查询订单，再去查询客户表，麻烦而且效率低

　　3，SQL中使用JOIN关键字来进行表连接。
　　　　表连接有多种不同的类型，被主流数据库系统支持的有交叉连接（CROSS JOIN）、内连接（INNER JOIN）、外连接（OUTTER JOIN）

　　　　外连接分为：left join、right join。

十八、外键约束

　　1，如果删除/更新T_Customers一行记录，那么就可能会导致T_Orders中存在CustomerId为非法值的数据，使得程序逻辑错误。
　　　　一般不会更新主键Id的值，所以谈外键约束的时候只谈“删除T_Customers时”

　　2，外键约束：当删除T_Customer中一条数据的时候，如何处理T_Orders等存在指向T_Customers外键的行。外键约束建立在外键字段***Id的表上

　　3，建外键约束的方法：新建或者修改表的时候“外键”→“添加外键”。
　　　　名字：自动命名即可；栏位名：CustomerId；参考表：t_customers；外栏位名：Id；
　　　　删除时、更新时：一般默认RESTRICT(CASCADE:删除T_Customers一行时把它的订单也删除了；
　　　　SET NULL:删除T_Customers一行时把它的订单CustomerId设置为NULL；NO ACTION/RESTRICT：拒绝删除)。

十九、扩展学习资料
　　1，DDL：

　　2，存储过程、触发器、约束、子查询、处理left join之外其他的join