mysql数据库的基本使用

mysql数据库

    mysql常用语句

    连接数据库：mysql -u root -p

    断开数据库： exit

    

    库操作

    1）新增数据库  create database 数据库名称 [库选项];

    

    库选项：用来约束数据库    

    如：字符集设定   charset 具体字符集（数据存储的编码格式）常用GBK UTF8

        

       注意：数据库名称不能用关键字或保留字，若非要用关键字可以用反引号

    2）查看字符集编码    echo $LANG

    3）查看数据库

    <1>查看所有数据库   show databases;

    

    <2>查看指定部分数据库（模糊查询） show databases like 'pattern'    （pattern是匹配模式，%表示匹配多个字符，_表示匹配一个字符）

    

    <3>查看数据库的创建语句     show create database 数据库名称;

    

    4）更新数据库（数据库的名字不能修改，数据库的修改仅限于库选项）

    alter database 数据库名称 [库选项] charset 字符集;

    

    5）删除数据库    drop database 数据库名称;

    当执行这句话后， 1.数据库内部看不到对应的数据库

               2.对应的数据库文件夹被删除，级联删除，里面的数据表全部被删除（不要随意删除数据库和修改）

    

   

 

    表操作

    在某个数据库下建表，首先    use 数据库名称;

    

    1）新增表

    create table [if not exits] 表名

   （

        字段名称  类型，

        字段名称  类型

    )[表选型];

    if not exits：如果表名不存在，就创建，否则不执行创建代码，检查功能

    表选型：控制表的表现

         charset  具体字符集;   保证表中数据存储的字符集

         存储引擎：engine 具体存储引擎（innodb,myisam）

    例：  create table bit

         (    name varchar(10),

              room varchar(10)

          )charset utf8;

    

    2）查看数据表

    <1>查看所有表    show tables;

    

    <2>查看部分表    show tables like 'parrent'

    

    <3>查看表的创建语句    show create table 表名\G

    

    <4>查看表结构     desc 表名;

    

    3）修改数据表

    表本身存在，还包含了字段：表的修改分为两个部分：

    1>修改表本身

    修改表名     rename table  原本表名  to   新表名;

    

    修改表选项   alter  table  bit_student   charset gbk;

    

    修改表字段：新增，修改，重命名，删除

    1.1 新增字段      alter table 表名  add 字段名   数据类型  [位置];

     位置：字段名可以放在表中的任意位置    first  第一个位置    after：在哪个字段之后

    

    1.2 修改字段：通常修改属性或数据结构

        alter   table   表名   modify   字段名    数据类型 [属性][位置]

    

    1.3 重命名字段： alter  table   表名   change   旧字段   新字段名  数据类型 [属性][位置]

    

    1.4 删除字段：alter table 表名 drop 字段名   （如果表中已经有数据，那么删除字段会清空该字段的所有数据）                 

    1.5 删除表： drop table 表名1，表名2....   （可以一次性删除多张表）

    

    2>数据操作

    新增数据：  insert into 表名[（列1，列2，...）] values(v1,v2...)     （非数值数据都要用单引号括起来）

    

    查看数据： select */字段列表  from 表名 [where条件列表]

    

    查找所有数据： select * from 表名

    

    更新数据： update 表名 set 字段=值 [where条件]

    

    删除数据：delete from 表名 [where条件]  （删除时不可逆的）

       

    查看服务器识别的字符集：  show charset;

    

 

    mysql的数据类型

    1）数值型

    整数型  tinyint（1字节）、smallint（2字节）、mediumint（3字节）、int（4字节）、bigint（8字节）

    小数型  浮点（小数点浮动，精度有限，会丢失精度） float、double  定点（小数点固定，精度固定，不会丢失精度） decimal      

    SQL中数值类型默认都是有符号的，当需要使用无符号数据，需要给数据类型限定如： int unsigned

    浮点型数据是一种精度型数据，因为超出指定范围后，会丢失精度（四舍五入）

    float：单精度，占4字节，精度范围7位左右

    double:双精度，占8字节，精度范围15位左右

    浮点的使用方法：直接float表示没有小数部分，float(M,D) M表示总长度，D表示小数部分  

    浮点数的插入：整数部分不能超出长度，但小数部分可以超出（因为系统会自动四舍五入）

    定点型：绝对保证整数部分不会被四舍五入（不会丢失精度），小数部分有可能丢失

    DECIMAL：变长，大致每9个数字，采用4字节存储

    DECIMAL(M,D）：M最大是65，D最大是30 默认是（10,2）

    定点数的插入：定点数的整数部分不能超出长度，小数部分可以超出长度，系统自动四舍五入

    2）时间日期类型

    datetime:时间日期格式'yyyy-mm-dd HH:ii:ss' 表示范围从1000到9999

    date:日期 'yyyy-mm-dd'

    time:时间段 指定某个区间之间  -时间   +时间

    timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和datetime完全一致

    时间出：只要当前所在记录被更新，该字段一定会自动更新成当前时间

    3）字符串类型

    定长字符串

    char:表在定义结构的时候，就已经确定了最终数据的存储长度

    char(L):L达标length，可以存储的长度，单位为字符，最大长度值可以为255

    如：char(4)：在utf8环境下，需要4*3=12个字节

    变长字符串

    varchar，在分配空间的时候，按照最大的空间分配，但实际上最终用了多少根据具体的数据来确定

    varchar(L):L表示字符长度，理论长度65536个字符，但是，假设分配的长度是100，实际存放的是5个，那怎么取得5个字符，它会多出一个表示实际上存储多少个字符的长度成员。但实际上，文本长度超过255，既不是用定长也不是用变长，使用text

    如何选择定长或变长字符串？

    定长的磁盘空间比较浪费，但是效率高；变长的磁盘空间比较节省，但是效率低

    文本字符串

    如果数据量非常大，通常超过255，就会使用文本字符串

    文本字符串根据存储的数据格式进行分类：text和blob

    text:存储文字

    blob：存储二进制

 

 

 

    列属性

    列属性：真正约束字段的数据类型，但是数据类型约束很单一，需要一些额外的约束更好的保证数据的合法性

    列属性有：NULL/not null,default,primary key,unique,auto_increment,comment

 

    数据库默认字段基本都是字段为空，但实际开发中，尽可能保证字段不为空，因为数据为空没有办法参与运算

 

    列描述

    列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解

                

 

 

    默认值 default

    默认值：某一种数据会经常出现某个具体的值，可以在一开始就指定好，在需要真实数据时，用户可以选择的使用默认值

    数据在插入的时候不给字段赋值，就使用默认值

   

 

    主键 primary key

    用某个字段来唯一表示所有记录时，设置主键。用来唯一的约束该字段里面的数据，不能重复，不能为空  一个表中最多只能有一个主键

    1.增加主键

    1）创建表的时候直接在字段上指定主键

            

    2）在创建表的时候，在所有字段之后

   

    3）当表创建好以后，可以再次追加主键

   

    主键约束：主键对应的字段不能重复，一旦重复，操作失败

    删除主键：alter table 表名 drop primary key;

   

    

 

 

 

    自增长

    自增长：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值，通常和主键搭配使用，作为逻辑主键

    自增长的特点：auto_increment

    1.任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）

    2.自增字段必须是整数

    3.一张表最多只能有一个自增长

    

 

 

    唯一键

    一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就能解决表中有多个字段需要唯一性约束的问题

    唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较

    增加唯一键

    

    

 

    索引

    索引：系统根据某种算法，将已有的数据（未来可能新增的数据），单独建立一个文件：文件能够实现快速的匹配数据，并且能够快速的找到对应的记录。

    索引的意义：1.提升查询数据的效率     2.约束数据的有效性（唯一性等）

    增加索引的前提条件：索引本身会产生索引文件（有时可能比数据文件还大），非常海飞磁盘空间

    如果某个字段经常作为查询字段，可以为该字段建立索引

    如果某个字段需要进行数据的有效性约束，也需要使用索引（主键，唯一性）

    mysql中提供了多种索引：

    1.主键索引：primary key

    2.唯一索引：quique

    3.普通索引：index

    4.全局索引

    创建索引：create index index_name on tbl_name (index_col_name,...);

      

     

    索引的使用原则：

    1.在大表上建立索引才有意义

    2.在where子句或是连接条件上经常使用的列上建立索引

    3.索引的层次不要超过4层

    索引的缺点

    1.建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引

    2.更新数据的时候，系统必须用额外的时间来对索引进行更新

 

 

    关系

    将实体与实体的关系反应到最终数据库表的设计上，将表与表的关系分成三种：1对1,1对多，多对多

    一对一：一张表的一条记录一定只能在另外一张表的一条记录进行对应，反之亦然

    一对多：一张表中有一条记录可以对应另外一张表中的多条记录，但是反过来，另外一张表中的一条记录只能对应第一张表的一条记录，这种关系就是一对多

    多对多：一张表（A）中的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录也能够对应A表中的多条记录，多对多的关系

 

 

    范式

    范式：Normal Format是离散数学上的概念，为了解决一种数据的存储与优化的问题，保证数据在存储之后，凡是能够通过关系寻找出来的，坚决不再重复存储，终极目标是减少数据冗余

    范式：是一种分层结构的规范，分为6层，每一层比上一层更加严格，若要满足下一层范式，前提是满足上一层范式

    六层范式：1NF,2NF...6NF    1NF是最底层，要求最低，6NF是最高层，最严格

    范式在数据库的设计中具有指导意义，但不是强制规范

    1NF：指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性

    2NF：是对记录的唯一性约束，要求记录有唯一标识

    3NF：要满足第三范式，必须满足第二范式。  第三范式，理论上说，应该一张表中的所有字段都依赖主键（逻辑主键除外），如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键：把这种不是直接依赖主键，而是依赖非主键字段关系称之为依赖传递。第三范式就是要解决传递依赖的问题

 

 

    数据的高级操作

    数据操作：增删改查

 

    新增数据

    基本语法   insert into 表名 [（字段列表）] values（值列表）；

    在数据插入的时候，假设主键对应的值已经存在：插入失败

    主键冲突

    当主键存在冲突的时候（duplicate key），可以选择性的进行处理：更新或替换

    1.主键冲突：更新操作

     insert into 表名（字段列表） values（值列表）on duplicate key update 字段=新值；

    

    

    2.主键冲突：替换

    replace into 表名（包含字段） values（值列表）；  主键如果没有冲突，就直接插入

    

    

 

 

    蠕虫复制

    蠕虫复制：从已有的数据中获取数据，然后将数据又进行新增操作，将数据成倍增加

    表创建，从已有表创建新表（复制表结构，不复制数据）

    create table 表名 like 数据库.表名；

    

    蠕虫复制：先查出数据，然后将查出的数据新增一遍

    insert into 表名[（字段列表）] select 字段列表/* from 数据表名;

    

                 

       

    蠕虫复制的意义：

    1.从已有表中拷贝数据到新表中

    2.可以迅速的让表中的数据膨胀到一定的数量级，用来测试表的压力及效率

 

 

    更新数据

    基本语法   update 表名 set 字段=值 [where 条件]；

    高级新增语法  表名 set 字段=值 [where 条件] [limit 更新数量];

    

 

 

    删除数据

    通过limit来限制删除数量

    delete  from 表名 [where 条件] [limit 数量]；

    

    如果表中存在主键自增长，当删除后，自增长并不会还原，继续按照原来的值增长

    思路：数据的删除不会改变表结构，只能删除表后，重建表

    truncate 表名;   --先删除该表，后新增该表  清空表后

    

    

 

    查询数据

    完整语法  select [select选项] 字段列表[字段别名]/* from 数据源 [where 条件] [group by 子句] [having 子句] [order by 子句] [limit 子句]；

    slelect选项：select 对查出来的结果进行处理方式

                 all：默认的，保留所有结果

                 distinct:去重，查出来的结果，将重复（所有的字段都相同）结果去重

    

                 

       

    字段别名：当数据查询出来的时候，有时候名字不一定满足需求（多表查询时，会有同名字段），需要对字段进行重命名，就叫别名

    语法

    字段名 [as] 别名    

               

    数据源

    数据的来源，关系型数据库的来源都是数据表，本质上只要保证数据类似二维表，最终都可以作为数据源

    数据源分为多种：单表数据源，多表数据源，查询语句

    从一张表中取出一条记录，去另外一张表中匹配所有记录，并且全部保留（记录和字段），将这种结果称为：笛卡尔积

    子查询：数据的来源是一条查询语句（查询的结果是二维表）

    select * from (select 语句） as 表名；

   

 

    where子句

    where子句用来判断数据，筛选数据

    where子句返回结果，0或者1,0表示false，1表示true

    判断条件：比较运算符：>,<,>=,<=,!=,<>,=,like,between and,in ,not in

              逻辑运算：&&   ||   ！

    where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断条件，从磁盘取出一条记录，开始进行where判断，判断的结果如果成立就保存到内存，如果失败就直接放弃

    select * from 表名 where 1;  表示所有条件都满足

    

 

    group by 子句

    分组的意思：根据某个字段进行分组（相同的放一组，不同的分到不同的组）

    基本语法：group by 字段名;

    

    分组的意义是为了统计数据（按组统计：按分组字段进行统计）

    SQL提供了一系列统计函数：count():统计分组后的记录数，每一组多少条记录

                             max():统计分组后，每组中的最大值

                             min():统计分组后，每组中的最小值

                             avg():统计分组后，每组中的平均值

                             sum():统计分组后，每组的和

    分组后自动排序：根据分组字段排序，默认是升序

    group by 字段[asc|desc];对分组的结果合并后的整体进行排序

 

    having 子句

    与where子句一样，进行条件判断

    where是针对磁盘数据进行判断，进入内存之后会进行分组操作，分组结果需要having来处理

 

    order by 子句

    order by:排序，根据某个字段进行升序或降序排序

    基本语法： order by 字段名1，字段名2... [asc|desc]

 

    limit 子句

    1.limit 数量；  子句限制数据量

    2.limit 起始位置，长度；  经常用于分页

    

 

    连接查询

    连接查询：将多张表（可以大于2张）记录进行记录的连接（按照某个指定的条件进行数据拼接）

    连接查询的意义：在用户查看数据的时候，需要显示的数据来自多张表

    连接查询分类：SQL中连接查询分为：内连接，外链接，自然连接和交叉连接

 

    交叉连接：cross join,从一张表中循环取出每一条记录，每条记录都从另外一张表中匹配，匹配一定保留，而连接本身字段会增加，最终形成的结果叫笛卡尔积

    基本语法：左表 cross join 右表；<----> from 左表，右表；

    select * from dept cross join dept;

    dept cross join dept是数据源。笛卡尔积没有意义，所以交叉连接没用，交叉连接存在的价值，保证连接结果的完整性

  

    内连接

    内连接：[inner] join,从左表中取每一条记录，去右表中与所有的记录进行匹配，匹配必须是某个条件在左表中与右表相同最终才会保留结果，否则不保留

    基本语法：左表 [inner] join 右表 on 左表.字段=右表.字段

    on表示连接条件，条件字段就是代表相同业务含义

    字段别名以及表别名的使用，在查询数据的时候，不同表有同名字段，这个时候需要加上表名才能区分，而表名太长，通常可以使用别名

 

    外连接

    外连接：outer join，以某张表为主，取出里面的所有记录，然后每条与例外一张表进行连接；不管能不能匹配上条件，最终都会保留，能匹配正确保留，不能匹配，就将其他表中的字段都置为NULL

    外连接分为两种：是已某张表为主：有主表

                    left join：左外连接，以左表为主表

                    right join：右外连接，以右表为主表

    基本语法：左表 left/right join 右表 on 左表.字段=右表.字段；

 

    自然连接

    自然连接：natural join，自然连接就是自动匹配连接条件：系统以字段名字作为匹配模式，同名字段就作为条件，多个同名字段都作为条件

    自然连接：分为自然内连接和自然外连接

    select * from EMP natural join DEPT;--比较出EMP和DEPT中的同名字段相等的匹配出来，连接之后会合并同名字段

    基本上不用自然连接

 

    外键

    如果一个实体的某个字段，指向另外一个实体的主键，就称这个实体的这个字段为另外一个实体的外键

    基本语法：foregin key (本表字段） references 主键表（主键字段）；

 

    级联操作

    on update 主表更新时，从表的外键才做出相应的改变

    on delete 删除时，从表的外键才做出相应的改变

    on后面表示级联操作：在主表发生变化时，与之关联的从表数据如何处理

    cascade，如果主表被删除或更新，从表就做出相应的操作

    set null,设置为null，表示从表不指向任何主表的记录

    restrict,拒绝主表相关的操作

 

 

    视图

    视图是一个虚拟的表，其内容由查询定义，视图并不实际存在，行和列有定义视图的查询所引用的表

    创建视图： create view 视图名 ；

    删除视图： drop view 视图名；

    视图和表的区别：

    1.表要占用磁盘空间，视图不需要

    2.视图不能添加索引

    3.使用视图可以简化查询

    4.视图可以提高安全性

 

 

    事务

    事务用于保证数据的一致性

    SQL执行分成两步：1.执行阶段  2.将结果提交到数据库

    如果用户选择提交数据才将数据提交到数据库。默认是自动提交，关闭自动提交

    set autocommit=0;

    1.开启事务：告诉系统一下写操作不要直接写入数据表，先存放到事务日志

    start transction 提交失败时回到start状态

    2.进行事务操作，一些列操作

    3.提交事务或回滚事务

    commit    rollback

 

 

    变量定义

    1.set @name:='张三';    select @name;

    MYSQL中允许从表中获取数据，然后赋值给变量

    select @变量：=字段名 from 数据源；    --从字段中取值赋给变量

    select 字段名 from 数据源 into @变量；

 

    2.declare 变量名 类型 [default 默认值]；

    说明：1.它必须先定义     2.赋值 set 变量：=值；   3.它只能在编程环境下使用

    编程环境：存储过程，函数，触发器

 

    if判断

    --单分支

    if 条件 then

       --代码

    end if；

 

    --双分支

    if 条件 then

       --代码1

    else

       --代码2

    end if；

 

    --多分支

    if 条件 then

       --代码1

    elseif 条件 then

       --代码2

    else

       --代码3

    end if；

    存储过程：类似函数，把一段代码封装起来，当要执行这段代码时，调用存储过程来实现

    语法：create procedure 过程名（参数，...）

          begin

               --代码

          end‘’

    例：接收4个数字，如果输入1则输出spring，2summer，3autumn，4winter，输入其他出错

    

                  

       

 

   case判断

    case 变量

    when 值 then 语句；

    when 值 then 语句；

    else 语句；

    end case；

    例：接收4个数字，如果输入1则输出spring，2summer，3autumn，4winter，输入其他出错

    

    

    

 

    循环

    MYSQL支持的循环有loop,while,repeat循环

    （1）loop循环

    标签名：loop 

           leave 标签名  --退出循环

           end loop;

    例：使用loop循环，计算1到n的和

    

    

        

 

    （2）while循环

    while 条件 do

          --代码

    end while；

    例：使用while循环，计算1到n的和

    

    

    

       

 

    （3）repeat循环

    repeat

      --代码

    until 条件 end repeat；

    例：使用repeat循环，计算1到n的和

    

    

          

 

    触发器

    事先为某张表绑定好一段代码，当表中的某些内容发生改变时（增删改）系统会自动触发代码

    触发器：事件类型，触发时间，触发对象

    事件类型：增删改

    触发时间：before和after

    触发对象：表中的每一行记录

 

    创建触发器：

    delimiter 自定义符号；后序代码中只有碰到自定义符号才算结束

    基本语法：

         create trigger 触发器名字 触发时间 事件类型 on 表名 for each row

         begin  --代表左括号

                --里面是触发器的内容，每行内容必须使用；结束

         end

     delimiter ;

 

 

    存储过程

    存储过程简称为过程（procedure），是一种没有返回值的函数

    创建存储过程

        delimiter $$

        create procedure 过程名字（[参数列表]）

        begin

          --过程体

        end$$

        delimiter ;

    说明：（1）存储过程中，可以有各种编程元素：变量，流程控制，函数调用

          （2）还可以有：增删改查各种sql语句

          （3）其中select会作为存储过程之后的结果集返回

          （4）形参可以设定数据的进出方向

    查看存储过程：show procedure status\G

    调用过程：call 过程名（）；

    删除过程：drop procedure 名字；

   

    存储过程参数

    过程的类型限定：

    in：数据只是从外部传递给内部使用（值传递），可以是数值也可以是变量，缺省是in

    Out:不允许过程内部使用（不用外部数据），给外部使用（引用传递，外部数据会被先清空才会进入内部）

    inout:外部数据可以在内部使用，内部修改也可以给外部使用

    基本使用：

    create procedure 过程名（in 形参名字 数据类型，out 形参名字 数据类型，inout 形参名字 数据类型）

    begin

       ...

    end