MYSQL数据库SQL语句集锦

  *特别说明:FILED代表数据表字段,CONDITIONS代表where之后的条件,TABLENAME代表数据表名   []中括号内的内容代表 可有可无。

  创建数据库

    create  database  DATABASE;

  删除数据库

    drop database  DATABASE

  创建数据表

    create   table TABLENAME(

      `id`   int   unsigned  auto_increment  primary key,     //把id设置为主键,数据类型是无符号int型,自动递增       

            //注:unsigned 只能紧跟在数据类型 后,顺序不能乱  unsigned相当于去掉数字负数部分   例:tinyint  默认取值范围 -128到127  unsigned 后  取值范围变 0到 255 

      `userid`   int   unsigned references  FOREIGNTABLE(FOREIGNFILED),  //把userid设置为无符号整型,并设置成外键。关联主键表(FOREIGNTABLE)中的主键(FOREIGNFILED)

      `imgid`   int  unsigned  not null default 1  comment '图片ID',   //把imgid  设置成无符号整数  且不为空  默认值为1  注释说明为  “图片ID”

      foreign key(imgid)  references  FOREIGNTABLE(FOREIGNFILED)    //设置  imgid 为外键。 关联主键表(FOREIGN_TABLE)中的主键(FOREIGN_FILED)   (下同)

    )engine=MYISAM  default charset=utf8  collate=utf8_general_ci    //设置数据表引擎为MYISAM  ,默认字符集为utf8  且字符集排序规则为   utf8_general_ci

  删除数据表

    drop table if  exists TABLENAME;

  添加字段

    alter table TABLENAME   add  colum  FIELD   DATATYPE   ATTRIBUTES    //DATATYPE   :字段数据类型     ATTRIBUTES字段属性      

    eg:  alter table test  ADD COLUMN  ceshi varchar(50)  not null default ''  comment  '测试字段'

  删除字段

    alter table TABLENAME   drop colum  FIELD ;

    eg:alter table test  DROP COLUMN  ceshi;

  修改字段属性

    alter table  TABENAME modify FIELD char(10) default 0 COMMENT "这是整数";

  修改字段名称

    alter table TABENAME  change FIELD mytest int unsigned not null default 1;

  添加默认值

    eg:   alter table test alter column user_id set default 1;

  删除默认值

    alter table TABENAME alter column FIELD drop default

  设置主键

    alter table TABENAME add primary key(FIELD1  [ ,FIELD2,.. ] );    

  删除主键

    alter table TABLENAME drop primary key;   //注意:如果表里的主键是具有自增长属性的;那么直接删除是会报错的

  添加唯一索引

    alter table TABLENAME    add  constraint   KEY_NAME  unique(FIELD);   //KEY_NAME  :索引名称  (下同)

  添加外键索引

    alter table TABLENAME    add  constraint   KEY_NAME  foreign key(FIELD)  references  FOREIGN_TABLE(FOREIGN_FIELD) ;

  删除索引(外键、唯一)

    alter  table  TABLENAME    drop  index KEY_NAME

    或

    alter  table  TABLENAME    drop  key  KEY_NAME

  检查约束 :注意,MYSQL 目前并不支持check检查约束。可以用以下方法,设置字段为枚举值

    eg:   alter table TABENAME modify sex ENUM("男","女");

  修改表的存储引擎

    alter table TABENAME engine=INNODB

  修改数据表的自增长值

    alter table TABENAME AUTO_INCREMENT=100;

  添加数据

    insert  [ into ]  TABLENAME (FIELD1,FELD2,...)  values(值1,值2,值3,...) ,(值1,值2,值3,...),...

  修改数据

    update  TABLENAME   set FILED1=值1,FEILD2=值2,....  where   CONDITISONS

  删除数据

    delete  from  TABLENAME    [  where  CONDITIONS ];

  清空数据 

    truncate   table   TABLENAME;   自增ID会重置。

  查询数据表所有

    select  from   TABLENAME  

  统计查询(查询结果集中的数据条数)

    select count(FIELD)  from  TABLENAME  [ where  CONDITIONS]

  查询某个字段的最大、最小、平均、求和的值

  select max(FIELD)  from TABLENAME  [where  CONDITIONS];

  select min(FIELD)  from  TABLENAME  [where  CONDITIONS];

  select  avg(FIELD)  from  TABLENAME  [where  CONDITIONS];

  select  sum(FIELD)  from  TABLENAME  [where  CONDITIONS];

  排序查询

  select  *  from   TABLENAME  [ where CONDITIONS]   order by FIELD ;  从小到大排

  select  *  from   TABLENAME  [ where CONDITIONS]   order by FIELD  desc;  从大到小排

  限制查询

  select * from TABLENAME   [ where CONDITIONS]   [ order by FIELD ]  limit START,NUM      //START代表数据集的开始位置,0为第一条数据的位置,依次往后为数据的位置。NUM代表限制数据集的数据条数。至少1条。

  去重查询 

  select distinct  FILED1,FIELD2,...  from  TABLENAME    [ where CONDITIONS]   [ order by FIELD ]  [ limit START,NUM ]   //代表FIELD1,FIELD2,....所有字段都重复时,才去除重复的数据条。

  分组查询  (  group  by  FIELD)

   示例::select name, first_letter,sum(parent_id) as cid  from mm_city where `parent_id` = 18 group by   first_letter  having cid>50   limit 0,1   //顺序不能乱。当有统计字段别名做条件时,不能用where,只能用 having。

  模糊查询

    select *  from  TABLENAME  where FIELD like  '%值%';      //  %  代表任意多个任意字符

  内连接查询

    select  TABLENAME1.FIELD1,TABLENAME2.FIELD2,TABLENAME3.FIELD3,...  from  TABLENAME1

    join  TBALENAME2  on TABLENAME1.FIELD  = TABLENAME2.FIELD   

    ( join  TBALENAME3  on TABLENAME1.FIELD  = TABLENAME3.FIELD )  

    或   

    ( join  TBALENAME3 on TABLENAME2.FIELD  = TABLENAME3.FIELD  )

    [  where   TABLENAME1.FIELD = 值 ...  ]        //查询或作为条件的字段中,若所有表中任意两张表都有该字段,则必须要指明数据表。即在字段前用 表名连上点(.)

  外连接

    左联接:left join   ;查询出来的数据,若有空值,则以在left join前面的表中的数据条数为准

    select  TABLENAME1.FIELD1,TABLENAME2.FIELD2,TABLENAME3.FIELD3,...  from  TABLENAME1

    left join  TBALENAME2  on TABLENAME1.FIELD  = TABLENAME2.FIELD   

    (left  join  TBALENAME3  on TABLENAME1.FIELD  = TABLENAME3.FIELD )  

    或   

    (left join  TBALENAME3 on TABLENAME2.FIELD  = TABLENAME3.FIELD  )

    [  where   TABLENAME1.FIELD = 值 ...  ]        //查询或作为条件的字段中,若所有表中任意两张表都有该字段,则必须要指明数据表。即在字段前用 表名连上点(.)

    

    右联接:rightjoin   ;查询出来的数据,若有空值,则以在left join后面的表中的数据条数为准

    select  TABLENAME1.FIELD1,TABLENAME2.FIELD2,TABLENAME3.FIELD3,...  from  TABLENAME1

    right join  TBALENAME2  on TABLENAME1.FIELD  = TABLENAME2.FIELD   

    (right join  TBALENAME3  on TABLENAME1.FIELD  = TABLENAME3.FIELD )  

    或   

    (right join  TBALENAME3 on TABLENAME2.FIELD  = TABLENAME3.FIELD  )

    [  where   TABLENAME1.FIELD = 值 ...  ]        //查询或作为条件的字段中,若所有表中任意两张表都有该字段,则必须要指明数据表。即在字段前用 表名连上点(.)

  常用数据库函数

    LENGTH  :返回字符串或列的数据的长度

      eg: select length(city)  as citylen  from  Demo

    lower/upper:返回字符串的小写/大写

      eg: select UPPER(account)  from admininfo;

    REPLACE :替换字符串
      eg: select REPLACE('SQL SERVER','SQL','sql') 结果是'sql SERVER'   

    POWER() 取数值的幂值

      eg:select POWER(5,3) 结果是125

    ABS 返回绝对值

      eg:select ABS(-99) 结果是99

    ROUND 根据指定精度返回数值的四舍五入

      eg: select ROUND(3.1415926,3) 结果是3.142

  

  数据库对象

    存储过程

      drop procedure if exists pr_multi;   //如果存在名为pr_multi的存储过程,则删掉
      create procedure pr_multi( out c int, a int, b int )      //创建名为pr_multi的存储过程(函数),第一个为数据类型int的输出参数,第二、三个分别为数据类型int的输入参数
      begin              //过程体开始标记
        if a is null THEN         //判断  a 参数是否为空,若果为空,
          set a=10;      //给  a赋值10
        end if;
        if b is null THEN     //判断  b 参数是否为空,若果为空,
          set b=20;     //给  b 赋值20
        end if;     
        set c=a*b;      //将参数c设置为  a  和  b  的乘积
      end               //过程体结束标记


      call pr_multi(@name,5,3);    //调用存储过程pr_multi     用变量  name  接收输出参数  c  ,给参数  a  传值  5,b传值  3

      select @name     //查询变量name的值。。  上述结果为  15  

      详情请看  https://www.cnblogs.com/bobi-PHP-blog/p/7501392.html

    视图

      create view  VIEWNAME    //VIEWNAME    视图名称 (下同)

      as

      SELECTBLOCK   //查询语句

      eg: 

        create  view  myview  as  select   name,age,sex from users  where  id   > 10;

      查询视图:  select   FIELDS  from VIEWNAME  

    触发器:

      create trigger  自定义名称

      after | before       insert | update | delete     on  表名  for each row

      begin

        SQL语句集

      end

        eg: 

          create trigger mytrigger AFTER  insert on users for each row

          BEGIN 

            insert into depts(`deptname`,`create_at`) VALUES('测试','2018-06-19 20:20:20');

            update depts set create_at = CURRENT_TIMESTAMP where id=1827;

          END

    事件: 

      请参考:https://www.cnblogs.com/bobi-PHP-blog/p/7506306.html

      

      

    

  

    

 

   

  

posted @ 2018-11-16 23:44  沸水香茶  阅读(628)  评论(1编辑  收藏  举报