Mysql学习笔记

最新推荐文章于 2024-09-30 11:01:45 发布

惜花人去

最新推荐文章于 2024-09-30 11:01:45 发布

阅读量226

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/m0_53624299/article/details/122034601

版权

MySQL

安装配置： Mysql5.7.19 解压版安装配置详细教程

Mysql5.7地址：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

文章目录

MySQL

数据库

连接登录、创建、查看、备份恢复删除

使用命令行窗口连接 MySQL 数据库

1. mysql -h 主机名 -P 端口 -u 用户名 -p密码
2. 登录前，保证服务启动
3. 启动mysql数据库的常用方式：[Dos命令]
	服务方式启动（界面）
	关闭 ：net stop mysql服务名
	启动 ：net start mysql服务名

连接到MysqI服务（Mysql数据库）的指令

mysql -h 主机IP -P 端口 -u 用户名 -p密码

mysql -u root -p

查询当前使用的数据库
select database();
查询数据库版本也可以使用
select version();
终止一条语句
如果想要终止一条正在编写的语句，可键入 \c
退出 mysql
可使用\q、 QUIT 或 EXIT：
如：mysql>\q (ctrl+c)

提醒

-p密码不要有空格
-p后面没有写密码，回车会要求输入密码
如果没有写-h主机，默认就是本机
如果没有写-P端口，默认就是3306
在实际工作中3306一般修改，防止被恶意攻击

数据库三层结构

所谓安装Mysql数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。DBMS（database manage system）
一个数据库中可以创建多个表，以保存数据（信息）。
数据库管理系统（DBMS）、数据库和表的关系如图所示：示意图
不同的端口监听就是代表不同的访问这个数据库的客户端
MySQL数据库-普通表的本质仍然是文件

数据在数据库中的存储方式表列（column）行（cow）
1. 列、行、主键。列叫做字段(Column),行叫做表中的记录,每一个字段都有:字段名称/字段数据类型/字段约束/字段长度
2. 表的一行称之为一条记录——在java程序中，一条记录往往使用对象表示
SQL 语句分类
```
DDL：数据定义语句 [create表，库]
DML：数据操作语句 [增加insert，修改update，删除delete]
DQL：数据查询语句 [select]
DCL：数据控制运句 [管理数据库：比如用户权限 grant revoke]
事务控制语言(TCL-Transactional Control Language)代表关键字:commit ,rollback;
```

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]

create_specification: #create_specification选项用于指定数据库的特性
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

#使用指令创建数据库
CREATE DATABASE yc_db01;
#删除数据库指令
DROP DATABASE yc_db01
#创建一个使用 utf8 字符集的 yc_db02 数据库
CREATE DATABASE yc_db02 CHARACTER SET utf8
#创建一个使用 utf8 字符集， 并带校对规则的 yc_db03 数据库
CREATE DATABASE yc_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写
    
#下面是一条查询的 sql , select 查询 * 表示所有字段 FROM 从哪个表
#WHERE 从哪个字段 NAME = 'tom' 查询名字是 tom
    SELECT * FROM t1 WHERE NAME = 'tom'
SELECT *
    FROM t1
	WHERE NAME = 'tom'

查看、删除数据库

#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#显示数据库创建语句
SHOW CREATE DATABASE db_name
#数据库删除语句[一定要慎用]
DROP DATABASE[IF EXISTS] db_name

#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库
SHOW DATABASES

#查看前面创建的 hsp_db01 数据库的定义信息
SHOW CREATE DATABASE `hsp_db01` (最好无脑加上``反引号)
#说明 在创建数据库,表的时候， 为了规避关键字， 可以使用反引号解决
#删除前面创建的 hsp_db01 数据库
DROP DATABASE hsp_db01
show databases;  #查看所有数据库
show tables;  #查看库中所有表

备份恢复数据库

#备份, 要在 Dos 下执行 mysqldump 指令其实在 mysql 安装目录\bin
#这个备份的文件， 就是对应的 sql 语句
mysqldump -u 用户名 -p -B 数据库1 数据库2 ...数据库n  > 盘符号:\\文件名.sql
#删除数据库
DROP DATABASE[IF EXISTS] db_name
#恢复数据库(注意： 进入 Mysql 命令行再执行)
source 盘符号:\\文件名.sql

案例

#备份, 要在 Dos 下执行 mysqldump 指令其实在 mysql 安装目录\bin
#这个备份的文件， 就是对应的 sql 语句
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql
DROP DATABASE ecshop;//删除数据库
#恢复数据库(注意： 进入 Mysql 命令行再执行)
source d:\\bak.sql
#第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中， 执行

备份恢复数据库的具体某些表

mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql

表

创建表

创建表的时候，表中有字段，每一个字段有：

字段名
字段数据类型
字段长度限制
字段约束

CREATE TABLE `user`(
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

Mysql 常用数据类型(列类型)

bigdeciml[ ] 比bigint[8个字节]还大，datetime和TimeStamp使用较多

数值型(整数)的基本使用

1.使用规范：在能够满足需求的情况下，尽量选择占用空间小的

#说明
#1. bit(m) m 在 1-64
#2. 添加数据 范围 按照你给的位数来确定， 比如 m = 8 表示一个字节 0~255
#3. 显示按照 bit
#4. 查询时， 仍然可以按照数来查询

如何定义一个无符号的整数

create table t10 (id tinyint)//默认是有符号的
createtable t11 (id  tinyint unsigned)无符号的
#1. 如果没有指定 unsinged , 则 TINYINT 就是有符号
#2. 如果指定 unsinged , 则 TINYINT 就是无符号 0-255

数值型(bit)的使用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2sh07wrz-1639963889789)(MySQL.assets/image-20211129201730852.png)]

