数据库：基于mysql总结,全部理论集合

最近在忙课程设计和期末考试！！！上线啦！！！此篇文章是博主自己学数据库的笔记，理论笔记在本子上，所以暂时没有理论笔记，都是sql笔记啦！要实践哦！！实践是.....对不起马克思背疯了！！

对于mac来说，打开终端然后输入此代码后输入密码即可登录：

source .bash_profile

mysql -uroot -p

创建数据表的相关应用：

1.显示数据库
SHOW DATABASES;

2.进入相应数据库,此处用的mysql
USE +数据库名称;
查询数据表,
SHOW TABLES;
3.数据表（注意逗号）
创建：
CREATE TABLE IF NOT EXISTS stu(
	stuNO  CHAR(5),
	stuName VARCHAR(20),
	stuGender TINYINT COMMENT’1:男，2:女’,
	stuTIME TIMESTAMP);
备注信息的单引号需要到终端去写
CREATE TABLE  stu(
	stuNO  CHAR(5),
	stuName VARCHAR(20),
	stuGender TINYINT  ,
	stuTIME TIMESTAMP);

创建指定字符集的数据表:
CREATE TABLE  stu(
	stuNO  CHAR(5),
	stuName VARCHAR(20),
	stuGender TINYINT  ,
	stuTIME TIMESTAMP)CHARSET utf8;
再用
SHOW CREATE TABLE stu;
验证是否成功

修改表名称：
ALTER TABLE stu RENAME TO student;
RENAME TABLE stu TO student;

4.查看表结构
SHOW COLUMNS FROM stu；或者DESC stu;

5.查看备注信息和表结构
SHOW  FULL COLUMNS FROM stu;

