MySQL笔记

目录

数据库类型表

文本字符串类型 

tinyBlob,Blob,mediumBlob,longBlob

tinytext,text,mediumtext,longtext

char 和 varchar 区别

char比varchar效率更快的原因

整型

浮点型 

日期和时间类型

创建表类型

修改表

添加列

删除列

修改数据表 

添加主键约束

添加唯一约束

添加外键约束

删除主键约束

删除唯一约束

删除外键约束 

修改列操作

更改列的顺序位置 

更改列的数据类型

修改数据表的名称 

使用 ALTER 修改数据表名称

使用 RENAME修改数据表名称 

INSERT的几种使用方式

INSERT 简写方式插入数据(不推荐)

INSERT 完整写法(推荐)

INSERT 批量插入 

update语句

delete语句

select语句

基本的SELECT语句

数学符号条件

“AND”与“OR” 

IN 和 NOT IN

通配符 

distinct

别名 

order by 使用

统计函数的使用

sum函数的使用

avg函数的使用

max 和 min函数的使用

group by + having函数的使用

字符串相关函数

字符串相关函数

数学函数

日期函数

加密函数和系统函数

流程控制函数

加强查询

分页查询

增强group by

自连接

子查询

all 和 any的使用

多列子查询

合并查询

表的复制

表的外连接

主键 

unique的使用

外键

check

自增长

索引

索引的创建

索引的删除

索引的查询

事务

事务的隔离级别

存储引擎

视图

视图的使用

视图的细节

视图练习

MySQL管理

MySQL用户管理

用户权限的管理

 用户管理的细节

---

数据库类型表

默认整数类型是带符号的，即可以有正负值

create table user (id int, age tinyint) ;

如果整数类型设置成如下，则不可以为负值

create table user (id int unsigned, age tinyint unsigned) ;

文本字符串类型 

tinyBlob,Blob,mediumBlob,longBlob

概述：MySQL中，Blob是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。

说明：插入Blob类型的数据必须使用PreparedStatement，因为Blob类型的数据无法使用字符串拼接写。

介绍：MySQL的四种Blob类型 (除了在存储的最大信息量上不同外，他们是等同的)，实际使用中根据需要存入的数据大小定义不同的Blob类型。 

tinytext,text,mediumtext,longtext

TEXT是用于存储可以采取从1个字节到4 GB长格式文本字符串。

在新闻站点中用于存储文章正文的数据类型通常为text。

char 和 varchar 区别

1、最大长度：

char最大长度是255字符，varchar最大长度是65535个字节。

2、定长：

char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。

3、空间使用：

char会浪费空间，varchar会更加节省空间。

4、查找效率：

char查找效率会很高，varchar查找效率会更低。

5、尾部空格：

char插入时可省略，varchar插入时不会省略，查找时省略。
 

char比varchar效率更快的原因

这是由他们在磁盘上存放的不同形式决定的

char类型在存放数据的时候，中间是没有间隔的，数据本身是有空格的，但是数据段之间没有间隔，因为我们在创建列的时候已经告诉MySQL列的长度了，MySQL在查询数据的时候，只需要按部就班寻找就行了，不需要在中途计算这个数据段的长度。
但是varchar类型的存放就不同了，在每个数据段开头，都要有一段空间（1~2个字节）存放数据段的长度，在数据段的结尾还有一段空间（1个字节）标记此字段的节数。MySQL在读取一个数据段的时候，首先要读开头，比如读到了3，说明数据段的长度是3，之后就不多不少，只读3个字节。所以MySQL在遍历数据的时候，磁针要比char类型的列多读很多次磁盘来获取字段的真实长度，这就是为什么varchar比char查询效率低的原因了。
 

整型

在能满足要求的情况下，尽量选择存储空间小的

M：

INT(M): M表示显示宽度，M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。

如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败？
答案：不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。

UNSIGNED：

UNSIGNED: 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。

int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。
 

TINYINT ：

一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

