MySQL

最新推荐文章于 2024-02-20 14:55:48 发布

转载最新推荐文章于 2024-02-20 14:55:48 发布 · 473 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dxllp/p/10726057.html

数据库相关的SQL

查看所有数据库
```
show databases;
```
创建数据库 -格式：create database 数据库名称;
```
create database db1;
```
查看数据库详情 -格式： show create database 数据库名;
```
show create database db1;
```

创建数据库指定字符集 utf8或gbk

create database db2 character set utf8;

create database db2 character set gbk;

删除数据库
```
drop database db2;
```
使用数据库

use db1;

和表相关的SQL

什么是表：关系型数据库中保存数据的单元，类似于Excel中的表，创建表时需要指定字段信息
创建表 -格式： create table 表名 (字段1名字段1类型,字段2名字段2类型);
```
create table person(name varchar(10),age int);
```

和表相关的SQL

查询所有表

    show tables;

查看单个表属性

show create table person;

表的引擎

Myisam：只支持数据基础的增删改查，不支持高级操作，如：事务、外键等
InnoDB：支持高级操作，默认为InnoDB。

创建表并且指定引擎和字符集

create table t1(id int,name varchar(10)) engine=myisam charset=gbk;
-查询：
show create table t1;

查看表字段信息

desc person;

对创建好的表进行修改

修改表名

rename table 原名 to 新名；

rename table student to t_stu;

create table hero(name varchar(10));

修改表属性引擎和字符集

alter table hero engine=myisam charset=gbk;

添加表字段

最后位置添加
```
alter table hero add age int;
```
最前面添加
```
alter table hero add money int first;
```

在某个字段的后面添加

alter table hero add gender varchar(5) after name;

删除表字段

  alter table hero drop money;

修改表字段名和类型

    alter table hero change 原字段名 新字段名 类型;
    alter table hero change name heroname varchar(10);

修改表字段类型和位置

    alter table hero modify age int first;
    alter table hero modify age int after xxx;

删除表

    drop table hero;

数据相关的SQL

插入数据

创建学生表

create table student(id int,name varchar(10),chinese int,math int,english int);

插入数据全表插入：每个字段都赋值顺序要和表字段一致

insert into student values(1,'zhangsan',88,38,98);

-指定字段插入

insert into student (id,name) values (2,'张飞');

-练习：插入刘备 id为3 语数外成绩 78 22 18 insert into student values(3,'刘备',78,22,18); 插入关羽 id为4 语文90 insert into student (id,name,chinese) values (4,'关羽',90); 插入孙悟空 id为5 英语18 insert into student (id,name,english) values (5,'孙悟空',18);

批量插入

    insert into student values(6,'唐僧',56,57,58),(7,'八戒',88,89,99);

    insert into student (id,name) values
    (8,'吕布'),(9,'貂蝉'),(10,'孙尚香');

查询

查询全部数据的全部字段信息
```
select * from student;
```
查询指定字段
```
select id,name from student;
```
条件查询
```
select * from student where id<5;
```

修改数据

update student set math=100; 

update student set english=30 where id=7;

删除数据

delete from student where id=7;

delete from student;

编码问题

sql语句在客户端中写完后需要通过网络发送给数据库服务器，这时需要字符的编解码，数据库保存数据时也需要字符的编解码
学校学生电脑保存中文数据只需要确认数据库和表是否统一为utf8
个人电脑如果是windows系统需要保证数据库和表为utf8 之外还需要在命令行中执行 set names gbk; 通知数据库客户端传过去的内容需要使用gbk解码。

主键约束

什么是主键：用来表示数据唯一性的字段称为主键
约束：约束就是对表字段的限制条件

主键约束：限制主键字段的数据唯一并且非空

create table t1(id int primary key,name varchar(10));

insert into t1 values(1,'Tom');//成功
insert into t1 values(1,'Jerry');//失败 id不能重复
insert into t1 values(null,'Jack');//失败 id不能为null

自增： auto_increment

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

insert into t2 values(null,'Lucy');
insert into t2 values(null,'Hanmeimei');
insert into t2 values(10,'LiLei');
insert into t2 values(null,'Lily');

自增会在曾经出现的最大值的基础上+1
自增数值不会因为删除数据减少
使用delete删除整个表的数据时自增数值不会清零

注释 comment

创建表声明字段的时候可以通过添加comment给字段添加注释

create table t3(id int primary key auto_increment comment '这是主键字段',age int comment '这是年龄字段');
show create table t3;

`和' 的区别

` : 用来修饰表名和字段名的可以省略
```
create table `t4`(`id` int,`name` varchar(10));
```
' : 用来修饰字符串

数据冗余

如果表设计不够合理随着数据量增多出现了大量的重复数据称为数据冗余，通过拆分表的形式解决冗余问题

练习：创建商品表item 字段：id,商品标题title,单价 price,库存 num, 分类cid 创建分类表category 字段：id,名称name

create table item(id int primary key auto_increment,title varchar(10),price int,num int,cid int);
create table category(id int primary key auto_increment,name varchar(10));

