MySQL 데이터베이스 명령의 폭발적인 수집(요약 공유)

1.使用select查询
select 列名/表达式/函数/常量 from 表名 where 查询条件 order by 排序的列名asc/desc;
(1)查询所有的数据行和列：
select * from 表名；
(2)查询部分行和列：
select 列名… from 表名 where 查询条件；
(3)在查询中使用列的别名：
select 列名 AS 新列名 form 表名 where 查询条件；
计算，合并得到新的列名：
select 列名1+’.’+列名2 AS 新列名 from 表名；
(4)查询空值：
通过is null 或者 is not null 判断列值是否为空

查询student表中Email为空的学生姓名：

SELECT `name` FROM student WHERE Email IS NULL;

2.分组查询

#查询不同课程的平均分，最低分，最高分,并查询出平均分大于80分的课程
SELECT r.subjectno,sub.`SubjectName` 课程名称,AVG(StudentResult) 平均分,
MAX(StudentResult) 最高分,MIN(StudentResult) 最低分
FROM result r INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo` 
GROUP BY r.subjectno
#where AVG(StudentResult)>=80出现错误，
#分组查询group by 在where语句后，
#group by 约束条件使用having语句
HAVING AVG(StudentResult)>=80;

常用函数

1.聚合函数：
(1）AVG (平均值):select avg(列名)from 表名
假设列名为成绩 则查询到的是表中所有成绩的平均值。
（2）count 返回某字段的行数
（3）max 返回某字段的最大数
（4）min 返回某字段的最小值
（5）sum 返回某字段的和。
2.字符串函数：
（1）concat() 连接字符串s1,s2…sn为一个完整的字符串。
（2）insert(s1,p1,n,news)将字符串s1从p1位置开始，n个字符长的字串替换为字符串news。
（3）lower(s)将字符串s中的所有字符改为小写。
（4）upper(s)将字符串s中的所有字符改为大写。
（5）substring(s,num,len)返回字符串s的第num个位置开始长度为len的子字符串。
3.时间日期函数：
（1）获取当前日期：curdate();
（2）获取当前时间：curtime();
（3）获取当前日期和时间：now();
（4）返回日期date为一年中的第几周：week(date);
（5）返回日期date的年份：year(date);
（6）返回时间time的小时值：hour(time);
（7）返回时间time的分钟值：minute(time);
（8）返回日期参数（date1和date2之间相隔的天数）:datediff(date1,date2);
（9）计算日期参数date加上n天后的日期：adddate(date,n);
4.数学函数
（1）返回大于或等于数值x的最小整数：ceil(x);
（2）返回小于或等于数值x的最大整数：floor(x)；
（3）返回0~1之间的随机数：rand();
order by 子句
order by子句按照一定的顺序排列查询结果，asc升序排列，desc降序排列。
limit子句
显示指定位置指定行数的记录。
select 字段名列表 form 表名 where 约束条件 group by分组的字段名 order by 排序列名 limit 位置偏移量,行数;
#查询学生信息里gid=1按学号升序排列前四条记录

#查询学生信息里gid=1按学号升序排列前四条记录（步长）
SELECT id,`name` FROM `student1` WHERE gid=1 ORDER BY id LIMIT 4;
（查询表里全部信息中gid=1的前四个学生）

查询结果：

#查询学生信息里gid=1按学号升序排列前四条记录（位置偏移量，步长）
SELECT id,`name` FROM `student1` WHERE gid=1 ORDER BY id LIMIT 4,4;
（查询表中全部信息gid=1前四条以后的全部信息中的前四条学生信息）

查询结果：

模糊查询

in子查询******not in 子查询
使用in关键字可以使父查询匹配子查询返回的多个单字段值。
解决使用比较运算符（=,>等），子查询返回值不唯一错误信息。
like模糊查询
LIKE语句语法格式：select * from 表名 where 字段名 like 对应值（子串）。

它主要是针对字符型字段的，它的作用是在一个字符型字段列中检索包含对应子串的。

A:% 包含零个或多个字符的任意字符串： 1、LIKE’Mc%’ 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
　　2、LIKE’%inger’ 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
　　3、LIKE’%en%’ 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
　　B:_（下划线） 任何单个字符：LIKE’_heryl’ 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。
　　C：[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符：、
　　1，LIKE’[CK]ars[eo]n’ 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
　　2、LIKE’[M-Z]inger’ 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）
　　***D：[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符：LIKE’M[^c]%’ 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。
　　E： 它同于DOS命令中的通配符，代表多个字符：cc代表cc,cBc,cbc,cabdfec等多个字符。
　　F：？同于DOS命令中的？通配符，代表单个字符 :b?b代表brb,bFb等
　　G：# 大致同上，不同的是代只能代表单个数字。k#k代表k1k,k8k,k0k 。
　　F：[!] 排除 它只代表单个字符
　　下面我们来举例说明一下：
　　例1，查询name字段中包含有“明”字的。
　　select * from table1 where name like ‘%明%’
　　例2，查询name字段中以“李”字开头。
　　select * from table1 where name like '李’
　　例3，查询name字段中含有数字的。
　　select * from table1 where name like ‘%[0-9]%’
　　例4，查询name字段中含有小写字母的。
　　select * from table1 where name like ‘%[a-z]%’
　　例5，查询name字段中不含有数字的。
　　select * from table1 where name like ‘%[!0-9]%’
　　可以自定义转移符----》escape’自定义转移符’
　　distinct------》去除重复项
　　
　　between*and模糊查询
　操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
　null ，not null查询

-- 查询手机号不为null的用户数据
SELECT * from user where phone is not null;
 
-- 查询手机号为null的用户数据
SELECT * from user where phone is null;

exists 子查询 not exists子查询
exists子查询用来确认后边的查询是否继续进行
drop table if exists test—>判断是否存在表test，如果存在就删除。
not exists实现取反操作。对不存在对应查询条件的记录。

多表连接查询

多表连接查询是通过各个表之间共同列的关联性来查询数据。
1.内连接查询
内连接查询根据表中共同的列进行匹配。取两个的表的交集。两个表存在主外键关系是通常使用内连接查询。
内连接使用inner join…on 关键字或者where子句来进行表之间的关联。
inner 可省略 on 用来设置条件。
（1）在where子句中指定连接条件
（2）在from中使用inner join…on关键字

#查询学生姓名和成绩
SELECT studentname,studentresult FROM student s,result r
WHERE s.`StudentNo`=r.`StudentNo`

#在from中使用inner join....on关键字
SELECT s.`StudentName`,r.`StudentResult` ,r.`SubjectNo`FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`

