2023年数据库基本知识和基础sql语句.pdf

《2023年数据库基本知识和基础sql语句.pdf》由会员分享，可在线阅读，更多相关《2023年数据库基本知识和基础sql语句.pdf（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库基本知识和基础 sql 语句 数据库的发展历程 没有数据库,使用磁盘文件存储数据;层次结构模型数据库;网状结构模型数据库;关系结构模型数据库:使用二维表格来存储数据;关系-对象模型数据库;理解数据库 RDBMS=管理员(manager)+仓库(database)database=N个 table table:表结构:定义表的列名与列类型！表记录:一行一行的记录！Mysql 安装目录:bin 目录中都就是可执行文件;my、ini 文件就是 MySQL 的配置文件;相关命令:启动:net start mysql;关闭:net stop mysql;mysql-u root-p 123-h l

2、ocalhost;-u:后面的 root 就是用户名,这里使用的就是超级管理员 root;-p:后面的 123 就是密码,这就是在安装 MySQL 时就已经指定的密码;退出:quit 或 exit;sql 语句 语法要求 SQL 语句可以单行或多行书写,以分号结尾;可以用空格与缩进来来增强语句的可读性;关键字不区别大小写,建议使用大写;分类 DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);基本操作 查瞧所有数据库名称:SHOW D

3、ATABASES;切换数据库:USE mydb1,切换到 mydb1 数据库;创建数据库:CREATE DATABASE IF NOT EXISTS mydb1;修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8 创建表:CREATE TABLE 表名(列名 列类型,数据库基本知识和基础 sql 语句 列名 列类型,、);查瞧当前数据库中所有表名称:SHOW TABLES;查瞧指定表的创建语句:SHOW CREATE TABLE emp,查瞧 emp 表的创建语句;查瞧表结构:DESC emp,查瞧 emp 表结构;删除表:DROP TABLE em

4、p,删除 emp 表;修改表:1.修改之添加列:给 stu 表添加 classname 列:ALTER TABLE stu ADD(classname varchar(100);2.修改之修改列类型:修改 stu 表的 gender 列类型为 CHAR(2):ALTER TABLE stu MODIFY gender CHAR(2);3.修改之修改列名:修改 stu 表的 gender 列名为 sex:ALTER TABLE stu change gender sex CHAR(2);4.修改之删除列:删除 stu 表的 classname 列:ALTER TABLE stu DROP cla

5、ssname;5.修改之修改表名称:修改 stu 表名称为 student:ALTER TABLE stu RENAME TO student;其她常用命令:mysql 基本操作命令 一、数据库操作 1、新增数据库 create database 数据库名字 数据库选项;数据库选项:规定数据库内部该用什么进行规范 字符集:charset 具体字符集(utf8)校对集:collate 具体校对集(依赖字符集)2、查瞧数据库 2、1 查瞧所有的数据库 show databases;匹配查询:show databases like pattern;#pattern 可以使用通配符 _:下划线匹配,表

6、示匹配单个任意字符,如:_s,表示任意字符开始,但就是以s结尾的数据库 数据库基本知识和基础 sql 语句%:百分号匹配,表示匹配任意个数的任意字符,如:student%,表示以 student 开始的所有数据库 2、2 查瞧数据库的创建语句 show create database 数据库名字;3、修改数据库 数据库名字在 mysql 高版本中不允许修改,所以只能修改数据库的库选项(字符集与校对集)alter database 数据库名字 数据库选项;eg:alter database stu charset utf8;4、删除数据库 对于数据库的删除要谨慎考虑,就是不可逆的。drop da

7、tabase 数据库名字;4、选择数据库 use 数据库名字;二、数据表操作(字段)1、新增数据表 create table 表名(字段名 1 数据类型 comment 备注、,字段名 2 数据类型 comment 备注、,、#最后一行不需要逗号 )表选项;表选项:1)字符集:charset/character set(可以不写,默认采用数据库的)2)校对集:collate 3)存储引擎:engine=innodb(默认的):存储文件的格式(数据如何存储)数据库基本知识和基础 sql 语句 注意:创建数据表的时候,需要指定要在哪个数据库下创建。创建方式有隐式创建与显式创建 1)显式创建:cre

8、ate table 数据库名字、数据表名字 2)隐式创建:use 数据库名字;2、查瞧数据表 2、1 查瞧所有的数据表 show tables;2、2 查瞧表使用匹配查询 Show tables like pattern ;#与数据库的 pattern 一样:_与%两个通配符 2、3 查瞧数据表的创建语句 show create table 数据表名字;2、4 查瞧数据表的结构 desc 数据表名字;3、修改数据表 3、1 修改表名字 rename table 旧表名 to 新表名;3、2 修改表选项(存储引擎,字符集与校对集)alter table 表名 表选项;3、3 修改字段(新增字段,

9、修改字段名字,修改西段类型,删除字段)新增字段:alter table 表名 add column 字段名字 数据库类型 位置 first/after;位置选项:first 在第一个字段 after 在某个字段之后,默认就就是在最后一个字段后面 数据库基本知识和基础 sql 语句 修改字段名称:alter table 表名 change 旧字段名字 新字段名字 字段数据类型 位置;eg:alter table student name fullname varchar(30)after id;修改字段的数据类型:alter table 表名 modify 字段名字 数据类型 位置;删除字段:a

