mysql数据库，

mysql数据库，

mysql数据库

一. 在 cetos Linux系统下的安装

yum install mariadb-server mariadb # 下载mariadb服务器和客户端
systemctl start mariadb  #打开mariadb服务
systemctl stop mariadb  #关闭mariadb服务
mysql -u root -p     #开启mysql的客户端
systemctl enable mariadb #设置mariadb开机自动启动
systemctl disable mariadb #关闭mariadb开机自动启动
systemctl restart mariadb #重新启动数据库

wget 网址    #下载源代码
gunzip  文件名   #解压缩
tar -xvf 文件名  # 解归档
cd 解压和解归档文件里   #进入到解压和解归档文件里
make && make install  #安装

二.mysql语句

create database company default charset utf8; # 创建名字叫company的数据库
use company;  # 表示要用这个数据库
create table tb_dept(
deptno integer not null comment '部门编号',
dname vachar(10) not null comment '部门名称',
dloc vachar(20) not null comment '部门所在地',
primary key (deptno)
) ;   # 创建一个名字到tb_dept的表
alter table tb_dept ddate date comment '成立日期'; # 向表中添加ddtade的字段
insert into tb_dept(deptno,dname,dloc) values
(10,'财务部','成都',now()); # 向表中添加数据
delete from tb_dept where deptno=10; # 删除表中的数据
update tb_dept set dloc='深圳' where deptno=10; #更新表中的数据
select * from tb_dept #查询表中的数据

-- SQL (structured Query Language - 结构化查询语言)
-- DDL (数据定义语言) create / drop / alter
-- DML (Data Manipulation Language) insert / delete / update
-- DQL (Data Query Language) select
-- DCL (Data Control Language) grant / revoke

-- 创建company数据库
drop database if exists company;
create database company default charset utf8;

-- 关系型数据库用二维表组织数据
-- 关系型数据库有自己的编程语言

-- 切换到company数据库
use company;

-- 创建部门表
-- 能够唯一确定一条记录的列可以设置为主键
drop table if exists tb_dept;

create table tb_dept
(
deptno integer not null comment '编号',
dname varchar(10) not null comment '名称',
dloc varchar(10) comment '所在地',
primary key (deptno)
);

alter table tb_dept add ddate date comment '成立日期';

-- 向部门表添加数据
insert into tb_dept values (10, '财务部', '成都', now());
insert into tb_dept values (20, '研发部', null, null);
insert into tb_dept (deptno,dname) values (30, '销售1部'), (40, '销售2部'), (50, '后勤保障部');

-- 删除数据（注意：一定要带上条件）
-- set deptno=50 表示给deptno赋值50
delete from tb_dept where deptno=50; 

update tb_dept set dloc='深圳', ddate='2018-4-1' where deptno=30;

-- 查所有行所有列
select * from tb_dept;
-- 投影
select deptno, dname from tb_dept;
-- 别名
select deptno as 部门编号, dname as 部门名称 from tb_dept;
-- 筛选
select deptno, dname from tb_dept where dloc='深圳';

drop database if exists student_one;
create database student_one default charset utf8;

use student_one;

create table tbstudent
(
stuid int primary key not null,
stuname varchar(5) not null,
stusex bit default 1,
stubirth datetime not null,
stuaddr text,
stuphoto longblob
);

create table tbcourse
(
cosid integer primary key not null,
cosname text not null,
coscredit float(3,2),
cosintro text
);

create table tbsc
(
scid integer primary key auto_increment,
sid integer not null,
cid int,
scdate datetime not null,
score float,
foreign key (sid) references tbstudent(stuid),
foreign key (cid) references tbcourse(cosid)
);

insert into tbstudent values (1001, '张三丰', default, '1978-1-1',  '成都市一环路西二段17号', null);
insert into tbstudent (stuid, stuname, stubirth) values (1002, '郭靖', '1980-2-2');
insert into tbstudent (stuid, stuname, stusex, stubirth, stuaddr) values (1003, '黄蓉', 0, '1982-3-3', '成都市二环路南四段123号');
insert into tbstudent values (1004, '张无忌', 1, '1990-4-4', null, null);
insert into tbstudent values (1005, '丘处机', 1, '1983-5-5', '北京市海淀区宝胜北里西区28号', null),
(1006, '王处一', 1, '1985-6-6', '深圳市宝安区宝安大道5010号', null),
(1007, '刘处玄', 1, '1987-7-7', '郑州市金水区纬五路21号', null),
(1008, '孙不二', 0, '1989-8-8', '武汉市光谷大道61号', null),
(1009, '平一指', 1, '1992-9-9', '西安市雁塔区高新六路52号', null),
(1010, '老不死', 1, '1993-10-10', '广州市天河区元岗路310号', null),
(1011, '王大锤', 0, '1994-11-11', null, null),
(1012, '隔壁老王', 1, '1995-12-12', null, null),
(1013, '郭啸天', 1, '1977-10-25', null, null);
-- 删除学生记录
delete from tbstudent where stuid=1004;
-- 更新学生记录
update tbstudent set stubirth='1980-12-12',
stuaddr='上海市宝山区同济支路199号' where stuid=1002;
-- 添加课程记录
insert into tbcourse values
(1111, 'C语言程序设计', 3, '大神级讲师授课需要抢座'),
(2222, 'Java程序设计', 3, null),
(3333, '数据库概论', 2, null),
(4444, '操作系统原理', 4, null);
-- 添加学生选课记录
insert into tbsc values 
(default, 1001, 1111, '2016-9-1', 95),
(default, 1002, 1111, '2016-9-1', 94),
(default, 1001, 2222, now(), null),
(default, 1001, 3333, '2017-3-1', 85),
(default, 1001, 4444, now(), null),
(default, 1002, 4444, now(), null),
(default, 1003, 2222, now(), null),
(default, 1003, 3333, now(), null),
(default, 1005, 2222, now(), null),
(default, 1006, 1111, now(), null),
(default, 1006, 2222, '2017-3-1', 80),
(default, 1006, 3333, now(), null),
(default, 1007, 1111, '2016-9-1', null),
(default, 1007, 3333, now(), null),
(default, 1007, 4444, now(), null),
(default, 1008, 2222, now(), null),
(default, 1010, 1111, now(), null);