SMALLINT ：

可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT ：

用于较大整数的计算，比如车站每日的客流量等。

INT、INTEGER ：

取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

BIGINT：

只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证
券公司衍生产品持仓等
 

浮点型 

日期和时间类型

MySQl中有多种表示日期和时间的数据类型。其中YEAR表示年份，DATE表示日期，TIME表示时间，DATETIME和TIMESTAMP表示日期和时间。它们的对比如下：
YEAR ，字节数为1，取值范围为“1901——2155”
DATE，字节数为4，取值范围为“1000-01-01——9999-12-31”
TIME，字节数为3，取值范围为“-838:59:59——838:59:59”
DATETIME，字节数为8，取值范围为“1000-01-01 00:00:00——9999-12-31 23:59:59”
TIMESTAMP，字节数为4，取值范围为“19700101080001——20380119111407” 当插入值超出有效取值范围时，系统会报错，并将零值插入到数据库中。

YEAR类型（取值范围为“1901——2155”）
给YEAR类型复制可以有三种方法。
第一种是直接插入4位字符串或者4位数字。
第二种是插入2位字符串，这种情况下如果插入‘00’~‘69’，则相当于插入2000~2069；如果插入‘70’~‘99’，则相当于插入1970~1999。第二种情况下插入的如果是‘0’，则与插入‘00’效果相同，都是表示2000年。
第三种是插入2位数字，它与第二种（插入两位字符串）不同之处仅在于：如果插入的是一位数字0，则表示的是0000，而不是2000年。所以在给YEAR类型赋值时，一定要分清0和‘0’，虽然两者相差个引号，但实际效果确实相差了2000年。
TIME类型（取值范围为“-838:59:59——838:59:59”）
TIME类型表示为“时：分：秒”，尽管小时范围一般是0~23，但是为了表示某些特殊时间间隔，MySQL将TIME的小时范围扩发了，而且支持负值。
对TIME类型赋值，标准格式是‘HH：MM：SS’，但不一定非要是这种格式。
如果插入的是‘D HH：MM：SS’格式，则类似插入了‘（D*24+HH）：MM：SS’。比如插入‘2 23:50:50’，相当于插入了‘71:50:50’。
如果插入的是‘HH：MM’或‘SS’格式，则效果是其他未被表示位的值赋为零值。比如插入‘30’，相当于插入了‘00:00:30’；如果插入‘11:25’，相当于插入了‘11:25:00’。
另外也可以插入‘D HH’和‘D HH：MM’，效果按上面的例子可以推理出来了吧。
在MySQl中，对于'HHMMSS'格式，系统能够自动转化为标准格式。
如果我们想插入当前系统的时间，则可以插入CURRENT_TIME或者NOW()。TIME类型只占3个字节，如果只是存储时间数据，它最合适了。
DATE类型（取值范围为“1000-01-01——9999-12-31”）
MySQL是以YYYY-MM-DD格式来显示DATE类型的值，插入数据时，数据可以保持这种格式。另外，MySQL还支持一些不严格的语法格式，分隔符“-”可以用“@”、“.”等众多富豪来替代。
在插入数据时，也可以使用“YY-MM-DD”格式，YY转化成对应的年份的规则与YEAR类型类似。
如果我们想插入当前系统的时间，则可以插入CURRENT_DATE或者NOW()。
DATETIME类型（取值范围为“1000-01-01 00:00:00——9999-12-31 23:59:59”）
标准格式为“YYYY-MM-DD HH：MM：SS”，具体赋值方法与上面各种类型的方法相似。
TIMESTAMP类型（取值范围为“19700101080001——20380119111407” ）
TIMESTAMP的取值范围比较小，没有DATETIME的取值范围大，因此输入值时一定要保证在TIMESTAMP的范围之内。它的插入也与插入其他日期和时间数据类型类似。
那么TIMESTAMP类型如何插入当前时间？第一，可以使用CURRENT_TIMESTAMP；第二，输入NULL，系统自动输入当前的TIMESTAMP；第三，无任何输入，系统自动输入当前的TIMESTAMP。
另外有很特殊的一点：TIMESTAMP的数值是与时区相关。 

