My SQL数据库

一、CMD操作（数据库可视化工具也可操作）

1、创建数据库

	-- 格式:
	create database 数据库名;
    create database 数据库名 character set 字符集;

2、 查看服务器中的所有的数据库:

 show databases;

3、删除数据库（谨慎操作）

  drop database 数据库名称;

4、查看某个数据库的定义的信息:

 show create database 数据库名;

5、切换数据库：

 use 数据库名;

6、创建表：

	-- 格式：
      create table 表名(
      // 字段名 类型(长度) 约束，
      id int primary key,  //创建主键
      alter table 表名 drop primary key;  //删除主键
      )

  主键自动增长：一般主键是自增长的字段 ，不需要指定。实现添加自增长语句, 主键字段后加  auto_increment(只适用MySQL)

表操作：

	# 查看数据库中的所有表：
      -- 格式：
      show tables;
    # 查看表结构：
      -- 格式：
      desc 表名;            
    # 删除表 ：
      -- 格式：
       drop table 表名;

7、 修改表结构：

	alter table 表名 add 列名 类型(长度) 约束；//作用：修改表添加列.
    alter table 表名 modify 列名 类型(长度) 约束;  //作用：修改表修改列的类型长度及约束
    alter table 表名 change 旧列名 新列名 类型(长度) 约束;  //作用：修改表修改列名.
    alter table 表名 drop 列名; 	//作用：修改表删除列.
    rename table 表名 to 新表名;  // 作用：修改表名
    alter table 表名 character set 字符集;  //作用：修改表的字符集

8、插入表记录：

 	 insert into 表 (列名1,列名2,列名3..) values  (值1,值2,值3..); -- 向表中插入某些列
     insert into 表 values (值1,值2,值3..); --向表中插入所有列

   注意：
            *插入的数据应与字段的数据类型相同
            *数据的大小应该在列的长度范围内
            *在values中列出的数据位置必须与被加入列的排列位置相对应。
            *除了数值类型外，其它的字段类型的值必须使用引号引起。
            *如果要插入空值，可以不写字段，或者插入 null。	
            *对于自动增长的列在操作时，直接插入 null值即可。

9、更新表记录：

	-- 语法：
		update 表名 set 字段名=值,字段名=值;
        update 表名 set 字段名=值,字段名=值 where 条件;
        -- 值如果是字符串或者日期需要加；

 注意：
        *列名的类型与修改的值要一致；
        *修改值得时候不能超过最大长度；

10、删除表记录：

   	-- 语法：
           delete from 表名 [where 条件];

面试题：
        删除表中所有记录使用delete from表名; 还是用truncate table 表名;
        删除方式：delete 一条一条删除，不清空auto_increment记录数；
        truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始；

DOS操作数据乱码解决方式：

在图中与客户端有关的编码设置
        *client connetion result 和客户端相关；
        *database server system 和服务器端相关 ；
        *将客户端编码修改为gbk；
        *set character_set_results=gbk; / set names gbk；
        
        以上操作，只针对当前窗口有效果，如果关闭了服务器便失效。如果想要永久修改，通过以下方式:
        * 在mysql安装目录下有my.ini文件
        * default-character-set=gbk 客户端编码设置				                  *
        *character-set-server=utf8 服务器端编码设置
       
        *注意:修改完成配置文件，重启服务

二、SQL-CURD（更新20220617）

1、运算符

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

2、逻辑运算符

not 运算符：非 ！
and 运算符：并且 &&
or  运算符：或者 ||
例：
	--判断
	typeid is not null
	typeid is null 
	---赋值
	typeid=null    

3、检索~根据条件进行查询

select列名1,列名2，列名3...(*:所有列)
from 表名 
where 
--条件
age>11 and sex='男'
orderby 列名1，列名2
limit 5；--查询几条，限制条数

4、排序

