数据库基本知识点

数据库基本知识点(MySQL)

数据库是所有软件体系中最核心的存在 DBA

什么是数据库

• 概念：是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合

• 作用：储存和管理数据

数据库的分类

• 关系型数据库

  • 包含不限于MySQL，Oracle，SQL Server，D2B等
  • 通过表和表之间，行和列之间的关系进行储存(excel)

• 非关系型数据库

  • Redis，MongDB等
  • 通过对象

MySQL安装注意事项

1. 下载的时候下载zip文件更好，因为exe会涉及注册表比较麻烦

2. 专门设置一个环境文件夹最好，这样设置环境变量也比较方便

3. 需要设置环境变量和自己看加一个配置文件ini

   ini文件中可以设置默认utf8，但是一般在创建表的时候设置，因为这样会有通用性

4. 参考安装流程

安装流程博客园有一个参考文章放在这里 (重点是最后要重启一下)

安装MySql

• 有个命令是sc delete mysql，意思是清空服务，安装错了可以重新试一下这个命令

需要安装一个可视化软件(navicat等)

一下是一个安装和破解的流程

navicat安装破解

不用可视化软件的一些操作命令

mysql -uroot -p --连接数据集 P后面不打空格直接按回车就是输入密码，或者在P后面直接加密码，如-p123456

--注意，除了上面的第一条，剩下的所有的语句都是以:结尾

update mysql.user set authentication_string=password("你的密码") where user="root"; 
--修改密码 实际上数据库中的确存在mysql的数据库和一个叫user的表，所以本质上其实也是修改了某张表中的数据
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码'; --这个同样也是修改密码，8.0之后是用这个语句

flush privileges; --刷新权限

------------------
--以下是数据库内部的操作
mysq1> use testSchool  --切换数据库 use 数据库名
Database changed

show tables;-- 查看数据库中所有的表
describe school; -- 显示数据库中所有的表的信息(指的是testSchool这个数据库中的school这个表的信息)
create database westos; -- 创建一个数据库
exit;--退出连接

数据库包含的语言类型

以上其实就是各种语言 增删改查其实就包含在以下语言类型中

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库的操作

操作数据库

