一、 编码
1、 查看MySQL数据库编码:SHOW VARIABLES LIKE ‘char%’
2、 编码解释:
- Character_set_client:MySQL将客户端传送的数据当成指定编码
- Character_set_results:MySQL发给客户端的指定类型。
- 通过 set character_set_client/result=gbk方式修改编码方式,同时修改后只在当前窗口有效,再my.ini中修改可以保证一劳永逸。
3、 数据库备份与恢复
- 备份:指将数据库转换成SQL脚本
- Mysqldump –u用户名 –p密码 数据库名>目录\xxx.sql
- 恢复
- Mysqldump –u用户名 –p密码 数据库<脚本文件路径 该操作需要在数据库外执行
- Source 备份文件 改操作直接在数据库内部执行
- 恢复的时候不能恢复数据库,只能恢复数据库内容,所以要保证数据库是存在的,如果不存在可以创建一个null的数据库
4、 约束:约束时添加在列上的,用来约束列
- 主键约束(非null、唯一、被引用(外键就是用来引用主键))
- 当表的某一列被指定为主键,该列就不能为null,同时不能有重复的出现。
- 创建表
CREATE TABLE stu(
Sid CHAR(5) PRIMARY KEY,指定sid为主键,即为sid列添加主键约束
Sname VARCH(20)
)
或者
CREATE TABLE stu(
Sid CHAR(5),
Sname VARCH(20),
PRIMARY KEY(sid)
)
ALTER TABLE stu ADD PRIMARY KEY(sid)修改表时指定主键
ALTER TABLE stu DROP PRIMARY KEY;删除主键,不用指定主键名字
ALTER TABLE tableName
ADD 添加列
MODIFY 修改列名和列类型
CHANGE 修改列
DROP 删除
RENAME TO 重新命名
- 主键自增长,主键通常会专门指定一个列,选择一个没有意义的作为主键,一般使用uuid作为主键。
CREATE TABLE stu(
Sid INT PRIMARY KEY AUTO_INCREMENT,必须时整数,可以指定也可以不指定,不指定的时候自增
Sname VARCH(20)
)
- 非null约束,要求添加的列不能为null,在列后添加NOT NULL.
- 唯一约束,表明该列不能设置唯一值,NOT NULL UNIQUE
二、 概念模型、对象模型和关系模型
1、 概念模型
- 根据系统中状况抽象出实体,一般概念模型对应着Java中的列
2、 对象模型:java中的domian类,可以双向关联,引用的是一个对象而不是一个主键
3、 关系模型:数据库中的表,一对一、一对多、多对多。只能多方引用一方,只能是主键,而不能是一整行记录就。
三、 外键约束:CONSTRAINT fk_emp_dept FOREIGN KEY(主表列名) REFERENCES 子表(子表主键)
1、 外键必须是另一个表中的主键值。
2、 外键可以重复。
3、 外键可以为null。
4、 一张表中可以有多个外键。
四、 一对一关系:需要让其中一张表的主键既是主键又是外键。创建方法类似一对多外键方式。会自动检擦
五、 多对多关系两张表都是主表,新增一个关系表,关联表中uuid不是次序的
六、 多表查询,分为合并结果集、链接查询和子查询
1、 合并结果集,要求两表的表结构必须一致,列名显示第一个查询表的列名,UNICN去除重复行,UNICN ALL不去除重复行。
- SELECT * FROM ab UNICN [ALL] SELECT * FROM cd;要求ab和cd表的结构相同
- SELECT id FROM ab UNICN [ALL] SELECT id FROM cd ;此时不要求ab和cd表结构相同,只需要查询出来的列的结构相同即可合并。
2、 链接查询一次查询多张表,同时分为内连接、外连接和自然连接,内连接又可以分为左连接、有链接和全连接。
- 内连接查出所有表中都满足条件的记录
- 方言:SELECT * FROM table1 别名1,table2 别名2 WHERE 别名1.xx=别名2.xx
- 标准:SELECT * FROM table1 别名1 INNER JOIN table2 别名2 ON 别名1.xx=别名2.xx
- 自然:SELECT * FROM table1 别名1 NATURE JOIN table2 别名2
- 外连接
- 左:SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx 左表记录全部查出+右表满足条件的记录
- 左自然:SELECT * FROM table1 别名1 NATURE LEFT OUTER JOIN table2 别名2 ON 别名1.xx=别名2.xx
- 右外:SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx 右表记录全部查出+左表满足条件的记录
- 右自然:SELECT * FROM table1 别名1 NATURE RIGHT OUTER JOIN table2 别名2 ON 别名1.xx=别名2.xx
- 全连接:可以使用UNION来完成连接。
3、 子查询子查询一般出现在from后作为表和where后面作为条件
- SELECT * FROM emp WHERE sal> [ALL\ANY](SELECT sal FROM emp WHERE job=’’)
七、 联系题
1、 查出至少有一个员工的部门,显示部门编号、部门名称、部门位置和部门人数
SELECT d.*,z.cnt
FROM dept d, (SELECT DEPTNO, COUNT(*) cnt FROM EMP GROUP BY deptno) z
WHERE d.deptno=z.deptno
2、 列出薪资不关于高的所有工资
3、 列出所有员工的姓名及其直接上级的姓名
SELECT e.ename 员工姓名, IFNULL(“e2.ename”,”boss”) 上级领导
FROM emp e1 LEFT OUTER JOIN emp e2
WHERE e1.mgr=e2.empno
4、 列出受雇日期遭遇直接上级的所有员工的编号姓名和部门名称
SELECT e.empno, e.ename, d.dname
FROM emp e1, emp e2, dept d
WHERE e1.hiredate< e2.hiredate AND e1.mgr=e2.empno AND e1.deptno=d.deptno
5、 列出部门名称和这些部门的员工信息,同时列出哪些没有员工的部门
SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno
6、 列出所有文员的姓名和其部门名称,部门的人数
SELECT
FROM
WHERE
7、 列出最低薪资大于150000的各种工作及其从事此工作的员工人数
SELECT job COUNT(*)
FROM emp e
GROUP BY job
HEAVING MIN(sal)>15000
8、 列出在销售部工作的员工姓名、假定不知道销售部门的编号
SELECT e.ename
FROM emp e, dept d
WHERE d.dname=”销售部” AND d.deptno=e.deptno
9、 列出薪资高于平均薪资的所有员工信息,所在部门名称和上级领导、工资等级
10、 列出与庞统从事相同工作的所有员工及部门名称
11、 列出薪资高于部门30工作的所有有员工的信息的员工姓名和薪资、部门名称
12、 列出每个部门的员工数量和平均工资
13、 查出年份、利润、年度增长比。