数据库基础

最新推荐文章于 2023-09-08 17:03:12 发布

江江同学啊

最新推荐文章于 2023-09-08 17:03:12 发布

阅读量784

点赞数 1

分类专栏： JAVA

本文链接：https://blog.csdn.net/qq_36330733/article/details/88934442

版权

JAVA 专栏收录该内容

43 篇文章 0 订阅

订阅专栏

create table stuinfo(
sid int primary key,
sname varchar(20)
);

select * from stuinfo;

--插入一条数据

insert into stuinfo values(1001,'张三');

select * from stuinfo;

---学生表

create table student(
sid int ,
sname VARCHAR(20) not null,--非空约束

sex CHAR(2) default '男',--默认约束
primary KEY(sid)--主键
);

insert into student (sid) values (1001);

create table stu(
sid int UNIQUE,--唯一约束不重复,可以为null
sname varchar(20)
);

insert into stu (sname)values('ddd');

--同校可以有重名的，但是一个班里面不可以重名的
create table stu2(
sname varchar(20),
classname varchar(20),
sage int,
primary key(sname,classname)--联合主键
);

insert into stu2 values('zhangsan','一班',20);
insert into stu2 values('zhangsan','二班',20);
insert into stu2 values('lisi','二班',20);
insert into stu2 values('zhangsan','一班',20);

--外键
select * from stuinfo

create table chengji(
sno INT,
--外键关联的是主表中的主键列
CONSTRAINT fk_1 foreign key (sno) references `stuinfo`(sid),--外键约束
cj decimal(5,2)
);

insert into chengji values(1001,80);

--自增
create table tb_book(
bid int primary key auto_increment,--从1开始，每次递增1
bname varchar(20),
bprice DECIMAL(10,2)
);

insert into tb_book (bname,bprice) values('java1',50);
insert into tb_book (bid,bname,bprice) values(100,'java1',50);

select * from tb_book;

--
desc stuinfo;

--增加一列
alter table stuinfo add sage int;

alter table stuinfo add sex varchar(20) default '男';

--删除一列
alter table stuinfo drop column sage;

--修改一列
alter table stuinfo modify sex varchar(10);

--修改列名
alter table stuinfo change sex ssex varchar(10)

--修改表明
alter table stuinfo2 rename to stuinfo

--删除表中的外键约束
alter table stuinfo2 drop foreign key fk_1

--删除数据
delete from stuinfo where sid=1001;

select * from stuinfo

--修改列的排列
alter table stuinfo modify sname varchar(10) after ssex

create table book(
Bookid   Int primary key auto_increment,
Bookname   Varchar(100) not null,
Bookprice   Int,
Bookauthor   Varchar(50),
bCount   Int

);

insert into book (bookname,bookprice,bookauthor,bcount)values('java',50,'张三',5);
insert into book (bookname,bookprice,bookauthor,bcount)values('oracle',40,'lisi',1);
insert into book (bookname,bookprice,bookauthor,bcount)values('jsp',50,'wangwu',10);
insert into book (bookname,bookprice,bookauthor,bcount)values('C#',500,'田七',5);
insert into book (bookname,bookprice,bookauthor,bcount)values('vb',500,'张三',5);

--所有行所有列
select * from book;

--所有行某一些列

select bookname,bookprice from book;

--某一些行所有列
select * from book where bookauthor='张三';

--某一些行某一些列
select bookname from book where bookauthor='张三';

--4，查询出书籍库存数量小于2的书籍
select * from book where bcount<2;

--5，修改书籍名为“java”的数据的库存数+100
update book set bcount=bcount+100 where bookname='java'

--6，删除表中库存等于0的书籍
delete from book where bcount=1;

--7，   在表上在增加一列：出版社（chubanshe）的列
alter table tb_book1 add chubanshe varchar(20);
--8，   将count字段改名为bookcount
alter table tb_book1 change bcount bookcount int;
--9，   修改bookautor字段数据类型为varchar（100）,非空约束
alter table tb_book1 MODIFY bookauthor varchar(100)
--10，   删除列bookprice
alter table tb_book1 drop COLUMN bookprice;
--11将表book的名称改为tb_book
alter table book rename to tb_book1;
--12删除表book
drop table book

create table office(
officeCode   int primary key,
city   Int not NULL,
address   varchar(50),
country   varchar(50),
postalCode   varchar(25)
);

insert into office values(1,100,'金水区','中国','45000');
insert into office values(2,101,'石家庄','中国','453333');

create table employee(
employeeNumber   int(11) primary key AUTO_INCREMENT,
lastName   varchar(50) not null,
firstName   varchar(50) not null,
mobile   varchar(25),
officeCode   Int ,
FOREIGN key (officecode) references office(officeCode),
jobTitle   varchar(50),
brth   datetime,
note   varchar(255),
sex   varchar(5)
);

