MySql学习笔记

2019-10-21补充：
什么是数据库三大范式？

每一列都是不可分割的原子数据项
实体的属性完全依赖于主关键字
任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

2019-07-18补充下图：（感觉之前对这个连接查询理解不到位，特补充，略略略）

SQL（Structured Query Language）结构化查询语言。

• DML(Data Manipulation Language statements)数据操作语言（处理数据等操作）（使用者角度）；
• DDL(Data Definition Language statements)数据定义语言（建表，建库，建视图等）（建设者角度）；
• DCL(Data Control Language statements)数据控制语言（用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等）（管理员角度）。

入门最基本语句

1. 库级知识：
   1.1 显示数据库: show databases;
   1.2 选择数据库: use dbname;
   1.3 创建数据库: create database dbname charset utf8;
   1.4 删除数据库: drop database dbname;

数据库名不能改变。
2. 表级操作
2.1 查看库下面的所有表: show tables（包括视图）;
2.2 查看表的创建过程: show create table tableName（查看视图创建过程：show create view 视图名）;
2.3 删除表: drop table 表名;（删除视图：drop view 视图名）
2.4 清空表数据: truncate 表名
2.5 建表：

　create table tbName (
列名称1　列类型　[列参数]　[not null default ],
....列2...
....
列名称N　列类型　[列参数]　[not null default ]
)engine myisam/innodb charset utf8/gbk

例子：

create table user (
    id int(10) auto_increment primary key,
    name varchar(20) not null default '',
    age tinyint unsigned not null default 0
   )engine=innodb charset=utf8;

2.6 修改表名:rename table oldname to newname;
2.7 查看表结构: desc tableName;
2.8 查看表详细信息：show table status;
（查看某一张表：show table status where name=‘表名’）

2.9 操作数据

• 增
  Insert 3问:
  1: 插入哪张表?
  2: 插入哪几列?
  3: 这几列分别插入什么值?

Insert into TableName
(列1,列2.... 列n)
Values
(值1,值2,....值n)

注意：如果没有声明列明,则默认插入所有列.因此,值应该与全部列,按顺序一一对应.

• 删
  Delete 2问
  从哪张表删除数据?
  要删除哪些行?

Delete from 表名 where  expr

注意：以行为单位进行删除，不能只删除某一列。

• 改
  Update 4问
  1: 改哪张表?
  2: 改哪几列的值?
  3:分别改为什么值?
  4:在哪些行生效?

Update 表名 
Set 
列1 = 新值 1,
列2 = 新值2,
列n = 新值n.....
Where  expr

• 查
  查询3问
  1:查哪张表的数据?
  2:查哪些列的数据?
  3:查哪些行的数据?

Select 列1, 列2, 列3,...列n
From 表名
Where expr;

注释：

1. 列是变量
2. 变量可以计算
   
3. where是表达式，值为真或假：比如where 1时，对应表中每一行都是真，所以会取出全部行数据，where 0 时，对应表中每行数据都为假，所以取出0行数据。

注意：

• 解决字符集问题:
  默认建表一般用utf8, 而我们在windows下窗口是GBK的,因此,需要声明字符集： Set names gbk;

• 打\c,退出本语句

• 关于查询：
  
  注释： 比较运算符和逻辑运算符可以组合在一起。
  例如：select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);
  like模糊匹配：%通配任意字符，_通配单一字符。

• group分组与统计函数
  avg() 平均值
  max() 最大值
  min() 最小值
  sum() 总和
  count() 数量
  注意： count(字段)不会统计null值，使用count(*)时会计算null

SELECT COUNT(class_id) FROM stu;

• distinct用于去除结果集中的重复记录
  获取所有班级编号

SELECT DISTINCT class_id AS class FROM stu WHERE class_id IS NOT NULL;

获取学生数，同班同名的算一个

SELECT COUNT(DISTINCT class_id,sname) FROM stu WHERE class_id IS NOT NULL;

• having筛选
  where having区别：
  下面以一个例子来具体的讲解：

1. where和having都可以使用的场景

1）select addtime,name from dw_users where addtime> 1500000000

2）select addtime,name from dw_users having addtime> 1500000000

解释：上面的having可以用的前提是我已经筛选出了addtime字段，在这种情况下和where的效果是等效的，但是如果我没有select addtime就会报错！！因为having是从前面筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

2. 只可以用where，不可以用having的情况

1） select addtime,name from dw_users where addtime> 1500000000

2） select phone,name from dw_users having addtime> 1500000000 //报错！！！因为前面并没有筛选出addtime字段

3. 只可以用having，不可以用where情况

查询每种category_id商品的价格平均值，获取平均价格大于100元的商品信息

1）select category_id , avg(price) as ag from dw_goods group by goods_category having ag > 100

2）select category_id , avg(price) as ag from dw_goods where ag>100 group by goods_category //报错！！因为from dw_goods 这张数据表里面没有ag这个字段

注意:where 后面要跟的是数据表里的字段，如果我把ag换成avg(price)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。

• order by 排序
  1 asc 升序（默认）
  desc 降序
  2 多列排序：select * from result order by name ,score desc;
  3 随机获取学生SELECT * FROM stu ORDER BY RAND() LIMIT 1;
  4 对别名字段可排序bash SELECT birthday,MONTH(birthday) as m FROM stu ORDER BY m ASC;
  5 排序受校对规则影响
  6 使用field()函数实现自定义排序order by field（str，str1，str2，str3，str4……）
  str与str1，str2，str3，str4比较，其中str指的是字段名字，
  意为：字段str按照字符串str1，str2，str3，str4的顺序返回查询到的结果集。如果表中str字段值不存在于str1，str2，str3，str4中的记录，放在结果集最前面返回。加desc倒序排列。
• limit限制取出条数
  用法：limit 偏移量，条数。
  注意： where group by having order by limit 几个子句顺序不能变。
• where 型子查询
  内层查询的结果作为外层查询的条件。

例子：查询每个栏目下最新的商品
select goods_id,goods_name,cat_id from goods where goods_id in(select max(goods_id) from goods group by cat_id);

• from 型子查询
  把先from查询的内容当成一张表来看。
• exists 型子查询
  例子：查询所有有商品的栏目&