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

发布时间:2024年01月20日

最近在忙课程设计和期末考试!!!上线啦!!!此篇文章是博主自己学数据库的笔记,理论笔记在本子上,所以暂时没有理论笔记,都是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;

?

5字段相关计算应用
创建计算字段:
	拼接: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;

6数据完整性

查看创建语句: 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’);

7键的相关

?

主键约束:
		创建时:
			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;

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

8事务

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;
	

9权限管理和备份,三大范式和性能

					用户表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

文章来源:https://blog.csdn.net/2201_75903640/article/details/134308025
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。