sql语句以及相关积累

一.启动和登录

1.开启mysql（windows环境）

net start mysql;

2.登录mysql

mysql -hhost -P3306 -uroot -proot;

-h 表示主机地址/ip
-P 表示端口号
-u 表示用户名
-p 表示密码

二.服务端或客户端

1.查看mysql服务端支持的字符集

show character set;

2.查看mysql客户端的字符集

show variables like 'character%';

3. 修改mysql客户端的字符集

#第一种
set character_set_client=utf8;#客户端字符集：能够让服务端正确接受客户端数据
set character_set_connection=utf8;#客户端字符集：能够允许客户端发送其它非表操作指令时服务端正确准备数据
set character_set_results=utf8;#客户端字符集：能够让服务端给客户端提供正确数据
#第二种，建议使用这种
set names utf8;#一次性配置

二.数据库

1.查看数据库

show databases; #可以使用like进行匹配

2.创建数据库

create database 数据库名;
create database 数据库名 charset = 字符集;#创建指定字符集的数据库

注：mysql中utf-8用utf8表示，不允许使用中划线；数据库的字符集仅是提供给表使用的默认字符集

3.查看数据库创建语句

show create database 数据库名;

4.修改数据库字符集

alter database 数据库名 charset 字符集;

针对以后在数据库创建的结构的默认字符集，不会修改已存在的数据表对应的字符集，5版本以前可以修改数据库名

5.删除数据库

drop database 数据名;

注：一次只能删一个

6.使用（切换）数据库

use 数据库名;

三.表

1.创建表

create table 表名(
	id int(11),
	name varchar(255)
)engine=存储引擎 default charset=字符集;

先选数据库，或者使用数据名.表名，不指定存储引擎使用默认，字符集合校对集也是

2.懒惰的方式创建已存在的表的同结构表

create table 表名 like 表名;#第一个表名是新建表的表名，第二个表名是数据源的表名

myisam存储引擎的可以复制表结构的文件来实现，但innodb存储引擎的不好实现

3.查看表

show tables;#可以使用like进行匹配

注：必须进入数据库环境

4.查看创建表语句

show create table 表名;

5.查看表结构

#第一种
desc 表名;
#第二种
describe 表名;
#第三种
show columns from 表名;

6.修改表名

rename table 旧表名 to 新表名;

7.修改表选项

alter table 表名 default charset=字符集;

8.删除表

#直接删除
drop table 表名;
#判断表是否存在再删
drop table if exists 表名;#最好判断表存不存在，不然报错

可以同时删除多张表

9.清空数据

truncate 表名;

原理： 先删除后创建表

10.校对集

#查看校对集
show collation;

#31.指定表的校对集（默认使用DBMS的校对集）
create table my_table1(
	id int(11),
	name varchar(255)
)engine=myisam default charset=utf8 collate=utf8_bin;

#32.指定数据库的校对集
create database my_database2 charset=utf8 collate=utf8_bin;

四.字段

1.新增字段

alter table 表名 add column 列名 varchar(255) default '默认值' comment '注释';

column可以省略，可以使用first或者after来指定字段的位置

2.修改字段名

alter table 表名 change column 旧字段名 新字段名 varchar(255) default '默认值' comment '注释';

注：主要用于修改字段名，column可以省略,但是它的类型和属性也要加上

3.修改字段类型[属性]

alter table 表名 modify column 字段名 varchar(255);

4. 删除字段(column可以省略)

alter table 表名 drop column 字段名;

3.修改字段的位置

alter table 表名 modify 字段名 varchar(255) first;#把字段放在首位
alter table 表名 modify id int(11) after 字段名;#把字段放在某个字段后面

默认是放在最后，change和modify指令可以用来修改位置，first是放在第一位，after是放在某个字段后面

4.主键

（1）新增主键

alter table 表名 add primary key(字段);#可以是复合主键

主键只能有一个且不能为null