time、timestamp、datetime数据类型四舍五入

CREATE TABLE `date_test` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `d1` date,
  `d2` datetime,
  `d3` time,
  `d4` timestamp,
  `d5` year,
  primary key(id)
) ENGINE=InnoDB AUTO_INCREMENT=0  COMMENT='test测试';

insert into date_test(d2,d4) values('2021-02-23 10:16:55.781','2021-02-23 10:16:55.781');

创建表类型

分析：Sno可用varchar()类型，也可以使用int类型，但表中的学号是依次递增的，故用int类型较好，可以实现按规律的增加；Sname是字符串类型，可用varchar()来存储，一般给10个字符就可以；Sex是字符类型，可用char()来存储，由于性别只有男和女，故给1个字符即可；Sage是数字类型，且最大不超过100，故可以用tinyint来存储；Sdept也是字符串型，可用char()来存储，根据表格来看，给4个字符即可。

create table Student(
    -> Sno int not null auto_increment primary key,
    -> Sname varchar(10) not null,
    -> Sex char(1) not null,
    -> Sage tinyint(100) not null,
    -> Sdept char(4) not null)comment = '学生表';

alter table Student auto_increment = 201215121;
insert into Student (Sname, Sex, Sage, Sdept) values ('李勇', '男', 20, 'CS');
insert into Student (Sname, Sex, Sage, Sdept) values ('刘晨', '女', 19, 'CS');
insert into Student (Sname, Sex, Sage, Sdept) values ('王敏', '女', 18, 'MA');
insert into Student (Sname, Sex, Sage, Sdept) values ('张立', '男', 19, 'IS');

修改表

在MySQL运维中，经常会用到alter这个命令来对数据表进行修改，ALTER tables允许修改表的结构，如增加/删除列，创建/删除索引，改变列的类型，对表中的列重新命名，重新命名数据表，修改表的注释等。不同于UPDATE命名，update主要用于修改字段的值。

添加列

ALTER TABLE table_name  ADD [COLUMN] col_name  column_defination [FIRST | AFTER] col_name

FIRST 和 AFTER 字段指定了新加入的列位于哪一列的之前或者哪一列的之后，如果省略该字段，则默认添加在表的最后

例：在uername之后添加一个 password 字段

ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;

删除列

ALTER TABLE table_name  DROP [COLUMN] col_name 

例：删除user1表中 age 这列

ALTER TABLE user1
DROP age;

例：col1 和 col2 这两列均删除

alter table user1 DROP col1,DROP col2;

修改数据表 

添加主键约束

create table user2(
    username varchar(20) not null,
    pid tinyint unsigned
    );

之后再增加一列 id

alter table user2 add id smallint unsigned;

现在将 id 这一列设置为主键

alter table user2 add constraint PK_user2_id PRIMARY KEY (id); 

 

constraint 这个关键词可以添加也可以不添加，添加了可以为这个关键词起一个名字，这里起了 PK_user2_id的名字，其中 PK表示主键的意思；这里将主键跟在 PRIMARY KEY 之后。

添加唯一约束

ALTER TABLE table_name  ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY][index_name] [index_type](index_col_name,...) 

添加主键约束时只能有一个，而唯一约束可以有多个。这里为 username 字段添加唯一约束，这里要注意，主键约束是确定了表中每条记录的唯一性，而唯一约束是确保了了每一列数据的唯一性，例如有一张表记录用户的姓名和身份证号码，定义了id为primary key auto_increment这样在表中id是一个唯一序列，通过id可以识别到用户，同时将username设置为唯一约束

alter table user2 add UNIQUE (username);

添加外键约束

ALTER TABLE table_name  ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_type](index_col_name,...) reference_defination

例：

alter table user2 add foreign key(pid) references province(id);

