mysql数据库
本帖最后由 漆慧芹 于 2021-11-27 20:18 编辑一、常见sql语句：
1、安装mysql数据库客户端：yum install mysql
2、安装mysql数据库服务端：yum install mysql-server
3、启动mysql数据库：service mysql start
4、停止mysql数据库：service mysql stop
5、查看mysql数据库状态：service mysql status
6、重启mysql数据库：service mysqld restart
7、给root用户设置访问密码mysqladmin -uroot password '123456'
8、直接带密码方式访问mysql数据库：
mysql -uroot -p123456
9、查看mysql数据库里面所有的数据库：show databases；
10、创建一个名为dcs10的库：create database dcs10；
删除名为dcs11的库：drop database dcs11；
11、进入到dcs10库里：use dcs10；
12，查看进入的库里的所有表：show tables；
13、创建一个名为test表：
create table test（id int（20）primary key auto_increment，name varchar(20) not null,score float(20,2),phone bigunt(20) default 15366669999,time date）；
删除一个为test1的表：drop table test1；
14、将test表名改为test1：alter table test rename test1；
15、把test表中id字段改为sid字段：alter table test change id sid int(20)；
16、给sid字段加自增长约束：alter table change sid sid int(20)auto_increment；
给sid字段删除自增长约束：alter table change sid sid int(20)；
给sid字段添加主键和自增长约束：alter table test change sid sid int(20) primary key auto_increment;
删除test表主键约束：alter table test drop primary key;
17、给test表添加class字段放在最前面：alter table test add class int(20) first；
18、添加一个字段sex放在class后面：alter table test add sex varchar(20) after class；
19、删除sex字段：alter table test drop sex;
20、同时添加sex和age字段默认放在表字段最后面： alter table test add(sex varchar(20),age int(20));
21、同时删除sex和age两个字段：alter table test drop sex,drop age;
22、将name字段放在class字段后面:alter table test modify name varchar(20) after class;
23、给test表所有字段都插入数据：
insert into test(sid,name,score,phone,time)values(1,'xiaoyi',2021,66.666,17633335555,'2021-11-21');
24、查看test表所有数据，* 号表示所有字段：select * from test；
25、给sid字段插入值1：insert into test(sid)values(1);
26、单独给test表name字段插入值xiaoer：insert into test(name)values('xiaoer');
27、给name字段增加不能为空的属性：alter table test change name varchar(20) not null;
28、查看test表结构：desc test；
mysql> desc test; 查看test表的表结构
+-------+-------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------+----------------+
| id | int(20) | NO | PRI | NULL | auto_increment |
| name| varchar(20)| NO | | NULL | |
| score | float(20,2) | YES | | NULL | |
| phone | bigint(20) | YES | | 15366669999 | |
| time | date | YES | | NULL | |
+-------+-------------+------+-----+-------------+----------------+
Field：字段 id name score phone time
Type：
类型为 int 和 bigint 整型
char 和 varchar 存储数据的时候都需要加上单引号或者双引号比如：'小周'
float：浮点型，默认保存6位 float（20，2）保留小数点后面2位
date：日期，存储日期数据也需要加引号
对表的数据进行增删改查操作
增： insertinto
删除：delete from
改：update表名称set
查：select ...from 表名
create database创建数据库
create table创建表
alter table修改表
drop 删除
退出MySQL数据库交互界面的方式：
Ctrl+c
Ctrl+z
mysql> quit; 回车(在MySQL数据库交互界面输入的命令后面都必须加上 ;分号)
mysql> exit; 回车
二、单表查询语句：
1、查询表中所有数据：
select*from+表名；
2，查询某个字段的数据：
select字段from+表名；
如 selectnamefromstudent； 查询student表里所有的姓名
3、查询多个字段的数据：
select 字段1，字段2 from+表名；
如selectname，class fromstudent；查询student表里的姓名和班级
4、查询满足某个条件的所有数据（where后面接满足的条件）
select*from表名 where字段=值；
如select* from student whereclass=1833；查询班级为1833的所有信息
5、查询不满足某个条件的数据（！=和<>表示不等于）
select * from 表名where字段！=值；
select* from student whereclass！=1833；查询班级不为1833所有信息
6、查询两个条件同时满足的数据（and是都必须满足）
select* from表名where 条件1 and条件2；
如select*from student where name='zhangsan'and class=1833；
查询班级为1833的张三的所有信息
7、查询满足至少一个条件数据（or表示至少满足一个）
select * from 表名where条件1 or 条件2；
8、查询一个条件范围内的数据（between....and）
select * from 表名where 字段between20and30；
select* fromstudent where agebeteen25and31；查询年龄在25到31之间的信息，包含25和31
9、查询字段满足在指定集合中的数据
select* from表名 where 字段in(值1，值2，值3)；（功能和or一样）
select* fromstudentwhere classin(1833，1834)；查询班级是1833或1834的所有信息
10、查询字段不满足在指定集合中的数据
select* from 表名 where 字段notin(值1，值2，值3)；
select* fromstudentwhere classnotin(1833，1834)；查询班级不是1833或1834的所有信息
11、查询字段值为空的数据（字段为空不能写成 字段=null）
select*from表名 where字段isnull；
12、查询某个字段模糊匹配成功的数据（%用来匹配结尾和开头）
select*from表名 where字段like"%值%"；
如select* fromstudentwherename like'xiao%'；查询姓名为xiao开头的信息
13、查询第几行到第几行的数据（m值为从第几行开始-1，n为最后行数-m）
select*from表名where 字段limitm,n；
select* fromstudentwhereid limit5；查询前5行
select* fromstudentwhereid limit1，4； 查询2到5行数据
14、查询的数据根据某个字段从小到大排序（order ...by ...asc）
select*from表名orderby 字段asc；
如select* fromstudentorderbyageasc；对年龄进行从小到大排序
15、查询的数据根据某个字段从大到小排序（order ...by ...desc）
select*from表名orderby 字段desc；
如select* fromstudentorderbyagedesc；对年龄进行从大到小排序
16、查询的数据根据每个字段进行分组（group by....）
select*from 表名group by字段；
如selectsum(math) fromstudentgroup byclass;查询每个班级的数学总分
17、查询的数据根据每个字段进行分组在条件过滤（having跟在groupby 后面相当于where）
select*from 表名group by字段having条件；
如selectclass，sum(math)fromstudentgroupbyclasshavingsum(math)>100;查询每个班级数学总分大于100的班级和总分
三、多表查询
基本连接:select * from aa,cc where aa.id=cc.s_id;
硬连接：select * from aa union select * from cc；
内连接：select * from aa inner join cc on aa.id=cc.s_id;
左连接：select * from aa left join cc on aa.id=cc.s_id;
右连接：select * from aa right join on aa.id=cc.s_is;
求张三的成绩：
1、select name,score from (select * from aa,cc where aa.id=cc.s_id)t where name='zhangsan';
2、select name,score from aa,cc where aa.id=cc.s_id and name='zhangsan';
3、select score from cc where s_id=(select id from aa where name='zhangsan');
4、select score from cc where s_id in (select id from aa where name='zhangsan');
四、MySQL增删改语句：
1、表中插入数据
insertinto表名 values(字段1value，字段2values，字段3values....)
2、一次性表中插入多条数据
insertinto表名 values(字段1value，字段2values，字段3values....)，(字段1value，字段2values，字段3values....)
3、对表中指定字段插入数据
insertinto表名 (字段1，字段2)values(字段1，字段2)；
4、删除表中指定数据
deletefrom 表名 where 条件
5、删除表
drop +表名 truncate +表名
6、修改/更新表中指定字段数据
update +表名set字段名=值 where条件；
五、备份表，备份数据，备份数据库，还原数据库
1、 备份表，创建一个表与某个表相同
createtable表1like表2
2、备份数据，把一个表插入到另一个表
insertinto表名 select * from表名
3、备份数据，把某个字段插入备份的表中
insertinto表1(字段1，字段2) select 字段1，字段2 from 表2
4、备份数据
mysqldump-uroot-p数据库名>脚本名
5、还原数据库
mysql-uroot-p 数据库<脚本名
六、聚合函数：
1、统计查询数据的数量
selectcount(*)from表名；
selectcount(*) fromstudent;统计student表数据的行数
2、查询某个字段求和
selectsum(字段)from表名；
如 selectsum(mast)fromstudent;查询student表数学成绩总分
3、查询某个字段平均值
selectavg(字段)from表名；
如selectavg(mast)fromstudent;查询student表数学平均成绩
4、查询某个字段最大值
selectmax(字段)from表名；
如selectmax(mast)fromstudent;查询student表最大数学成绩
5、查询某个字段最小值
selectmin(字段)from表名；
如selectmin(mast)fromstudent;查询student表最小数学成绩
6、对某个字段进行去重
select distinct(字段)from表名；
如select distinct(sex)fromstudent；对学生表里的sex字段进行去重操作
七、数据库的用户权限操作
如何给普通的数据库用户赋予相应的权限（增、删、改、查）
1、进入mysql数据库：usemysql；
2、查询MySQL数据库已经创建了那些用户：select host ，usefromuser；
3、创建用户但是未授权(方法一)
insertintouser (host,user,password)values('localhost','wang',password('123456'))；
创建用户后都需要刷新一下：flush privileges；
4、创建用户后进行授权（方法二）
grantselect，update，delete，drop on *.* to'wang'@'localost'identifiedby'123456'
5、创建用户同时授权
grant allprivileges on*.*to'xiaowang'@'%'identifiedby'123456';
授予一个普通用户xiaowang及密码为123456，允许其可以通过所有客户机访问本数据库下所有的库及所有的表，假如为localhost只能在本地进行访问
6、flush privileges；刷新数据库
7、showgrants for'wang'@'%'；查看数据库的指定用户的权限
revoke all on *.* from'dcs'@'%'；取消所有权限
deletefromuserwhereuser='zhongguo' and host='localhost'；删除用户
update userset password=password('123456')whereuser='root'；修改root用户的密码
8、查询数据库版本：selectversion();
查询数据服务器当前访问的时间：selectnow();
查询当前使用的是哪个库：select database();
查询当前登录用户：selectuser();
八、MySQL管理工具 Navicat
linux 系统root 用户设置数据库初始密码 ：
MySQL -urootpassword 123456#root 用户设置访问密码
mysql> usemysql;
mysql>grant all privileges on *.*to root@'%'identified by '123456'; #赋予最高权限
mysql> flush privileges#刷新
C:\Users\Administrator\AppData\Local\YNote\data\qqB05BC596BE5CA70950A29DE9A6BFCD35\dfeb6fc45a2d42769c98101272629e76\f8a2567b76bf7755ebdd80bdbad330cd.png
九、常见的约束有哪些？
1、非空约束 not null，表示当前这个字段对应的值不能为空
2、主键约束 primarykey，用来约束主键对应的值不能重复，默认不能为空
3、外键约束 foreign key，表与表之间建立的约束
4、默认值约束default，当往这个表中插入数据的时候不给phone字段插入新的值，则默认用15366669999
5、自增长约束auto_increment，当往这个表中插入数据不给id插入值，id字段会自动加1
十、索引
1、什么是索引
一个索引是存储在表中的数据结构，索引在表的列名上创建。索引中包含了一个列的值，这些值保存在一个数据结构中。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后随指针到达包含该值的行。
重点:索引是一种结构
2、索引的作用：可以利用索引快速访问数据库中的特定信息
3、索引分类：
1）、普通索引
2）、唯一索引
3）、主键索引
4、给单个字段创建索引：
show tables；查看当前数据库有哪些表
select * from dcs；查看dcs表中所有数据
desc dcs；查看dcs表的结构
create index tt on dcs(sid)；#给单个字段创建普通索引
create index yy on dcs(sid,class);#同时创建两个字段的普通索引索引
show index from dcs； #查看dcs表中创建了哪些索引
普通索引：这是最基本的索引，它没有任何限制
创建索引：create index tt on dcs(sid); #给id创建索引
查索引：show index from dcs #查看dcs表中创建了哪些索引
删除索引：alter table dcs drop index tt；#删除tt普通索引
唯一索引：它在前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值
创建索引：create unique index aa on dcs(sid)
查索引：show index from dcs
删除索引：alter table dsc drop index aa;
主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
创建索引：alter table dcs add primary key(id);
查索引：show index from dcs
删除索引：alter table dcs drop primary key
步骤：第一步：先删除自增长约束 alter table student change id id int(4);
第二步：再删除主键alter table student drop primary key
第三步：查看有哪些索引show index from student;
第四步：给math字段创建主键约束alter table student add primary key(math);
第五步:查看表结构desc student；
十一、数据库视图
1、什么是视图：
视图是一个虚拟的表，它不在数据库中以存储数据的形式保存，是在使用视图的时候动态生成的。
2、视图的特点
1）、视图是由基本表产生的虚表
2）、视图的更新和删除会影响基础表
3）、基础表的更新和删除也会影响到视图
3、视图的作用
对视图的操作与对基本表的操作都是一样的 （包括alter，create，insert into，update，delete，select），且二者任意一方的字段值被修改，都会实时影响到对方（如修改view的字段值，会同步修改table相应的字段值）；但是视图的创建是基于基本表的，它的作用可以对基本表的敏感信息进行保护，在实际工作中，处于安全考虑，将用户的信息创建成视图给用户调用，避免了直接操作基本表。
步骤：
select * from studen；
create view dcs as(select id,class,math from student);#创建一个视图表dcs，把student表的id、class、math字段作为视图表字段
show tables；查看所有表（包括视图表）
show create view dcs；查看视图结构
select * from dcs;查看视图表的数据
delete from dcs where id=10；删除视图表数据，视图表和基本表同步被删除
update dcs set math=99 where id=1；更新视图表数据，基本表同步被修改
update student set math=100 where id=1；更新基本表数据，视图表和基本表同步更新
drop view dcs；删除视图表
show create view dcs；删除之后查看视图结构，提示不存在了
十三、存储过程
1、什么是存储过程
存储过程是完成特定功能的sql语句集合，通过编译后存储在数据库中，通过指定的存储过程名称调用执行它。
存储过程=sql语句集合+控制语句
2、使用存储过程的优点：
1）、存储过程创建可以多次调用，不需要重新编写存储过程语句
2）、假如要往表中插入大量数据，用insert into 语法执行速度太慢，效率较低，那么这个时候可以通过编写存储过程，把需要插入的数据sql集合放入存储过程的函数体当中，创建存储过程，下次即可调用
3）、存储过程加快程序的运行速度
4）、存储过程增加sql语句的功能和灵活、创建的存储过程可以重复使用
3、网存储过程中加while语句（循环语句）
while 语句的格式
while条件 do
执行循环体（sql）
end while；
注意：什么时候进入循环：当条件成立时，进入循环
什么时候退出循环：当条件不成立时，退出循环
总结：主键索引和主键约束其实是绑在一起的，如果有了主键索引，那么必会产生主键约束，或者说主键约束是主键索引的一个特征。
页:
[1]