（2）删除主键

alter table 表名 drop primary key;

（3）给id加上自动增长(它是主键)

alter table 表名 modify 字段 int auto_increment;

5.唯一索引

（1）添加unique key

alter table 表名 add unique(字段名);

可以是复合唯一索引，多个唯一索引

（2）删除唯一索引

alter table 表名 drop 索引名;

6.外键

（1）添加外键

#第一种,创建表时，在所有字段后面
constraint 子表表名 foreign key(子表的外键字段) references 父表表名(主键) 

#第二种,建表后添加外键
alter table 子表表名 add constraint `外键名` foreign key(子表的外键字段) references 父表表名(主键);

创建外键会自动加上普通索引，外键名整个数据库唯一，外键名使用反引号。

（2）删除外键

alter table 表名 drop foreign key `外键名`;

删除外键后索引还在

五.别名

3.别名

#字段别名、表别名
select 字段 as 字段别名 from 表名 as 表别名;

取了别名后的操作只能使用别名进行操作

六. 增删改查

1.新增数据

（1）普通新增

#指定字段新增
insert into 表名(字段1, 字段2, ...) values(值1, 值2, ...), (值1, 值2, ...); 
#不指定字段新增
insert into 表名 values(value1, value2, ...);#注：值的个数必须跟表里的字段个数相同，当然顺序也要一致
#蠕虫复制(字段可以有，也可以没有)
insert into 表名(字段1, 字段2, ...) values(select 字段1, 字段2, ... from 表名);#要注意顺序一致和字段个数相同

要注意字段跟值对应,可以新增指定字段的数据，可以新增多条数据

（2）解决插入主键冲突问题的方案

#1.忽略
insert ignore into 表名 values(值1, 值2, ...)
#2.替换
replace into 表名 values(值1, 值2, ...);
#3.更新
insert into 表名 values(值1, 值2, ...) on duplicate key update 字段=值

2.查询数据

（1）普通查询

#无条件查询
select * from 表名;# *匹配所有字段信息
#有条件查询
select 字段（一个或者多个字段，以英文逗号隔开，结尾不能有逗号） from 表名 where 字段=1; 
#多表查询
select * from 表1,表2;
#between  and 的条件查询
select * from m表名 where 字段 between 4 and 6;

#分组
select * from 表名 group by 字段;
select group_concat(字段) from 表名 group by 字段;#分组字符串连接
select group_concat(字段) from 表名 group by 字段 with rollup;#回溯统计
select * from 表名 group by 字段 having 字段>4; #筛选分组

#排序
select * from 表名 order by 字段 desc;
select * from 表名 order by  convert(字段 using gbk);#中文排序（按拼音排序）
select * from 表名 order by field(字段,1,2,5,4);#自定义排序

#限制获取
select * from 表名 limit 1;#简易版
select * from 表名 limit 1,2;#综合版

后面都可以加上条件（多个条件），“and”表示且，“or”表示或

（2）数据去重

select distinct * from 表名;

（3）标量子查询

select * from 表名 where 字段=(select 字段 from 表名 where 字段=2);

一定要使用（）括起来

（4）列子查询

select * from 表名 where 字段 in(select 字段 from 表名);
select * from 表名 where 字段 > any(select 字段 from 表名);
select * from 表名 where 字段 > all(select 字段 from 表名);

可以使用 any、some和all作为额外的匹配方式，any和some一样；一定要使用（）括起来

（5）行子查询

select * from 表名 where (字段1, 字段2)=(select 字段1,字段2 from 表名 where 字段=2);

（6）表子查询

select * from (select * from 表名) as 结果集名称;

（7）联合查询

select * from 表名 union select * from 表名;

select * from 表名 union all select * from 表名;#不去重

select * from 表名 union all select * from 表名 where 字段 ='test';#union使用where条件,属于union前后的select语句的条件限定，union是针对结果的合并

