MYSQL语句

SQL整理

连接数据库

• Linux系统:

mysql -uroot -p

导入*.sql数据到数据库

source ??/??/??.sql

数据库相关的SQL

1.查看所有数据库

show databases;

2.创建数据库，并指定字符集

create database 数据库名称;
create database 数据库名称 character set utf8/gbk;

3.删除数据库

drop database 数据库名字;

4.查看数据库详情

show create database 数据库名字;

5.使用数据库

use 数据库名字;

表相关SQL

1.创建表并

create table 表名(字段1名 字段类型,字段2名 字段类型,...);

(1)指定引擎和字符集

create table 表名(字段1名 字段类型,字段2名 字段类型，...) engine=innodb/myisam charset=gbk;

(2)注释(comment)

create table 表名(字段1名 字段1值 primary key auto_increment comment '这是自增主键',字段2名 字段2值 comment '这是字段2',...);

约束

(1)主键约束（保证主键的值唯一并且非空）（primary key）

create table 表名(字段1名 字段类型 primary key,字段2名 字段2值,...);

(2)主键约束and自增(primary key auto_increment)

create table 表名(字段1名 字段1值 primary key auto_increment,字段2名 字段2值,...);

ps:删除表并创建新表，自增数值清零：

truncate table 表名;

(3)非空约束（该字段的值不能为null）(not null)

create table 表名 (字段1名 字段1类型 not null,字段2名 字段2类型,...);

(4)唯一约束（该字段的值不能重复）(unique)

create table 表名 (字段1名 字段1类型 unique,字段2名 字段2类型,...); 

(5)默认约束（给字段设置默认值）(default)

create table 表名 (字段1名 字段1类型 default 默认值,字段2名 字段2类型,...);

(6)外键约束

• 外键：用来建立关系的字段称为外键
• 外键约束： 添加外键约束的字段，值可以为null，可以重复，但是值不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表也不能先删除

create table 表名(字段1名 字段1类型 primary key auto_increment,字段2名 字段2类型,字段3名 字段3类型,constraint 约束名称 foreign key(外键字段名) references 依赖地表名(依赖的字段名));

• 格式介绍：constraint 约束名称 foreign key(外键字段名) references 依赖的表名(依赖的字段名)

2.查看所有表

show tables;

3.查看表详细

show create table 表名;

4.查看表字段信息

desc 表名;

5.修改表名

rename table 原名 to 新名;

6.修改表引擎和字符集

alter table 表名 engine=myisam/innodb charset=gbk/utf8;

7.添加表字段

alter table 表名 add 字段名 字段类型;//最后
alter table 表名 add 字段名 字段类型 first;//最前面
alter table 表名 add 字段名 字段类型 after 原有字段名;//在原有字段后面

8.删除表字段

alter table 表名 drop 字段名;

9.修改字段名称和类型

alter table 表名 change 原字段名 新字段名 型类型;

10.修改字段类型和位置

alter table 表名 modify 字段名 新类型 first/after 原有字段;

11.删除表

drop table 表名;

数据相关

1.插入数据

全表插入：

insert into 表名 values(字段1值.字段2值，...，全部字段值);

(1)指定字段插入：

insert into 表名 (字段1名，字段2名,...指定字段) values (字段1值,字段2值,...指定字段值);

(2)批量插入数据：

insert into 表名 values(字段1值，字段2值，...全字段值),(字段1值，字段2值，...全字段值),....;
insert into 表名 (字段1名,...)values(字段1值,...),(字段1值,...),....;

2.查询数据：

select 字段信息 from 表名 where 条件;
select 字段信息 from 表名 where 条件 group by 字段名 order by 字段名 limit (页数-1)*每页数量,每页数量;

(1)分组group by

select 字段信息 from 表名 group by 字段名;
select 字段信息 from 表名 group by 字段名1,字段名2;

a.group_concat()：

select 字段1名，group_concat(字段名,'随意可连接符',字段名)from emp group by 字段1名;

ex:查询每个部门的员工姓名，要求每个部门只显示一行

select deptno,group_concat(ename) from emp group by deptno;

(2)排序order by(+字段名 desc降序/asc升序(默认))

select 字段信息 from 表名 order by 字段名;
select 字段信息 from 表名 order by 字段名1,字段名2,...;(先字段1，再字段2，再...);

(3)分页查询limit(limit 跳过的条数,请求的数量)

select 字段信息 from 表名 limit (页数-1)*每页数量,每页数量;

(4)having

• having后面可以写普通字段的条件也可以谢桔核函数的条件，但是不推荐再having后面写普通字段的条件
• where后面不能写聚合函数的条件
• having要结合分组查询使用
  ex：查询每个部门的平均工资，要求工资大于2000.

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

(5)数值计算+，-，*，/，%，7%2=mod(7,2)
(6)子查询

