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 );