mysql——前面内容——前期整理笔记00

客官°小女子只卖身不卖艺 2023-10-02 08:51 4阅读 0赞

create table student(  sid varchar(50),
                           sname varchar(50),
                           sage  varchar(50),
                           ssex  varchar(50)
                        );
    
    insert into student( sid,sname,sage,ssex ) values('1','zhaolei','1990-01-01','nan');
    
    insert into student values('2','qiandian','1990-12-21','nan');
    insert into student values('3','sunfeng','1990-05-20','nan');
    insert into student values('4','liyun','1990-08-06','nan');
    
    insert into student values('5','zhoumei','1991-12-01','nv'),
                              ('6','wulan','1992-03-01','nv'),
                              ('7','zhenzu','1989-07-01','nv'),
                              ('8','wangju','1990-01-20','nv');
    
    select * from student;
    
    select sid,sname,sage,ssex from student;
    
    select sid,sname,ssex from student;
    
    =============================================================================================
    
    
    1、修改表名
    
    语法格式：alter table 旧表名 rename [to] 新表名;
    
    注释：修改后example1表就不存在了，只存在名为user的新表，但是其内容是一致的，只是换了个名称.
    
    alter table student rename to sww;
    
    select * from student;
    
    select * from sww;
    
    alter table sww rename to student;
    
    =====================================================================================================
    2、修改字段名
    
    语法格式：alter table 表名 change 旧属性名 新属性名 新数据类型;
    
    注释：新数据类型指修改后的数据类型，如不需要修改，则将新数据类型设置成与原来一样
    
    
    
    alter table student change sname sww varchar(50);
    
    select * from student;
    
    alter table student change sww sname varchar(50);
    
    
    ==============================================================================================
    
    
    3、修改字段的数据类型
    
    语法格式：alter table 表名 modify 属性名 数据类型;
    
    注释：表名指所要修改数据类型的字段的表的名称；
      
          属性名指：所要修改数据类型字段的名称；
    
          数据类型指：修改后的新的数据类型
    
    =========================================================================================================
    4、修改字段的排列位置
    
    语法格式：alter table 表名 modify 属性名1 数据类型 first|after 属性名2;
    
    alter table  student modify sage varchar(50) first;
    
    select * from student;
    
    alter table  student modify sage varchar(50) after sname;
    
    ======================================================================================
    
    
    5、增加字段
    
    语法格式：alter table 表名 add 属性名1 数据类型 [完整性约束条件] [first | after 属性名2];
    
    
    完整性约束条件：是可选参数，用来设置新增字段的完整性约束条件
    
    first：是可选参数，其作用是将新增字段设置为表的第一个字的
    
    after：是可选参数，其作用是将新增字段添加到“属性名2”所指的字段后
    
    如果执行的SQL语句中没有“first”或者“after 属性名2”参数指定新增字段的位置，则新增字段默认为表的最后一个字段
    
    
    alter table student add saddress varchar(50) after ssex;
    
    
    select * from student;
    
    ==============================================================================================================
    
    6、删除字段
    
    删除字段是删除表中已经定义好的表中的某个字段,删除后其字段所属的数据都会被删除
    
    语法格式：alter table 表名 drop 属性名;
    
    alter table student drop saddress;
    
    select * from student;
    
    ==========================================================================================================
    
    select * from student;
    
    select * from student where sid in('1','3','5','7');
    
    select * from student where sid not in('1','3','5','7');
    
    select * from student where  sid > 4;
    
    select * from student where  sid != 4;
    
    select * from student where sid between 4 and 6;
    
    select * from student where sid not between 4 and 6;
    
    select * from student where sname like '%n%';
    
    select * from student where sname not like '%n%';
    
    =====================================================================================================
    
    select * from student;
    
    select * from student order by sid desc;
    
    select count(*) from student;
    
    select sum(sid) from student;
    
    select avg(sid) from student;
    
    select max(sid) from student;
    
    select min(sid) from student;
    
    select count(*) from student group by ssex;
    
    select sid as a, sname as b,sage as c, ssex as d from student;
    
    select * from student limit 3;
    
    select * from student limit 1,2;
    
    
    ====================================================================================
    增加一个字段，插入数据，并更改数据
    
    alter table student add saddress varchar(50) after ssex;
    
    select * from student;
    
    update student set saddress = 'jiangsu' where sid = '1';
    update student set saddress = 'jiangsu' where sid = '2';
    
    update student set saddress = 'shanghai' where sid = '3';
    update student set saddress = 'shanghai' where sid = '4';
    update student set saddress = 'shanghai' where sid = '5';
    
    update student set saddress = 'beijing' where sid = '6';
    update student set saddress = 'beijing' where sid = '7';
    
    update student set saddress = 'anhui' where sid = '8';
    
    insert into student ( sid,sname,sage,ssex ) values ('9','shenweiwei','1989-10-18','nan');
    
    update student set saddress = 'jiangsu' where sid = '9';
    
    
    
    一、将查询结果插入到表中
    
    
    insert语句可以将一个表中查询到的数据插入到另外一个表中
    
    
    语法格式：
    
    insert into 表名1 (属性列表1) select 属性列表2 from 表名2 where 条件表达式;
    
    
    
    表名1说明记录插入到哪个表中；
    
    表名2表示记录是从哪个表中查询出来的；
    
    属性列表1参数表示为哪些字段赋值；
    
    属性列表2表示从表中查询出哪些字段的数据；
    
    条件表达式参数设置了select语句的查询条件；
    
    
    
    注意：使用这种方法时，必须保证属性列表1和属性列表2中的字段个数是一样的，而且每个对应字段的数据类型必须是一样的。
    
    
    create table student2(  sid varchar(50),
                           sname varchar(50),
                           sage  varchar(50),
                           ssex  varchar(50),
                           saddress varchar(50)
                        );
    
    select * from student2;
    delete from student2;
    insert into student2 ( sid,sname,sage,ssex,saddress ) values ('11','fenglili2','1988-09-18','nv','jiangsu');
    insert into student2 ( sid,sname,sage,ssex,saddress ) values ('12','fenglili3','1988-08-18','nv','jiangsu');
    insert into student2 ( sid,sname,sage,ssex,saddress ) values ('13','fenglili4','1988-07-18','nv','shanghai');
    
    
    insert into student ( sid,sname,sage,ssex,saddress ) values ('11','fenglili2','1988-09-18','nv','jiangsu');
    insert into student ( sid,sname,sage,ssex,saddress ) values ('12','fenglili3','1988-08-18','nv','jiangsu');
    insert into student ( sid,sname,sage,ssex,saddress ) values ('13','fenglili4','1988-07-18','nv','shanghai');
    delete from student where sid in('11','12','13');
    select * from student;
    
    insert into student(sid,sname,sage,ssex) select sid,sname,sage,ssex from student2 where sid = '11';
    
    select * from student;
    
    insert into student(sid,sname,sage,ssex,saddress) select sid,sname,sage,ssex,saddress from student2 where sid in ('12','13');
    
    select * from student;
    
    update student set saddress = 'jiangsu' where saddress is null;
    
    select * from student;
    
    ================================================================================================================================
    一、基本查询语句
    
    select的基本语法格式如下：
    
    select 属性列表 from 表名和视图列表
    
    [ where 条件表达式1 ]
    
    [ group by 属性名1 [ having 条件表达式2 ] ]
    
    [ order by 属性名2 [ asc | desc ] ]
    
    
    属性列表参数表示需要查询的字段名；
    
    表名和视图列表参数表示从此处指定的表或者视图中查询数据，表和视图可以有多个；
    
    条件表达式1参数指定查询条件；
    
    属性名1参数指按照该字段的数据进行分组；
    
    条件表达式2参数满足该表达式的数据才能输出；
    
    属性名2参数指按照该字段中的数据进行排序；排序方式由asc和desc这两个参数指出；
    
    asc参数表示升序，这是默认参数，desc表示降序；(升序表示从小到大)
    
    对记录没有指定是asc或者desc，默认情况下是asc；
    
    
    
    如果有where子句，就按照“条件表达式1”指定的条件进行查询；如果没有where子句，就查询所有记录；
    
    
    
    
    如果有group by子句，就按照“属性名1”指定的字段进行分组，如果group by后面带having关键字，那么只有
    
    满足“条件表达式2”中知道的条件才能输出。group by子句通常和count()、sum()等聚合函数一起使用；
    
    
    
    
    如果有order by子句，就按照“属性名2”指定的字段进行排序，排序方式由asc和desc两个参数指出；默认情况下是asc；
    
    
    查询结果不重复：distinct 关键字
    
    select * from student;
    
    select distinct ssex from student;
    
    select distinct saddress from student;
    
    
    分组查询：
    
    select * from student group by ssex;
    
    select * from student group by saddress;
    
    
    group by 关键字与group_concat()函数一起使用，每个分组中指定字段值都显示出来：
    
    select ssex,GROUP_CONCAT(ssex) from student group by ssex;
    select ssex,GROUP_CONCAT(sname) from student group by ssex;
    select saddress,GROUP_CONCAT(sname) from student group by saddress;
    
    
    group by 关键字与集合函数一起使用：
    
    select ssex,count(ssex) from student group by ssex;
    
    select saddress,count(saddress) from student group by saddress;
    
    
    
    group by 关键字与"having 条件表达式"一起使用，可以限制输出结果，只有满足条件表达式的结果才会显示：
    
    
    select ssex,count(ssex) from student group by ssex;
    select ssex,count(ssex) from student group by ssex having count(ssex) >6;
    
    
    select saddress,count(saddress) from student group by saddress;
    select saddress,count(saddress) from student group by saddress having count(saddress) >3;
    
    
    注意：“having 表达式” 与 "where 表达式"都是用来限制显示的，但是两者起作用的地方不一样；
    
    
          "where 表达式"用于表或者视图，是表和视图的查询条件；
    
          “having 表达式”作用于分组后的记录，用于选择满足条件的分组。
    
    
    group by 关键字与 with rollup 一起使用，会在所有记录的最后加上一条记录，这条记录是上面所有记录的总和。
    
    
    select ssex,count(ssex) from student group by ssex;
    select ssex,count(ssex) from student group by ssex with rollup;
    
    
    
    select saddress,count(saddress) from student group by saddress;
    select saddress,count(saddress) from student group by saddress with rollup;
    
    select * from student;
    
    select * from student limit 4;
    select * from student limit 1,5;
    
    =======================================================================================================
    
    create table score ( xh int(50),
                         km varchar(50),
                         cj int(50)
                       );
    
    select * from score;
    
    insert into score values(1,'shuxue',80);
    insert into score values(1,'yuwen',70);
    insert into score values(1,'yingyu',40);
    
    insert into score values(2,'shuxue',40);
    insert into score values(2,'yuwen',60);
    insert into score values(2,'yingyu',50);
    
    insert into score values(3,'shuxue',60);
    insert into score values(3,'yuwen',20);
    insert into score values(3,'yingyu',90);
    
    insert into score values(4,'shuxue',50);
    insert into score values(4,'yuwen',60);
    insert into score values(4,'yingyu',70);
    
    select xh,sum(cj) from score where xh = 1;   查询此同学的总成绩；
    select xh,sum(cj) from score where xh = 4;
    
    select xh,sum(cj) from score group by xh;     查询每一个同学的各科总和成绩；
    
    select km,max(cj) from score group by km;      查询各个科目的最高成绩；
    
    select km,avg(cj) from score group by km;   查询每一科目的平均成绩；
    
    select km,max(cj) from score group by km;    查询每一科目的最高成绩；
    
    select km,min(cj) from score group by km;    查询每一科目的最低成绩；
    =======================================================================================================================
    
    
    create table employee ( num int(50),
                            d_id int(50),
                            name varchar(50),
                            age int(50),
                            sex varchar(50),
                            homeadd varchar(50)
                           );
    
    insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
    insert into employee values(2,1001,'lisi',24,'nv','hunan');
    insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
    insert into employee values(4,1004,'aric',15,'nan','yingguo');
    
    select * from employee;
    
    create table department ( d_id int(50),
                              d_name varchar(50),
                              functione varchar(50),
                              address varchar(50)
                            );
    
    insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
    insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
    insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
    
    select * from department;
    
    select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id;
    
    
    内连接查询：可以查询两个或者两个以上的表，当两个表中存在表示相同意义的字段时，可以通过该字段来连接这两个表；
    
                 当该字段的值相等时，就查询出该记录。
    
    ======================================================================================================================
    
    外连接查询：可以查询两个或者两个以上的表，外连接查询也需要通过指定字段来进行连。
    
                当该字段取值相等时，可以查询出该记。
    
                而且该字段取值不相等的记录也可以查询出来。
    
    外连接包括：左连接、右连接
    
    语法：
    
            select 属性列表 from 表名1 left | right join 表名2 on 表名1.属性名1 = 表名2.属性名2；
    
    属性列表表示要查询的字段的名称，这些字段可以来自不同的表；
    
    Left表示左连接查询；
    
    rigth表示右连接查询；
    
    on后面接的是连接的条件；
    
    
    1、左连接查询
    
    进行左连接查询时，可以查询出表名1所指的表中的所有记录。而表名2所指的表中，只能查询出匹配的记录
    
    select * from employee;
    select * from department;
    
    select num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id = department.d_id;
    
    
    2、右连接查询
    
    进行右连接查询时，可以查询出表名2所指的表中的所有记录。而表名1所指的表中，只能查询出匹配的记录
    
    select * from employee;
    select * from department;
    
    select num,name,employee.d_id,age,sex,d_name,functione from employee right join department on employee.d_id = department.d_id;
    
    
    复合条件查询
    
    在连接查询时，通过增加其他的限制条件，可以使查询结果更加准确
    
    select * from employee;
    select * from department;
    
    select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id;
    
    select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id and age > 24;
    
    select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id order by age asc;
    
    select 语句先按照内连接的方式从employee和department表中查询出数据。然后查询结果按照age字段从小到大的顺序进行排序。
    
    =================================================================================================================================================
    
    子查询
    
    子查询是将一个查询语句嵌套在另外一个查询语句中，内层查询语句的查询结果，可以作为外来层查询语句提供查询条件。
    
    1、带in关键词的子查询
    
    select * from employee;
    select * from department;
    
    select * from employee where d_id in (select d_id from department );
    
    select * from employee where d_id not in (select d_id from department );
    
    
    
    
    
    2、带exists关键字的子查询
    
    exists关键字表示存在，使用exists关键字时，内层查询语句不用返回查询的记录。而是返回一个真假值。
    
    如果内层查询语句查询到满足条件的记录，就返回一个真值(true)；否则，返回一个假值(false);
    
    当返回值为true时，外层查询语句将进行查询；而返回false时，外层查询语句不进行查询或者查询不出任何记录。
    
    
    select * from employee;
    select * from department;
    
    select * from employee where exists (select d_name from department where d_id = 1003);
    
    select * from employee where exists (select d_name from department where d_id = 1004);
    
    
    exists关键字可以与其他查询条件一起使用。条件表达式与exists关键字之间用and或者or来连接。
    
    select * from employee where age > 24 and exists (select d_name from department where d_id = 1003);
    
    select * from employee where age > 24 and exists (select d_name from department where d_id = 1004);
    
    
    not exists与exists相反。
    
    select * from employee where age > 24 and not exists (select d_name from department where d_id = 1003);
    
    select * from employee where age > 24 and not exists (select d_name from department where d_id = 1004);
    
    
    
    3、带比较运算符的子查询
    
    
    create table schoarship ( levela int(50),
                              score int(50)
                             );
    
    insert into schoarship(levela,score) values(1,90);
    insert into schoarship values(2,80);
    insert into schoarship values(3,70);
    
    select * from schoarship;
    
    create table computer_stu (  id int(50),
                                 name varchar(50),
                                 score int(50)
                               );
    
    insert into computer_stu(id,name,score) values (1001,'lily',85);
    
    insert into computer_stu(id,name,score) values (1002,'tom',91),
                                                   (1003,'jim',87),
                                                   (1004,'aric',77),
                                                   (1005,'lucy',65),
                                                   (1006,'andy',99),
                                                   (1007,'ada',85),
                                                   (1008,'jeck',70);
    
    select * from computer_stu;
    
    
    select id,name,score from computer_stu where score >= (select score from schoarship where levela = 1);
    
    /* 查询获得一等奖学金的学生有哪些，第一个表为奖学金等级和最低分数*/
    
    
    
    select d_id,d_name from department where d_id != (select d_id from employee where age = 24);
    
    /*只有生产部和销售部没有年龄等于24岁的员工*/
    
    
    
    4、带any关键字的子查询
    
    
    any关键字表示满足其中任何一个条件。使用any关键字时，只要满足内查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句
    
    
    
    select * from computer_stu where score >= any ( select score from schoarship );
    
    /*结果显示，有7个人获得奖学金，只有id为1005的人没有，因为分数为65，不高于奖学金指定最低分数的任何一个*/
    
    
    5、带all关键字的子查询
    
    all关键字表示满足所有条件。使用all关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
    
    select * from computer_stu where score >= all ( select score from schoarship );
    
    /*结果显示，只有两个人获得奖学金。因为这两个人的分数比所有奖学金要求的分数都高*/
    
    
    ============================================================================================================================
    合并查询结果
    
    合并查询结果 是将多个select语句的查询结果合并到一起
    
    union关键字，数据库会将所有的查询结果合并到一起，然后除掉相同的记录；
    
    union all关键字，只是简单的合并到一起
    
    
    select d_id from department;
    
    
    select d_id from employee;
    
    
    
    select d_id from department union select d_id from employee;
    
    select d_id from department union all select d_id from employee;
    
    
    =====================================================================================================================
    
    为表和字段取别名
    
    1、语法：表名 表的别名
    
    select * from department d where d.d_id = 1001;
    
    
    2、语法：属性名 [as] 别名
    
    select d_id as department_id,d_name department_name from department;
    
    
    数据库中，可以同时为表和字段取别名
    
    
    select d_id as department_id,d_name department_name,d.functione,d.address from department d where d.d_id = 1001;
    
    =================================================================================================================================