数据库语句(mysql)

查看所有数据库名字                   show databases;             
创建一个数据库名字是db1         create database db1;     
查看数据库的详情                      show create database  db1;                                           
创建数据库的时候指定字符集     create database db2 character set gbk;            
删除数据库                               drop database db2;                                         
修改数据库字符集                     alter database db1 character set gbk;  
使用数据库db1                         use db1;                             
创建表（（                                                                                                                
create table person(name varchar(10), age int);
create table student(id varchar(3), name varchar(10), gender varchar, chinese int, math int, english int); 
            ））
查看所有表                               show tables;                                                      
查看表详情                               show create table person;                                                                 
创建表并指定引擎和字符集        create table t1(id int, name varchar(10)) engine = myisam charset=gbk;       
查看字段信息                           desc t1;   
复制表结构                              create table t3 like t1; 
复制表信息                             create table t4 select * from t1 where id = 1;
                                              create table t5 select * from t1;
修改表名                                    rename table t1 to t2;   
修改表的引擎和字符集                alter table t2 engine = innodb charset = utf8;
最后位置添加                             alter table t2 add id int;
最前面位置添加                          alter table t2 add moneyy int first;
在某个字段后加添加                    alter table t2 add genter varchar(5) after age;
删除字段                                     alter table t2 drop genter;
修改 字段名字和类型                    alter table t2 change moneyy qian varchar(10); 
修改字段的位置到最前                  alter table t2 modify name varchar(10) first;
修改字段的位置到某个字段后        alter table t2 modify name varchar(10) after id;
删除表                                         drop  table t2;

插入数据
全表插入                                     insert into student values(1021,'li',88,89,86);（每个字段都符值 顺序要和表字段一致）
指定插入                                     insert into student (id,name,math) values (1021,'sds', 85);      
批量插入                                     insert into student values(1021,'li',88,89,86)，(1022,'kk',88,89,86);
查看所有数据                              select * from student; 
查询指定字段                              select id,name from student; 
条件查询                                     select * from student where name = 'li';                        

修改数据                                      update student set math = 120;
修改指定数据                                update student set math = 121 where name = 'li';
删除数据                                      delete from  student where id =1;
                                                    truncate [table] student;
设置主键                                       create table t1(id int primary key , name varchar(10));
主键+自增                                    create table t1(id int primary key auto_increment , name varchar(10));
注释                                   create table t1(id int primary key auto_increment comment'这是id' , name varchar(10) comment '这是名字');

查看客户端自动提交的状态               show variables like '%autocommit%';/select @@autocommit;
             关闭                                    set autocommit = 0;
            打开                                       set autocommit = 1;
           手动提交                                 commit;
