Mysql数据库基础大全

关系型数据库:表
非关系型数据库:不支持连接查询，它的数据是独立设置的
反引号区分Mysql保留字的
列–>字段
decimol(13,2) ：浮点数字长度13位小数点后面有两位
AUTO_INCREMENT：自动增长的，每添加一条数据，自动在上一个记录上加1
通常用于设置主键(唯一区分)，且为整数类型
可定义起始值和步长
UNSIGNED：无符号的
声明该数据列不允许负数
NULL和NOT NULL:默认为NULL，即没有插入该列的数值
如果设置为NOT NULL,则该列必须有值
DEFAULT:默认的
用于设置默认值

#查看数据库的定义
SHOW CREATE DATABASE school
#查看数据表的定义
SHOW CREATE TABLE student
#显示表结构
DESC student
AUTO_INCREMENT：改变自增列的初始值

给表加注释COMMENT=‘测试表’;
COMMENT’编码号’
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3
引擎InnoDB（表类型） 自增从几开始 默认字符集utf8mb3
MyISAM：节约空间及相应速度
InnoDB：安全性，事务处理及多用户操作数据表

涉库字符集:CHARACTER SET utf8
涉表字符集:CHARSET=utf8
————————————
#重命名表
ALTER TABLE tab1 RENAME AS table1
#为表添加列
ALTER TABLE table1 ADD address VARCHAR(100) DEFAULT’中国北京’
#修改表
ALTER TABLE table1 MODIFY address VARCHAR(500) DEFAULT’中国北京朝阳区’
#修改名
ALTER TABLE table1 CHANGE address 地址 VARCHAR(100) DEFAULT’中国北京朝阳区’
#删除字段
ALTER TABLE tab4 DROP name
CREATE TABLE tab4(
id INT(4) ZEROFILL NOT NULL ,
NAME VARCHAR(50)
)ENGINE=MYISAM
#删除表
DROP TABLE IF EXISTS tab4

CONSTRAINT FK_gradeid FOREIGN KEY(gradeid) REFERENCES grade(gradeid)—外键
#创建外键方式一：创建子表的同时创建外键
#创建外键方式二：创建子表完毕后，修改子表添加外键
ALTER TABLE student
ADD CONSTRAINT FK_gradeid FOREIGN KEY(gradeid) REFERENCES grade(gradeid)
#删除具有主外键关系的表时，要先删子表，后删主表
#删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
ALTER TABLE student DROP INDEX FK_gradeid; 外键会创建同名索引，也删除

NOW()：代表当前系统时间
添加