- 插入以下数据：电视机分类下的康佳电视价格3520库存25 夏普电视价格18888 库存100 手机分类下的苹果9价格800库存100，华为p20 价格6888 库存4

insert into category values(null,'电视机'),(null,'手机');
insert into item values(null,'康佳电视',3520,25,1),(null,'夏普电视',18888,100,1),(null,'苹果9',800,100,2),(null,'华为p20',6888,4,2);

事务

create table person(id int primary key auto_increment,name varchar(10),money int);

insert into person values(null,'超人',50);
insert into person values(null,'钢铁侠',30000);

update person set money=5050 where id=1;
update person set money=25000 where id=2;

什么是事务：数据库中执行sql语句的工作单元，此工作单元不可拆分，能够保证全部成功或全部失败
查看数据库自动提交的状态
```
show variables like '%autocommit%';
```
设置自动提交的开关 0：关闭 1：开启
```
set autocommit=0/1;
```

- 验证过程：

create table person(id int primary key auto_increment,name varchar(10),money int);
insert into person values(null,'超人',50);
insert into person values(null,'钢铁侠',30000);

update person set money=5050 where id=1;
先在当前窗口执行select * from person 查看是否改变，结果是修改了因为查看的是内存中的数据
此时打开另外一个终端，先使用db2 然后select * from person 查询是否改变，结果是没有改变因为此时查看的是数据库文件中的数据，没提交就不会改变
回到原窗口再执行转账的另一部分
```
update person set money=25000 where id=2;
```
此时原窗口查询数据因为查询的是内存中的数据所以改变，但是另外一个窗口数据并没变化
在原窗口中执行commit，此时两个窗口的数据都发生改变
事务回滚： rollback, 此指令会将数据库中的数据回滚到上次提交的点步骤：1. 在关闭了自动提交的窗口修改超人的钱修改后查询由于查询到的是内存中的数据所以数据是修改过的，此时执行rollback，则数据会回滚到修改前的值
保存回滚点：
```
savepoint s1;
```
回滚到某个回滚点：
```
rollback to s1;
```
事务总结：执行sql语句的工作单元，保证全部成功或全部失败
查看自动提交状态 show variables like '%autocommit%';
设置自动提交状态 set autocommit=0/1;
手动提交 commit;
回滚 rollback;
保存回滚点 savepoint s1;
回滚到某个点 rollback to s1;

SQL分类

DDL：Data Definition Language（数据定义语言）

包括：create,drop,alter,truncate
不支持事务

DML：Data Manipulation Language（数据操作语言）

包括：insert update delete select(DQL)
支持事务

DQL：Data Query Language（数据查询语言）

只包括select

TCL：Transaction Control Language（事务控制语言）

包括：commit rollback savepoint rollback to

DCL：Data Control Language（数据控制语言）

分配用户权限相关的sql

truncate

删除表并且创建一个相同的空表，此时表中的自增数值清零
格式： truncate table 表名;
truncate：删除表创建新表 drop：删除表 delete：只删除数据
效率：drop>truncate>delete

数据库的数据类型

整型

- 常用类型：int(m) bigint(m)，m代表显示长度

create table t_int(num int(10) zerofill);
insert into t_int values(123);
select * from t_int;

浮点型

常用类型： double(m,d) m代表总长度 d代表小数长度 76.232 m=5 d=3, decimal(m,d):超高精度小数，当需要涉及超高精度运算的时候使用此类型

字符串

char：固定长度 char(10) 'abc' 占10 ，执行效率高，最大255
varchar:长度可变 varchar(10) 'abc' 占3 ，节省空间，最大65535 超高255建议使用text
text:长度可变最大值65535 用于保存大文本

日期

date:只保存年月日
time:只保存时分秒
datetime:保存年月日时分秒，默认值为null，最大值 9999-12-31

timestamp:保存年月日时分秒保存距离1970年1月1日八点的毫秒数，默认值为当前时间，最大值2038年1月19号

create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
insert into t_date values('2018-04-23',null,null,null);
insert into t_date values
(null,'12:38:45','2018-05-12 12:38:33',null);

导入数据

在db6数据库下执行source命令 - 学生机（linux）

source /home/soft01/桌面/tables.sql;

个人电脑（windows）
```
source d:/tables.sql;

show tables;
```

is null 和 is not null

查询没有奖金的员工信息
```
select * from emp where comm is null;
```

查询有奖金的员工信息

select * from emp where comm is not null;

别名

select ename from emp;
select ename as '名字' from emp;
select ename '名字' from emp;
select ename 名字 from emp;

去重 distinct

去掉重复的数据 select distinct job from emp;

比较运算符 > < >= <= = !=和<>

查询工资小于等于1600的员工姓名和工资
```
select ename,sal from emp where sal<=1600;
```
查询部门编号是20的员工姓名，职位job和部门编号
```
select ename,job,deptno from emp where deptno=20;
```

查询职位是manager的员工姓名和职位

select ename,job from emp where job='manager';

查询部门不是10号部门的所有员工姓名和部门编号，两种方式实现

