Mysql高级操作

一.新增数据

          1.基本语法：insert into 表名(字段列表) values(值列表)；

           2.主键冲突：可以（duplicate)进行选择性处理：更新或替换

                    1)：更新操作: insert into 表名(字段列表:包含主键) values(值列表) on duplicate key update 字段=值；

                           insert into test3 values(1,'steven','female') on  duplicate key update sex='female';

                    2)：替换操作：replace into 表名(字段列表：包含主键) values（值列表 )；

                           replace into test3 values(1,'candy','female');

            3.蠕虫复制：

                    1)：从已有的表中获取数据并将数据进行新增操作，从已有表中创建新表(复制表结构)

                           create table 表名 like database.table;         create table my_copy like test3;

                     2)：复制表中的数据

                           insert into 表名(字段列表) select * from 表名;     insert into my_copy select * from test3;

                     3)：蠕虫复制的意义

                            1.重已有表中复制数据

                            2.使表膨胀到一定的数量级，测试表的压力及效率。

二.更新&删除数据

            1.基本语法：update 表名 set 字段=值 [where条件][limit 更新数量];

                update my_copy set name='mary' where name='candy' limit 10;

            2.基本语法：delete from 表名 [where条件][limit更新数量];

                delete from my_copy where id=1 limit 4;

            3.清空表重置自增长：truncate 表名;

三.查询语句

            1.基本语法：select * from 表名；

            2.完整语法：select [select选项]  字段列表 [别名] from 数据源   [where子句] [group by子句] [having 子句]

              [order by] [limit子句];

            3.具体参数解释

                    1.select选项：1)：查询出来的结果的处理方式 All 默认，保留所有字段  

                                          2)： distinct将查询出来的结果去掉重复的(所有字段都相同)

                                         select distinct * from my_copy;

                    2.字段别名：多张表联合查询可能会出现重名字段，这时需要起别名进行区分

                                        select id 序号,name 姓名,sex 性别 from my_copy;

                    3.数据源：关系型数据库的数据源都是数据表

                                    1.多数据源查询  select * from my_class,test3;

                                    2.从一张表中取出一条记录对应另一张表的所有条记录，全部保留(字段和记录数)，这种结果叫笛卡尔积(交叉连接)

                                    3.子查询：一条查询记录作为另一条查询记录的数据源

                                       select * from (select * from student) as stu; as指的是数据源的别名

                    4.where子句：用来判断筛选数据，where唯一从磁盘读取数据就开始就行判断，判断成功保存到内存，失败就放弃。

                                    1.判断条件

                                           1)：比较运算符：>，<,=，!=，>=，<=，like，between and，in，not in

                                           2)：逻辑运算符：&&(and)，II(or)，!(not)

                                           3):查找id为1，3，5的学生

                                               select * from student where id in(1,3,5);

                                               select * from student where id=1 || id=3 ||id=5;

                                           4):查找height在某一区间

                                               select * from student where height between 109 and 168;

                                                   select * from student where height>=109 && height<=168 ;    

                      5. group by子句：根据某个字段分组(相同的分一组) 

                                            1.基本语法：select * from 表名 [where子句]  [group by子句];

                                            2.分组的意义：为了统计数据(按住统计：按分组字段进行数据统计)

                                            3. Sql提供的统计函数

                                                       1)：count()统计分组后的记录数，每组有多少条记录。

                                                                    1.count(*)代表统计所有记录

                                                                    2.count(字段名)：代表统计的字段(空字段不统计)

                                                       2)：max()统计每组中的最大值

                                                       3)：min()统计每组中的最小值

                                                       4)：avg()统计每组中的平均值

                                                       5) ：sum()统计和

                                             4.具体应用：

                                                       1)单字段分组:select sex,count(*),max(height),min(height),avg(age),min(age) from student group by sex;

                                                        2)多字段分组:select uid,sex,count(*) from student group by uid,sex;

                           6.having子句：where是针对磁盘数据进行判断，进入内存之后，会进行分组操作，分组操作就需要having，having能使用别名

                                           1.求出班级所有人数大于2的学生人数：select uid,name,count(*) from student group by uid having count(*)>=2;

                                           2.求出班级所有人数大于2的学生人数(使用别名)：

                                          select uid,name,count(*) as total from student group by uid having total>=2;

                       7.order by子句：select * from student group by uid，sex desc;先uid 升序在sex 降序

                      8.limit子句：限制数量

                                       1.只用来限制数量：select * from student limit 2;

                                       2.限制起始位置，限制数量：select  * from student 3,2;