MySQL

数据库

1.概念

• 数据库是一个文件系统，只不过我们需要用命令（SQL）来操作，用来存储数据，带有访问权限，限制不同的人有不同的操作。

2. 分类

• 关系型数据库（SQL）：用来描述实体与实体之间的关系
  a. mysql : 开源免费 被 oracle收购后开始收费
  b. mariadb：是mysql开源版本的分支，基本命令时一致的
  c. oracle：甲骨文公司，收费，适用于大型电商网站
  d. db2：IBM公司，大多银行系统使用
  e. sqlserver：Windows里面，政府网站（asp.net），图形化工具成熟
• 非关系型数据库（NOSQL）：key => value的形式存储数据
  a. mongodb
  b. redis : set get 来操作数据

3. SQL语句（Structure Query Language）

• DDL (Data Define Language)：数据定义语言，定义数据库数据表和他们的结构 create(创建) drop(删除) alter(修改)
  1. 创建数据库
     create database databasename;
     指定字符集和校对规则
     create database databasename character set utf8 collate utf8_bin;
  2. 查看数据库
     a. 查看所有数据库
     show databases;
     b. 查看创建数据库的定义
     show create database databasename;
     c. 查看当前正在操作的数据库
     select database();
status; 显示 current database
     d. 切换数据库
     use databasename;
  3. 修改数据库字符集
     alter database databasename character set 字符集;
  4. 删除数据库
     drop database databasename;
  5. 创建数据表：列的约束（primary key(主键) unique(唯一) not null(非空)）
     create table tablename(字段名 类型（长度） 约束，字段名 类型（长度） 约束 )
  6. 查看表
     a. 查看所有表
     show tables;
     b. 查看表的定义
     show create table tablename;
     c. 查看表结构
     desc tablename;
  7. 修改表
     a. 添加列
     alter table tablename add 列名 类型 约束;
     b. 修改列定义
     alter table tablename modify 列名 类型 约束;
     c. 修改列名和定义
     alter table tablename change 旧列名 新列名 类型 约束;
     d. 删除列
     alter table tablename drop 列名;
     e. 修改表名(一般不修改表名)
     rename table tablename to newtablename
     f. 修改表的字符集
     alter table tablename character set 字符集;
  8. 删除表
     a. 删除表及其定义
     drop table tablename;
     b. 删除表中数据,并释放空间，之后插入的数据id标识会连续（覆盖之前的id标识），删除的数据无法恢复
     truncate table tablename
• DML (Data Manipulation Language)：数据操作语言，主要来操作数据 insert(插入) update(修改) delete(删除)
  1. 插入数据：如果values写全，可以不写字段，另外，多个values以逗号隔开可以批量插入数据
     insert into tablename (字段1, 字段 2) values （值1，值2）;
  2. 删除数据：如果没有指定条件，会删除所有记录（一条一条删除），不会释放空间，使用roll back指令可以恢复数据
     delete from tablename [where 条件];
  3. 更新数据
     update table set 字段=值，字段=值 [where 条件]
• DCL (Data Control Language)：数据控制语言，定义访问权限，安全设置，grant
• DQL (Data Query Language)：数据查询语言，select(查询) from(子句) where(子句) 编写顺序 select ...from...where...group by...having...order by...，执行顺序 F-W-G-H-S-O
  1. 基本语法
     select [distinct] [ * ] [列名，列名2] from tablename [where 条件] [group by 字段名] [having 条件过滤] [order by 排序];
  2. 查询表中所有数据
     select * from product;
  3. 查询指定字段
     select pname,price from product;
  4. 别名查询：主要用在多表查询中
     a. 表别名
     select p.pname, p.price from tablename as p;
     b. 列别名
     select pname as 商品名称, price as 商品价格 from product;
     效果
  5. 去掉重复的值（distinct）
     select distinct price from product;
  6. 运算查询 仅仅在查询结果上做 + - * /
select *,price*0.8 as "会员价" from product;
  7. 条件查询 [where 关键字] 关系运算符 > < = >= <= != <>（表示不等于这是标准的sql语法） 逻辑运算and or not
select * from product where price > 60 and price < 100;
select * from product where price between 60 and 100;
  8. 模糊查询 like _： 表示一个字符 %：表示多个字符
select * from product where pname like "%小%";
  9. 排序查询 order by asc 升序(默认) desc 降序
select * from product where pname like "_米%" order by price desc;
  10. 聚合函数，聚合函数不能直接用在 where 条件后面
      a. sum()：求和 select sum(price) from product;
      b. avg()：求平均值 select avg(price) from product;
      c. count()：统计数量 select count(*) from product;
      d. max()：求最大值 select max(price) from product;
      f. min() ：求最小值 select min(price) from product;
      g. select * from product where price > (select avg(price) from product);
  11. 分组 group by
      a. 基本语法
      select cno,count(*) from product group by cno;
      b. having关键字，可以接聚合函数，出现在分组之后；where关键字，不能接聚合函数，出现在分组之前
      select cno,avg(price) from product group by cno having avg(price) > 0 order by desc;

4. 多表关系和多表查询

• 多表之间的外键维护：外键约束foreign key
  1. 添加外键约束 alter table product add foreign key(cno) references category(cid);
• 多表之间的建表原则
  1. 一对多（一个表中一条记录关联另一个表中多条记录）：在多的一方添加一个外键指向一的一方的主键
  2. 多对多：多建一张中间表，至少有两个外键，这两个外键分别指向原来的两张表的主键，将多对多转换成两个一对多的关系。
  3. 一对一：当成一对多处理，给一个表添加外键，或者将两个表的外键设置成一样，或者直接写出一张表
• 多表查询
  1. 交叉连接查询，笛卡尔积，没什么实际意义
     select * from product, category ;
  2. 内连接查询
     a. 隐式内连接
     select * from product as p,category as c where p.cno=c.cid;
     b. 显示内连接
     select * from product as p inner join category as c on p.cno=c.cid;
select p.pname,c.cname from product p inner join category c on p.cno=c.cid;
  3. 外连接查询
     a. 左外连接，把左表中的数据全部查询出来，如果右表中没有对应的数据，用null填充
     select * from product p left outer join category c on p.cno=c.cid;
     b. 右外连接
     select * from product p right outer join category c on p.cno=c.cid;
  4. 分页查询
     select * from product limit 开始索引,个数;
  5. 子查询：查询语句中嵌套查询语句
     select * from product where cno = (select cid from category where cname="手机数码");
select p.pname as "商品名称",(select c.cname from category c where c.cid=p.cno) as "分类名称" from product p;

附：DQL中的表结构

category表结构 product表结构
数据库.png