第三章 高级查询（一）

1.修改表

    （1）修改表名

           语法：alter  table   旧表名   rename  [to]  新表名；

                     eg：alter   table  `demo` rename `demo1`;

     (2)添加字段

          语法：alter  table  表名   add  字段名   数据类型[属性];

                  eg:  alter  table `demo` add `password` varchar(50) not null;

    (3)修改字段

         语法： alter  table 表名  change  原字段名  新字段名  数据类型[属性]；

              eg：alter  table `demo1`change `name` `username` char(10) not  null;

    (4)删除字段

          语法： alter table  表名   drop 字段名

               eg： alter  table  `demo` drop `password`;

2.添加主键

    语法：alter table  表名 add  constraint  主键名  primary  key 表名（主键字段）；

            eg：alter  table  `student` add  constraint  pk_studentNo  primary key student  (studentNo);

3.添加外键

       语法： alter table  表名  add  constraint  外键名  foreign  key（外键字段)  references  关联表名（关联字段）；

            eg：alter  table `student` add  constraint  fk_gradeId_studentNo  foreign key(gradeId)  reference  result(studentNo);

   注意：

           （1）设置自增时，必须设置主键或唯一约束

           （2）设置外键时，关联表里边必须设置主键或唯一约束来才能连接

             (3)添加外键，数据类型，存储类型必须一致

4.DML——插入单条数据

         语法： insert  into  表名（字段列表）values（值列表）;

               eg:  insert  into `student`(`loginPwd`)values（'123456'）;

     注意：

               （1）字段名时可选的，如省略必须依次按照插入所有字段

               （2）多个列表和多个值之间用逗号隔开

               （3）值列表和字段名列表一一对应

               （4）如插入的是表中部分数据，字段名列表必填

   插入多条数据

          语法： insert  into  表名（字段列表）values（值1，值2）,(值1，值2)；

                eg： insert  into `subject`(`gradeId`,`classHour`)values(220,1),(160,1);

5.将查询结果插入新表

        （1）事先创建且与插入数据字段相符

             insert  into  新表（字段1，字段2）

             select   字段1，字段2 from  原表；

         （2）无需事先创建

                 create  table  新表

                 select（字段1，字段2  from  原表);

6.更新数据记录

     语法：update  表名  set 字段1=值1，字段2=值2  where  条件；

          eg：update   student  set  password='0000' where  studentNo=20000;

8.删除数据记录

     （1）delete  from   表名   where   条件；    注意：有外键不能清空

 

     （2）truncate  table   表名；

9.truncate和delete区别：

      （1）truncate语句删除后将重置自增列，而delete不会

      （2）两者的表结构及其字段，约束，索引保持不变

      （3）truncate速度比delete速度快

10.DQL——查询

       语法：

                select 字段列表  from  表名

                where  条件

                group  by分组的字段名

                having  条件

                order  by 排序的字段  ASC或DESC

                LIMIT 位置偏移量，行数；

    使用LIMIT子句时，注意第一条记录的位置是0

    位置偏移量是指从第几行开始    行数：显示几行

     LIMIT 4; 只会显示几行

    LIMIT公式： limit（n-1）*a,a

   如果行数>本身，则会显示全部

11.字符串函数

    （1）concat：字符串连接

            eg：

                     SELECT CONCAT('My','S','QL');               返回：MySQL

    （2）insert：替换

                eg：

                       SELECT INSERT( '这是SQL Server数据库', 3,10,'MySQL');         返回：这是MySQL数据库

   （3）uuper：大写

              eg：

                        SELECT UPPER('MySQL');        返回：MYSQL

   （4）lower：小写

               eg：                      

                          SELECT LOWER('MySQL');       返回：mysql

    （5）substring：截取

                 eg：                    

                           SELECT SUBSTRING( 'JavaMySQLOracle',5,5);       返回：MySQL

12.聚合函数

      (1)AVG():平均值 

    （2）Count（）：行数

    （3）Max（）：最大值

    （4）Min（）：最小值

    （5)Sum（）：和

