sql语句

-- 一 数据库宏观基本操作（数据库，表，数据的操作）


-- 双中划线加空格表示注释（单行），也可以用#号 


create database mydatabase1 charset utf8;


-- 查看所有数据库 


show databases;


--更新数据库


-- 删除数据库


drop database mydatabase1;


--删除数据(某一条)


delete from student where stu_id = '0006';


-- 新增数据库表 create table if not exists 数据库命.表名(表成员)




 create table if not exists testdatabase.class(
 
 class_name varchar(10),


 class_number int


 )charset utf8;


 --新增加列


 alter table student add stu_score varchar(10);




-- 插入数据 insert into 表名 vaule(内容)  可以添加多条内容 用逗号隔开


insert into student values ("tom","19","java","0001");


-- 插入数据的时候也可以不按照 定义表的顺序填写内容 但是必须要在 表明后面添加你所依据的顺序 其他相同


insert into student (stu_age, stu_class,stu_id,stu_name)values("20", "java","0005","jack");


-- 查看所有数据 select


select * from student;


-- 查看指定字段，指定条件数据，例如查看id=1的


select stu_id , stu_name ,stu_class
from student
where stu_id ="0001";


-- 更新数据 update 表名 set 字段 = 值 [where=。。。]


update student 
set stu_name = "dahuilang"
where(stu_name = "lang");


-- 删除数据 delete from 表名 where 删除条件


delete from student 
where stu_id ="0005";




-- 中文问题 查看所有字符集 show character set;


-- 中文问题 查看当前服务器默认 字符集


show variables like 'character_set%';




-- 更改服务器默认的字符集 此处是指客户端识别改为gbk（认证中文字，更改为gbk）


set character_set_client = gbk;


-- 修改修改服务器默认字符集 此处修改 服务器传送给客户端时 传送类型为gbk；


set character_set_results = gbk;


-- 快捷设置字符集(一下修改三个 客户端character_set_client,connection,results )


set stu_name = gbk;


-- 查看所有的校对集


show collaction;


-- 排序查找  asc 升序 desc 降序 (放在比较字段后面)


select * from student order by stu_id desc;




--二  数据库基本数据类型介绍




-- 1 数值型 （整形4类  小数类型 ：定点类型，浮点类型）
-- 2 创建定点数与浮点数  1 浮点数  f1 float （10,2），括号前面值的是浮点数总长度 ，后面是小数部分长度
-- 创建定点数 2 d1 decimal （10 ，2） 浮点数小数部分即使没超过限制也会进行四舍五入，定点数不会，而且定点数的整数部分不能超出范围


-- 3 时间类型
-- 定义时间格式  时间的类型有datetime,date ,time ,timestamp,year4 (time可以为负数，year可以是两位插入也可以是四位插入)


-- 4 字符串类型（char ,varchar ,text , blob, enum, set）


-- 分为变长字符串和定长字符串


-- 定长字符串例如char（4） ，定义了储存长度
-- 变长字符串 例如 varchar（4） ， 定义了最大存储空间 ，首先会分派如此才空间 ，根据实际用多少来确定最终长度


-- 5 文本字符串（Test 存储文本，Blob 存储二进制数据：比如说图片。通常不用）


-- 6 枚举类型 enum： 事先将所有可能出现的结果都设计好 用法 enum（'情景1','2','3'）


-- 添加枚举类型的性别


alter table student add stu_sex enum('男','女');


-- 7 集合类型  参考枚举类型 ，但是输入元素可以多选


-- set(集合列表);


-- 定义表的时候，尽可能满足所有数据不为空（空类型没有意义，1+null = null）


-- 列描述：comment ， 描述，没有实际含义：是专门用来描述字段，会根据表创建语句保存（类似注释）


-- 默认值：关键字default，默认数据的值 






-- 三


-- 1 主键
-- 增加主键 
-- 1.直接在在字段后跟上primary key关键字 。 
-- 2 . 在创建完表之后，使用primary key （主键字段列表，定义复合主键）依旧放在表的定义中：例如creat table（(列定义)，primary key (列名)）charset utf8;
-- 3  表已经创建好 ，追加主键的方法 ，alter table 表名 add primary key（字段列名）


-- 删除主键
-- alter table 表名 drop primary key；