删除主键约束

主键只有一个，所以不需要添加索引

ALTER TABLE table_name DROP PRIMARY  KEY

删除唯一约束

因为一个表中可以有多个唯一约束因此不同与删除主键约束，这里需要指定索引

ALTER TABLE table_name DROP {INDEX | KEY} index_name

例：删除username的唯一约束

alter table user2 drop index username;

删除外键约束 

ALTER TABLE table_name DROP FOREIGN KEY fk_symbol

查看表中哪个是外键约束

show create table user2;

  

可以发现user2表中pid的外键为province表中的id

alter table user2 DROP FOREIGN KEY user2_ibfk_1;

 可以发现外键已删除

修改列操作

更改列的顺序位置 

在表user2 中，发现 id 不在第一位置，尽管表中的位置先后没有影响，但是习惯将 id 放置在第一列，因此可以：

alter table user2 MODIFY id smallint(5) unsigned NOT NULL FIRST;

可以发现表中id已经在第一位了 

 

更改列的数据类型

 现在将id 的类型进行修改为 TINYINT类型

alter table user2 MODIFY id TINYINT(5) unsigned NOT NULL;

 修改类型的时候要注意，从高精度修改为低精度类型，可能会造成数据精度丢失的问题，也有可能造成数据丢失，如之前的ID 定义。为 smallint unsigned 为 0-65535，改成tinyint unsigned 之后为 0-255，这样如果有5000条记录会造成丢失

change

使用 change 不仅可以修改列定义，还可以修改列名称，其功能远大于modify

这里将id 更改为 p_id ，将其字段数据类型由smallint(5) unsigned 更改为 tinyint unsigend

alter table user2 CHANGE pid p_id tinyint unsigned NOT NULL;

 

修改数据表的名称 

修改数据表的名称通常有两种方法，分别是使用关键字 ALTER 和 RENAME

使用 ALTER 修改数据表名称

例：将user2改名为user

alter table user2 RENAME user;

使用 RENAME修改数据表名称 

例：将user改名为user_new

alter table user RENAME user_new;

INSERT的几种使用方式

INSERT 简写方式插入数据(不推荐)

简写方式：使用Insert into 表名 values(值1,值2)进行插入

　　注意:insert这种简写的方式虽然非常简单,但是Values后面的值必须和表中的类顺序对应,且类型要保持一致,即使表中某一个列不需要值也必须赋值为null,比如我们的主键id设置的是递增实际上是不用设置值的,但是使用这种方式必须赋值为null
　　不推荐的原因:在实际开发中如果使用此方法进行插入数据,后面表进行了改动(比如字段顺序改变了)那么整个语句都将报错,扩展性及其差,且维护起来比较困难

INSERT 完整写法(推荐)

使用Inset into 表名(字段1,字段2) values(值1,值2)进行插入

推荐使用的原因:这一次我们设置了没有给id赋任何值包括null,而且不用关心表中字段的顺序,比如下面不按照正常顺序添加,我们将age放在第一,name放在第二个.也能添加成功;需要注意的是表名后面的字段名必须和后面values赋的值保持一致;实际开发中在维护和扩展方面都比简写要好
 

INSERT 批量插入 

使用Insert into 表名(字段1,字段2,字段3) values(值1,值2,值3)(值1,值2,值3)进行插入

例：

insert into user(id,username,p_id,uid)
values(1,'jack',2,1),
(2,'tom',3,1);

update语句

将所有员工薪水修改为5000元。[如果没有带where 条件，会修改所有的记录，因此要小心]

UPDATE employee SET salary = 5000 

 将姓名为 小妖怪 的员工薪水修改为3000元

UPDATE employee 
	SET salary = 3000 
	WHERE user_name = '小妖怪' 

将 老妖怪 的薪水在原有基础上增加1000元

UPDATE employee 
	SET salary = salary + 1000 
	WHERE user_name = '老妖怪' 

 可以修改多个列的值