保存回滚点                                        savepoint  s1(标识）；
回滚到指定的标识点                           rollback to s1;
删除事务标识点                                  release savepoint s1;

去重                              select  distinct  name  from t3;
包含                            select name from t3 where comm in (1000,1203);
                                  select name from t3 where comm between 1000 and 1203;
模糊查询like 
    _    代表单个未知字符
   %   代表0个或多个未知字符
举例：：
           包含字符a                           :    %a%
           以a开头                              ： a%
           以a结尾                               ： %a
          第二个字符是a                      :    _a%
         倒数第三个字符是a                 :%a__
        第二个字符是a 最后一个字符是b:_a%b
                              select * from t3 where name like '_a%b%';
排序    order by
         写在 where 后面 没有where 写在最后
                             select  * from t3 where  sex = 0 order by comm desc;
         by 的后面写排序的字段名称
        升序 asc   降序 desc  
                                select name from t3 order by comm desc;
分页查询    limit
        limit  跳过数量，每页条数
          limit(页数-1)*每页条数,每页条数
                               select * from t3 order by comm limit 0,4;
获取当前日期+时间     select now();
获取当前日期            select curdate();
获取当前时间            select curtime();
时间差                                   select timediff(curtime(),'15:10:00');
日期差                                   select datediff(curdate(),'2002-07-03');
从年月日时分秒中提取年月日和提取时分秒  select date (now());       select time(now());
从年月日时分秒中提取年，月，日，时，分，秒
                         select  extract(year from now());
                         select extract(month from now());
                         select extract(day from now());
                         select extract(hour from now());
                         select extract(minute from now());
                         select extract(second from now());
                                [               select year(now());
                                               select month(now());
                                               select day(now());
                                               select hour(now());
                                               select miute(now());
                                              select second(now());
                                                                                                 ]
下一月(此方法对于12月加1到一月份，而不是13月份)
select date_add(curdate(),interval 1  month); 
日期格式化函数
格式：date_format(date,fromt)
       %Y 四位年 , %r两位年 , %m 两位月, %c 一位月份 , %d 日, %H 24小时 , %h 12小时 ,%i 分, %s 秒        把now 转换成****年**月**日**时**分**秒的格式 
 select date_format(now(),'%Y年%m月%d日%H时%i分%s秒');
   把 非标准格式的时间转成标准格式        str_to_date(非标准格式的时间，格式)；   
把 14.08.2008 08:00:00转成标准格式
select str_to_date('14.08.2008 08:00:00','%d.%m.%Y %H:%i:%s');    
计算年龄
1.select extract( year from now())-extract(year from sbirthday )from student where sno = 101;  //年份差

更推荐第二种
 2.select truncate(datediff(curdate(),  date(sbirthday))/365,0) from student;     //获取日期差截取保留小数点后0位
if函数
select if(1>0,'true','false');
ifnull函数
        age = ifnull(x,y)如果x的值为null 则age的值为y,如果x不为null,则age的值为x;
update sss set name = ifnull(name,'哈哈哈');
select ifnull(name,'空') from stu;

求和              select sum(comm) from t3;
平均               select avg(comm) from t3;
最大值           select max(comm) from t3;     
最小值            select min(comm) from t3; 
统计条数         select count(*) from t3;   

字符串拼接      select ename ,concat(sal,'元') from emp;
多个字符串之间加上第一个字符串                 select concat_ws('A','b','c','d','e');
获取字符串的长度   select name,char_length(ename) from emp  where id = 1;    
返回字符串的字节数              select length('嗯');
获取字符在另外一个字符串中出现的位置 (不是角标)     select instr('abcde','d');
插入字符串      insert (srt,start,length,newstr)               select insert('abcdef',3,2,'m');
使z添加到x中达到y长度(x,y,z) 添加到开头 ,末尾         select lpad('asdfgh',11,'ASD');          select rpad('asdfgh',11,'ASD');

转大写 转小写   select upper('asd');    select lower('ASD');
从左边截取 从右边截取    select  left('asdfg',2);    select   right('asdfdvc',2);
去除字符串两端的空格     select trim('   a     b     '); 
截取字符串     select substring('asdczxc',2);   select substring('asdcxf',2,3);
重复   select repeat('ab',2);
替换  replace(str,old,new); select replace('abcde','c','m');
反转    select reverse('abc');    

 取绝对值        
select abs(-100);
向上取整     
select ceil(3.01);
向下取整
select floor(3.66);
四舍五入
select round(23.8);
保留小数
select round(23.862,2)
保留小数（非四舍五入）
select truncate(23.986,3);
pow(x,y)  计算x值的y次方                   
select pow(2,5);
求平方根
select sqrt(9);
取随机数 填入数字后随即数对应固定值，取值范围是0~1       
select rand();--不固定               select rand(1);--固定
负数返回-1，0返回0，正数返回1            
select sign(-10);--   -1   select sign(0);--  0 select sign(10);--   1

分组查询   group by
select 部门,字段from 表名 group by 部门;
select dept,max(money) from temp group by dept;
having（与group by 一起使用）
where 后面写普通字段的过滤条件，having后面写聚合函数的过滤条件
select shop_id,sroce from shop_all group by shop_id having avg(sroce) > 600;

嵌套查询(子查询)
select * from t1 where age = (select max(age)from t1);
(创建表（必须有别名）的时候导入数据)
create table temp as (select * from t1 where id = 2);
关联查询
等值连接格式： select * from A,B where A.x=B.x and A.age=18;
内连接: 
select * from A [inner] join B on A.x=B.x where A.age=18;
select * from temp t join depet d on t.id = d.tempid where t. name like '王%_b'
外连接：
          左外连接：(left join on)
                  select *,so.examdate from student st left join score so on st.scode = so.courseid
          右外连接:(right join on)      
                  select *,so.examdate from student st right join score so on st.scode = so.courseid

自定义函数
无参函数 fn1替换now()
create function fn1()   returns date                     
begin
return now();
end

有参函数  
 create function fn2( a int, b int) returns int 
begin
return a+b;
end;

循环
while循环
delare i int default 1;//定义局部变量
while 条件 do 
sql语句...
...
set i = i+1;
end while;

create function f1() returns int 
begin
declare i int default 1;
while i <10 do 
insert into t4(sal) values (i);
set i = i+1;
end while;
return 1;
end#

创建索引
create index语句
给student表sid字段创建索引        create index indx_sid on student(sid asc);
alter table语句
给teacher表tid字段增加索引         alter table teacher add index indx_tid(tid);
create table语句
创建shopping表时指定pid字段为索引 create table shopping(pid int not null,name varchar(10),index idx_pid(pid));
删除索引                            drop index[索引名] on table_name;
显示索引信息                      show index from table_name;

外键              alter table t1 add constraint 外键名 foreign key(列名) references 另一个表名(列);

创建视图                                       create view 视图名 as(子查询)；
查看视图                                       select * from 视图名;
查看所有视图                                show table status where comment = 'view';
修改视图                                     create or replace view 视图名 as(子查询)；
                                                   alter view 视图名 as(子查询);
                                                   update 视图名 set age= 25 where id = 1;
删除视图数据                               delete from 视图名 where id = 1;
插入数据                                     insert into 视图名 values (表的全部字段信息)；
删除视图                                     drop view 视图名；

存储过程

存储过程的语法：
         create procedure 存储过程名(参数)
         begin
                   sql语句.......
                   sql语句.......
         end;
调用存储过程的语法：
         call 存储过程名(实参);
注：sql语句后与end后均为分号(默认;)会导致系统走到sql语句及结束，所有要声明语句结束符，可自定义      
       delimiter #
        delimiter /

例：
 delimiter #
    create procedure pro_a()
     begin
           select * from student;
           .........
      end#  
  call pro_a()#

带参数的存储过程：
     in            out          inout         
in输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
out输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
inout输入输出参数:即表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)

