文章目录
一、数据库简介
- 数据库(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 |
三、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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- 数据操纵语言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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- 数据控制语言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表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | -- 数据查询语言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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | -- 一、分组查询 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | 多张数据表或视图的查询叫做连接查询 -- 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数据库练习题
标签:
MySQL数据库总结