Mysql全程讲解（难点：键、索引和约束的关系）

1、Mysql常识

数据库在服务器上安装完成后，一般通过客户端管理工具访问服务器，常见的有Navicat、SQLyog等，输入服务器IP、数据库端口号、数据库用户名和密码即可，本地登陆命令是是mysql -u数据库用户-p登陆密码。登陆后，使用show databases;查看系统中Mysql已经创建出来的数据库，安装完后数默预置就有information_schema，mysql和test三个库。

数据库中的数据都是以二维表的形式被保存在不同的数据表中。其中每行表示一条数据记录，每条记录包含多个字段。数据库中的每张表都会通过几个文件占用一定的存储空间，分别存储表结构、表里数据和表索引信息等。如果确定不再使用某张表，就删除以释放存储空间。

2、数据库设计

2.1、安装数据库

          以Mysql为例，多个项目可用同一个数据库，也可以单独使用一台服务器安装全新Mysql，安装完成后需要提供四个信息：

2.2、建库脚本

    如何是大型复杂的项目，需要编写规范的建库脚本create_database.sql。但如果是规模较小的项目，本地或远程登录Mysql后在库模式下执行create database 数据库名称;就可以创建项目需要的数据库了。

2.3、建表脚本

实际开发中，当从产品经理处拿到新需求后，首先就是考虑如何建表，根据E-R模型需求有哪些关键信息就建立哪些字段，并且是通过建表脚本或者是客户端软件的建表模板而不是直接登录数据库服务器通过CLI的方式建表。

3、增删改查基础

增

createdatabase 数据库名称; 创建新的数据库。

use 数据库名称; 选择一个数据库对其进行操作。

createtable 表名( 字段1 字段类型[约束或索引][额外设置],

字段2 字段类型[约束或索引] [额外设置],

…………

[索引或约束]，

)[ENGINE=MYISAMAUTO_INCREMENT=1 DEFAULT CHARSET=utf-8]; 创建新表

insertinto 表名(字段列表) values (对应插入的值列表);     插入新记录

altertable 表add 新字段字段类型[宽度] [约束或索引] [额外设置][位置];

删

dropdatabase 数据库名称; 删除数据库

droptable 数据表名称; 删除一张表

deletefrom 表查询条件; 删除表中符合条件的记录

altertable 表drop 字段; 删除一个字段

改

altertable 表名renameto 新表名;改表名

update表名set 字段=新值查询条件;更新表的某个字段

altertable 表change旧字段名新字段名; 改字段名

altertable 表modify字段名新字段类型; 改字段的类型

查

showtables;显示当前库已经存在的表

describle表名; 查看表结构

selecta,b from 表名 查询条件;查看符合条件的表的a和b字段

select distinct namefrom 表名;排重显示表中的name字段

select(temp_hi+temp_lo)/2 astemp_avgfrom weather;

标示符as可作为单个字段或表达式的别名，输出时显示该别名。

 

4、查询子句、子查询和多表查询

4.1、五种查询子句

a．where子句

where后接布尔表达式针对表中的列发挥作用，查询数据，支持算术运算符、关系运算符和逻辑运算符

1、selectgoods_id,goods_name from goods where market_price - shop_price> 200;

输出market_price比shop_price多200的记录的商品ID和商品名

2、whereprice>=3000 and price <= 5000 or price betweenand price 1000;

 取3000-5000或者500-1000的值 

3、delete from 表whereproduct_id = 30; 删除表中product_id为30的记录

4、where goods_namelike '诺基亚%'; %匹配0到多个字符

5、where goods_namelike '诺基亚N_'; _匹配单个字符

b．having子句

 having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写，

不同的是where只能针对表中的列发挥作用，查询数据， Having是对查询结果中的列发挥作用，筛选数据。

1、selectgoods_id,good_name,market_price - shop_price as s from goods having s>200 ;比a.where字句的第一个例子，多输出了差价s

 2、select cat_id,market_price from goods wherecat_id> 3 having market_price> 200;输出cat_id、market_price，要求是cat_id>3且market_price大于200的记录

c．group by（分组）

一般情况下group需与统计函数（聚合函数，共五种）一起使用才有意义，从字面意义上理解就是根据“By”指定的规则先对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

d．order by（排序）

从字面意义上理解就是根据“By”指定的规则对输出结果进行排序

sql语句没有order by//按表中记录的顺序默认输出

order byprice //默认升序排列

order by price asc//升序排列

order by price desc//降序排列

e．limit（限制结果区间）

用于获取一段区间的记录，第一个参数是初始值，第二个参数是偏移量，常常和order by字句合用。

selectgood_id,goods_name,goods_price from goodsorder by good_price limit 3,3;

取价格第4-6高的商品，输出其三字段，并按字段good_price升序

4.2、子查询

a、where型子查询

把内层查询结果当作外层的查询条件

select name from (select name,count(*) as guakefrom stu where score < 60having guake>=2);

查出挂科2门以上的同学有多少个，输出其姓名

b、from型子查询

把内层的查询结果(可以看做是内存里的一张临时表)供外层再次查询

  selectname,avg(score) from stu where namein (select name from (select name,count(*) as guake from stuwhere score < 60havingguake>=2)) group by name;

然后获取这些同学的名字，最后再计算每个同学的平均分

4.3、多表查询

当查询结果来自多个表时，就需要进行多表查询，常用的有内连接、外连接和自连接3种，实际开发