Mysql数据库总结

喜欢ヅ旅行 2023-10-06 11:39 75阅读 0赞

#### 文章目录 ####

*  一、数据库简介
 *  二、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中除了字符串类型需要设置长度,其他类型都有默认长度.

<table> 
 <thead> 
  <tr> 
   <th>数值类型</th> 
   <th>Java中</th> 
   <th>MySQL中</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>整型</td> 
   <td>byte</td> 
   <td>tinyint</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>short</td> 
   <td>smallint</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>int</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>long</td> 
   <td>bigint</td> 
  </tr> 
  <tr> 
   <td>浮点型</td> 
   <td>float</td> 
   <td>float</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>double</td> 
   <td>double</td> 
  </tr> 
  <tr> 
   <td>字符串类型</td> 
   <td>String</td> 
   <td>定长char() ;可变长varchar()</td> 
  </tr> 
  <tr> 
   <td>时间日期</td> 
   <td>date</td> 
   <td>date/time/datetime</td> 
  </tr> 
 </tbody> 
</table>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 2]

## 三、Sql语句 ##

### (1)Sql语句简介 ###

*  SQL(Structured Query Language):结构化查询语言
 *  SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言，可以用来查询数据，操纵数据，定义数据，控制数据。

SQL可以分为：

*  数据定义语言(DDL)：Data Definition Language
 *  数据操纵语言(DML)：Data Manipulation Language
 *  事务控制语言(TCL)：Transaction Control Language
 *  数据查询语言(DQL)：Data Query Language
 *  数据控制语言(DCL)：Data Control Language

### (2)数据定义语言DDLcreate,alter,drop ###

--数据定义语言DDL(create,alter,drop)

– 一、数据库相关的DDL

– 1.创建数据库  
CREATE DATABASE mybase;  
– 2.创建数据库并指定字符集  
CREATE DATABASE mybase1 CHARACTER SET UTF8;  
– 3.查看所有数据库  
SHOW DATABASES;  
– 4.查看当前使用的数据库  
SELECT DATABASE();  
– 5.修改数据库  
ALTER DATABASE mybase CHARACTER SET UTF8;  
– 6.删除数据库  
DROP DATABASE mybase1;  
– 切换数据库  
USE mybase;

–二、表相关DDL

– 1.创建表  
create table exam(  
id INT(11) PRIMARY KEY AUTO\_INCREMENT,  
name VARCHAR(20),  
English INT,  
Chinese INT,  
Math int  
);  
– 2.查看数据库中所有表  
show TABLES;  
– 3.查看表结构  
desc exam;

– 4.表的删除  
drop table exam;

– 5.表的修改(基于exam表)

– 5.1添加列  
ALTER TABLE exam ADD History INT NOT NULL;  
– 5.2修改列的类型、长度、约束  
ALTER TABLE exam MODIFY History DOUBLE(7,2);  
– 5.3修改表的列名  
ALTER TABLE exam CHANGE History Physics INT NOT NULL;  
– 5.4修改表名  
RENAME TABLE exam TO score;  
– 5.5修改表的字符集  
ALTER TABLE score CHARACTER SET GBK;  
– 5.6删除列  
ALTER TABLE score DROP Physics;  
– 5.7

ALTER TABLE

– 三、练习:创建emp表

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 – 非空约束  
);

表exam:  
![在这里插入图片描述][20191121172343618.png]  
表emp:  
![在这里插入图片描述][20191121173216115.png]

### (3)数据操纵语言DMLupdate,insert,delete ###

-- 数据操纵语言DML(update,insert,delete)

– 1.插入部分列  
INSERT INTO score(id,NAME,English,Chinese,Math) VALUE(1,‘Hudie’,90,90,90);  
INSERT INTO score(id,NAME,English,Chinese) VALUE(NULL,‘diedie’,91,91);  
– 2.插入所有列  
INSERT INTO score VALUES(3,‘Shu’,80,80,80);  
– 3.修改记录  
UPDATE score set Chinese=99; –全表修改  
UPDATE score SET Math=100 WHERE id=‘1’;  
– 4.删除记录  
DELETE FROM score WHERE id=‘2’;  
DELETE FROM score;

– delete与truncate的区别 √   
–TRUNCATE TABLE 删除表的记录:将整个表删除掉,重新创建一个新的表,属于DDL.  
–DELETE FROM 删除表的记录:一条一条进行删除,DELETE.  
INSERT INTO score VALUES(3,‘Shu’,80,80,80);  
DELETE FROM score;  
INSERT INTO score VALUES(NULL,‘Libai’,10,10,10); –不会清空AUTO\_INCREMENT值  
TRUNCATE TABLE score;  
INSERT INTO score VALUES(NULL,‘Libai’,10,10,10); –清空AUTO\_INCREMENT的值

– 事务管理:只能作用在DML语句上,如果在一个事务中使用delete删除所有记录,可以找回.  
– 使用delete删除后可以用COMMIT和ROLLBACK找回数据,使用truncate后就找不回来了.

– delete、truncate、drop的区别 √  
delete、truncate、只是删除表的记录,而drop会直接删除表.

### (4)数据控制语言DCLgrant,revoke ###

-- 数据控制语言DCL(grant,revoke)
    -- 主要为用户授予和撤销权限

– 1.创建用户:CREATE USER 用户名@ip IDENTIFIED BY 密码;  
create user Fox@localhost identified by ‘123456’;

– 2.给用户授权:grank 权限1,权限2,…,权限n ON 数据库名.\* TO 用户名@IP;  
grant select,drop on mysql.\* to Fox@localhost;

– 3.撤销权限:REVOKE 权限1,权限2,…,权限n ON 数据库名.\* FROM 用户名@IP;  
revoke select on mysql.\* from Fox@localhost;

– 4.查看用户的权限:SHOW GRANTS FOR 用户名@IPl  
show grants for Fox@localhost;  
– 5.删除用户: DROP USER 用户名@IP;  
drop user Fox@localhost;  
– 6.登录:mysql -u 用户名-p 密码;  
mysql \-u root \-p  
– 7.退出登录: exit;  
exit;

### (5)数据查询语言DQLselect ###

exam表：  
![在这里插入图片描述][20191122111711880.png]

-- 数据查询语言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数据库练习题 ##

[练习题][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191121114713687.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191121114814886.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191121114905962.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[20191121172343618.png]: https://img-blog.csdnimg.cn/20191121172343618.png
[20191121173216115.png]: https://img-blog.csdnimg.cn/20191121173216115.png
[20191122111711880.png]: https://img-blog.csdnimg.cn/20191122111711880.png
[Link 1]: https://blog.csdn.net/qq_15719613/article/details/105520718