6.字段相关应用
添加：
ALTER TABLE stu ADD stuage TINYINT FIRST;     (首位
ALTER TABLE stu ADD stuage TINYINT AFTER stuName;  （指定位置
ALTER TABLE stu ADD stuage TINYINT ;（最后位
ALTER TABLE stu ADD stuage TINYINT ,ADD stuBirth DATE;     （ 添加多个在最后
ALTER TABLE stu ADD stuage TINYINT AFTER stuName ,ADD stuBirth DATE AFTER stuTIME;
(指定位置添加

删除：
ALTER TABLE stu DROP stuage;
删除多个：
ALTER TABLE stu DROP stuage,DROP stuBirth;

修改：

修改字段名：
ALTER TABLE stu CHANGE stuage stuAge TINYINT;

修改字段类型：
ALTER TABLE stu MODIFY stuAge INT;

修改字段位置（与新增相似，不同点在于关键字）：
ALTER TABLE stu MODIFY stuage TINYINT FIRST;     (首位
ALTER TABLE stu MODIFY stuage TINYINT AFTER stuName;  （指定位置
ALTER TABLE stu MODIFY stuage INT AFTER stuTIME ,MODIFY stuBirth DATE AFTER stuTIME;
(指定位置添加多个

修改字段字符(char类型）集：
ALTER TABLE stu MODIFY  stuName VARCHAR(8) CHARSET utf8;
ALTER TABLE stu MODIFY  stuName VARCHAR(8) CHARSET gbk;
SHOW CREATE TABLE stu;(验证是否修改成功）

3.表中数据操作

DML:增删数据表

查找数据表中添加的所有数据：
SELECT*FROM stu;


1.添加数据(单引号需要到终端修改）
 INSERT INTO  stu VALUES (1,'zs',18);
 INSERT INTO  stu VALUES (1,’qx’,20);
INSERT INTO  stu VALUES (3,’zy’,19);
INSERT INTO  stu VALUES (4,’zg’,22);
INSERT INTO  stu VALUES (5,’faf’,20);
INSERT INTO  stu VALUES (6,’ys’,25);

2.添加指定字段数据（避免交换顺序带来的不便）
INSERT INTO stu(stuName,stuage,stuNO) VALUES(‘zmy’,19,7),(‘xlh’,20,8);

3.SET(键值对的形式）
(不可以添加多个语句）eg：INSERT INTO Stu SET stuNO=9,stuName=‘myy’,stuage=18, stuNO=10,stuName=‘lyy’,stuage=19;错误

INSERT INTO Stu SET stuNO=9,stuName=‘myy’,stuage=18


4.修改数据（如果省略WHERE，则默认修改表中选择的相应字段的所有数据）
UPDATE stu SET stuNO=2 WHERE stuName=‘qx’;



5.删除数据（若不加WHERE则一行一行删掉所有数据）根据条件删除相应行

DELETE FROM stu WHERE stuName=‘ys’;
6没有了

6.彻底清空表中的数据：
TRUNCATE TABLE stu;
(请勿轻易尝试无法撤销）

7.查询相关操作：(按顺序排序关键字）SELECT FROM WHERE (GROUP BY) HAVING (ORDER BY) LIMIT

	查询去重复值：
	SELECT DISTINCT stuage FROM stu;

	SELECT DISTINCT stuage,stuName FROM stu;（同时满足多个条件才去除）

	查看数据：
		索引：创建索引，相对于赋值唯一id，在搜索时直接找对应id，大量数据查找时减少搜索时间，普通搜索是遍历，耗时
		查看索引：
			SHOW INDEX FROM stu;
		创建索引(大数据量适用）：
			1.创建表后修改：
				全文索引 ()里面是索引的名字，区别不同的索引
				ALTER TABLE stu ADD FULLTEXT INDEX stuNO(stuNO);
			2.创建表：
				CREATE…..(
					PRIMARY KEY(stuNO)主键索引
					UNIQUE KEY stuNO(stuNO)唯一索引
					KEY stuName(stuName)常规索引
				)
			
		查看前n条：
			SELECT stuage,stuName FROM stu LIMIT 5;
			SELECT stuage FROM stu LIMIT 5;

		从指定位置（索引位置）开始（不包括当前）的第n条：
			索引位置=排序n-1，因此第一行为0；
			简写格式LIMIT 索引，显示条数：
			SELECT stuage,stuName,stuNO FROM stu LIMIT 3,3;
			正常格式：
			SELECT stuage,stuName,stuNO FROM stu LIMIT 3 OFFSET 3;

		当前索引=（当前页码-1）*每页显示条数
			1	SELECT stuage,stuName,stuNO FROM stu LIMIT 0,3; （1-1）*3
			2	SELECT stuage,stuName,stuNO FROM stu LIMIT 3,3; （2-1）*3
			3	SELECT stuage,stuName,stuNO FROM stu LIMIT 6,3; （3-1）*3

			多个数据表查询数据时在字段名前加上表名
				eg:  stu.stuage

	对数据排序（默认为插入顺序）：
		从小到大：
		SELECT*FROM stu ORDER BY stuNO;		(stuNO后面省略了ASC）
		从大到小：
		SELECT*FROM stu ORDER BY stuNO DESC;	（必不可省）

		多个条件排序时，按序满足
		SELECT*FROM stu ORDER BY stuage,stuNO DESC;（年龄排序，如果年龄有重复的再按照学号降序调整）
	
		以中文规则排序：
			SELECT*FROM stu ORDER BY CONVERT(stuName USING gbk);

	过滤数据条件查询（WHERE）：
		逻辑比较：  =    >    <   >=   <=    （  <>   等价  !=    表示不等于）

		范围：	
			SELECT*FROM stu WHERE stuage BETWEEN 20 AND 25;				包括20和25	*通配符 SELECT*FROM显示表中所有内容

			SELECT stuage FROM stu WHERE stuage BETWEEN 20 AND 25;
				仅查询（显示）（stuage）相应字段名的限定范围

	多个限定条件查询：
		AND 同时成立
			SELECT*FROM stu WHERE stuage=18 AND stuNO=9;
				
		OR 任一成立
			SELECT*FROM stu WHERE stuage=18 OR stuNO=3;

		AND优先级大于OR
		OR、AND同时出现时，先满足AND左右的条件筛选，再满足OR
	eg：SELECT*FROM stu WHERE stuage=19 OR stuage=18 AND stuName=‘myy’; 
                             筛选名字为myy且年龄为18的和年龄19的所有集

		SELECT*FROM stu WHERE (stuage=19 OR stuage=18) AND stuName=‘myy’; 
               		 筛选名字为myy年龄为18或19的所有集

	操作符 IN：用于简化同一字段的多个筛选条件
		不能有其他筛选字段条件eg：… WHERE stuage IN(18,19) AND stuName=‘myy’; 
			SELECT*FROM stu WHERE  stuage=19 OR stuage=18 ;
			简化为：
			SELECT*FROM stu WHERE stuage IN(18,19);

	模糊查询：
	1.LIKE操作符结合通配符%和_
		查询字段以m开头：
			SELECT*FROM stu WHERE stuName LIKE’m%’;
		查询字段包含m：
			SELECT*FROM stu WHERE stuName LIKE’%m%’;
		查询字段以m结尾：
			SELECT*FROM stu WHERE stuName LIKE’%m’

		查询字段中y后面跟一个字,y为首字母：
			SELECT*FROM stu WHERE stuName LIKE’y_’;
		查询字段中y后面跟两个字,y为首字母：
			SELECT*FROM stu WHERE stuName LIKE’y__’;
		查询字段中y前面跟一个字，后面可能跟有字：
			SELECT*FROM stu WHERE stuName LIKE’_y%’

		数据中含有_时，查询方法使用转义字符：\反斜杠
			SELECT*FROM stu WHERE stuName LIKE’%\_%’;


	2.使用正则表达式（regular expression)：REGEXP
		查询包含“z”的姓名：
			SELECT *FROM stu WHERE stuName REGEXP’z’;

		查询包含“z”或者“y”的姓名：
			SELECT *FROM stu WHERE stuName REGEXP’z|y’;

		查询以“z”开头的姓名：
			SELECT *FROM stu WHERE stuName REGEXP’^z’;

		查询以“y”结尾的姓名：
			SELECT *FROM stu WHERE stuName REGEXP’y$’;

		查询以“z”或者“m”开头的姓名：
			SELECT *FROM stu WHERE stuName REGEXP’[my]’;
			SELECT *FROM stu WHERE stuName REGEXP’^m|y’;

		查询包含字母“a-m””A-M的所有名字：
			SELECT *FROM stu WHERE stuName REGEXP’^[a-mA-M]’;

		查询包含3个字母的名字：
			SELECT *FROM stu WHERE stuName REGEXP’^[a-zA-Z]{3}’;

4字符集和校对集

查看字符集的相关变量：
	SHOW VARIABLES LIKE ‘CHARACTER%’;
校对集：定义比较字符串的方式，如是否区分大小写等

1.查看字符集信息：
		SHOW CHARACTER SET;
		SHOW CHARSET;


2.查看校对集信息：
		SHOW COLLATION;

3.字符集和校对集的设置：
		SET 变量名=值;
		SET character_sets_system=gbk;
		
		同时更改客户机、链接和返回结果字符集(仅当前窗口有效）：
			SET NAMES utf8;
			SET NAMES gbk;

?

创建计算字段：
	拼接：Concat（不知道拼接后的字段别名）
		SELECT Concat(stuage,stuName,stuNO)FROM stu;
	
	使用别名：AS
		SELECT Concat(stuage,stuName,stuNO) AS 学生信息 FROM stu;
		简写：
		SELECT Concat(stuage,stuName,stuNO)   学生信息 FROM stu;

	算数运算：针对于数值型
		SELECT stuNO,stuName,YEAR(NOW())-stuage  学生出生日期 FROM stu;
		SELECT stuNO,stuName,Y2022-stuage  学生出生日期 FROM stu;

	使用函数：

聚集函数：不计算NULL值
		Count(*):计算有多少条记录,计数的作用
			SELECT Count(*) FROM stu;
		指定字段：
			SELECT Count(stuNO) FROM stu;
		去重复值：
			SELECT Count(DISTINCT stuage) FROM stu;

		SUM()    AVG()	  MAX()	MIN()
			应用类似eg:
			SELECT AVG( stuage) FROM stu;	

	分组数据：GROUP BY
		按年龄分组，每一组有多少人：
			SELECT stuage,Count(*)FROM stu GROUP BY stuage;

分组过滤：HAVING,对计算出的结果进行过滤
			查询各个年龄2个人以上的：人数代表组名，可用Count(*)
				SELECT stuage,Count(*)人数 FROM stu GROUP BY stuage HAVING 人数>1;
	
	子查询：（嵌套）
		1.不相关子查询：
		查询年龄最高：
			SELECT MAX(stuage) FROM stu;

		查询选课编号最大的学号：
			SELECT stuNO FROM sc WHERE stuclass=(SELECT MAX(stuclass) FROM sc);

		多个数据表的查询：stu、sc
		查询选了课的学生信息：
			SELECT*FROM  stu WHERE stuNO IN (SELECT DISTINCT stuNO FROM sc);

		2.相关子查询：
			显示学生选课门数的信息（sc。stu）
				SELECT stuNO,stuName,stuage,(SELECT  COUNT(*) FROM sc WHERE sc.stuNO=stu.stuNO)FROM stu;

查看创建语句： SHOW CREATE TABLE sc1;	
	非空约束：
		创建表时：
		CREATE TABLE IF NOT EXISTS sc1(
 			stuNO  CHAR(5) NOT NULL,
 			stuName VARCHAR(20),
 			stuclass VARCHAR(10) COMMENT'1:高数2:英语3:java’);

		创建后，修改：先删掉表中非空数据，再修改）
			ALTER TABLE sc MODIFY stuName VARCHAR(20) NOT NULL;

		删除数据表中相关字段含有NULL的：
			DELETE FROM sc1 WHERE stuName IS NULL;
		
		删除非空：（
			ALTER TABLE sc1 MODIFY stuName VARCHAR(20)  NULL;

	唯一约束：不出现重复的
		创建表：
			CREATE TABLE IF NOT EXISTS sc2(
 			stuNO  CHAR(5) UNIQUE,
 			stuName VARCHAR(20),
 			stuclass VARCHAR(10) COMMENT'1:高数2:英语3:java’);


		创建后修改：
			ALTER TABLE sc2 MODIFY stuNO  CHAR(5) UNIQUE;
			ALTER TABLE sc2 ADD CONSTRAINT stuN UNIQUE (stuNO);
			(stuN是唯一约束的别名，自己设置，未设置则需要查询语句找到名字)

		删除唯一约束：
			ALTER TABLE sc2 DROP INDEX stuN;

	主键约束：
			CREATE TABLE IF NOT EXISTS sc2(
 			stuNO  CHAR(5) ,
 			stuName VARCHAR(20) NOT NULL UNIQUE,
 			stuclass VARCHAR(10) COMMENT'1:高数2:英语3:java’);

?

主键约束：
		创建时：
			CREATE TABLE IF NOT EXISTS sc3(
   			 stuNO  CHAR(5)  ,
   			 stuName VARCHAR(20),
   			 stuclass VARCHAR(10) COMMENT'1:高数2:英语3:java’,
			PRIMARY KEY(stuNO,stuName));

		创建后修改：
			针对列：
			ALTER TABLE sc3 MODIFY stuName VARCHAR(20) PRIMARY KEY;
			针对表：
			ALTER TABLE sc3 ADD PRIMARY KEY(stuName,stuNO);

		删除：
			ALTER TABLE sc3 DROP PRIMARY KEY;

	外键约束：参照完整性


		创建时：
			CREATE TABLE IF NOT EXISTS sc4(
   			 stuNO  CHAR(5)  ,
   			 stuName VARCHAR(20),
   			 stuclass VARCHAR(10) COMMENT'1:高数2:英语3:java’,
			FOREIGN KEY(stuNO) REFERENCES sc3(stuNO));
			sc3是sc4的主表


		创建后修改：
			ALTER TABLE sc4 ADD[CONSTRAINT stuNO FOREIGN KEY (stuNO) REFERENCES  sc3(stuNO);
				stuN为自定义别名
		删除：
			ALTER TABLE sc4 DROP FOREIGN KEY stuNO;

			外键约束的数据来源必须来源于主表，此时不能直接删除主表，要先删除子表的外键约束

1.要么都成功，要么都失败，多个对象同时运行
2.事务原则：
	ACID原则：
		1.原子性：针对同一个事务，要么都成功要么都失败，不能只发生其中一个（转账）

		2.隔离性：多组用户交互操作不会产生影响，有隔离。
			 若隔离失败，可能会产生脏读（一个事务读取了另外一个事务未提交女的数据，
			 如多组用户转账超出本身存款限额），幻读（在一个事务内读取到了别的事务插入的数				 据，导致前后读取数据不一样），不可重复读（在一个事务内多次读取结果不同）。

		3.一致性：最终一致性，事务前后数据完整性保证不变（银行转账最终双方总额不变）

		4.持久性：事务结束后的数据不会因为外界原因导致数据丢失，事务一旦提交则不可逆
			（事务没有提交遭受外界因素会恢复到原状，事务提交则持久化到数据库不会变）

3.mysql是默认开启事务自动提交的：
	SET autocomit=0;关闭
	SET autocomit=1;开启

4.手动处理事务：
	先关闭自动提交：
	SET autocomit=0;关闭

	标记一个事务的开始：
	STATRT TRASACTION;
	开始后结束前的内容都为同一个事务
	提交：持久化(成功！）
	COMMIT;
	回滚：回到原来的样子（提交失败）
	ROLLBACK;
	设置一个事务的保存点：
	SAVEPOINT;
	回滚到保存点：
	ROLLBACK TO SAVEPOINT;
	删除保存点：
	RELEASE SAVEPOINT;

	事务结束：开启自动提交即可
	SET autocomit=1;
	

					用户表mysql.user
权限

1.创建用户：用户名，by密码
	CREATE USER qingxiao IDENTIFIED BY ‘123456’;

2.修改密码:
	修改当前用户密码：
		SET PASSWORD=PASSWORD(‘111111’);

	修改指定用户密码:
		SET PASSWORD FOR 用户名=PASSWORD(‘111111’);

3.用户重命名：
	RENAME USER 用户名旧 TO 新用户名;

4.用户授权：
	ALL PRIVILEGES 授予全部的权限：
		GRANT ALL PRIVILEGES ON *.* TO 用户名;

5.查看权限:
	查看指定用户
		SHOW GRANTS FOR 用户名;
	查看管理员
		SHOW GRANTS FOR root@localhost;

6.撤销权限：
	REVOKE ALL PRIVILEGES ON *.* TO FROM 用户名;

7.删除用户：
	DROP USER 用户名；



备份：
1.手动导出文件

2.终端（命令行）导出：
	source .bash_profile
	mysqldump -u root -p mysql stu>text.sql
				数据库 数据表>导出来的文件名.sql