MySQL

1.描述表得结构

     desc table_name;

2.删除表中得数据

    delete from table_name where [约束条件]

3.可变浮点数定义用decimal（n,m）

    n :是浮点数的（二进制）位数
    m:是小数分的位数
    eg:decimal(10,2)

4.标准的存储过程模板如下：

drop procedure if exists pro_1;
delimiter $
create procedure pro_1(


)
begin
end $
delimiter ;
5.存储过程的基本语法

 <1>.设置变量                 

set @var_name = 0,@var_id = 12;
select @var_name , @var_id;
 <2>.流程控制

select username,
case username
when 'belong' then 'handsome'
when 'tom' then 'sou'
else 'ban'
end
from user;
结果如下： 

  红色部分就是查询的第二个字段

<3>.if

select * ,if(id>4,'男','女') sex from user;
  重命名可以省略 as 因为查询的值都在select 与from之间
所以表达式要写在select 与from之间

<4>.ifnull 

ifnull(expr1,expr2):如果expr1 为空 返回 expr2 否则返回expr1
set @a:=null;
select ifnull(@a,2);
set @a:=10;
select ifnull(@a,19);

<5>.NULLIF(expr1,expr2):

      

 如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为expr1这和Case when expr1  = expr2 then NULL ELSE expr1 END相同
        eg:
       select NULLIF(1,1)
<6>.存储过程中为什么要使用DELIMITER

DELIMITER 是分隔符的意思，因为MySQL默认是以“；”为分隔符的，如果我们没有声明分隔符的话，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程就会报错，所以要事先用DELIMITER关键字声明当前段分割符，这样MySQL才会将“；”当做存储过程中的代码，不会执行这些代码，用完之后就把分隔符还原

<7>.存储过程与函数区别

存储过程参数分为输入和输出两类
用out和in表示存储过程用call来调用
存储过程参数还有inout型的参数 既可以当输入也可以当输出
存储过程中的控制语句都要有结束标志end


<8>.分支语句if…then…else ……end if；

DELIMITER $
create procedure pro_1()
begin
declare var int ;
set var = parameter +1;
if var=0 then
    select * from user where id = 1058;
end if;
if parameter = 0 then 
    select * from user where id = 1060;
else 
    select * from user where id = 1059;
end if;
end $
DELIMITER ;
<9>.While循环：

DELIMITER $
create procedure pro_2()
begin
declare var int;
set var = 0;
while var<6 do
    select var;
    set var = var +1;
end while;
end $
DELIMITER ;
<10>.Repeat循环：

DELIMITER $
create procedure pro_3()
begin
declare v int;
set v:=0;
repeat
    select v;
    set v = v+1;
    until v>-5
end repeat;
end $
DELIMITER ;
<11>.LOOP循环：

DELIMITER $
create procedure pro_4()
begin
declare v int;
set v:=0;
LOOP_Lable:loop
    select v;
    set v = v+1;
    if v >= 5 then
        leave LOOP_Lable;#离开循环
    end if;
end loop;
end $
DELIMITER ;
6.MySQL中declare 与 set 的区别

MySQL存储过程中，定义变量有两种方式：
(1).使用set或select直接赋值，变量名以 @ 开头.例如:set @var=1;可以在一个会话的任何地方声明，作用域是整个会话，称为会话变量。
(2).以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量，例如：
DECLARE var1  INT DEFAULT 0;  主要用在存储过程中，或者是给存储传参数中。
两者的区别是：
在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。在存储过程中，使用动态语句，预处理时，动态内容必须赋给一个会话变量。

例：
set @v_sql= sqltext;
PREPARE stmt FROM @v_sql;  
EXECUTE stmt;     
DEALLOCATE PREPARE stmt;
6.5函数模板

drop function if exists fun_1;
delimiter $
create function fun_1()returns int 
begin
	    代码块
    return 1;
end $
delimiter ;
7.sql语句

<1>.显示创建的存储过程语句
        show create procedure pro_1<2>.显示所有存储过程的状态详细信息        show procedure status;<3>.显示所有函数的详细信息        show function status;<4>.显示具体函数得创建语句        show createfunction fun_1

8.Mysql存储过程和函数区别介绍

存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。 
存储过程和函数存在以下几个区别： （1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。 （2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。 （3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。（4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

9.使用存储过程与函数的原则：

（1）.如果需要返回多个值和不放回值，就是用存储过程；如果只需要返回一个值，就用函数。
（2）.函数不需要使用IN模式和OUT模式，它认为所有的参数都是IN的
（3）.过程一般用于执行一个指定的动作，函数一般用于就计算和反回一个值。（4）.可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。

10.更新数据

update movies set views = views+1 where Vid = 5;
实现字段加1