• 嵌套在SQL语句中的查询语句称为子查询
• 子查询可以嵌套n层
• 子查询可写位置
  a.写在where或having后面作为查询条件的值
  b.写在from后面当成一张表示用,必须有别名
  ex：

select ename from(select * from emp where deptno=20)t1;

c.写在创建的时候
ex:

create table newemp as(select ename,sal,deptno from emp where deptno=20);

(7)关联查询

• 同时查询多张表的查询方式称为关联查询
  ex：查询每个员工姓名和其对应的部门名称

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

a.笛卡尔积

• 如果关联查询不写关联关系，则得到两张表结果的乘积，这个乘积称为笛卡尔积
• 笛卡尔积是错误的查询方式导致的结果，工作中切记不要出现
  b.等值连接和内连接
• 这两种查询方式得到的结果是一样的
• 等值连接：

select 
	*
 from 
 	A,B 
 where 
 	A.x=B.x and A.age=18;

• 内连接：

select 
	* 
from 
	A join B on A.x=B.x 
where 
	A.age=18;

c.外连接

• 查询A，B两张表的数据，如果查询两张表的交集数据使用内连接或等值连接，如果查询某一张表的全部数据另外一张表的交集数据则用外链接
• 左外链接：

select 
	* 
from 
	A left join B 
on 
	A.x=B.x 
where 
	A.age=18;

• 右外链接：

select 
	* 
from 
	A right join B 
on 
	A.x=B.x 
where 
	A.age=18;

ex：查询所有员工和对应的部门名称

select 
	e.ename,d.dname
from 
	emp e left join dept d
on 
	e.deptno=d.deptno;

ex：查询所有部门名称和对应的员工姓名

select 
	e.ename,d.dname
from 
	emp e right join dept d
on 
	e.deptno=d.deptno;

d.自关联

• 当前表的外键指向当前表的主键，这种关联方式叫做自关联
• 应用场景：需要保存上下级关系时

**ex：**查询员工姓名和对应的主管姓名

select 
	e.ename,m.ename
from 
	emp e join emp m
on 
	e.mgr=m.empno;

e.连接方式和关联关系

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

3.修改数据：

update 表名 set 修改字段名=修改字段值 where 条件;

4.删除数据

delete from 表名 where 条件;

删除表中所有数据

delete from 表名;

5.条件语句

(1)is null和is not null

select 字段信息 from 表名 where 字段名 is null(或is not null);

(2)别名

select 字段名 as '字段别名' from 表名;
select 字段名 '字段别名' from 表名;
select 字段名 字段别名 from 表名;

(3)去重distinct

select distinct 字段名 from 表名;

(4)比较运算符>,<,>=,<=,=,!=和<>

>大于,<小于,>=大于等于,<=小于等于,=等于,!=和<>不等于

(5)and和or

• and逻辑与，or逻辑或

(6)in和not in(当查询某个字段值为多个时使用)

• select 字段信息 from 表名 where in(值1,值2,…);
• select 字段信息 from 表名 where not in(值1,值2,…);

(7)between x and y(包括x和y且x<y,同…where字段名>=and字段名<=;)

select 字段信息 from 表名 where between x and y;

(8)模糊查询like

• _:代表单个未知字符，**%**代表零个或多个未知字符

数据类型

1.整数类型

• int(m)
• bigint(m)
  ps：
  (1)m表示显示长度
  (2)分别相当于Java中int，long
  (3)可结合zerofill使用

2.浮点型

• double(m,y)
• decimal？
  (1)m代表总长度，y代表小数长度
  (2)decimal超高精度小数，当涉及超高精度运算时使用

3.字符串

• char(m)
• varchar(m)
• text(m)
  (1)char固定长度，最大255
  (2)varchar可变长度,最大65535
  (3)text可变长度，最大65535
  (4)255内建议varchar，超过255建议text
  (5)当值为’abc’、m=10时，固定长度所占长度10，可变长度所占长度3
  (6)可变长度更节省空间，固定长度执行效率略高

4.日期

• date（只能保存年月日）
• time（只能保存时分秒）
• datetime（保存年月日时分秒，最大值9999-12-31，默认值null）
• timestamp（保存年月日时分秒，最大值2038-1-19，默认值当前时间）

SQL函数

日期相关函数

1.获取当前 日期+时间 now（）

select now();