如果表名或者字段名是特殊字符，就需要带tab上面的那个符号(`)，比如你的表里面有user这个字段，但是其实一定有user这个表格的，所以查询user 的时候可以加

创建数据库

CREATE DATABASE [IF NOT EXISTS] testData;

删除数据库

DROP DATABASE [IF EXISTS] testData;

使用数据库

USE  `testData`; --这句就是用了tab上面的那个符号

查看数据库

SHOW DATABASES ; -- 查看所有的数据库

数据库的列类型

还有个特殊的是null

数值

• tinyint 十分小的数据 1字节
• smallint 较小的数据类型 2字节
• mediumint 中等大小的数据 3字节
• int 整数 4字节
• bigint 较大的数据 8字节
• float 浮点数 4字节
• double 浮点数 8字节
• decimal 字符串形式的浮点数 涉及金融计算的时候，一般是使用decima

字符串

• char 字符串固定大小 0~255
• varchar 可变字符串 0~65535(好像是看版本，所以不一定)
• tinytext 微型文本 2^8-1
• text 文本 2^16-1

时间日期

• year 年

• date YYYY-MM-DD

  如果不是数据库中的话需要说一下yyyy和YYYY DD和dd 以及HH和hh区别

  如2020-12-27到2020-12-31号这段时间内，使用小写的yyyy得到的年份是正常的2020年，而使用大写的YYYY得到的年份是2021年

  大写的D打印出来的时间是这一年中的那一天，小写的d是这个月中的那一天

  小写h就是12小时制，大写就是24小时

  （MM和mm一个是月一个是分）

• time HH:mm:ss()

• datetime YYYY-MM-DD HH:mm:ss

• timestape 时间戳 1970年.1.1到现在的毫秒数

创建一个数据库

CREATE TABLE IF NOT EXISTS `student`(                      -- 很重要的一点！student左右两边是tab上面那个点不是单引号
`id`    INT(4) NOT NULL AUTO_INCREMENT COMMENT '作为学号',   -- 很重要的一点！id等涉及字段的左右两边也是tab上面那个点不是单引号
`name`  VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd`   VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex`   VARCHAR(10) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(40) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)                                             -- 这里一样！！！！id左右两边是tab上面那个点不是单引号

)ENGINE = INNODB DEFAULT CHARSET=utf8                        -- 是utf8而不是utf-8  
-- ENGINE=InnoDB  数据库存储引擎  DEFAULT 默认 CHARSET=utf8 数据库字符编码

• 一些特殊的命令

  show CREATE DATABASE testschool  -- 查看创建表的语句
  SHOW CREATE TABLE student        -- 查看创建数据量的语句
  DESC student                     -- 查看创建表的语句
  

数据表的类型

-- 关于数据库引擎 即创建表的时候提到的ENGINE这个
/*
INNODB 默认生成！
MYISAM 早些年使用
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持(但是5.6.4之后可以支持了貌似)
表空间大小	较小	较大，约为MYISAM的两倍

各自对比：

• 各自优点

  • MYISAM 节约空间

  • INNODB 安全性较高

• 物理空间存在位置区别

  • InnoDB 在数据库表中只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件
  • MYISAM 则有三个对应文件
    1. *.frm表结构的定义文件
    2. *.MYD数据文件 (data)
    3. *.MYI索引文件 (index)

修改和删除表

-- 修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS studentTest
-- 增加表的字段 :ALTER TABLE 表名 ADD 字段名 列属性-
IALTER TABLE student ADD age INT(11)

-- 修改表的字段
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student MODIFY age VARCHAR(11) 
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE student CHANGE pwd newpwd INT(10)
-- 所以理论上说CHANGE更厉害?

-- 删除表的字段
1ALTER TABLE student DROP age

-- 删除表
DROP TABLE IF EXISTS student

增删改查中删的注意事项

增改太简单了就不做笔记了

删除的命令有两个，一个是delete一个是truncate，简单来讲，truncate是删除原表并且重新建立一个没有数据的新表，delete是逐条删除原表数据

所以他们会有些不同

不同点：

• TRUNCATE会重新设置自增列，计数器会归零

  如果是INNODB的话，使用delete后，只要重启数据库自增列才会重新归零(内存中，断电丢失)

  如果是MYISAM 的话，使用delete后，重启数据库自增列也不会重新归零(文件中，断电不会丢失)

• TRUNCATE不会影响事务

增删改查中查的注意事项

1. 去重关键字 DISTINCT

   SELECT DISTINCT name FROM student 
   

2. 特殊查询

   SELECT VERSION() -- 查询系统版本
   SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
   SELECT @@auto_increment_increment -- 查询自增的步长(变量)
   

3. 分页查询和排序

   排序就是ASC和DESC不用管

   首先要建立两个表再查询比较方便 navicat有生成随机数据的方法，可以快速生成数据

   -- LIMIT 当前行 显示多少条数据 
   -- 如下就是从第0条数据开始显示，显示十条数据 被注释的就是从第三条数据开始显示显示十条
   
   select S.*,Stu.* from  `school` S 
   INNER JOIN `student` Stu 
   ON S.NAME = Stu.NAME 
   ORDER BY S.SCHOOLID
   LIMIT 0,10
   -- LIMIT 2,10
   

4. 子查询

   即where，效率不高不推荐，最好不用

5. 嵌套查询

   即先用查询sql查出一个结果，这个结果可以当做条件查询,以下是实例

   -- 分数不小于80分的学生的学号和姓名
   SELECT DISTINCT s.`StudentNo`，`StudentName` 
   FROM student s
   INNER JOIN result r
   ON r.StudentNo = s.StudentNo
   WHERE `StudentResult`>=80
   
   -- 在这个基础上增加一个科目，高等数学-2
   -- 查询 高等数学-2 的编号
   SELECT DISTINCT s.`StudentNo`，`StudentName` 
   FROM student s
   INNER JOIN result r
   ON r.StudentNo = s.StudentNo
   WHERE `StudentResult`>=80 AND `SubjectNo` = (
   	SELECT SubjectNo FROM `subject`
       WHERE `SubjectName `='高等数学-2'
   )
   
   
   -- 改造
   SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (
       SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo = (
           SELECT SubjectNo FROM `subject` WHERE `SubjectName`='高等数学-2'
           )
       )
   

MySQL常用函数

数学运算

select ABS(-1);      -- 取得绝对值，结果是-1 absolute 绝对值 
SELECT CEILING(9.1)  -- 向上取整，结果是10
SELECT FLOOR(9.9)    -- 向下取整，结果是9
SELECT RAND()        -- 返回一个0到1之间的随机数
SELECT SIGN(10)     --  判断正负数，正数返回1  
SELECT SIGN(-10)     -- 判断正负数，负数返回-1

字符串函数

SELECT CHAR_LENGTH('玄黎是个小姑娘');					-- 字符串长度                          			7
SELECT CONCAT ('玄黎','超级','厉害');					-- 拼接字符串                          			玄黎超级厉害
SELECT INSERT('玄黎是个很厉害的DM',4,2,'超级');		  -- 查询，从某个位置之后开始替换某个长度				  玄黎是超级厉害的DM
SELECT LOWER('XuanLi');								-- 小写字母							  			 xuanli
SELECT UPPER('XuanLi');								-- 大写字母							  			 XUANLI
SELECT INSTR('xuanli','l');							-- 返回第一次出现的子串的索引             		    5
SELECT REPLACE('玄黎喜欢摆烂','摆烂','努力');			  -- 替换出现的指定字符串							   玄黎喜欢努力
SELECT SUBSTR('玄黎在开罗拉西里',5,4) ;				   -- 返回指定的子字符串(源字符串,截取的位置，截取的长度)   罗拉西里
SELECT REVERSE('她爱我');							   -- 反转										我爱她

时间和日期

SELECT CURRENT_DATE();			-- 获取当前日期
SELECT CURDATE();				-- 获取当前的时间
SELECT NOW();					-- 获取当前的时间
SELECT LOCALTIME(); 			-- 系统时间
SELECT YEAR(NOW()); 			-- 获取当前年
SELECT MONTH(NOW());			-- 获取当前月
SELECT DAY(NOW());				-- 获取当前日
SELECT HOUR(NOW());				-- 获取当前时
SELECT MINUTE(NOW());			-- 获取当前分
SELECT SECOND(NOW());			-- 获取当前秒

特殊函数

HAVING用法(和where不同)

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。

而 having子句在聚合后对组记录进行筛选。