MySql学习小结 2016-01-04

最近参加了一个面试，最后面试官叫我写出一个内链接查询的语句（查询出每个班级有多少学生？）我竟然写不出来，两年经验瞬间归零，十分尴尬。所以总结前人的经验和我的学习，将MySql的所有常用知识点总结出来，作为初学者，有的地方理解不深刻或不准确，还请大家多多指教。

言归正传：

    1.关于MySql数据库：是关系型的数据库（保存的实体与实体之间的关系）它是开源的，被Oracle收购了，小型的数据库。5.x版本免费，6.x收费了。（据说淘宝用的就是MySql）

    2. SQL简介：（Structured Query Language）结构化查询语言，非过程性的语言（过程性的语言：我下一条语句，需要依赖上一条或者上几条语句）： 写一条 语句，就会执行一个结果。

                 在开发中我们多数情况下使用的是对表的CRUD操作，所以对数据库的CRUD我们此处略去250个字儿...

    2.1 表的CRUD：

                     2.1.1创建表：create table 表名(
字段1 类型(长度) 约束,
字段2 类型(长度) 约束,
字段3 类型(长度) 约束,
字段4 类型(长度) 约束
);

                                 注：       ① 表名小括号，后面要有分号。
②每一行字段后面要有逗号，但是最后一行没有逗号。
③数据的类型后面有长度，如果是字符串类型，长度必须加。如果其他类型可以不加。默认长度。int 默认长度11；
                                                ④varchar和char区别：

* varchar（经常使用） 长度是可变的。 name varchar(8) 存入数据hello，但是如果存入helloworld报错了。

* char长度不可变的。name char(8) 存入的数据hello，如果不够用空格补全。

* 效率高：char效果。

                    2.1.2添加数据：

                                   方式一： insert into 表名 (字段1,字段2,字段3..) values(值1,值2,值3...); 有几列就插入多少的值。
           方式二：insert into 表名 values(值1,值2,值3...); 插入所有的列；
  注意：
① 数据与字段的类型相同；
② 字段长度需要控制；

③字符串或者日期类型需要使用''

                     2.1.3  修改语句
   语法： update 表名 set 字段=值,字段=值... [where ]；
        如果没有where条件，默认更新所有的记录；
        有where提交，选择某一条记录。

                     2.1.4  删除数据：

                                          方式一：  delete from 表名 [where ]; 删除数据
                  方式二： truncate 表名; 删除所有的数据

                  truncate 和 delete的区别：
                            truncate删除数据，先删除整个表。再创建一个新的空的表。（效率）
                           delete删除数据，一条一条删除的。
     

                       2.1.5 查询语句：（单表）

                                                方式一：select * from 表名;查询所有（字段）；
方式二：select 字段名1,字段名2,字段名3 from 表名;显示查询字段名；
方式三：select DISTINCT 字段名 from 表名; 去除重复的数据。

                               Ⅰ. 常用的符号：
>   <   <=   >=   =    <>（不等于）
in(范围内取内容)
like -- 模糊查询 写法：like '张_或者%'; _和%区别：占位符。_只一个%可以有多个
%的写法 like '%张'; 结果XXX张
like '张%'; 结果张XXX
like '%张%'; 只要有张就行
is null -- 判断是否为null
and -- 并且
or -- 或者
not -- 不成立
       例句：

                                         查询英语分数在 80－90之间的同学。
select * from stu where english >80 and english <90;
select * from stu where english between 80 and 90;

查询数学分数为18,78,46的同学。（in）
select * from stu where math in(18,78,46);

查询所有姓班的学生成绩。
select * from stu where name like '班%';
查询数学分>80，语文分>80的同学。
select * from stu where math >80 or chinese > 80;

Ⅱ. 排序 使用order by 升序默认的(asc)/降序(desc)
* 出现select的语句末尾。
例句：
对数学成绩排序后输出。
select name,math from stu order by math;
对总分排序按从高到低的顺序输出
select name,math+english+chinese from stu order by (math+english+chinese) desc;
对学生成绩按照英语进行降序排序，英语相同学员按照数学降序
select * from stu order by english desc,math desc;
对姓美的学生成绩排序输出
select * from stu where name like '美%' order by english desc;

                            

                       Ⅲ. 聚集函数
* count 获取数量
练习：
统计一个班级共有多少学生？
select count(*) from stu;
统计数学成绩大于90的学生有多少个？
select count(*) from stu where math > 90;
统计总分大于150的人数有多少？
select count(*) from stu where (math+english+chinese) > 150;

* sum 求和（忽略null值） 可以同ifnull(xxx,0)
统计一个班级数学总成绩？
select sum(math) from stu;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(english),sum(chinese) from stu;
统计一个班级语文、英语、数学的成绩总和
select sum(ifnull(math,0)+english+chinese) from stu;
select sum(math)+sum(english)+sum(chinese) from stu;

统计一个班级语文成绩平均分
select sum(chinese) / count(*) from stu;

* avg 平均数
练习：
求一个班级数学平均分？
select avg(math) from stu;
求一个班级总分平均分
select avg(ifnull(math,0)+english+chinese) from stu;

* max 最大值
select max(math) from stu;
* min 最小值
select min(math) from stu;


* group by 分组（一起使用） 条件过滤需要是having，不能使用where
练习：对订单表中商品归类后，显示每一类商品的总价.
select product,count(*),sum(price) from orders group by product;

练习：查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price) > 100;


                      整合语句 ： S-F-W-G-H-O 组合 select ... from ... where ... group by... having... order by ... ; 
顺序不能改变。

                          2.1.6多表查询：

                内连接（用的比较多）

  普通内连接

          前提条件：需要有外键的。

  提交关键字 inner join ... on

select * from dept inner join emp on dept.did = emp.dno;

                               例句：查询每个部门有多少员工？(e_did：为emp表的外键，为dept对应的主键)

                                select count(e_did) ,d_name  from dept inner join emp on dept.d_id = emp.e_did group by d_name;    

 隐式内连接（用的是最多的）

 可以不使用inner join ... on关键字

select * from dept,emp where dept.did = emp.dno;

外连接

 左外链接（看左表，把左表所有的数据全部查询出来）

前提条件：需要有外键的。

        语法： 使用关键字 left [outer] join ... on

select * from dept left outer join emp on dept.did = emp.dno;

右外链接（看右表，把右表所有的数据全部查询出来）

前提条件：需要有外键的。

语法： 使用关键字 right [outer] join ... on

select * from dept right join emp on dept.did = emp.dno;

子查询

       查询的内容需要另一个查询的结果。

select * from emp where ename > (select * from emp where 条件);

any 任意

all 全部

>any 大于结果的最小值

>all 大于结果的最大值