MySQL数据库总结

点击上方蓝字关注我们

一

数据库简介

　　数据库(Database，DB)是按照数据结构来组织，存储和管理数据的仓库。

　　典型特征：数据的结构化、数据间的共享、减少数据的冗余度，数据的独立性。

　　关系型数据库：使用关系模型把数据组织到数据表(table)中。现实世界可以用数据来描述。

　　主流的关系型数据库产品：Oracle(Oracle)、DB2(IBM)、SQL Server(MS)、MySQL(Oracle)。

　　数据表：数据表是关系数据库的基本存储结构，二维数据表有行(Row),和列(Column)组成，也叫作记录(行)和字段(列)。

二

MySQL数据类型(5.5版本)

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

三

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:　　

　　表emp:　　

(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 用户名@IPlshow 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表：

-- 数据查询语言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,orSELECT * FROM exam WHERE English > 90 AND Chinese >90;SELECT * FROM exam WHERE English < 90 or Math >99;-- 9.使用in,not inSELECT * 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 nullINSERT 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)*5SELECT * 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.deptnoWHERE dept.loc ='北京';-- 3.查看每个城市员工的平均工资SELECT dept.loc,ifnull(avg(sal),0)FROM emp right join depton emp.deptno =dept.deptnoGROUP BY dept.loc;-- 4.查看工作地点在上海的员工有哪些SELECT emp.*,dept.dname,dept.loc FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptnoWHERE dept.loc='上海';-- 5.查找和曾小贤同职位的员工SELECT *FROM empWHERE job=(SELECT job FROM emp WHERE ename='曾小贤');-- 6.查找薪水比整个机构平均水平高的员工SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);-- 7.查询出部门中有clerk但职位不是clerk的员工的信息SELECT * FROM empWHERE 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_salFROM emp GROUP BY deptno HAVING min_sal>(SELECT MIN(sal) FROM emp WHERE deptno=20);-- 10.列出所有员工的姓名及其直接上级的姓名SELECT e1.ename,e2.enameFROM emp e1 LEFT JOIN emp e2 ON e1.mgr = e2.empno;