Mysql数据库总结

文章目录

​​一、数据库简介​​​​二、mysql数据类型(5.5版本)​​​​三、Sql语句​​​​(1)Sql语句简介​​​​(2)数据定义语言DDLcreate,alter,drop​​​​(3)数据操纵语言DMLupdate,insert,delete​​​​(4)数据控制语言DCLgrant,revoke​​​​(5)数据查询语言DQLselect​​​​(6)分组查询与分页查询group by，limit​​​​四、完整性约束(单表)​​​​五、多表查询​​​​六、MySQL数据库练习题​​

---

一、数据库简介

数据库(Database，DB)是按照数据结构来组织，存储和管理数据的仓库。典型特征：数据的结构化、数据间的共享、减少数据的冗余度，数据的独立性。关系型数据库：使用关系模型把数据组织到数据表(table)中。现实世界可以用数据来描述。主流的关系型数据库产品：Oracle(Oracle)、DB2(IBM)、SQL Server(MS)、MySQL(Oracle)。数据表：数据表是关系数据库的基本存储结构，二维数据表有行(Row),和列(Column)组成，也叫作记录(行)和字段(列)。

二、MySQL数据类型(5.5版本)

MySQL中除了字符串类型需要设置长度,其他类型都有默认长度.

数值类型

Java中

MySQL中

整型

byte

tinyint

short

smallint

int

int

long

bigint

浮点型

float

float

double

double

字符串类型

String

定长char() ;可变长varchar()

时间日期

date

date/time/datetime

-- 数据查询语言DQL(select)
create table exam(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  English INT,
  Chinese INT,
  Math int
);
INSERT INTO exam VALUES(NULL,'小花',99,99,99);
INSERT INTO exam VALUES(NULL,'小蓝',89,89,89);

– 1.全表查询: SELECT * FROM 表;
SELECT ​ FROM exam;
– 2.查询部分字段: SELECT 字段,字段,字段… FROM 表;
SELECT NAME,English,Math FROM exam;
– 3.过滤重复字段行: SELECT [DISTINCT] |列名 FROM 表;
SELECT DISTINCT Math FROM exam;
SELECT DISTINCT name,Math FROM exam;
– 4.查询字段起别名: SELECT 字段 AS 新字段名,字段 新字段名 FROM 表;
SELECT NAME,English AS English_score FROM exam;
– 5.查询指定字段
SELECT NAME,English,Chinese FROM exam WHERE NAME=‘李白’;
– 6.使用表达式+、-、、/
SELECT id,NAME,English-20 AS _English FROM exam;
SELECT NAME,English+Math+Chinese FROM exam;
– 7.模糊查询
SELECT FROM exam WHERE NAME LIKE ‘小_’;
SELECT ​ FROM exam WHERE NAME LIKE ‘%%’;
– 8.使用and,or
SELECT FROM exam WHERE English > 90 AND Chinese >90;
SELECT ​ FROM exam WHERE English < 90 or Math >99;
– 9.使用in,not in
SELECT FROM exam WHERE id=2 OR id=3 OR id=4;
SELECT ​ FROM exam where id IN(2,3,4);
SELECT FROM exam where id not IN(2,3,4);
– 10.使用between…and []
SELECT ​ FROM exam WHERE English BETWEEN 90 AND 100;
– 11. is null,is not null
INSERT INTO exam(id,NAME) VALUES(NULL,NULL);
SELECT FROM exam WHERE NAME IS NULL;
SELECT ​ FROM exam WHERE NAME IS not NULL;
– 11.排序查询
SELECT FROM exam ORDER BY Chinese ASC;
SELECT ​ FROM exam ORDER BY Chinese DESC;
SELECT FROM exam ORDER BY English DESC,Chinese DESC;– 如果英语成绩相同,按照汉语成绩降序排列
SELECT * FROM exam WHERE NAME LIKE ‘小%’ ORDER BY English ASC;
– 12.聚合函数
SELECT SUM(English+Math+Chinese) FROM exam;
SELECT COUNT(id) FROM exam WHERE NAME IS NOT NULL;
SELECT MAX(English) FROM exam;
SELECT MIN(English) FROM exam;
SELECT AVG(English) FROM exam ;