select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;

查询商品表t_item 单价等于23的商品信息
```
select * from t_item where price=23;
```
查询商品表中价格不等于8443的商品信息
```
select * from t_item where price!=8443;
```

and 和 or

and相当于java的&&
or相当于java中的||
1. 查询工资小于2000并且是10号部门的员工信息
  
  select * from emp where sal<2000 and deptno=10;
2. 查询有奖金comm 或者工资大于3000的员工姓名，工资，奖金
  
  select ename,sal,comm from emp where comm is not null or sal>3000;
3. 查询t_item表中单价price大于500并且库存num大于100的商品信息
  
  select * from t_item where price>500 and num>100;

in

当查询某个字段的值等于多个值的时候使用in关键字

查询工资为800，3000，5000，1600的所有员工信息

select * from emp where sal=800 or sal=3000 or sal=5000 or sal=1600;

select * from emp where sal in (800,3000,5000,1600);

between x and y 包含x和y

查询某个字段的值在两个数值之间的时候使用
查询员工工资在2000至4000之间的员工信息
```
select * from emp where sal between 2000 and 4000;
```

练习题：

查询员工表中上级领导为7698的员工姓名，工资，领导编号select ename,sal,mgr from emp where mgr=7698;
查询没有奖金（包括null和0）并且工资低于3000的员工信息

select * from emp where (comm is null or comm=0) and sal<3000;
查询领导是7698，7902，7566 的员工信息

select * from emp where mgr in(7698,7902,7566);
查询单价小于100 或者分类id为163的商品信息

select * from t_item where price<100 or category_id=163;
查询20号部门中工资小于3000的员工信息

select * from emp where deptno=20 and sal<3000;
查询有图片image的的商品信息

select * from t_item where image is not null;
查询部门表中地点是new york的部门名称

select dname from dept where loc='new york';

like

用来进行模糊查找
_：代表单个未知字符
%：代表0或多个未知字符
举例：
以a开头的内容 a%
以a结尾的内容 %a
两个字符第一个是a a_
第二个字符是a _a%
倒数第三个字符是a %a__
包含a %a%
like相关案例：
查询所有记事本的价格（title中包含记事本）

select title,price from t_item where title like '%记事本%';
查询单价低于100的记事本信息

select * from t_item where price<100 and title like '%记事本%';
查询单价在50到200之间的得力商品（title包含得力）

select * from t_item where price between 50 and 200 and title like '%得力%';
查询有图片的得力商品

select * from t_item where image is not null and title like '%得力%';
查询有赠品的商品信息（sell_point字段包含赠）

select * from t_item where sell_point like '%赠%';
查询不包含得力的商品标题

select title from t_item where title not like '%得力%';
查询价格介于50到200之外的记事本信息

select * from t_item where price not between 50 and 200 and title like '%记事本%';

排序 order by

order by写在where的后面如果没有where 写在表名的后面
by后面写排序的字段名称
默认是升序，也可指定升序降序： asc 升序 desc降序
案例：
查询员工姓名和工资按照工资降序排序

select ename,sal from emp order by sal desc;
查询所有的dell商品按照价格的降序排序

select * from t_item where title like '%dell%' order by price desc;
查询所有员工的姓名，工资，部门编号按照部门编号升序排序

select ename,sal,deptno from emp order by deptno;
查询所有员工的姓名，工资，部门编号按照部门编号升序排序如果部门一致则按照工资降序排序

select ename,sal,deptno from emp order by deptno,sal desc;

分页查询 limit

limit 跳过条数,每页条数
limit 通常写在sql语句的最后面
查询所有商品按照单价升序排序查询第二页每页7条数据

select * from t_item order by price limit 7,7;
查询工资排名前三名的三位员工的信息

select * from emp order by sal desc limit 0,3;
查询拿最低工资的员工姓名和编号

select ename,empno from emp order by sal limit 0,1;
查询商品表中的记事本，第三页每页两条数据；

select * from t_item where title like '%记事本%' limit 4,2;

数值计算 + - * / % mod(7,2)等效 7%2

查询员工姓名，工资，年终奖（年终奖=月薪*5）

select ename,sal,sal*5 年终奖 from emp;
查询商品单价，库存和总金额（单价*库存）

select price,num,price*num 总金额 from t_item;
修改所有员工的工资每人涨薪20块钱

update emp set sal=sal+20;

日期相关函数

SQL的helloworld select 'helloworld';
获得当前时间年月日时分秒 now()
select now();
获得当前年月日 current

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

日期格式化 date_format(日期,格式); 四位年 %Y 两位年 %y 两位月 %m 一位月 %c 日 %d 24小时 %H 12小时 %h 分钟 %i 秒 %s
把now() 格式改成年月日时分秒

select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');
把非标准格式转回标准格式 str_to_date(非标准格式的字符串,格式);
把 14.08.2018 09:10:20 转回标准格式

select str_to_date('14.08.2018 09:10:20','%d.%m.%Y %H:%i:%s');

ifnull函数