#演示 bit 类型使用
#说明
#1. bit(m) m 在 1-64
#2. 添加数据 范围 按照你给的位数来确定， 比如 m = 8 表示一个字节 0~255
#3. 显示按照 bit
#4. 查询时， 仍然可以按照数来查询
CREATE TABLE t05 (num BIT(8));
INSERT INTO t05 VALUES(255);
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;

数值型(小数)的基本使用

字符串的基本使用

#演示字符串类型使用 char varchar
#注释的快捷键 shift+ctrl+c , 注销注释 shift+ctrl+r
-- CHAR(size)
-- 固定长度字符串 最大 255 字符
-- VARCHAR(size) 0~65535 字节，并不是字符,但是size是字符（根据是utf-8还是gbk有不同大小）
-- 可变长度字符串 最大 65532 字节 【utf8 编码最大 21844 字符 1-3 个字节用于记录大小】
-- 如果表的编码是 utf8(1个字符占用3个字节) varchar(size) size = (65535-3) / 3 = 21844
-- 如果表的编码是 gbk(1个字符占用2个字节) varchar(size) size = (65535-3) / 2 = 32766


CREATE TABLE t09 (
	`name` CHAR(255));
CREATE TABLE t11 (
`name` VARCHAR(21844));
CREATE TABLE t11 (
`name` VARCHAR(32766)) CHARSET gbk;

字符串使用细节 (3个)

细节1 字符而不是字节

char（4）//这个4表示字符数（最大255），不是字节数，不管是中文还是字母都是放四个，按字符计算。
varchar（4）//这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据，
不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的

细节2 定长和变长
1. char（4）是定长（固定的大小），就是说，即使你插入"aa"，也会占用分配的4个字符的空间
2. varchar（4）是变长（变化的大小），就是说，如果你插入了“aa"，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配；varchar本身还需要占用1-3个字节来记录存放内容长度）实际空间：L（实际数据大小）+（1-3）字节
细节3 什么时候使用char，什么时候使用varchar：定长char，变长varchar
1. 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等.char（32）
2. 如果一个字段的长度是不确定，我们使用varchar，比如留言，文章
3. 查询速度：char>varchar
细节4 在存放文本时，也可以使用Text数据类型

在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列，注意Text不能有默认值，大小为0-2^16字节，如果希望存放更多字符，可以选择MEDIUMTEXT0-2^24 或者 LONGTEXT 0~2^32

日期类型的基本使用

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 列会自动的以当前时间进行更新

修改表（重点）—— 对列进行修改

添加字段

alter table 表名 add 字段名 字段类型(长度);

修改字段

alter table 表名 modify 字段名 字段类型(长度);

删除字段

alter table 表名 drop 字段名;

#修改表的操作练习
-- 员工表 emp 的上增加一个 image 列， varchar 类型(要求在 resume 后面)。
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER RESUME
DESC employee -- 显示表结构， 可以查看表的所有列
-- 修改 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 employee
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''
DESC employee -- 显示表结构
SELECT * FROM `emp`; -- 显示表信息

数据库 C[create]R[read]U[update]D[delete]语句

Insert 语句（添加数据）

INSERT INTO table_name [(column [,column ...])]
VALUES  (value[,value...])

#案例
INSERT INTO `goods` (id, goods_name, price)
VALUES(10, '华为手机', 2000);

细节说明 insertdetail.sql

#说明 insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
-- 比如 把 'abc' 添加到 int 类型会错误
INSERT INTO `goods` (id, goods_name, price)
VALUES('阳阳', '小米手机', 2000);
-- 2. 数据的长度应在列的规定范围内， 例如： 不能将一个长度为 80 的字符串加入到长度为 40 的列中。
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机 vovo 手机 vovo 手机 vovo 手机 vovo 手机', 3000);
-- 3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
INSERT INTO `goods` (id, goods_name, price) -- 不对
VALUES('vovo 手机',40, 2000);
-- 4. 字符和日期型数据应包含在单引号中。
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, vovo 手机, 3000); -- 错误的 vovo 手机 应该 'vovo 手机'
-- 5. 列可以插入空值[前提是该字段允许为空]， insert into table value(null)
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机', NULL);
-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
INSERT INTO `goods` (id, goods_name, price)
VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800);
-- 7. 如果是给表中的所有字段添加数据， 可以不写前面的字段名称
INSERT INTO `goods`
VALUES(70, 'IBM 手机', 5000);
-- 8. 默认值的使用， 当不给某个字段值时， 如果有默认值就会添加默认值， 否则报错
-- 如果某个列 没有指定 not null ,那么当添加数据时， 没有给定值， 则会默认给 null
-- 如果我们希望指定某个列的默认值， 可以在创建表时指定
INSERT INTO `goods` (id, goods_name)
VALUES(80, '格力手机');
SELECT * FROM goods;


