31.MySQL数据库基础（2）

于 2021-12-15 17:14:00 发布
阅读量278 收藏
点赞数
分类专栏： Java基础 文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wuzj1314/article/details/121957037
版权

1.基本select语句

 

#创建数据表

create table t_person(

per_id int primary key auto_increment,

per_name varchar(20),

per_age int,

per_address varchar(20),

per_day datetime

);



insert into t_person values(null,'zhangsan',23,'西安','2021-12-01');

insert into t_person values(null,'lisi',24,'北京','2014-12-01');

insert into t_person values(null,'wangwu',25,'上海','2020-12-01');

insert into t_person values(null,'zhaosi',25,'深圳','2011-12-01');

insert into t_person values(null,'lisisi',26,'广州','2021-12-01');

insert into t_person values(null,'zhangsansan',24,'广州','2022-12-01');

insert into t_person values(null,'wangwuwu',23,'南昌','2021-11-01');

#1.查询所有

select*from t_person;

#2.查询指定列

select per_name,per_age from t_person;

#3.基本条件查询【= or and】

select*from t_person where per_name='lisisi';

select*from t_person where per_name='zhangsan'and per_age=23;

select*from t_person where per_name='lisi' or per_age=25;

#4.比较运算符查询

select*from t_person where per_age<25;

#5.区间查询 between 最小值 and 最大值

select*from t_person where per_day between '2014-11-2' and '2021-12-01';

#6.in(集合)条件查询

select*from t_person where per_age in(23,24,45,27);

#7.模糊查询

select*from t_person where per_name like '%wu%';

select*from t_person where per_name like '%si';

select*from t_person where per_name like 'wang%';

#8.null  not null

select*from t_person where per_name is null;

select*from t_person where per_name null;

#9.排序查询   order by 列名 asc[升序] desc[降序]

select*from t_person order by per_age asc;

select*from t_person order by per_name desc;

#10.SQL聚合函数查询

#count(列名/*)----行的总数【总记录数】

select count(*) from t_person;

select count(per_name) from t_person;

#sum(列名)----求和

select sum(per_age) from t_person;

#avg(列名)----求平均数

select avg(per_age) from t_person;

#max(列名)----求最大值

#min(列名)----求最小值

select max(per_age) from t_person;

select min(per_age) from t_person;

#11.分页查询  limit 参数1,参数2

#参数1----当前页的第一条记录的开始位置【（当前页码-1）*每页记录数】

#参数2----每页记录数

#假设查询第2页，每页只能显示2条记录

select*from t_person limit 2,2;

#12.分组查询 group by 列名[先分组，后查询统计]

#根据年龄分组统计每一组的人数

select per_age,count(*) from t_person group by per_age;

#13.别名查询

#数据库表可以有别名 【表名称 as 表的别名  /   表名称 表的别名】

select*from t_person as 用户表;

#列也可以有别名【列名称 as 列的别名 / 列名称 列的别名】

select per_age as 年龄,count(per_age) as 人数 from t_person group by per_age;

#14.子查询

#查询年龄小于平均年龄的学生信息

select*from t_person where per_age<(select avg(per_age) from t_person);

2.多表查询

      表与表之间的关系【一对一，一对多，多对多】

      方法：通过外键建立表与表的关系。

#1.一对一主键关联关系【主键关联主键，主键既是主键也是外键】

#2.一对一外键关联关系【专门创建外键列，外键列关联到主键列】 外键列要添加unique

#3.一对多【外键的建立要放在多方表中维护】

#4.多对多【需要单独的创建一张表类维护关系】

#一对一主键关联关系【主键关联主键，主键既是主键也是外键】

#创建用户信息表

create table t_user(

user_id int primary key auto_increment,

user_name varchar(20),

user_hei double,

foreign key t_user(user_id) references t_card(card_id)

);

#创建用户身份证信息表

create table t_card(

card_id int primary key auto_increment,

card_name varchar(20),

card_address varchar(20)

);

#一对一外键关联关系【专门创建外键列，外键列关联到主键列】 外键列要添加unique

create table t_lock(

lock_id int primary key auto_increment,

lock_name varchar(20),

k_id int unique,

foreign key t_lock(k_id) references t_key(key_id)

);



create table t_key(

key_id int primary key auto_increment,

key_name varchar(20)

);



insert into t_key value(null,'钥匙1');

insert into t_key value(null,'钥匙2');

