MySQL相关知识整理二

四大语句：增删改查

    CURD:create update retrieve delete

    插入：新增

    insert [into] 表名[(字段名,字段名...)] values(值1,值2);

        如果省略字段列表，那么必须为所有字段一次赋值；字段值的数量和数据类型以及顺序必须与字段完全相同；

        必须为设置为非空且没有默认值的字段赋值。

        insert user(name,sex,create_time)values('张三',1,1522312350);

        insert user values(null,'李四',22,2,1524564520);

    修改：

    update 表名 set 字段名=值[,字段名=值] [where条件];//如果省略where条件，将更新全部记录

        update user set age=23,name='张小三' where id=1;

    删除：

    delete from 表名 [where条件];//如果省略where条件，将删除全部数据记录

    查询：

    select 字段名 [ [as] 别名],字段名.... from 表名[where条件];

    1.查询所有字段的话，可以用*表示

    [where条件]：

        [where 条件] [group by 字段 [having 条件]] [order by 字段[ASC升序|DESC降序]][limit [start,] row_counts]

        where 条件：一般出现在修改，删除，查询where条件中，一般是以字段进行条件判断。

        group by:根据字段进行分组。

        having 条件:在分组基础上进行的条件判断，保证字段名称出现在分组中或出现聚合函数中。

        order by:指定字段进行排序

        limit:限制返回的记录条数，start指定从下标为(start)的数据记录开始返回（记录从0开始编号）,row_counts指定返回多少条记录。

条件：

    1.运算符：算术运算符：+-*/%

           字符运算符：[not] like '_%';（_通配一个字符，%通配0个或多个字符）

                 lower,upper,concat

            比较运算符：>、<、>=、<=、=、 [not] between and 、 [not] in(..,..,..)

            逻辑运算符：and or not

  2.聚合函数

        count()计数           sum()求和      avg()求平均值

            max()最大值       min()最小值

    例子：

        select * from user order by id DESC;

        select age ,count(*) from user group byage;

        select age ,count(*) from user group byage having age>25;

        select age ,count(*) from user group byage having count(*)>2;

        select * from user limit 2;

        select * from user limit 2,2;

       

        select * from user where name like '张__';

        select * from user where name like '张_';

        select * from user where name like '张%';

        select * from user where id>1 andid<4;

        select * from user where id between 2and 3;

        select * from user where id in(2,3);

 

 

MYSQL：多表查询

1.内连接

    select * from 表1 as 别名,表2,表3 where 意义相同的字段相等;

    select * from 表1 inner join 表2 on 意义相同的字段相等 inner join 表3 on 意义相同的字段相等;

    SELECT g.`gid`,g.`name` ASgrade_name,b.`bid`,b.`name` banji_name FROM grade AS g,banji b WHEREg.`gid`=b.`gid`;

2.外连接

    a.左外连接

    select * from 表1 left join 表2 on 意义相同的字段相等;

    b.右外连接

    select * from 表1 right join 表2 on 意义相同的字段相等;

   

    ps:1左外连接：以左表为主，右表为副；左表的所有数据记录全部显示，右表只显示相对应的数据记录；如果右表中没有对应的数据记录，将显示null

       2左外连接：以右表为主，左表为副；右表的所有数据记录全部显示，左表只显示相对应的数据记录；如果左表中没有对应的数据记录，将显示null

      

       SELECT g.`gid`,g.`name` ASgrade_name,b.`bid`,b.`name` banji_name FROM grade g LEFT JOIN banji b ONg.gid=b.gid;

SELECT g.`gid`,g.`name` ASgrade_name,b.`bid`,b.`name` banji_name FROM grade g RIGHT JOIN banji b ONg.gid=b.gid;

 

3.子查询（嵌套查询）：在一个sql语句中包含了另外一个查询语句

    a.子查询出现在查询语句中的条件中

       select * from 表1 where 字段=(select 字段 from 表2 where 条件);

        SELECT g.`gid`,g.`name` ASgrade_name  FROM grade g WHERE g.gidIN(SELECT b.gid FROM banji b);

    b.子查询出现在删除语句的条件中

    c.子查询出现在修改语句的条件中

    d.子查询出现在查询语句的字段位置，从当字段

        SELECT (SELECT b.name AS banji_nameFROM banji b WHERE b.bid=2),g.gid FROM grade g;

4.联合查询:多张表的表结构必须一致

    select * from 表1 union select * from 表2;//去掉重复记录

    select * from 表1 union all select * from 表2;//不会去掉重复记录

   

练习：1.查询输出所有学生信息：学号，姓名，班级号，班级名称，年级号，年级名称

       SELECT * FROM student,mark WHEREmark.studentid=student.studentid;

    2.查询输出所有学生的成绩：学号，姓名，班级名称，各科成绩

        SELECTstudent.`name`,student.studentid,banji.banjiname,mark.`chinese`,math,english,lizong,mark.`type`FROM student,mark,banji WHERE student.`studentid`=mark.`studentid` ANDbanji.`banjiid`=student.`banjiid`;

    3.

   

    SELECT s.`xuehao`,s.`name` AS s_name,b.`name` AS b_name, n.`name` AS n_nameFROM students s,banji b, nianji n WHERE s.bid=b.id AND b.nid=n.nid;

SELECT* FROM chengji c,students s WHERE s.`bid`=2 AND c.`type`=2 ANDc.`xuehao`=s.`xuehao`;

 

CREATEVIEW students_banji_nianji AS SELECT s.`xuehao`,s.`name` AS s_name,b.`name` AS b_name, n.`name` AS n_nameFROM students s,banji b, nianji n WHERE s.bid=b.id AND b.nid=n.nid;

SELECT* FROM students_banji_nianji;

 

SELECTb.name,COUNT(*) FROM banji b ,students s WHERE b.id=s.bid GROUP BY b.id;

SELECT*,(yuwen+shuxue+yingyu+tiyu) AS score FROM chengji c,students s WHERE s.`bid`=2AND c.`type`=2 AND c.`xuehao`=s.`xuehao` ORDER BY score DESC,yuwen DESC,shuxueDESC;

SELECTb.name,AVG(c.`yuwen`) AS yuwen_avg FROM chengji c,students s,banji b WHERE b.idIN(2,6) AND s.`bid` = b.id AND c.`xuehao`=s.`xuehao` GROUP BY b.id;