UPDATE employee 
	SET salary = salary + 1000 , job = '出主意的'
	WHERE user_name = '老妖怪' 

delete语句

用法：

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 您可以在单个表中一次性删除记录。

删除表中名称为’老妖怪’的记录

DELETE FROM employee 
	WHERE user_name = '老妖怪';

 删除表中所有记录

DELETE FROM employee;

删除employee这个表 

DROP TABLE employee;

delete，drop，truncate 都有删除表的作用，区别在于：

 delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除
 delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚
 执行的速度上，drop>truncate>delete

select语句

在数据库操作语句中，使用最频繁，也被认为最重要的是 SELECT 查询语句。

基本的SELECT语句

SELECT 语句的基本格式为：

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

如果要查询表的所有内容，则把 要查询的列名 用一个星号 * 号表示，代表要查询表中所有的列。

select * from employee;

而大多数情况，我们只需要查看某个表的指定的列，比如要查看employee 表的 name 和 age： 

SELECT name,age FROM employee;

数学符号条件

SELECT 语句常常会有 WHERE 限制条件，用于达到更加精确的查询。WHERE限制条件可以有数学符号 (=,<,>,>=,<=)

筛选出 age 大于 25 的结果：

SELECT name,age FROM employee WHERE age>25;

或者查找一个名字为 Mary 的员工：

SELECT name,age,phone FROM employee WHERE name='Mary';

“AND”与“OR” 

从这两个单词就能够理解它们的作用。WHERE 后面可以有不止一条限制，而根据条件之间的逻辑关系，可以用 OR(或) 和 AND(且) 连接：

筛选出 age 小于 25，或 age 大于 30:

SELECT name,age FROM employee WHERE age<25 OR age>30;

筛选出 age 大于 25，且 age 小于 30:

SELECT name,age FROM employee WHERE age>25 AND age<30;

 而刚才的限制条件 age>25 AND age<30 ，如果需要包含25和30这两个数字的话，可以替换为 age BETWEEN 25 AND 30 

IN 和 NOT IN

关键词IN和NOT IN的作用和它们的名字一样明显，用于筛选“在”或“不在”某个范围内的结果，比如说我们要查询在dpt3或dpt4的人:

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

NOT IN的效果则是，如下面这条命令，查询出了不在dpt1也不在dpt3的人：

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

通配符 

关键字 LIKE 在SQL语句中和通配符一起使用，通配符代表未知字符。SQL中的通配符是 _ 和 % 。其中 _ 代表一个未指定字符，% 代表不定个未指定字符。

比如，要只记得电话号码前四位数为1101，而后两位忘记了，则可以用两个 _ 通配符代替：

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

这样就查找出了1101开头的6位数电话号码：

另一种情况，比如只记名字的首字母，又不知道名字长度，则用 % 通配符代替不定个字符：

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

这样就查找出了首字母为 J 的人

distinct

过滤表中重复数据使用 distinct:

SELECT DISTINCT english FROM student;

别名 

使用别名表示学生分数 

SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score 
    FROM student;

order by 使用

-- 演示order by使用
-- 对数学成绩排序后输出【升序】。
SELECT * FROM student 
	ORDER BY math;
-- 对总分按从高到低的顺序输出 [降序] -- 使用别名排序
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;

统计函数的使用

统计一个班级共有多少学生

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);

 count(*) 返回满足条件的记录的行数

SELECT COUNT(*) FROM t15;

count(列): 统计满足条件的某列有多少个，但是会排除 为null的情况

SELECT COUNT(`name`) FROM t15;

sum函数的使用

统计一个班级数学总成绩

SELECT SUM(math) FROM student;

统计一个班级语文、英语、数学各科的总成绩

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;

avg函数的使用

求一个班级数学平均分？

SELECT AVG(math) FROM student;

求一个班级总分平均分

SELECT AVG(math + english + chinese) FROM student;

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;

group by + 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', 'NEW YORK'), 
(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 /*部门编号*/
);
DESC emp;

 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);