(6)分组查询与分页查询group by，limit

-- 一、分组查询

CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr int,
hiredate DATE,
sal DOUBLE(7,2),
commit double(5,2),
deptno INT NOT NULL
);

INSERT INTO emp VALUES
(1002,‘白展堂’,‘clerk’,1001,‘1983-05-09’,7000.00,200.00,10),
(1003,‘李大嘴’,‘clerk’,1002,‘1980-07-08’,8000.00,100.00,10),
(1004,‘吕秀才’,‘clerk’,1002,‘1985-11-12’,4000.00,null,10),
(1005,‘郭芙蓉’,‘clerk’,1002,‘1985-03-04’,4000.00,null,10),
(2001,‘胡一菲’,‘leader’,null,‘1994-03-04’,15000.00,NULL,20),
(2002,‘陈美嘉’,‘manger’,2001,‘1993-05-24’,10000.00,300.00,20),
(2003,‘吕子乔’,‘clerk’,2002,‘1995-05-19’,7300.00,100.00,20),
(2004,‘张伟’,‘clerk’,2002,‘1994-10-12’,8000.00,500.00,20),
(2005,‘曾小贤’,‘clerk’,2002,‘1993-05-10’,9000.00,700.00,20),
(3001,‘刘梅’,‘leader’,null,‘1968-08-08’,13000.00,NULL,30),
(3002,‘夏冬梅’,‘manger’,3001,‘1968-09-21’,10000.00,600.00,30),
(3003,‘夏雪’,‘clerk’,3002,‘1989-09-21’,8000.00,300.00,30),
(3004,‘张一山’,‘clerk’,3002,‘1991-06-16’,88000.00,200.00,30);

– 1.查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
– 2.查询每个职位的最高工资和最低工资
SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job;
– 3.查询每个部门每种职位的最高工资
SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job;
– 4.查询每个部门的最高薪水,只有最高薪水大于15000的记录才被输出显示
SELECT deptno,MAX(sal)AS max_sal FROM emp GROUP BY deptno HAVING max_sal>=15000;
– 5.查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>9000;
– 6.Havaing子句与where子句的区别
(1)where是用来过滤记录的,HAVING是用来过滤分组的
(2)过滤的时机不相同,先过滤Where后过滤Having.
(3)WHERE是在查询表时逐行过滤以选取满足条件的记录
(4)having是在数据查询后并且分完组后对分组进行过滤的
(5)HAVING必须跟在group BY
(6)查询语句执行顺序:5select 1from 2where 3group by 4having 6order by

– 二、分页查询
– 1.从第几页开始多少页(下标从0开始)
SELECT ​ FROM emp LIMIT 0,3;
– 2.每页几条第几页==需要查看第几页-1)乘以第二个参数
SELECT FROM emp LIMIT 10,5;– 每页五条第三页(3-1)5
SELECT FROM emp LIMIT 2,2; –每页2条第2页(2-1)2
– 3.查看工资最高的前十个职员信息
SELECT FROM emp ORDER BY sal DESC LIMIT 0,10;

四、完整性约束(单表)

主键约束：​​primary key​​​ (默认就是唯一非空的)
外键约束: 用于在两个表之间建立关系，需要指定引用主表的哪一列。

如果表A的主键是表B中的字段,则该字段称为表B的外键,表A(主表),表B(从表).外键是用来实现参照完整性的，主表更新时从表也更新,主表删除时如果从表有匹配的项,删除失败

唯一约束：​​unique​​​ 非空约束：​​not null​​