(select * from 表名 order by 字段  desc limit 9) union all (select * from 表名 order by 字段  desc limit 9 );#对select中使用order by，一定要使用（）把select语句包裹，并且使用limit辅助才生效

select * from 表名 union select * from 表名 order by 字段 ;#union使用order by，对整个union得到的结果进行排序

默认是distinct，字段数量要一致，不考虑数据类型，不限定次数

3.更新数据

#普通更新
update 表名 set 字段= 'test1' where id =1;

#限制更新
update 表名 set name = 'test' limit 1;

最好加上条件

4.删除数据

#普通删除
delete from 表名 where id= 1;

#限制删除
delete from 表名 limit 1;

最好加上条件

七.连接

1.交叉连接

select * from 表名1, 表名2;#隐式交叉连接
select * from 表名1 cross join 表名2;#显示式交叉连接

笛卡尔积，数据量很大，不建议使用

2.内连接

select * from 表名1  inner join 表名2  on 表名1.字段 = 表名2.字段;#inner可以省略

select * from 表名1  inner join 表名2  where 表名1.字段 = 表名2.字段;#可以是用where代替on,区别是on决定是否拿数据，而where是对拿出来的数据的判定，不建议使用where

select * from 表名1 inner join 表名2 ;#省略条件则最终结果跟交叉连接一样

3. 外连接

（1）左连接和右连接

select * from 表名1 left outer join 表名2 on 表名1.字段 = 表名2.字段;#左连接,以左表为主表

select * from 表名1 right outer join 表名2 on 表名1.字段 = 表名2.字段;#右连接，以右表为主表

select * from 表名1 right outer join 表名2 on 表名1.字段 = 表名2.字段 and 表名2.字段 = 1;#使用and附加条件,理论上还是属于外链接部分

select * from 表名1 right outer join 表名2 on 表名1.字段 = 表名2.字段 where 表名2.字段 = 1;#对外链接结果再进行筛选

outer可以省略，一定有on，不能使用where

（2）全外连接

select * from 表名1 full outer join 表名2 on 表名1.字段 = 表名2.字段;#全外连接,mysql不支持，会报错

select * from 表名1 left outer join 表名2 on 表名1.字段 = 表名2.字段  union select * from 表名1 right outer join 表名2 on 表名1.字段 = 表名2.字段;#这样可以实现全外链接，union不使用union all

4.自然连接

select * from 表名1 natural join 表名2 ;#自然内连接

select * from 表名1 natural left/right join 表名2 ;#自然外连接

#使用using关键字模拟自然连接
select * from 表名1 inner join 表名2 using(表名1.主键);

select * from 表名1 left/right join 表名2 using(表名1.主键);

表必须规范，并且关系字段同名，其他字段不同名，自动保留一个同名字段

八.视图

1.创建视图

create view 视图名 as select * from 表名;

create or replace view 视图名 as select * from 表名;#创建或替换

只有文件结构

2.修改视图

alter view 视图名 as select * from 表名;

3.从视图中查询数据

select * from 视图名;

4. 单基表的可以删除、更新和插入数据

delete from 视图名 where 字段 = 1;

update 视图名 set 字段 = 'test' where 字段 = 2;

insert into 视图名 values(null, '21');

5.视图的算法选择

create algorithm= undefined view 视图名 as select * from 表名;#(undefined(默认，未定义算法)

create algorithm= merge view 视图名 as select * from 表名;#merge(合并算法，将视图外部查询语句跟视图内部select语句合并后执行，效率高【系统优先选择】)

create algorithm= temptable view 视图名 as select * from 表名;#temptable(临时表算法，系统将视图的select语句查出来先得一张临时表，然后外部再查不允许写操作)

使用到排序和分组时要考虑算法带来的影响

九.用户

1.查询用户的权限

show grants for 用户名@主机地址;

2.创建用户

