SQL语言学习笔记

与怪物战斗的人,应当小心自己不要成为怪物。当你远远凝视深渊时,深渊也在凝视你。  --尼采

规范

--范式
  第一范式：表中的每一个列中的元素必须拆分成最小单元
  第二范式：必须有主键，其他元素必须完全依赖于主键，不能依赖一部分
  第三范式：主键外的其他元素必须直接依赖于主键，而不能传递依赖
--E-R模型
  一对一 ：在那张表中都可以添加字段
  一对多 ：在多的那张表中添加字段，不要再一中添加
  多对多 ： 多建立一张表用来存储他们两张表的对应关系，这张表就被称之为聚合表

数据类型

  整数：int bit
  小数：decimal
  字符串：varchar char
  日期时间：date time datetime
  枚举类型：enum

--sql语句最后需要以;结尾
--mysql中可以直接执行sql文件，在sql文件路径下进入MySQL，进入对应的数据库，然后直接source xxx.sql
--显示时间
  select now();
--where 从表中取数据
--having 从查询结果中取数据
--create table if not exists good()  --建立数据库，如果存在这个数据库则不建立，如果给不存在建立

数据库操作

--显示数据库结构
  desc 数据库名;

--显示数据库版本
  select version();
  
--查看所有数据库
  show databases;
  
--使用数据库
  use 数据库名;

--查看当前使用数据库
  select database();
  
--创建数据库语句
  create database 数据库名; 
  create database 数据库名 charset=utf-8;

--删除数据库
  drop database 数据库名;
  
  
数据表操作

--查看数据库中所有的数据表
  show tables;
 
--查看表结构
  desc 表名

表
--auto_increment表示自动增长
--not null 表示不能为空
--primary key 表示主键
--default 默认值

--创建表
  create table 数据表的名字 (
  字段 类型 约束,
  字段 类型 约束
  );
  例：创建一个student表(id, name, age, high, gender, cls_id)
  create table student(
  id int unsigned not null auto_increment primary key,
  name varchar(30),
  age tinyint unsigned default 0,
  high decimal(5,2),
  gender enum("男", "女", "中性", "保密") default "保密",
  cls_id int unsigned
  )

--查看表的创建语句
  show create table 表名字;
  
--修改表-添加字段
  alter table 表名 add 列名 类型;
  
--修改表-修改字段-不重命名版
  alter table 表名 modify 列名 类型及约束;

--修改表-修改字段-重命名版
  alter table 表名 change 原名 新名 类型及约束;

--修改表-删除字段
  alter table 表名 drop 列名;  
  
--删除表
  drop table 表名;
  

增删改查curd(创建:Create, 更新:Update, 读取:Retrieve, 删除:Delete)
  
  
--增加
--主键字段 可以用 0 null default 来占位
--全列插入
  insert into 表名 values(....);
  例：向student中插入一个学生信息
  insert into student vlaues(0, "小李", 20, "女", 1, "1990-01-01");

--部分插入
  insert into student (name, age) values ("小乔", 20);
  
--多行插入
  insert into student (name, age) vlaues ("小乔", 20), ("貂蝉",21);
  insert into student vlaues(0, "小李", 20, "女", 1, "1990-01-01"), (default, "小王", 20, 2, 1, "1990-01-01")

--修改
  update 表名 set 列1=值1, 列2=值2,... where 条件;
  
  
--查询的基本使用

--查询所有列
  select * from 表明;
  
--查询指定列
  select 列1，列2,....from 表名;
  select student.name, student.age from student;

--指定条件查询
  select * from students where name="小李",id=2;
  
--可以使用as为查询的列指定别名
  select s.name as 姓名 from student as s;
  
--去重
  distinct 字段
  select distinct gender from student;
  用分组的方式去重
  select name from student group by name;  --先分组，然后在显示名字

条件查询
--比较运算符(=, <, >, <=, >=等)
  select * from student where age<18;

--逻辑运算符(and, or, not等)
  select * from student where not(age>18 and gender=2);
  
--模糊查询
  --like
    % 替换 零个、一个或者多个
    _ 替换一个
    select name from student where name like "小%"
  
  
  --rlike 正则
    select name from student where name rlike "^周.*伦$"
    
