mysql学习笔记(一)
mysql视频学习做的笔记，上传一下，看一下csdn的直接上传md文件编辑排版怎么样。感觉效果还是非常不错的，不过某些字段缩进还是有些问题。为什么有些代码可以高亮，有些又不可以，emmmm。
sql的分类
- 数据查询语言（dql ：data query language）
- 代表关键字：select
- 数据操作语言（dml：data manipulation language）
- 代表关键字：insert，delete，update
- 数据定义语言（ddl：data definition language）
- 代表关键字：create，drop，alter
- 事物控制语言（tcl：transactional control language）
- 代表关键字：commit，rollback
- 数据控制语言（dcl：data control language）
- 代表关键字：grant，revoke
#创建数据库
create database fliename；
#选择数据库
use database_name
#查询当前数据库
select database();
#查询数据版本
select version();
#终止当前语句
\c
#退出mysql
\q,QUIT，EXIT
#显示命令
show databases
show tables
desc tabele_name
#查看怎么创建的某个表
show create table 【name】查询语句
#无所谓大小写
select 【字段，字段，字段】 from 表
#条件查询
select 【 】 from 表 where 表达式
找出工作岗位是manger和工作岗位是salesman的员工
select
ename，job
from
emp
where
job= 'manger' or job='salesman'；
等同于
select
ename,job
from
emp
where
job in ('manger','salesman')；
######################################3
like 用法： 其中%代表0～n个字符 _代表一个任意字符
select ename from emp where ename like '%o%' 模糊查询
###################################################
升序排列，降序排列
select ename，sal from emp order by sal asc；
#升序排列
select ename，sal from emp order by sal desc；
#降序排列数据处理函数/单行处理函数
| Lower | 转换成小写 |
|---|---|
| upper | 转换成大写 |
| substr | 取子字符串（substr（被截取的字符串，起始下标，截取的长度）） |
| length | 去长度 |
| trim | 去空格 |
| str_to_date | 将字符串转换成日期 |
| date_format | 格式化日期 |
| format | 设置千分位 |
| round | 四舍五入 |
| rand() | 生成随机数 |
| ifnull | 可以将null转换成一个具体值 |
关于mysql中的日期处理
每一个数据库处理日期的时候，采用的机制是不同的，日期处理都有自己的一套机制。所以在实际的开发中，表中的字段定义为DATA类型，这种情况很少。因为一旦使用日期类型，那么java程序将不能够通用那么在实际开发中，一般会使用”日期字符串“来表示日期
日期是数据库本身的特色，也是数据库本身机制中的一个重要的内容，所以还是需要掌握
MYSQL数据库管理系统中对日期提供了两个重要的函数：
str_to_date
date_format
str_to_date
该函数的作用是：将”日期字符串“转换成”日期类型“数据。【varchar—>date】
该函数的执行结果是date类型
该函数的使用格式：
str_to_date(‘日期类型’,’日期格式’)
关于MYSQL中的日期格式：
回顾java中的日期格式： yyyy 年
MM 月
dd 日
HH 时
mm 分
ss 秒
SSS 毫秒
java中将字符串转换成日期类型：
SimpleDateFormat sdf = new SimpleDateFormat（"yyyy-MM-dd"） Date date = sdf.parse("1970-10-10")MYSQL的日期格式：
| %Y | 年 |
|---|---|
| %m | 月 |
| %d | 日 |
| %H | 时 |
| %i | 分 |
| %s | 秒 |
案例：查询出1980-12-17入职的员工
select ename from hirdate from emp where hirdate='1980-12-17'
#mysql默认的日期格式：%Y-%m-%d，以上的日期字符串'1980-12-17'正好和默认的日期格式一致，存在了自动类型 转换，自动将日期字符串转换了日期类型，所以以上查询可以查询出结果。
#如果写下面语句则会报错
select ename from hirdate from emp where hirdate='12-17-1980'
#想要纠正错误可以改成以下语句
select ename from hirdate from emp where hirdate=str_to_date('12-17-1980','%m-%d-%Y')date_format
该函数的作用是：将日期类型date转换成具有特定格式的日期字符串varchar
该函数的运算结果是：varchar类型【具备特定格式的】
该函数的语法的格式：
date_format(日期类型数据，’日期格式’)
- 多用于查询操作
分组函数/聚合函数/多行处理函数
| count | 取得记录数 |
|---|---|
| sum | 求和 |
| avg | 取平均 |
| max | 取最大的数 |
| min | 取最小的数 |
以上函数自动忽略空值，分组函数不能使用在where语句中
去除重复内容
distinct只能放在记录最前面
select distinct job from emp；
select distinct deptno，job from emp；#将两个字段同时来看分组查询
group by
group by 【表示通过哪个或者哪些字段进行排序】
案例：找出每个工作岗位的最高薪水
#先按照工作岗位分组，使用max函数求每一组的最大值 select max(sal) from emp group by job #其中 emp group by job 是一个整体 #表示先按照job分组，然后对每一组使用max（sal）求最高薪水 #在这里有个坑 select ename,job,max(sal) from emp group by job #在低版本的mysql中这个语句是可以执行的但是执行结果没有意义，这是由于语法在这方面比较松散，不严格。因为ename这个字段根本不是分组字段所以是不能放在select后面的。在其他数据库中比如oracle中时会报错的 #重点：若一条DQL语句中有group by 子句，那么select关键字后面只能跟参与分组的字段和分组函数having
having和where功能相同都是为了完成数据的过滤
where和having后面都是添加条件
where在group by之前完成过滤
having在group 后面完成过滤
案例：找出每个工作岗位的平均薪水，要求显示平均薪水大于1500
select job,avg(sal) from emp group by job having avg(sal)>1500原则：
尽量在where中过滤，无法过滤的数据，通常都是需要先分组之后再过滤的，这个时候可以选择使用having。
一个完整的DQL语句的总结：
select
×××××
from
×××××
where
×××××
group
×××××
having
×××××
order by
×××××
第一：以上的关键字顺序不能变，严格遵守
第二：执行顺序：
1、from 从某张表中检查数据
2、where 经过某条件进行过滤
3、group 然后分组
4、having 分组之后不满意在过滤
5、select 查询出来
6、order by 排序输出
连接查询
连接查询，也可以叫跨表查询，需要关联多个表进行查询
链接查询根据出现的年代分类：
- sql92
- sql99
链接查根据链接方式可以分为：
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全连接【不讲，使用很少】
当多张表进行连接查询，若没有任何条件限制，会发生什么现象？
案例1：查询每个员工所在的部门名称，要求最终显示员工名和对应的部门名。
- 小知识点：在进行多表连接查询的时候，尽量给表起别名，这样效率高，可读性高。
select e.ename,d.dname from emp e,dept d; #如果不增加任何限制的话会出现笛卡尔积现象 #注意：连接查询过程中虽然使用了限制条件，但是匹配的次数没有减少，还是相乘次数，只不过这一次显示的结果都是有效记录 select e.ename,d.dname from emp e,dept d where e.xx = d.xx案例2：找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级
#sql99语法中的等值连接中的非等值连接 select e.ename,e.sal,s.grade from emp e 【inner】 join salgrade s on e.sal between s.local ans s.hisal #sql92语法： select e.ename,e.sal,s.grade from emp e ， salgrade s where e.sal between s.local ans s.hisal案例3：找出每一个员工的上级领导，要求显示员工名以及对应的领导名
#同一张表，我们需要吧一张表看成两张表 #sql99语法中内连接中的非等值连接 select a.ename empname ,b.ename leadername from emp a 【inner】join emp b on a.mgr = b.empno;案例4：找出每一个员工对应的部门名称，要求部门名称全部显示
#内连接： # a表和b表能够完全匹配的记录查询出来，就被称为内连接 #外连接： # a表和b表能够完全匹配的记录查询出来之外，将其中一张表的记录无条件的 完全查询出来，对方表没有匹配的记录，会自动模拟出null与之匹配，这种 查询被称为外链接。 外连接查询的结果条数>=内连接的查询结果条数 #sql99语法：外连接中的右外连接【右连接】 select e.ename,d.dname from emp e right 【outer】 join dept d on e.deptno = deptno; #outer可以省略 #sql99语法：外连接中的左外连接【左连接】 select e.ename,d.dname from dept d left 【outer】 join emp e on e.deptno = deptno; #outer可以省略 #上面两种写法是相同的，任何一个右外连接都可以写成左外连接，反之同样成立为什么inner和outer可以省略，加上去有什么好处？
- 可以省略，因为区分内连接和外连接依靠的不是这些关键字，而是看sql 语句中是否存在right和left，若存在，表示一定是一个外连接，其他的都是内连接
- 加上去的好处是增强可读性
多张表进行表连接的语法格式：
select xxx from a
join
b
on
条件
join
c
on
条件；
原理：
子查询
1、什么是子查询？
- select语句嵌套select语句
2、子查询可以出现在哪而？
select…(select)
from…(select)
where..(select)
3、where后面添加select子查询
案例：找出薪水比公司平均薪水高的员工，要求显示员工名和薪水。
select ename，sal from emp where sal > avg(sal) #以上语句执行报错，分组函数不能直接使用在where后面 #正确做法 #第一步：找出公司的平均薪水 #第二部：找出薪水大于平均薪水的员工信息 select ename,sal from emp where sal > (select avg(sal) from emp)
4、from后面使用子查询
案例：找出每个部门的平均薪水，并且要求显示平均薪水的薪水等级。
第一步：找出每个部门的平均薪水
select deptno, avg(sal) as avgsal from emp group by deptno;第二步：将上面的临时查询结果当做临时表t，t表进行表连接，条件：t.avg(sal) between s.local and s.hisal
select
t.deptno,t.avgsal,s.grade
from
(select deptno, avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.local and s.hisal;
5、在select后面使用子查询【了解】
select e.ename,(select d.dname from dept d where e,deptno = d.deptno) as dname from emp e;union
union可以合并集合（相加）
select ename,job from emp where job = "manager"
union
select ename,job from emp where job = "salesman"
#等同于以下写法
select ename,job from emp where job in ["manager","salesman"]limit
- limit用来获取一张表中的某部分数据
- limit只有在mysql数据库中存在，不通用，是mysql数据库管理系统的特色
#案例1：找出员工表中前5条记录
select ename from emp limit 5；
#以上的sql语句的“limit 5”中的5表示从表中记录下标0开始，取5条等同于下面的sql语句
select ename from emp limit 0,5；
#limit的使用语法格式：limit起始下标没有指定，默认从0开始，0表示表中第一条记录。
#案例2：找出公司中工资排名在前5名的员工
select ename,sal from emp order by sal desc limit 5;- mysql中通用的分页sql语句：
#每页显示3条记录
#每页显示3条记录
#第1页：0,3
#第2页：3,3
#第3页：6,3
#第4页：9,3
#。。。。
#每页显示pagesize条记录
#第pageNo页：（pageNo-1）×pagesize，pageSize
select ename,sal from emp order by sal desc limit （（pageNo-1）×pagesize，pageSize）;表
创建表
- 表格（table），用来存储数据，表格是一种结构化文件。
- 表格行被称为记录（表中的数据），表格列被称为字段。
- 表格的字段属性包括：字段名称、字段数据类型、字段长度、字段约束
create table tableName（ columnName dataType(length), columnName dataType(length) ）; set character_set_results='gbk';关于mysql数据库中的数据类型？
- varchar 可变长度字符串
- 空间效率高，时间效率低
- char 定长字符串
- 时间效率高，但是空间效率不一定会高
- int 整数型
- int(3)表示最大可以存储999
- bigint 长整形
- 对应的java程序中的lon类型
- float 浮点型单精度
- double 浮点型双精度
- double（7,2）表示7个有效数字，2个小数位
- date 日期类型
- 在实际来发中为了通用，所以日期类型一般不适用，采用字符串代替日期类型比较多
- blob Binary Largre Object二进制大对象
- 专门存储图片声音视频等数据
- 数据库表中存储一个图片是很常见的，但是存储一个比较大的视频是很少见的，一般都是提供一个视频的链接地址
- clob character Large Object字符大对象
- 可以存储比较大的文本，4G+的字符串可以存储
- 其他
insert
- 向表格插入数据必须是使用insert语句，这属于dml语句
- dml语句包括：insert，update，delete、
- insert语句的语法格式：
- insert into tablename （columnname1，columnname2，columnname3）values（value1，value2，value3）
- 字段和值必须一一对应，个数必须相同，数据类型必须一致。
关于sql脚本
- 该文件是一个普通的文本文件，后缀名.sql，被称为sql脚本
- 在sql脚本中有大量的sql语句，想批量的执行sql语句，可以将这些sql语句写入sql脚本文件中，直接使用source执行这个脚本，可以执行大量的sql语句
增删改表结构(不是很重要)（DDL）
#如需求改变，需要向t_studnts中加联系电话字段，字段名称为：contact_tel 类型为varchar（40）
#增
alter table t_student add contact_tel varchar(40)
#将长度改为20
#改
alter table t_student modify tel varchar(20)
#删
alter table t_student drop tel增删改表中的数据【insert、update、delete】
update
update语句的语法格式：
- update tablename set 字段名 = 字段值，字段名 = 字段值，where 条件；
注意：update语句没有条件，会将一张表中所有的数据全部更新
#将no=3的记录name修改为zhangsan，email修改为zhangsan@bjpowernode.com update t_student set name='zhangsan',email ='zhangsan@bjpowernode.com' where no = 3; #将所有的name都修改为lisi update t_student set name = 'lisi'; #将emp_bak中的所有的名字中含有o的员工名修改为zhangsan update emp_bak set ename = 'zhangsan' where ename like '%o%'; #将emp_bak表中所有工作岗位是manager和salesman的员工工资上调10% update emp_bak set sal=sal*1.1 where job = 'manager' or job = 'salesman'
delete
delete 语句的语法格式：
- delete from tablename where 条件
注意：若没有条件限制，会将这种表中所有的记录全部删除
#删除学号=3的学生 delete from t_student where no = 3 #删除所有记录 delete from t_student
约束
什么是约束，为什么要使用约束？
- 约束对应的英语单词：constraint
- 约束实际上就是表中数据的限制条件
- 表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效
约束包括哪些？
- 非空约束 not null
- 唯一性约束 unique
- 主键约束 primary key 简称PK
- 外键约束 foreign key 简称FK
- 检查约束【目前mysql不支持，oracle支持】
非空约束
not null约束的字段，不能为NULL值，必须给定具体的数据
创建表，给字段添加非空约束【创建用户表，用户名不能为空】
create table t_user( id int(10), name varchar(32) not null not null, email varchar(128) );唯一性约束
- unique约束的字段具有唯一性，不可重复
#创建用户，保证邮箱地址唯一 #列级约束 create table t_user( id int(10), name varchar(32) not null not null, email varchar(128) unique ); #表级约束 create table t_user( id int(10), name varchar(32) not null not null, email varchar(128) ， unique（email） ); #注意，使用表级约束给多个字段联合添加约束【以下程序表示name和email两个字段联合唯一】 create table t_user( id int(10), name varchar(32) not null not null, email varchar(128) ， unique（name,email） ); #表级约束还可以给约束起名字 #为什么要起名字，因为以后可以通过这个名字删除这个约束 drop table if exist t_user; create table t_user( id int(10), name varchar(32) not null not null, email varchar(128) ， constraint t_user_email_unique unique（name,email） ); #查询约束名字 use information_schema; show tables; #其中有个表叫table_constraint的表专门用来存储约束信息的 select constraint_name from table_constraints where table_name='t_user';主键约束-primary key 简称PK
主键涉及到的术语：
主键约束
- 主键字段
主键值
以上的主键约束、主键字段、主键值的关系？
表中的某个字段添加主键约束之后，该字段被称为主键字段，主键字段中出现的每一个数据都被称为主键值。
给某个字段添加主键约束primary key后，该字段不能重复，并且也不能为空。效果和not null unique相同，但是本质不同，主键约束除了可以做到not null unique之外，主键字段还会默认添加“索引-index”
一张表应该有主键字段，若没有，表示这张表是无效的。“主键值”是当前行数据的唯一标识。“主键值”是当前行数据的身份证号。即使表中的两行记录相关的数据是相同的，但是由于主键值不同，我们认为这是两行完全不同的数据。
给一个字段添加主键约束，被称为单一主键。
#单一主键 #列表定义方式 drop table if exist t_user; create table t_user( id int(10) primary key, name varchar(32) ) #单一主键 #列表定义方式 drop table if exist t_user; create table t_user( id int(10), name varchar(32)， primary key（id） )给多个字段联合添加一个主键约束，被称为复合主键。
无论是单一主键还是复合主键，一张表主键约束只能有一个
主键根据性质分类：
自然主键
- 主键值若是一个自然数，这个自然数和当前表的业务没有任何关系，这种主键叫做自然主键 。
业务主键
- 主键值若和当前表中业务紧密相关的，那么这种主键值被称为业务主键，当业务数据发生改变的时候，主键值通常会受到影响，所以业务主键使用较少。大部分都是使用自然主键。
在mysql数据库管理系统中提供了一个自增的数字，专门用来自动生成主键值。主键值不需要用户维护，也不需要用户提供了，自动生成的。这个自增的数字默认从1开始，以1递增：1、 2、 3、 …….
drop table if exist t_uesr; create table t_user( id int(10) primary key auto_increment, name varchar(32) )
外键约束 foreign key 简称FK
外键涉及到的术语
外键约束
- 外键字段
外键值
外键约束、外键字段、外键值之间的关系？
某个字段添加外键约束之后，该字段称为外键字段，外键字段中的每一个数据都是外键值。
外键也分为：单一外键和复合外键（联合多个字段添加一个外键）
一张表中可以有多个外键字段。
分析场景：
请设计数据库用来存储学生和班级信息，给出两种解决方案：
学生信息和班级信息之间的关系，一个班级对应多个学生，这是典型的一对多的关系。
第一种设计方案：将学生信息和班级信息存储到一张表中。
学生信息表t_student
sno(pk) sname classno cname
1 jack 100 实验中学1班
2 allen 200 实验中学2班
3 lucy 200 实验中学2班
4 lisi 300 实验中学3班
以上设计缺点：数据冗余
第二种解决方案：将学生信息和班级信息分开两张表存储你，学生表 + 班级表
学生表t_student
sno(pk) sname classno（fk）
1 jack 100
2 allen 200
3 lucy 200
4 lisi 300
班级表t_class
cno(pk) cname
100 实验中学1班
200 实验中学2班
300 实验中学3班
结论：为了保证t_student表中的classno字段中的数据必须来自于t_class表中cno字段中的数据，有必要给t_student表中的classno字段添加外键约束，classno字段被称为外键字段，该字段中的100、 200、 300、被称为外键值。classno这里是一个单一外键字段
注：外键字段可以为null。
注：外键字段去引用一张表的某一个字段的时候，被引用的字段必须具有unique约束。
注：有了外键引用之后，表分为父表和子表，以上父表是：班级表，子表是：学生表。创建表先创建父表，在创建子表，删除数据的时候，先删除子表中的数据，后删除父表中的数据。插入数据的时候先插入父表中的数据，再插入子表中的数据
364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
