mysql笔记
在虚拟机中安装mysql1.在线安装：yum install mysql -y :安装mysql的客户端yum install mysql -service rpm -ivh +对应的rpm包rpm -ivh +对应的rpm包文件 -i ：安装软件包v ：可视化，可以提供更多的安装信息h ：显示安装进度rpm -qa|grep 'mysql'rpm -qa ：显示当前linux系统已经安装的第三方工具 启动mysql服务1. service mysqld start 2. 查看mysql服务的运行状态service mysqld staus 3. 关闭mysql服务service mysqld stop 4. 重启mysql服务service mysqld restart 登录mysql ！第一次登录不需要密码登录mysql ： mysql -uroot -p密码 查看当前所有数据库 ：show databases； 进入对应数据库：use 库名; 查看表 ： show tables; 创建数据库：create database +库名; 查看在哪个库进行操作：select database（）； 创建表：create table 表名(字段名1 字段属性,字段名2 字段属性,....); 常见的约束：primary key : 主键约束，一个表中只能有一个主键字段，主键字段约束了这个字段中的值不能重复，也不能为空。auto_increment ：自增长约束，它只能跟着主键约束去使用，一般是在 整数型的字段中使用。eg : 不输入id时，直接跟着原来最大的值 自增长。not null ：非空约束，不管是否填写内容，他都是会占用对应资源，不会显示为null（啥也没有）default ：默认值约束，插入数据时，不填值时，会默认填充默认值。常见数据类型：int : 整数型 整型，计算机可以保存的数据长度为10个byte，只能保存数字4个字节，保存数据范围-2^31~2^31(±2147483647)，当要保存超过10位数的整数，就要用bigint0000-0000-0000-0000-0000-0000-0000-0000↑ ：表示正负，0是+ ，1是-bigint（20）：大整数类型，当保存的数据大于21474836347就要使用bigint字符串：char（20）：定长字符串，固定占255个字节，不管括号中写多少个字节，保存时都是固定占255个字节的，但是现实的时候是按照括号中的字节数现实。varchar（20） ：可变长字符串，计算机可以保存20byte（字节），一个英文字母占一个字节，utf-8的汉字占3个字节，gbk汉字占2个字节。(节省资源)浮点数：float（20,2）：浮点数，保存小数，（20,2）20-->可以保存数据的长度为20个字节，2-->保留小数点后两位。本质上占4个字节空间。double（xx，x）：浮点数，可以保存更大的浮点数，占8个字节空间。日期：data ：用来保存日期：固定格式YYYY-MM-DD ！面试题：char和varchar区别两者都是保存字符串的，char是定长的（255字节），varchar是可变长的（0~2^16个字节）。char的查询性能会更好，但是不节约磁盘资源，varchar性能差点但是能节省资源。 desc 表名：查看表的表结构desc 表名； 修改表结构： alter 表名；alter table 原名 rename 新名； 修改字段名 ：alter table test change id sid int（4）；！主键不会通过change被修改，但是auto_increment会被修改 修改表中字段属性：alter table test change sid sid int（4） auto_increment； ---增加自动增长 增加表中字段：alter table test add class int（4）； 在表中增加多个字段：alter table test add（tel int（10），age varchar（5））； 增加表中字段指定在表最前面：alter table test add sex varchar（5）first； 增加表中字段指定在 某个字段后：alter table test add age int（4）after name； 删除表中字段：alter table test drop sex； 删除表中多个字段：alter table test drop age，drop name； 改变表中字段位置：alter table test change age age int（4）after name； ↑ ↑ 修改字段名 修改表中字段位置：alter table test modify class bigint(8) after sid;↑不能修改字段名 ！change和modify的区别 两者都可以改变表结构，但是使用change时字段是要写两次的，modify则只要写一次就行。change可以对字段名进行重命名，modify则不能对字段名进行重命名。 删除主键： alter table test drop primary key；（！在删除主键前要先删除自增长，因为自增长是依赖主键的，所以在删除主键之前要先删除自增长约束。） 删除自增长：alter table test modify sid int（4）；（不写自增长，自增长就会被清楚） 添加主键 ：方式一： alter table test add primary key（sid）； 方式二： alter table test change sid sid int（4） primary key； 增删改查：查 ： select * from 表 ； 查询所有字段的值 来源 哪张表。 增 ：方式1：要求表的字段不多，对表结构要熟悉。 1.插入的值的数量必须跟字段数量一致。 2.insert into 表名 values（字段1的值，字段2的值，字段3的值，...），（字段1的值，字段2的值，字段3的值，...）！字符串和日期类型的数据插入必须要使用成对的英文引号。 方式2：对指定字段插入数据；1.insert into 表名（字段4，字段2，字段1）values（字段4的值，字段2的值，字段1的值），（字段4的值，字段2的值，字段1的值）； ！null和' ': null是完全的不占空间内存，' '占系统一个字节的内存。 删除表：drop table +表名；删除库：drop database +库名；select * from 库名.表名； ：不需要进入库，就可以查看表 查select * from 表； ： 查询所有字段*的值来自于xx表select 字段 from 表；select 字段1，字段2，字段3 from 表； 查询具体对应字段数据select 字段1，字段2，字段3 from 表 where 字段=xxx；select id，name，score from test where class = 2240； 对日期或者字符串进行条件匹配搜索时也要加英文引号。select* from emp where name = “李四”; 查询不等于情况下的信息：select 字段1，字段2 from 表 where 字段 ！= xxx；select name,incoming from emp where dept2!=101;: 查询name，incoming字段的信息 来源emp表 当dept2（部门）不是101的员工信息。 多条件查询：select *from 表 where 字段1 = xx and 字段2 = xxx；select *from 表 where dept2=101 且 incoming = 3500. 查询 x条件或者x条件下的数据；select * from 表 where 字段1 = xxx or 字段2 = xxx；select *from emp where dept2=102 or incoming = 7500； 查询内容为空的字段；select * from test where score is null；查询内容不为空；select *from test where score is not null； 查询 大于小于的情况1. select * from test where age<=28 and age > 19;2. select * from test where age between 19 and 28; 对特定字段进行查询：select * from 表 where 字段 in （x，x，x）；select * from test where age in（18,28,29）；select * from test where age not in（18,28,29）；！not in 不会显示null的值 多条件查询 查询限定的数据--分页查询limit, - -般是放在最后面select * from test limit 0,3； ： 从第一行开始的3行数据。！计算机中的排序是从0开始算起的 ！ limit查询限定只能放在sql语句的后面，否则报错！ 模糊匹配： linux的模糊匹配符:'*' '? '，但是在mysql中*和?有其他作用mysqI中的模糊匹配符: '%' '_' '%' : 匹配0个或者多个字符 '_' : 匹配1个字符模糊匹配: like select * from test where name like '%m%'; select * from test where name like 'xiao_ '; select * from test where time like '2022%'select *from test where age like '2%'; 排序：默认是升序排序select * from test order by age asc；升序 select * from test order by age desc；降序 ！进行条件筛选的时候 order by要在where之后select * from test where time is not null order by time desc； ！要先进行过滤再排序再进行限定查询。select * from test where time is not null order by time asc limit 3; 对多个字段进行排序，写在前面的排序优先级高，在写在前面的字段排序完后，在此基础上对写在后面的字段进行排序。select * from test order by phone desc,age asc; 分组查询：group by！注意：1.一般分组查询要查询指定的字段，对什么进行分组就查询什么字段。因为分组之后，只有分组字段可以正常进行匹配，其他非分组字段是随机出现（一般是表中第一条）2.使用group by分组之后，进行条件过滤，是不能使用where，where条件要写在group by 之前。3.如果希望在group by分组之后进行条件过滤，则使用having（作用类似where，但是只能用在group by 之后）4.group by 分组一般是结合 聚合函数使用的，where条件是不能接聚合函数的，having才可以接聚合函数对emp表中的dept2进行分组；select dept2 from emp group by dept2； 聚合函数:having 后接聚合函数count()统计select count(*) from表; ：统计表中行数select count(1) from表; ：统计表中行数select count(字段名) from表; ：统计表中对应字段有多少条数据 sum()求和对表中某个字段进行求和，求emp表的总收入select sum(incoming) from emp; avg()平均值对表中incoming进行求平均值；select avg(incoming) from emp; max()对表中数字进行求最大值select max（incoming）from emp；min（）对表中数据进行求最小值select min（incoming）from emp； 在sql语句中进行四则运算，并且 as 取别名select （max(incoming)-min(incoming)）*100 from emp;select （max(incoming)-min(incoming)）*100 as "别名" from emp; 去重复 ：select distinct（phone） from test；update 更新：update 表 set name = 'dcs39' , age = 38 , score = 100 , data = "2022-2-22" where sid = 5;1.不限定条件 则整个字段所有数据都会更新2.限定条件，则对对应这段进行更新数据。 删：delete from 表 where 字段 = xxx； delete from 表； 不限定条件删除表中数据，可以清空表中内容。！会删除表的内容，再重新插入数据时，自增长字段还是会在原来最大值的基础上进行递增。 ！面试题：mysql中删除数据有多少种方式；delete：可以根据条件删除指定数据，如果不接条件删除时，可以清空表中数据，但是重新插入数据时候=，自增长字段还是会在原来最大值基础上进行累积递增。（只删除表中数据，保留了表结构，没有释放空间。）truncate ： truncate+表名 ； 直接清空表中数据，不能接条件删除指定数据。保留表结构，重新插入数据时，自增长会重新从1开始（清空表中数据并释放空间，保留表结构。） drop table +表名；删除整张表，删除数据，表定义也被删除（释放空间）。 备份：1.备份表 ： 直接在数据库中操作先备份表结构 create table 新表 like 旧表； 再备份表数据。 insert into 新表 select * from 旧表； 对特定字段内容进行备份。 2.备份整个库（整个库中所有的表结构和表数据）！要在linux界面执行，通过linux中mysql相关指令进行。mysqldump -uroot -p123456 dcs39 > ./mysqldcs39.sql恢复备份的数据库新建一个数据库mysql -uroot -p123456 dcs40 < ./mysqldcs39.sql!在linux系统执行，dcs40必须要在数据库中。 mysql权限在mysql表中 新增insert into user（host，user，password）values（"localhost","dcs39",password('123456')）;！password('123456') 密码加密处理。刷新权限 ：flush privileges；！不刷新就 增加权限grant insert，delete，update，select，create on *.* to 'dcs30'@'localhost' identified by '123456';刷新权限 ：flush privileges； 修改指定用户的账号密码 删除用户：delete from mysql.user where host='网络'and user = ‘xx’；然后 flush privileges； 表与表的连接方式 连接方式的区别 多表连接的方式 ddl语言：数据库定义语言对表的定义、表字段进行操作 :creat 、alter、drop DML语言：数据库操作语言、对表中的数据进行操作：insert into、delete、update set、select 索引：index为了提高查询的性能、一般增加在常用的字段上（热字段）：索引是对表的字段（列）生效，但是不能对所有的字段都加索引，如果整个表都加索引就丧失了索引的意义，反而导致系统的性能受到影响。注意：1. 创建索引的前提：表中有大量的数据（上万行），添加索引会对查询速度有明显的的提升。2. 不能对一个表的每个字段都添加索引，如果都添加就丧失了索引的意义，反而会消耗大量的内存或者磁盘的资源，通常只对热字段（经常需要查询的字段）添加索引。3.索引的优先级：主键索引PRI > 唯一UNI > 普通MUL 索引的类型：普通索引：index ：允许索引上的字段为空，允许表中存在多个普通索引唯一索引：unique index：唯一索引字段上的值可以为空，不能重复，允许表中存在多个唯一索引主键索引：primary key：索引值所在字段的值即不能为空也不能重复（当定义了主键约束，也是也就是定义了主键索引，主键约束是主键索引的一个表征），一个表中只能有一个主键索引。 创建普通索引：1. 给单个字段创建普通索引create index aa on grade（class）；== >创建 普通索引 aa 在grade表的class字段上。 show index from grade；查看索引情况 2.给多个字段创建普通索引（创建联合索引） create index bb on grade（age，sex）； 通过alter table创建普通索引 alter table grade add index bb(age);==>修改grade 表 添加普通索引 bb在age字段上。删除索引：索引是一种表结构，所以可以通过alter table 的方式进行修改：alter table grade drop index bb;==>修改表grade表 丢弃索引bb创建唯一索引：（唯一索引字段的值不能重复）create unique index cc on grade（name）；==>创建唯一索引cc在grade表上的name字段；create unique index xx on grade(chinese); ！：创建 唯一索引 xx 在grade表上的chinese字段(但是chinese字段上的值存在重复的情况，所以创建失败)通过alter： alter table grade drop index cc；==>删除alter table grade add unique index ff(name); ==>通过alter增加唯一索引 主键索引：删除主键索引：！注：在要删除主键索引时，如果该主键索引有自增长约束的话要先删除自增长约束1.先删除自增长约束:alter table grade change sid sid int(4); - - >修改表时，没有写自增就代表删除自增长2.再删除主键索引alter table grade drop primary key;添加主键索引：alter table gradeadd primay key（sid）；==> 修改表 grade表添加主键索引在sid字段上。添加主键索引的复合索引：alter table grade add primay key （sid，chinese）；： 同时可以有一个为空视图：view ：虚拟表是一种虚拟表，不负责存储数据，是通过实际的基础表创建的，用来显示基础表的数据，数据是动态的。为什么要使用视图view？ 1. 为了安全，在实际表中有些字段可能是保密的，那么就可以通过创建属兔的方式，让用户操作视图从而避免对实绩表的保密字段进行操作。2. 为了方便操作，在数据库中有些查询语句比较复杂，就可以对这些复杂的语句加工成视图表，就可以避免复杂的查询重复编写出错。 视图的特点： 1.视图是由实际表（基础表）创建的虚拟表 2.视图是由列（字段）和行（值）组成，它的列可以是多个表中列 3.视图的创建和修改是不影响基础表--结构？ 4.基础表的修改或者删除就会影响到视图 5.基础表和视图中的数据是互相影响的，当一个视图是由复杂的查询创建出来时，不允许修改视图数据。 视图的缺点：1.视图的性能差，因为视图查询时本质上是对基础表进行查询。2.视图存在修改限制，当用户想修改视图里面的数据时，本质上需要先转化成底层基础表的某些信息进行修改，如果视图的数据来源比较复杂时不允许修改的。 创建视图： - - >对基础表的数据进行复制。create view dcs as(select name,chinese,math from grade);：创建视图 dcs 依据 查询grade表的name,chinese,math字段的值 得到 show tables;- ->展示当前库中所有的表（包括基础表跟视图表）select *from dcs ；- - > 对视图表dcs表进行查询，得到的结果本质上就是 select name,chinese,math from gradeshow create view dcs； - - >查看视图dcs的结构 对视图表中的数据进行操作可以直接使用 增删改查。insert into dcs(name) values('xiaomai');update dcs set chinese=88 where name='xiaomai';delete from dcs where name='lisi' 对基础表中的数据进行修改，也会影响到视图delete from grade where sid=1; 创建一个通过多表查询获取的视图表create view duobiao as(select* from dept left join emp on dept.dept1=emp.dept2);select *from duobiao; 删除视图drop view duobiao; 查看当前库中有哪些视图：show table status where comment='view'; 外键约束 foreign key！使用外键的相关表，必须是数据引擎为innodb的：外键约束，约束的是表和表之间的关系，目的为了保持数据的一致性，完整性1，当表A字段不存在值时，表B不能进行添加对应的值2，当表B字段存在时，表A对应的值不能改变 现在创建两张表 dcs1 和 dcs2 ： dcs1: ==>主表 父表create table dcs1(id int(4) primary key,name varchar(20)) engine=innodb;# 创建表 dcs1表 包含字段id 类型为int(4)主键约束，字段name类型varchar(20) 数据引擎为innodb dcs2: ==》从表 子表create table dcs2(sid int(4) primary key,sname varchar(20), constraint gz foreign key(sid) references dcs1(id) ) engine=innodb; ↑ // constraint gz：约束名称为 gz ，gz 外键的名称// foreign key(sid) 外键定义在本表中的sid ：外键加在这个sid字段上// references dcs1(id) 引用的是dcs1表中id字段 ：dcs1表变成了该表的主表，主表的id字段对应着子表中sid字段注！：sid字段的值就受到dcs1表中id字段的约束，如果id的值不存在，那么sid就不能添加对应的值 如果sid已经添加了，那么id不能被删除,id和sid就不能随意改变了 往dcs1与dcs2两张表中添加数据：要先对主表进行添加1.insert into dcs1(id,name) values(1,'xiaomai');select *from dcs1; 才可以对从表添加相同的值2. insert into dcs2(sid,sname) values(1,'damai');select *from dcs2; ×异常的添加：直接对从表添加主表不存在的数据×insert into dcs2(sid,sname) values(2,'damai1');#报错 Cannot add or update a child row: a foreign key constraint fails ×异常的更新：更新主表,当主表的数据被从表引用，不能直接更新或者删除主表的数据×update dcs1 set id=3 where id=1; #Cannot delete or update a parent row: a foreign key constraint fails×delete from dcs1 where id=1;#Cannot delete or update a parent row: a foreign key constraint fails 更新子表，当主表没有对应的数据可以被子表引用，子表是不能更新到对应的值的× update dcs2 set sid=2 where sid=1;#Cannot add or update a child row: a foreign key constraint fails 如果需要删除或者修改主表的数据，就要先删除子表引用的数据delete from dcs2 where sid=1;update dcs1 set id=3 where id=1;select *from dcs1;delete from dcs1;总结：1，主表不存在的数据，在相关的从表无法插入与之相关的数据2，对主表进行删除或者更新，会导致主表没有了外键值时，那么执行会报错3，对子表进行更新或者插入数据时，如果主表没有这个外键值，那么执行会报错4，要删除主表的信息，需要先确认从表是否存在，如果从表存在，要先删除从表对应的数据 删除外键约束：alter table dcs2 drop foreign key gz;==>修改表 dcs2表 丢弃 外键 gz; 添加外键约束alter table dcs2 add constraint gzdcs foreign key(sid) references dcs1(id); // 修改表 dcs2 添加 约束 名称gzdcs 外键在sid字段上 引用 dcs1表的id字段 注意：添加外键时，需要确保从表和主表数据的一致性，否则添加失败 存储过程：本质上是mysql中的一个自建函数。存储过程 procedure：-- 查看当前mysql的版本 ：5.1.73select version(); 存储过程的创建：1.创建#删除存储过程如果存在dcs39 - - > 先确认是否存在存储过程dcs39drop procedure if exists dcs39; 建表的sql语句要写在存储过程外，如果建表语句在存储过程里，每次调用就会进行删表建表操作。
2.调用 ：调用存储过程时，本质上就是执行 创建时卸载begin ..end 中间的sql语句call dcs39(); 形式参数与实际参数的区别：形式参数：是在定义函数时用来接收实际参数的实际参数：用来给形式参数进行传递一个实际的值 存储过程题目：现在有一个user用户表,需要往user表中插入1000个登录用户要求如下：1、在插入用户前先判断是否存在1000个登录用户,如果存在则统计表中实际的行数、如若不存在则自动补齐剩余的数据2、表名为user,存储过程名字随意取,表中字段有id， user_name ，user_pwd，verify 格式如下(1,user1,123456,W4E38J),且id、用户名不能重复,verify验证码字段为随机生成6位数验证码 ！select concat('user',2222222) ===>拼接函数 ！select substring(MD5(RAND()),1,6) ==》随机生成6位字符的函数create table user (id int(4) primary key auto_increment, user_name varchar(20) , user_pwd int(10) default 123456, verify varchar(10) ); insert into user (user_name,verify) values ('user1','W4E38J'); drop procedure if exists dcs;
create procedure dcs(n int)begin
declare i int default (select count(*)from user); if i>=n then select count(*) from user; else while i<n do insert into user(user_name,verify)values(CONCAT('user',i+1),substring(MD5(RAND()),1,6)); set i = i+1; end while; select *from user order by id desc limit 1; end if;
end;
call dcs(2000);
页:
[1]