前言
一、Mysql的基础部分
1.1 数据库三层结构
1.所谓安装Mysql数据库，就是在主机安装一个数据库管理系统（DBMS)，这个管理程序可以管理多个数据库。DBMS(database manage sysytem)。
2.一个数据库中可以创建多个表，以保存数据（信息）。
3.数据库管理系统（DBMS)、数据库和表的关系：如图所示：（略）
4.数据在数据库中的存储方式
表的一行称之为一条记录---->在java程序中，一行记录往往使用对象表示
1.2 SQL语句的分类
1.3数据库的创建、查看、删除、备份和恢复
1.31 创建数据库
1、CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8
2、COOLLAT:指定数据库字符集的校对规则（常用的utf8_bin[区分大小写]、utf8_general_ci[不区分大小写]注意默认是utf8_general_ci)
课后练习
1.创建一个名称为database02 的数据库
2.创建一个使用utf8字符集的database02 数据库
3.创建一个使用utf8字符集，并带校对规则的database02 数据库
答案:
1.CREATE DATABASE database02
2.CREATE DATABASE database02 CHARACTER SET utf8
3.CREATE DATABASE database02 CHARACTER SET utf8 COLLATE utf8_bin
1.31 查看、删除数据库
课后练习
| SHOW DATABASES | 显示数据库语句 |
|---|---|
| SHOW CREATE DATABASE +【数据库名字】 | 显示数据库创建语句 |
| DROP DATABASE [IF EXISTS]+【数据库名字】 | 数据库删除语句【一定要慎用】 |
1.查看当前数据库服务器中的所有数据库
2.查看前面创建的database02数据库的定义信息
3.删除前面创建的database02数据库
答案:
1.SHOW DATABASES
2.SHOW CREATE DATABASE database02
3.DROP DATABASE database02
注意：在创建数据库，表的时候，为了规避关键字，可以使用反引号解决[’ ']
1.32 备份恢复数据库
1.备份数据库（注意：在DOS命令窗口执行）
mysqldump -u 用户名 -p -B 数据库1~n >文件名.sql
2.恢复数据库（注意：进入Mysql命令行再执行）
source 文件名.sql
3.备份库的表
mysqldump -u 用户名 -p 密码 数据库 表1 表2 表3 表n > d:\\ 文件名.sql
课后练习
- 备份和恢复文件
答案:
2.备份库的表
2.1 Mysql常用的数据类型（列类型）
2.11 数值型的基本使用
1.整数型数值
-- 使用 tinyint 来演示范围 有符号 -128 ~ 127
-- 如果没有符号 0-255
-- 说明： 表的字符集，校验规则, 存储引擎，老师使用默认
-- 1. 如果没有指定 unsinged , 则 TINYINT 就是有符号
CREATE TABLE table_01(
id TINYINT);
INSERT INTO table_01 VALUES(127);
INSERT INTO table_01 VALUES(-1);
SELECT * FROM table_01
-- 2. 如果指定 unsinged , 则 TINYINT 就是无符号 0-255
CREATE TABLE table_02(
id TINYINT UNSIGNED);
INSERT INTO table_02 VALUES(255);
SELECT * FROM table_02
2.小数型数值
#演示decimal类型（可以存放很大的数）、float、double类型的使用
CREATE TABLE table_04(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20));
INSERT INTO table_04 VALUES(123.7892587568,123.7892587568,123.7892587568)
SELECT *FROM table_04
2.12 字符串的基本使用
– 可变长度字符串 最大65532 【utf8编码最大21844字符1-3个字节用于记录大小】
– 如果表的编码是utf8 varchar(size) size=(65535-3)/3=21844
– 如果表的编码是gbk(占两个字节） varchar(size) size=(65535-3)/2=32766
#演示字符串类型使用 char varchar
CREATE TABLE table_05(
‘name‘ CHAR(255));
CREATE TABLE table_06(
‘name‘ VARCHAR(32766))CHARSET gbk;
DROP TABLE t10;
- char(4)和varchar(4)这个4表示的是字符，而不是字节，不区分字符是汉字还是字母， 不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的
2.char4)是定长的，即使你插入’aa’，也会占用分配的4个字符的空间
varchar(4)是变长的，即使你插入’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配。varchar不是还需要占用1-3个字节来记录存放内容长度 实际数据大小+（1-3）字节 —查询速度：char>varcgar
#演示字符串类型的使用细节
#char(4)和varchar(4)这个4表示的是字符，而不是字节，不区分字符是汉字还是字母
CREATE TABLE t11(
‘name‘ CHAR(4));
INSERT INTO t11 VALUES('abcd');
INSERT INTO t11 VALUES('你好你好');
SELECT * FROM t11;
CREATE TABLE t12(
‘name‘ VARCHAR(4));
INSERT INTO t12 VALUES('abcd');
INSERT INTO t12 VALUES('大家好呀');
INSERT INTO t12 VALUES('ab北京');
SELECT * FROM t12;
#如果VARCHAR不够用，可以考虑使用mediumtext或longtext,
#如果想要简单点，可以使用直接使用text
CREATE TABLE t13(content TEXT,content2 MEDIUMTEXT,content3 LONGTEXT);
INSERT INTO t13 VALUES('新东方教育','新东方教育100','新东方教育1000~~');
SELECT * FROM t13;
2.13 日期类型的基本使用
#演示日期相关（date,datetime,timestamp）的类型
#创建一张表，
CREATE TABLE t14(
birthday DATE,-- 生日
job_time DATETIME,-- 记录年月日 时分秒
login_time TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
-- 登录时间，如果希望login_time自动更新
SELECT * FROM t14;
INSERT INTO t14( birthday,job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
-- 如果我们更新 t14 表的某条记录，login_time列会自动的以当前时间进行更新
2.2 创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)character set 字符集 collate 校对规则 engine 存储引擎
field：指定列名 datatype ：指定列类型（字段类型）
character set ：如不指定则为所在数据库字符集
collate :如不指定则为所在数据库校对规则
engine:引擎（这个后面讲解）
2.21 课后练习1
database02创建表时，要根据需要保存的数据创建相对应的列，并根据数据的类型定义相应的列类型。练习创建user表
id 整形
name 字符串
password 字符串
birthday 日期
答案：CREATE TABLE ‘user‘( id INT, ‘name‘ VARCHAR(255), ‘password‘ VARCHAR(255), ‘bithday‘ DATE) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
2.22 课后练习2
创建一个职员表emp，选用适当的数据类型
id 整形
name 字符串
password 字符串
birthday 日期
entry_date 日期
job 字符型
Salary 小数型
resume 文本型
答案：
CREATE TABLE `emp` (
id INT,
`name` VARCHAR(32),
sex CHAR(1),
brithday DATE,
entry_date DATETIME,
job VARCHAR(32),
salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 添加一条记录
INSERT INTO `emp`
VALUES(1, '刘海', '男', '2000-11-11',
'2021-11-10 11:11:11', '教师', 5000, '教Mysql');
SELECT * FROM `emp`;
2.3修改表
应用实例
■员工表emp的上增加一个image列， varchar类型(要求在resume后面)。
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER `resume`;
■ 修改job列，使其长度为60。
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
■删除sex列。
ALTER TABLE emp
DROP sex
■表名改为employee。
RENAME TABLE emp TO employee
■修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8
■列名name修改为user name
alter table user change column name username varchar(20);
ALTER TABLE employee
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''
DESC employee -- 显示表的结构，可以查看所有表的所有列
2.31 数据库语句
2.31.1 Insert语句（添加数据）
说明:
1.插入的数据应与字段的数据类型相同。
2.数据的长度应在列的规定范围内。
3.在values中列出的数据位置必须与被加入的列的排列位置相对应。
4.字符和日期型数据应包含在单引号中。
5.列可以插入空值【前提是该字段允许为空】。
6.insert into table_name(列名) values(),(),() 形式添加多条记录
7.如果是给表中的所有字段添加数据，可以不写前面的字段名称
模板：INSERT INTO table_name[(column [,column...])] vaules (value [,value...])
1.创建一张学生表student (id int,d_student_name varchar(10),total_score double);
2.添加两条记录
-- 创建表
CREATE TABLE student(
id INT,
student_name VARCHAR(10), -- 长度为10
total_score DOUBLE);
DROP TABLE student NOT NULL DEFAULT;
-- 添加记录
INSERT INTO student(id,student_name,total_score) VALUES(1,'小米',345.5);
INSERT INTO student VALUES(4,'笑笑',500); -- 这样子写也是对滴！
SELECT * FROM student
2.31.2 update语句（修改数据）
模板：tabl_name SET col_name1=expr1 [, col_name=expr2 ...] [WHERE where_definition
说明：
1.update语法可以用新值更新原有表行中的各列。
2.set语句指示要修改那些列和要给予哪些值。
3.where语句指定应更新哪些行，如没有where语句，则更新where语句，则更新所有的行（记录）。
4.如果要修改多个字段，可以 set 字段1=值1，字段2=值2…
练习:
要求在上面创建的student表中修改表中的记录
1.将所有学生的成绩改为100分。
UPDATE student SET total_score=100;
2.将姓名为”笑笑“同学的成绩改为500分。
UPDATE student SET total_score=500 WHERE student_name='笑笑'
3.将小米同学的成绩在原有的基础上增加30分。
UPDATE student SET total_score=total_score+30 WHERE student_name='小米'
2.31.3 delete语句（删除数据）
模板：delete from tbl_name [WHERE where_definition]
说明:
1.如果不使用where语句，将删除表中所有数据
2.delete语句不能删除某一列的值（可使用update语句 设为null或者’‘）
3.使用delete与仅删除记录，不删除表本身。如要删除表，使用drop table语句。
练习:
1.删除表中小米的记录
DELETE FROM student WHERE student_name='小米'
2.删除表中所有的记录
DELETE FROM student
3.删除这个表
DROP TABLE student
2.31.3 select语句(查询数据）
模板：SELECT [DISTINCT] * {column1,column2,column3..} FROM table_name
#使用表达式对查询的列进行运算 SELECT *|{column1|expression,column2|expression ,...} from tablename
#在select语句中可使用as语句
-- select colum_name as 别名 from 表名 SELECT column_name as 别名 from 表名
– 1.select指定查询哪些列的数据
– 2.column指定列名
– 3.*号代表查询所有列
– 4.distinct可选，指显示结果时，是否去掉重复数据
练习
-- 创建表
CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
NAME VARCHAR(20) NOT NULL DEFAULT '',
chinese FLOAT NOT NULL DEFAULT 0.0,
english FLOAT NOT NULL DEFAULT 0.0,
math FLOAT NOT NULL DEFAULT 0.0
);
-- 插入数据
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'曹操',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'刘备',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'马超',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(9,'宋祖儿',45,95,99);
-- 查询表
SELECT * FROM student;
-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct 。
SELECT DISTINCT english FROM student;
-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`, english FROM student;
-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student;
-- 在所有学生总分加 10 分的情况
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 使用别名表示学生分数。
SELECT `name` AS '名字', (chinese + english + math + 10)AS total_score
FROM student;
2.31.4 where语句常用的运算符
– 比较运算符 ：
< <= >= = !=
BETWEEN…AND… 显示在某一区间
IN(set) 显示在In列表中的值，eg:in(100,200)
LIKE ‘张pattern’ 模糊查询
NOT LIKE ’’ 模糊查询
IS NULL 判断是否为空
– 逻辑运算符
and 多个条件同时成立
or 多个条件任一成立
not 不成立，例如：where not (salaruy>100);
练习
#1.查询姓名为赵云的学生的成绩
SELECT * FROM student WHERE NAME='赵云'
#2.查询英语成绩大于90分的同学
SELECT * FROM student WHERE english>90
#3.查询总分大于250分的同学
SELECT * FROM student WHERE (english+chinese+math)>250
查询math大于60，并且id大于7的学生成绩
SELECT * FROM student WHERE math>60 AND id>7
#查询英语成绩大于语文成绩的同学
SELECT * FROM student WHERE english>chinese
#查询总分大于200分并且数学成绩小于语文成绩的姓宋的学生 – 宋%表示名字以宋开头的就可以了
SELECT * FROM student WHERE (english+chinese+math)>200 AND math<chinese AND NAME LIKE '宋%'
#查询英语分数在80-90之间的同学 – betwen…and…是闭区间
SELECT * FROM student WHERE english BETWEEN 80 AND 90
#查询数学分数为89，90，91的同学
SELECT * FROM student WHERE math IN(89,90,91)
SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91;
– 查询所有姓李的学生成绩。
SELECT * FROM student WHEREnameLIKE '李%'
2.31.4 order by语句排序查询结果
模板： SELECT column1,column2,column3…
FROM table;
order by column asc|desc,…
说明：
- ORDER BY ：指定排序的列，排序的列既可以时表中的列名，也可以是 select语句后指定的列名
- Asc 升序【默认】、Desc 降序
- order by 语句应位于select语句的结尾
练习
-- 演示order by使用
-- 对数学成绩排序后输出【升序】
SELECT * FROM student
ORDER BY math
-- 对总分按从高到低的顺序输出【降序】--使用别名来排序
SELECT NAME ,(chinese + math + english) AS total_score FROM student
ORDER BY total_score DESC;
-- 对姓宋的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`, (chinese + english + math)AS total_score FROM student
WHERE `name` LIKE '宋%'
ORDER BY total_score;
2.32 表查询-- 加强
使用where语句
– 如何查询1992.1.1入职的员工
SELECT * FROM emp
WHERE hiredate > '1992-01-01'
如何使用like操作符
%：表示0到多个字符 _：表示单个字符
–如何显示首字母为S的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%'
– 如何显示第三个字符为大写O的所有员工的姓名个工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'
– 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
– 使用 order by 子句
– ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
ORDER BY sal
– 按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptnoASC , sal DESC;
2.33 合计/统计函数
2.33.1 count返回行的总数
模板：Select count (*) | count (列名) from table name [WHERE where definition]
说明：
– count()和count(列)的区别
– 解释：count()返回满足条件的记录的行数
– count(列)：统计，满足条件的某列有多少个，
– 但是会排除为null的情况
-- eg:
CREATE TABLE t15 (
`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT * FROM t15;
SELECT COUNT(*) FROM t15 -- 4
SELECT COUNT(`name` ) FROM t15 -- 3
练习
-- 统计一个班级共有多少学生?
SELECT COUNT(*) FROM student
-- 统计数学成绩大于90的学生有多少个?
SELECT COUNT(*) FROM student
WHERE math>90
-- 统计总分大于250的人数有多少?
SELECT COUNT(*) FROM student
WHERE (math+english+chinese)>250
2.33.2 sum函数
练习
-- 统计一个班级数学总成绩？
SELECT SUM(math) FROM student
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total,SUM(english) AS english_total,
SUM(chinese) AS chinese_total FROM student
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student
-- 注意：sum仅对数值起作用，对多列求和，“ ，”号不能少
SELECT SUM(`name`) FROM student
2.33.3 avg函数
练习
-- 求一个班级数学平均分？
SELECT AVG(math) FROM student
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;
2.33.4 max函数和min函数
练习
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
FROM student;
2.33.5 group by分组
模板：-- 使用group by语句进行分组，用于对查询的结果进行分组统计-- SELECT column1， column2 . column3. . FROM table-- group by column
-- 使用having语句对分组后的结果进行过滤，用于限制分组显示结果 -- SELECT column1, column2. column3. .FROM table -- group by column having
说明：
练习
/*部门表*/
CREATE TABLE dept(
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, -- 编号
dname VARCHAR(20) NOT NULL DEFAULT "", -- 名字
loc VARCHAR(13) NOT NULL DEFAULT "" -- 地点
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEWYORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
SELECT * FROM dept;
/*员工表*/
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利 奖金*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
-- 添加测试数据
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
SELECT * FROM emp;
-- 工资级别
/*工资级别表*/
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/
losal DECIMAL(17,2) NOT NULL, /* 该级别的最低工资 */
hisal DECIMAL(17,2) NOT NULL /* 该级别的最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;
– 如何显示每个部门的平均工资和最高工资
– 分析: avg(sal) max(sal)
– 按照部门来分组查询
SELECT AVG(sal),MAX(sal),deptno
FROM emp GROUP BY deptno
– 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2),MAX(sal),deptno
FROM emp GROUP BY deptno
显示每个部门的每种岗位的平均工资和最低工资
– 分析 :1. 显示每个部门的平均工资和最低工资
– 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal),deptno,job
FROM emp GROUP BY deptno,job
– 显示平均工资低于 2000 的部门号和它的平均工资 // 别名
– 分析 [写 mysql 语句的思路是化繁为简,各个击破]
– 1. 显示各个部门的平均工资和部门号
– 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
– 3. 使用别名进行过滤
SELECT AVG(sal) AS avg_sal,deptno
FROM emp GROUP BY deptno HAVING avg_sal < 2000
课后练习3
1）显示每种岗位的雇员总数、平均工资
SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
2)显示雇员总数，以及获得补助的雇员数
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)
FROM emp
- 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;
- 显示雇员工资的最大差额。
SELECT MAX(sal) - MIN(sal)
FROM emp;
– 应用案例：请统计各个部门 group by 的平均工资 avg，
– 并且是大于 1000 的 having，并且按照平均工资从高到低排序， order by
– 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal)AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2
2.33.6 数据分组总结
如果selsct语句同时包含droup by,having
,limit,order by 那么他们的顺序是group by，
having，order by ,limit.
2.34 字符串相关函数
| CHARSET(str) | 返回字串字符集 |
|---|---|
| CONCAT (string2 [… ]) | 连接字串 |
| INSTR (string substring ) | 返回substring在string中出现的位置没有返回0 |
| UCASE (string2 ) | 转换成大写 |
| LCASE (string2 ) | 转换成小写 |
| LEFT (string2 ,length ) | 从string2中的左边起取length个字符 |
| RIGHT (string2 ,length ) | 从 string2 中的右边起取 length 个字符 |
| LENGTH (string ) | string长度[按照字节] |
| REPLACE str ,search_ ,replace str ) | 在str中用replace_ str 替换search_ str |
| STRCMP (string1 ,string2 ) | 逐字符比较两字串大小 |
| SUBSTRING (str，position ,[,length]) | |
| LTRIM (string2 ) RTRIM (string2 ) | 去除前端空格或后端空格 |
练习
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp
-- CONCAT (string2 [... ])连接字串
SELECT CONCAT(ename,'工作是 ',job) FROM emp
-- INSTR (string substring )
-- 返回substring在string中出现的位置没有返回0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('zouliping', 'ping') FROM DUAL;
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT (string2 ,length )从string2中的左边起取length个字符
SELECT LEFT(ename, 2) FROM emp;
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT RIGHT (ename, 2) FROM emp;
-- LENGTH (string ) string长度[按照字节]
SELECT LENGTH(ename) FROM emp;
-- REPLACE ( str ,search_ _,replace_ str )
-- 在str中用replace_ str 替换search_ str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('abc','sbc') FROM DUAL
--SUBSTRING (str，position ,[,length])
-- 从str的position开始[从1开始计算] ,取length 个字符
SELECT SUBSTRING('absdf',1,2) FROM DUAL
-- LTRIM (string2 ) RTRIM (string2 )
-- 去除前端空格或后端空格
-- trim
SELECT LTRIM(' 你好mysql')
SELECT RTRIM('你好mysql ')
SELECT TRIM(' 你好mysql ')
-- 练习:以首字母小写的方式显示所有员工emp表的姓名str.sql使用两种方式5min
SELECT CONCAT( LCASE (SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) AS new_name
FROM emp
SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2)) AS new_name
FROM emp;
2.35 数学相关函数
| ABS(num) | 绝对值 |
|---|---|
| BIN (decimal_ number ) | 十进制转二进制 |
| CEILING (number2 ) | 向上取整，得到比num2大的最小整数 |
| CONV(number2,from_ base,to_ base) | 进制转换 |
| FLOOR (number2） 向下取整，得到比num2小的最大整数 | |
| FORMAT (number,decimal places ) | 保留小数位数(四舍五入） |
| HEX (DecimalNumber ) | 转十六进制 |
| LEAST (number , number2 […]) | 求最小值 |
| MOD (numerator ,denominator ) | 求余 |
| RAND([seed]) | 其范围为0≤v≤1.0范围在0到1之间 (即，其范围为0≤v≤1.0)。若已指定一个整数参数N则它被用作种子值，用来产生重复序列。 |
练习 |
-- ABS(num)绝对值
SELECT ABS(-10) FROM DUAL
-- BIN (decimal_ number )十进制转二进制
SELECT BIN(10) FROM DUAL
-- CEILING (number2 )向上取整，得到比num2大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
-- CONV(number2,from_ base,to_ base) 进制转换
-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(8, 16, 2) FROM DUAL;
-- FLOOR (number2 )向下取整，得到比num2小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- FORMAT (number,decimal places )保留小数位数(四舍五入）
SELECT FORMAT(88.125458,2) FROM DUAL;
-- HEX (DecimalNumber )转十六进制
-- LEAST (number , number2 [..])求最小值
SELECT LEAST(0,1, -19, 3) FROM DUAL;
-- MOD (numerator ,denominator )求余
SELECT MOD(20, 3) FROM DUAL
-- RaND([seed])
-- RAND([seed])其范围为0≤v≤1.0范围在0到1之间
-- (即，其范围为0≤v≤1.0)。若已指定一个整数参数N则它被用作种子值，用来产生
-- 重复序列。
-- 说明：
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT RAND(3) FROM DUAL;
2.36 时间日期函数
| CURRENT_ DATE ( ) | 当前日期 |
|---|---|
| CURRENT_ TIME ( ) | 当前时间 |
| CURRENT_ TIMESTAMP ( ) | 当前时间戳 |
| DATE (datetime ) | 返回datetime的日期部分 |
| DATE_ ADD (date2 , INTERVAL d_value d_type) | 在date2中加上日期或时间 |
| DATE SUB (date2 , INTERVAL d_value d_type) | 在date2上减去-个时间 |
| DATEDIFF (date1 ,date2 ) | 两个日期差(结果是天) |
练习
CREATE TABLE mes(
id INT,
content VARCHAR(30),
sendtime DATETIME);
INSERT INTO mes VALUES(1,'北京新闻',NOW());
INSERT INTO mes VALUES(2,'上海新闻',NOW());
SELECT * FROM mes
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间
SELECT id,content,DATE(sendtime) FROM mes
-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下.
/*方法一*/
SELECT *
FROM mes
WHERE DATE_ADD(sendtime, INTERVAL 10 MINUTE) >= NOW()
/*方法二*/
SELECT *
FROM mes
WHERE sendtime >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 2000-11-11 出生
SELECT DATEDIFF(NOW(), '2000-11-11') FROM DUAL;
-- 如果你能活 80 岁，求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活 80 岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 2000-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以 date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('2000-11-11', INTERVAL 80 YEAR), NOW())
FROM DUAL;
-- TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;
-- NOW( )当前时间
-- YEAR|Month| DATE (datetime ) FROM_ UNIXTIME() 年月日
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2018-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
--
SELECT FROM_UNIXTIME(1657871684, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1657871685, '%Y-%m-%d %H:%i:%s') FROM DUAL;
2.37 加密和系统函数
| USER() | 查询用户 |
|---|---|
| DATABASE() | 数据库名称 |
| MD5(str) | 为字符串算出一个MD5 32的字符串，(用户密码)加密 |
| PASSWORD(str) | 从原文密码str计算并返回密码字符串,通常用于对mysql |
-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL; -- 用户@IP 地址
-- DATABASE()数据库名称
SELECT DATABASE() FROM DUAL;
-- MD5(str)为字符串算出一个MD5 32的字符串，(用户密码)加密
-- root 密码123456zz->加密md5->在数据库中存放的是加密后的密码
SELECT MD5('123456zz') FROM DUAL;
SELECT LENGTH(MD5('123456zz')) FROM DUAL; -- 32
-- -----------演示用户表，存放密码时，时md5---------
CREATE TABLE zz_user(
id INT,
‘name‘ VARCHAR(32) NOT NULL DEFAULT'',
pwd CHAR(32) NOT NULL DEFAULT'');
INSERT INTO zz_user
VALUES (1,'zz', MD5('123456zz'));
SELECT * FROM zz_user;
SELECT * FROM zz_user
WHERE ‘name‘='zz' AND pwd=MD5('123456zz')
SELECT * FROM zz_user
WHERE ‘name‘='zz' AND pwd='123456zz' -- 找不到
-- PASSWORD(str)从原文密码str计算并返回密码字符串,通常用于对mysql
SELECT PASSWORD('123456zz') FROM DUAL;/* 为啥我不行呢*/
-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串
-- 通常用于对 mysql 数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user
2.38 流程控制函数
| IF（expr1,expr2,expr3） | 如果expr1为True，则返回expr2,否则返回expr3 |
|---|---|
| IFNULL(expr1,expr2) | 如果expr1不为空NULL,则返回expr1,否则返回expr2 |
| SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END | 如果expr1为TRUE,则返回expr2,如果expr2为t,返回expr4,否则返回expr5 |
# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECT IFNULL( NULL, '合肥') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom'
ELSE 'mary' END
-- 1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0
-- 老师说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm)
FROM emp;
SELECT ename, IFNULL(comm, 0.0)
FROM emp;
-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END)AS 'job'
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
2.4 分页查询
基本语法：select … limit start,rows
表示从start+1行开始取，取出rows行，start从0开始计算
-- 分页查询
-- 按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页，第 3 页
-- 第 1 页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3;
-- 第 2 页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3;
-- 第 3 页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job;
-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;
2.5 多表查询
概念：多表查询是基于两个和两个以上的表查询，查询单个表可能不能满足你的需求。
练习
1.显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
分析：
- 雇员名,雇员工资 来自 emp 表
- 部门的名字 来自 dept 表
- 需求对 emp 和 dept 查ename,sal,dname,deptno
- 当我们需要指定显示某个表的列是，需要 表.列表
多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno
- 如何显示部门号为 10 的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10
- 显示各个员工的姓名，工资，及其工资的级别
SELECT ename, sal, grade
FROM emp , salgrade
WHERE sal between losal and hisal;
2.51 自连接
自连接：是指同一张表的连接查询【将同一张表看做两张表】
练习
显示公司员工名字和他的上级的名字
员工名字 在 emp, 上级的名字的名字 emp
员工和上级是通过 emp 表的 mgr 列关联
小结：
自连接的特点:
1. 把同一张表当做两张表使用
2. 需要给表取别名 表名 表别名
3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
SELECT * FROM emp;
2.52 mysql表子查询
子查询：是指嵌入在其他sql语句中的select语句，也叫做嵌套查询。
单行子查询：是指只返回一行数据的子查询语句
多行子查询：指返回多行数据的子查询 使用关键字in
多列子查询：是指查询返回多个列数据的子查询语句
练习
如何显示与 SMITH 同一部门的所有员工?
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)
如何查询和部门 10 的工作相同的雇员的
select ename, job, sal, deptno
from emp
where job in (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) and deptno <> 10
如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
-- 分析: 1. 得到 'ALLEN' 的部门和岗位
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
FROM emp
WHERE (deptno , job) = (
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
)AND ename != 'ALLEN'
2.52.1在多行子查询中使用all和any操作符
显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal >ALL(
SELECT sal
FROM emp
WHERE deptno = 30
)
如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > any(
SELECT sal
FROM emp
WHERE deptno = 30
)
2.52.2在from语句中使用子查询
练习
查找每个部门工资高于本部门平均工资的人的资料
-- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal)AS avg_sal
FROM emp GROUP BY deptno
-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
--
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 ename, sal, temp.max_sal, emp.deptno
FROM emp, (
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptnoAND emp.sal = temp.max_sal
查询每个部门的信息(包括：部门名,编号,地址)和人员数量,我们一起完成
-- 1. 部门名,编号,地址 来自 dept 表
SELECT COUNT(*), deptno
FROM emp
GROUP BY deptno;
-- 2. 各个部门的人员数量 -》 构建一个临时表
SELECT dname, dept.deptno, loc , tmp.per_num AS '人数'
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化 sql 语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT tmp.* , dname, loc
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
2.6 表的复制
问题：如何自我复制
#创建表
CREATE TABLE my_tab01
( id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;
-- 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;
SELECT COUNT(*) FROM my_tab01;
如何删除一张表重复的记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把 emp 表的结构(列)，复制到 my_tab02
DESC my_tab02;
INSERT INTO my_tab02
SELECT * FROM emp;
SELECT * FROM my_tab02;
-- 3. 考虑去重 my_tab02 的记录
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
create table my_tmp like my_tab02
-- (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp
select distinct * from my_tab02;
-- (3) 清除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02
select * from my_tmp;
-- (5) drop 掉 临时表 my_tmp
drop table my_tmp;
select * from my_tab02;
2.7 合并查询
有时在实际应用中，为了合并多个select语句的结果，
可以使用集合操作符号 union,union all.
1.union all ：该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
SELECT ename, sal,job FROM emp WHERE sal>2500 UNION ALL
SELECT ename, sal,job FROM emp WHERE job='MANAGER'
2.union操作与union all类似，但是会自动去掉结果集中重复行
SELECT ename, sal,job FROM emp WHERE sal>2500 UNION
SELECT ename, sal,job FROM emp WHERE job='MANAGER'
2.8 表外连接
左外连接:如果左侧的表完全显示我们就说是左外连接
右外连接：如果右侧的表完全显示我们就说是右外连接
我们这里有两张表
表1：学生表（student）
| id | name |
|---|---|
| 1 | tom |
| 2 | chenjie |
| 3 | mike |
| 4 | jack |
#创建表student
CREATE TABLE student(
id INT,
ename VARCHAR(32))
INSERT INTO student VALUES(1,'tom'),(2,'chenjie'),(3,'mike'),(4,'jack');
SELECT* FROM student;
表2：成绩表（exma）
| id | grade |
|---|---|
| 1 | 76 |
| 2 | 78 |
| 10 | 89 |
#创建exam
CREATE TABLE exam(
id INT,
grade INT)
INSERT INTO exam VALUES(1,76),(2,78),(10,89);
SELECT * FROM exam;
使用左外连接（显示所有人的成绩，如果没有成绩，也要显示该人的id和姓名，成绩为空）
select … from 表1 left join 表2 on 条件
-- 使用左连接
SELECT ename,student.id,grade
FROM student LEFT JOIN exam
ON student.id=exam.id;
使用右外连接（显示所有成绩，如果没有名字匹配，显示空)
即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
-- 使用右连接
SELECT ename,student.id,grade
FROM student RIGHT JOIN exam
ON student.id=exam.id;
2.9 约束
约束用于确保数据库的数据满足特定的商业规则。
在MySQL中，约束包括：not null, unique, primary key, foreign key
和check.
2.9.1 primary key主键
字段名 字段类型 primary key
用于唯一的标示表行的数据，当定义主键约束后，该列不能重复
说明:
1.主键不能重复而且不能为null；
2.一张表中最多只能有一个主键，但是可以是复合键
3.主键的指定方式：1）直接在字段名后指定：字段名 primary key
2)在表定义最后写 primary key（列名）4.使用desc表名，可以看到 primary key的情况
5.在实际开发中，每个表往往都会设计一个主键
-- 演示主键的使用
#创建表 t1
CREATE TABLE t1(
id INT PRIMARY KEY,-- 编号
ename VARCHAR(32),-- 姓名
number VARCHAR(32))-- 手机号
INSERT INTO t1 VALUES (1,'jack','18356723452'),(2,'tom','13827673451'),
(3,'mike','18329875469')
– 主键列的值是不可以重复
INSERT INTO t1
VALUES(1, 'jack', '13814363590');
primary key 不能重复而且不能为 null。
INSERT INTO t1
VALUES(NULL, 'jack', '13814363590');
演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t2
(id INT ,
`name` VARCHAR(32),
number VARCHAR(32),
PRIMARYKEY (id, `name`) -- 这里就是复合主键
);
– 使用 desc 表名，可以看到 primary key 的情况-- 使用 desc 表名，可以看到 primary key 的情况
2.9.2 not null
如果在列上定义了not nul，那么当插入数据时，必须为列提供数据。
字段名 字段类型 not null
2.9.3 unique(唯一)
当定义了唯一约束后，该列值是不能重复的
字段名 字段类型 unique
细节注意1.如果没有指定not null，则unique字段可以有多个null
2.一张表可以有多个unique字段,unique not null 使用效果类似 primary key
-- 演示unique的使用
#创建表 t1
CREATE TABLE t1(
id INT UNIQUE,-- 编号
ename VARCHAR(32),-- 姓名
number VARCHAR(32))-- 手机号
INSERT INTO t1 VALUES (1,'jack','18356723452')
INSERT INTO t1 VALUES (1,'tom','18356723453')-- Duplicate entry '1' for key 't1.id'
#细节演示
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个 null
INSERT INTO t1 VALUES (NULL,'tom','18356723453')
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
#创建表 t2
CREATE TABLE t2(
id INT UNIQUE,-- 编号
ename VARCHAR(32) UNIQUE,-- 姓名
number VARCHAR(32))-- 手机号
2.9.4 foreign key(外键)
用于定义主表和从表之间的关系：外键约束要定义在从表中，主表则必须具有主键约束或unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或为null
FOREIGN KEY （本表字段名） REFERENCES 主表名（主键名或unique字段名）
细节注意：1.外键指向的是表的字段，要求是主键或者是unique
2.表的类型是innodb,这样子的表才支持外键
3.外键字段类型要和主键字段的类型一致，长度可以不同
4.外键字段值，必须在主键字段中出现，或者为null，前提是外键字段允许为null
5.一旦建立主外键的关系，数据不能随意删除了。
这个外键我的不知道哪里有问题弄不出来
-- 外键的演示
#创建主表my_class
CREATE TABLE my_class (
id INT PRIMARY KEY , -- 班级编号
ename VARCHAR(32) NOT NULL DEFAULT '');
#创建 从表 my_stu
CREATE TABLE my_stu (
id INT PRIMARY KEY , -- 学生编号
ename VARCHAR(32) NOT NULL DEFAULT '',
class_id INT ) -- 学生所在班级的编号
DROP TABLE my_stu
-- 下面指定外键关系
FOREIGN KEY(class_id) REFERENCES my_class(id)
-- 测试数据
INSERT INTO my_class
VALUES(100, 'javaweb'), (200, 'mysql');
INSERT INTO my_class
VALUES(300, 'javaee');
SELECT * FROM my_class;
INSERT INTO my_stu
VALUES(1, 'tom', 100);
INSERT INTO my_stu
VALUES(2, 'jack', 200);
INSERT INTO my_stu
VALUES(3, 'mike', 300);
INSERT INTO my_stu
VALUES(4, 'mary', 400); -- 这里会失败...因为 400 班级不存在
INSERT INTO my_stu
VALUES(5, 'king', NULL); -- 可以, 外键 没有写 not null
SELECT * FROM my_class;
SELECT * FROM my_stu;
-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class
WHERE id = 100;
2.9.5 check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在 1000 ~ 2000之间如果不再1000~2000之间就会提示出错
oracle 和sql server均支持check，但是mysql5.7目前还不支持check,只做语法校验，但不会生效。基本语法：列名 类型 check(check条件)
-- 演示check的使用
#创建表t4
CREATE TABLE t4 (
id INT PRIMARY KEY,
ename VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);
#添加数据
INSERT INTO t4
VALUES(1, 'jack', 'mid', 1);-- Check constraint 't4_chk_1' is violated.
INSERT INTO t4
VALUES(2, 'tom', 'man', 1500);
SELECT * FROM t4;
2.9.6课后练习
现有一个商店的数据库 shop_db，记录客户及其购物情况，由下面三个表组成：
商品 goods（商品号 goods_id，商品名 goods_name，单价 unitprice，商品类别 category，
供应商 provider);
客户 customer（客户号 customer_id,姓名 name,住址 address,电邮 email 性别 sex,身份证 card_Id);
购买 purchase（购买订单号 order_id，客户号 customer_id,商品号 goods_id,购买数量 nums);
1 建表，在定义中要求声明 [进行合理设计]：
(1)每个表的主外键；
(2)客户的姓名不能为空值；
(3)电邮不能够重复;
(4)客户的性别[男|女] check 枚举…
(5)单价 unitprice 在 1.0 - 9999.99 之间 check# 总结
3.1自增长
在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动的增长，怎么处理？
字段名 整型 primary key auto_increment
细节说明1.一般来说自增长是和primary key配合使用
2.自增长也可以单独使用【但是需要配合一个unique】
3.自增长修饰的字段为整数型的（虽然小数也是可以但是非常非常少这样子用）
4.自增长默认从1开始，你也可以通过如下命令修改
alter table 表名 auto_increment=新的开始值
5.如果你添加数据时，给自增长字段（列）指定的值，则以指定的值为准，如果指定了自增长，一般按自增长的规则添加数据。
-- 演示自增长的使用
#创建表
CREATE TABLE t5
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
#自增长的使用
INSERT INTO t5
VALUES(NULL, 'tom@qq.com', 'tom');
INSERT INTO t5
(email, `name`) VALUES('jack@sohu.com', 'jack');
DESC t5
SELECT *FROM t5
#修改默认的自增长开始值
CREATE TABLE t6
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
ALTER TABLE t6 AUTO_INCREMENT = 100
INSERT INTO t6
VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t6
VALUES(666, 'mike@qq.com', 'mike');
DESC t6
SELECT *FROM t6
DROP TABLE t6
3.2 mysql索引【这个不太理解】
说起提高数据库性能，索引是最物美价廉的东西了。
不用加内存，不用改程序，不用调用sql，
查询速度就可能提高百倍千倍。
原理：没有索引为什么会那么慢？（
因为全表扫描）
使用索引为什么那么快？（形成一个索引的数据结构，比如二叉树索引的代价【1.磁盘占用 2.对dml(update delete insert)语句的效率影响】）
索引类型：1.主键索引，主键自动的为主索引（类型Primary key）
2.唯一索引（unique）
3.普通索引（index）
4.全文索引（fulltext）[适用于MYLSAM]
一般开发，不使用mysql自带的全文索引，而是使用：全文搜索Solr和ElasticSearch索引的使用
1.添加索引(建小表测试id , name ) index use.sql
create [UNIQUE] index index name on tbl name (col name [(length)]
[ASC | DESC] , …
alter table table name ADD INDEX [index name] (index col name…)
2.添加主键(索引) ALTER TABLE表名ADD PRIMARY KEY(列名.);
3.删除索引
DROP INDEX index name ON tbl
name:
alter table table name drop index index name;
4.删除主键索引比较特别: alter table t b drop primary key;查询索引(三种方式)
show index(es) from table name;
show keys from table name;
desc table Name;
哪些列上适合使用索引1.较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex =男
3.更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1
4.不会出现在WHERE子句中字段不该创建索引
-- 演示 mysql 的索引的使用
#创建索引
CREATE TABLE t7 (
id INT ,
`name` VARCHAR(32));
# 查询表是否有索引
SHOW INDEXES FROM t7;
#添加索引
#添加唯一索引
CREATE UNIQUE INDEX id_index ON t7 (id);
#添加普通索引方式 1
CREATE INDEX id_index ON t7 (id);
-- 1. 如果某列的值，是不会重复的，则优先考虑使用 unique 索引, 否则使用普通索引
-- 添加普通索引方式 2
ALTER TABLE t7 ADD INDEX id_index (id)
-- 添加主键索引
CREATE TABLE t8 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t8 ADD PRIMARY KEY (id)
SHOW INDEX FROM t8
DROP TABLE t8
-- 删除索引
DROP INDEX id_index ON t7
-- 删除主键索引
ALTER TABLE t8 DROP PRIMARY KEY
-- 修改索引 ， 先删除，在添加新的索引
-- 查询索引
-- 1. 方式
SHOW INDEX FROM t7
-- 2. 方式
SHOW INDEXES FROM t7
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t7
3.2.1 练习
练习1：创建一张订单表order(id 号，商品名，订购人，数量）。要求id为主键，请使用2种方式来创建主键
练习2:创建一张特价菜谱表menu(id 号,菜谱名,厨师,点餐人,点餐人身份证,价格)要求id号为主键,点餐人身份证是unique请使用两种方式来创建unique
练习3:创建一张运动员表是sportman(id 号,名字,特长).要求id为主键,名字为普通索引,请使用两种方法来创建索引.
3.3 mysql事物
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dm语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。
事物与锁:
当执行事务操作时(dml语句) ,mysqI会在表上加锁,防止其它用户改表的数据.
这对用户来讲是非常重要的
mysql数据库控制台事务的几个重要操作(基本操作transaction.sql)1.start transaction – 开始一个事务
2.savepoint保存点名-- 设置保存点
3. rollack to保存点名-- 回退事务
4. rollback - 回退全部事务
5.commit - 提交事务，所有的操作生效，不能回退
-- 1. 创建一张测试表
CREATE TABLE t9
( id INT,
`name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行 dml 操作
INSERT INTO t9 VALUES(100, 'tom');
SELECT * FROM t9;
SAVEPOINT b
-- 执行 dml 操作
INSERT INTO t9 VALUES(200, 'jack');
-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK
COMMIT
回退事物:在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时(commit) ,会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点.
提交事物:使用commit语句可以提交事务当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效.]
细节说明:1.如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2.如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到
你事务开始的状态.
3.你也可以在这个事务中(还没有提交时)，创建多个保存点比如: savepoint
aaa;
执行dml，savepoint bbb;
4.你可以在事务没有提交前，选择回退到哪个保存点.
5, mysq|的事务机制需要innodb的存储引擎才可以使用,myisam不好使.
6.开始一个事务start transaction,
set autocommit off;
-- 1. 如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300, 'milan'); -- 自动提交 commit
SELECT * FROM t27
-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400, 'king');
INSERT INTO t27 VALUES(500, 'scott');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;
-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa;
-- 执行 dml , savepoint bbb
-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, set autocommit=off;
总结
提示：这里对文章进行总结：
以上是对Mysql部分内容的介绍，喜欢的话就点赞收藏吧！后续会慢慢就行补充。
1499
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