两种方法查询结果相同。
2.外连接查询
外连接查询中参与连接的表有主从之分，已主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中，对不符合连接条件的列，将被填上null值再返回到结果集中。
（1）左外连接查询
left join…on 或者left outer join…on关键字进行表之间的关联。

SELECT s.`StudentName`,r.`StudentResult` ,r.`SubjectNo`FROM student s
LEFT JOIN result r ON s.`StudentNo`=r.`StudentNo`

将没有成绩的学生成绩查出。
（2）右外连接查询
右外连接包含右表中所有的匹配行，右表中有的项在左表中没有对应的项将以null值填充。
right join…on 或right outer join…on关键字进行表之间的关联。
（3）自连接
把一个表作为两个表使用。

#创建一个表
CREATE TABLE book(
id INT(10),
sort INT(10),
books VARCHAR(10) NOT NULL
);
#插入数据
INSERT INTO book VALUES (2,1,'古文书'),
(3,1,'现代书'),
(4,2,'《三字经》'),
(5,2,'《唐诗三百首》'),
(6,3,'《我与地坛》'),
(7,2,'《游大林寺》'),
(8,2,'《王右军年减十岁时》'),
(9,3,'《致橡树》');

#查询结果为：
#书籍类型       书籍名
#古文书         三字经....
#现代书         我与地坛....

SELECT a.books 书籍类型, b.books 书籍名  
FROM book a,book b
WHERE a.id=b.sort;

自连接查询结果：

MySQL 트랜잭션, 뷰, 인덱스, 백업 및 복구

1. 트랜잭션
트랜잭션은 통합 관리를 위해 일련의 데이터 작업을 전체로 묶는 것을 의미합니다.
모든 명령 전체와 함께 시스템에 생성 요청을 제출하거나 취소합니다.
트랜잭션 속성: 원자성, 일관성, 격리성, 내구성.
myISA 스토리지 엔진은 트랜잭션을 지원하지 않습니다.

자동 트랜잭션 제출 끄기: set autocommit=0;
(1) 트랜잭션 시작: 트랜잭션 시작/
(2) 트랜잭션 제출: 커밋
(3) 트랜잭션 롤백/실행 취소: 롤백; set autocommit=1;
결과를 다음으로 설정하시겠습니까? ? 인코딩 형식 표시: 이름을 설정하시겠습니까? ? ;

2. 보기 보기는 데이터베이스에 있는 하나 이상의 테이블에 있는 데이터를 보는 방법입니다. 뷰는 하나 이상의 테이블에서 행 또는 열의 하위 집합으로 생성된 가상 테이블입니다. 뷰는 쿼리에서 테이블 필터 역할을 합니다.
(1) 뷰 생성: 으로 뷰 뷰 이름 생성
(2) 뷰 삭제: 뷰 뷰 이름 삭제
(3) 뷰 데이터 보기: 선택. . . . .

3. 색인 색인은 책의 목차와 유사합니다. 색인을 사용하면 데이터베이스 프로그램이 전체 테이블을 스캔하지 않고도 필요한 데이터를 찾을 수 있습니다.
(1) 일반 데이터: 중복 및 null 값이 허용됩니다.
(2) 고유 인덱스 : 중복이 허용되지 않습니다. 고유 인덱스는 여러 개 있을 수 있습니다.
(3) 기본 키 인덱스: 비어 있지 않고 고유합니다. 삭제 시 기본 키를 삭제합니다.
(4) 복합 인덱스: 여러 열을 인덱스로 결합합니다. ?
(5) 전체 텍스트 인덱스: char, varchar, text로 생성된 반복 가능 및 null 값.

where match (column name) against ('find content') (6) 공간 인덱스: 공간 데이터 유형의 열에 설정된 인덱스입니다.

인덱스 생성: 테이블 이름(인덱스를 생성할 컬럼)에 [인덱스 유형] 인덱스 인덱스 이름을 생성합니다.
또는 테이블 생성 시 컬럼 뒤에 인덱스 유형을 추가합니다.
또는 테이블 변경 테이블 이름 추가 인덱스 인덱스 이름(인덱스 열)
인덱스 보기: 테이블 이름에서 인덱스 표시


1. mysqldump 명령을 사용하여 데이터베이스 백업 mysqldump -u -p 데이터베이스 이름 > 데이터베이스 위치 및 이름 백업
테이블 데이터를 텍스트 파일로 내보내기
쿼리 조건이 있는 테이블 이름에서 데이터베이스 위치 및 이름 백업 선택 ;
2. mysql 명령을 사용하여 데이터베이스 복원
(먼저 새 데이터베이스 생성)