age=ifnull(x,y) 如果x的值为null则age=y,如果不为null则age=x;
修改员工表的奖金，如果奖金值为null修改成0不为null则不变

update emp set comm=ifnull(comm,0);

聚合函数

对多行数据进行统计：求和求平均值最大值最小值统计数量
求和 sum(字段名)
查询10号部门工资总和

select sum(sal) from emp where deptno=10;
查询单价在100以内的商品库存总量

select sum(num) from t_item where price<100;
平均值 avg(字段名)
查询20号部门的平均工资

select avg(sal) from emp where deptno=20;
查询记事本的平均价格

select avg(price) from t_item where title like '%记事本%';
最大值 max(字段名) 最小值 min(最小值)
查询所有员工的最大工资和最小工资

select max(sal),min(sal) from emp;
统计数量 count(字段名) 一般使用count(*)
统计工资在2000以下的员工有多少人

select count(*) from emp where sal<2000;

字符串相关函数

字符串拼接 concat(s1,s2) s1s2

select concat('abc','mm');
查询员工姓名和工资在工资后面添加元字

select ename,concat(sal,'元') from emp;
获取字符串的长度 char_length('abc') 3

select char_length('abc');
查询员工姓名和姓名的长度

select ename,char_length(ename) from emp;
获取一个字符串在另一个字符串中出现的位置
格式： instr('abcdefg','d') 从1开始

select instr('abcdefg','d');
格式： locate('d','abcdefg')

select locate('d','abcdefg');
插入字符串
格式: insert(str,start,length,newstr);

select insert('abcdefg',3,2,'m');
转大小写
格式： upper(str) lower(str)

select upper('Nba'),lower('CBA');
从左边截取和从右边截取

格式： left(str,count) right(str,count)

`select left('abcdefg',2);`
`select right('abcdefg',2);`

截取字符串

格式： substring(str,start) substring(str,start,count)

select substring('abcdefg',2);
select substring('abcdefg',2,3);

去字符串两端的空白
格式： trim(str);

select trim(' a b ');
重复
格式： repeat(str,count)

select repeat('ab',2);
替换
格式： replace(str,old,new)

select replace('abcdefg','c','m');
反转
格式： reverse(str)

select reverse('abc');

数学相关函数

向下取整 floor()

select floor(3.14);
四舍五入 round()

select round(3.8); - 四舍五入指定小数位数

`select round(3.2358,2);`

非舍五入 truncate()

select truncate(3.2358,2);

随机数 rand() 0-1 不包含1 select rand();

0-8的随机数

select floor(rand()*9);

3-5的随机数；

0-2   +3；

select floor(rand()*3)+3;

分组查询

查询每个部门的最高工资

select deptno,max(sal) from emp group by deptno;
查询每个职位的平均工资

select job,avg(sal) from emp group by job;
查询每个领导手下的人数

select mgr,count(*) from emp where mgr is not null group by mgr;

-group by SQL中的位置

select * from 表名 where ... group by ... order by ... limit ...;
查询每个部门的每个领导下的人数

select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;

查询每个部门的平均工资，要求查询平均工资大于2000

-以下是错写法：where 后面不能写聚合函数

select deptno,avg(sal) a from emp where a>2000 group by deptno;

-正确写法：
```
select deptno,avg(sal) a from emp group by deptno having a>2000;
```
-having关键字要和group by结合使用，写在group by的后面，用于在SQL语句中添加聚合函数的条件

-where后面写普通字条件，having后面写聚合函数的条件

-1). 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，部门工资，最后根据平局工资降序排序
```
  select deptno,count(*) ,avg(sal) a from emp group by deptno having a>2000 order by avg(sal) desc;
```
-2). 查询商品中每个分类的平均单价，要求显示平均单价低于100的信息
```
  select category_id, avg(price) a from t_item  group by category_id having a<100;
```
-3). 查询每个分类对应的库存总量，显示高于19999的库存总量
```
   select category_id,sum(num) from t_item group by category_id having sum(num)>199999;
```
-4). 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门信息，按照平均工资升序排序
```
 select deptno,sum(sal),avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a;
```
-5). 查询emp表中不是以s开头，每个职位的人数，工资总和，最高工资，过滤掉平均工资高于3000的职位，根据人数升序排序，如果一致根据工资总和降序排序
```
select job,count(*) c,sum(sal) s,max(sal) from emp where ename not like 's%' group by job having avg(sal)<=3000 order by c,s desc;
```
-6). (提高题) 查询每年入职的人数
```
    select extract(year from hiredate) year, count(*) from emp group by year;
```

子查询

查询emp表中最高工资的员工信息

select max(sal) from emp;

select * from emp where sal=5020;

-把上面两条合成一条
```
select * from emp where sal=(select max(sal) from emp);
```

查询工资高于20号部门平均工资的所有员工信息

select * from emp where sal>(select avg(sal） from emp where deptno=20);

查询和jones做相同工作的员工信息

select * from emp where job=(select job from emp where ename='jones');

查询最悲惨员工（工资最低）的同事们的信息 ???

 select min(sal) from emp;

 select deptno from emp where sal=(select min(sal) from emp);

 select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp));