--根据年龄倒叙排序desc倒叙asc升序默认是升序
select * from studentinfo
--如果第一列数值一样的情况下才会触发第二列的排序
order by age desc,Birthday asc;
--limit 2,5 从第2开始获取5条
--limit 5 获取5条
select * from studentinfo
order by age desc,Birthday asc
limit 10;

5、Distinct去重

--distinct排重
--distinct是对结果集排重，如果单表查询中有主键，百分之百不重复，这个写没有意义
select DISTINCT * from studentinfo;
select DISTINCT gender,age from studentinfo;

6、模糊检索

1）like模糊查询

运算符	说明
%	代表任意长度字符串
-	代表一个字符
escape	转译字符（注意：ESCAPE‘/’含义：/之后的_不作为通配）

2）正则表达式

运算符	说明
“.”	匹配任意单个的字符
“[]”	匹配在[]内的任意一个字符
“[^]”	匹配不在[]内的任意一个字符
“*”	匹配零个或多个在它前面的字符
“^”	匹配文本的开始字符
“$”	匹配文本的结尾字符
“+”	匹配+前面的字符1次或多次
“{n}”	匹配前面的字符至少n次

--模糊匹配like和regexp/1[a-z]2/1--like类似正则，一套规则，但是规则很简单
--_代表一个字符%任意字符
select * from studentinfo where student name like '王%';
select * from studentinfo where student name like '%处%';
select* from studentinfo where student name like '王_';
--身份证号以410和412开头
select * froms tudentinfo where studentno REGEXP '^[410|412]';

7、分组查询

1）聚合函数

获取某些列的最大值、最小值、平均值等统计分析信息，有时候还需要计算出究竟查询到多少行数据项。分别有：sum、count（注意非空值）——计数，结果集有几行、max、min、avg

--根据性别分组
--count计数：求所有非空(空指的是count中书写的列的内容全部为空)条数的
select gender,count(*),count(age),max(age),min(age),avg(age),
--多行转一列
GROUP_CONCAT(studentname)
from studentinfo
group by gender;
--如果没有groupby所有数据算是一组进行聚合
select count(*) from studentinfo;

2）分组Groupby

--计算各个城市的人数
selectcity,count(*) from studentinfo group by city;
--计算各个城市的男生女生人数
select city,gender,count(*) from studentinfo group by city,gender;

3）Having子句

Having目的：对分组后的数据做过滤

--年龄>15的班级人数大于5的班级是？
--年龄>15是分组钱的过滤：where，需要在groupby前
--人数>5是分组后的过滤：having，需要书写在groupby后边
select classid,count(*) from studentinfo
where age>15
group by classid
having count(*)>5;

8、关联查询

内连接：
	根据关联条件，左表和右表数据都是完整的，这条记录就会显示。
外连接：
	保证至少一张表的完整性
左外连接：
	保证左表完整
右外连接：
	右表完整
全外连接：
	保证两张表的完整性
交叉连接：
	笛卡尔乘积(交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行再一一组合，相当于两个表“相乘”)**

1）内连接（等值连接）

select列名from表名
	[inner]join表名 --inner 可省略
on 关联条件
	**[inner]join表名
on 关联条件

2）外连接

--左外
select studentid,studentname,studentinfo.classid,classname
from studentinfo
left join classon studentinfo.classid = class.classid;

--右外
select studentid,studentname,studentinfo.classid,classname
from studentinfo
right join classon studentinfo.classid = class.classid;

--全外
select studentid,studentname,studentinfo.classid,classname
from studentinfo
left join classon studentinfo.classid = class.classid
union   --合并结果集自动排重
select studentid,studentname,studentinfo.classid,classname
from studentinfo
right join classon studentinfo.classid = class.classid;

3）交叉连接（笛卡尔乘积）