insert into t_lock value(null,'锁1',1);

insert into t_lock value(null,'锁2',3);

#一对多【外键的建立要放在多方表中维护】 把一对一中的unique去掉即可

create table t_student(

student_id int primary key auto_increment,

student_name varchar(20),

student_age int,

student_address varchar(20),

c_id int,

foreign key t_student(c_id) references t_class(class_id)

);



create table t_class(

class_id int primary key auto_increment,

class_name varchar(20)

);



insert into t_class values(null,'java班');

insert into t_student values(null,'zhangsan',23,'西安',1);

insert into t_student values(null,'lisi',24,'西安',1);

insert into t_student values(null,'wangwu',25,'西安',1);

insert into t_student values(null,'liuliu',26,'西安',1);



insert into t_class values(null,'前端班');

insert into t_student values(null,'zhangsansan',23,'西安',2);

insert into t_student values(null,'lisisi',24,'西安',2);

insert into t_student values(null,'wangwuwu',25,'西安',2);

insert into t_student values(null,'liuliuliu',26,'西安',2);

#多对多【需要单独的创建一张表类维护关系】

#创建角色表

create table t_role(

r_id int primary key auto_increment,

r_name varchar(10),

r_desc varchar(30)

);



#创建项目表

create table t_group(

g_id int primary key auto_increment,

g_name varchar(20),

g_desc varchar(30)

);



#创建中间表维护关联关系

create table t_rolegroup(

z_id int primary key auto_increment,

role_id int,

group_id int

);



##通过修改表结构的方式添加外键

alter table t_rolegroup add constraint fk1 foreign key t_rolegroup(role_id) references t_role(r_id);

alter table t_rolegroup add constraint fk2 foreign key t_rolegroup(group_id) references t_group(g_id);

#向角色表中添加测试数据

insert  into t_role values(null,'java程序员','负责开发java程序');

insert  into t_role values(null,'测试员','负责测试程序');

#向项目组表中添加测试数据

insert  into t_group values(null,'CRM组','负责开发CRM系统');

insert  into t_group values(null,'ERP组','负责开发ERP系统');

#向中间表中添加测试数据

insert  into t_rolegroup values(null,1,1);

insert  into t_rolegroup values(null,1,2);

insert  into t_rolegroup values(null,2,1);

insert  into t_rolegroup values(null,2,2);

3.联合查询

内连接查询  【inner  join】

左连接查询  【left    join】 左外连接查询 【left   outer  join】

右连接查询  【right   join】 右外连接查询 【right  outer  join】

内连接与左连接和右连接查询的区别

--内连接：显示左边右边共有的 

--左连接：左边有的显示，右边没有的为null

--右连接：右边有的显示，左边没有的为null

#语法格式

/*

select  [col1,col2...coln来自多张表【最好使用别名】]

from table1

inner  join/

left  outer join/left   join/

right outer join/right  join

table2

on table1.col = table2.col

where 查询条件

*/



#根据锁名称，得到与锁相关的所有信息【包括锁对应的钥匙信息】

select lock_id,lock_name,key_id,key_name from t_lock inner join t_key on t_lock.k_id=t_key.key_id where t_lock.lock_name='锁1';

select lock_id,lock_name,key_id,key_name from t_lock right join t_key on t_lock.k_id=t_key.key_id where t_lock.lock_name='锁2';





#查询zhangsan的所有信息，即包括张三所在班级的信息

select student_id,student_name,student_age,student_address,class_id,class_name

from t_student inner join t_class on t_student.student_id=t_class.class_id where t_student.student_name='zhangsan';



select student_id,student_name,student_age,student_address,class_id,class_name

from t_student left join t_class on t_student.student_id=t_class.class_id where t_student.student_name='zhangsan';





#查询测试员的所有信息，即包括测试员所在项目的信息

#3表了

select r_id,r_name,r_desc,g_id,g_name,g_desc

from t_role

right join t_rolegroup on t_role.r_id=t_rolegroup.role_id

right join t_group on t_rolegroup.group_id=t_group.g_id

where t_role.r_name='测试员';

面试题1

       CREATE TABLE test(

        name varchar(20),

        course varchar(20),

        score varchar(20)

       );    

INSERT INTO test VALUES

       ('张三','语文',81),

       ('张三','数学',75),

       ('李四','语文',76),

       ('李四','数学',90),

       ('王五','语文',81),

       ('王五','数学',82);