2.获取当前的日期 curdate() cur(current当前）

select curdate();

3.获取当前时间 curtime()

select curtime();

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

select date(now());
select time(now());

5.从年月日时分秒中提取去时间分量 extract(xxx from now());

selecct extract(year from now());
selecct extract(month from now());
selecct extract(day from now());
selecct extract(hour from now());
selecct extract(minute from now());
selecct extract(second from now());

6.日期格式化

• 格式：

%Y：四位年2020；
%y：二位年20；
%m：两位月01；
%c：一位月1；
%d：日
%H：24小时
%h：12小时
%i：分
%s：秒

date_format(时间,格式);

• 把时间默认格式转成 年 月 日 时 分 秒

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

• 把非标准格式的时间转回默认格式

str_to_date('非标准格式的时间',格式);
select str_to_date('14.08.2018 08:00:00',%d.%m.%Y %H:%i:%s);

ifnull(x,y)函数

• 字段名=ifnull(x,y)如果x的值为null则字段名=y,如果x值不为null则字段名=x
• ex：把员工表中奖金为null的改成0其它的不变；

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

聚合函数

• 聚合函数用于对多行数据进行统计，平均值，最大值，最小值，求和，统计数量
  1.平均值：

avg(字段名称);

• ex:查询美丽日记商品的平均单价

select avg(price)from t_item where title like '%美丽日记%';

2.最大值：

max(字段名);

• ex:查询30号部门(deptno)中的最高奖金(comm)(员工表emp)

select max(comm)from emp where deptno=30;

3.最小值：

min(字段名);

• ex:查询20号部门的最低工资

select min(price) from emp where deptno=20;

4.求和：

sum(字段名);

• ex：查询30号部门每个月需要发多少公司

select sum(sal) from emp where deptno=30;

5.统计数量：

count(字段名);

• ex：查询员工表中的员工总数

select count(*)from emp;

字符串相关函数

1.字符串拼接:

concat('aa','bb')//值为aabb

2.获取字符串的长度:

char_length('abc')//值为3

3.获取字符串在另一个字符串出现的位置:

 instr(str,substr);
locate(substr,str);

4.插入字符串:

 insert(str,start,length,newstr);

5.转大小写:

upper(str)
lower(str)

6.去两端空白:

trim(str);

7.截取字符串:

left(str,num);
right(str,num);
substring(str,start,length);

8.重复:

repeat('ab',2);

9.替换:

replace（str,被替换字符,替换字符）

10.反转:

reverse(str);

数学相关函数

1.向下取整

floor(num)
select floor(3.84); //3

2.四舍五入

round(num)
select round(23.8); //24

3.取到小数几位

round(num,m)
select round(23.879,2); //23.88

4.非四舍五入

select truncate(23.879,2); //23.87

5.随机数 rand() 0-1

• 获取3、4、5 随机数

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

视图

1.创建视图的格式

create view 视图名 as 子查询;
create table 表名 as 子查询;

视图的分类

1.简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询。可以对视图中的数据进行增删改查操作
2.复杂视图：和简单视图相反，只能对视图中的数据进行查询操作

• 创建一个复杂视图

create view v_emp_info as (select avg(sal),max(sal),min(sal) from emp);

-查询

 select * from v_emp_info;

对简单视图进行增删改查，操作方式和table一样

1.插入数据

insert into v_emp_10 (empno,ename,deptno) values(10011,‘刘备’,10);
select * from v_emp_10;
select * from emp;

• 如果插入一条在视图中不可见，但是原表中却可见的数据称为 数据污染。

insert into v_emp_10 (empno,ename,deptno) values(10012,‘关羽’,20);
select * from v_emp_10;
select * from emp;

• 通过 with check option 解决数据污染问题

create view v_emp_20 as (select * from emp where deptno=20) with check option;
insert into v_emp_20 (empno,ename,deptno) values(10013,‘赵云’,20); //成功
insert into v_emp_20 (empno,ename,deptno) values(10014,‘黄忠’,30); //失败

2.修改和删除视图中的数据（只能修改删除视图中有的数据）

update v_emp_20 set ename=‘赵云2’ where ename=‘赵云’;
update v_emp_20 set ename=‘刘备2’ where ename=‘刘备’;//修改失败
delete from v_emp_20 where deptno=10;//没有数据被删除

3.创建或替换视图

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

4.删除视图

drop view 视图名;
drop view v_emp_10;
show tables;

• 如果创建视图的子查询中使用了别名 则对视图操作时只能使用别名

create view v_emp_10 as (select ename name from emp where deptno=10);
select name from v_emp_10;//成功
select ename from v_emp_10;//失败

#####视图总结
1.视图是数据库中的对象，代表一段SQL语句，可以理解成一张虚拟的表
2.作用： 重用SQL，隐藏敏感信息
3.分类：简单视图（创建视图时不使用去重、函数、分组、关联查询，可以对数据进行增删改查）和复杂视图（和简单视图相反，只能对数据进行查询操作）
4.插入数据时有可能出现数据污染，可以通过with check option解决
5.删除和修改只能操作视图中存在的数据
6.起了别名后只能用别名

索引

索引分类

• 聚集索引：通过主键创建的索引称为聚集索引，聚集索引中保存数据，只要给表添加主键约束，则会自动创建聚集索引
• 非聚集索引：通过非主键字段创建的索引称为非聚集索引，非聚集索引中没有数据

如何创建索引 title varchar(10)

格式：

create index 索引名 on 表名(字段名(字符长度))

ex:

create index index_item_title on item2(title);

如何查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

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

create index 索引名 on 表名(字段1,字段2);