-- 2 自增长 auto_increment
--  定义自增长 例如： id int primary key auto_increment; 之间与自增长搭配使用
--  如果没有规定出事值，即初始值为默认值时，第一个自增长数值为1，
-- 删除自增长：alter table 表名 modify 列名 类型；（modify为关键字）


-- 3 唯一建  ：默认为空。创建方法参考主键 关键字 unique/ unique key。
-- 删除唯一建 alter table 表名 drop index 索引名字（唯一建的索引名字即为字段名）。


-- 4 索引


-- 范式
-- 第一范式（1NF）：第一范式似要求数据具有原子性，不可再分。


-- 数据库高级操作：
--1.当新添加的数据和原有的数据产生主键冲突时，进行主键更新操作
-- Insert into 表名[字段列名：包含主键] values (值列表) on duplicate key update =新值；
-- 或者进行替换 replace into my_class[参数列表] values(值)


-- 2.
-- 数据库更新数据（limit）
-- update 表名 set 字段 = 值 [where条件][limit 更新数量];
-- 删除数据同理 Delete from 表名 [where 条件] limit [数量]；
--  (重置自增长，删除并且重建该表) Truncate 表名；


-- 3.完整的选择语法
-- select[select选项]列名 字段别名 或者* from 数据源 [where 条件字句][group by 字句]
-- [having 字句][order by 字句][limit 字句]； select 选项 ：All 保留所有的，Distinct ：去除所有字段都相同的


-- 4.字段别名 
-- 当字段名不清楚的时候，可以用个字段别名，即把列名在查找中用别名替代
-- select  id [as] 学号, name [as] 姓名 ，sex[as] 性别 from student;


-- 5.多表数据源 from 表1，表2，表3；
-- 6. 子查询 select * from （select * from 某表）as 表名； as把查询后的结果作为一个表 ，子查询就是把查询结果作为一个数据源再次查询


-- 7 where介绍 
-- 例如：寻找id为1，3，5的sql语句   select* from 表名 where id in(1,3,5);
-- 例如：寻找身高在 180-190之间的学生  select * from 表名 where height>=180 ||(或者此处用and替代) height <= 190
---   select * from 表名 where height between 180 and 190;  between 为闭区间




-- 8.分组group by  其他关键字count（），max（），min（），avg（），sum（），
-- count 统计总和时候会放弃null（即当前值为null的时候不统计该行），group by 会默认对分组字段进行排序（排序对象是查询结果）
-- 例如：seiect sex ，count（*），max（height），min（height），avg（age），sum（age）from 数据源 group by sex；
-- 分组统计 统计按照性别分组下 总人数  身高最高的 和最矮的，还有年龄平均数 年龄总和
-- select sex ， count(*),max(height),min(height),avg(age),sum(age) from 表名 group by sex;


-- 9.对已经定义好的列添加值 floor向下取证 ceil向上取整,rand 是随机取值 等同于java中random
-- update 表名 set 列名 = [floor] rand(),[floor] rand();  此处可以添加多列


-- 10. 多字段分组
-- 先根据 一个字段进行分组，然后对分组后的结果进行其他字段分组
-- 例如：按照先按照id 分组在按照性别分组 select c_id ，sex ，count（*），group_concat(name)from 表名 group by c_id,sex;
-- group_concat():对分组结果结果中的某个字段进行连接（保留该组的字段）


-- 11 having 语句  与where的区别：where是操作从磁盘中取出的数据，但是对进行分组（group by ）的语句不行
-- having 能做where能做的几乎所有事情，
-- 例如求出所有人数大于2的班级 select c_id,count(*),from my_student group by c_id havaing count(*)>=2;
-- having 能够用字段别名：where不能，因为where是从磁盘取出数据，而名字只可能是字段名，别名是在字段名进去
-- 内存之后才产生的。可以进行代码优化
-- 例如求出所有人数大于2的班级 select c_id,count(*) as total,from my_student group by c_id havaing total>=2;
-- 此处having之后的count（*）已经统计过一次 ，所以不用再次统计，


-- 12 order by 语句  ：排序根据某个字段进行升序或者降序排序，依赖校对集
-- order by 字段名 [asc(升序，默认值)|desc(降序)];


