MySQL
1.数据库的分类
关系型数据库：（SQL）
-
MySQL，Oracle，Sql Server， DB2，SQLlite
-
通过表和表之间，行和列之间的关系进行数据的存储，学员表信息，考勤表
非关系型数据库：（NoSQL）
-
Redis，MongDB
-
非关系型数据库，对象存储，通过对象的自身属性来决定
DBMS（数据库管理系统）
-
数据库的管理软件，科学有效的管理我们的数据，维护和获取数据;
-
MySQL本质上就是数据库管理系统
2.操作数据库（了解）
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] westos；
2.删除数据库
DROP DATABASE [IF NOT EXISTS] westos;
3.使用数据库
USE school
4.查看数据库
SHOW DATABASE --查看所有数据库
3.数据库的列类型
数值
-
tinyint 十分小的数据 1个字节[有符号-128~127；无符号0~255]
-
smallint 较小的数据 2个字节
-
mediumint 中等大小的整数 3个字节
-
int 标准的整数 4个字节 常用的
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节 （精度问题）
-
decimal 字符串形式的浮点数 金融计算的时候一般使用
字符串、文本、二进制类型
-
char 字符串 固定大小 0~255
-
varchar 可变字符串 0~65535
-
tinytext 微型文本 2^8 - 1
-
text 文本串 2^16 - 1
-
longtext 长文本串 2^32 - 1
-
blob 二进制数据类型 2^16 - 1
-
longblob 二进制数据类型 2^32 - 1
时间日期
java.util.Data
-
date YYYY-MM-DD 日期格式
-
time HH:mm:ss 事件格式
-
datetime YYYY-MM-DD HH:mm:ss 最常用时间格式
-
timestamp 时间戳 1970.1.1 到现在的毫秒数
-
year 年份表示
null
-
没有值 ，未知
-
==注意，不要使用NULL进行运算，结果为NULL
4.数据库的字段属性（重点）
Unsigned：
-
无符号的整数
-
声明了该列不能为附属
zerofill：
-
0填充的
-
不足的位数，使用0来填充
自增：
-
通常理解为自增，自动在上一条记录的基础上+1（默认）
-
通常用来设计唯一的主键 index，必须是整数类型
-
可以自定义设计主键自增的起始值和步长
非空 NULL not null
-
假设设置为 not null，如果不给他赋值，就会报错
-
NULL，如果不填写值，默认就是null
默认：
-
设置默认的值！
-
sex，默认值为男，如果不指定该列的值，则会有默认的值
扩展
/*每一个表，都必须存在以下五个字段，未来做项目用的，表示一个记录存在的意义*/ id 主键 verson 乐观锁 id_delete 伪删除 gmt_create 创建时间 gmt_update 修改事件
5.Mysql三层结构
简单来说一个指令首先打入到3306端口，然后传递到DBMS，DBMS进行解读后，找到要操作的对应的数据库及表，操作完成后再返回命令终端
SQL语句分类
DDL：数据定义语句[创建表，库]
DML：数据操作语句（增删改查）
DQL：数据查询语句[select]
DCL：数据控制语句[管理数据库 grand revoke]
6.Java操作MySQL
public class JavaMysql {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//如何用Java来操作Mysql
//加载类，得到mysql连接
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc.mysql://localhost:3306");
//创建一个商品hsp_goods表，选用适当的数据类型
//添加2条数据
//删除表goods
//这里可以编写sql
String sql = "create table hsp_goods (id int,name varchar(32),price double,introduce text)";
//得到statement对象，把sql 语句发送给mysql执行
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
}
}
7.创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name --如果不写if not exists，若存在表则会报错 [DEFAULT]CHARACTER SET charset_name [DEFAULT]COLLATE collation_name
1.CHARACTER SET:指定数据库采用的字符集，默认为utf8
2.COLLATE:指定数据库字符集校对规则（常用的 utf8_bin[区分大小写]；utf8_general_ci[不区分大小写]）默认用ci
例如：创建一个使用utf8字符集并带校对规则的的db02数据库
在创建数据库时，为了规避关键字，可以打上反引号``
CREATE DATABASE db02 CHARACTER SET utf8 COLLATE utf8_bin
8.查询数据库
显示数据库语句
SHOW DATABASES
显示数据库创建语句
SHOW CREATE DATAVASE db_name
数据库删除语句[一定要慎用]
DROP DATABASE [IF EXISTS] db_name
9.备份恢复数据库
备份数据库（在DOS执行）
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库3 >文件名.sql
恢复数据库（注意：进入Mysql命令行再执行）
Source 文件名.sql
第二个恢复方法：直接将文件的内容放到查询编辑器中，执行
备份库的表
mysqldump -u 用户名 -p 数据库 表1 表2 表n > d:\\文件名.sql
注意：如果写了-B，会默认后面的表都为数据库，导致存储错误
10.创建表
CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype, )character set 字符集 collate 校对规则 engine 引擎
field：指定列名
datatype：指定列类型（字段类型）
character set 如不指定，则为所在数据库字符集
collate 如不指定，则为所在数据库校对规则
11.列类型之整形
12.列类型之bit
255按二进制来算刚好8个1，可以存储进去
默认是BIT（1）也就是存储0~64
13.列类型之小数
数值型（小数）的基本使用
1.FLOAT/DOUBLE [UNSIGNED]
Float是单精度，Double是双精度
2.DECIMAL[M,D] [UNSIGNED]
-
可以支持更加精确的小数位，M是小数位数（精度）的总数，D是（标度）小数点后面的位数
-
如果D是0，则值没有小数点或分数部分。M最大是65，D最大是30。如果D被省略，默认是0。如果M被省略，默认是10
以上是三个小数类型的存放结果
13.列类型之字符串
字符串的基本使用
-
CHAR（size）
固定长度字符串 最大255字符
-
VARCHAR（size）
可变长度字符串 最大65532字节 【utf8编码最大21844字符 1~3个字节用于记录大小；GBK的话用2个字符记录大小，即（65535-3）%2=32766】
细节1：
1.char（4） //这里的4表示字符数（最大255），不是字节数，不管中文还是字母都是放四个，按字符计算
2.varchar（4） //这个4表示字符数，不管是字母还是中文都已定义好的表的编码来存放数据（占用多少字节，取决于你的编码）
细节2：
char（4）是定长，就是说，即使你插入”aa“，也会占用分配的四个字符
varchar（4）是变长，就是说，如果你插入了”aa“，实际占用并不是4个字符，二十按照实际占用空间来分配（varchar本身需要1~3个字节来记录存放内容长度）
细节3：
什么时候用char，什么时候用varchar
1.如果数据是定长，推荐用char，比如md5加密的密码，邮编，手机号，身份证号等 char（32）
2.如果一个字段的长度是不确定的用varchar，比如留言，文章
查询速度：char>varchar
细节4：
14.列类型之日期类型
日期类型的基本使用
CREATE TABLE birthday6
(t1 DATE,t2 DATETIME,
t3 TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP );timestamp时间戳
mysql>INSERT INTO birthday (t1,t2)
VALUES('2022-11-11','2022-11-11 10:10:10');
TimeStamp在insert和update时，自动更新
演示如下：
15.创建表练习
16.修改表
17.Insert的基本使用
基本使用
INSERT INTO table_name[(culumn [,column...]）] VALUES （value [,value...])
具体如图
注意事项
-
插入的数据应与字段的数据类型相同，否则报错
-
数据的长度应在列的规定范围内
-
在values中列出的数据位置必须与被加入的列的排列位置相对应
-
字符和日期型数据应包含在单引号中
-
列可以插入空值[前提是允许为空] insert into table value（null）
-
insert into tab name （列名...）value (),(),()形式添加多条记录
-
如果是给表中的所有字段添加数据，可以不写前面的字段名
-
默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
想指定默认值的话，在创建表的时候写DEFAULT
18.update语句
UPDATE tbl_name STE col_name1=expr1 [col_name2=expr2...] [WHERE where_definition]
如果不带WHERE语句，会修改全部语句
使用细节
-
UPDATE语句可以用新值更新原有表行中的各列
-
SET语句指示要修改那些列和要给予那些值
-
WHERE语句指定应更新哪些行，如果没有WHERE语句，则更新所有行
-
如果需要修改多个字段，可以通过 set字段1=值1，字段2=值2
19.delete语句
delete from tb1_name [WHERE where_definition]
使用细节
-
如果不适用where语句，将删除表中的所有数据
-
Delete语句不能删除某一列的值（可使用update 设置为null或者''）
-
使用delete语句仅删除记录，不能删除表本身。如果要删除表，使用drop table语句
20.select语句（最重要）
SELECT使用表达式对查询的列进行运算
SELECT *|{column1|expression，column2|expression，..}
FROM tablename；
也可以使用as语句
SELECT columnname as 别名 from 表名；
在WHERE子句中经常使用的运算符
比较运算符IN的用处
order by 子句排序查询结果
SELECT column1,column2,column3... FROM table; order by column asc|desc .....
-
Order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名
-
Asc升序[默认] Desc[降序]
-
ORDER BY 子句应位于SELECT语句的结尾
21.合计/统计函数
Count返回行的总数
Select count（*）|count（列名） from tablename [WHERE where_definition]
count * from会导致即使有空值也会计算，因为它计算的是有存入数据的
但是count * from 列 就不会出现这种情况
合计函数 -sum
Sum函数返回满足where条件的行的和 一般使用在数值列
SELECT SUM(列名) {SUM(列名)...} FROM tablename
[WHERE where_definition]
总结
Count更多是用在统计人数，数量上面
Sum更多用在统计总和，统计数值上面
合计函数 -Max/Min
Max/Min函数返回满足where条件的一列的最大/最小值
SELECT MAX(列名) FROM tablename [WHERE where_definition]
合计函数 -Avg
SELECT AVG(列名) {AVG(列名)...} FROM tablename
[WHERE where_definition]
22.分组统计
使用group by 子句对列进行分组
SELECT column1，column2，columu3.. FROM table GROUP BY column
使用having 子句对分组后的结果进行过滤
SELECT column1，column2，column3... FROM table GROUP BY column HAVING ...
计算薪资小于2000的部门号和他的平均工资
使用别名不需要进行第二次计算，效率更快
23.字符串函数
标红的就是常用的
-
CONCAT
连接字符串，将多个列拼接成一个列
SELECT CONCAT(ename,`工作是`,job) FROM emp;
最终效果：
-
INSTR 其中DUAL是系统自带的一个表，可以用来测试（亚元表
SELECT INSTR('hanshunping'，‘ping’) FROM DUAL;
代表‘ping’在字符中第八个出现
-
UCASE 转换成大写
SELECT UCASE(ename) FROM emp；
将这一列的所有文字都转换成大写
-
REPLACE
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;
如果是查询到是MANAGER就替换成经理
-
SUBSTRING
SELECT SUBSTRING(ename,1,2) FROM emp;
从ename列的第一个位置开始取出2个字符
练习题以及练习效果
24.数学函数
-
CONV
SELECT CONV(8,10,2) FROM DUAL;
认为8是10进制，将其转换为2进制
-
FORMAT
SELECT FORMAT(78.123443,2) FROM DUAL;
会对数字进行四舍五入保留两位小数
-
RAND
如果使用rand每次都会返回随机数字，加入了seed后，seed不变，值就不会便
25.日期函数
那么显示时间的意义呢？
如图，可以在存入数据的时候，顺便存入时间
时间日期相关函数
--显示所有新闻信息，发布日期只显示日期，不显示时间
SELECT id,content, DATE(send_time)
FROM mes;
--请查询在10分钟内发布的新闻
SELECT *
FROM mes
WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE)>=NOW()
SELECT *
FROM mes
WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE)<=send_time
--请用mysql的sql语句中求出2011-11-11和1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11'，'1990-1-1') FROM DUAL;
--请用mysql的sql语句求出你活了多少天
SELECT DATEDIFF(NOW(),'2004-4-26') FROM DUAL;
--如果你能活100岁，求出你还能活多少天
SELECT DATEDIFF( DATE_ADD('2004-4-26',INTERVAL 100 YEAR),
NOW()) FROM DUAL;
以上函数的细节说明
-
DATE_ADD()中的 interval 后面可以是 year minute second day等
-
DATE_SUB()中的 interval 后面可以是 year minute second day等
-
DATEDIFF(date1，date2)得到的是天数，而且是date1-date2的天数，因此可以得到负数
-
这四个函数的日期类型可以是date，datetime 或者timestamp
--YEAR|MONTH|DAY|DATE(datetime)
SELECT YEAR(NOW()) FROM DUAL; SELECT MONTH(NOW()) FROM DUAL; SELECT DAY(2023-1-4) FROM DUAL;
--unix_timestamp（）返回的是1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
--FROM_UNIXTIME()可以把一个nuix_timestamp 秒数，转成指定格式的日期
SELECT FROM_UNIXTIME(1618483484,'%Y-%M-%D') FROM DUAL;
意义：在开发中，可以存放一个整数，然后表示时间，通过这个函数进行转换
26.加密函数
--USER（）查询用户
SELECT USER() FROM DUAL; --用户@IP地址
--MD5（str） 为字符串算出一个MD5 32的字符串，常用（用户密码）加密
SELECT MD5('ZKW') FROM DUAL;
SELECT *
FROM user WHERE `name`='ZWK' AND pwd = MD5('ZKW')
--PASSWORD(str) --加密函数,MySQL数据库的用户密码就是用这个加密
SELECT PASSWORD('ZKW') FROM DUAL
MD5加密的密码，无论输入多少，都是32位的
27.流程控制函数
#IF(expr1，expr2，expr3) 如果expr1为Ture，则返回expr2，反之expr3 SELECT IF(TRUE,'北京'，'上海') FROM DUAL; #IFNULL(expr1，expr2) 如果expr1不为空，则返回expr1，反之expr2 SELECT IFUNLL(NULL,'张柯威') FROM DUAL; #SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 EMD； 如果expr1为T，则返回expr2，如果expr3为T，返回expr4，否则返回expr5 SELECT CASE WHEN TRUE THEN 'jack' WHEN FALSE THEN 'tom' ELSE 'mary' END --判断为空用的是is null，不为空是is notnull --1.查询emp表，如果comm是null，则显示0.0 SELECT ename，IF(comm IS NULL , 0.0 , comm) FROM emp； --2.如果emp表的job是CLERK则显示职员，MANAGER 显示经理，其他正常 SELECT ename，(SELECT CASE WHEN job = 'CLERK' THEN '职员' WHEN job = 'MANAGER' THEN '经理' ELSE job END) AS 'job'，job FROM emp；
28.查询加强（多表查询）
#使用where子句 --查询1002.1.1后入职的员工 SELECT * FROM emp WHERE hiredate > '1992-01-01' #使用like操作符(模糊查询) --%：表示0到多个任意字符 _：表示任意单个字符 --如何侠士首字符为S的员工姓名和工资 SELECT ename,sal FROM emp WHERE ename LIKE 'S%' --如何显示第三个字符为大写O的所有员工姓名和工资 SELECT enane,sal FROM emp WHERE ename LIKE '__O%' --如何显示没有上级的员工 SELECT * FROM emp WHERE mgr IS NULL; --查询表结构 DESC emp #使用ORDER BY 子句 --如何按照工资从低到高，显示员工信息 SELECT * FROM emp ORDER BY sal ASC --按照部门号升序，显示员工工资降序，显示员工信息 SELECT * FROM emp ORDER BY deptno ASC ,sal DESC
说明：在mysql中日期类型可以直接比较
29.分页查询
#按员工的id号升序取出，每页显示3条记录，请分别显示这三页 --第1页 SELECT * FROM emp ORDER BY id LIMIT 0,3； --第2页 SELECT * FROM emp ORDER BY id LIMIT 3,3； --第3页 SELECT * FROM emp ORDER BY id LIMIT 6,3； --推导一个公式： SELECT * FROM emp ORDER BY id LIMIT 每页显示记录数*（第几页-1），每页显示记录数
基本语法
SELECT ... LIMIT START,ROWS 表示从start+1行开始取出，取出rows行，start从0开始计算
30.分组增强
#使用分组函数和分组句子 group by --1.显示每种岗位的员工总数，平均工资 SELECT COUNT(*),AVG(sal),job FROM emp GROUP BY job; --2.显示员工总数，以及获得的补助的员工数(若comm为null，则不会统计) SELECT COUNT(*),COUNT(comm) FROM emp; --统计没有获得补助的 SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL)) FROM emp; SELECT COUNT(*),COUNT(*) - COUNT(comm) FROM emp; --3.显示管理者的总人数(DISTINCT去重) SELECT COUNT(DISTINCT mgr) FROM emp; --4.显示员工工资的最大差额 SELECT MAX(sal) - MIN(sal) FROM emp;
31.多子句查询
#应用案例：统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录 SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 1000 ORDER BY avg_sal DESC LIMIT 0,2
32.多表笛卡儿集（import）
--多表查询 --显示员工的名字，工资以及所在部门的名字（笛卡尔集） SELECT ename,sal,dname,emp.deptno(查询固定的) FROM emp,dept WHERE emp.deptno = dept.deptno --如何显示部门号为10的部门名，员工名和工资 SELECT dname,ename,sal FROM emp,dept WHERE emp.deptno = 10 --显示各个员工的姓名，工资，及其工资的级别 SELECT ename,sal,grade FROM emp,salgrade WHERE sal BETWEEN losal AND hisal;
SELECT*FROM emp,dept
在默认情况下：当两个表查询时，规则：
-
从第一张表中，取出每一行和第二张表的每一行进行组合，返回这个结果
-
一共返回的记录数，第一张表的行数乘以第二张的记录
-
这压根多表查询默认处理返回的结果，称为笛卡尔集
-
解决这个多表的关键就是要写出正确的过滤条件where，需要程序员分析
夺标查询的条件不能少于表的个数-1
33.多表查询（import）
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
34.自连接
自连接是指在同一张表的连接查询[将同一张表看作两张表]
利用别名
#显示公司员工和他上级的名字 SELECT worker.ename AS '职员名',boss.ename AS '上' FROM emp worker,emp boss WHERE worker.mgr = boss.empno; SELECT
35.多行子查询
-
单行子查询
单行子查询是指只返回一行数据的子查询语句
-
多行子查询
多行子查询指返回多行数据的子查询 使用关键字in
单行子查询案例
--如何显示与SMITH同一部门的所有员工 SELECT deptno FROM emp WHERE ename = 'SMITH' //获取了SMITH的编号 然后查询与他一个部门的编号 SELECT * FROM emp WHERE deptno = ( SELECT deptno FROM emp WHERE ename = 'SMITH'; )
多行子查询案例
--如何查询和部门10的工作相同的员工的名字，岗位，工资，部门号，但不是汉10部门自己的雇员 SELECT DISTINCT job FROM emp WHERE deptno = 10; --下面是完整语句 SELECT ename,job,sal,deptno FROM emp WHERE job IN ( SELECT DISTINCT job FROM emp WHERE deptno = 10 ) AND deptno != 10;
36.子查询临时表
--查询ecshop中各个类别中，价格最高的商品 --查询 商品表 --先得到 各个类别中，价格最高的商品max + group by cat_id当作临时表 SELECT cat_id,MAX(shop_price) FROM ecs_goods GROUP BY cat_id SELECT good_id,ecs_goods.cat_id,goods_name,shio_price FROM ( SELECT cat_id,MAX(shop_price) AS max_price FROM ecs_goods GROUP BY cat_id )temp,ecs_goods WHERE temp.cat_id = ecs_goods.cat_id AND temp.max_price = shop_price
37.all和any
使用all
--如何显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号 SELECT ename,sal,deptno FROM emp WHERE sal > ALL( SELECT sal FROM emp WHERE deptno = 30 )
使用any
--如何显示工资比部门30的其中一个员工的工资高的员工的姓名，工资和部门号 SELECT ename,sal,deptno FROM emp WHERE sal > ANY( SELECT sal FROM emp WHERE deptn = 30 )
38.多列子查询
多列子查询则是指查询返回多个列数据的子查询的语句
--如何查询与smith的部门和岗位完全相同的所有的员工，并且不含smith本人 SELECT * FROM emp WHERE (deptno,job) = ( SELECT deptno,job FROM emp WHERE enmae = 'SMITH' )AND ename != 'SMITH' SELECT * FROM student WHERE(math,english,chinese) = ( SELECT math,english,chinese FROM student WHERE ename = '宋江' )
39.子查询练习
--查询每个部门工资高于本部门平均工资的人的资料 SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno SELECT ename,sal,temp.avg_sal,emp.deptno FROM emp,( SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno )temp WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal
--查找每个部工资最高的人的详细资料 SELECT deptnp,MAX(sal) AS max_sal FROM emp GROUP BY deptno SELECT ename,sal,temp.max_sal,emp.deptno FROM emp,( SELECT deptnp,MAX(sal) AS max_sal FROM emp GROUP BY deptno )temp WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal
--显示每个部门的信息（包括：部门名，编号，地址）和人员数量 SELECT COUNT(*), deptno FROM emp GROUP BY deptno; SELECT dname,dept.deptno,local,tmp.per_num AS '人数' FROM dept,( SELECT COUNT(*) AS per_num, deptno FROM emp GROUP BY deptno )tmp WHERE tmp.deptno = dept.deptno 写法：表名.* 表示将表的所有列都显示出来
40.表复制和去重
--先创建一个表 mysql> CREATE TABLE my_tab01 -> (id INT, -> `name` VARCHAR(32), -> job VARCHAR(32), -> sal DOUBLE, -> deptno INT); --1.先把emp表的记录复制到my_tab01 INSERT INTO my_tab01 (id,`name`,sal,job,deptno) SELECT empno,ename,sal,job,deptno FROM emp; --2.自我复制 INSERT INTO my_tab01 SELECT * FROM my_tab01; #为了对某个sql语句进行效率测试，我们需要海量数据的时候，可以用此方法为表创建海量数据
#面试题：如何删除一张表中的重复记录 mysql> CREATE TABLE my_tab02 LIKE my_tab01; 创建一张结构类似的表 /* 1.先创建一张临时表my_tmp，该表的结构和my_tab02一样 2.把my_tmp的记录通过distinct关键字处理后，把记录复制到my_tmp 3.清除掉my_tab02的记录 4.把my_tmp表的记录复制到my_tab02 5.drop掉临时表my_tmp */ CREATE TABLE my_tab LIKE my_tab02; INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02; DELETE FROM my_tab02; INSERT INTO my_tab02 SELECT * FROM my_tmp; SELECT * FROM my_tab02;
41.合并查询
union all和union
--union all 就是将两个查询结果合并，不会去重 SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALL SELECT ename,sal,job FROM emp WHERE job='MANAGER' --union 与union all基本一样，可以去除重复的 SELECT ename,sal,job FROM emp WHERE sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job='MANAGER'
42.外连接需求
--外连接 --列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门 SELECT dname,ename,job FROM emp,dept WHERE emp.deptno = dept.deptno ORDER BY dname #匹配不上的数据，无法显示，这时候就需要外连接
43.左外连右外连
-
左外连接（如果左侧的表完全显示我们就说是左外连接）
-
右外连接（如果右侧的表完全显示我们就说是右外连接）
--使用左外连接显示所有人的成绩，如果没有成绩，也要显示姓名和id，成绩显示为空 SELECT `name`,stu.id,grade FROM stu,exam WHERE stud.id = exam.id; ----------------改成左连接-------------------- SELECT `name`,stu.id,grade FROM stu LEFT JOIN exam 表1 left join 表2 ON stu.id = exam.id; on后跟条件 #表1就是左表，表2就是右表
--右外连接显示所有成绩，如果没有名字，显示空 #右边的表exam和左表没有匹配的记录，也会把右表的记录显示出来 SELECT `naem`,stu.id,grade FROM stu RIGHT JOIN exam ON stu.id = exam.id;
44.主键
约束有not null，unique，primary key，foreign key，check
--创建主键 CREATE TABLE pk ( -> id INT primary key, -> `name` VARCHAR(32), -> email VARCHAR(32)); --主键列的值是不可以重复的
注意：
-
主键不能重复且不能为null
-
一张表最多只能有一个主键，但是可以复合主键
--错误的 CREATE TABLE pk ( -> id INT primary key, -> `name` VARCHAR(32),primary key -> email VARCHAR(32)); --正确的（复合主键）id和name做成复合主键 CREATE TABLE pk ( -> id INT, -> `name` VARCHAR(32), -> email VARCHAR(32） -> PRIMARY KEY(id,`name`));---复合主键 #当id和name同时为主键的时候，需要id和name都相同，才会存入失败，若id相同，name不同，是可以存入的
-
主键的指定方式有两种
1.直接在字段名后指定：字段名 primary key
2.在表定义最后写 primary key（列名）；
-
使用desc表名，可以看到primary key 的情况
45.unique 唯一约束
使用细节
-
如果没有指定not null ，则unique字段可以有多个null
INSERT INTO t21 VALUES(NULL,'TOM','TOM@QQ.COM') --没有在unique后面添加not null时，是可以存入的
-
一张表可以有多个unique字段
-
如果一个列（字段），是unique not null 使用效果类似 primary key
46.外键介绍 foreign key
若设置外键后，在添加class_id为300的人，会添加失败
同时，设置了外键后，若要删除班级表中id为200的一行，必须要先删除学生表，也就是外键的id为200的一行，否则会失败
47.外键的使用细节
用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数必须在主表的主键列存在或是为null
FOREIGN KEY(本表字段名) REFERENCES 主表名(主键名或unique字段名)
--创建主表 mysql> CREATE TABLE my_class ( -> id INT PRIMARY KEY, -> `name` VARCHAR(32) NOT NULL DEFAULT ''); --创建从表 mysql> CREATE TABLE my_stu ( -> id INT PRIMARY KEY, -> `name` VARCHAR(32), -> class_id INT, --加入外键元素-- -> FOREIGN KEY (class_id) REFERENCES my_class(id)); --测试数据 mysql> INSERT INTO my_class -> VALUES(100,'java'),(200,'web'); Query OK, 2 rows affected (0.00 sec) mysql> INSERT INTO my_stu -> VALUES(1,'tom',100),(2,'jack',200); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 #添加两条学生记录都成功了 mysql> INSERT INTO my_stu -> VALUES(3,'hsp',300); 1452 - Cannot add or update a child row: a foreign key constraint fails (`db01`.`my_stu`, CONSTRAINT `my_stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)) #添加id为300的失败了，因为主表里面没有id为300的 mysql> SELECT * FROM my_stu; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | tom | 100 | | 2 | jack | 200 | +----+------+----------+ 2 rows in set (0.04 sec) #最终查询效果
小细节
-
外键指向的表的字段，要求是primary key或者是unique
-
表的类型是（存储引擎）innodb，这样的表才支持外键
-
外键字段的类型要和主键字段的类型一致（长度可以不同）
-
外键字段的值，必须在主键字段中出现过，或者为null（前提是外键字段允许为null）
-----------------------存入失败--------------------------- mysql> INSERT INTO my_stu -> VALUES(3,'hsp',300); 1452 - Cannot add or update a child row: a foreign key constraint fails (`db01`.`my_stu`, CONSTRAINT `my_stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)) -----------------------存入成功------------------------- mysql> INSERT INTO my_stu -> VALUES(3,'ally',NULL); Query OK, 1 row affected (0.00 sec)
-
一旦建立主外键关系，数据就不能随意删除了
48.check
用于强制性数据必须满足的条件
oracle 和 sql server 均支持check ，但是mysql5.7目前还不支持check，只做语法校验，但不会生效
CREATE TABLE t23
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK (sal>1000 AND sal<2000));
#在check条件的语句中添加不符合的数据会报错
49.商店表设计
#商品表
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
goods_name VARCHAR(32) NOT NULL DEFAULT '',
unitprice DOUBLE NOT NULL DEFAULT 0
CHECK(unitprice >=1.0 AND unitprice <=9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(32) NOT NULL DEFAULT '',
);
#客户表
CREATE TABLE customer (
customer_id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
address VARCHAR(32) NOT NULL DEFAULT '',
email VARCHAR(32) UNQUIE,
sex VARCHAR(6) (sex IN('man','woman'))
--sex ENUM('男','女') NOT NULL, --枚举类型
card_id VARCHAR(18) UNQUIE,
);
#购买表
CREATE TABLE purchase(
order_id INT PRIMARY KEY,
customer_id INT NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
nums INT,
FOREIGN KEY(customer_id)REFERENCES customer(customer_id),
FOREIGN KEY(goods_id)REFERENCES goods(goods_id)
);
50.自增长
字段名 整型 primary key auto_increment
#创建一张id带自增长的表
mysql> CREATE TABLE t24 (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> email VARCHAR(32) NOT NULL DEFAULT '',
-> `name` VARCHAR(32) NOT NULL DEFAULT '');
#此时往里添加数据
mysql> INSERT INTO t24 VALUES (NULL,'JACK@QQ.COM','JACK');
+----+-------------+------+
| id | email | name |
+----+-------------+------+
| 1 | JACK@QQ.COM | JACK |
+----+-------------+------+
#如果此时再添加数据
mysql> INSERT INTO t24
-> VALUES(NULL,'TOM@QQ.COM','TOM');
#同时也可以
mysql> INSERT INTO t24
-> (email,`name`) VALUES('hsp@qq.com','hsp')
--id也会实现自增长
+----+-------------+------+
| id | email | name |
+----+-------------+------+
| 1 | JACK@QQ.COM | JACK |
| 2 | TOM@QQ.COM | TOM |
+----+-------------+------+
自增长使用细节
-
一般配合主键使用
-
也可以单独使用，但需要配合一个unique
-
自增长的修饰字段为整数类型的（也可以为小数，极少使用）
-
自增长默认从1开始，也可以通过如下明空修改
alter table 表名 auto_increment = xxx;
51.索引 优化速度
索引不需要加内存，不需要改程序，不用调sql，查询速度可以提高千倍百倍
#创建索引 CREATE INDEX empno_index ON emp(empno);
52.索引机制
索引的代价
-
磁盘占用
-
对dml(update delete insert)语句的效率影响
索引的原理
当没有索引的时候，会进行一下全表的扫描，所以比较慢，即使第一次就找到了该id，系统不确定下面是否还有此id，所以会继续进行全表扫描
加入了索引后，会形成二叉树，从而进行更快的比较
==]=
如果对表进行dml操作，会对索引进行维护，所以会对速度产生影响
为什么索引有这些缺点还要用索引呢？
因为项目中，select 占90%，而update，delete，insert只占10%，而且造成服务器压力的主要是大量的访问，而不是修改
53.创建索引
索引类型
-
主键索引
CREATE TABLE t1( id INT PRIMARY KEY, --是主键，同时也是索引，称为主键索引 `name` VARCHAR(32) );
-
唯一索引
CREATE TABLE t2( id INT UNIQUE, --id是唯一的，同时也是索引，成为unique索引 `name` VARCHAR(32) );
-
普通索引（用的最多）
-
全文索引（适用于MyISAM）开发中一般用Solr和ElasticSearch
，不使用mysql自带的全文索引
#查询表是否有索引 SHOW INDEXES FROM t25; #添加索引 --添加主键索引 ALTER TABLE t25 ADD PRIMARY KEY(id); --添加唯一索引 某列的值不会重复，优先考虑unique CREATE UNIQUE INDEX id_index ON t25(id); --添加普通索引 方法1 CREATE INDEX id_index ON t25(id); --添加普通索引 方法2 ALTER TABLE t25 ADD INDEX id_index(id);
54.删除索引
--删除索引 DROP INDEX id_index ON t25; --删除主键索引 ALTER TABLE t25 DROP PRIMARY KEY; --不需要查询，因为每个表只有一个主键 #查询索引 --1.方式 SHOW INDEX FROM t25; --2.方式 SHOW INDEXES FROM t25; --3.方式 SHOW KEYS FROM t25; --4.方式 DESC t25 --不如以上详细
修改索引的话，就先删除后添加
CRETAE TABLE sportman ( id INT, name VARCHAR(32), good VARCHAR(32) ); CREATE INDEX id_index ON sportman(id); ALTER sportman ADD INDEX id_index(id);
55.创建索引规则
-
较频繁的作为查询条件的字段应该创建索引
-
唯一性太差的字段，不适合单独创建索引，即使作为频繁查询条件
-
更新非常频繁的字段不适合创建索引
-
不会出现再WHERE子句中字段不该创建索引
56.事务有什么用
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败
可以理解为：将几个语句看作一个整体，要么全成功，要么全失败
在执行事务操作的时候，mysql会在表上加锁，防止其他用户修改表的数据，这对用户来说，非常重要
57.事务的操作
#创建一张测试表 CREATE TABLE t27( id INT, `name` VARCHAR(32) ); #开始事务 START TRANSACTION; #设置保存点 SAVEPOINT a; #执行dml操作 INSERT INTO t27 VALUES(100,`tom`); SELECT * FROM t27; #回退到a ROLLBACK TO a; #直接回退到事务开始的状态 ROLLBACK;
若在a点后还设置了b点，回到a点时，会删除b点
58.事务注意事项
-
如果不开始事务，默认情况下，dml操作是自动提交的，不能滚回
INSERT INTO t27 VALUES(300,'milan'); --自动提交commit 不能回滚
-
如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是退回到事务开始的状态
-
你也可以在这个事务中（还没有提交的时候），创建多个保存点
-
你可以在事务没有提交前，选择退回到哪个保存点
-
mysql的事务机制需要innodb的存储引擎还可以使用，myisam不好使
-
开始一个事务start transaction ， set autocommit=off;
59.四种隔离级别
事务隔离级别介绍
-
多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
-
如果不考虑隔离性，可能会引发脏读，不可重复读，幻读
脏读：当一个事务读取另一个事务尚未提交的改变时，产生脏读
不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所作的修改或删除，每次返回不同的结果集，此时发生不可重复读
幻读：同一查询在同一事务中多次进行，由于其他提交事务所作的插入操作，每次返回不同的结果集，此时发生幻读
加锁：当一个表被另一个客户端操作未提交时，就不会去操作此表，会卡在那里等待提交
也就是说客户端C1读取后，进行了提交操作，但是C2此时也在进行此表的操作，但并没有提交，却能看见C1提交的结果，正确的是，C2不应该看见，C2索要查询的结果受到了C1的影响。如果C2要查询10点前的数据，但是C1在10，1提交了数据，导致了C2也会查询到这个数据，这就产生了错误
60.设置隔离
#查询当前隔离级别 SELECT @@tx_isolation; #查询系统当前隔离级别 SELECT @@global.tx_isolation; #设置当前隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL （级别）READ UNCOMMITTED #设置系统隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL （级别）READ UNCOMMITTED #全局修改 找到my.ini配置文件，在最后加上 transaction-isolation = SERIALIZABLE
事务的ACID特性
-
原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
-
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态
-
隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他的事务的操作数据所干扰，多个并发事务之间要互相隔离
-
持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
61.mysql表类型和存储引擎
mysql的表类型由存储引擎决定，主要包括MyISM，innoDB，Memory等
MySQL数据表主要支持六种类型：
-
事务安全型：innoDB
-
非事务安全型：CSV,Memory,ARCHIVE,MRG MYISAM,MYISAM,InnoBDB
#查看所有的存储引擎 SHOW ENGINES
Memory整个数据是在内存中的，查询速度非常快
-
InnoDB：最常用的，默认的
-
Myisam：1.添加速度快 2.不支持外键和事务 3.支持表级锁
-
Memory：1.数据存储在内存中[关闭了mysql数据就会丢失] 2.执行速度很快（没有IO读写） 3.默认支持索引（hash表）
如何选择存储引擎？
-
如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM最好，速度快
-
如果需要支持事务，选择InnoDB
-
Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改咋子服务器重启后都将消失（经典用法 用户的在线状态）
#修改存储引擎 ALTER TABLE `表名` ENGINE = 存储引擎;
62.视图原理
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
通过视图可以修改基表的数据；基表的改变，也会影响到视图的数据
63.视图使用细节
#创建视图 CREATE VIEW emp_view01 AS SELECT empno,enmae,job,deptno FROM emp; #查看创建视图的指令 SHOW CREATE VIEW emp_view01 #删除视图 DROP VIEW emp_view01; #视图中可以再使用视图，比如从emp_view01视图中，选出empno和ename做新的视图 CREATE VIEW emp_view02 AS SELECT empno,ename FROM emp_view01;
64.视图应用实践
#针对emp，dept和salgrade三张表，创建一个视图emp_view03 #可以显示雇员编号，雇员名，雇员部门名称和薪水级别 CREATE TABLE emp_view03 AS SELECT empno,ename,dname,grade FROM emp,dept,salgrade WHERE emp.deptno = dept.deptno AND (sal BETWEEN losal AND hisal)
65.Mysql用户管理
mysql中的用户，都存储再系统数据库mysql中user表中
其中user表的重要字段说明：
-
host：允许登录的”位置“，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
-
user：用户名
-
authenticatio_string：密码，是通过mysql的password（）函数加密之后的密码
#创建用户
CREATE USER '用户名' @'允许登录位置' IDENTIFIED BY '密码'
#删除用户
DROP USER '用户名' @ '允许登录位置';
#用户修改密码
SET PASSWORD = PASSWORD('密码')
66.Mysql权限管理
#基本语法 GRANT 权限列表 ON 库.对象名 TO '用户名' @ '登录位置'【IDENTIFIED BY '密码'】 --权限列表，多个权限用逗号隔开 --*.* ：代表本系统中的所有数据库的所有对象 --库.*：代表某个数据库中的所有数据对象 #回收用户授权 REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'登录位置'； #权限生效指令 FLUSH PRIVILEGES;
67.Mysql管理细节
#在创建用户的时候，如果不指定Host，则为%，%表示所有IP都有连接权限 CREATE USER jack, SELECT `host`,`user` FROM mysql.user; #你也可以这样指定 create user 'xxx'@'192.168.1.%' 表示xxx用户在192.168.1.*可以登录mysql CREATE USER 'smith'@'192.168.1.%' #在删除用户的时候，如果host不是%，需要明确指定 '用户'@'host值' DROP USER jack --默认就是DROP USER 'jack'@'%' DROP USER 'smith'@'192.168.1.%'
68.Mysql作业1
D是错误的，别名中间有空格，且没有引号引上，导致系统会认为别名是Annual，导致Salary无法运行
B是对的，应为判断非空不可以用不等于’<>‘，只能使用is not
3无法解释，无法排序
69.Mysql作业2
--使用简单查询语句完成 （1）显示所有部门名称 （2）显示所有雇员名以及全年收入 ，并指定列名为“年收入” SELECT dname FROM emp; SELECT ename,(sal + IFNULL(comm,0))*13 AS "年收入" FROM emp; #一旦奖金为空，则会导致整个年收入为空，所以加入函数判断，当奖金为0的时候，将其赋值为0 --限制查询数据 （1）显示工资超过2850的雇员姓名和工资 SELECT ename,sal FROM emp; WHERE sal > 2850; （2）显示工资不在1500到2850之间的所有的雇员名及工资 SELECT ename,sal FROM emp WHERE sal < 1500 OR sal > 2850; 也可以： SELECT enmae，sal FROM emp； WHERE NOT （sal >= 1500 AND sal <= 2850）; （3）显示编号为7566的雇员姓名以及所在部门编号 SELECT ename,deptno FROM emp WHERE empno = 7566; （4）显示部门10和30中工资超过1500的雇员名及工资 SELECT ename,sal FROM emp WHERE (deptno = 10 OR deptno = 30) AND sal >= 1500 （5）显示无管理者的雇员名及岗位 SELECT ename,job FROM emp WHERE mgr IS NULL; --排序数据 （1）显示在1991年2月1日到1991年5月1日之间雇佣的雇员名，岗位及雇佣日期，并以雇佣日期进行排序 SELECT ename,job,hiredate FROM emp WHERE hiredate >= '1991.2.1' AND hiredate <= '1991.5.1' ORDER BY hiredate; （2）显示获得补助的所有雇员名，工资及补助，并以工资降序排序 SELECT ename,sal,comm FROM emp ORDER BY sal DESC;
70.Mysql作业3
--1.选择部门30中的所有员工 SELECT * FROM emp WHERE deptno = 30 --2.列出所有办事员（CLERK）的姓名,编号和部门编号 SELECT ename,empno,deptno FROM emp WHERE job = 'CLERK'; --3.找出佣金高于薪金的员工 SELECT * FROM emp WHERE IFNULL(comm,0) > sal; --4.找出雇佣金高于薪金60%的员工 SELECT * FROM emp WHERE IFNULL(comm,0) > (sal*0.6) --5.找出部门10中所有经理和部门20中所有办事员的详细资料 SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK') --6.找出部门10中所有经理，部门20中所有办事员，还有既不是经理又不是办事员，但薪资大于或等于2000的所有员工的详细资料 SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK') OR (job !='MANAGER' AND job !='CLERK' AND sal >= 2000) --7.找出收取佣金的员工的不同工作 SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL; --8.找出不收佣金或收取佣金低于100的员工 SELECT * FROM emp WHERE comm IS NULL OR IFNULL(comm,0) < 100; --9.找出各月倒数第3天受雇的员(last_day可以返回当月最后一天)工 SELECT * FROM emp WHERE LAST_DAY(hiredate) -2 = hiredate --10.找出早于12年前受雇的员工 SELECT * FROM emp WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW(); --11.以首字母小写的方式显示所有员工的姓名 SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) FROM emp --12.显示正好为5个字符的员工的姓名 SELECT * FROM emp WHERE LENGTH(enmae) = 5;
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
