数据库基本知识和基础sql语句.docx
- 文档编号:4650786
- 上传时间:2022-12-07
- 格式:DOCX
- 页数:19
- 大小:39.60KB
数据库基本知识和基础sql语句.docx
《数据库基本知识和基础sql语句.docx》由会员分享,可在线阅读,更多相关《数据库基本知识和基础sql语句.docx(19页珍藏版)》请在冰豆网上搜索。
数据库基本知识和基础sql语句
数据库的发展历程
●没有数据库,使用磁盘文件存储数据;
●层次结构模型数据库;
●网状结构模型数据库;
●关系结构模型数据库:
使用二维表格来存储数据;
●关系-对象模型数据库;
理解数据库
●RDBMS=管理员(manager)+仓库(database)
●database=N个table
●table:
●表结构:
定义表的列名和列类型!
●表记录:
一行一行的记录!
Mysql安装目录:
●bin目录中都是可执行文件;
●my.ini文件是MySQL的配置文件;
相关命令:
●启动:
netstartmysql;
●关闭:
netstopmysql;
●mysql-uroot-p123-hlocalhost;
Ø-u:
后面的root是用户名,这里使用的是超级管理员root;
Ø-p:
后面的123是密码,这是在安装MySQL时就已经指定的密码;
●退出:
quit或exit;
sql语句
语法要求
●SQL语句可以单行或多行书写,以分号结尾;
●可以用空格和缩进来来增强语句的可读性;
●关键字不区别大小写,建议使用大写;
分类
●DDL(DataDefinitionLanguage):
数据定义语言,用来定义数据库对象:
库、表、列等;
●DML(DataManipulationLanguage):
数据操作语言,用来定义数据库记录(数据);
基本操作
●查看所有数据库名称:
SHOWDATABASES;
●切换数据库:
USEmydb1,切换到mydb1数据库;
●创建数据库:
CREATEDATABASE[IFNOTEXISTS]mydb1;
●修改数据库编码:
ALTERDATABASEmydb1CHARACTERSETutf8
●创建表:
CREATETABLE表名(
列名列类型,
列名列类型,
......
);
●查看当前数据库中所有表名称:
SHOWTABLES;
●查看指定表的创建语句:
SHOWCREATETABLEemp,查看emp表的创建语句;
●查看表结构:
DESCemp,查看emp表结构;
●删除表:
DROPTABLEemp,删除emp表;
●修改表:
1.修改之添加列:
给stu表添加classname列:
ALTERTABLEstuADD(classnamevarchar(100));
2.修改之修改列类型:
修改stu表的gender列类型为CHAR
(2):
ALTERTABLEstuMODIFYgenderCHAR
(2);
3.修改之修改列名:
修改stu表的gender列名为sex:
ALTERTABLEstuchangegendersexCHAR
(2);
4.修改之删除列:
删除stu表的classname列:
ALTERTABLEstuDROPclassname;
5.修改之修改表名称:
修改stu表名称为student:
ALTERTABLEstuRENAMETOstudent;
其他常用命令:
mysql基本操作命令
一、数据库操作
1.新增数据库
create database数据库名字[数据库选项];
数据库选项:
规定数据库内部该用什么进行规范
字符集:
charset具体字符集(utf8)
校对集:
collate具体校对集(依赖字符集)
2.查看数据库
2.1查看所有的数据库
showdatabases;
匹配查询:
showdatabaseslike'pattern'; #pattern可以使用通配符
_:
下划线匹配,表示匹配单个任意字符,如:
_s,表示任意字符开始,但是以s结尾的数据库
%:
百分号匹配,表示匹配任意个数的任意字符,如:
student%,表示以student开始的所有数据库
2.2查看数据库的创建语句
showcreatedatabase数据库名字;
3.修改数据库
数据库名字在mysql高版本中不允许修改,所以只能修改数据库的库选项(字符集和校对集)
alterdatabase数据库名字[数据库选项];
eg:
alterdatabasestucharsetutf8;
4.删除数据库
对于数据库的删除要谨慎考虑,是不可逆的。
dropdatabase数据库名字;
4.选择数据库
use数据库名字;
二、数据表操作(字段)
1.新增数据表
createtable表名(
字段名1数据类型comment'备注...',
字段名2数据类型comment'备注...',
.... #最后一行不需要逗号
)[表选项];
表选项:
1)字符集:
charset/characterset(可以不写,默认采用数据库的)
2)校对集:
collate
3)存储引擎:
engine=innodb(默认的):
存储文件的格式(数据如何存储)
注意:
创建数据表的时候,需要指定要在哪个数据库下创建。
创建方式有隐式创建和显式创建
1)显式创建:
createtable数据库名字.数据表名字
2)隐式创建:
use数据库名字;
2.查看数据表
2.1查看所有的数据表
showtables;
2.2查看表使用匹配查询
Showtableslike‘pattern’; #与数据库的pattern一样:
_和%两个通配符
2.3查看数据表的创建语句
showcreatetable数据表名字;
2.4查看数据表的结构
desc数据表名字;
3.修改数据表
3.1修改表名字
renametable旧表名to新表名;
3.2修改表选项(存储引擎,字符集和校对集)
altertable表名[表选项];
3.3修改字段(新增字段,修改字段名字,修改西段类型,删除字段)
新增字段:
altertable 表名add[column]字段名字数据库类型[位置first/after];
位置选项:
first在第一个字段
after在某个字段之后,默认就是在最后一个字段后面
修改字段名称:
altertable表名change旧字段名字新字段名字字段数据类型[位置];
eg:
altertablestudentnamefullnamevarchar(30) afterid;
修改字段的数据类型:
altertable表名modify字段名字数据类型[位置];
删除字段:
altertable表名drop字段名字;
4.删除数据表
droptable表名;
三、数据操作
1.新增数据
inserintotable表名[(字段列表)]values(值列表);
2.查看数据
select*/字段列表from表名[where条件];
3.修改数据
update表名set字段名=值 where条件;
注意:
使用update操作最好配合limit1使用,避免操作大批量数据更新错误.
4.删除数据
deletefrom表名where条件;
注意:
没有where条件就是默认删除全部数据.
四、列属性(字段)
1.删除主键:
altertable表名dropprimarykey;
2.增加主键:
altertable表名addprimarykey(字段列表); #可以是复合主键
3.删除自增长:
只能通过修改字段属性的方法操作.
4.删除唯一键:
altertable表名dropindex索引名字; #默认的唯一键名字就是字段的本身
5.增加唯一键:
altertable表名adduniquekey(字段列表); #可以是复合唯一索引
五、外键约束
1.创建表的时候增加外键
constraint外键名字foreignkey(外键字段)references父表(主键字段);
eg:
--创建父表(班级表)
createtableclass(
idintprimarykeyauto_increment,
namevarchar(10)notnullcomment'班级名字',
roomvarchar(10)notnullcomment'教室号'
)charsetutf8;
--创建子表(外键表)
createtablestudent(
idintprimarykeyauto_increment,
numberchar(10)notnulluniquecomment'学号:
itcast+四位数',
namevarchar(10)notnullcomment'姓名',
c_idintcomment'班级ID',
--增加外键
foreignkey(c_id)referencesclass(id)
)charsetutf8;
2.创建表之后增加外键
altertable表名addconstraint外键名字foreignkey(外键字段)references父表(主键字段);
eg:
--增加外键
altertablestudentaddconstraintstudent_class_fk foreignkey(c_id)referencesclass(id);
3.删除外键
altertable表名dropforeignkey外键名字; #查看外键名字需要通过表创建语句来查询.
eg:
--删除外键
altertablestudentdropforeignkeystudent_ibfk_1;
数据查询语法(DQL)
DQL就是数据查询语言,数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。
SELECTselection_list/*要查询的列名称*/
FROMtable_list/*要查询的表名称*/
WHEREcondition/*行条件*/
GROUPBYgrouping_columns/*对结果分组*/
HAVINGcondition/*分组后的行条件*/
ORDERBYsorting_columns/*对结果分组*/
LIMIToffset_start,row_count/*结果限定*/
基础查询
1.1 查询所有列
SELECT*FROMstu;
1.2 查询指定列
SELECTsid,sname,ageFROMstu;
2 条件查询
2.1 条件查询介绍
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
●=、!
=、<>、<、<=、>、>=;
●BETWEEN…AND;
●IN(set);
●ISNULL;
●AND;
●OR;
●NOT;
2.2 查询性别为女,并且年龄50的记录
SELECT*FROMstu
WHEREgender='female'ANDge<50;
2.3 查询学号为S_1001,或者姓名为liSi的记录
SELECT*FROMstu
WHEREsid='S_1001'ORsname='liSi';
2.4 查询学号为S_1001,S_1002,S_1003的记录
SELECT*FROMstu
WHEREsidIN('S_1001','S_1002','S_1003');
2.5 查询学号不是S_1001,S_1002,S_1003的记录
SELECT*FROMtab_student
WHEREs_numberNOTIN('S_1001','S_1002','S_1003');
2.6 查询年龄为null的记录
SELECT*FROMstu
WHEREageISNULL;
2.7 查询年龄在20到40之间的学生记录
SELECT*
FROMstu
WHEREage>=20ANDage<=40;
或者
SELECT*
FROMstu
WHEREageBETWEEN20AND40;
2.8 查询性别非男的学生记录
SELECT*
FROMstu
WHEREgender!
='male';
或者
SELECT*
FROMstu
WHEREgender<>'male';
或者
SELECT*
FROMstu
WHERENOTgender='male';
2.9 查询姓名不为null的学生记录
SELECT*
FROMstu
WHERENOTsnameISNULL;
或者
SELECT*
FROMstu
WHEREsnameISNOTNULL;
3 模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。
模糊查询需要使用关键字LIKE。
3.1 查询姓名由5个字母构成的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'_____';
模糊查询必须使用LIKE关键字。
其中“_”匹配任意一个字母,5个“_”表示5个任意字母。
3.2 查询姓名由5个字母构成,并且第5个字母为“i”的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'____i';
3.3 查询姓名以“z”开头的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'z%';
其中“%”匹配0~n个任何字母。
3.4 查询姓名中第2个字母为“i”的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'_i%';
3.5 查询姓名中包含“a”字母的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'%a%';
4 字段控制查询
4.1 去除重复记录
去除重复记录(两行或两行以上记录中系列的上的数据都相同),例如emp表中sal字段就存在相同的记录。
当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用DISTINCT:
SELECTDISTINCTsalFROMemp;
4.2 查看雇员的月薪与佣金之和
因为sal和comm两列的类型都是数值类型,所以可以做加运算。
如果sal或comm中有一个字段不是数值类型,那么会出错。
SELECT*,sal+commFROMemp;
comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。
下面使用了把NULL转换成数值0的函数IFNULL:
SELECT*,sal+IFNULL(comm,0)FROMemp;
4.3 给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为total:
SELECT*,sal+IFNULL(comm,0)AStotalFROMemp;
给列起别名时,是可以省略AS关键字的:
SELECT*,sal+IFNULL(comm,0)totalFROMemp;
5 排序
5.1 查询所有学生记录,按年龄升序排序
SELECT*
FROMstu
ORDERBYsageASC;
或者
SELECT*
FROMstu
ORDERBYsage;
5.2 查询所有学生记录,按年龄降序排序
SELECT*
FROMstu
ORDERBYageDESC;
5.3 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
SELECT*FROMemp
ORDERBYsalDESC,empnoASC;
6 聚合函数
聚合函数是用来做纵向运算的函数:
●COUNT():
统计指定列不为NULL的记录行数;
●MAX():
计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
●MIN():
计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
●SUM():
计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
●AVG():
计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
6.1 COUNT
当需要纵向统计时可以使用COUNT()。
●查询emp表中记录数:
SELECTCOUNT(*)AScntFROMemp;
●查询emp表中有佣金的人数:
SELECTCOUNT(comm)cntFROMemp;
注意,因为count()函数中给出的是comm列,那么只统计comm列非NULL的行数。
●查询emp表中月薪大于2500的人数:
SELECTCOUNT(*)FROMemp
WHEREsal>2500;
●统计月薪与佣金之和大于2500元的人数:
SELECTCOUNT(*)AScntFROMempWHEREsal+IFNULL(comm,0)>2500;
●查询有佣金的人数,以及有领导的人数:
SELECTCOUNT(comm),COUNT(mgr)FROMemp;
6.2 SUM和AVG
当需要纵向求和时使用sum()函数。
●查询所有雇员月薪和:
SELECTSUM(sal)FROMemp;
●查询所有雇员月薪和,以及所有雇员佣金和:
SELECTSUM(sal),SUM(comm)FROMemp;
●查询所有雇员月薪+佣金和:
SELECTSUM(sal+IFNULL(comm,0))FROMemp;
●统计所有员工平均工资:
SELECTSUM(sal),COUNT(sal)FROMemp;
或者
SELECTAVG(sal)FROMemp;
6.3 MAX和MIN
●查询最高工资和最低工资:
SELECTMAX(sal),MIN(sal)FROMemp;
分组查询
当需要分组查询时需要使用GROUPBY子句,例如查询每个部门的工资和,这说明要使用部分来分组。
7.1 分组查询
●查询每个部门的部门编号和每个部门的工资和:
SELECTdeptno,SUM(sal)
FROMemp
GROUPBYdeptno;
●查询每个部门的部门编号以及每个部门的人数:
SELECTdeptno,COUNT(*)
FROMemp
GROUPBYdeptno;
●查询每个部门的部门编号以及每个部门工资大于1500的人数:
SELECTdeptno,COUNT(*)
FROMemp
WHEREsal>1500
GROUPBYdeptno;
HAVING子句
●查询工资总和大于9000的部门编号以及工资和:
SELECTdeptno,SUM(sal)
FROMemp
GROUPBYdeptno
HAVINGSUM(sal)>9000;
注意,WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。
8 LIMIT
LIMIT用来限定查询结果的起始行,以及总行数。
8.1 查询5行记录,起始行从0开始
SELECT*FROMempLIMIT0,5;
注意,起始行从0开始,即第一行开始!
8.2 查询10行记录,起始行从3开始
SELECT*FROMempLIMIT3,10;
8.3 分页查询
如果一页记录为10条,希望查看第3页记录应该怎么查呢?
●第一页记录起始行为0,一共查询10行;
●第二页记录起始行为10,一共查询10行;
●第三页记录起始行为20,一共查询10行;
多表连接查询
●连接查询
Ø内连接
Ø外连接
✧左外连接
✧右外连接
✧全外连接(MySQL不支持)
Ø自然连接
●子查询
连接查询
连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积,假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的,那么怎么去除重复的,不想要的记录呢,当然是通过条件过滤。
通常要查询的多个表之间都存在关联关系,那么就通过关联关系去除笛卡尔积。
2.1 内连接
上面的连接语句就是内连接,但它不是SQL标准中的查询方式,可以理解为方言!
SQL标准的内连接为:
SELECT*
FROMempe
INNERJOINdeptd
ONe.deptno=d.deptno;
内连接的特点:
查询结果必须满足条件。
例如我们向emp表中插入一条记录:
其中deptno为50,而在dept表中只有10、20、30、40部门,那么上面的查询结果中就不会出现“张三”这条记录,因为它不能满足e.deptno=d.deptno这个条件。
2.2 外连接(左连接、右连接)
外连接的特点:
查询出的结果存在不满足条件的可能。
左连接:
SELECT*FROMempe
LEFTOUTERJOINdeptd
ONe.deptno=d.deptno;
左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 基本知识 基础 sql 语句