欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MYSQL数据库SQL语句集锦

程序员文章站 2022-06-26 09:13:34
*特别说明:FILED代表数据表字段,CONDITIONS代表where之后的条件,TABLENAME代表数据表名 []中括号内的内容代表 可有可无。 创建数据库 create database DATABASE; 删除数据库 drop database DATABASE 创建数据表 create ......

  *特别说明: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