MySQL常用语句

-- 双中划线+空格:注释(单行注释),也可以使用#号


-- 创建数据库
create database mydatabase charset utf8;


-- 使用关键字或者保留字,必须使用反引号
create database `database` chartset utf8;


-- 创建中文数据库(中文数据库是可以的,但是要有前提条件 set name gbk,建议不用)
set name gbk
create database `中国` chartset utf8


-- 在数据库的SQL语句执行之后,发生了什么?
1 .在数据库系统中,增加了对应的数据库信息
2 .会在保存数据的文件夹下:Data目录,创建一个对应数据库名字的文件夹
3 .每个数据库下都有一个opt文件:保存了库选项




-- 查看数据库创建语句(关键字需要转义)


show create database mydatabase;


-- 更新数据库
1.数据库名字不可以修改
2.数据库的修改仅限库选项:字符集和校对集(校对集依赖字符集)


-- 修改数据库的字符集
alter database mydatabase charset GBK;


-- 删除数据库
drop database mydatabase


-- 创建表
create table mytable(
字段名字 数据类型,
字段名字 数据类型
)


create table if not exists mydatabase.student(
name varchar(10),
gender varchar(10),
num varchar(10),
age int
)charset utf8;


create table if not exists class(
name varchar(10),
room varchar(10)
)charset utf8;


当创建数据表的SQL指令执行之后,
1.指定的数据库下已经存在的表
2.在数据库对应的文件夹下,会产生对应表的结构文件(跟存储引擎有关系)




查看表结构:查看表中的字段信息
Desc/describe/show columns from student




--表操作
--修改字段
字段操作很多:新增,修改,重命名,删除数据库


 --新增字段 增加ID到第一个位置
alter table my_student
add id int 
first;


--修改字段
alter table my_student
modify num char(10) 
after id;


alter table my_student 
change gender sex varchar(10) ;


-- 删除字段
alter table my_student 
drop address
----------插入数据---------


insert into my_student value(,,,),(,,,);
---------更新数据--------
update my_student set sex = 'nv' where id = 1;


---------删除数据--------
delete from my_student where id = 1;


----------中文问题-------
查看服务器支持的字符集
show character set 


查看服务器默认的对外处理的字符集
show variables like 'character_set%';


修改服务器认为的客户端数据的字符集为gbk
set character_set_client =gbk;
修改服务器返回的设定的字符集为 gbk
set character_set_result =gbk;


设置服务器对客户端的字符集的认识:可以使用快捷方式


set names = gbk;


---------校对集--------
校对集应用:只有当数据产生比较的时候校对集才会生效


创建表使用不同的校对集
create table my_collate_bin(
name char(1)
)charset utf8 collate utf8_bin;


create table my_collate_ci(
name char(1)
)charset utf8 collate utf8_general_ci;


插入数据
insert into my_collate_bin values('a'),('A'),('B'),('b');
insert into my_collate_ci values('a'),('A'),('B'),('b');


比较:根据某个字段进行排序:order by asc (升序) desc (降序) 默认升序


校对集:必须在没有数据之前声明好,如果有了数据,那么在进行校对集修改:那么修改无效.


---有数据后修改校对集
alter table my_collate_ci collate = utf8_bin;
alter table my_collate_ci collate = utf8_general_ci;


--------创建整型表
create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint
)charset utf8;


insert into my_int values (100,100,100,100);


insert into my_int values('a','b','199','f');


insert into my_int values (255,10000,10000,100000);


-------- 增加无符号类型
alter table my_int add int_5 tinyint unsigned;


-- 再插入数据
insert into my_int values(127,1000,10000,100000000,255);


--- 显示宽度
alter table my_int add int_6 tinyint(1) unsigned;
insert into my_int values(127,1000,10000,100000000,255,255);


alter table my_int add int_7 tinyint(2) zerofill;
insert into my_int values(1,1,1,1,1,1,1);
insert into my_int values(1,1,10,10,10,10,254);




-----------主键--------
-----增加主键
create table my_pri1(
name varchar(20) not null comment '姓名',
number char(10) primary key comment '学号,不能重复'
)charset utf8;
----复合主键
create table my_pri2(
number char(10) comment '学号',
course char(10) comment '课程代码',
score tinyint unsigned default 60 comment '成绩',
-- 增加主键限制:学号和课程号应该是对应的,具有唯一性
primary key(number,course)
)charset utf8;


-- 追加主键
create table my_pri3(
course char(10) not null comment '课程编号',
name varchar(10) not null comment '课程名称'
)charset utf8;
-- 追加主键
alter table my_pri3 modify course char(10) primary key comment '课程编号';
alter table my_pri3 add primary key(course) ;
 
---- 删除主键
alter table my_pri1 drop primary key; 


----- 逻辑主键
create table my_student(
id int primary key auto_increment comment '逻辑主键',
number char(10) not null comment '学号',
name varchar(10) not null comment '姓名'
)


--- 自增长
create table my_auto(
id int auto_increment primary key comment '自动增长',
name varchar(10) not null comment '姓名'
)charset utf8;


--- 触发自增长
insert into my_auto(name) values('邓立军'); 
insert into my_auto values(null,'公孙瓒');
insert into my_auto values(default,'公孙瓒');


--- 修改自增长
alter table my_auto auto_increment = 4;(向下修改不生效)
alter table my_auto auto_increment = 10;(向上)




---- 查看自增长变量
show variables like 'auto_increment%';


---- z删除自增长
alter table my_auto modify id int;(不用再加primary key)