-- 1.查询所有学生信息
select * from (
select * from tbstudent s join tbsc c on s.stuid=c.sid) t1 join tbcourse t on t.cosid=t1.cid order by stuid; 
-- 2.查询所有课程名称及学分
select cosname, coscredit from tbcourse;
-- 3.查询所有女学生的姓名和出生日期(筛选)
select stuname, stubirth from tbstudent where stusex=0; 
-- 4.查询所有80后学生的姓名、性别和出生日期(筛选)
select stuname, stusex, stubirth from tbstudent where stubirth < '1980-1-1' and stubirth >= '1970-1-1';
-- 5.查询姓王的学生姓名和性别(模糊)
select stuname, stusex from tbstudent where stuname like '王%';
-- 6.查询姓郭名字总共两个字的学生姓名(模糊)
select stuname from tbstudent where stuname like '郭_';
-- 7.查询姓郭名字总共三个字的学生姓名(模糊)
select stuname from tbstudent where stuname like '郭__';
-- 8.查询名字中有王字的学生的姓名(模糊)
select stuname from tbstudent where stuname like '%王%';
-- 9.查询没有录入家庭住址和照片的学生姓名(多条件筛选和空值处理)
select stuname from tbstudent where stuaddr is null and stuphoto is null;
-- 10.查询学生选课的所有日期(去重)
select distinct scdate from tbsc;
-- 11.查询学生的姓名和生日按年龄从大到小排列(排序)
select stuname, stubirth from tbstudent order by stubirth;
-- 12.查询录入了家庭住址的男学生的姓名、出生日期和家庭按年龄从小到大排列(多条件筛选和排序)
select stuname, stubirth, stuaddr from tbstudent where stuaddr is not null order by stubirth;
-- 13.查询年龄最大的学生出生日期(聚合函数)
select stuname, stubirth from tbstudent t where t.stubirth = (select min(stubirth) from tbstudent);
-- 14.查询年龄最小的学生出生日期(聚合函数)
select stuname, stubirth from tbstudent t where t.stubirth = (select max(stubirth) from tbstudent);
-- 15.查询男女学生的人数(分组和聚合函数)
select stusex as '学生性别', count(*) as '学生人数' from tbstudent group by (stusex);
select if(stusex, '男', '女') as '性别', count(stusex) as '人数' from tbstudent group by stusex;
-- 16.查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select cid, avg(score) from tbsc where cid = 1111;
-- 17.查询学号为1001的学生所有课程的总成绩(筛选和聚合函数)
select sum(t2.score) from tbstudent t1 join tbsc t2 on t1.stuid = t2.sid where stuid = 1001; 
-- 18.查询每个学生的学号和平均成绩，null值处理为0(分组和聚合函数)
select t1.stuid, ifnull(t2.c, 0) from tbstudent t1 left join 
(select sid, avg(score) as c from tbsc group by sid) t2 on t1.stuid = t2.sid;
-- 19.查询平均成绩大于等于90分的学生的学号和平均成绩
select t1.stuid, ifnull(t2.c, 0) as a from tbstudent t1 left join 
(select sid, avg(score) as c from tbsc group by sid) t2 on t1.stuid = t2.sid where t2.c > 90;
-- 20.查询年龄最大的学生的姓名
-- 21.查询选了两门以上的课程的学生姓名
select t3.stuname from (
select stuname, count(*) as c from tbstudent t1 join tbsc t2 on t1.stuid = t2.sid group by stuid) t3 
where t3.c > 2;
-- 22.查询选课学生的姓名和平均成绩
select stuname, ifnull(avg(score), 0) from tbstudent t1 join tbsc t2 on t1.stuid = t2.sid group by stuid;
-- 23.查询学生姓名、所选课程名称和成绩
select t3.stuname, t4.cosname, t3.score from (select * from tbstudent t1 join tbsc t2 on t1.stuid = t2.sid) t3 join tbcourse t4 on t3.cid = t4.cosid order by t3.stuid;
-- 24.查询每个学生的姓名和选课数量
select t5.stuname, count(t5.cosname) from (select t3.stuname, t4.cosname from (select * from tbstudent t1 left join tbsc t2 on t1.stuid = t2.sid) t3 left join tbcourse t4 on t3.cid = t4.cosid order by t3.stuid) t5 group by (t5.stuname);

