常用数据库 SQL 命令详解（下）

CREATE FUNCTION fn_name(func_parameter[,...])
RETURNS type
[characteristic...]
routine_body
1.
2.
3.
4.

ALTER FUNCTION fn_name [characteristic...]
1.

DROP FUNCTION  [IF EXISTS]  fn_name;
1.

SHOW FUNCTION STATUS [LIKE 'pattern']
1.

SHOW FUNCTION STATUS LIKE 'user_function';
1.

SHOW CREATE FUNCTION fn_name;
1.

CREATE TABLE `t_user` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',
  `user_name` varchar(5) DEFAULT NULL COMMENT '用户名',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1.
2.
3.
4.
5.
6.

INSERT INTO t_user (user_name, age)
VALUES('张三',24),('李四',25),('王五',26),('赵六',27);
1.
2.

-- 创建一个函数
DELIMITER $$

-- 开始创建函数
CREATE FUNCTION user_function(v_id INT)
RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
  -- 定义变量
  DECLARE userName VARCHAR(50);
  -- 给定义的变量赋值
  SELECT user_name INTO userName FROM t_user
  WHERE user_id = v_id;
  -- 返回函数处理结果
  RETURN userName;
END;

-- 函数创建定界符
DELIMITER ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

//查询用户ID为1的信息
SELECT user_function(1);
1.
2.

DROP FUNCTION  IF EXISTS  user_function;
1.

CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
1.

DELIMITER $$
或
DELIMITER //
1.
2.
3.

DELIMITER $$
CREATE PROCEDURE user_procedure(IN v_id int,OUT userName varchar(255))  
    BEGIN
    SELECT user_name as userName FROM t_user WHERE user_id = v_id;
    END $$ 
DELIMITER ;
1.
2.
3.
4.
5.
6.

-- @out为输出参数
CALL user_procedure(1, @out);
1.
2.

张三
1.

DROP PROCEDURE [IF EXISTS]  proc_name;
1.

DROP PROCEDURE IF EXISTS  user_procedure;
1.

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
  [trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

CREATE TRIGGER trig1 AFTER INSERT
ON t_user FOR EACH ROW
INSERT INTO t_time VALUES(NOW());
1.
2.
3.

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表
END;
1.
2.
3.
4.
5.

DELIMITER //
CREATE TRIGGER trig2 AFTER INSERT
ON t_user FOR EACH ROW
BEGIN
INSERT INTO t_time VALUES(NOW());
INSERT INTO t_time VALUES(NOW());
END//
DELIMITER ;
1.
2.
3.
4.
5.
6.
7.
8.

SHOW TRIGGERS;
1.

select * from information_schema.triggers where trigger_name='trig1';
1.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
1.

DROP TRIGGER IF EXISTS trig1
1.

#创建一个表test_db，字段内容为id,name
create table test_db(id int,name char(10));

# 设置id主键
alter table test_db add primary key(id);

# 将id主键设置为自增长模式
alter table test_db modify id int auto_increment;
1.
2.
3.
4.
5.
6.
7.
8.

CREATE TABLE `sequence` (
  `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字',
  `current_value` int(11) NOT NULL COMMENT '序列的当前值',
  `increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1.
2.
3.
4.
5.
6.

BEGIN 
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence 
          WHERE name = seq_name; 
     RETURN value; 
END
1.
2.
3.
4.
5.
6.
7.
8.

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

# 添加一个sequence名称和初始值，以及自增幅度
INSERT INTO sequence VALUES ('testSeq', 0, 1);

#设置指定sequence的初始值
SELECT SETVAL('testSeq', 10);

#查询指定sequence的当前值
SELECT CURRVAL('testSeq');

#查询指定sequence的下一个值
SELECT NEXTVAL('testSeq');
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
1.

select uuid();
1.

select UUID_SHORT();
1.

select * from mysql.user;
1.

# 格式
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
1.
2.

# 例子，创建一个用户名为admin,密码123456，可以本地访问的用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY '123456';
1.
2.

# 格式
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
1.
2.

# 例子，将用户名admin,密码修改为456789，可以本地访问的用户
SET PASSWORD FOR 'admin'@'localhost' = PASSWORD("456789");
1.
2.

# 格式
DROP USER 'username'@'host';
1.
2.

# 例子，删除用户名为admin的用户
DROP USER 'admin'@'localhost';
1.
2.

#刷新操作使其生效
flush privileges
1.
2.

# 格式
SHOW GRANTS FOR 'username'@'host'
1.
2.

# 查询用户名为 'root'@'%'的权限信息
SHOW GRANTS FOR 'root'@'%'
1.
2.

# 格式
GRANT privileges ON databasename.tablename TO 'username'@'host'
1.
2.

grant all privileges on test_db.* to 'testuser'@'localhost';
1.

grant all privileges on *.* to 'testuser'@'localhost';
1.

grant all privileges on test_db.user_info to 'testuser'@'localhost';
1.

grant all privileges on *.* to 'testuser'@'localhost';
1.

grant select on *.* to 'testuser'@'localhost';
1.

grant select,insert,update,delete on *.* to 'testuser'@'localhost';
1.

grant all privileges on *.* to 'testuser'@'192.168.1.100';
1.

# bind-address = 127.0.0.1
1.

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
1.

grant all privileges on *.* to 'root'@'%'  identified by '123456';
1.

grant all privileges on *.* to 'root'@'localhost'  identified by '123456';
1.

flush privileges;
1.

#例如，创建一个admin用户，密码为admin
grant all privileges on *.* to 'admin'@'%' identified by 'admin';

#刷新MySQL的系统权限相关表方可生效
flush privileges;
1.
2.
3.
4.
5.