GROUP by用于对查询的结果分组统计, (示意图)
having子句用于限制分组显示结果 

如何显示每个部门的平均工资和最高工资

SELECT AVG(sal), MAX(sal) , deptno 
	FROM  emp GROUP BY deptno; 

使用数学方法，对小数点进行处理

显示每个部门的每种岗位的平均工资和最低工资

SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno 
    FROM  emp GROUP BY deptno;  

显示每个部门的每种岗位的平均工资和最低工资

SELECT AVG(sal), MIN(sal) , deptno, job 
    FROM  emp GROUP BY deptno, job; 

 显示平均工资低于2000的部门号和它的平均工资 // 别名

 [写sql语句的思路是化繁为简,各个击破]
1. 显示各个部门的平均工资和部门号
2. 在1的结果基础上，进行过滤，保留 AVG(sal) < 2000
3. 使用别名进行过滤   

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) FROM emp;

 

INSTR (string ,substring )    返回substring在string中出现的位置,没有返回0
dual 亚元表, 系统表 可以作为测试表使用

SELECT INSTR('zhaolaoban', 'lao') 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个字符

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('ztc', 'ztc') 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('  ztc') FROM DUAL;
SELECT RTRIM('ztc   ') FROM DUAL;
SELECT TRIM('    ztc   ') FROM DUAL;

 

数学函数

-- 演示数学相关函数

-- 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;
-- 下面的含义是 16 是16进制的16, 转成 10进制输出
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;

日期函数

-- 日期时间相关函数

-- 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), 
	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', '1990-01-01') FROM DUAL;
-- 请用mysql 的sql语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') 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;

加密函数和系统函数

-- 演示加密函数和系统函数

-- USER()	查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
-- DATABASE()	查询当前使用数据库名称
SELECT DATABASE();

-- MD5(str)	为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('2537') FROM DUAL;
SELECT LENGTH(MD5('226')) FROM DUAL;

-- 演示用户表，存放密码时，是md5
CREATE TABLE ztc_user
	(id INT , 
	`name` VARCHAR(32) NOT NULL DEFAULT '', 
	pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO ztc_user 
	VALUES(100, 'ztc', MD5('ztc'));
SELECT * FROM ztc_user; -- csdn

SELECT * FROM ztc_user  -- SQL注入问题
	WHERE `name`='ztc' AND pwd = MD5('ztc')  


-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密

SELECT PASSWORD('ztc') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC


-- select * from mysql.user \G 	从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表 
SELECT * FROM mysql.user

流程控制函数

# 演示流程控制语句

# 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;

	

加强查询

-- 查询加强
-- ■ 使用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;

分页查询

-- 分页查询
-- 按雇员的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 * FROM emp
	ORDER BY empno 
	LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
	
	
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY  job;
-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp  WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;
	
	
	

增强group by

-- 增强group by 的使用

-- (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(*), COUNT(IF(comm IS NULL, 1, NULL)) AS NONE
	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;



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

自连接

-- 多表查询的 自连接

-- 思考题: 显示公司员工名字和他的上级的名字

-- 分析： 员工名字 在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;

子查询

-- 子查询的演示
-- 请思考：如何显示与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 
	-- <>即为!=
	SELECT ename, job, sal, deptno
	FROM emp
	WHERE job IN (
		SELECT DISTINCT job 
		FROM emp 
		WHERE deptno = 10
	) AND deptno != 10
	

	

all 和 any的使用

-- 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
		)

 SELECT ename, sal, deptno
	FROM emp
	WHERE sal > (
		SELECT min(sal) 
			FROM emp
			WHERE deptno = 30
		)

多列子查询

-- 多列子查询

-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1， 字段2 ...) = (select 字段 1，字段2 from 。。。。)

-- 分析: 1. 得到smith的部门和岗位

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'

合并查询

-- 合并查询

SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5

SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union all 就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union  就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION 
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

表的复制

