mysql学习整理

纯手打，转载请标注！

sql类型 

DDL：数据定义语言  包括 create alter drop truncate  不支持事务

DML：数据操作语言  包括insert delete update select （DQL） 支持事务

DQL：数据查询语言 包括select

TCL：事务控制语言  包括 commit rollback savepoint  rollback to

DCL：数据控制语言  分配用户权限

数据类型 五种

整数类型: int bigint(m)  显示长度 结合zerofill使用

浮点类型： double decimal(m,d) m总长度 d小数长度

字符串类型：char(m):长度不可变 执行效率高 最大255，varchar(m):长度可变，节省空间

日期类型

date：保存年月日

time：保存时分秒

datetime：默认值为null 最大9999-12-31

timestamp：默认值为当前时间 最大2038-01-19

各种sql语句

创建数据库 create database 数据库的名称；

删除数据库 drop database 数据库名；

查询所有数据库 show databases;

查看单个数据 show create database 数据库名；

创建指定字符集 create database 数据库名 character set gbk/utf8;

使用数据库 use 数据库名;

创建表 create table t1(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;

删除表 drop table t1;

查看所有表  show tables;

查看单个表  show create table t1;

查看表字段  desc t1;

 修改表名： rename table  t1 to t2;

 修改表属性:  alter table t1 engine=myisam/innodb charset=utf8/gbk;

添加表字段: alter table t1 add age int first/after xxx;

删除表字段 alter table t1 drop age;

修改表字段名和类型 alter table t1 change age abc int;

修改类型和位置  alter table t1 modify age int first/after xxx;

插入数据： insert into t1 values(值1,值2),(值1,值2),(值1,值2);

insert into t1 (字段1,字段2) values(值1,值2),(值1,值2),(值1,值2);

查询数据： select * from t1;

select 字段名，字段名 from t1 where 条件

修改数据： update t1 set age=20 where id=10;

删除数据： delete from t1 where id<10;

主键约束 primary key  create table t1（id int primary key，age int）；

主键+自增 auto_increment  create table t1 (id int primary key auto_increment,age int);

注释 comment  create table t1 (id int primary key auto_increment comment 'abcd',age int);

`和’区别  

`修饰表名和字段名可以省略

‘修饰字符串

事务：数据库中执行sql语句的最小工作单元

查看自动化提交状态 show variables like '%autocommit%'；

修改状态 set autocommit=0/1；

手动提交commit；

回滚rollback；

保存回滚点 savepoint s1；

回滚到指定回滚点 rollback to s1；

别名的三种写法

emp表中所有的姓名ename该为姓名

1、select ename as ‘姓名’ from emp；

2、select ename ‘姓名’ from emp；

3、select ename 姓名 from emp；

去重用distinct   select distinct deptno from emp;

添加查询条件用比较运算符 >,<,>=,<=,=,!=和<>

并列条件and和or

in的用法 select * from sal in （5000，1500，3000）；

between and 的用法  select * from emp where sal between 2000 and 4000;

like 用于模糊查询

_：代表单个位置字符

%：代表0个或多个未知字符

举例：

包括字符a  %a%

以a字开头  a%

以啊字结尾  %a

第二个字符是a  _a%

倒数第三个字符a %a__

第二个字符是a 最后一个字符是b  _a%b

select ename,sal from emp oder by sal desc/asc；查询emp中的ename并按照sal的降序/升序排序

分页查询 limit 

limit x，y  代表跳过的条数  y代表查询的条数（每页的条数）  limit 写在最后

例1：查询第一页的10条数据    limit 0,10

例2：查询第五页的8条数据    limit 32,8

例3：查询第三页每页撒每条数据  limit （3-1）*3，3

数值运算  + - * / 7%2等效（mod（7，2））

日期的格式化  格式：date_format(日期，格式)

select date_format(now(),'%Y年%m月%d日%H点%i分%s秒');

非标准格式时间转成标准格式：str_to_date(非标准格式的时间，格式)

%Y代表四位年  %y两位年

%m两位月  %c代表一位月

%d 日

%H 24小时  %h12小时

%i 分    %s秒

获取当前日期+时间

select now（）；

获取当前的日期

select curdate（）；

获取当前时间

select curtime（）；

从年月日时分秒中提取年月日和提取时分秒

select date （now（））；

select time （now（））；

从年月日分秒中提取年，月，日，是，分，秒

select extract （year from now（））；

select extract （month from now（））；

select extract （day from now（））；

select extract （hour from now（））；

select extract （minute from now（））；

select extract （second from now（））；

聚合函数

对多余数据进行统计查询

求品均值avg（sal）

例子:select avg（sal） from emp；

最大值max（字段名）

最小值min（字段名）

总和sum（字段名）

计数count（*）

字符串拼接concat（s1，s2）结果等于s1s2

获取字符串的长度char_length(str)

获取字符串在另一个字符中出现的位置 从1开始

格式一：instr（str，substr）；

格式二：locate（substr,str）;

转大写和转小写 select upper（‘abc’）m，lower（‘Nba’）；

去俩端空格  select trim（‘    a b  ssss       ’）；

截取字符串   select left（‘asdbja’，2）；从左边截

select right（‘asdasdasdasd’，2）；

从某个位置截取 select substring （'asdasd'，2，3）；//bcd

重复 select repeat （‘ab’，3）；//ababab

反转select reverse（‘abc’）；//cba

替换select replace （‘abcd’，‘c’，‘m’）；//abmd

分组查询

分组查询通常和聚合函数结合使用

查询条件中每个xxx就以xxx为分组的条件

格式：每个部门的平均工资

select deptno，avg（sal） from emp group by deptno；

查询emp表中的deptno和avg（sal）字段以deptno为分组

having用法：在where后面只能写普通字段的条件。在having后面写聚合函数的条件

例子：

1、查询每个部门的平均工资，只查询平均工资大于2000的。

select 部门编号，平均工资 a from 员工表 group by 部门编号 having a>2000;

2、查询所有分类对应的平均单价，要求平均单价低于100

select category_id,avg(price) a from t_item group by category_id having a<100;

子查询

在查询语句嵌套另一个查询语句

关联查询

同时查询多张表的数据成为关联查询

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

等值连接和内链接

等值连接：select * from A，B where A.x=B.x and A.y=xxx;

内链接·：select * from A [inner] join B on A.x=B.x where A.y=xxx;

内连接跟等值连接是一样的高高兴兴的把两个表的数据一起输出。

外联接 格式：select * from A left/right join B on A.x=B.x where A.y=xxx;

左外连接:以join左侧表为主表，查询其所有数据和右侧表的交集数据

右外连接：以join右侧表为主表，查询其所有数据和左侧表的交集数据

表设计

一、关联关系

一对一关系

什么是一对一： 有两张表A和B，A表中有一条数据对应B表中的一条数据称为一对一关系

应用场景：用户表和用户信息扩展表，商品表和商品信息扩展表

如何建立关系：在从表中添加一个外键字段 指向主表的主键

1.创建用户和用户信息扩展表

create table user(id int primary key auto_increment,username varchar(10),password varchar(10));

create table userinfo(uid int,nick varchar(10),qq varchar(10),phone varchar(11));

2.在两个表中各插入三条数据

insert into user values(null,'wukong','admin'),(null,'bajie','admin'),(null,'wujing','admin');

insert into userinfo values(1,'悟空','123456','13838383838'),(2,'八戒','332211','13533555533'),(3,'悟净','334422','13212212122');

3. 写SQL查询每个用户的所有信息

select *from user u join userinfo ui on u.id=ui.uid;

4. 查询名字叫悟空的用户名和密码

select u.username,u.password from user u join userinfo ui on u.id=ui.uid where ui.nick='悟空';

5. 查询用户名bajie的昵称

select ui.nick from user u join userinfo ui on u.id=ui.uid where u.username='bajie';

二、一对多

什么是一对多：AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据，称为一对多

应用场景： 员工表和部门表 ， 分类表和商品表

如何建立关系：一对多两张表，在多的表里添加外键指向另外一个表主键

1. 创建t_emp表 id,name,deptid       t_dept表 id，name，loc

create table t_emp(id int primary key auto_increment,name varchar(10),deptid int);

create table t_dept(id int primary key auto_increment,name varchar(10),loc varchar(10));

2.插入数据

部门表数据： 神仙部 地址：天上  妖怪部 地址盘丝洞

员工表数据：孙悟空，猪八戒，蜘蛛精，白骨精

insert into t_dept values (null,'神仙部','天上'),(null,'妖怪部','盘丝洞');

insert into t_emp values (null,'悟空',1),(null,'八戒',1),(null,'蜘蛛精',2),(null,'白骨精',2);

3. 查询每个部门对应的员工信息

select d.*,e.* from t_emp e join t_dept d on e.deptid=d.id;

4. 查询在盘丝洞工作的所有员工姓名

select e.name from t_emp e join t_dept d on e.deptid=d.id where d.loc='盘丝洞';

5. 查询孙悟空的部门名称和工作地点

select d.name,d.loc from t_emp e join t_dept d on e.deptid=d.id where e.name='悟空';

多对多

- 什么是多对多：AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的多条数据，称为多对多

- 应用场景： 老师表和学生表

- 如何建立关系：需要创建单独的关系表表中两个字段分别指向两个表的主键

自关联

- 什么是自关联： 在表中有一个外键字段指向当前表的主键值，称为自关联

- 应用场景：需要保存层级关系，但又不确定有多少层的时候使用自关联，如：员工的上级领导，部门的上级部门，分类的上级分类

连接方式和关联关系

- 连接方式： 包括 等值连接，内连接和外链接，是关联查询的查询方式

- 关联关系： 包括 一对一，一对多，多对多，是指设计表的时候两个表之间存在的逻辑关系

- 当查询存在关联关系的表时，使用某一种连接方式进行查询。

视图 view

- 什么是视图：数据库中存在多种对象，表和视图都是数据库中的对象，视图本质上就是一段sql查询语句，可以理解成是一张虚拟的表，视图没有自己独立的数据，数据会随着原表的改变而改变。

- 为什么使用视图：因为有些数据的查询需要书写大量的sql语句，每次书写比较麻烦，使用视图可以起到SQL重用的作用，可以隐藏敏感信息。

- 格式： create view 视图名 as (子查询);

例：create view v_emp_10 as (select * from emp where deptno=10);

select * from v_emp_10;

视图的分类

1. 简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询的视图称为简单视图，可对视图中的数据进行增删改操作。

2. 复杂视图：和简单视图相反，只能对复杂视图中的数据进行查询操作

对简单视图中的数据进行增删改操作

1. 在视图中插入数据

create view v_emp_10 as (select * from emp where deptno=10);

insert into v_emp_10 (empno,ename,deptno,sal) values (1001,'猪八戒',10,8000);

select * from v_emp_10;

insert into v_emp_10 (empno,ename,deptno,sal) values (1002,'唐僧',20,10000);

select * from v_emp_10;

- 数据污染：往视图中插入一条视图中不可见，但是在原表中存在的数据，称为数据污染，如果需要避免数据污染的出现，可以使用 with check option关键字

例：

create view v_emp_30 as(select * from emp where deptno=30) with check option;

测试：

insert into v_emp_30 (empno,ename,deptno,sal) values (1003,'蝙蝠侠',30,888);（成功）

insert into v_emp_30 (empno,ename,deptno,sal) values (1004,'钢铁侠',20,666); (报错失败)

修改视图

- 格式：

create or replace view 视图名 as 子查询;

create or replace view v_emp_10 as (select * from emp where deptno=10 and sal<2000);

删除视图

- 格式：  drop view v_emp_10;

show tables;

视图总结：

1. 数据库中的对象，本质是一段sql语句，虚拟表，数据会随着原表改变而改变

2. 作用：重用sql，隐藏敏感信息

3. 分类：简单（不包含 去重 函数 分组 关联查询）可以进行增删改 和复杂视图(包含 去重 函数 分组 关联查询) 只能查询数据

4. 创建视图： create view 视图名 as （子查询）;

5. 修改视图: create or replace view 视图名 as （子查询）;

6. 删除视图： drop view 视图名;

什么是约束：给表的字段添加限制

非空约束 not null 添加非空约束后字段不能为null

添加唯一（unique）约束后 字段值不能重复

create table t_unique(id int,age int unique);

测试：

insert into t_unique values(1,18);（成功）

insert into t_unique values(2,18);(报错失败)

主键约束 primary key

添加了主键约束 主键值不能为null并且不能重复

默认约束 default

- 给字段添加默认约束，当不对此字段赋值时 默认值生效

create table t_def(id int,age int default 10);

insert into t_def (id) values (1);

- 可以通过 show create table 表名;查看约束信息

- 删除外键约束

alter table emp drop foreign key fk_dept;

- myisam引擎不支持外键约束，要求两张表都是innodb引擎才能使用外键约束

- 在java开发中，除非特定情况，一般不使用外键约束，因为会给测试带来不便，但是通常会通过java代码所写的逻辑对插入的数据进行控制。

索引的分类 （了解）

1. 聚集索引(聚簇索引)：一个表只有一个聚集索引，通过主键创建的索引称为聚集索引，数据库会自动为添加了主键约束的表增加聚集索引，在聚集索引中保存着查询依据和数据

2. 非聚集索引：通过非主键字段创建的索引称为非聚集索引，在非聚集索引中保存着查询依据和地址（没有数据）

如何创建索引

- 格式： create index 索引名 on 表名(字段名(长度));

create index index_item2_title on item2(title);

- 验证：

select * from item2 where title='100'; 耗时0.04秒

如何查看索引

show index from item2;

复合索引

- 通过多个字段创建的索引称为复合索引

- 应用场景：频繁使用多个字段作为查询条件的时候，可以为这几个字段创建复合索引

- 创建格式：

create index index_item2_title on item2(title,price);

事务的ACID特性   **面试常考**

- Atomicity 原子性：最小 不可拆分，保证全部成功或全部失败

- Consistency 一致性： 从一个一致状态到另一个一致状态

- Isolation 隔离性：多个事务之间互不影响

- Durability 持久性：事务提交后数据持久保存到数据库中