13.时间日期函数

  （1）curdate（）：当前日期   

          eg：

               SELECT CURDATE();     返回：2016-08-08

  （2）curtime（）：当前时间

           eg：

                SELECT CURTIME();    返回：19:19:26

  （3）Now（）：时间和日期

              eg：

                 SELECT NOW();         返回：2016-08-08 19:19:26

   （4）Week（）：返回日期一年中第几周

              eg：

                SELECT WEEK(NOW());    返回：26

    （5）Year（）：返回date年份

                eg：

                   SELECT YEAR(NOW());     返回：2016

     （6）Hour（time）：返回time小时值

                eg：

                   SELECT HOUR(NOW());         返回：9

       （7）minute（）：分钟值

               eg：

                    SELECT MINUTE(NOW());   返回：43

       （8）datediff（date1，date2）：返回date1和date2相隔天数

                eg：

                       SELECT DATEDIFF(NOW(), '2008-8-8');      返回：2881

        （9）adddate：计算日期参考date加n天后日期       通常用于保质期

                     eg：

                      SELECT ADDDATE(NOW(),5);     返回：2016-09-02 09:37:07

14.数学函数

      （1）celt（x）：返回大于或等于数值x最小整数

                  eg：

                    SELECT CEIL(2.3)    返回：3

     （2）floor（x）：返回大于或等于数值x最大整数

                   eg：

                       SELECT FLOOR(2.3)    返回：2

     （3）rand（）：返回0~1间的随机数

                   eg：

                     SELECT RAND()       返回：0.5525468583708134

15.什么是子查询？

     （1）一个表的情况

              eg：查看年龄比李斯文小的学生，要求显示显示这些学生的信息

                        第一步：先查出李斯文的出生日期

                      select  bornDate  as  出生日期 from  student  where  studentName='李斯文';

                       第二步：用where筛选出比李斯文年龄小的学生

                  select  studentName，sex，bornDate，email  from student  where bornDate>'李斯文出生日期'

              子查询：

                           select  studentName，sex，bornDate，email  from student  where bornDate>( select                                       bornDate  as  出生日期 from  student  where  studentName='李斯文';)

      子查询是一个嵌套在 select，insert，update或delete语句或其他子查询中的查询

      子查询在where语句中的一般语法：

                             select .....from  表1 where  字段1   比较运算符（子查询）

       注意：将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

               执行顺序：先执行子查询，返回所有来自子查询的结果

                                 再执行外围的父查询，返回查询的最终结果

          （2）子查询替换表连接 (多个表)

                    eg：查询logic  java课程至少一次考试刚好等于60分的学生

                    方法一：   表连接

                                    select  studentName  from  Student

                                   inner  join   result  on  student.studentNo=result.studentNo

                                   inner  join  subject  on  subject.subjectNo=result.subjectNo

                                   where  studentResult=60  and subjectName='logic java';

                    方法二：子查询

                                select  studentName  from  Student  where  studentNo=(

                                   select  studentNo  from  result

                                    inner  join  subject  on  subject.subjectNo=result.subjectNo

                                    where  studentResult=60 and subjectName='logic java');         

                 子查询比较灵活，方便，常作为增删改查的筛选条件，适合于操纵一个表的数据

                  表连接更适合于查看多表的数据

16.IN查询

        eg1：查询logic java课程考试成绩为60分的学生名单

                select  studentName  from  student

                  where studentNo IN(

                   select studentNo  from  result

                    where subjectNo=( 

                    select  subjectNo  from  student

                     where  subjectName='logic java'

                    )and  studentResult=60);

           常用IN替换等于（=）的子查询

            IN后面的子查询可以返回多条记录

         eg2：查询参加logic java课程最近一次考试在读学生名单   

                第一步：获得logic  java课程的课程编号

                   select subjectNo from  subject  where  subjectName='logic java';

                第二步：根据课程编号查询到logic java课程最近一次的考试题日期

             select  max（examDate）from  result where subjectNo=（

             select subjectNo  from  subject  where subjectName='logic jaba'）;

             第三步：根据课程编号和最近一次考试日期查询出在读学生信息

           

            SELECT `studentNo`, `studentName` FROM `student` WHERE `studentNo IN (

           SELECT `studentNo` FROM `result` WHERE `subjectNo` IN (

           SELECT `subjectNo` FROM `subject` WHERE `subjectName`=' Logic Java' )

           AND `examDate` = ( SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (

          SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java ' ) ) );

17.NOT IN查询

           同上一样，只要把IN改为NOT IN就ok了！