insert into employee (lastname,firstname,mobile,officecode,jobtitle,brth,note,sex)
values('lijuan','guo','ssss',2,'sdfsf','2000-03-05','ddd','女');

insert into employee (lastname,firstname,mobile,officecode,jobtitle,brth,note,sex)
values('sss','guo','ssss',2,'sdfsf','2000-03-05 12:40:50','ddd','女');

select * from employee;

-------------------------

create table tb_dept(
d_no   int primary key auto_increment,
d_name   varchar(50) not null,
d_location   varchar(100)
);

INSERT INTO tb_dept
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');

select * from tb_dept;

create table tb_employee(
e_no   int primary key ,
e_name   varchar(50) not null,
e_gender   char(2) ,
dept_no   int(11),
foreign key(dept_no) references tb_dept(d_no),
e_job   varchar(50) not null,
e_salary   int(11) not null,
hireDate   date not null
);

INSERT INTO tb_employee
VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
(1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
(1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
(1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'),
(1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),
(1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),
(1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),
(1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),
(1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),
(1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),
(1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');

INSERT INTO tb_employee
VALUES (1013, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12 12:30:40');

select * fromm tb_employee

select mod(5,3)

select * from stu;

--把一个查询结果放入放到另一个表中
insert into stu
select sid,sname from student where sid=1003;

update student set sname='ssss', sex='女' where sid=1001

select * from student

-------in
select * from tb_employee;
--查询部门编号等于10或者等于20的
select * from tb_employee where dept_no =10 or dept_no=20;

select * from tb_employee where dept_no in(10,20);-- in()

--查询部门编号不等于10而且不等于20的
select * from tb_employee where dept_no !=10 && dept_no!=20;

select * from tb_employee where dept_no not in(10,20);-- not in()

--工资在2000-3000之间的
select * from tb_employee where e_salary>=2000 and e_salary<=3000;

select * from tb_employee where e_salary between 2000 and 3000;

--like 模糊查找
--%： 0..n任意字符
--_: 1个任意字符

select * from tb_book1 where bookauthor like '张%';
select * from tb_book1 where bookname like '%j%';

--
select * from tb_book1;

--查询出版社为null的
--is null is not null
select * from tb_book1 where chubanshe is not null;

--去除重复行
select DISTINCT firstname,mobile from employee;

--排序 order by 列 asc/desc
select * from tb_employee order by e_salary desc;

--先按照工资从高到低排列，在按照工号从低到高
select * from tb_employee order by e_salary desc,e_no ;

--limit
--工资最高的前三人
select * from tb_employee order by e_salary desc limit 3;

--分页开始位置计算 (页码-1)*每页显示的个数
select * from tb_employee order by e_salary desc limit 6,3;

create table dept (
d_no int(11)   primary key not null unique ,
d_name varchar(50)   not null ,
d_location varchar(100)

);

CREATE TABLE employee(
e_no       int(11)   PRIMARY key   not null unique ,
e_name       varchar(50)   not null,
e_gender       char(2)   ,
dept_no       int(11)   not null ,
FOREIGN KEY employee(dept_no) REFERENCES dept(d_no),
e_job   varchar(50)   not null ,
e_salary   int(11)   not null ,
hireDate   date

);

INSERT INTO dept
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');

INSERT INTO employee
VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
(1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
(1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
(1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'),
(1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),

/*3：在employee表中，查询所有记录的e_no、e_name和e_salary字段值*/
SELECT e_no ,e_name ,e_salary from employee;

/*4：在employee表中，查询dept_no等于10和20的所有记录。*/
SELECT * from employee where dept_no=10 or dept_no=20;

/*5：在employee表中，查询工资范围在800到2500之间的员工信息。*/
SELECT * from employee where e_salary BETWEEN 800 and 2500;
SELECT * from employee where e_salary>=800 AND e_salary<=2500;

/*6：在employee表中，查询部门编号为20的部门中的员工信息。*/
SELECT * from employee where dept_no=20;

/*7：在employee表中，查询每个部门最高工资的员工信息。*/

/*8：查询员工BLAKE所在部门和部门所在地。*/
SELECT dept_no from employee where e_name='BLAKE';
select e_name,d_no,d_location from dept,employee where d_no=30;

/*9：使用连接查询，查询所有员工的信息和部门信息。*/

/*10：在employee表中，计算每个部门各有多少名员工。*/

/*11：在employee表中，计算不同职位的总工资数。*/
SELECT e_job,sum(e_salary) as e_job_sumsalary from employee GROUP BY e_job ;

/*12：在employee表中，计算不同部门的平均工工资。*/
SELECT dept_no,avg(e_salary) as dept_avg_salary from employee GROUP BY dept_no;

/*13：在employee表中，查询工资低于1500的员工信息。*/
SELECT * from employee where e_salary<1500;

/*14：在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。*/
SELECT * from employee ORDER BY dept_no desc,e_salary desc;

--先按照工资从高到低排列，在按照工号从低到高
select * from tb_employee order by e_salary desc,e_no ;

/*15：在employee表中，查询员工姓名以字母A或S开头的员工的信息。*/
SELECT * from employee where e_name like 'A%' or 'S%';

/*16：在employee表中，查询到目前为止，工龄大于等于10年的员工信息。*/

/*17：把employee表中，按照工资排序，把工资最高的人显示出来*/
SELECT * from employee order by e_salary desc limit 1;

11：查询平均成绩>60分的学生的平均成绩和学生姓名

select * from studentinfo inner join (
select (writtenexam+labexam)/2, sid from score
where (writtenexam+labexam)/2 >60
) a on studentinfo.sno=a.sid

select * from studentinfo inner join score on studentinfo.sno=score.sid
where (writtenexam+labexam)/2 >60

12：查询每个学生的总分并求出前三名的学号和总分
14:创建视图v_stu_score 包含学号姓名成绩信息

-------------------聚合函数

select * from tb_employee;
UPDATE tb_employee set dept_no=null where e_no='1014'
--count(*) 求多少行
--count(列名): 去除null行之后结果

select count(*) from `tb_employee`;
select count(e_no) from tb_employee;
select count(e_gender) from tb_employee;

--sum(列名)：去除null行之后的值来运算
--avg(列名)

select sum(e_salary) as 总分,count(e_salary) 人数,avg(e_salary) from tb_employee ;

--max min
select max(e_salary) 最高工资,min(e_salary) 最低工资 from tb_employee;

select t.e_no,t.e_name from tb_employee as t;

--group by 分组
---每个部门下有多少人

-- where条件 ->group by分组 -> having 条件

select dept_no,count(e_name) as 人数 ,max(e_salary)
from tb_employee
where e_gender='m'
group by dept_no
having count(e_name)>3

select * from tb_employee;

-----------
--9：使用连接查询，查询所有员工的信息和部门信息。
select * from tb_dept;
select * from tb_employee;

select * from tb_employee,tb_dept
where tb_employee.dept_no=tb_dept.d_no;

----------

--内连接：两个表中都满足条件
create table student(
   sno   varchar(10) primary key,
   sname   varchar(20) ,
   ssex   varchar(4),
   sage   int,
   stel   Int,
   saddress   varchar(50)
);

insert into student values
('s1001','aaa','男',20,1232132131,'河南郑州'),
('s1002','aaa','女',23,234324324,'河南平顶山'),
('s1003','bbb','男',19,1232132131,'河南洛阳'),
('s1004','ddd','男',20,1232132131,'河南郑州');

create table kemu(
kid int primary key,
kname varchar(20)
);

insert into kemu values
(1001,'语文'),
(1002,'数学'),
(1003,'英语');

--
create table chengji(
s_id varchar(10),--学号
foreign key(s_id) references student(sno) ,
k_id int ,--科目
FOREIGN key (k_id) references kemu(kid),
cj decimal(4,1)--成绩
);

insert into chengji values
('s1001',1001,90),
('s1001',1003,80),
('s1003',1001,100),
('s1004',1002,70)

--查询每个学生的考试的科目以及成绩
select * from student,kemu,chengji
where
student.sno=chengji.s_id
and kemu.kid =chengji.k_id
and chengji.cj>90

--inner join
select * from student
inner join chengji on student.sno=chengji.s_id
inner join kemu on chengji.k_id=kemu.kid
where chengji.cj>90

--没有考试过的学生信息

select student.* from student left join chengji on student.sno=chengji.s_id
where cj is null

------显示所有员工信息和所在的部门的基本信息
select * from tb_employee
inner join tb_dept on tb_employee.`dept_no`=tb_dept.d_no

select * from tb_dept;

--左连接右连接
--要显示所有的部门，部门下有员工的显示出员工信息

--左连接：要都满足条件的记录，然后在加上左边表中不满足条件的记录
select * from tb_dept left join tb_employee on tb_dept.d_no=tb_employee.dept_no

--右连接：要都满足条件的记录，然后在加上右边表中不满足条件的记录
select * from tb_dept right join tb_employee on tb_dept.d_no=tb_employee.dept_no

-----统计一下每个部门下有多少人

select d_no,count(dept_no) from tb_dept left join tb_employee on tb_dept.d_no=tb_employee.dept_no
group by d_no

--统计工资的员工信息

select * from `tb_employee`
inner join (
select max(e_salary) as maxsalary from tb_employee) as a
on tb_employee.`e_salary`=a.maxsalary

----子查询
--where做条件
--放到表的位置

--
select * from student;

--查找年龄比 bbb 大的学生
select * from student where sage>(
select sage from student where sname='bbb'
)

--没有考试过的学生信息
select * from student where sno not in(
select s_id from chengji
)

--查询考试的学生的学号和姓名
select * from student where sno in(
select s_id from chengji
)

select student.* from student inner join chengji on student.sno=chengji.s_id

--查询考试的学生的学号和姓名,成绩
select student.*,chengji.cj from student inner join chengji on student.sno=chengji.s_id

--union：去掉重复的
--union all:不去掉重复的

select sno,sname from student
union all
select sno,sname from studentinfo

--------------mysql 作业03-3
-- 1、查询选修了课程的学生的学号。
select * from student where sno in(
select s_id from chengji
)

select * from student inner join chengji on student.sno=chengji.s_id

-- 2、查询选修了课程号为K1002的学生的姓名。
select * from student inner join chengji on student.sno=chengji.s_id
where k_id=1002

-- 2-2、查询选修了课程号为语文的的学生的姓名。
select * from student inner join chengji on student.sno=chengji.s_id
inner join kemu on chengji.k_id=kemu.kid
where kname='语文'

select * from student where sno IN(
select s_id from chengji where k_id =(
select kid from kemu where kname='语文'
)
)


-- 3、查询姓名中第二个字为“林”的学生。
like '_林%'
-- 4、查询各课程号及相应的选课人数。
select k_id,count(*) from chengji group by k_id
-- 5、查询同时选修了课程1001和1003的学生的学号。

select s_id from chengji where k_id=1003 and s_id in(
select s_id from chengji where k_id=1001
);

-- 6、查询各科成绩的最高分、最低分、平均分。

select k_id,max(cj),min(cj),avg(cj) from chengji group by k_id

-- 7、查询各个学生的总成绩和平均成绩，总成绩降序排列，总成绩相同时平均升序排列。
select s_id,sum(cj),avg(cj) from chengji group by s_id
order by sum(cj) DESC,avg(cj)

-- 8、查询课程《语文》成绩在70~85之间的学生的学号和姓名。

select * from student inner join chengji on student.sno=chengji.s_id
inner join kemu on chengji.k_id=kemu.kid
where kname='语文' and cj between 90 and 100

select * from student where sno in(
select s_id from chengji where k_id =(
select kid from kemu where kname='语文'
) and cj between 90 and 95
)

-- 9、查询K1002成绩高于此课程平均分的学生的学号。
select * from student where sno in(
select s_id from chengji where cj >=(
select avg(cj) from chengji where k_id='1002'
) and k_id='1002'
)

-- 10、将李芳同学的课程号为K1003的成绩改为85分。

update chengji set cj=85
where
s_id in(
select sno from student where sname='aaa'
) and k_id='1003'

-----------------常用函数----------------
--四舍五入
select round(3.567)
select round(3.567,2)

select truncate(3.567,2) --截断

select power(5,3)

select length('22222')
select length('你好') --utf8的话一个汉字是3个

select CHAR_LENGTH('您好') --一个汉字一个

select CONCAT('dd','ee','rrr') --字符串的连接

select CONCAT_WS('#','dd','ed','sss');--连接，用第一个表达式作为字符之间的分隔

select left('guolijuan',3) --窃取左边三个
select right('guolijuan',5)

select substring('guolijuan',3,5)

select lpad('guo',5,'X')

select rpad('guo',5,'X')

select REPEAT('aaa',5)

--
select LOCATE('u','guolijuan') --查询

--类型转换
select cast('13' as SIGNED)
select 13

-------------------题如下------
13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2

现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，
然后再按照后半部分的数字进行排序，输出要排成这样：
13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2
-------------------------------
create table test22(
a varchar(20)
);

insert into test22 values('135-1'),('13-100'),('13-2'),
('13-5'),('13-108'),('13-5'),
('14-1'),('14-3'),('15-2'),
('13-11'),('13-10'),('14-2');

select * from test22 order by
cast(left(a, locate('-',a)-1) as signed),
cast(right(a,length(a)-locate('-',a)) as signed)

----日期时间类型
select now()
select sysdate() --年月日时分秒
select CURRENT_DATE() --年月日

select month(now())
select monthname(now())

select DAYOFWEEK(now())--1是周日 2是周一
select WEEKDAY(now()) -- 0是周一 6是周日

select WEEKOFYEAR(now()) --一年中的第几周

select DAYOFmonth(now())

----日期的增加
create table kuandai(
id int primary key,
startdate date,
enddate date
);

insert into kuandai values(1,'2018-05-01','2019-05-01');

update kuandai set enddate=date_add(enddate, INTERVAL 10 month );

江江同学啊

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库基础

create table stuinfo( sid int primary key, sname varchar(20));select * from stuinfo;--插入一条数据insert into stuinfo values(1001,'张三');select * from stuinfo;---学生表create table student(...
复制链接

扫一扫

专栏目录