CREATE TABLE `goods2`(
 id int,
 goods_name VAECHAR(10), --长度10
 price DOUOBLE NOT NULL DEFAULT 100)； --设置price默认值为100
 
INSERT INTO `goods2` (id, goods_name)
VALUES(10, '平平手机');
SELECT * FROM goods2;

update语句

UPDATE table_name
	SET column_name=expr1 [,column_name2=expr2 ...]
	[WHERE where_definition]

案例


-- 1. 将所有员工薪水修改为 5000 元。 [如果没有带 where 条件， 会修改所有的记录， 因此要小心]
UPDATE employee SET salary = 5000
-- 2. 将姓名为 小妖怪 的员工薪水修改为 3000 元。
UPDATE employee
SET salary = 3000
WHERE user_name = '小妖怪'
-- 3. 将 老妖怪 的薪水在原有基础上增加 1000 元
INSERT INTO employee
VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 5000, '给大王捶背', 'd:\\a.jpg');
UPDATE employee
SET salary = salary + 1000
WHERE user_name = '老妖怪'
-- 4. 可以修改多个列的值
UPDATE employee
SET salary = salary + 1000 , job = '出主意的'
WHERE user_name = '老妖怪'
SELECT * FROM employee;

update使用细节:
1. UPDATE语法可以用新值更新原有表行中的各列。
2. SET子句指示要修改哪些列和要给予哪些值
3. WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行（记录），因此一定小心。
4. 如果需要修改多个字段，可以通过set字段1=值1，字段2=值2…

delte语句

delete from table_name
	[WHERE where_definition]

案例

-- 删除表中名称为’老妖怪’的记录。
DELETE FROM employee
WHERE user_name = '老妖怪';
-- 删除表中所有记录, 一定要小心
DELETE FROM employee;
-- Delete 语句不能删除某一列的值（可使用 update 设为 null 或者 ''）
UPDATE employee SET job = '' WHERE user_name = '老妖怪';
SELECT * FROM employee
-- 要删除这个表
DROP TABLE employee;

使用细节
1. 如果不使用where子句，将删除表中所有数据。
2. Delete语句不能删除某一列的值（可使用update设为null或者’ '）
3. 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。drop table 表名

select 语句(最重要) （单表多表）

基本语法

SELECT [DISTINCT] * | {column1, column2, colunm3...}
		FROM table_name;

注意事项

Select指定查询哪些列的数据
column指定列名
*号代表查询所有列。
From指定查询哪张表
DISTINCT可选，指显示结果时，是否去掉重复数据

-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct 。
SELECT DISTINCT english FROM student;
-- 要查询的记录， 每个字段都相同， 才会去重
SELECT DISTINCT `name`, english FROM student;

采用 select * from emp，虽然简单，但是*号不是很明确，建议查询全部字段将相关字段写到 select 语句的后面,在以后java 连接数据库的时候,是需要在 java 程序中编写 SQL 语句的,这个时候编写的 SQL 语句不建议使用 select * 这种形式,建议写明字段,这样可读性强.

使用表达式对查询的列进行运算

SELECT *| {column1 | expression, column2|}

使用as语句取别名

SELECT colunm_name as 别名 from table_name;

案例

-- 统计每个学生的总分
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;

在 where 子句中经常使用的运算符

使用 where 子句，进行过滤查询

-- select 语句
-- 查询姓名为赵云的学生成绩
SELECT * FROM student
WHERE `name` = '赵云'
-- 查询英语成绩大于 90 分的同学
SELECT * FROM student
WHERE english > 90
-- 查询总分大于 200 分的所有同学
SELECT * FROM student
WHERE (chinese + english + math) > 200


-- 查询 math 大于 60 并且(and) id 大于 4 的学生成绩
SELECT * FROM student
WHERE math >60 AND id > 4
-- 查询总分大于 200 分 并且 数学成绩小于语文成绩,的姓赵的学生.
-- 赵% 表示 名字以赵开头的就可以
SELECT * FROM student
WHERE (chinese + english + math) > 200 AND
math < chinese AND `name` LIKE '赵%'
-- 查询英语分数在 80－90 之间的同学。
SELECT * FROM student
WHERE english >= 80 AND english <= 90;
SELECT * FROM student
WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间
-- 查询数学分数为 89,90,91 的同学。
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
WHERE math IN (89, 90, 91);
-- 查询所有姓李的学生成绩。
SELECT * FROM student
WHERE `name` LIKE '李%'

使用 order by 子句排序查询结果

SELECT column1,column2,column3...
		FrOM table;
		order by column asc|desc,  ....

Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
Asc升序[默认]、Desc降序
ORDER BY 子句应位于SELECT语句的结尾。

-- 对总分按从高到低的顺序输出 [降序] -- 使用别名排序
SELECT `name` , (chinese + english + math) 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;

合计/统计函数

合计/统计函数-count

SELECT count(*)|count (列名) from table_name (WHERE where_definition)

– count() 和 count(列名) 的区别
– 解释 :count() 返回满足条件的记录的行数
– count(列): 统计满足条件的某列有多少个，但是会排除为 null 的情况

-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于 90 的学生有多少个？
SELECT COUNT(*) FROM student
WHERE math > 90
-- 统计总分大于 250 的人数有多少？
SELECT COUNT(*) FROM student
WHERE (math + english + chinese) > 250
-- count(*) 和 count(列) 的区别
-- 解释 :count(*) 返回满足条件的记录的行数
-- count(列): 统计满足条件的某列有多少个， 但是会排除 为 null 的情况
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