in输入类型：
                         默认为in
       例：
           给存储过程一个学号，存储过程显示学生信息
     delimit #
        create procedure pro_b(in x int )
          begin
                    select * from student where id = x;
          end#
       call pro_b#

           //存储过程中limit后不能使用参数
delimit #
 create procedure aaa(in a int ,in b int )
begin
 select * from student limit a,b;
end#     
--错误

out输出类型：
            例：
                 delimit #
                create procedure pro_r1(out x int)
                  begin
                      select max(degree) into x from student where class = 3;
                  end#
               set @x = 1#
               call pro_r1(@x) #
               select @x#
 注：窗口不关，@x就可以使用
        调用out存储过程前，需要定义一个变量：set @变量类名=值
 例：
             给存储过程一个系标号，存储过程返回这个系的名字
        delimiter  #
        create procedure pro_r2(in a int,out name varchar(10))
        begin 
           select dname into name from student where id = a;
         end#
         set @a = 'zs'#
         call pro_r2(3,@a)#
         select @a# 

inout输入类型：
               例：
                    传入一个班级编号，返回这个班级的最高分
                   delimiter #
                    create procedure pro_r3(inout x int)
              begin
                    select max(degree) into x from student where sid = x;
               end#
               set @s = 1#
              call pro_r3(@s)#
              select * from @s#

查看所有的存储过程：           show procedure status;
查看存储过程的详细信息：      show create procedure 存储过程名;
删除存储过程:                         drop procedure [if exists]存储过程名;

case-when函数
select case
when 1>0 then 'true'
when 1<0 then 'flase'
else 'default'
end;

系统函数
select version();
select user();
select database();

加密函数
select password(123); mysql自带  不可逆
select md5(123);  可逆

万能金句

select ... from ... where ... group by ... having ... order by ... limit ... ;