-- 13 limit字句： 用法1: limit 长度（查询对应长度的数量）
-- 用法2：limit 起始 ，长度 （查询从起始位置开始，到长度个单位，最初起始值为0）
-- limit 实现分页。


-- 多表查询
-- 1 交叉连接：（笛卡尔积）基本语法 左表cross join 右表；（等价于from 左表，右表）
-- 2 内连接：[inner] join,从左表中取出每一条记录，去由右表中与所有的记录进行匹配：匹配必须是某个字段左右表中相等才会保留结果
--  例如：左表 [inner] join 右表 on 左表.字段 = 右表.字段；（on表示连接条件）
-- 3 外连接：outer join ,以某张表为主，取出所有的信息，与另外一张表进行连接，不管能不能匹配上，都会保留，
--  能匹配正确保留，不能匹配字段信息设置为null。
-- 外连接分为两种：左连接：left join ：左外连接 ，以左表为主 。同理 右连接：right join
-- 4 自然连接：nature join ,自然连接，就是自动匹配连接条件：系统以字段名字作为匹配模式（同名字段就作为条件，多个同字段就都作为条件）
-- 5 自然外连接  nature (left/right) join




-- 外键 ：如果一张表中有一个字段（非主键）指向另外一张表的主键，该字段称之为外键 （外键名是通过show creat table 表名 展示）
-- 1 创建表的时候增加外键：再所有的表字段之后，使用foreign key （外键字段） reference外部表（主键字段）
-- 2 在创建表之后增加外键：修改表结构 Alter table 表名 add [constraint 外键名字] foreign（外键字段）references 父表（主键字段）；
-- 外键不可修改只能先删除后新增:Alter table 表名 drop foreign key 外键名;一张表中有多个外键但是外键名不相同.


-- 外键作用：在对外键进行写数据的时候如果外键字段不能和父表相匹配，则操作失败。
-- 外键作用：在对父表进行删除和修改主键的时候，如果被子表引用，则不能修改成现在没有的内容。
-- 外键存在条件：1 保证数据库存储引擎是innodb（默认存储引擎）
-- 2 外键西段类型必须与父表主键类型相同，3 外键名不能相同




-- 联合查询：将多次查询（多条select语句），在记录上进行拼接（字段不会增加），每一条select 语句获取的列数必须相同（字段类型无关），字段名保留第一张表的字段


-- 语法：select 语句1  union[union选项] select 语句2 .... 
-- union 选项 ：1 All ：保留所有   2 Distinct ：去重 （默认）
-- 联合查询实例：学生表中查询男生年龄升序，女生年龄降序。 注意在联合查询中order by不能直接使用，
-- 要对查询语句使用括号，而且必须搭配limit ，
-- （select * from student where sex = "男" order by age asc limit 10）
-- union 
-- （select * from student where sex = "女" order by age desc limit 10）;




-- 子查询：sub query  子查询是在某个查询结果之上进行查询，（select中包含select语句）
-- 子查询分类：1 按照位置分类：（1）from 子查询：子查询在from之后。（2）where 子查询：跟在where后 （3）Exists子查询
-- 2 按照结果分类：（1）标量子查询 （2） 列子查询：结果是一行多列（3）行子查询：多列一行（多列多行） 【这三种都是where子查询】
-- （4）表子查询：多行多列（from 子查询）   【此处的几行几列是指内层查询结果】


-- 行子查询：查询年龄最大且身高最高的学生
-- select * from student where （age，height） = （select max（age），max（height） from student）；
-- 【此处的age 和height 组合在一起 构成行元素】


-- 表子查询：查询每一个班身高最高的
-- select * from （select * from student order by height desc）as stu group by class；
-- 此处的as 是因为from后面必须跟表名 ，而查询结果跟左为表需要有一个别名。


-- exists 子查询：查询班级满足【例如班级id为1】条件的学生；
--select * from student where ( exists(select* from class where id = 1));
-- exists 会返回满足条件的查询对象


-- 列子查询的实例： 已知班级名  查询在班学生
-- select * from student where c—id in（select class—ID from class）;   【内层查询结果通常有多个 ，需要时用in作为条件匹配，七十在mysql中海油其他条件：all ，some ， any】
-- any：和in含义相同 ，使用的时候要 用加等于号，例如：where ** = any（）【还可以加不等于号！=】，any和same在等于中相等，
-- all ：匹配内层查询结果的所有值【把所有值当做一个整体去匹配】