合计函数-sum

Sum函数返回满足where条件的行的和一般使用在数值列

Select sum（列名）{，sum(列名)} from table_name
	[WHERE where_definition]

sum仅仅对数值起作用，对别的没有意义；对多列求和，“，”不能少

-- 统计一个班级语文、 英语、 数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
-- 统计一个班级语文、 英语、 数学的成绩总和
SELECT SUM(math + english + chinese) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
SELECT SUM(`name`) FROM student;

AVG函数-求平均值

SELECT avg(列名) {,avg()...} from table_name [WHERE where_defination]

-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;

MAX和MIN函数-求最大最小值

SELECT max/min(列名) from table_name 
	[WHERE where_defination]
	
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
FROM student;

分组函数 group by 和过滤函数having

使用 group by 子句对列进行分组，使用having 子句对分组后的结果进行过滤

SELECT column1,column2,colulmn3.. FROM table group by column

SELECT column1,column2,colulmn3.. 
FROM table 
group by column 
having column ...

案例

-- ?显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 分析 [写 sql 语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
SELECT AVG(sal), deptno
FROM emp GROUP BY deptno
-- 2. 在 1 的结果基础上， 进行过滤， 保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤
SELECT AVG(sal), deptno
FROM emp GROUP BY deptno
HAVING AVG(sal) < 2000;

SELECT AVG(sal) AS avg_sal, deptno
FROM emp GROUP BY deptno
HAVING avg_sal < 2000;

字符串函数

-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) AS '工作详情' FROM emp;
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL;
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp;
-- LENGTH (string ) string 长度[按照字节]
SELECT LENGTH(ename) FROM emp;
-- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL;
-- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始【从 1 开始计算】 ,取 length 个字符
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' 韩老师教育') FROM DUAL;
SELECT RTRIM('韩老师教育 ') FROM DUAL;
SELECT TRIM(' 韩老师教育 ') FROM DUAL;
-- 练习: 以首字母小写的方式显示所有员工 emp 表的姓名
-- 方法 1
-- 思路先取出 ename 的第一个字符， 转成小写的
-- 把他和后面的字符串进行拼接输出即可
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;

数学相关函数

-- 演示数学相关函数
-- 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 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL;
-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(16, 16, 10) FROM DUAL;
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL;
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 老韩说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ， 在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;

时间日期相关函数 date.sql

mysel>create table mes(id int, content varchar(30),sendtime datetime );
mysql>insert into mes values(1,'北京新闻',current_timestamp());




INSERT INTO mes VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
SELECT *FROM mes;
SELECT CURRENT_DATE() FROM DUAL;
-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 创建测试表 信息表
CREATE TABLE mes (
  id INT,
  content VARCHAR (30),
  sendtime DATETIME
) ;
ALTER TABLE mes 
CHANGE `sendtime` `send_time` DATETIME;
-- 添加一条记录
INSERT INTO mes
VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL;
-- 上应用实例
-- 显示所有新闻信息， 发布日期只显示 日期， 不用显示时间.
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 send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '2011-11-21') FROM DUAL;
-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1998-10-30') FROM DUAL;
-- 如果你能活 80 岁， 求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活 80 岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日， 时分秒
-- '1986-11-11' 可以 date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW())
FROM DUAL;
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-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(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;
SELECT * FROM mysql.user \G

上面时间日期函数的细节说明：

DATE_ADD()中的 interval 后面可以是year minute second day 等
DATESUB()中的 interval后面可以是year minute second hour day 等
DATEDIFF(date1.date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
这四个函数的日期类型可以是date，datetime或者timestamp

加密和系统函数

基本使用

-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户， 以及登录的 IP
SELECT USER() FROM DUAL; -- 用户@IP 地址
-- DATABASE()查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32 的字符串， 常用(用户密码)加密
-- root 密码是 hsp -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;
-- 演示用户表， 存放密码时， 是 md5
CREATE TABLE hsp_user (
  id INT,
  `name` VARCHAR (32) NOT NULL DEFAULT '',
  pwd CHAR(32) NOT NULL DEFAULT ''
) ;

INSERT INTO hsp_user
VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL 注入问题
WHERE `name`='韩顺平' AND pwd = MD5('hsp')
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECT PASSWORD('hsp') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串
-- 通常用于对 mysql 数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user

流程控制函数

常用流程控制函数	效果
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,如果 expr3 为 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,如果 expr3 为 t, 返回 expr4, 否则返回 expr5
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job'
FROM emp;

mysql 表查询–加强

在前面我们讲过mysql表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。

下面将使用前面创建三张表（empdept，salgrade）演示如何进行多表查询

使用where子句
？如何查找1992.1.1后入职的员工
如何使用like操作符
%：表示0到多个字符； _：表示单个字符
？如何显示首字符为S的员工姓名和工资
？如何显示第三个字符为大写0的所有员工的姓名和工资
如何显示没有上级的雇员的情况
查询表结构selectinc.sql
使用order by子句
? 如何按照工资的从低到高的顺序，显示雇员的信息
? 按照部门号升序而雇员的工资降序排列，显示雇员信息

-- 查询加强
-- ■ 使用 where 子句
-- ?如何查找 1992.1.1 后入职的员工
-- 老师说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式
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;
-- ■ 查询表结构
DESC emp
-- 使用 order by 子句
-- ?如何按照工资的从低到高的顺序[升序]， 显示雇员的信息
SELECT * FROM emp
ORDER BY sal
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;

分页查询

select ...  
limit start ,rows
表示从start+1 行开始取，取出rows行，start从0开始计算

-- 推导一个公式
SELECT * FROM emp
ORDER BY colunm
LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数   //只能计算结果后填入，不能直接使用乘法计算

使用分组函数和分组子句 group by

SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
-- (2) 显示雇员总数， 以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)， 
-- 如果该列的值为 null, 是不会统计 , 
-- SQL 非常灵活， 需要我们动脑筋.
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) 显示管理者的总人数。 小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;

