MySql数据库详解

来源：互联网发布：libcurl https linux 编辑：程序博客网时间：2024/06/05 17:18

时间过的很快，转眼间大三就快过完了立马面临着找实习，向饿了么投了一份简历也石沉大海般没有回应。

从大二到现在，自学技术也学了两年。写这一系列播客主要也是想对以前学过的东西做一个整理和总结，外加

对知识点升华一下吧。主要是为了应付接下来的笔试面试吧

一. mysql基础

关系型数据库:用表来存储数据的数据库，我们常见的mysql,sqlserver,oracle等都属于关系型数据库

在设计数据库时，一般一个应用对应一个数据库每一个javaBean对应数据库中一张表， javaBean的每对象对应表中的每一条记录，javabean中的每一个属性跟数据库中的属性一一对应

数据库的创建删除语句:

        create database mydb character set gbk;(编码集默认为utf-8)        drop database mydb;use mydb;

表的创建修改删除:

create table _user(              _id INTEGER PRIMARY KEY auto_increment,              _name varchar(30) NOT NULL   default ""         )engine=innodb default charset=gbk;  drop table _user;               alter table s add address varchar(30);

增加约束的语法：修改表表名
增加约束约束名约束的类型；
alter table emp
add constraint FK_deptno FOREIGN key(deptno)
REFERENCES dept(dno);

数据库表简单的增删改查:

①增删改:insert :insert into tableName(_name)value('liujian')
insert into tableName(_name) values('liujian'),
('zhangsan');
delete :delete from Student1 where _id=1;

update:update Student1 set age=age-1 where id="3";

②查询语言:
select * from Student1 where id=1;
select * from Student limit 2,3;
select count(*) from Student1;

MySql常见数据类型:
①字符串型:VARCHAR,CHAR;(CHAR为定长字符串,VARCHAR为变长字符串,VARCHAR更省空间,CHAR更省时间)
②大数据类型:BLOB:
TEXT:最大支持4个G

③数值型:INT,FLOAT,DOUBLE
④日期型:DATE,TIME,DATETIME,timestamp

二.mysql查询语句

1.查找当前时间：select now();
select sysdate();
select curdate();

2. select ename "刘建" sal*10+100 "9000" from emp;
select ename as 刘建,sal*10+1000 as 9000 from emp;
select pname '刘建',age*10 as 年龄 from person;
ifnull(age,0):如果age=null,age=0;

3. distinct关键字：消除重复项；
select distinct job,deptno from emp：消除job跟deptno的组合项；
select distinct * from emp:

4.查询的五中语句：（若一起出现，则按照where>group>order>having>limit的顺序）
①.where
复杂的比较运算符：
between ..and :
select * from _user where age BETWEEN 19 and 40;
in(20,10,30):包含20,10或者30的意思
select * from _user where age in(20,12,17);
like '':通配查询
'%:通配任意字符,0个或者多个字符；
'_':通配单一字符
以Q开头:'Q%'
以Q结尾：'%Q'
含Q:'%Q%'
五个字符：'_____'
select * from _user where _name like '刘%';
select * from _user where _name like '___';//查询名字为三个字
is null(空值不能用等号去判断)

操作符
and
or
not
算术运算>not>and>or;

②.group by
a.group聚合函数:(一般配合统计函数才有意义)
max:select max(age) 最大年龄 from _user;
min:select min(age) 最小年龄 from _user;
sum:指定列进行求和:select sum(ifnull(age,0))/count(*) from _user where _name like '___';//求名字个数为3的平均年龄
avg:求平均值: select avg(ifnull(comm,0)) from emp;
select avg(comm) from emp:总数不算null
count:查行数:select count(*) from _user where age>100;

b .group by 根据某一组划分；
分组以后查询的列只能是被分组的列或者聚合函数
select job,deptno,max(sal) from emp group by deptno,job ;

分组以后再做筛选不能用where,而改用having;
select deptno,count(*),avg(sal) from emp group by deptno having avg(sal)>2000;

求出薪水值大于2000，部门的平均薪水大于3000，部门人数在2以上的部门编号，平均工资
select deptno,avg(sal) from emp where sal>2000 group by deptno
having avg(sal)>3000 and count(*)>=2;

c.注意：一个SQL语句中可以同时有where和having，where用于在分组前过滤数据，having用于在分组之后过滤数据；
having子句中可以使用聚合函数,where中不能使用.
很多情况下having语句可以替代where语句

③.order by:排序
null没法计算，ifnull(sal,0)
asc :升序
desc:降序
默认升序asc;
select * from _user where _name like '刘%' order by IFNULL(age,0) desc;

④.having:(对查询结果进行处理)
看上

⑤.limit:
limit 1: select * from emp limit 1;//查询一条记录
limit 1,3: select * from emp limit 1,3//查询第二条到第四条记录
select * from emp limit 1 offset 2;//第三条开始查询一条记录

5.子查询
where型子查询：
把内层查询的结果作为外层查询的条件；

from 型子查询：
将内层查询的结果当成临时表，供外层sql再次查询；
exists型子查询：
将外层查询的查询结果拿到外层，看内层的查询是否成立

三.多表连接查询和子查询

一.多表设计
1.外键:
增加一个约束：alter table tableName add constraint 约束名约束类型

2.部门表(dept)和员工表(emp),一个部门对于多个员工(一对多方式),我们就需要在
员工表中把部门表的主键(id)设置为员工表的外键deptid
alter table emp
add constraint FK_deptno FOREIGN key(deptid)
REFERENCES dept(id);

建立外键约束方式二:
create table emp(
id int primary key auto_increment,
name VARCHAR(20),
dept_id int,
FOREIGN key(dept_id) REFERENCES dept(id)
);

3. 一对多关系:在多的一方保存一的一方的主键作为外键
一对一关系:在任意的一方保存另一方的主键作为外键
多对多关系:设置一张第三方表,在这张表中保存两方的主键作为外键,保证两者的对应关系

二.多表查询
1.笛卡尔积查询:将两张表的记录进行一个相乘的操作,左表有m条记录
右表有m条记录,笛卡尔积查询出有n*m条记录

2.内连接查询:左边表和右边表都能找到对应记录的记录
select * from emp,dept where dept.id=emp.dept_id;
select * from dept inner join emp on dept.id=emp.dept_id ;

3.左外连接查询:在内连接的基础上,增加左边表有而右边表没有的记录
select * from emp left join dept on dept.id=emp.dept_id;
select * from dept right join dept on dept.id=emp.dept_id;(两者效果一样)

4.右外连接查询: 在内连接的基础上,增加右边表有而左边表没有的记录
select * from emp right join dept on dept.id=emp.dept_id;

5.全外连接查询:是左外连接和右外连接的结果
select * from emp full join dept on dept.id=emp.dept_id;(mysql不支持全外连接)
union关键字模拟全外连接:
select * from emp left join dept on dept.id=emp.dept_id
union
select * from emp right join dept on dept.id=emp.dept_id;

三.mysql子查询
1、where型子查询
（把内层查询结果当作外层查询的比较条件）
#不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
#取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

2、from型子查询
(把内层的查询结果供外层再次查询)
#用子查询查出挂科两门及以上的同学的平均成绩
思路：
#先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
#以上查询结果，我们只要名字就可以了，所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
#找出这些同学了，那么再计算他们的平均分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

3、exists型子查询
（把外层查询结果拿到内层，看内层的查询是否成立）
#查询哪些栏目下有商品，栏目表category,商品表goods
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

0 0