mysql学习笔记

数据库的设计三范式：列不可拆分（数据拆分到不能在拆了，但是还是要针对具体问题具体分析），唯一标识（有了唯一的标识就可以区分出不同的数据了），引用主键（引用唯一的标识来建立关系）建表的过程实质上就是一个创建字段的过程 关系有三种 1对1的关系：一个对象A对应着对应着一个对象B，一个对象B对应着一个对象A，那么两个对象之间的关系可以存入A或者B对象中都可以、一个对象A对应着n个对象B，一个对象B对应着一个对象A，那么两个对象的关系存入B对象中、一个对象A对应着n个对象B，一个对象B对应着m个对象A，就新建一个表用于存两类对象的关系，这个表不对应着实体，而是对应着关系 当我们连接上数据库后首先面对的是多种多样库，然后每个库中又有不同的表 使用库用 use [库名]；就可以了，用show databases；来显示所有的库，创建数据库时用create database [库名] charset utf-8；就是创建数据库然后选用utf-8编码，drop database [数据库名]删除数据库，drop tables [表名]删除表，mysql中表和列可以改名，但是库名不能改rename  table [表名] [新表名]即修改表名，创建表用create table [表名]< 回车  列名 数据类型，回车   列名2 数据类型2  回车  >charest utf8;,insert into [表名] values 回车再加<标识，'数据'>，回车<标识，'数据'>;就是将数据存入到表中了，注意每一个命令的结束都是以;号结尾我笔记中的<>符号实际上是英文中的括号，用insert into [表名] 回车 <列名1，列名2，列名3>  回车 <数据1，数据2，数据3>；这样就可以在某些列插入数据而不是在全部的列都要插入数据，而如果用insert into [表名]  回车 values  回车 <直接插入所有列的数据>这样就可以简洁的插入所有列的数据，（注意就算能自增的int类型的数据也不能不填，要么老老实实的写完，要么不写但是要用逗号隔开）truncate [表名]，瞬间请空表相当于删除表在新建一个相同的表，而delete命令是删除了表中的行，这两种中truncate的速度要快的、、因为MySQL命令窗口只识别gbk，所以中文可能会乱码，所以用set names gbk；就能解决这个问题了；当命令写错时，可以用\c重写命令；tee [.sql文件地址]，这个命令是将sql语句和结果都输入到该文件中该地址例子为D:1010.sql，若想学详细的纸面上的教程可以看http://www.runoob.com/mysql/mysql-data-types.html,若想查看表的详细结构用desc [表名]；即可，select * from [表名]；可以看到表的列和数据的情况，update [表名] 回车 set 回车 要改的列 [改后的列值] 回车 where 列名（一般是id）=数据;这样就能具体的改变某一行的数据，这里面的where命令就是一个判断，也可以是id>6之类的，反正就是一个表达式，使之判断为真时，进行特定的修改就行了，在判断时并且的条件用and或者用or，delete from [表名] where [判断语句]；可以删除判断为真的部分行，delete from [表名]，会删除表中所有的数据但是表还在，select [列名],[列名] [表名] where [判断语句] mysql中的三大列类型包括；select connect <列名，添加的数据><列名2，添加的数据> from [表名]，会在该列的最后一行的添加指定的数据，数值型，整型，浮点型，整型包括tinyint，smallint，mediumint，int，bigint。tinyint占一个字节，smallint占两个字节，mediumint占三个字节，int占四个字节，bigint占八个字节。注意不要超过类型的范围，一般字节默认是有符号的，是从-128--127，而不是0--255，int系列有三个参数分别是 M unsigned zerofill ，加unsigned就是int系列类型变成无符号类型，而M和zerofill是一起使用的 alter table [表名] add [列名] int系列<M> zerofill not null default 0;这样的话M和zerofill就一起连用了，比如M为5，那么如果数据为1，那么存进去的就是00001， 1：删除列 ALTER TABLE 【表名字】 DROP 【列名称】 2：增加列 ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL  COMMENT '注释说明' 3：修改列的类型信息 ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL  COMMENT '注释说明' 4：重命名列 ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL  COMMENT '注释说明' 5：重命名表 ALTER TABLE 【表名字】 RENAME 【表新名字】 6：删除表中主键 Alter TABLE 【表名字】 drop primary key 7：添加主键 ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid) 8：添加索引 ALTER TABLE sj_resource_charges add index INDEX_NAME (name); 9: 添加唯一限制条件索引 ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber); 10: 删除索引 alter table tablename drop index emp_na 注意alter table [表名] add [列名] 类型 列参数 after [列名] <即可放在具体某一列的后面>，alter table [表名] add [列名] 类型 列参数 first<即可放在第一列> alter table [表名] modify [列名] 新类型 列参数 可以修改列，如果用change命令就是能改列名和列类型，而modify只能改列类型，如果改类型可能会导致数据的丢失或者根本改不了 某一个表的列用[表名].[列名]来表示。 浮点类型的数据有float<m,d>、decimal<m，d>m是精度即小数点前后总位数，d是标度即小数点后的位数float<6,2>是从-9999.99到9999.99.如果m<=4占4个字节否则占8个字节，decimal与float的区别是decimal将小数点前后分开存，比float更精确是一种变长类型，更省内存。相反float有时候会损失精度，char型和varchar型就是定长和变长的，使用定长的时候查找行记录会比较快，因为依次往后定长移动就行了，char<N>不够N个长度用空格补至N个长度，varchar<N>可以存N个字符，而不是N个字节，比如char<2>能存六个中文utf8汉字，这里面的字符要特别分清楚，它并不是n个字节，也不是n个中文或英文字，一个字符就是能存6个中文或英文字，varchar类型在内存中实际上每个字符的前面还有一个数据比如数据“张三”之前会有一个2标识，后面一个字符会有两个utf8字符，text类型一般用于存文章，blob是二进制类型用于存图像、音频的,日期类型有date、time、datetime、year。如果是date类型就比较简单了，他没有什么参数，直接存xxxx-xx-xx就行了，time类型是用来存小时分钟秒数据，xx:xx:xx,datetime也就很好理解了就是date和time的结合xxxx-xx-xx xx:xx:xx,timestamp类型会有一个默认值default CURRENT_STAMP,是会取当前时间，year类型只占一个字节，只能记录从1901到2155年，但是可以设置一个默认值0000， mysql中不同的引擎存储的数据是相同的，但是储存的方式是不同的 表的引擎有三种： MyISAM 优点：速度快，磁盘空间占用少；某个库或表的磁盘占用情况既可以通过操作系统查相应的文件（夹）的大小得知，也可以通过SQL语句SHOW TABLE STATUS查得 缺点：没有数据完整性机制，即不支持事务和外键 InnoDB 优点：支持事务和外键，数据完整性机制比较完备；可以用SHOW TABLE STATUS查得某个库或表的磁盘占用 缺点：速度超慢，磁盘空间占用多；所有库都存于一个（通常情况）或数个文件中，无法通过操作系统了解某个库或表的占用空间 BDB 优点：支持事务，不支持外键，由于在事务支持的基础上，外键可以在数据库的客户端（可能是最终客户的服务器端，例如php）间接实现，所以数据完整性仍然是有保障的； 缺点：速度慢，磁盘占用多；不能通过SHOW TABLE STATUS查询某个库或表的空间占用；用操作系统可了解库相应的文件夹，或表相应的文件的大小，但由于BDB表总是还要产生log文件，而实际的磁盘占用应该把log文件也包含在内，所以用操作系统查得某库或表的大小总是小于实际占用空间。 一般建表用engine mysiam就行了 查找操作select有5种子句where<条件查询>、group by<分组>、having<筛选>、order by<排序>、limit<限制结果条数> select [列名1] [列名2] [列名3] from [表名] where [判断条件]，判断条件中可以用>,<=,!=,AND,OR,NOT,in<a,b,d>（这个意思是在a,b,d这三个数组成的集合中），between a and b在数字a到b之间的包括边界值，not in能连用，like(like是用于匹配的可以匹配以什么什么开头的可以是一个具体的字符比如'华为'也可以是通配符%，或者正则表达式)like与where连用可以很强大， 在select中各个列都可以看成是变量，所以变量之间的运算就可以运行，比如不同列的的加减乘除之类的运算可以运行，得出的结果(包括计算的结果)叫做广义投影，就像是用手电筒从总表中的某一部分从上往下照下去，那么得到的就是光的投影，而且这种列之间计算的结果还可以起一个别名用[列名1][列名2] as [别名]，sql语句中是有数学函数的比如update [表名] set [表达式  例：floor(lie/10)*10]就是将lie这个列的数据除以10取整再乘以10这里面的floor就是一个sql数学函数用于取整，常用的函数有max：取最大值，sum：求和函数，avg：求平均，rand函数 rand()括号内的数无论是什么数，都只会随机产生0-1之间的数，celling()是向上取整，concat(列名，'符号')，就是用指定的符号把数据连接起来，效果是数据1'符号'数据2'符号'等等，ascii()返回ascll码值，length()计算字符串的字节长度，char_length()计算的是字符数，reverse()翻转字符串就是'abc'变成'cba',positon('字符串a','字符串b')是查看字符串a在字符串b的位置注意不是从0开始而是从1开始，right('字符串',字符数),是用来从右边截取字符串，now()取出现在的时间，curtime()返回时间部分，curdate()返回日期部分，dayofweek()查看某天是星期几,week()计算某一天是该年的第几周，md5('')加密数据，case 值 when 某种可能 then 返回值 when 另一种可能 then 返回值 end这时sql的流程控制，sql语句还有三目运算符if(条件,'值1','值2')，条件为真返回值1，条件为假返回值2，ifnull(值1，值2)如果值1为null那么返回值2，否则返回值1，user()返回用户及主机，database()返回正在使用的数据库，count：计算不为null的行数。关于count还是挺坑的，可以看一下博客https://blog.csdn.net/wendychiang1991/article/details/70909958/。count(表达式)，这个count中的表达式并不是一个判断条件，不能像where 之类的命令可以利用判断语句。 而关于group的相关内容推荐看https://blog.csdn.net/yaruli/article/details/79239859的博客。 having命令与where、group是有联动关系的，where先进行查询，group分组，得到了一个包含投影集的中间表，然后再用having命令进行二次处理，意思就是普通列进行了第一次处理之后得到了广义投影，也就是中间集，然后用having [处理语句]得到最终结果集，也就是第二次处理的结果。where不能对投影结果即中间集进行处理，因为where只能对原始列处理，而having能对投影集处理，有一点要注意就是第一次处理结果也就是中间集，和最终结果集也是一张表，还可以再次利用，当做一张表在进行处理。但是代码量可能会有一点大。 order by命令是一个排序命令，order by [结果集中的列名1]desc/asc [结果集中的列名2] desc/asc(desc是降序排列，asc是升序排列)，需要注意的是order by是对结果集进行处理的，必须是最终结果。 limit命令一般是接在order by命令的后面，用limit [偏移量] [条目数]，偏移量是跳过几行，条目是取出多少条，偏移量可以不写， 从此我们就能看出select的五个参数是环环相扣的，它们的使用顺序一般是不可调换的，即按先后使用顺序排列是，where、group by、having、order by、limit，下一个命令是对上一个命令结果的再次处理。这五个子句的使用是有很多的坑的比如，在group分组时，有一个int类型的数据，你以它作为分组标准，那么其他类型的数据就会取第一个出现的数值作为数据 where类型的子查询，就是把内层的查询当做外层查询到比较条件，比如找一个最贵的商品，select goods_name,goods_price from goods where goods_price=(select max(goods_price));就行了，注意内层的查询不能是只有max(goods_price)因为只有这个的话就不会有返回值，只有select之后才会有返回值， 子查询总共有三种，分别是where、from、exists： where型子查询就是查询了两次，只不过一个查询用到了另一个查询的结果， from型子查询即内层sql的查询结果当成一张表，供外层的sql再次查询， exists子查询就是把外层sql的结果，拿到内层生气了去测试，如果内层sql成立，则将外行sql语句要取出的行取出，例如：select goods_id,goods_name from goods where exists (select * from goods where goods.cat_id=category.cat_id);将外层的取出的行的数据代入到内层的sql语句中如果内层sql语句真的能取出东西的话，那么外层取出的这行数据就是可用的，可取出的。 在建表的过程中，我们总是用not null default ''/default 0；这时因为我们不想要表中有null这个东西，null不参与任何东西，它在任何情况下都没用，我们要想表达空或者非空的意思的话，比较时就用is null,is not null,单纯的null参与比较或者运算时，返回的东西一律是null，但是有一点要注意单纯的null与'NULL'字符是有区别的，'NULL'是字符并不是空，就像某个人的名字班长，但他并不是班长是一个意思。 在数据库中每一个表就像一个集合，这个集合就是高中里面学到的集合，在表中每一行就是一个集合中的元素，两个表的相乘，就是和两个集合相乘是同一个道理，表一有M个元素，表二有N个元素，那么两个表相乘就会有MxN个元素即MxN个行，那么具体在sql语句中两个表相乘的效果就是两个表全相乘查询，代码就是[表1]，[表2]在select的过程中把两个表用，连接起来就行，那么查到的就是两个表的相乘，表1的行一分别与表2的行一到N连接起来作为新表的第一到第N行，表1的行二分别与表2的行一到N连接起来作为新表的第N+1到第2N行,依次类推，得到了两表相乘后的结果。在得到了两表全连接查询后的表后，还可以对得到的新表进行查询，此时可能会遇到两张原表中有同名的列，这时就要用[表名].[列名]来具体的表示哪一个表的列。 在做了全相乘之后，并不是所有的相乘的结果我们是都需要的，我们要在全相乘的结果的基础上取出一些特定的行，那么就用到了左连接的语法，左连接的原理就像是表1在左边不动，而表2在表1的右边滑动，两个表通过一种标准来筛选所需要的表2的行，具体语法就是[表1]left join [表2] on [标准](这行命令接在from之后)，得到的结果仍然是一个表，在全相乘与左连接的比较中，强烈推荐用左相乘，因为左连接的效率远高于全相乘，并且全相乘得到的只是一个临时表而左连接得到的是一个基础表可以进行where、group by等处理，所以左连接在效率和得到的效果上远好于全相乘。 那么有左连接就有右连接内连接，它们之间的区别其实左右连接是相同的意思，A左连接B就相当于B右链接A，内连接的命令是[表1] inner join [表2] on [条件]，在相同条件下，左右连接的交集就是内连接. union 就是合并两个或两个以上的sql语句的结果，所以union合并的是结果集并不区分来自哪张表其实就是快速的将两张表简单的连接在一起，具体命令是[sql] union [sql2]，但是在union的时候会有一个问题，那就是两个结果集的列名是不一样的，如果不作处理的话，那么union后的表的列名就会以第一个结果集的列名为准了，或者你想自定义那你就用as命令把列名自定义一下。然后第二个问题就是列数不同能合并吗，当然是不行的，但是合并在同一个列下的数据可以是不同数据类型的但是可能会发生数据丢失的情况。此外union后的表还是可以进行order by的排序的，union时如果两个结果集中有完全相同的一行，那么系统默认会去重，但是如果你想保留两个相同的行，那么就用union all 替换union。 在表的查询中会经常用到一个结果集，每次都要把得到这个结果集的代码重新写一遍会很麻烦，这时有了view视图就会很方便，view就是一张虚拟表，就是通过某种运算得到的一个投影，这个投影会随着原表的变化而发生相应的变化，他并没有实实在在的数据只是，原表运算结果的影子，具体命令很简单就是create view [视图名] as select [语句]，但是在视图的知识中还有一个非常重要的点就是视图能不能修改，修改后原表又会不会变，其实视图在某些情况下是可以改的，而且会影响到原表，基本上就是视图中的数据不是原表中的多个数据之间经过计算处理后的结果，而是原来的数据在满足了判断条件后被拿到视图中的，该数据在视图中和原表中都有，这时可以修改，并能影响原表。除此之外大部分情况是不能修改的，具体的你还可以看MySQL的参考手册检索create view的具体内容。而视图的删除就是用drop view [视图名]。创建视图的时候其实是可以选择模式的，总共有三种模式merge、temptable、undefined ，merge是不建立临时表，只是把命令语句存储下来了，这样的话就可以比较节省资源，temptable就是建立临时表了，而undefined就是由系统决定，具体命令就是create algorithm=[三种模式之一] [视图名] as select [语句]， 在MySQL中有事物这个概念，事物有原子性，即所有的操作是同一个整体，只有所有的操作都成功了，这个事物才算完成了，只要有一个部分没完成就算这个事务没完成那么所有的处理都会取消，事务还有有一致性，隔离性：事物结束前结果别人看不见，持久性：事务一旦完成就无法取消，开启事务的命令是start transaction；结束事务的命令是commit，注意事务会被InnoDB引擎支持而不被MyISAM引擎支持。