CREATE TABLE emp(
  empno INT PRIMARY KEY AUTO_INCREMENT,--主键约束
  ename VARCHAR(20),
  job VARCHAR(20),
  mgr int,
  hiredate DATE,
  sal DOUBLE(7,2),
  commit double(5,2),
  deptno INT NOT NULL  -- 非空约束
);
-- √ 添加 唯一约束和非空约束
ALTER TABLE exam MODIFY NAME VARCHAR(21) UNIQUE NOT NULL;

– 创建主表
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)
);
INSERT INTO dept VALUES
(10,‘餐饮部’,‘上海’),
(20,‘销售部’,‘浙江’),
(30,‘财务部’,‘北京’),
(40,‘技术部’,‘深圳’);

为从表emp加外键

ALTER TABLE emp ADD FOREIGN KEY (deptno) REFERENCES dept(deptno);

五、多表查询

多张数据表或视图的查询叫做连接查询

– 1.笛卡尔积:
SELECT ​
FROM emp,dept;
– 2.等值链接(SELECT * FROM A,B WHERE A.主键=B.外键;)
SELECT
FROM emp,dept WHERE dept.deptno = emp.deptno;
– 3.内连接(SELECT * FROM A INNER JOIN B ON A.主键=B.外键;)
SELECT ​
FROM emp INNER JOIN dept ON dept.deptno = emp.deptno;
– 4.外连接:
– 4.1左外连接:(SELECT * FROM A LEFT OUTER JOIN B ON 条件;)
SELECT
FROM emp LEFT OUTER JOIN dept ON dept.deptno=emp.deptno;
– 4.2右外连接:(SELECT * FROM A right OUTER JOIN B ON 条件;)
SELECT ​
FROM emp RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno;
– 5.子查询:
– 5.1单行单列,工作地点在上海的员工
SELECT
FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc =‘上海’);
– 5.2多行单列,工作地点不在上海的员工
SELECT ​
FROM emp WHERE deptno in(SELECT deptno FROM dept WHERE loc <>‘上海’);
– 6.自连接
SELECT e1.,e2.* FROM emp e1 inner join emp e2 ON e1.mgr =e2.empno
WHERE e1.ename =‘吕子乔’

多表查询练习↓
– 1.查看每个员工的名字以及其所在部门的名字
SELECT emp.ename,dept.dname,dept.loc
FROM emp,dept
WHERE emp.deptno = dept.deptno;
– 2.查看工作地点在北京的员工有哪些
SELECT ​
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
WHERE dept.loc =‘北京’;
– 3.查看每个城市员工的平均工资
SELECT dept.loc,ifnull(avg(sal),0)
FROM emp right join dept
on emp.deptno =dept.deptno
GROUP BY dept.loc;
– 4.查看工作地点在上海的员工有哪些
SELECT emp.,dept.dname,dept.loc
FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno
WHERE dept.loc=‘上海’;
– 5.查找和曾小贤同职位的员工
SELECT ​
FROM emp
WHERE job=(SELECT job FROM emp WHERE ename=‘曾小贤’);
– 6.查找薪水比整个机构平均水平高的员工
SELECT
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);
– 7.查询出部门中有clerk但职位不是clerk的员工的信息
SELECT *
FROM emp
WHERE deptno IN(SELECT DISTINCT deptno FROM emp WHERE job=‘clerk’) AND job!=‘clerk’;
– 8.查看每个城市员工的平均工资’
SELECT dept.loc,AVG(sal)
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno GROUP BY dept.loc;
– 9.查询出最低薪水高于部门20的最低薪水的部门信息
SELECT deptno,MIN(sal) AS MIN_sal
FROM emp
GROUP BY deptno
HAVING min_sal>(SELECT MIN(sal) FROM emp WHERE deptno=20);
– 10.列出所有员工的姓名及其直接上级的姓名
SELECT e1.ename,e2.ename
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr = e2.empno;

六、MySQL数据库练习题

​​练习题​​