-- 表的复制
-- 为了对某个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;

表的外连接

-- 外连接

-- 比如：列出部门名称和这些部门的员工名称和工作，
-- 同时要求 显示出那些没有员工的部门。

-- 使用我们学习过的多表查询的SQL， 看看效果如何?

SELECT dname, ename, job 
	FROM emp, dept
	WHERE emp.deptno = dept.deptno
	ORDER BY dname
SELECT * FROM dept;

SELECT * FROM emp;


-- 创建 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;

-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, job
	FROM dept LEFT JOIN emp
	ON dept.deptno = emp.deptno
	
-- 使用右外连接实现

SELECT dname, ename, job
	FROM emp RIGHT JOIN dept
	ON dept.deptno = emp.deptno

主键 

主键（primary key ）没有着明确的概念定义，其是索引的一种，并且是唯一性索引的一种，且必须定义为“PRIMARY KEY”，主键不能重复，一个表只能有一个主键。

Mysql中规定自增列必须为主键，在插入时，如果自动增长列没有输入值，则插入的值为自动增长后的值；如果输入的值为0或者null，则插入的值也为自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，则可以直接插入。

1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。 
所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。数据库在设计时，主键起到了很重要的作用。 
主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。 

2. 一个表中可以有多个唯一性索引，但只能有一个主键。

3. 主键列不允许空值，而唯一性索引列允许空值。 

4. 索引可以提高查询的速度。 
其实主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中，主键一般都要建，主要是用来避免一张表中有相同的记录，索引一般可以不建，但如果需要对该表进行查询操作，则最好建，这样可以加快检索的速度。
 

-- 主键使用

-- 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');
	
SELECT * FROM t17;

-- 主键使用的细节讨论
-- 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(列名); 
CREATE TABLE t19
	(id INT , 
	`name` VARCHAR(32) PRIMARY KEY, 
	email VARCHAR(32)
	);

CREATE TABLE t20
	(id INT , 
	`name` VARCHAR(32) , 
	email VARCHAR(32),
	PRIMARY KEY(`name`) -- 在表定义最后写 primary key(列名)
	);
 
-- 使用desc 表名，可以看到primary key的情况

DESC t20 -- 查看 t20表的结果，显示约束的情况
DESC t18

unique的使用

-- unique的使用

CREATE TABLE t21
	(id INT UNIQUE ,  -- 表示 id 列是不可以重复的.
	`name` VARCHAR(32) , 
	email VARCHAR(32)
	);
	
INSERT INTO t21
	VALUES(1, 'jack', 'jack@sohu.com');

INSERT INTO t21
	VALUES(1, 'tom', 'tom@sohu.com');
	
-- unqiue使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
	VALUES(NULL, 'tom', 'tom@sohu.com');
SELECT * FROM t21;

-- 2. 一张表可以有多个unique字段

CREATE TABLE t22
	(id INT UNIQUE ,  -- 表示 id 列是不可以重复的.
	`name` VARCHAR(32) UNIQUE , -- 表示name不可以重复 
	email VARCHAR(32)
	);
DESC t22

外键

-- 外键演示

-- 创建 主表 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_class;

-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class
	WHERE id = 100; 

check

-- 演示check的使用
-- mysql5.7目前还不支持check ,只做语法校验，但不会生效
-- 了解 
-- 学习 oracle, sql server, 这两个数据库是真的生效.

-- 测试
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)
	);
	
-- 添加数据
INSERT INTO t23 
	VALUES(1, 'jack', 'mid', 1);
SELECT * FROM t23;

自增长

-- 演示自增长的使用
-- 创建表
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');
SELECT * FROM t25;

CREATE DATABASE tmp;
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
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 NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
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);	

索引

 

索引的创建

-- 创建索引
CREATE TABLE t25 (
	id INT ,
	`name` VARCHAR(32));
	