请用一条Sql语句查询分数大于80的学生

    -- 查询分数大于80的学生

select * from test where score>80;

面试题2

有如下表数据，表面（product, sale），请用一条sql语句查出201808月份商品销售额前2名，显示列为产品名称，销售总额。

product（产品）

id

product_name

price

1

苹果

4.5

2

荔枝

6

3

杨桃

7

4

樱桃

20

5

板栗

8

6

猕猴桃

6.5

sale（销售）

id

product_id（产品id）

price(单价)

quantity（销售数量）

sale_date（销售日期）

1

1

4.5

20

2018/9/28

2

1

3.5

10

2018/8/28

3

2

6

3

2018/8/28

4

4

20

2

2018/8/30

5

2

6

3

2018/8/26

create table product(

      id int primary key,

      product_name varchar(10),

      price double

);



insert into product values(1,'苹果',4.5);

insert into product values(2,'荔枝',6);

insert into product values(3,'杨桃',7);

insert into product values(4,'樱桃',20);

insert into product values(5,'板栗',8);

insert into product values(6,'猕猴桃',6.5);





create table sale(

      id int primary key,

      product_id int,

      price double,

      quantity int,

      sale_date date,

      constraint sale_fk foreign key(product_id) references product(id)

);



insert into sale values(1,1,4.5,20,'2018-9-28');

insert into sale values(2,1,3.5,10,'2018-8-28');

insert into sale values(3,2,6,3,'2018-8-28');

insert into sale values(4,4,20,2,'2018-8-30');

insert into sale values(5,2,6,3,'2018-8-26');



-- 查出201808月份商品销售额前2名的产品名称和销售总额

select a.product_name '产品名称',sum(b.price*b.quantity) '销售额' from product a join sale b on a.id=b.product_id and b.sale_date>='2018-08-01' and b.sale_date<='2018-8-31' group by a.product_name limit 2;

-- 注意：分组group by使用后，select 后只能跟分组字段和聚合函数

-- 注意： 上述语句执行顺序为 两个表内连接——》按日期筛选——》按名称分组——》求和函数——》显示产品名称和销售额

面试题3

2.根据下面三张表，编写SQL语句

表1 学生信息表 表名：StudentInfo

id

StudentName

Age

Sex

Classname

1

王小明

22

男

大一(1)班

2

李伟

23

男

大一(1)班

表2 课程表  表名：Course

id

CourseName

Types

1

语文

必须

2

高数

必须

3

英语

必须

4

计算机原理

选修

表3  学生所选课程表  表名：Student _sel_Course

id

Studentid(对应StudentInfo表的id)

Courseid(对应Course表的id)

1

1

1

2

1

3

3

1

4

4

2

2

5

2

3

1.) 查出王小明未选择的课程名称。

2.）查询选了选修课程的人员姓名。

-- 学生信息表

create table studentinfo(

      id int primary key,

      studentname varchar(10),

      age int,

      sex varchar(1),

      classname varchar(10)

);

-- 添加学生信息

insert into studentinfo values(1,'王小明',22,'男','大一(1)班');

insert into studentinfo values(2,'李伟',23,'男','大一(1)班');



-- 课程表

create table course(

      id int primary key,

      coursename varchar(10),

      types varchar(10)

);

-- 添加课程信息

insert into course values(1,'语文','必修');

insert into course values(2,'高数','必修');

insert into course values(3,'英语','必修');

insert into course values(4,'计算机原理','选修');





-- 中间表

create table student_sel_course(

      id int,

      studentid int,

      courseid int,

      foreign key(studentid) references studentinfo(id),

      foreign key(courseid) references course(id)

);

-- 中间表添加信息

insert into student_sel_course values(1,1,1);

insert into student_sel_course values(2,1,3);

insert into student_sel_course values(3,1,4);

insert into student_sel_course values(4,2,2);

insert into student_sel_course values(5,2,3);





-- 查出王小明选择的课程名称

select  studentname,coursename from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id having a.studentname='王小明';

-- 查出王小明未选择的课程名称

select  b.id from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id and a.studentname='王小明';



select id,coursename from course where id not in(select  b.id from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id and a.studentname='王小明');

-- 查询选了选修课程的人员姓名

select studentname,coursename,types from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id and types='选修';

 

Java-请多指教
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Java-请多指教

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值