#使用语句增加数据
INSERT INTO grade1(grade1name)VALUES(‘大一’);
INSERT INTO grade1(grade1name)VALUES(‘大二’),(‘大三’),(‘大四’);
INSERT INTO student(studentno,studentname,sex,gradeid,phone,address,borndate,email,identityCard)
VALUES(）
CONCAT()连接字符串

#使用语句修改数据
#将李四的地址修改为中国南京
#同时修改多列
UPDATE student SET address =‘中国南京’,email=‘lisi@163.com’
WHERE studentno =1001
#条件可以使用运算符
UPDATE student SET sex=2
WHERE studentno=1001 OR studentno=1002
#WHERE studentno>=1000 AND studentno<=1002
#WHERE studentno BETWEEN 1000 AND 1002
#使用函数
UPDATE student SET studentname=CONCAT(“姓名:”,studentname)

#删除数据
DELETE FROM grade WHERE gradeid=8

#删除表全部数据(不带where条件的delete)
#自增当前值依然从原来的基础上进行
DELETE FROM test;

#删除表全部数据(truncate)
#自增值恢复到初始值重新开始
TRUNCATE TABLE test;

#id–>6
#重启数据据库服务后，tab1：1 2 3；tab2：7 8 9
#同样使用delete from清空表数据，对于innodb的表，自增列从初始值重新开始
#而MYISAM类型的表，自增列依然从上一个自增数据基础上开始

#启动 net start mysql ；停止服务 netstop MySQL
#查询用查询所有列，效率低
#查询指定列（不区分大小写）
SELECT studentno,studentname FROM student;
#为列取别名（as,也可以省略）
SELECT studentno AS 学号,studentname AS 姓名 FROM student;
#使用as也可以为表取别名(也可以省略as)
SELECT studentno AS 学号,studentname AS 姓名 FROM student （AS）s;
#查看哪些同学参加了考试（学号）–去除重复项(distinct,默认all)
SELECT DISTINCT studentno FROM result;
#SELECT查询中可以使用表达式
SELECT @@auto_increment_increment
SELECT VERSION();#mysql版本号
SELECT 1003-1 AS 计算结果;#表达式
#学院考试成绩集体提升1分
SELECT *FROM result;
SELECT studentno,studentresult+1 AS ‘提分后’ FROM result;

#满足条件的查询(where)考试成绩95-100
SELECT studentno ,studentresult
FROM result
WHERE studentresult>=95 AND studentresult<=100;
#模糊查询
SELECT studentno,studentresult
FROM result
WHERE studentresult BETWEEN 95 AND 100;
#精确查询
SELECT studentno,studentresult
FROM result
WHERE studentno=1000;
#或者写成&&
SELECT studentno,studentresult
FROM result
WHERE studentresult >= 95 && studentresult<= 100;

---

#除了1000号同学，我要其他同学的考试成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

#模糊查询 between and\like\in\null
#查询姓李的同学的学号及姓名
#like结合使用的通配符：%(0到任意个字符)(一个字符)
SELECT studentno,studentname
FROM student
WHERE studentname LIKE ‘李%’;#一个字的’李’两个字’李__’

#姓名中含有“文”字的同学
SELECT studentno,studentname
FROM student
WHERE studentname LIKE ‘%文%’;

#查询学员姓名中有“%”这个字的同学学号、姓名 转义符
SELECT studentno,studentname FROM student
WHERE studentname LIKE ‘%%%’
#查询学员姓名中有“_”这个字的同学学号、姓名 转义符
SELECT studentno,studentname FROM student
WHERE studentname LIKE ‘%_%’

#转义符\能不能使用自己的转义符？（:）
SELECT studentno,studentname FROM student
WHERE studentname LIKE ‘%:%%’ ESCAPE’:‘; #使用自己的转义符ESCAPE’:’

#模糊查询 ：in null
SELECT studentno,studentname
FROM student
WHERE studentno IN (1000,1001,1002,1003);

#查询出生日期没有填写的同学 =NULL 是错误的 和null比较必须写is NULL
SELECT studentname FROM student
WHERE borndate IS NULL
#查询出生日期填写的同学
SELECT studentname FROM student
WHERE borndate IS NOT NULL*******

#区别空字符串与null
SELECT FROM student;
#查询家庭住址没有写的同学
SELECT studentname FROM student
WHERE address=‘’ OR address IS NULL;******

---------------连接查询
内连接查询原理：把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户，拿两个表中共有的记录
#连接查询
#内连接 inner join 查询两个表中的结果集中的交集
#外连接 outer join
#左外连接 LEFT JOIN
以左表作为基准，右边表来一一匹配，匹配不上的，返回左表的记录，右表以null填充
#右外连接 right join
以右表作为基准，左边表来一一匹配，匹配不上的，返回右表的记录，左表以null填充
#只写JOIN 默认就是INNER JOIN
#自连接
#等值连接 非等值连接
#先写连接查询再写where
#非等值连接 两个表里所有的记录一一匹配都返回，返回记录的行数是左边表里的行数乘右边表里的行数

#查询参加了考试的同学信息（学号、学生姓名、科目编号、分数）
SELECTFROM student;
SELECTFROM result;
#思路：（1）分析需求，确定查询的列来源于两个表 student result，连接查询
#（2）确定使用哪一种连接查询？—内连接
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
INNER JOIN result r
#上面代表把它俩内连起来了 ON后面写内连接的条件 on后面就是我们加的条件
ON r.StudentNo=s.StudentNo #两表连接的查询

case when 在以下情况下
then 1 else 2 end 则 1 否则 2 结束

---

#三连接查询
#查询参加过考试的同学信息(学号 姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s #第一个表
INNER JOIN result r #第二个表 桥梁
ON s.StudentNo =r.StudentNo
INNER JOIN subject sub #第三个表
ON r.SubjectNo = sub.subjectno

-------------自连接
自连接表要取别名
---------------select 语法
#查询《数据库结构-1》的所有考试结果(学号 学生 科目名称 成绩)
WHERE subjectname LIKE ‘%数据库结构-1%’
#WHERE subjectname =‘%数据库结构-1%’
#成绩排序 默认升序 升序ASC 降序desc
ORDER BY studentresult DESC,studentno DESC#先按成绩降序排如果一样再按学号降序排

#常见错误：ORDER BY studentresult DESC,studentno DESC 成绩升序 学号降序

#分页必须必会用到----(用户体验、网络传输、查询压力)
#查询《数据库结构-1》的所有考试结果(学号 学生 科目名称 成绩)
#每页显示5条记录出来
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s #第一个表
INNER JOIN result r #第二个表 桥梁
ON s.StudentNo =r.StudentNo
INNER JOIN subject sub #第三个表
ON r.SubjectNo = sub.subjectno
#查询《数据库结构-1》的所有考试结果(学号 学生 科目名称 成绩)
WHERE subjectname LIKE ‘%数据库结构-1%’
#WHERE subjectname =‘%数据库结构-1%’
#成绩排序 默认升序 升序ASC 降序desc
ORDER BY studentresult DESC
#分页limit 从哪条记录开始（起始行）第几页(pageno-1)pagesize 要显示几条 每页显示多少行记录pagesize
#LIMIT 0,5; 第一页 等同于 LIMIT 5 offset 0
#LIMIT 5,5 #第二页
#LIMIT 10,5 #第三页
#LIMIT 15,5 #第四页
#LIMIT (pageno-1)pagesize,pagesize (当前页码-1)页容量 页容量
#数据库不同用的分页语句不同
------------------子查询
在查询语句中的where条件子句中，又嵌套了另外一个查询语句
SELECT 1 from 2 where 1（从 2 中选择 1，其中 1）************

#查询《数据库结构-1》的所有考试结果(学号 科目编号 成绩),并按成绩降序排序列
#方法一:使用连接查询
SELECT studentno,r.subjectno,studentresult
FROM result r INNER JOIN subject sub
ON r.SubjectNo=sub.SubjectNo
WHERE subjectname=‘数据库结构-1’
ORDER BY StudentResult DESC;
#方法二:使用子查询,子查询执行顺序：由里到外
#外面select父查询 里面的select子查询
SELECT studentno,subjectno,studentresult
FROM result WHERE subjectno=(SELECT SubjectNo FROM subject WHERE subjectname=‘数据库结构-1’)
ORDER BY StudentResult DESC;

#方法二:使用子查询(将需求拆分、细化，逐步完善sql语句)
#分数不小于80分的学生的学号和姓名
SELECT r.StudentNo,StudentName
FROM student s INNER JOIN result r
ON s.StudentNo=r.StudentNo
WHERE studentresult>=80
#在上面sql基础上，添加需求:查询课程为《高等数学-2》
AND SubjectNo =(SELECT SubjectNo FROM subject WHERE SubjectName=‘高等数学-2’)

#方法三：查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
#分步写简单sql语句，然后将其嵌套起来
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN(
SELECT StudentNo FROM result WHERE studentresult>=80 AND subjectno=(
SELECT SubjectNo FROM subject WHERE SubjectName=‘高等数学-2’ ))

#查询《C语言-1》的前5名学生的成绩信息：学号、姓名、分数
SELECT s.studentno,studentname,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo=r.StudentNo
INNER JOIN subject sub
ON r.SubjectNo=sub.SubjectNo
WHERE SubjectName=‘C语言-1’
ORDER BY StudentResult DESC
LIMIT 0,5;

---------函数
#数学函数
SELECT ABS(-8);#绝对值
SELECT CEILING(9.8);#大于等于我的最小整数
SELECT FLOOR(9.3);#小于等于我的最大的整数
SELECT RAND(); #返回0-1之间的随机数
SELECT RAND(10); #以某个数作为种子，返回重复随机数
SELECT SIGN(0); #符号函数，正数1 负数-1 0返回0

#字符串函数
SELECT CHAR_LENGTH(‘好好学习数据库’);#返回字符串中包含的字符数
SELECT CONCAT('我 ',‘爱 ‘,’ 你 ‘);#合并字符串
SELECT INSERT (‘我爱你啊啊啊’,1,3,‘很爱’);#替换字符串，从某个位置开始，替换某个长度 如果起始位置
SELECT LOWER(’ I LOVE YOU’); #变小写
SELECT UPPER(‘i lOVE you’); #变大写
SELECT LEFT(‘课工场欢迎你’,3);
SELECT RIGHT(‘课工场欢迎你’,3);
SELECT REPLACE(‘课工场欢迎你,你好’,‘你’,‘你们’);#替换
SELECT SUBSTR(‘课工场欢迎你’,1,3); #截取，从哪个位置开始截取，截取多长
SELECT REVERSE(‘课工场欢迎你’); #反转

#日期和时间函数
SELECT CURRENT_DATE(); #获取当前日期
SELECT CURDATE();
#获取当前的日期和时间
SELECT NOW();
SELECT LOCALTIME();
SELECT SYSDATE();
#分别获取日期中的某个部分 年 月 日 时 分 秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

#系统信息函数
SELECT VERSION();
SELECT USER();

#查询姓李的同学，改成历
SELECT REPLACE(StudentName,‘李’,‘历’) AS 新名字
FROM student
WHERE StudentName LIKE’李%';
---------聚合函数
group by +sub:分组+哪列

#聚合函数
SELECT COUNT(StudentName) FROM student;
SELECT COUNT(*) FROM student; #效率不高 COunt非空值计数
SELECT COUNT(1) FROM student;

GROUP_CONCAT 以id分组，把price字段的值在同一行打印出来，逗号分隔(默认)
select id, group_concat(price) from goods group by id;

SELECT SUM(StudentResult) AS 总和 FROM result; #返回的结果只有一个值
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;

#查询不同课程的平均分，最高分，最低分
#前提:根据不同课程进行分组
SELECT subjectno,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result
GROUP BY subjectno;

GROUP BY r.subjectno
HAVING 平均分>80 #分组以后再去筛选

SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r INNER JOIN subject sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.subjectno
HAVING 平均分>80 #分组以后再去筛选
ORDER BY 平均分 DESC
LIMIT 0,4;

----------事务处理
MySQL事务处理只支持InnoDB和BDB数据表类型
事务ACID原则
原子性(Atomic):事务处理可能包括一组sql语句，当一个整体执行的，不能单独执行其中某一条，要么全部成功，要么全部失败
一致性(Consist):确保数据库能够正确改变数据以后，来成功提交事务。这些数据要么都是事务提交前的状态，要么全是事务提交后的状态
隔离性(Isolated):一个数据库里可能不只是一个数据处理，数据库里可能存在很多的事务处理，每一个事务之间是互不影响的，一个数据处理结果影响了其他事务，其他事务会被撤回，这样能保证每个事务之间是互不影响的，事务是隔离的，彼此独立，透明的。
持久性(Durable):事务成功提交以后，如果事务成功提交了，事务就会被永久修改并且保留，如果回滚了（撤销了）相当于没有操作过，事务结束以后必须重新启动一个新的事务才能去修改事务。事务一旦提交成功对事务的影响是持久的

---

SET autocommit=0 #关闭自动提交

START TRANSACTION; #开始提交
UPDATE account SET cash=cash-500 WHERE NAME=‘A’;
UPDATE account SET cash=cash+500 WHERE NAME=‘B’;
#COMMIT; #没有异常情况
ROLLBACK; #执行ROLLBACK回撤到提交之前
SET autocommit=1;
----------------索引
主键索引:非空唯一
唯一索引:可以有空，为了避免同一列里的值有重复
#添加索引方式一:创建表，声明列属性时添加上
CREATE TABLE test1(
id INT(4) PRIMARY KEY,
testno VARCHAR(10) UNIQUE,
c VARCHAR(50),
d VARCHAR(20),
e TEXT,
INDEX index_c(c,d),
FULLTEXT(e)
)ENGINE=myisam;

#添加索引方式二:创建表，将所有列都声明完毕后，再添加索引
CREATE TABLE test2(
id INT(4) ,
testno VARCHAR(10) ,
c VARCHAR(50),
d VARCHAR(20),
e TEXT,
PRIMARY KEY(id),
UNIQUE KEY(testno),#唯一索引
INDEX index_c(c,d),
FULLTEXT(e)
)ENGINE=myisam;

#添加索引方式三:先创建表，创建表完毕后，修改表添加索引
CREATE TABLE test3(
id INT(4) ,
testno VARCHAR(10) ,
c VARCHAR(50),
d VARCHAR(20),
e TEXT
)ENGINE=myisam;
ALTER TABLE test3 ADD PRIMARY KEY(id); #修改表添加索引
ALTER TABLE test3 ADD UNIQUE KEY(testno);
ALTER TABLE test3 ADD INDEX(c,d);#括弧前面是索引名字 里面是给哪一列加索引
ALTER TABLE test3 ADD FULLTEXT(e);

EXPLAIN SELECT *FROM student WHERE StudentNo=‘1000’; #解释这段依赖的索引是什么 分析这段sql语句执行性能

#把student 引擎innodb转换为myisam
SELECT *FROM student;
ALTER TABLE student ENGINE = myisam;
ALTER TABLE student ADD FULLTEXT(StudentName);

EXPLAIN SELECT *FROM student WHERE StudentName LIKE ‘李%’;

#MATCH使用sytudentname全文索引去查找love against (‘love’) MATCH against 固定语法
EXPLAIN SELECT *FROM student WHERE MATCH(StudentName) against (‘love’);

#删除索引
DROP INDEX testno ON test3;
ALTER TABLE test3 DROP INDEX c;
ALTER TABLE test3 DROP INDEX e;
ALTER TABLE test3 DROP PRIMARY KEY;

#显示索引信息
SHOW INDEX FROM student;
-----------备份
#将school数据库student表中的学号、姓名两列备份出去
USE school;
#备份出去的文件不可以提前存在
SELECT studentno,StudentName INTO OUTFILE ‘d:/MySQL备份/student.sql’ FROM student;

#需要将备份出去的数据恢复到test数据库的stutab表中来
USE test
CREATE TABLE stutab(
id INT(4),
sname VARCHAR(20)
)
#加载文件中的数据
LOAD DATA INFILE ‘d:/MySQL备份/student.sql’ INTO TABLE stutab(id,sname);
SELECT *FROM stutab;
-------------规范化数据库设计
第一范式：确保每列都是不可再分的最小数据单元
第二范式：确保每列都与主键相关
第三范式：确保表中各列必须和主键直接相关，不存在传递依赖
---------------SQL底层执行逻辑
sql92语法：
SELECT …,…(存在聚合函数)
FROM …,…,…
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY …,…
HAVING包含聚合函数的过滤条件
ORDER BY …,…(ASC,DESC)
LIMIT …, …