-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引 
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 添加普通索引方式1
CREATE INDEX id_index ON t25 (id);
-- 如何选择 
-- 1. 如果某列的值，是不会重复的，则优先考虑使用unique索引, 否则使用普通索引
-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)

-- 添加主键索引
CREATE TABLE t26 (
	id INT ,
	`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)

SHOW INDEX FROM t25

索引的删除

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

-- 修改索引 ， 先删除，再添加新的索引

索引的查询

-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25

事务

 

-- 事务的一个重要的概念和具体操作
-- 看一个图[看示意图]
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27
	( id INT,
	  `name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION 
-- 3. 设置保存点
SAVEPOINT a
-- 执行dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;

SAVEPOINT b
-- 执行dml操作
INSERT INTO t27 VALUES(200, 'jack');

-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK 
COMMIT

-- 讨论 事务细节
-- 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隔离级别定义了事务与事务之间的隔离程度。

 

• 查看当前会话隔离级别（SELECT @@tx_isolation）
• 查看系统当前隔离级别（SELECT @@global.tx_isolation）
• 设置当前会话隔离级别（SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED）
• 设置系统当前隔离级别（SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]）

 

存储引擎

 

视图

视图的使用

-- 创建一个视图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;

视图的细节

-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm) 
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]

-- 修改视图 会影响到基表

UPDATE emp_view01 
	SET job = 'MANAGER' 
	WHERE empno = 7369
	
SELECT * FROM emp; -- 查询基表


SELECT * FROM emp_view01

-- 修改基本表， 会影响到视图

UPDATE emp 
	SET job = 'SALESMAN' 
	WHERE empno = 7369

-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
DESC emp_view01

CREATE VIEW emp_view02
	AS
	SELECT empno, ename FROM emp_view01
	
SELECT * FROM emp_view02

视图练习

-- 视图的课堂练习
-- 针对 emp ，dept , 和   salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]

/*
	分析: 使用三表联合查询，得到结果
	将得到的结果，构建成视图
	  
*/
CREATE VIEW emp_view03
	AS
	SELECT empno, ename, dname, grade
	FROM emp, dept, salgrade
	WHERE emp.deptno = dept.deptno AND 
	(sal BETWEEN losal AND hisal) 

DESC emp_view03
SELECT * FROM emp_view03
	

MySQL管理

MySQL用户管理

Mysql用户的管理

原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限。所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用。

创建新的用户

CREATE USER 'ztc'@'localhost' IDENTIFIED BY '123456'

(1) 'ztc'@'localhost' 表示用户的完整信息 'ztc' 用户名 'localhost' 登录的IP

(2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码（*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9）

删除用户

DROP USER 'ztc'@'localhost'

登录

root 用户修改 ztc@localhost 密码, 是可以成功.
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('123456')

用户权限的管理

-- 创建用户 ztc  密码 123 , 从本地登录
CREATE USER 'ztc'@'localhost' IDENTIFIED BY '123'

-- 使用root 用户创建 testdb  ,表 news
CREATE DATABASE testdb
CREATE TABLE news (
	id INT ,
	content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;

-- 给 ztc 分配查看 news 表和 添加news的权限
GRANT SELECT , INSERT 
	ON testdb.news
	TO 'ztc'@'localhost'
	
-- 可以增加update权限
GRANT UPDATE  
	ON testdb.news
	TO 'ztc'@'localhost'
	
	
-- 修改 ztc的密码为 abc
SET PASSWORD FOR 'ztc'@'localhost' = PASSWORD('abc');

-- 回收 ztc 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'ztc'@'localhost'
REVOKE ALL ON testdb.news FROM 'ztc'@'localhost'

-- 删除 ztc
DROP USER 'ztc'@'localhost'

 用户管理的细节

在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限 

create user  xxx;

你也可以这样指定：

create user  'xxx'@'192.168.1.%'  表示 xxx用户在 192.168.1.*的ip可以登录mysql

在删除用户的时候，如果 host 不是 %, 需要明确指定  '用户'@'host值'

DROP USER jack -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%'