drop database if exists company_one;
create database company_one default charset utf8;

use company_one;

create table TbDept 
(
deptno tinyint primary key,
dname varchar(30),
dloc varchar(30)
);

create table TbEmp
(
empno int primary key,
ename varchar(4),
job varchar(10),
mgr int,
sal int,
dno tinyint,
foreign key (dno) references TbDept(deptno)
);

insert into TbDept values(10, '会计部', '北京');
insert into TbDept values(20, '研发部', '成都');
insert into TbDept values(30, '销售部', '重庆');
insert into TbDept values(40, '运维部', '深圳');

insert into TbEmp values(7800, '张三丰', '总裁', null, 9000, 20);
insert into TbEmp values(2056, '乔峰', '分析师', 7800, 5000, 20);
insert into TbEmp values(3088, '李莫愁', '设计师', 2056, 3500, 20);
insert into TbEmp values(3211, '张无忌', '程序员', 3088, 3200, 20);
insert into TbEmp values(3233, '丘处机', '程序员', 3088, 3400, 20);
insert into TbEmp values(3235, '李易峰', '程序员', 3088, 3300, 20);
insert into TbEmp values(5566, '狄仁杰', '会计师', 7800, 4000, 10);
insert into TbEmp values(5234, '张翠山', '出纳', 5566, 2000, 10);
insert into TbEmp values(3344, '郭靖', '销售主管', 7800, 3000, 30);
insert into TbEmp values(1359, '黄蓉', '销售员', 3344, 1800, 30);
insert into TbEmp values(4466, '胡一菲', '销售员', 3344, 2500, 30);
insert into TbEmp values(3244, '苗人凤', '程序员', 3344, 3250, 30);
insert into TbEmp values(5577, '杨过', '会计师', 5566, 2200, 10);
insert into TbEmp values(5588, '朱九真', '会计师', 5566, 2100, 10);

-- 查询薪水最高的员工姓名和工资
select ename, sal from TbEmp where sal = (
select max(sal) from TbEmp);

-- 查询员工的姓名和年薪(月薪*12)
select ename, sal*12 from TbEmp;

-- 查询所有部门的编号和人数
select t1.deptno as '部门编号', ifnull(t2.c, 0) as '部门人数' 
from TbDept t1 left join (
select dno, count(*) as c from TbEmp group by dno) t2 on t1.deptno=t2.dno;

-- 查询所有部门的编号和人数
select t1.dname as '部门名称',ifnull(t2.c, 0) as '部门人数' 
from TbDept t1 left join (
select dno,count(*) as c from TbEmp group by dno) t2 on t1.deptno=t2.dno;

-- 查询薪水最高的员工(boss除外)的姓名和工资
select t1.ename, t1.sal from TbEmp t1 
where t1.sal=(
select max(sal) from TbEmp t where t.mgr is not null);

-- 查询薪水超过平均薪水的员工的姓名和工资
select ename, sal from TbEmp e where e.sal >= (select avg(sal) from TbEmp);

-- 查询薪水超过其所在部门的平均薪水的员工的姓名，部门编号和工资

-- select avg(e.sal), d.deptno as deptno from TbEmp e join TbDept d on e.dno=d.deptno group by(d.deptno);

select e.ename, d.dname, e.sal 
from TbEmp e join TbDept d on e.dno=d.deptno 
where e.sal >= (
select average.avgsal from 
(select avg(sal) as avgsal, dno from TbEmp group by(dno)) average where d.deptno=average.dno) 
order by dname;

-- 查询薪水最高的人的姓名,工资和所在部门的名称
-- select e.sal, d.deptno from TbEmp e join TbDept d on e.dno=d.deptno order by d.deptno, (-e.sal)

select e.ename as '员工姓名', e.sal as '工资', d.dname as '部门名称' 
from TbEmp e join TbDept d on e.dno=d.deptno 
where e.sal = (
select t1.maxsal from 
(select max(sal) as maxsal, dno from TbEmp group by dno) t1 where d.deptno=t1.dno);

-- 查询主管的姓名和职位
-- distinct (去重)
select * from TbEmp where empno in(
select distinct mgr from TbEmp where mgr is not null);

-- 查询薪水排名前三的员工姓名和工资
select ename, sal from TbEmp order by (-sal) limit 3;

-- 求薪水排在第4-8名的员工
select ename, sal from TbEmp order by (-sal) limit 5 offset 3;