-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;

数据分组的总结–语法顺序总结

如果select语句同时包含有group by having，limit order by那么他们的顺序是group by，having，order by , limit
先分组，再过滤，再排序，最后分页

-- 应用案例： 请统计各个部门 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

mysql 多表查询

多表查询是指基于两个和两个以上的表的查询，在实际应用中，查询单个表可能不能满足你的需求

在默认不筛选的条件下时，两张表查询得到的结果是两张表作笛卡尔集，得到的表行数为两表行数乘积，列数为两表列数之和
**多表查询的条件不能少于表的个数-1, 否则会出现笛卡尔集 **

-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- 小技巧： 多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为 10 的部门名、 员工名和工资
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10
-- ?显示各个员工的姓名， 工资， 及其工资的级别
-- 思路 姓名， 工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写 sql , 先写一个简单， 然后加入过滤条件...
select ename, sal, grade
from emp , salgrade
where sal between losal and hisal;

自连接

自连接的特点 : 把同一张表当做两张表使用

-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 老韩分析： 员工名字 在 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;

mysql 表子查询（嵌套查询）

什么是子查询 : 子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询

单行子查询 : 单行子查询是指只返回一行数据的子查询语句

可以将子查询（得到的结果表）当做一张临时表使用

-- 子查询的演示
-- 请思考： 如何显示与 SMITH 同一部门的所有员工?
/*
1. 先查询到 SMITH 的部门号得到
2. 把上面的 select 语句当做一个子查询来使用
*/
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
-- 下面的答案.
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)

-- 课堂练习:如何查询和部门 10 的工作相同的雇员的
-- 名字、 岗位、 工资、 部门号, 但是不含 10 号部门自己的雇员.
/*
1. 查询到 10 号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
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

把子查询当做一张临时表可以解决很多很多复杂的查询

-- 查询 ecshop 中各个类别中， 价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中， 价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询
select cat_id , max(shop_price)
from ecs_goods
group by cat_id
-- 这个最后答案
select goods_id, ecs_goods.cat_id, goods_name, shop_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 = ecs_goods.shop_price

在多行子查询中使用all操作符

-- all 和 any 的使用
-- 请思考:显示工资比部门 30 的所有员工的工资高的员工的姓名、 工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
    WHERE deptno = 30
)
-- 可以这样写
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MAX(sal)
FROM emp
WHERE deptno = 30
)

-- 请思考:如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、 工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > any(
SELECT sal
FROM emp
WHERE deptno = 30
)

-- 也可以使用min代替
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT min(sal)
FROM emp
    WHERE deptno = 30
)

多列子查询

多列子查询则是指查询返回多个列数据的子查询语句

(column1, column2, column3...) = (select column1, column2, from...)

-- 多列子查询
-- 请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
-- (字段 1， 字段 2 ...) = (select 字段 1， 字段 2 from 。 。 。 。 )
-- 分析: 1. 得到 allen 的部门和岗位
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
-- 分析: 2 把上面的查询当做子查询来使用， 并且使用多列子查询的语法进行匹配
SELECT * 
	FROM emp
	WHERE(deptno,job) = (
	SELECT deptno , job
	FROM emp
	WHERE ename = 'ALLEN'
	) AND ename != 'ALLEN'
	
	
	
-- 查询每个部门的信息(包括： 部门名,编号,地址)和人员数量
-- 1. 部门名,编号,地址 来自 dept 表
-- 2. 各个部门的人员数量 -》 构建一个临时表
SELECT temp.* ,dname , loc 
FROM dept,(
SELECT COUNT(deptno) AS num,deptno
 FROM emp
GROUP BY deptno
) temp
WHERE temp.deptno = dept.`deptno`

表复制

自我复制数据(蠕虫复制)

有时,为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
思考题：如何删除掉一张表重复记录

- 表的复制
-- 为了对某个 sql 语句进行效率测试， 我们需要海量数据时， 可以使用此法为表创建海量数据
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 一样
(2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
(3) 清除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02 //或者可以改名
(5) drop 掉 临时表 my_tmp
*/
-- (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;

合并查询

有时在实际应用中，为了合并多个select语句的结果可以使用集合操作符号union，union all 例：union.sql

union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
```
select ename ,sal, job from emp where sal > 2500 union
Select ename ,sal ,job from emp where job='MANAGER';
```

union :该操作赋与union all相似，但是会自动去掉结果集中重复行

select ename ,sal, job from emp where sal > 2500
union all select ename ,sal, job from emp where job = 'manager'

MySQL表外连接

外连接
1. 左外连接（如果左侧的表与右边的表没有完全匹配，也会将左侧的表完全显示）
2. 右外连接（如果右侧的表与左边的表没有完全匹配，也会将右侧的表完全显示）

-- 外连接
-- 比如： 列出部门名称和这些部门的员工名称和工作，
-- 同时要求 显示出那些没有员工的部门。
-- 使用我们学习过的多表查询的 SQL， 看看效果如何?
SELECT dname,ename,job
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dname
-- 创建 stu
/*
id name
1 Jack
2 Tom
3 Kity
4 nono
*/
CREATE TABLE stu (
id INT,
`name` VARCHAR(32));
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
SELECT * FROM stu;
-- 创建 exam
/*
id grade
1 56
2 76
11 8
*/
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;

-- 使用左连接
-- （显示所有人的成绩， 如果没有成绩， 也要显示该人的姓名和 id 号,成绩显示为空）
SELECT `name`, stu.id, grade
FROM stu, exam
WHERE stu.id = exam.id;
-- 改成左外连接
SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
-- 使用右外连接（显示所有成绩， 如果没有名字匹配， 显示空)
-- 即： 右边的表(exam) 和左表没有匹配的记录， 也会把右表的记录显示出来
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;