查询最后入职的员工信息

select * from emp where hiredate=(select max(hiredate) from emp);

查询King的部门名称是什么（需要用到dept）

select dname from dept where deptno=(select deptno from emp where ename='King');

查询名字中不包含a 并且工资高于10号部门平均工资的员工信息

select * from emp where sal>(select avg(sal) from emp where deptno=10) and ename not like '%a%';

查询有员工的部门详情（需要用到部门）

select distinct deptno from emp;

select * from dept where deptno in(select distinct deptno from emp);

扩展题（难度最高）：查询平均工资最高的部门信息

得到最高的平均工资

select avg(sal) a from emp group by deptno order by a desc limit 0,1;

通过最高的平均工资得到部门的编号

select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);

通过部门编号得到部门信息

select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));

-子查询可以写在的位置

1.写在where或者having后面当查询条件的值

2.写在创表的时候

     create table t_emp as(select * from emp where sal<2000);

3.可以写在form后面把查询结果当成一个虚拟的表 必须取别名
```
select ename,sal from (select * from emp sal<2000) e;
```

关联查询

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

查询每个员工的姓名和所在部门名称

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

查询每个员工的姓名工资部门名称部门地点

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

查询纽约工作的的所有员工信息

  select e* from emp e,dept d where e.deptno=d.deptno and d.loc='new york';

笛卡尔积

如果关联查询不写关联关系，则会得到两张表数据的乘积，这个乘积称为笛卡尔积
笛卡尔积是一个错误的执行结果，在工作中切记不要出现，数据量如果太大会导致内存溢出

等值连接和内连接

等值连接

  select a from A,B where A.x=B.x and A.y=xxx;

内连接：主要使用内连接

select * from A join B on A.x=B.x and A.y=xxx;

1.查询在纽约工作的所有员工信息

select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='new york';

外连接

左外连接：以join左边的表为主表查询所有数据，右边的表只显示有关系的数据

右外连接：以join右边的表为主表查询所有数据，左边的表只显示有关系的数据

 insert into emp (empno,ename,sal) values(10010,'赛亚人',500);

 select * from emp e left join dept d on e.deptno=d.deptno;

关联查询总结：

等值连接内连接外连接都是关联查询的查询方式，使用哪一种取决于具体业务需求
1. 如果查询两张表的交集数据使用等直连接或者内连接（推荐）；
2. 如果查询一张表全部数据另外一张是交集数据则使用外连接，左外或者右外都可以

表设计之关联关系

一对一

什么是一对一：AB两张表，A表中的一条数据对应B表中的一条数据，同时B表的一条数据对应A表的一条数据，此时两张表的关系称为一对一关系。
应用场景：商品表和商品详情表，用户表和用户信息扩展表
主键是表示数据唯一性的外键是用来建立关系的
如何让两张表建立关系？在从表中添加一个外键指向主表的主键
练习：创建user表和userinfo表
1. user: id int,username varchar(10), password varchar(10)
2. userinfo:nick varchar(10),qq varchar(10), phone varchar(15),uid int
保存以下数据：
1. libai admin 李白 66668888 1345678912
2. liubei 123456 刘备 33334444 12345678901

1.查询李白的用户名和密码

select u.username,u.password 
from user u join userinfo ui
ON u.id=ui.uid
where ui.nick='李白';

2.查询每个用户的用户名和昵称

select u.username,ui.nick
from user u join userinfo ui
on u.id=ui.uid;

3.查询 liubei的所有数据 select * from user u join userinfo ui on u.id=ui.uid where u.username='liubei';

一对多

什么是一对多：AB两张表 A表中的一条数据对应B表中的多条，同时B表中的一条数据对应A表的一条，此时两张表的关系为一对多
应用场景：部门表和员工表，分类表和商品表，用户表和收货地址表
如何建立关系：在两张表中多的表中添加外键指向另外一张表的主键
练习：员工表和部门表 emp dept 创建db5 utf8 并使用

保存以下数据：保存盘丝洞的妖怪部的白骨精年龄28 工资3000 蜘蛛精年龄32 工资2000 美国的英雄部门钢铁侠年龄45 工资8888 美国队长年龄252 工资6000 日本的海贼部门路飞年龄18 工资100 娜美年龄20 工资500

create table emp(id int primary key auto_increment,name varchar(10),age int,sal int,deptno int);

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