--范围查询
  --in(非连续)
    select name, age from student where age in (12, 25, 34);
    
  --not in(非连续)
    select name, age from student where age not in (12, 25, 34);
    
  --between...and...(连续)
    select name, age from student where age between 18 and 34;  --查询18到34岁的姓名和年龄。

  --not between...and...(连续)
    select name, age from student where age not between 18 and 34;  --查询不在18到34岁的姓名和年龄。
    
--空判断
  --判断空is null
    select * from student where hight is null;
    
  --判断非空 is not null
    select * from student where hight is not null;
    
--排序
  --order by
    --asc 从小到大，升序
      select * from student where (age between 18 and 34) and gender=1 order by age asc;  --查询年龄18-34男性 并按照年龄从小到大排序。
    --desc 从大到小，降序
      select * from student where (age between 18 and 34) and gender=1 order by age desc;  --查询年龄18-34男性 并按照年龄从大到小排序。
  --order by 多字段(按照第一个字段排，如果第一个字段相同按照第二个字段排，第二个字段相同按照第三个排。)
    select * from student where (age between 18 and 34 ) and gender=1 order by age desc, hight desc; 

--聚合查询
  --count 总数
    select count(*) as "男生人数" fron student where gender=1;  
  
  --max 最大值
    select max(age) from students;
    
  --min 最小值
    select min(hight) from students;
    
  --sun 求和
    select sun(age) from students;
    
  --avg 平均值
    select avg(age) from students;

--分页
  limit
  --限制查询显示个数
    select * from student limit 5;  --只显示5个
  
  limit start, count;
  --从第几个开始显示几个
    select * from studnet limit 0,5;  --显示从第一个开始5个

  规律：limit (第n页-1)*每个的个数, 每页的个数;
        limit一定要在整个语句的最后；
        limit 2*(6-1), 2; 这种写法不对；

--连接查询
mysql有三种类型的连接查询:内连接，左连接，右连接。
内连接:
  --inner join...on
    --取出A表中的每个数据和B表中的每个数据匹配一遍。
      select ... from 表A inner join 表B;

    --查询对应的学生及班级
      select * from student inner join classes on students.cls_id=classes.id;
    
    --显示对应学生的班级姓名
      select student.name, classes.name from student inner classes on student.cls_id=classes.id;
      select s.name, c.name from student as s inner join classes as c on s.cls_id=c.id;
      
左连接:
  --left join...on
  以left join左边的表为基准查询，若右边表中有则显示，没有显示为NULL
    --查询每位学生及对应班级信息
      select * from student as s left join classes as c on s.cls_id=c.id;
    
    --查询没有对应班级信息的学生
      select * from student as s left join classes as c on s.cls_id=c.id having c.id is null;
      
右连接：
  --right join... on 
  一般不用，相当于左连接中letf join 左右两边调换位置，所以一般用左连接。
  

自关联
  --一个表中的一个字段，关联另一个字段；（比如：省、市、县）
  --查询出山东省中有哪些市  表名areas 名字atitle 上一级pid 
    select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";
    
子查询
  --查询身高最高的学生
    select * from student where hight=(select max(hight) from student);

练习
  --求所有电脑产品的平均价格并保留两位小数
    select round(avg(price), 2) as avg_price from goods;
  --计算每一种价格的平均价格
    --group by 分组之后再去操作是对分组之后的各组进行操作，而不是对数据表操作
      select cate_name, avg(price) from goods group by cate_name;
     
  --显示每一种商品中各商品最贵价，最低价，平均价和数量
    select cate_name, max(price), min(price), avg(price), count(*) from goods group by cate_name;

  --显示价格高于平均值的商品
    select * from goods where price>(select round(avg(price), 2) as avg_price from goods) order by price desc;

  --显示出各产品价格最高的产品的详细信息
    select * from (select cate_name,max(price) as max_price from goods group by cate_name) as new_g left join goods as g on new_g.cate_name=g.cate_name and new_g.max_price=g.price;
  
  --将分组的结果写入到goods_cates数据表中
    insert into goods_cates (name) select cate_name from goods group by cate_name;
  
  --update...set 更新数据
    update goods set cate_nmae = 401 where cate_name = "台式机";
  --update...set 同步表数据
    update goods as g inner join goods_cates as c on g.cate_name = c.name set g.cate_name=c.id;