SELECT dname,ename,job
FROM dept LEFT JOIN emp
ON emp.deptno = dept.deptno
ORDER BY dname

-- 使用右外连接实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno

mysql 约束（五种约束）

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：

not null、unique、primary key、foreign key 和check五种。

primary key(主键)-基本使用

用于唯一的标示表行的数据，当定义主键的约束后，该列不能重复

字段名 字段类型 primary key

primary key 不能重复而且不能为 null。
一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
主键的指定方式有两种
– 1. 直接在字段名后指定：字段名 primakry key
– 2. 在表定义最后写 primary key(列名);
使用 desc 表名，可以看到 primary key 的情况
在实际开发中，每个表往往都会设计一个主键

-- 主键使用
-- id name email
CREATE TABLE t17
(id INT PRIMARY KEY, -- 表示 id 列是主键
`name` VARCHAR(32),
email VARCHAR(32));
-- 主键列的值不可以重复
INSERT INTO t17
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t17
VALUES(2, 'tom', 'tom@sohu.com');
INSERT INTO t17
VALUES(1, 'hsp', 'hsp@sohu.com');
-- 主键使用的细节讨论
-- primary key 不能重复而且不能为 null。
INSERT INTO t17
VALUES(NULL, 'hsp', 'hsp@sohu.com');
-- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
CREATE TABLE t18
(id INT PRIMARY KEY, -- 表示 id 列是主键
`name` VARCHAR(32), PRIMARY KEY -- 错误的
email VARCHAR(32));
-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18
(id INT ,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (id, `name`) -- 这里就是复合主键
);
INSERT INTO t18
VALUES(1, 'tom', 'tom@sohu.com');
INSERT INTO t18
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t18
VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;
-- 主键的指定方式 有两种
-- 1. 直接在字段名后指定： 字段名 primakry key
-- 2. 在表定义最后写 primary key(列名);
-- 使用 desc 表名， 可以看到 primary key 的情况
DESC t17 -- 查看 t20 表的结果， 显示约束的情况
DESC t18

not null(非空)

如果在列上定义了not null,那么当插入数据时，必须为列提供数据。

字段名  字段类型 not null

unique

当定义了唯一约束后，该列值是不能重复的
```
字段名 字段类型 unique
```
unique 细节（注意）：unique.sql
1. 如果没有指定not null，则unique字段可以有多个null
2. 一张表可以有多个unique字段
3. 如果一个列(字段)，是 unique not null 使用效果类似 primary key

foreign key(外键)

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束；当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null（学生/班级图示）

FOREIGN KEY(本表字段名)  REFERENCES  主表名(主键名或者unique字段名) 

学生表（从表）                   班级表（主表） foreignsql
id name class_id               id    class_name

-- 外键演示
-- 创建 主表 my_class
CREATE TABLE my_class (
id INT PRIMARY KEY , -- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT '');
-- 创建 从表 my_stu
CREATE TABLE my_stu (
id INT PRIMARY KEY , -- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT , -- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id))
-- 测试数据
INSERT INTO my_class
VALUES(100, 'java'), (200, 'web');
INSERT INTO my_class
VALUES(300, 'php')

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, 'hsp', 300);
INSERT INTO my_stu
VALUES(4, 'mary', 400); -- 这里会失败...因为 400 班级不存在
INSERT INTO my_stu
VALUES(5, 'king', NULL); -- 可以, 外键 没有写 not null
SELECT * FROM my_stu;
-- 一旦建立主外键的关系， 数据不能随意删除了
DELETE FROM my_class
WHERE id = 100;

foreignkey（外键）细节说明（创建小表演示）：

外键指向的表的字段，要求是primary key 或者是 unique
先创建主表，再创建从表
表的类型是innodb（存储引擎），这样的表才支持外键
外键字段的类型要和主键字段的类型一致（长度可以不同）
外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为 null ]
一旦建立主外键的关系，主表的数据不能随意删除了

check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000_{2000之间如果不再1000}2000之间就会提示出错
提示：oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效。check.sql

基本语法：列名类型 check（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)
);

在mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成。

自增长