-- 建表举例
CREATE TABLE `daka`(
`dakaiid` int NOT NULL,
`studentid` int NULL DEFAULT NULL,
`riqi` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARYKEY(`dakaiid`) USINGBTREE) ENGINE = InnoDB CHARACTER SET = utf COLLATE=utf8_general_ci 
ROW_FORMAT=Dynamic;
-- ----------------------------
--Recordsofdaka
-- ----------------------------
INSERT INTO `daka` VALUES(1,2011001002,'2011-01-01');
INSERT INTO `daka` VALUES(2,2011001002,'2011-01-02');
INSERT INTO `daka` VALUES(3,2011001003,'2011-01-01');
CREATE TABLE `riqi`(
`riqi` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- --------------------------
-- Recordsofriqi
-- ----------------------------
INSERT INTO `riqi` VALUES('2011-01-01');
INSERT INTO `riqi` VALUES('2011-01-02');
INSERT INTO `riqi` VALUES('2011-01-03')

-- 查询
select StudentName,riqi.riqi,daka.studentid
from studentinfo
cross join riqi
left join daka on studentinfo.StudentID = daka.studentid
and daka.riqi = riqi.riqi;

4）一种特殊的连接：自连接

--自连接：关联查询（关联的是自己，自己关联自己）
select 自己.stuname,组长.stuname
	fromtab_stu 自己 
left join 
	tab_stu 组长
on 自己.zuzhang = 组长.stuid;

9、子查询

1. 子查询结果为多行一列(可以当作多个值来用(1,2,3,4))

-- 查询存在有85分以上成绩的课程名字
	-- 先查85分以上成绩的课程cno
	SELECT DISTINCT cno from scores where degree > 85;
	-- 根据cno查 cname
	SELECT cs.cno,cs.cname from courses cs where cs.cno in (SELECT DISTINCT cno from scores where degree > 85);

2. 子查询结果为一个值。（可以作为字段使用）

-- 查询成绩表中分数最高的同学的记录
SELECT * from scores where degree = (SELECT max(degree) from scores);

3. all子查询

① all可以与=、>、>=、<、<=、<>结合是来使⽤，分别表示等于、⼤于、⼤于等于、 ⼩于、⼩于等于、不等于其中的所有数据。当所有数据都满足才是true，会返回满足所有条件的数据。
② in关键字用于where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足条件的记录。
③ 只要大于其中的所有值才会被显示。

-- 查询选修编号为3-105且成绩高于选修编号为3-245的课程同学的cno,sno,degree
/*
分析：
 第一步：我需要查出编号为3-105的所有的成绩
 第二步：查出课程为3-245的最高成绩
总结：3-105的成绩 要高于 3-245的成绩最大值
*/
-- 先查3-245的最大成绩
SELECT max(degree) from scores where cno='3-245';

SELECT * from scores 
where cno='3-105' 
and degree > (SELECT max(degree) from scores where cno='3-245');

-- 方法二：
-- 查出课程为3-245的所有成绩
SELECT degree from scores where cno='3-245';

SELECT * from scores WHERE cno='3-105' and degree > all(
  SELECT degree from scores where cno='3-245'
);

4. any/some子查询

any 可以与=、>、>=、<、<=、<>结合起来使⽤，分别表示等于、⼤于、⼤于等于、⼩于、⼩于等于、不等于其中的任何⼀个数据。也就是说只要有任意一个满足就是true。并且显示满足条件的数据。

5. 使⽤exists 和 not exists ⼦查询

where exist （⼦查询）如果该⼦查询有结果数据（⽆论什么数据，只要⼤于等于1⾏），则就为true，否则就为false
如果内层select返回true则外层select可以返回值，否则就返回空。

-- 查询全部的学生，如果子条件满足的情况下
-- 如果存在年龄大于40的学生，那么就展示全部的学生
SELECT * from student WHERE EXISTS (SELECT * from class where classid=4);

-- 查询全部的学生，如果子条件不满足的情况下
-- 如果存在年龄大于20的学生，那么就不展示全部的学生
SELECT * from student WHERE NOT EXISTS (SELECT * from student where age>20);