create user 'test_user'@'%' identified by '123456';#创建一个允许所有人访问的用户，密码为123456
create user 'test_user1'@'localhost' identified by '123456';#创建一个允许本机访问的用户，密码为123456

3.赋予用户权限

grant select on my_database.my_table to 'test_user'@'%';#给test_user用户在my_database.my_table下的select权限

grant all privileges on *.* to 'test_user'@'%';#给test_user用户所有权限

4.取消权限

revoke select on my_database.my_table from 'test_user'@'%';#取消test_user用户在my_database.my_table下的select权限

5.修改密码

set password for 'test_user' @'%' ='root';

6.删除用户和回收用户

drop user test_user;

十.备份与还原

1.数据库备份(不使用mysql客户端，-p可以先不写密码)

#单表备份
mysqldump -hlocalhost -P3307 -uroot -p my_database my_table >H:/table.sql
#多表备份
mysqldump -hlocalhost -P3307 -uroot -proot my_database my_table my_clone_table > H:tables.sql
#整个数据库备份
mysqldump -hlocalhost -P3307 -uroot -p my_database >H:database.sql

2.数据库还原

#数据库还原（必须指定数据库名字）
source H:/table.sql; #还原（登录mysql服务端后）
#还原（回车后输入密码）
mysql -hlocalhost -P3307 -uroot -p my_database <H:/database.sql

十一.事务

（1）查看自动提交的值

show variables like 'autocommit'

（2）事务示例

#修改自动提交的值
set autocommit = off;

#开启事务
start transaction;

#进行相关操作
insert into 表名(字段1, 字段2, ...) values(值1, 值2, ...), (值1, 值2, ...); 

#事务提交，如果失败了则使用rollback回滚
commit;

这里的修改自动提交的值仅仅是当前连接有效

十二.积累

1.查重

#单个字段作为判重条件
SELECT * from 表名 GROUP BY 字段 HAVING count(*)>1;


#多个字段作为判重的条件
SELECT * from 表名 GROUP BY 字段1, 字段2 HAVING count(*)>1;

#找出重复的数据，单个字段判重的
select * from 表名 join (SELECT 字段 from 表名 GROUP BY 字段 HAVING count(*)>1)
as res on res.字段  = 表名.字段 #这里的字段是相关的字段，即判重的字段

#找出重复的数据，多个字段判重的
SELECT
	*
FROM
	表名
JOIN (
	SELECT
		字段1,
		字段2
	FROM
		表名
	GROUP BY
		字段1,
		字段2
	HAVING
		count(*) > 1
) AS res ON (
	res.字段1,
	res.字段2
) = (
	表名.字段1,
	表名.字段2
);

2.其他

• nullif（expr1,expr2） 如果两个参数相等则返回NULL，否则返回第一个参数的值expr1
• 数据库的整型类型如非必要则不要填充零，有可能在不同环境下会出问题
• SELECT * from w_user where FIND_IN_SET(‘21’,email);find_in_set(str1,str2) 函数：返回str2中str1所在的位置索引，其中str2必须以","分割开
• strict是数据库的严格模式
• 反引号用于处理命名跟关键字或者保留字冲突的情况
• 凡是删除操作都要注意备份和谨慎操作
• 整型的unsigned指定不使用负数
• int(11)括号里的数字表示宽度（zerofill填充零）
• varchar(222)括号里的数字表示长度
• 一个表的行有最大65535个字节限制，text的数据不存在行中
• 如果查询包括group by 但用户想要避免排序结果的消耗，则可以指定order by null禁止排序
• inet_aton将ip地址转换成数字型，inet_ntoa将数字型转换成ip地址
• where in 值太多会导致全盘检索
• mysql有中文的json串使用case语句批量更新时有问题，中文不转Unicode就可以解决
• 使用 “or” 注意索引失效的问题（“or”的两边必须都是索引才会走索引）；如果有多个“or”注意层级问题，可以使用括号来解决