在某张表中，存在一个id列（整数）我们希望在添加记录的时候，该列从1开始，自动的增长，怎么处理？increment.sql

字段名  整型  primary key auto_increment

-- 添加自增长的字段方式
insert into xxx(字段1，字段2...) values(null,'值'...);
insert into xxx(字段2....) values('值1','值2'...);
insert into xxx values (null,'值1'...)

-- 演示自增长的使用
-- 创建表
CREATE TABLE t24
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
DESC t24
-- 测试自增长的使用
INSERT INTO t24
VALUES(NULL, 'tom@qq.com', 'tom');
INSERT INTO t24
(email, `name`) VALUES('hsp@sohu.com', 'hsp');
SELECT * FROM t24;

-- 修改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT = 100
CREATE TABLE t25
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
INSERT INTO t25
VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t25
VALUES(666, 'hsp@qq.com', 'hsp'); -- 自增长时指定了值，之后为667
SELECT * FROM t25;

自增长使用细节

一般来说自增长是和primarykey配合使用的
自增长也可以单独使用[但是需要配合一个unique]
自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用）
自增长默认从1开始，你也可以通过如下命令修改alter:
```
table 表名 auto_increment = 新的开始值；
```
如果你添加数据时。给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，
般来说，就按照自增长的规则来添加数据。

mysql 索引

说起提高数据库性能，索引是最物美价廉的。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍干倍。

索引的原理

没有索引为什么会慢？因为全表扫描
使用索引为什么会快？形成一个索引的数据结构，比如二叉树

索引的代价：

磁盘占用
对 dml (update delete insert）语句的效率有影响（增删改的时候需要重构索引）
在我们项目中，select [90%]多 , update,delete,insert [10%]操作较少

CREATE INDEX empno_index ON emp (empno)
CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创建索引
-- 在没有创建索引前 , emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]
-- 创建 ename 列索引,emp.ibd 文件大小 是 827m
-- 创建索引后， 只对创建了索引的列有效
SELECT *
FROM emp
WHERE ename = 'PjDlwy' -- 没有在 ename 创建索引时， 时间 4.7s - 0.003s 原来是 4.7s

索引的类型

主键索引，主键自动的为主索引（类型Primary key）（主键自然就是一个索引）
唯一素引（UNIQUE）
普通索引（INDEX）
全文索引（FULLTEXT）[适用于MyISAM]

一般开发，不使用mysql自带的全文索引，而是使用：全文搜索Solr 和 ElasticSearch（ES）

create table t1(
id int primary key,-- 主键，同时也是索引，称为主键索引，
name varchar(32));
create table t2(
id int unique,   -- id是唯一的，同时也是索引，称为unique索引

索引使用

1. 如果某列的值，是不会重复的，则优先考虑使用 unique 索引, 否则使用普通索引

添加索引

create [unique] index index_name on table_name(colunme_name[(length)])

-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
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)

添加主键（索引）

alter table table_name ADD INDEX [index_name] (index_colume_name, ....)

-- 添加主键索引
-- 方式一 建立表直接设置为clolumn 为 primary key

-- 方式二
CREATE TABLE t26 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)

删除索引

DROP INDEX index_name ON table_name;

-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY

修改索引，先删除，在添加新的索引
查找索引（三种方式）

-- 查询索引
show index(es) from table_name;
show keys from table_name;
desc table_name;

-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25

哪些列上适合使用索引

较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select* from emp where sex = 男
更新非常频繁的字段不适合创建索引
select* from emp where logincount= 1
不会出现在WHERE子句（即不会用于查询）中字段不该创建索引

MySQL事务

事务：事务用于保证数据的一致性，它由一组相关的dml语句（修改、、添加、删除）组成，该组的dml（修改、、添加、删除）语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。

事务和锁

当执行事务操作时（dml语句（修改、、添加、删除）），mysq会在表上加锁防止其它用户改表的数据。
这对用户来讲是非常重要的

mysql数据库控制台事务的几个重要操作（基本操作transaction.sql）

1. start transaction --开始一个事务
2. savepoint  保存点名 --设置保存点
3. rollback to 保存点名  --回退事务
4. rollback --回退全部事务
5. commit  --提交事务，所有的操作生效，同时删除设置的保存点，不能回退

细节：

1. 没有设置保存点
2. 多个保存点 
3. 存储引擎
4. 开始事务方式

回退事务 :在介绍回退事务前，先介绍一下保存点（savepoin）。保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点
提交事务：使用commit语句可以提交事务。当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据【所有数据就正式生效】【和隔离级别相关】

事务细节讨论

如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
如果开始一个事务，你没有创建保存点。你可以执行rollback，默认就是回退到你事务开始的状态
你也可以在这个事务中（还没有提交时)，创建多个保存点。比如：savepoint aaa；执行dml；savepoint bbb；
你可以在事务没有提交前，选择回退到哪个保存点
mysql的事务机制需要innodb的存储引擎才可以使用。myisam不好使
开始一个事务start transaction ，set autocommnit = off；

mysql 事务隔离级别

事务隔离级别介绍：

多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。（通俗解释）

如果不考虑隔离性，可能会引发如下问题：
1. 脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读
2. 不可重复读 ：（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
3. 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

事务隔离级别

MySQL隔离级别定义了事务与事务之间的隔离程度

说明：√ 为可能出现，×为不会出现

设置事务隔离级别