insert into dept values(null,'妖怪部','盘丝洞'),(null,'英雄部','美国'),(null,'海贼部','日本');
insert into emp values(null,'白骨精',28,3000,1),(null,'蜘蛛精',32,2000,1),(null,'钢铁侠',45,8888,2),(null,'美国队长',252,6000,2),(null,'路飞,100,3),(null,'娜美',20,500，3);

1.查询每个员工姓名工资部门名称

select e.name,e.sal,d.name
from emp e join dept d
on e.deptno=d.id;

2.查询路飞的工资和工作地点

select e.name,e.sal,d.name
from emp e join dept d
on e.deptno=d.id
where e.name='路飞';

3.查询英雄部门的所有员工信息

select e.*
from emp e join dept d
on e.deptno=d.id
where d.name='英雄部';

多对多

什么是多对多：AB两张表 A表中的一条数据对应B表中的多条，同时B表中的一条数据对应A表的多条，此时两张表的关系称为多对多
场景：老师表和学生表
如何建立关系：创建中间关系表在中间关系表中添加两个外键指向两个表的主键

练习：

创建两张主表 teacher(id,name) 和 student(id,name)

create table teacher(id int primary key auto_increment,name varchar(10));
create table student(id int primary key auto_increment,name varchar(10));

创建一个关系表 t_s(tid,sid)
```
create table t_s(tid int,sid int);
```

插入以下数据

传奇老师：刘德华张学友

苍老师：刘德华张学友小明小红

insert into teacher values(null,'传奇老师'),(null,'苍老师');
insert into student values(null,'刘德华'),(null,'张学友'),(null,'小明'),(null,'小红');
insert into t_s values(1,1)(1,2),(2,1),(2,2),(2,3),(2,4);

查询每个学生对应老师的姓名

select s.name,t.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on t.id=ts.tid;

查询刘德华的老师的姓名

select t.name
from student s join t_s ts
on s.id=ts.tid
join teacher t
on t.id=ts.tid
where s.name='刘德华';

查询苍老师的学生信息

select s.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on t.id=ts.tid
where t.name='苍老师';

自关联

什么是自关联：当前表的外键指向自己表的主键这种称为自关联
应用场景：用于保存有层级关系，并且不确定有多少层的数据，如：员工和上级领导，部门和上级部门，分类和上级分类。
可以保存一对一或者一对多的数据
查询数据时把一张表当成两张表查询

连接方式和关联关系的区别

连接方式：包括等值连接内连接外连接，是指关联查询的查询方式
关联关系：指表设计时两张表之间存在的逻辑关系包括一对一，一对多和多对多

表设计案例：权限管理

创建三张主表：用户表user(id,name）角色表role（id，name) 权限表module(id,name)

create table user(id int primary key auto_increment,name varchar(10));
create table role(id int primary key auto_increment,name varchar(10));
create table module(id int primary key auto_increment,name varchar(10));

创建两张关系表：用户-角色关系表 u_r:uid,rid；角色-权限关系表r_m:rid,mid
```
create table u_r(uid int,rid int);
create table r_m(rid int ,mid int);
```

用户表插入：刘德华，张学友，凤姐

insert into user (name) values('刘德华'),('张学友'),('凤姐');

权限表插入：男浏览男评论男发帖男删帖，女浏览女评论女发帖女删帖

insert into module (name) values('男浏览'),('男评论'),('男发帖'),('男删帖'),('女浏览'),('女评论'),('女发帖'),('女删帖')；

角色表插入：男会员男管理女游客女会员

insert into role (name) values ('男会员'),('男管理'),('女游客'),('女会员');

角色和权限保存以下关系：
1. 男会员对用权限：男浏览，男评论，男发帖；
2. 男管理员对应权限: 男浏览男评论男发帖男删帖;
3. 女游客对应权限：女浏览；
4. 女会员对应权限：女浏览女评论女发帖；
```
insert into r_m values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(2,4),(3,5),(4,5),(4,6),(4,7);
```
用户和角色保存以下关系：
1. 刘德华：男会员；
2. 张学友：男管理；
3. 凤姐：女会员和男会员；
```
insert into u_r values(1,1),(2,2),(3,4),(3,1);
```

查询刘德华所拥有的权限名称

1.子查询

得到刘德华的id

select id from user where name='刘德华';

通过用户id得到对应的角色id

select rid from u_r where uid=( select id from user where name='刘德华');

通过角色id找到对应的权限id

select mid 
from r_m where rid 
in( select rid from u_r 
where uid=( select id from user where name='刘德华'));

通过权限id得到权限的名字

select name 
from module 
where id in(select mid from r_m where rid 
in( select rid from u_r 
where uid=( select id from user where name='刘德华')));

2.内连接

select m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on rm.rid=ur.rid
join module m
on rm.mid=m.id
where u.name='刘德华';

查询拥有男浏览权限的用户有哪些

select u.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on rm.rid=ur.rid
join module m
on rm.mid=m.id
 where m.name='男浏览';

查询每个用户拥有的权限名称

select u.name,m.name
from user u join u_r ur 
on u.id=ur.uid
join r_m rm 
on rm.rid=ur.rid
join module m
on rm.mid=m.id;

面试题

create database db6;
use db6;

创建交易流水表：trade(id,time,money,tpye,pid)

create table trade(id int primary key auto_increment,time date,
money int,type varchar(10),pid int);

创建人物表：person(id,name,gender,rel)

create table person(id int primary key auto_increment,name varchar(10),
gender varchar(5),rel varchar(5));

3.插入数据：

刘德华男亲戚收现金 500，给他发了50 现金
杨幂女亲戚收100 给她发了2000 微信
马云男同事收50000 给他发了10 支付宝
特朗普男朋友收1000 给他发了100 微信

貂蝉女朋友给她发了20000 现金

insert into person values(null,'刘德华','男','亲戚'), 
                         (null,'杨幂','女','亲戚'), 
                         (null,'马云','男','同事'), 
                         (null,'特朗普','男','朋友'), 
                         (null,'貂蝉','女','朋友');
insert into trade values(null,'2018-3-10',500,'现金',1),
                        (null,'2018-3-11',-50,'微信',1),
                        (null,'2018-3-12',100,'微信',2),
                        (null,'2018-3-13',-2000,'支付宝',2),
                        (null,'2018-3-14',50000,'支付宝',3),
                        (null,'2018-3-15',-10,'支付宝',3),
                        (null,'2018-3-15',1000,'微信',4),
                        (null,'2018-3-16',-100,'微信',4),
                        (null,'2018-3-20',2000,'现金',5);

统计2018年2月15号到现在的所有红包收益

select  sum(money) from trade
where time>str_to_date('2018年2月15号','%Y年%c月%d号');

查询2018年2月15号到现在金额大于100所有女性亲戚的名字和金额

select p.name,t.money
from trade t join person p 
on t.pid=p.id
where time>str_to_date('2018年2月15号','%Y年%c月%d号')
and t.money not between -100 and 100
and p.gender='女'
and p.rel='亲戚';

查询三个平台分别收入的红包金额

select type,sum(money) from trade
where money>0
group by type;

视图

什么是视图：数据库中存在的表和视图都是其内部的对象，视图可以理解为是一个虚拟的表，数据来自原表，视图本质上就是取代一段sql语句
为什么使用视图：因为有些数据查询的SQL语句比较长，每次书写比较麻烦，使用视图可以起到SQL代码重用的作用，提高开发效率，可以隐藏敏感信息

格式：create view 视图名 as 子查询

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

create view v_emp_nosal as (select ename,job,deptno,mgr from emp);

练习：

创建部门为20号部门并且工资小于3000的视图

create view v_dept_20 as (select * from emp where deptno=20 and sal<3000);

创建每个部门平均工资，工资总和，最大工资，最小工资的视图

create view v_emp_info as (select deptno,avg(sal),sum(sal),max(sal),min(sal) from emp group by deptno);

视图分类

简单视图：创建视图的子查询中不包含去重函数分组关联查询的视图称为简单视图，简单视图可以对数据进行增删改查操作
复杂试图：含去重函数分组关联查询的视图称为复杂视图，一般只进行查询操作

对视图中的数据进行增删改查

视图的操作方式跟表的方式一样

插入数据

insert into v_emp_10 (empno,ename,deptno) values(10001,'钢铁侠',10);
原表视图都有

 insert into v_emp_10 (empno,ename,deptno) values(10002,'葫芦娃',20);
原表中有 视图没有

往视图中插入一条视图中不可见但是在原表中存在的数据，称为数据污染

解决数据污染：在创建视图的时候添加 with check option 关键字

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

    insert into v_emp_10 (empno,ename,deptno) values(10003,'葫芦娃',20);

2.修改数据只能修改视图中存在的数据

    update v_emp_30 set  ename='葫芦娃' where empno=1003;

    update v_emp_30 set  ename='葫芦娃' where empno=1002;

3.删除数据只能修改视图中存在的数据

    delete from v_emp_30 where empno=1003;//视图中存在，成功

    delete from v_emp_30 where empno=1001;//视图中不存在，失败

修改视图

   - 格式：create or replace view 视图名 as 子查询；

            create or replace view v_dept_20 as (select * from emp);

删除视图

    drop view v_emp_20;

视图别名

如果创建视图的时候使用了别名则后面的各种操作只能使用别名

create view v_emp_20 as (select ename name,sal from emp);

update v_emp_20 set  name='aaa' where name='钢铁侠';//成功

 update v_emp_20 set  ename='aaa' where ename='钢铁侠';//失败

约束

什么是约束：约束是给表字段添加的约束条件

非空约束

约束字段的值不为空

create table t1(id int,age int not null);

insert into t1 values(1,20);//成功
insert into t1 values(2,null);//失败

唯一约束 unique

限制字段的值不能重复

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

insert into t2 values(1,20);//成功
insert into t2 values(1,20);//失败

主键约束

限制字段值唯一并且非空

创建表时添加

create table t_pri(id int primary key auto_increment);

创建表后添加主键约束：

create table t_pri2(id int);

alter table t_pri2 add primary key(id);

删除主键约束
```
alter table t_pri2 drop primary key;
```

默认约束 default

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

create table t3(id int,age int default 20);

insert into t3 values(1,88);
insert into t3 (id) values(2);
insert into t3 values(3,null);

检查约束 check mysql中没有效果但是语法不报错

    create table t4(id int,age int check(age>10));

外键约束

字段的值可以为null可以重复，但是不可以是不存在的
被依赖的数据不能先删除

被依赖的表不能先删除

测试外键约束

创建部门表

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

create table emp(id int primary key auto_increment,name varchar(10),
deptid int,constraint fk_dept foreign key(deptid)
references dept(id));

格式：在创建表最后一个字段后面添加 constraint 约束名 foreign key（外键字段名称） references 被依赖的表名（被依赖的字段名）
插入数据

insert into dept values(null,'神仙'),(null,'妖怪');

测试：

insert into emp values(null,'悟空',1);//成功
insert into emp values(null,'赛亚人',3);//失败   不存在的数据
delete from dept where id=1;//失败    被关联的数据

索引

什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录。
为什么使用目录：如果不使用索引，查询数据时会依次遍历每一个保存数据的磁盘块，直到找到目标数据为止，使用索引后，磁盘块会以树状结构保存，查询数量会大大降低磁盘块的访问量，从而提高查询效率。
原理图

索引是越多越好吗？
- 因为索引会占用储存空间，只对常用的查询字段创建索引。
有索引就一定好吗？
- 如果数据量小的话，添加索引反而会降低插叙效率。
尽量不要在频繁修改的表上面添加索引
索引的分类（了解）
- 1. 聚集索引（聚簇索引）：通过主键创建的索引称为聚集索引，一个表只能有一个聚集索引，添加了主键约束的表会自动创建聚集索引，聚集索引的树状结构中保存了数据
- 2. 非聚集索引：通过非主键字段创建的索引称为非聚集索引，一个表有多个，树状结构中不保存数据只保存指针（磁盘块地址）
如何非创建索引
1. linux系统把文件解压到桌面
  
  在终端中执行
```
source /home/soft01/桌面/item_backup.sql
```
2. window系统把文件解压到C根目录
```
source c:/item_backup.sql;
```
测试：
- 1. show tables;//看是否有items
- 2. select count(*) from item2;//查看是否是172万多条数据
- 3. select * from item2 where title='100'; //看查询时间是多少？ 1.15秒

如何创建索引

格式：create index 索引名 on 表明（字段名（长度））；
- 给title字段添加索引
```
create index index_item2_title on item2(title); 
```
- 再次查询看时间多少？ 0.04秒提高近30倍
```
select * from item2 where title='100';
```

查看索引

show index from item2;

删除索引

drop index index_item2_title on item2;

复合索引

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

创建标题和价格的索引：

create index index_item2_title_price on item2(title,price);

事务

数据中执行SQL语句的工作单元，不可拆分可以保证同一业务中的所有SQL语句全部成功或者失败
事务的ACID特性
- Atomicity：原子性，最小不可拆分，保证全部成功，全部失败
- Consistency：一致性，从一个一致状态到另一个一致状态
- Isolation：隔离性多个事务之间互不影响
- Durability：持久性事务完成后数据提交到数据库文件中持久保存

事务相关指令

show variables like '%autocommit%';

set autocommit =0/1;

commit;

rollback;

savepoint s1;

rollback to s1;

转载于:https://www.cnblogs.com/dxllp/p/10726057.html

MySQL

数据库相关的SQL

和表相关的SQL

和表相关的SQL

查询所有表

查看单个表属性

表的引擎

创建表并且指定引擎和字符集

查看表字段信息

对创建好的表进行修改

修改表名

修改表属性 引擎和字符集

添加表字段

删除表字段

修改表字段名和类型

修改表字段类型和位置

删除表

数据相关的SQL

插入数据

批量插入

查询

修改数据

删除数据

编码问题

主键约束

注释 comment

`和' 的区别

数据冗余

- 插入以下数据： 电视机分类下的 康佳电视价格3520库存25 夏普电视价格18888 库存100 手机分类下的苹果9价格800库存100，华为p20 价格6888 库存4

事务

- 验证过程：

SQL分类

DDL：Data Definition Language（数据定义语言）

DML：Data Manipulation Language（数据操作语言）

DQL：Data Query Language（数据查询语言）

TCL：Transaction Control Language（事务控制语言）

DCL：Data Control Language（数据控制语言）

truncate

数据库的数据类型

整型

- 常用类型：int(m) bigint(m)，m代表显示长度

浮点型

字符串

日期

导入数据

is null 和 is not null

别名

去重 distinct

比较运算符 > < >= <= = !=和<>

and 和 or

in

between x and y 包含x和y

练习题：

like

排序 order by

分页查询 limit

数值计算 + - * / % mod(7,2)等效 7%2

日期相关函数

ifnull函数

聚合函数

字符串相关函数

数学相关函数

分组查询

子查询

关联查询

笛卡尔积

等值连接和内连接

外连接

关联查询总结：

表设计之关联关系

一对一

一对多

多对多

自关联

连接方式和关联关系的区别

表设计案例：权限管理

面试题

视图

视图分类

对视图中的数据进行增删改查

修改表属性引擎和字符集

- 插入以下数据：电视机分类下的康佳电视价格3520库存25 夏普电视价格18888 库存100 手机分类下的苹果9价格800库存100，华为p20 价格6888 库存4

检查约束 check mysql中没有效果但是语法不报错