10、lter table 表名 drop 字段名字;4、删除数据表 drop table 表名;三、数据操作 1、新增数据 inser into table 表名(字段列表)values(值列表);2、查瞧数据 select*/字段列表 from 表名 where 条件;3、修改数据 update 表名 set 字段名=值 where 条件;注意:使用 update 操作最好配合 limit 1 使用,避免操作大批量数据更新错误、4、删除数据 delete from 表名 where 条件;注意:没有 where 条件 就就是默认删除全部数据、四、列属性(字段)1、删除主键:alter table

11、 表名 drop primary key;数据库基本知识和基础 sql 语句 2、增加主键:alter table 表名 add primary key(字段列表);#可以就是复合主键 3、删除自增长:只能通过修改字段属性的方法操作、4、删除唯一键:alter table 表名 drop index 索引名字;#默认的唯一键名字就就是字段的本身 5、增加唯一键:alter table 表名 add unique key(字段列表);#可以就是复合唯一索引 五、外键约束 1、创建表的时候增加外键 constraint 外键名字 foreign key(外键字段)references 父表(主键字

12、段);eg:-创建父表(班级表)create table class(id int primary key auto_increment,name varchar(10)not null comment 班级名字,room varchar(10)not null comment 教室号 )charset utf8;-创建子表(外键表)create table student(id int primary key auto_increment,number char(10)not null unique comment 学号:itcast+四位数,name varchar(10)not null

13、 comment 姓名,c_id int comment 班级 ID,-增加外键 foreign key(c_id)references class(id)charset utf8;2、创建表之后增加外键 alter table 表名 add constraint 外键名字 foreign key(外键字段)references 父表(主键字段);eg:数据库基本知识和基础 sql 语句 -增加外键 alter table student add constraint student_class_fk foreign key(c_id)references class(id);3、删除外键 a

14、lter table 表名 drop foreign key 外键名字;#查瞧外键名字需要通过表创建语句来查询、eg:-删除外键 alter table student drop foreign key student_ibfk_1;数据查询语法(DQL)DQL 就就是数据查询语言,数据库执行 DQL 语句不会对数据进行改变,而就是让数据库发送结果集给客户端。SELECT selection_list/*要查询的列名称*/FROM table_list/*要查询的表名称*/WHERE condition/*行条件*/GROUP BY grouping_columns/*对结果分组*/HAVIN

15、G condition/*分组后的行条件*/ORDER BY sorting_columns/*对结果分组*/LIMIT offset_start,row_count/*结果限定*/基础查询 1、1 查询所有列 SELECT*FROM stu;1、2 查询指定列 SELECT sid,sname,age FROM stu;2 条件查询 2、1 条件查询介绍 条件查询就就是在查询时给出 WHERE子句,在 WHERE子句中可以使用如下运算符及关键字:=、!=、=;BETWEEN AND;数据库基本知识和基础 sql 语句 IN(set);IS NULL;AND;OR;NOT;2、2 查询性别为女

16、,并且年龄 50 的记录 SELECT*FROM stu WHERE gender=female AND ge=20 AND age=40;或者 SELECT*FROM stu WHERE age BETWEEN 20 AND 40;2、8 查询性别非男的学生记录 SELECT*FROM stu WHERE gender!=male;或者 SELECT*FROM stu 数据库基本知识和基础 sql 语句 WHERE gendermale;或者 SELECT*FROM stu WHERE NOT gender=male;2、9 查询姓名不为 null 的学生记录 SELECT*FROM stu

17、 WHERE NOT sname IS NULL;或者 SELECT*FROM stu WHERE sname IS NOT NULL;3 模糊查询 当想查询姓名中包含 a 字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。3、1 查询姓名由 5 个字母构成的学生记录 SELECT*FROM stu WHERE sname LIKE _;模糊查询必须使用 LIKE关键字。其中“_”匹配任意一个字母,5 个“_”表示 5 个任意字母。3、2 查询姓名由 5 个字母构成,并且第 5 个字母为“i”的学生记录 SELECT*FROM stu WHERE sname LIKE _i;3

18、、3 查询姓名以“z”开头的学生记录 SELECT*FROM stu WHERE sname LIKE z%;其中“%”匹配 0n 个任何字母。3、4 查询姓名中第 2 个字母为“i”的学生记录 SELECT*FROM stu WHERE sname LIKE _i%;数据库基本知识和基础 sql 语句 3、5 查询姓名中包含“a”字母的学生记录 SELECT*FROM stu WHERE sname LIKE%a%;4 字段控制查询 4、1 去除重复记录 去除重复记录(两行或两行以上记录中系列的上的数据都相同),例如emp 表中sal 字段就存在相同的记录。当只查询 emp 表的 sal 字

19、段时,那么会出现重复记录,那么想去除重复记录,需要使用 DISTINCT:SELECT DISTINCT sal FROM emp;4、2 查瞧雇员的月薪与佣金之与 因为 sal 与 comm 两列的类型都就是数值类型,所以可以做加运算。如果 sal 或 comm 中有一个字段不就是数值类型,那么会出错。SELECT*,sal+comm FROM emp;comm 列有很多记录的值为 NULL,因为任何东西与 NULL相加结果还就是 NULL,所以结算结果可能会出现 NULL。下面使用了把 NULL转换成数值 0 的函数 IFNULL:SELECT*,sal+IFNULL(comm,0)FRO

20、M emp;4、3 给列名添加别名 在上面查询中出现列名为 sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为 total:SELECT*,sal+IFNULL(comm,0)AS total FROM emp;给列起别名时,就是可以省略 AS 关键字的:SELECT*,sal+IFNULL(comm,0)total FROM emp;5 排序 5、1 查询所有学生记录,按年龄升序排序 SELECT*FROM stu ORDER BY sage ASC;或者 SELECT*FROM stu ORDER BY sage;5、2 查询所有学生记录,按年龄降序排序 S

21、ELECT*数据库基本知识和基础 sql 语句 FROM stu ORDER BY age DESC;5、3 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序 SELECT*FROM emp ORDER BY sal DESC,empno ASC;6 聚合函数 聚合函数就是用来做纵向运算的函数:COUNT():统计指定列不为 NULL的记录行数;MAX():计算指定列的最大值,如果指定列就是字符串类型,那么使用字符串排序运算;MIN():计算指定列的最小值,如果指定列就是字符串类型,那么使用字符串排序运算;SUM():计算指定列的数值与,如果指定列类型不就是数值类型,那么计算结果为

22、 0;AVG():计算指定列的平均值,如果指定列类型不就是数值类型,那么计算结果为 0;6、1 COUNT 当需要纵向统计时可以使用 COUNT()。查询 emp 表中记录数:SELECT COUNT(*)AS cnt FROM emp;查询 emp 表中有佣金的人数:SELECT COUNT(comm)cnt FROM emp;注意,因为 count()函数中给出的就是 comm 列,那么只统计 comm 列非 NULL的行数。查询 emp 表中月薪大于 2500 的人数:SELECT COUNT(*)FROM emp WHERE sal 2500;统计月薪与佣金之与大于 2500 元的人数

23、:SELECT COUNT(*)AS cnt FROM emp WHERE sal+IFNULL(comm,0)2500;查询有佣金的人数,以及有领导的人数:SELECT COUNT(comm),COUNT(mgr)FROM emp;6、2 SUM 与 AVG 当需要纵向求与时使用 sum()函数。查询所有雇员月薪与:SELECT SUM(sal)FROM emp;查询所有雇员月薪与,以及所有雇员佣金与:SELECT SUM(sal),SUM(comm)FROM emp;查询所有雇员月薪+佣金与:SELECT SUM(sal+IFNULL(comm,0)FROM emp;统计所有员工平均工资:

24、SELECT SUM(sal),COUNT(sal)FROM emp;或者 SELECT AVG(sal)FROM emp;数据库基本知识和基础 sql 语句 6、3 MAX 与 MIN 查询最高工资与最低工资:SELECT MAX(sal),MIN(sal)FROM emp;分组查询 当需要分组查询时需要使用 GROUP BY子句,例如查询每个部门的工资与,这说明要使用部分来分组。7、1 分组查询 查询每个部门的部门编号与每个部门的工资与:SELECT deptno,SUM(sal)FROM emp GROUP BY deptno;查询每个部门的部门编号以及每个部门的人数:SELECT de

25、ptno,COUNT(*)FROM emp GROUP BY deptno;查询每个部门的部门编号以及每个部门工资大于 1500 的人数:SELECT deptno,COUNT(*)FROM emp WHERE sal1500 GROUP BY deptno;HAVING子句 查询工资总与大于 9000 的部门编号以及工资与:SELECT deptno,SUM(sal)FROM emp GROUP BY deptno HAVING SUM(sal)9000;注意,WHERE就是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而 HAVING就是对分组后数

26、据的约束。8 LIMIT LIMIT用来限定查询结果的起始行,以及总行数。8、1 查询 5 行记录,起始行从 0 开始 SELECT*FROM emp LIMIT 0,5;注意,起始行从 0 开始,即第一行开始！8、2 查询 10 行记录,起始行从 3 开始 SELECT*FROM emp LIMIT 3,10;数据库基本知识和基础 sql 语句 8、3 分页查询 如果一页记录为 10 条,希望查瞧第 3 页记录应该怎么查呢？第一页记录起始行为 0,一共查询 10 行;第二页记录起始行为 10,一共查询 10 行;第三页记录起始行为 20,一共查询 10 行;多表连接查询 连接查询 内连接 外

27、连接 左外连接 右外连接 全外连接(MySQL 不支持)自然连接 子查询 连接查询 连接查询就就是求出多个表的乘积,例如 t1 连接 t2,那么查询出的结果就就是 t1*t2。连接查询会产生笛卡尔积,假设集合 A=a,b,集合 B=0,1,2,则两个集合的笛卡尔积为(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)。可以扩展到多个集合的情况。数据库基本知识和基础 sql 语句 那么多表查询产生这样的结果并不就是我们想要的,那么怎么去除重复的,不想要的记录呢,当然就是通过条件过滤。通常要查询的多个表之间都存在关联关系,那么就通过关联关系去除笛卡尔积。2、1 内连接 上面的连接

28、语句就就是内连接,但它不就是 SQL 标准中的查询方式,可以理解为方言！SQL 标准的内连接为:SELECT*FROM emp e INNER JOIN dept d ON e、deptno=d、deptno;内连接的特点:查询结果必须满足条件。例如我们向 emp 表中插入一条记录:其中 deptno 为 50,而在 dept 表中只有 10、20、30、40 部门,那么上面的查询结果中就不会出现“张三”这条记录,因为它不能满足 e、deptno=d、deptno 这个条件。2、2 外连接(左连接、右连接)外连接的特点:查询出的结果存在不满足条件的可能。左连接:SELECT*FROM emp

29、e LEFT OUTER JOIN dept d ON e、deptno=d、deptno;左连接就是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示 NULL。子查询 子查询就就是嵌套查询,即 SELECT中包含 SELECT,如果一条语句中存在两个,或两个以上SELECT,那么就就是子查询语句了。子查询出现的位置:where 后,作为条件的一部分;from 后,作为被查询的一条表;当子查询出现在 where 后作为条件时,还可以使用如下关键字:any all 子查询结果集的形式:单行单列(用于条件)单行多列(用于条件)多行单列(用于条件)多行多列(用于

30、表)练习:1.工资高于 smith 的员工。分析:查询条件:工资smith 工资,其中 smith 工资需要一条子查询。第一步:查询 smith 的工资 数据库基本知识和基础 sql 语句 SELECT sal FROM emp WHERE ename=smith 第二步:查询高于 smith 工资的员工 SELECT*FROM emp WHERE sal ($第一步)结果:SELECT*FROM emp WHERE sal (SELECT sal FROM emp WHERE ename=smith)子查询作为条件 子查询形式为单行单列 2.工资高于 30 部门所有人的员工信息 分析:查询条

31、件:工资高于 30 部门所有人工资,其中 30 部门所有人工资就是子查询。高于所有需要使用 all 关键字。第一步:查询 30 部门所有人工资 SELECT sal FROM emp WHERE deptno=30;第二步:查询高于 30 部门所有人工资的员工信息 SELECT*FROM emp WHERE sal ALL($第一步)结果:SELECT*FROM emp WHERE sal ALL(SELECT sal FROM emp WHERE deptno=30)子查询作为条件 子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用 ALL或 ANY关键字)3.查询工作与工资与 s

32、mith 完全相同的员工信息 分析:查询条件:工作与工资与 smith 完全相同,这就是子查询 第一步:查询出 smith 的工作与工资 SELECT job,sal FROM emp WHERE ename=smith 第二步:查询出与 smith 工作与工资相同的人 SELECT*FROM emp WHERE(job,sal)IN($第一步)结果:SELECT*FROM emp WHERE(job,sal)IN(SELECT job,sal FROM emp WHERE ename=smith)子查询作为条件 子查询形式为单行多列 4.查询员工编号为 1006 的员工名称、员工工资、部门名

33、称、部门地址 分析:查询列:员工名称、员工工资、部门名称、部门地址 查询表:emp 与 dept,分析得出,不需要外连接(外连接的特性:某一行(或某些行)记录上会出现一半有值,一半为 NULL值)条件:员工编号为 1006 第一步:去除多表,只查一张表,这里去除部门表,只查员工表 SELECT ename,sal FROM emp e WHERE empno=1006 第二步:让第一步与 dept 做内连接查询,添加主外键条件去除无用笛卡尔积 SELECT e、ename,e、sal,d、dname,d、loc FROM emp e,dept d WHERE e、deptno=d、deptno

34、 AND empno=1006 数据库基本知识和基础 sql 语句 第二步中的 dept 表表示所有行所有列的一张完整的表,这里可以把 dept 替换成所有行,但只有 dname 与 loc 列的表,这需要子查询。第三步:查询dept 表中dname 与 loc 两列,因为deptno 会被作为条件,用来去除无用笛卡尔积,所以需要查询它。SELECT dname,loc,deptno FROM dept;第四步:替换第二步中的 dept SELECT e、ename,e、sal,d、dname,d、loc FROM emp e,(SELECT dname,loc,deptno FROM dept)d WHERE e、deptno=d、deptno AND e、empno=1006 子查询作为表 子查询形式为多行多列