--- 增加唯一键
create table my_unique1(
number char(10) unique comment '学号:唯一,允许为空',
name varchar(20) not null
)charset utf8;






----------主键冲突 :更新--------
insert into my_class values('1','A') on duplicate key update room = 'A'
-----------主键:替换--------
replace into my_class values('1','B');


-- 复制创建表
create table my_copy like my_gbk;


-- 蠕虫复制
insert into my_copy select * from my_collate_bin;
insert into my_copy select * from my_copy;


--- 限制
update my_copy set name = 'c' where name = 'a' limit 3;


--- 删除数据:限制记录数为10
delete from my_copy where name = 'b' limit 10;


----------select选项
select * from my_copy;
select all * from my_copy;


--- 去重
select distinct * from my_copy;


-- 字段别名
select id,
num as 学号,
name as 姓名,
sex as 性别 from my_student;


--- 多表数据源
select * from my_student,my_class;


-- 子查询
select * from (select * from my_student) as s;


-- 增加height字段 
alter table my_student add height tinyint unsigned comment '身高';
--- 增加值: rand取得一个随机数
update my_student set height = floor(rand() * 20 + 170);
--- 找学生ID为1,3,5的学生
select * from my_student where id =1 || id =3 ||id = 5;
select * from my_student where id in(1,3,5);
--- 找身高在180到190之间的学生
select * from my_student where height >= 180 and height <=190;
select * from my_student where height between 180 and 190;
--- 根据性别分组
select * from my_student group by sex;


---- 分组的意思:是为了统计数据(按组统计:按分组字段进行数据统计)
---- 分组统计:身高高矮,年龄平均和总年龄
---- SQL提供了一系列统计函数
---- Count():统计分组后的记录数:每一组有多少记录
----- Max():统计每组中最大的值
---- Min():统计最小值
---- Avg():统计平均值
---- Sum():统计和
---- 分组会自动排序:根据分组字段:默认升序




--- 多字段分组:先根据一个字段进行分组,然后对分组后的结果再次按照其他字段进行分组


select c_id ,sex,count(*) from my_student group by c_id,sex; -- 多字段排序(先按照班级排序,然后按性别排序)
有一个函数:可以对分组的结果中的某个字段进行字符串连接(保存该组所有的某个字段):
group_concat(name)
select c_id ,sex,count(*),group_concat(name) from my_student group by c_id,sex;


回溯统计:with rollup:任何一个分组后都会有一个小组,


-- 统计
select c_id,count(*) from my_student group by c_id;
-- 回溯统计
select c_id,count(*) from my_student group by c_id with rollup;
--- 多字段分组回溯统计
select c_id ,sex,count(*),group_concat(name) from my_student group by c_id,sex with rollup;
--- 多字段回溯:考虑第一层分组会有此回溯;第二次分组要看第一次分组的组数,
组数是多少,回溯就是多少,然后就加上第一层回溯即可


----having字句
having 能做where能做的几乎所有事情,但是where却不能做having能做的很多事情
1. 分组统计的结果或者说统计函数都只有having能够使用
求出所有班级人数大于等于2的学生人数
select c_id,count(*) from my_student group by c_id having count(*)>=2;


Having 能够使用字段别名:where 不能:where是从磁盘去数据,
别名是在字段进入到内存
select c_id,count(*) as total from my_student group by c_id having total>=2;


---- order by 字句
Order by:排序,根据某个字段进行升序或者降序排序,一栏校对集.
基本语法
Order by 字段名 -- asc升序(默认) desc 降序
-- 排序
select * from my_student order by c_id;
-- 多字段排序:先班级排序,后性别排序
select * from my_student order by c_id ,sex;


----- Limit字句
1. 用来限制长度(数据量):limit数据量
2. 限制起始位置,限制数量:limit起始位置,长度


select * from my_student limit 2;
select * from my_student limit 2,1;-- 用来实现数据分页 


Length :每页显示的数据量:基本不变
Offset: offset =(页码-1)*每页显示量




------- change 
alter table my_student change id id int auto_increment first




---------- 连接查询
----SQL 中将连接查询分成四类:内连接,外连接,自然连接和交叉连接 join
----左表:在join关键字左边的表
----右表:在join关键字右边的表


---交叉连接
基本语法:左表 cross join 右表; 等价于   from左表,右表
---- 交叉连接存在的价值:保证连接这种结果的完整性


--- 内连接
内连接: inner join ,从左表中取出每一条记录,去右表中与所有的记录进行匹配:匹配必须
是某个条件在左表中与右表中相同最终才会保留结果,否则不保留,
select * from my_student inner join my_class on my_student.c_id = my_class.id;


字段别名以及表别名的使用:在查询数据的时候,不同表有同名字段,这个时候需要加上表名才能区分
而表名太长,通常可以使用别名
select s.*,c.name c_name,c.room from my_student s join my_class c on s.c_id = c.id; 


内连接还可以使用where代替on关键字(where没有on效率高)




--- 外连接
外连接:以某张表为主,取出里面的所有记录,然后每条与另外一张表进行连接
:不管能不能匹配上条件,最终都会保留.能匹配,正取保留,不能匹配,其他表的字段都置空


外连接分为两种:是以某张表为主:有主表
left join:左连接,左表为主表 
select s.*,c.name c_name,c.room from my_student s left join my_class c on s.c_id = c.id;
right join:右连接,以右表为主表
select s.*,c.name c_name,c.room from my_student s right join my_class c on s.c_id = c.id;