MySQL资料之常规语句举例

◆ 赋权：

grant all privileges on rat.* to snow@localhost identified by 'xxx';

◆ 刷新权限：

flush privileges;

◆ 建表：

create table customer (
id int(11) NOT NULL AUTO_INCREMENT,  
up_sys varchar(100) not null comment '上游',
age  int(2) NOT NULL,
primary key (up_sys,down_le)
)comment '对照表'

◆ 创建存储过程：

#不带参数：

DELIMITER //   
CREATE PROCEDURE abc()
    BEGIN
    SELECT count(*)  FROM fireworks; 
    END;
//

#带参数：‘
DELIMITER //  
CREATE PROCEDURE demo_in_parameter(IN p_in int)  
BEGIN   
SELECT p_in;   
SET p_in=2;   
SELECT p_in;   
END; 
//  
DELIMITER ; 

注：如果不写DELIMITER会报如下异常：

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3    0.000 sec

◆ 执行存储过程：

SET @p_in=1; 
CALL demo_in_parameter(@p_in);  

◆ 修改字段：

alter table test change column id id int not null default 0 comment '测试表id'

◆ 异常：

1、更新数据时报错

update upstream_sys set comm = ”

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.。

解决方案：

SET SQL_SAFE_UPDATES = 0;

未完待续……