数据库基本知识和基础sql语句.docx
《数据库基本知识和基础sql语句.docx》由会员分享，可在线阅读，更多相关《数据库基本知识和基础sql语句.docx（18页珍藏版）》请在冰豆网上搜索。
数据库基本知识和基础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数据类型ment'备注...',
字段名2数据类型ment'备注...',
.... *最后一行不需要逗号
）[表选项];
表选项：
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）notnullment'班级名字',
roomvarchar（10）notnullment'教室号'
）charsetutf8;
--创立子表〔外键表〕createtablestudent（
idintprimarykeyauto_increment,
numberchar（10）notnulluniquement'学号：
itcast+四位数',
namevarchar（10）notnullment'XX',
c_idintment'班级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，或者XX为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 查询XX不为null的学生记录
SELECT*
FROMstu
WHERENOTsnameISNULL;
或者
SELECT*
FROMstu
WHEREsnameISNOTNULL;
3 模糊查询
当想查询XX中包含a字母的学生时就需要使用模糊查询了。
模糊查询需要使用关键字LIKE。
3.1 查询XX由5个字母构成的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'_____';
模糊查询必须使用LIKE关键字。
其中“_〞匹配任意一个字母，5个“_〞表示5个任意字母。
3.2 查询XX由5个字母构成，并且第5个字母为“i〞的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'____i';
3.3 查询XX以“z〞开头的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'z%';
其中“%〞匹配0~n个任何字母。
3.4 查询XX中第2个字母为“i〞的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'_i%';
3.5 查询XX中包含“a〞字母的学生记录
SELECT*
FROMstu
WHEREsnameLIKE'%a%';
4 字段控制查询
4.1 去除重复记录
去除重复记录〔两行或两行以上记录中系列的上的数据都一样〕，例如emp表中sal字段就存在一样的记录。
当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：
SELECTDISTINCTsalFROMemp;
4.2 查看雇员的月薪与佣金之和
因为sal和m两列的类型都是数值类型，所以可以做加运算。
如果sal或m中有一个字段不是数值类型，那么会出错。
SELECT*,sal+mFROMemp;
m列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。
下面使用了把NULL转换成数值0的函数IFNULL：
SELECT*,sal+IFNULL（m,0）FROMemp;
4.3 给列名添加别名
在上面查询中出现列名为sal+IFNULL（m,0），这很不美观，现在我们给这一列给出一个别名，为total：
SELECT*,sal+IFNULL（m,0）AStotalFROMemp;
给列起别名时，是可以省略AS关键字的：
SELECT*,sal+IFNULL（m,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.1COUNT
当需要纵向统计时可以使用COUNT（）。
●查询emp表中记录数：
SELECTCOUNT（*）AStFROMemp;
●查询emp表中有佣金的人数：
SELECTCOUNT（m）tFROMemp;
注意，因为count（）函数中给出的是m列，那么只统计m列非NULL的行数。
●查询emp表中月薪大于2500的人数：
SELECTCOUNT（*）FROMemp
WHEREsal>2500;
●统计月薪与佣金之和大于2500元的人数：
SELECTCOUNT（*）AStFROMempWHEREsal+IFNULL（m,0）>2500;
●查询有佣金的人数，以及有领导的人数：
SELECTCOUNT（m）,COUNT（mgr）FROMemp;
6.2SUM和AVG
当需要纵向求和时使用sum（）函数。
●查询所有雇员月薪和：
SELECTSUM（sal）FROMemp;
●查询所有雇员月薪和，以及所有雇员佣金和：
SELECTSUM（sal）,SUM（m）FROMemp;
●查询所有雇员月薪+佣金和：
SELECTSUM（sal+IFNULL（m,0））FROMemp;
●统计所有员工平均工资：
SELECTSUM（sal）,COUNT（sal）FROMemp;
或者
SELECTAVG（sal）FROMemp;
6.3MAX和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是对分组后数据的约束。
8LIMIT
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;
左连接是先查询出左表〔即以左表为主〕，然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
子查询
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
●子查询出现的位置：
Øwhere后，作为条件的一局部；
Øfrom后，作为被查询的一条表；
●当子查询出现在where后作为条件时，还可以使用如下关键字：
Øany
Øall
●子查询结果集的形式：
Ø单行单列〔用于条件〕
Ø单行多列〔用于条件〕
Ø多行单列〔用于条件〕
Ø多行多列〔用于表〕
练习：
1.工资高于smith的员工。
分析：
查询条件：
工资>smith工资，其中smith工资需要一条子查询。
第一步：
查询smith的工资
SELECTsalFROMempWHEREename='smith'
第二步：
查询高于smith工资的员工
SELECT*FROMempWHEREsal>（${第一步}）
结果：
SELECT*FROMempWHEREsal>（SELECTsalFROMempWHEREename='smith'）
●子查询作为条件
●子查询形式为单行单列
2.工资高于30部门所有人的员工信息
分析：
查询条件：
工资高于30部门所有人工资，其中30部门所有人工资是子查询。
高于所有需要使用all关键字
升级会员 