11.concat（）函数中间的参数可以链接无数个，用逗号隔开

12.存储过程中也可以向高级语言一样可以定义准备语句，用于执行SQL语句 select insert ...等等

13.在存储过程中要想得到select 的查询结果就一定要用准备语句，才可以把结果在存储过程外得到返回值

prepare var_name from sql_str;#sql_str是select的字符串
execute var_name;
14.join on 就是按on的条件将所加入的表与原表组合在一起

15.mysql 导出数据库 

 mysqldump -u username -p db_name > 导出的数据库文件

<2> 备份多个数据库

mysqldump -u username -p  --database dbname1 dbname2 > backup.sql;
<3>.备份所有数据库

mysqldump -u username -p --all-database > backup.sql;
<4>.数据库的还原

 mysql -u root -p [dbname] <backup.sql 
eg：mysql -u root -p < backup.sql;
<5>.只恢复其中的一个数据库

    mysql -u root -p db_name --one-database < backup.sql;

16.mysql把用户的信息都存到了名为mysql的数据的数据库里来管理用户

 select user from user;//查看所有用户
17.显示用户的权限

    show grants for user_name;

18.为用户分配所有权限 

    grant  all on *.* to username;

19.declare continue handler for sqlstate '02000' set done = 1; 

20.查看表的属性结构

<1>.desc <table_name>;
<2>.show columns from <table_name>
24.删除表的某个字段

alter table <table_name> drop column <column_name>;
25.当表中的字段名和MySQL的关键字重名时要使用 数字键旁边的点作为转义才可以改变

<span data-wiz-span="data-wiz-span" style="font-size: 1.067rem;">Untitled</span>

alter table user change `password varchar` password varchar(20) character set utf8 not null
26.表重命名

<span data-wiz-span="data-wiz-span" style="font-size: 1.067rem;">Untitled</span>

alter table table_name change column_name rename_column_name column_type;
27.java编程时只有存储过程中有parementstatement 语句是才能用execute 
28.重命名数据库
rename database music to audio;
29.mysql引擎类型区别
（1）.InnoDB:是一个可靠地事物处理引擎，但是它不支持全文检索
（2）.MEMORY:在功能等同于MyISAM，但由于数据库在内存中，速度很快，适用于临时表
（3）.MyISAM:性能极高的引擎，支持全文搜索，不支持事物处理。
30.Mysql在进行使用UNION时column的数量一定要相等
31.Mysql添加主键
alter table video_type_config ADD
CONSTRAINT pk_Video_no PRIMARY KEY (video_no);
32.Mysql添加注释
表注释
ALTER TABLE table_name COMMENT='这是表的注释';  

字段注释
ALTER table table_name 
MODIFY `column_name` datetime DEFAULT NULL COMMENT '这是字段的注释'
33.查看字段的注释
SHOW FULL COLUMNS  FROM video_type_config;
34.MySQL查看具体字段使用情况所占空间
#schema表示的是数据库(模式下有多个数据库schema：db=1：n)
-- use information_schema;

#table_schema表示的是数据库
SELECT TABLE_NAME, DATA_LENGTH + INDEX_LENGTH, TABLE_ROWS
  FROM TABLES
 WHERE TABLE_SCHEMA = 'video'
   AND TABLE_NAME = 'video_type_config';
35.MySQL查看变量的值
select @@global.sql_mode
36.MySQL为变量赋值
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
37.MySQL查看表的状态（引擎、使用情况）
show table status like 'video_type_config';
38.mysql中查看表的状态采用G结尾的方式只能在命令行中进行，在navicat中不好使
show table status like 'video_type_config' G;

mysql> show table status like 'video_type_config' G;
*************************** 1. row ***************************
           Name: video_type_config
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 156
 Avg_row_length: 105
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-04-28 09:29:48
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: 地址和类型配置表
1 row in set (0.01 sec)
39.MySQL计算字段的存储情况（按字节）
SELECT
	CONCAT(
		SUM(CHAR_LENGTH(video_no)) / 1024,
		'Kb'
	) video_no,
	CONCAT(
		SUM(CHAR_LENGTH(video_type)) / 1024,
		'Kb'
	) video_type
FROM
	video_type_config
ORDER BY
	video_no,
	video_type;
40.MySQL表的最大允许多少字段
在mysql中，每个数据库最多可创建20亿个表，一个表允许定义1024列，

41.MySql进行 group by 时，select后面的列一定要在分组内（group by），要不列的行数不对应会报错
42.MySQL查看索引使用状态
show status like ‘Handler_read%';
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效
43.Mysql从win的mysql导出数据库一定要用cmd导出，不能使用power shell，否则会出现：如下代码
ERROR: ASCII '' appeared in the statement, but this is not allowed unless









【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Mrbelong/p/7634675.html