-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL [你设置的级别，如：READ UNCOMMITTED]
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别，如：READ UNCOMMITTED]
-- mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊需求，没有必要修改(因为该级别可以满足绝大部分项目要求)

mysql默认的事务隔离级别是repeatable read，修改方法：

全局修改，修改my.ini配置文件，在最后加上
#可选参数有：READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ，SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ

mysql 事务 ACID

事务的 acid 特性

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务必须使数据库从一个致性状态变换到另外一个一致性状态
隔离性（lsolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

mysql 表类型和存储引擎

基本介绍：

MySQL的表类型由存储引擎（StorageEngines）决定，主要包括MyISAM、innoDB、Memory等。
MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB。
这六种又分为两类，一类是”事务安全型”（transaction-safe)，比如：InnoDB；其余都属于第二类，称为”
非事务安全型”（non-transaction-Safe）[mysiam和memory]

主要的存储引擎/表类型特点

细节说明（MyISAM、 InnoDB、 MEMORY ）

MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。
InnoDB存储引擎提供了具有提交、回滚和崩渍恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在。（基于哈希的，存储在内存中，不村存储在磁盘中，对临时表有用）

三种存储引擎表使用案例

-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎， 是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁

-- myisam 存储引擎
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1, 'jack');
SELECT * FROM t28;
ROLLBACK TO t1  -- 错误写法，因为myisam引擎不支持事务

-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了 Mysql 服务， 数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有 IO 读写,IO读写在磁盘中，物理滚动，速度慢) 3. 默认支持索引(hash 表)
CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29
VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB

修改存储引擎

ALTER TABLE `table_name` ENGINE = 存储引擎;

如何选择表的存储引擎

如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快
如果需要支持事务，选择InnoDB。
Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./0的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法 ——用户的在线状态（））

视图

emp表的列信息很多，有些信息是个人重要信息（比如sal，comm，mgr，hiredate），如果我们希望某个用户只能查询emp表的（empno，ename，job和deptno）信息，有什么办法？=> 视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
视图与基表（对应的真实的表）关系的示意图（映射关系）
对视图的总结：
1. 视图是根据基表（可以是多个基表）来创建的视图是虚拟的表
2. 视图也有列，数据来自基表
3. 通过视图可以修改基表的数据

视图的基本使用

create view 视图名 as  select语句
alter view 视图名 asselec 语句-更新成新的视图
SHOW CREATE VIEW 视图名
drop view 视图名1,视图名2

-- 视图的使用
-- 创建一个视图 emp_view01， 只能查询 emp 表的(empno、 ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno,ename,job,deptno FROM emp;
-- 查看视图
DESC emp_view01
SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;

-- 修改视图 会影响到基表
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369
SELECT * FROM emp; -- 查询基表

视图的细节

创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
修改视图会影响到基表，修改基本表，会影响到视图
视图中可以再使用视图 , 数据仍然来自基表

视图最佳实践

安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

Mysql 管理

Mysql 用户

mysql中的用户，都存储在系统数据库mysql重点user表中
其中user表的重要字段说明：
1. host : 允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
2. user : 用户名；
3. authentication string : 密码，是通过mysql的password()函数加密之后的密码。

创建用户

create user '用户名'@'允许登录位置' identified by '密码'
说明 ： 创建用户，同时指定密码

删除用户

drop user '用户名'@'允许登录位置'

用户修改密码

-- 修改自己的密码
set password = password('密码');
-- 修改他人的密码（需要有修改用户密码权限）：
set password for '用户名'@'允许登录位置' = password('密码')

MySQL中的权限

基本语法：某个用户在某个库的某个对象的某些权限

grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']

说明：
1. 权限列表，多个权限用逗号分开
```
grant select on .......
grant select delete create on .......
grant all [privilegesl on ..... //表示赋予该用户在该对象上的所有权限
```
2. 特别说明
  *.*：代表本系统中的所有数据库的所有对象（表，视图，存储过程）
  库.*：表示某个数据库中的所有数据对象（表，视图，存储过程等）
3. identifiedby可以省略，也可以写出
  （1）如果用户存在，就是修改该用户的密码
  （2）如果该用户不存在，就是创建该用户！

回收用户授权

revoke 权限列表 on 库.对象名 from '用户名' '@' '登录位置'

权限生效指令

-- 如果权限没有生效，可以执行下面的命令
-- 基本语法
FLUSH PROVILEGES;

细节说明

在创建用户的时候，如果不指定Host，则默认为%，%表示表示所有IP都有连接权限
create user xxx；
你也可以这样指定： create user xx@192.168.1.%表示xxx用户在192.168.1.的i可以登录mysql
在删除用户的时候，如果host不是%，需要明确指定’用户‘@’host‘ 值。

-- 说明 用户管理的细节
-- 在创建用户的时候， 如果不指定 Host, 则为% , %表示表示所有 IP 都有连接权限
-- create user xxx;
CREATE USER jack
SELECT `host`, `user` FROM mysql.user
-- 你也可以 这样指定
-- create user 'xxx'@'192.168.1.%' 表示 xxx 用户在 192.168.1.*的 ip 可以登录 mysql
CREATE USER 'smith'@'192.168.1.%'
-- 在删除用户的时候， 如果 host 不是 %, 需要明确指定 '用户'@'host 值'
DROP USER jack -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%'