mysql数据库命令.docx
- 文档编号:27083356
- 上传时间:2023-06-26
- 格式:DOCX
- 页数:18
- 大小:588.87KB
mysql数据库命令.docx
《mysql数据库命令.docx》由会员分享,可在线阅读,更多相关《mysql数据库命令.docx(18页珍藏版)》请在冰豆网上搜索。
mysql数据库命令
1.对数据库常用命令
1.连接数据库
mysql-u用户名-p密码
2.显示已有数据库
showdatabaxxxxses;
3.创建数据库
createdatabaxxxxsesqlname;
4.选择数据库
usedatabaxxxxsesqlname;
5.显示数据库中的表(先选择数据库)
showtables;
6.显示当前数据库的版本信息以及连接用户名
selectversion()user();
7.删除数据库(删除时没有提示直接删除)
dropdatabaxxxxsesqlname;
2.数据库中对表的命令
1.创建表
(1)语法:
createtabletablename(
字段1数据类型字段属性
…
字段n
);
(2)注意:
1.创建表时为了防止与保留字冲突,用’'括起来
2.单行注释:
#…
多行注释:
/…/
3.创建表时多字段中间用英文逗号隔开,最后一行不用逗号。
(3)字段约束和属性
1.非空约束notnull(字段不允许为空)
2.默认约束default(设置默认值)
3.唯一约束uniquekey(uk)(设置字段的值是唯一的,可为空,但只能有一个空值)
4.主键约束primarykey(pk)(作为表记录的唯一标识)
5.外键约束foreignkey(fk)(用于两个表之间建立关系,需要指定引用主表的哪一字段。
在数据库的存储引擎中InnoDB支持外键,MyISAM不支持外键。
作为外键的字段要求是主表中的主键(单字段主键))
添加外键约束:
CONSTRAINTFK_外键名FOREIGNKEY(字表中外键字段)REFERENCES关联表名(关联字段)。
grandid作为字表的外键
1.设置自增auto_increment=n从n开始。
2.设置自增set@@auto_increment_increment=m步长为m。
3.多字段设置主键:
primarykey(字段1,字段2…字段n)4.表中的注释/说明性文字:
)comment=“说明文字”;
5.设置字符集:
)charset=“字符集”;
6.查看表的结构:
describe’表名’/desc表名
7.查看数据库定义:
showcreatedatabaxxxxsesqlname;
8.查看数据表定义:
showcreatetabletablename;
9.查看默认存储引擎:
showvariableslike’storage_engine%’;
11.指定表的存储引擎:
)engine=存储引擎;
10.删除表:
droptable‘tablename’;
11.获取当前日期:
now();
12.修改表:
(1)修改表名:
altertable旧表名rename新表名;
(2)添加字段:
altertable表名add字段名数据类型…;(添加新的字段)
(3)修改字段:
altertable表名change原字段名新字段名数据类型…;
(4)删除字段:
altertable表名drop字段名;
(5)在创建完表以后添加主键约束:
altertable表名addconstraint主键名primarykey表名(主键字段);
(6)创建完表以后添加外键约束(作为外键的字段要求是主表中的主键(单字段主键)):
altertable表名addconstraint外键名foreignkey(外键字段)references关联表名(关联字段);
插入数据
1.插入单行数据:
insertinto表名(字段名列表(逗号隔开))values(值列表(逗号隔开));
2.插入多行数据:
insertinto表名(字段名列表)values(值列表1)…(值列表n);
3.将查询结果插入到新表中:
createtable新表(select字段1…from原表);
查询student表中的id,name,sex,phone数据插入到newstudent表中:
CREATETABLEnewstudent(SELECTid`name`sexphoneFROMstudent);
3.更新数据(修改数据):
update表名set列名=更新值where更新条件;
修改newstudent表中id=1001的数据名字为tom:
UPDATEnewstudentSET`name`='tom'WHEREid=1001;
4.删除数据
(1)deletefrom表名where删除条件;
delete删除的是整条数据,不会只删除单个列。
删除newstudent表中名字为tom的数据:
DELETEFROMnewstudentWHERE`name`='tom';
(2)truncatetable删除数据:
truncatetable删除的是表中所有的行,但表的结构,列,约束,索引等不会改变。
不能用于有外键约束的表。
删除数据不能恢复。
truncatetable表名where删除条件;
数据查询
1.使用select查询
select列名/表达式/函数/常量from表名where查询条件orderby排序的列名asc/desc;
(1)查询所有的数据行和列:
select*from表名;
(2)查询部分行和列:
select列名…from表名where查询条件;
(3)在查询中使用列的别名:
select列名AS新列名form表名where查询条件;
计算,合并得到新的列名:
select列名1+’.’+列名2AS新列名from表名;
(4)查询空值:
通过isnull或者isnotnull判断列值是否为空
查询student表中Email为空的学生姓名:
SELECT`name`FROMstudentWHEREEmailISNULL;
2.分组查询
#查询不同课程的平均分,最低分,最高分并查询出平均分大于80分的课程
SELECTr.subjectnosub.`SubjectName`课程名称AVG(StudentResult)平均分
MAX(StudentResult)最高分MIN(StudentResult)最低分
FROMresultrINNERJOIN`subject`sub
ONr.`SubjectNo`=sub.`SubjectNo`
GROUPBYr.subjectno
#whereAVG(StudentResult)>=80出现错误,
#分组查询groupby在where语句后,
#groupby约束条件使用having语句
HAVINGAVG(StudentResult)>=80;
∙
常用函数
1.聚合函数:
(1)AVG(平均值):
selectavg(列名)from表名
假设列名为成绩则查询到的是表中所有成绩的平均值。
(2)count返回某字段的行数
(3)max返回某字段的最大数
(4)min返回某字段的最小值
(5)sum返回某字段的和。
2.字符串函数:
(1)concat()连接字符串s1s2…sn为一个完整的字符串。
(2)insert(s1p1nnews)将字符串s1从p1位置开始,n个字符长的字串替换为字符串news。
(3)lower(s)将字符串s中的所有字符改为小写。
(4)upper(s)将字符串s中的所有字符改为大写。
(5)substring(snumlen)返回字符串s的第num个位置开始长度为len的子字符串。
3.时间日期函数:
(1)获取当前日期:
curdate();
(2)获取当前时间:
curtime();
(3)获取当前日期和时间:
now();
(4)返回日期date为一年中的第几周:
week(date);
(5)返回日期date的年份:
year(date);
(6)返回时间time的小时值:
hour(time);
(7)返回时间time的分钟值:
minute(time);
(8)返回日期参数(date1和date2之间相隔的天数):
datediff(date1date2);
(9)计算日期参数date加上n天后的日期:
adddate(daten);
4.数学函数
(1)返回大于或等于数值x的最小整数:
ceil(x);
(2)返回小于或等于数值x的最大整数:
floor(x);
(3)返回0~1之间的随机数:
rand();
orderby子句
orderby子句按照一定的顺序排列查询结果,asc升序排列,desc降序排列。
limit子句
显示指定位置指定行数的记录。
select字段名列表form表名where约束条件groupby分组的字段名orderby排序列名limit位置偏移量行数;
#查询学生信息里gid=1按学号升序排列前四条记录
#查询学生信息里gid=1按学号升序排列前四条记录(步长)
SELECTid`name`FROM`student1`WHEREgid=1ORDERBYidLIMIT4;
(查询表里全部信息中gid=1的前四个学生)
查询结果:
#查询学生信息里gid=1按学号升序排列前四条记录(位置偏移量,步长)
SELECTid`name`FROM`student1`WHEREgid=1ORDERBYidLIMIT44;
(查询表中全部信息gid=1前四条以后的全部信息中的前四条学生信息)
查询结果:
模糊查询
in子查询******notin子查询
使用in关键字可以使父查询匹配子查询返回的多个单字段值。
解决使用比较运算符(=>等),子查询返回值不唯一错误信息。
like模糊查询
LIKE语句语法格式:
select*from表名where字段名like对应值(子串)。
它主要是针对字符型字段的,它的作用是在一个字符型字段列中检索包含对应子串的。
A:
%包含零个或多个字符的任意字符串:
1、LIKE’Mc%’将搜索以字母Mc开头的所有字符串(如McBadden)。
2、LIKE’%inger’将搜索以字母inger结尾的所有字符串(如Ringer、Stringer)。
3、LIKE’%en%’将搜索在任何位置包含字母en的所有字符串(如Bennet、Green、McBadden)。
B:
_(下划线)任何单个字符:
LIKE’_heryl’将搜索以字母heryl结尾的所有六个字母的名称(如Cheryl、Sheryl)。
C:
[]指定范围([a-f])或集合([abcdef])中的任何单个字符:
、
1,LIKE’[CK]ars[eo]n’将搜索下列字符串:
Carsen、Karsen、Carson和Karson(如Carson)。
2、LIKE’[M-Z]inger’将搜索以字符串inger结尾、以从M到Z的任何单个字母开头的所有名称(如Ringer)
***D:
[^]不属于指定范围([a-f])或集合([abcdef])的任何单个字符:
LIKE’M[^c]%’将搜索以字母M开头,并且第二个字母不是c的所有名称(如MacFeather)。
E:
它同于DOS命令中的通配符,代表多个字符:
cc代表cccBccbccabdfec等多个字符。
F:
?
同于DOS命令中的?
通配符,代表单个字符:
b?
b代表brbbFb等
G:
#大致同上,不同的是代只能代表单个数字。
k#k代表k1kk8kk0k。
F:
[!
]排除它只代表单个字符
下面我们来举例说明一下:
例1,查询name字段中包含有“明”字的。
select*fromtable1wherenamelike‘%明%’
例2,查询name字段中以“李”字开头。
select*fromtable1wherenamelike'李’
例3,查询name字段中含有数字的。
select*fromtable1wherenamelike‘%[0-9]%’
例4,查询name字段中含有小写字母的。
select*fromtable1wherenamelike‘%[a-z]%’
例5,查询name字段中不含有数字的。
select*fromtable1wherenamelike‘%[!
0-9]%’
可以自定义转移符----》escape’自定义转移符’
distinct------》去除重复项
between*and模糊查询
操作符BETWEEN…AND会选取介于两个值之间的数据范围。
这些值可以是数值、文本或者日期。
null,notnull查询
--查询手机号不为null的用户数据
SELECT*fromuserwherephoneisnotnull;
--查询手机号为null的用户数据
SELECT*fromuserwherephoneisnull;
exists子查询notexists子查询
exists子查询用来确认后边的查询是否继续进行
droptableifexiststest—>判断是否存在表test,如果存在就删除。
notexists实现取反操作。
对不存在对应查询条件的记录。
多表连接查询
多表连接查询是通过各个表之间共同列的关联性来查询数据。
1.内连接查询
内连接查询根据表中共同的列进行匹配。
取两个的表的交集。
两个表存在主外键关系是通常使用内连接查询。
内连接使用innerjoin…on关键字或者where子句来进行表之间的关联。
inner可省略on用来设置条件。
(1)在where子句中指定连接条件
(2)在from中使用innerjoin…on关键字
#查询学生姓名和成绩
SELECTstudentnamestudentresultFROMstudentsresultr
WHEREs.`StudentNo`=r.`StudentNo`
#在from中使用innerjoin....on关键字
SELECTs.`StudentName`r.`StudentResult`r.`SubjectNo`FROMstudents
INNERJOINresultrONs.`StudentNo`=r.`StudentNo`
两种方法查询结果相同。
2.外连接查询
外连接查询中参与连接的表有主从之分,已主表的每行数据匹配从表的数据列,将符合连接条件的数据直接返回到结果集中,对不符合连接条件的列,将被填上null值再返回到结果集中。
(1)左外连接查询
leftjoin…on或者leftouterjoin…on关键字进行表之间的关联。
SELECTs.`StudentName`r.`StudentResult`r.`SubjectNo`FROMstudents
LEFTJOINresultrONs.`StudentNo`=r.`StudentNo`
将没有成绩的学生成绩查出。
(2)右外连接查询
右外连接包含右表中所有的匹配行,右表中有的项在左表中没有对应的项将以null值填充。
rightjoin…on或rightouterjoin…on关键字进行表之间的关联。
(3)自连接
把一个表作为两个表使用。
#创建一个表
CREATETABLEbook(
idINT(10)
sortINT(10)
booksVARCHAR(10)NOTNULL
);
#插入数据
INSERTINTObookVALUES(21'古文书')
(31'现代书')
(42'《三字经》')
(52'《唐诗三百首》')
(63'《我与地坛》')
(72'《游大林寺》')
(82'《王右军年减十岁时》')
(93'《致橡树》');
#查询结果为:
#书籍类型书籍名
#古文书三字经....
#现代书我与地坛....
SELECTa.books书籍类型b.books书籍名
FROMbookabookb
WHEREa.id=b.sort;
自连接查询结果:
MySQL的事务,视图,索引,备份和恢复
1.事务
事务是指将一系列数据操作捆绑成为一个整体进行统一管理。
把所有的命令作为一个整体一起向系统提交或者撤销造组偶请求。
事务属性:
原子性,一致性,隔离性,持久性。
myISA存储引擎不支持事务。
关闭事务自动提交:
setautocommit=0;
(1)开始事务:
begin/starttransaction;
(2)提交事务:
commit;
(3)回滚/撤销事务:
rollback;
恢复自动提交:
setautocommit=1;
设置结果集以?
?
编码格式显示:
setnames?
?
;
2.视图
视图是一种查看数据库中一个或多个表中数据的方法。
视图是一种虚拟表,作为来自一个或多个表的行或列的子集创建的。
视图充当查询中的表筛选器的角色。
(1)创建视图:
createview视图名as
(2)删除视图:
dropview视图名;
(3)查看视图数据:
select。
。
。
。
。
from视图名;
3.索引
索引类似于书的目录,使用索引可以将数据库程序无须对整个表扫描就可以在其中找到所需数据。
(1)普通数据:
允许重复和空值。
(2)唯一索引:
不允许出现重复。
可以有多个唯一索引。
(3)主键索引:
非空,唯一。
删除时dropprimarykey;
(4)复合索引:
将多个列组合作为索引。
?
(5)全文索引:
可重复和空值,在char,varchar,text创建。
wherematch(列名)against(‘查找内容’);
(6)空间索引:
对空间数据类型的列建立的索引。
创建索引:
create【索引类型】index索引名on表名(创建索引的列);
或者创建表时之间在列后面加上索引类型。
或者修改表altertable表名addindex索引名(索引列);
删除索引:
dropindex索引名;
查看索引:
showindexfrom表名;
4.数据库备份和恢复
1.使用mysqldump命令备份数据库
mysqldump-u-p数据库名>备份数据库位置及名字;
表数据导出到文本文件
select*from表名where查询条件intooutfile备份数据库位置及名字;
2.使用mysql命令恢复数据库(先创建新的数据库)
mysql-u-p新创建数据库名<所要恢复数据库位置及文件名;
source命令恢复数据库
source数据库备份文件;
新建用户
#创建本地用户
CREATEUSER`user`@`localhost`IDENTIFIEDBY'123123';
#用户可登陆任何远程主机,使用通配符%
CREATEUSER`user2`@`123%`IDENTIFIEDBY'123123';
#对用户进行全部权限授权
GRANTALLONmysql.`user`TO`user2`@`123%`;
#对已创建的用户授权
GRANTSELECTINSERTONmysql.`user`TO`user2`@`123%`;
#创建用户时授权
GRANTSELECTINSERTONmysql.`user`TO`user_2`@`123%`IDENTIFIEDBY'123123';
#删除用户user2(使用删除语句时必须拥有数据库全局权限或select权限)
DROPUSER`user2`@`123%`;
DROPUSER`user_2`@`123%`;
DROPUSER`user`@`localhost`;
#mysqladmin修改超级用户user2账户密码(mysqladmin命令在cmd中使用,只能修改超级用户密码)
mysqladmin-uroot-pPASSWORD"123456";
#修改当前登录用户密码
SETPASSWORD=PASSWORD("123456");
#修改其他用户密码
SETPASSWORDFOR`user2`@`123%`=PASSWORD("123456");
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 数据库 命令