-- 视图：
-- 视图的创建： create view 视图名 as select语句
-- 例如： create view my_view1 as select * from my_student;（单表视图）
-- 例如： create view my_view2 as select s.*,c.* from my_student as s left join my_class as c on s.id = c.id;
-- 以上为多表视图，注意当多表视图的时候，基表字段名不能相同，如若相同用别名区分。【例如上图中select 后面就需要加s.列名和c.列名】


-- 查看视图：show tables[like]/desc 视图名字   show create table 视图名字
--  视图一旦被创建就会在数据库对应文件夹下创建一个对应的结构文件：frm文件


-- 视图的执行：select * from 视图名字; 其实本质就是执行封装的select语句


-- 修改视图：视图本身是不可以修改的，但是可以修改视图来源：Alter view 视图名字 as 新的select语句;
-- 删除视图：drop view 视图名字
-- 在视图中增加数据：1 多表视图不能插入数据 2 单表视图：视图中包含的字段必须有基表中所有不能为空（或者没有默认值）的字段
-- 单表视图添加数据，可以插入到基表中：insert into 视图名 value （各列值）
-- 删除数据：多表视图不能删除，单表可以。 delect from 表名 where 删除条件; 基表中数据也会删除


-- 视图数据更新：update 视图名 set 列名 = 数值 where 修改条件；
-- with check option ：数据跟新时候的限制。
-- 例如： 创建一个年龄大于三十岁的视图：
-- create view my_view as 
-- select * from student where age > 30 with check option;  此视图修改数据的时候不能将年龄修改至30岁以下
-- 创建视图的时候会自动选择复合条件的信息。可以修改基表中原本不符合条件的数值为复合条件的： update 表名 set age = 数值 where id = 数值;  但是无效果 因为修改表中数据，而表中无不符合条件的数据




-- 数据库的备份：
-- 备份引擎：inndb：只有表结构，数据全部存储在ibdatal文件中；
-- mysiam:表，数据，索引单独存储。


-- 例如：创建数据库引擎为myisam的数据表:
-- create table my_mysiam(id int )charset utf8 engine= mysiam; 在数据库中会产生三个文件.frm(结构)、.MYD（数据）、.MYI（索引）
-- mysiam数据库引擎的备份直接将上述三个文件放到想执行的数据库中即可。


-- 单表数据备份：每次只能备份一张表，只能备份数据（表结构不能备份）。
-- 备份：从表中选出一部分数据保存到外部的文件中：selectt */列名 into outfile '文件所在的路径' from 数据源;
-- 前提是外部文件不存在。


-- 高级备份：自己制定字段和行的处理方式
-- selectt */列名 into outfile '文件所在的路径' fields 字段处理 lines 行处理 hfrom 数据源 ;
-- fileds :字段处理 1 Enclosed by ：字段使用什么内容包裹，默认是空字符串。 2Terminated by：字段是以什么结束，默认是"/t",tab键
--  3 Escaped by ：特殊符号用什么方式处理，默认是"//",使用反斜杠转义，


-- lines：行处理 1 Starting by 每行以什么开始，默认是空字符串 2 Terminated by ：每行以什么结束，默认是换行符
-- 例如：select * into outfile 'D:/server/test/test1.txt' 
--  (字段处理) fields enclosed  by '"' -- 用双引号包裹字段。 from my_class;


-- 数据的还原：将一个在外部保存的数据，重新恢复到表中（如果表结构不存在，则无法恢复）
-- Load data infile 文件所在路径 into table 表名 [(字段列表)] fields 字段处理 lines 行处理;
-- 数据表如何备份的，就如何还原。


-- sql备份：（单表备份）mysqldump.exe-hPup 数据库名字 [数据表名字1][数据表名字2]..>外部文件目录（建议使用.sql文件）
-- （整库备份） mysqldump.exe-hPup 数据库名字>外部文件目录（建议使用.sql文件）


-- sql 备份的数据还原：Mysql.exe/mysql-hPup 数据库名字 < 备份文件目录 （不需要分号）【此时需要使用mysql客户端还原】
-- 使用sql 指令还原：Source 备份文件所在路径。


-- 增量备份：针对mysql服务器日志文件进行备份