mysql学习笔记一
一。mysql是什么？
总结：mysql就是一个基于socket编写的C/S架构的软件
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型： 1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER 2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT 3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
二。数据库管理软件分类：
分两大类： 关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用 非关系型：mongodb，redis，memcache 总结两句话： 关系型数据库需要有表结构 非关系型数据库是key-value存储的，没有表结构 所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合
代数等数学概念和方法来处理数据库中的数据。 RDBMS即关系数据库管理系统(Relational Database Management System)的特点： 1.数据以表格的形式出现 2.每行为各种记录名称 3.每列为记录名称所对应的数据域 4.许多的行和列组成一张表单 5.若干的表单组成database RDBMS 术语 数据库: 数据库是一些关联表的集合。. 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。 外键：外键用于关联两个表。 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
三。基础的mysql语句：
1.操作文件（库）
帮助方法：help create database;
选择数据库：use 数据库名
增：创建数据库
CREATE DATABASE 数据库名 charset utf8;
删：删除数据库 DROP DATABASE 数据库名;
改：修改数据库字符编码
alter database db1 charset utf8;
查：
# 查看当前创建的数据库
show create database db1;
#查看所在的库 select database();
# 查看所有的数据库
show databases;
2.操作文件（表）
use db1; #切换文件夹 select database(); #查看当前所在文件夹 增：创建表
create table a1(
id int,
name varchar(50),
age int(3)
);删： drop table t1;
改：
# modify修改的意思
alter table t1 modify name char(6);
# 改变name为大写的NAME
alter table t1 change name NAMA char(7);
查：
#查看当前的这张a1表的详细结构
show create table a1\G;
# 查看所有的表
show tables;
# 查看表的详细信息
desc t1;
复制表：https://www.cnblogs.com/iamjianghao/p/10169485.html
3.操作文件内容（数据）
增： # 插入一条数据，规定id，name数据leilei
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
删：
语法：
DELETE FROM 表名
WHERE CONITION;
delete from t1;
delete from t1 where id=2;
改：
update db1.t1 set name=\'zhangsan\'; update db1.t1 set name=\'alex\' where id=2;
语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
查：
select id from db1.t1;
select id,name from db1.t1;
select * from db1.t1;
4.单表查询(基本操作)
语法：
一、单表查询的语法 SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 二、关键字的执行优先级（重点） 重点中的重点：关键字的执行优先级 from #找到表 where #条件 group by #分组查询 having #过滤 select #查询 distinct #去重 order by #查询将结果排序 limit #限制查询条件
like #正则匹配（配合%关键字%使用）
replace #替换
（1）.where约束
where子句中可以使用 1.比较运算符：>、<、>=、<=、<>、!= 2.between 80 and 100 ：值在80到100之间 3.in(80,90,100)值是10或20或30 4.like \'xiaomagepattern\': pattern可以是%或者_。%小时任意多字符，_表示一个字符 5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1 :单条件查询 mysql> select id,emp_name from employee where id > 5; +----+------------+ | id | emp_name | +----+------------+ | 6 | jingliyang | | 7 | jinxin | | 8 | xiaomage | | 9 | 歪歪 | | 10 | 丫丫 | | 11 | 丁丁 | | 12 | 星星 | | 13 | 格格 | | 14 | 张野 | | 15 | 程咬金 | | 16 | 程咬银 | | 17 | 程咬铜 | | 18 | 程咬铁 | #2 多条件查询 mysql> select emp_name from employee where post=\'teacher\' and salary>10000; +----------+ | emp_name | +----------+ | alex | | jinxin | +----------+ #3.关键字BETWEEN AND SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; #注意\'\'是空字符串，不是null SELECT name,post_comment FROM employee WHERE post_comment=\'\'; ps： 执行 update employee set post_comment=\'\' where id=2; 再用上条查看，就会有结果了 #5:关键字IN集合查询 mysql> SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; +------------+---------+ | name | salary | +------------+---------+ | yuanhao | 3500.00 | | jingliyang | 9000.00 | +------------+---------+ rows in set (0.00 sec) mysql> SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; +------------+---------+ | name | salary | +------------+---------+ | yuanhao | 3500.00 | | jingliyang | 9000.00 | +------------+---------+ mysql> SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ; +-----------+------------+ | name | salary | +-----------+------------+ | egon | 7300.33 | | alex | 1000000.31 | | wupeiqi | 8300.00 | | liwenzhou | 2100.00 | | jinxin | 30000.00 | | xiaomage | 10000.00 | | 歪歪 | 3000.13 | | 丫丫 | 2000.35 | | 丁丁 | 1000.37 | | 星星 | 3000.29 | | 格格 | 4000.33 | | 张野 | 10000.13 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +-----------+------------+ rows in set (0.00 sec) #6:关键字LIKE模糊查询 通配符’%’ mysql> SELECT * FROM employee WHERE name LIKE \'jin%\'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec) 通配符\'_\' mysql> SELECT age FROM employee WHERE name LIKE \'ale_\'; +-----+ | age | +-----+ | 78 | +-----+ row in set (0.00 sec) 练习： 1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 #对应的sql语句 select name,age from employee where post = \'teacher\'; select name,age from employee where post=\'teacher\' and age > 30; select name,age,salary from employee where post=\'teacher\' and salary between 9000 and 10000; select * from employee where post_comment is not null; select name,age,salary from employee where post=\'teacher\' and salary in (10000,9000,30000); select name,age,salary from employee where post=\'teacher\' and salary not in (10000,9000,30000); select name,salary*12 from employee where post=\'teacher\' and name like \'jin%\'; where约束
where约束举例
（2）.group by分组查询
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
继续验证通过group by分组之后，只能查看当前字段,如果想查看组内信息，需要借助于聚合函数
（3）.聚合函数
max()求最大值 min()求最小值 avg()求平均值 sum() 求和 count() 求总个数 #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 # 每个部门有多少个员工 select post,count(id) from employee group by post; # 每个部门的最高薪水 select post,max(salary) from employee group by post; # 每个部门的最低薪水 select post,min(salary) from employee group by post; # 每个部门的平均薪水 select post,avg(salary) from employee group by post; # 每个部门的所有薪水 select post,sum(age) from employee group by post;
（4）.having过滤
HAVING与WHERE不一样的地方在于 #！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
验证： mysql> select * from employee where salary>1000000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec) mysql> select * from employee having salary>1000000; ERROR 1463 (42000): Non-grouping field \'salary\' is used in HAVING clause # 必须使用group by才能使用group_concat()函数，将所有的name值连接 mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##错误，分组后无法直接取到salary字段 ERROR 1054 (42S22): Unknown column \'post\' in \'field list\'
验证
（5）.order by查询排序
按单列排序 SELECT * FROM employee ORDER BY age; SELECT * FROM employee ORDER BY age ASC; SELECT * FROM employee ORDER BY age DESC; 按多列排序:先按照age升序排序，如果年纪相同，则按照id降序 SELECT * from employee ORDER BY age ASC, id DESC;
验证多列排序： SELECT * from employee ORDER BY age ASC,id DESC; mysql> SELECT * from employee ORDER BY age ASC,id DESC; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.01 sec) mysql>
验证多列排序
（6）.limit限制查询数量
示例： SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为0 SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
5.多表查询（高级玩法）
重点：外链接语法
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
#（内查询|优先匹配左表记录|全外连接）
（1）先看第一种情况交叉连接：不适用任何匹配条件。生成笛卡尔积（关于笛卡尔积的含义，大家百度自行补脑）。
mysql> select * from employee,department; +----+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 201 | 人力资源 | | 5 | nvshen | male | 18 | 200 | 202 | 销售 | | 5 | nvshen | male | 18 | 200 | 203 | 运营 | | 6 | xiaomage | female | 18 | 204 | 200 | 技术 | | 6 | xiaomage | female | 18 | 204 | 201 | 人力资源 | | 6 | xiaomage | female | 18 | 204 | 202 | 销售 | | 6 | xiaomage | female | 18 | 204 | 203 | 运营 |
笛卡尔积举例
（2）内连接：只连接匹配的行
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了匹配的结果 #department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+---------+------+--------+--------------+ | id | name | age | sex | name | +----+---------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | nvshen | 18 | male | 技术 | +----+---------+------+--------+--------------+ 5 rows in set (0.00 sec) #上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
内连接
（3）外链接之左连接：优先显示左表全部记录
#以左表为准，即找出所有员工信息，当然包括没有部门的员工 #本质就是：在内连接的基础上增加左边有，右边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+----------+--------------+ | id | name | depart_name | +----+----------+--------------+ | 1 | egon | 技术 | | 5 | nvshen | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | xiaomage | NULL | +----+----------+--------------+ 6 rows in set (0.00 sec)
左连接
（4） 外链接之右连接：优先显示右表全部记录
#以右表为准，即找出所有部门信息，包括没有员工的部门 #本质就是：在内连接的基础上增加右边有，左边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+---------+--------------+ | id | name | depart_name | +------+---------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | nvshen | 技术 | | NULL | NULL | 运营 | +------+---------+--------------+ 6 rows in set (0.00 sec)
右连接
（5） 全外连接：显示左右两个表全部记录(了解)
#外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 #注意：mysql不支持全外连接 full JOIN #强调：mysql可以使用此种方式间接实现全外连接 语法：select * from employee left join department on employee.dep_id = department.id union all select * from employee right join department on employee.dep_id = department.id; mysql> select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ; +------+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | xiaomage | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+----------+--------+------+--------+------+--------------+ rows in set (0.01 sec) #注意 union与union all的区别：union会去掉相同的纪录
全外连接
6.符合条件连接查询
示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，即找出年龄大于25岁的员工以及员工所在的部门 select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25; 示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示。 select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;
举例
7.子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等
举例：
（1）带in关键字的子查询
#查询平均年龄在25岁以上的部门名 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); # 查看技术部员工姓名 select name from employee where dep_id in (select id from department where name=\'技术\'); #查看不足1人的部门名 select name from department where id not in (select dep_id from employee group by dep_id);
in关键字查询
（2）带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<> #查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from employee where age > (select avg(age) from employee); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ #查询大于部门内平均年龄的员工名、年龄 思路： （1）先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。 (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。 (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。 mysql> select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; +------+------+ | name | age | +------+------+ | alex | 48 |
比较运算查询
(3)带EXISTS关键字的子查询
#EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False #当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询 #department表中存在dept_id=203，Ture mysql> select * from employee where exists (select id from department where id=200); +----+----------+--------+------+--------+ | id | name | sex | age | dep_id | +----+----------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | nvshen | male | 18 | 200 | | 6 | xiaomage | female | 18 | 204 | +----+----------+--------+------+--------+ #department表中存在dept_id=205，False mysql> select * from employee where exists (select id from department where id=204); Empty set (0.00 sec)
EXISTS关键字查询
小练习：
查询每个部门最新入职的那位员工
#创建表 create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum(\'male\',\'female\') not null default \'male\', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum(\'male\',\'female\') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门：教学，销售，运营 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values (\'egon\',\'male\',18,\'20170301\',\'老男孩驻沙河办事处外交大使\',7300.33,401,1), #以下是教学部 (\'alex\',\'male\',78,\'20150302\',\'teacher\',1000000.31,401,1), (\'wupeiqi\',\'male\',81,\'20130305\',\'teacher\',8300,401,1), (\'yuanhao\',\'male\',73,\'20140701\',\'teacher\',3500,401,1), (\'liwenzhou\',\'male\',28,\'20121101\',\'teacher\',2100,401,1), (\'jingliyang\',\'female\',18,\'20110211\',\'teacher\',9000,401,1), (\'jinxin\',\'male\',18,\'19000301\',\'teacher\',30000,401,1), (\'成龙\',\'male\',48,\'20101111\',\'teacher\',10000,401,1), (\'歪歪\',\'female\',48,\'20150311\',\'sale\',3000.13,402,2),#以下是销售部门 (\'丫丫\',\'female\',38,\'20101101\',\'sale\',2000.35,402,2), (\'丁丁\',\'female\',18,\'20110312\',\'sale\',1000.37,402,2), (\'星星\',\'female\',18,\'20160513\',\'sale\',3000.29,402,2), (\'格格\',\'female\',28,\'20170127\',\'sale\',4000.33,402,2), (\'张野\',\'male\',28,\'20160311\',\'operation\',10000.13,403,3), #以下是运营部门 (\'程咬金\',\'male\',18,\'19970312\',\'operation\',20000,403,3), (\'程咬银\',\'female\',18,\'20130311\',\'operation\',19000,403,3), (\'程咬铜\',\'male\',18,\'20150411\',\'operation\',18000,403,3), (\'程咬铁\',\'female\',18,\'20140512\',\'operation\',17000,403,3) ;
练习题
select * from employee as t1 inner join (select post,max(hire_date) as new_date from employee group by post) as t2 on t1.post=t2.post where t1.hire_date=t2.new_date;
答案
四。mysql支持的存储引擎
mysql> show engines\G;# 查看所有支持的引擎 mysql> show variables like \'storage_engine%\'; # 查看正在使用的存储引擎
create table t1(id int)engine=innodb;# 指定表类型、存储引擎，默认不写就是innodb
1 1、InnoDB 存储引擎 2 3 支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其 4 5 特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。 6 7 InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。 8 9 InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。 10 11 对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。 12 13 InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。 14 15 2、MyISAM 存储引擎 16 17 不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。 18 19 3、NDB 存储引擎 20 21 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。 22 23 4、Memory 存储引擎 24 25 正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。 26 27 5、Infobright 存储引擎 28 29 第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。 30 31 6、NTSE 存储引擎 32 33 网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。 34 35 7、BLACKHOLE 36 37 黑洞存储引擎，可以应用于主备复制中的分发主库。 38 39 MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。
存储引擎介绍
举例：
创建四张表，分别使用innodb,myisam,memory,blackhole存储引擎，进行插入数据测试
create table t1(id int)engine=innodb; create table t2(id int)engine=myisam; create table t3(id int)engine=memory; create table t4(id int)engine=blackhole;
查看data文件下db1数据库中的文件：
#.frm是存储数据表的框架结构 # .ibd是mysql数据文件 #.MYD是MyISAM表的数据文件的扩展名 #.MYI是MyISAM表的索引的扩展名 #发现后两种存储引擎只有表结构，无数据 #memory，在重启mysql或者重启机器后，表内数据清空 #blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录
五。数据类型：
存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的
详细参考链接：http://www.runoob.com/mysql/mysql-data-types.html
mysql常用数据类型参考：https://www.cnblogs.com/majj/p/9164480.html
六。数据类型升级（重点）：
1.char类型和varchar类型对比：
#官网：https://dev.mysql.com/doc/refman/5.7/en/char.html #注意：char和varchar括号内的参数指的都是字符的长度 #char类型：定长，简单粗暴，浪费空间，存取速度快 字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节） 存储： 存储char类型的值时，会往右填充空格来满足长度 例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储 检索： 在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（设置SQL模式：SET sql_mode = \'PAD_CHAR_TO_FULL_LENGTH\'; 查询sql的默认模式：select @@sql_mode;） #varchar类型：变长，精准，节省空间，存取速度慢 字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html） 存储： varchar类型存储数据的真实内容，不会用空格填充，如果\'ab \',尾部的空格也会被存起来 强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用） 如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255） 如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535） 检索： 尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
验证之前了解两个函数：
length()：查看字节数
char_length():查看字符数
char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形。
# 创建t1表,分别指明字段x为char类型，字段y为varchar类型 mysql> create table t1(x char(5),y varchar(4)); Query OK, 0 rows affected (0.16 sec) # char存放的是5个字符，而varchar存4个字符 mysql> insert into t1 values(\'你瞅啥 \',\'你瞅啥 \'); Query OK, 1 row affected (0.01 sec) # 在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少 mysql> select x,char_length(x),y,char_length(y) from t1; +-----------+----------------+------------+----------------+ | x | char_length(x) | y | char_length(y) | +-----------+----------------+------------+----------------+ | 你瞅啥 | 3 | 你瞅啥 | 4 | +-----------+----------------+------------+----------------+ row in set (0.02 sec) #略施小计，让char现原形 mysql> SET sql_mode = \'PAD_CHAR_TO_FULL_LENGTH\'; Query OK, 0 rows affected (0.00 sec) #查看当前mysql的mode模式 mysql> select @@sql_mode; +-------------------------+ | @@sql_mode | +-------------------------+ | PAD_CHAR_TO_FULL_LENGTH | +-------------------------+ row in set (0.00 sec) #原形毕露了吧。。。。 mysql> select x,char_length(x) y,char_length(y) from t1; +-------------+------+----------------+ | x | y | char_length(y) | +-------------+------+----------------+ | 你瞅啥 | 5 | 4 | +-------------+------+----------------+ row in set (0.00 sec) # 查看字节数 #char类型：3个中文字符+2个空格=11Bytes #varchar类型:3个中文字符+1个空格=10Bytes mysql> select x,length(x),y,length(y) from t1; +-------------+-----------+------------+-----------+ | x | length(x) | y | length(y) | +-------------+-----------+------------+-----------+ | 你瞅啥 | 11 | 你瞅啥 | 10 | +-------------+-----------+------------+-----------+ row in set (0.02 sec)
验证过程与方法
#常用字符串系列：char与varchar 注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡 #其他字符串系列（效率：char>varchar>text） TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB BINARY系列 BINARY VARBINARY text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。 mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters. longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
总结
2.枚举类型和集合类型
字段的值只能在给定范围中选择，如单选框，多选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）
mysql> create table consumer( -> id int, -> name varchar(50), -> sex enum(\'male\',\'female\',\'other\'), -> level enum(\'vip1\',\'vip2\',\'vip3\',\'vip4\'),#在指定范围内，多选一 -> fav set(\'play\',\'music\',\'read\',\'study\') #在指定范围内，多选多 -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into consumer values -> (1,\'赵云\',\'male\',\'vip2\',\'read,study\'), -> (2,\'赵云2\',\'other\',\'vip4\',\'play\'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from consumer; +------+---------+-------+-------+------------+ | id | name | sex | level | fav | +------+---------+-------+-------+------------+ | 1 | 赵云 | male | vip2 | read,study | | 2 | 赵云2 | other | vip4 | play | +------+---------+-------+-------+------------+ 2 rows in set (0.00 sec)
版权声明：本文为iamjianghao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。