mysql：用户管理、索引、视图、函数、存储过程
#创建一个用户并设置密码，注意IP地址要是登录mysql电脑的IP地址
USE mysql
CREATE USER lisi@'192.168.149.1' IDENTIFIED BY "111111"
CREATE USER lisi1@'192.168.149.10' IDENTIFIED BY "111111"
SELECT * FROM USER
#删除一个用户
DROP USER zhangsan@"192.168.149.1"
DROP USER lisi1@"192.168.149.10"
DELETE FROM USER WHERE USER="lisi1"
SELECT * FROM USER
#修改密码
UPDATE USER SET PASSWORD=PASSWORD("123456") WHERE USER="lisi"
#使新密码立即生效
FLUSH PRIVILEGES
#给lisi@"192.168.149.1"授权能查询test1数据库中的table1表的id属性
GRANT SELECT(id) ON test1.table1 TO "lisi"@"192.168.149.1" IDENTIFIED BY "123456"
#查看lisi@"192.168.149.1"的权限
SHOW GRANTS FOR lisi@"192.168.149.1"
#给lisi@"192.168.149.1"授权，让它能查询整个table1表的所有属性
GRANT SELECT ON test1.table1 TO "lisi"@"192.168.149.1" IDENTIFIED BY "123456"
#查看lisi@"192.168.149.1"的权限
SHOW GRANTS FOR lisi@"192.168.149.1"
#给lisi@"192.168.149.1"授权，让它能够查询test1数据库中的所有表的所有属性
GRANT SELECT ON test1.* TO "lisi"@"192.168.149.1" IDENTIFIED BY "123456"
#查看lisi@"192.168.149.1"的权限
SHOW GRANTS FOR lisi@"192.168.149.1"
#权限没起作时，请刷新权限,如果还是不行，只能重新登录一次
FLUSH PRIVILEGES
#取消权限 ，取消对test1数据库所有表的查询权限
REVOKE SELECT ON test1.* FROM "lisi"@"192.168.149.1" #此时，只能看到test1中的table1数据表了，看不到table2
#查看lisi@"192.168.149.1"的权限
SHOW GRANTS FOR lisi@"192.168.149.1"
#取消权限，取消对test1数据库table1中所有属性的查询权限
REVOKE SELECT ON test1.table1 FROM "lisi"@"192.168.149.1"
#查看lisi@"192.168.149.1"的权限
SHOW GRANTS FOR lisi@"192.168.149.1"
#取消权限,取消对test1数据库table1中id的查询权限
REVOKE SELECT(id) ON test1.table1 FROM "lisi"@"192.168.149.1"
#给lisi@"192.168.149.1"授权能查询test1数据库中的table1表的id和name属性
GRANT SELECT(id,NAME) ON test1.table1 TO "lisi"@"192.168.149.1" IDENTIFIED BY "123456"
#查看lisi@"192.168.149.1"的权限
SHOW GRANTS FOR lisi@"192.168.149.1"
#取消权限,取消对test1数据库table1中id的查询权限
REVOKE SELECT(id) ON test1.table1 FROM "lisi"@"192.168.149.1" #此时lisi只能查询table1表中的name属性了，不能查询id属性了
#在定义了主键的表上，查看索引
SHOW INDEX FROM dept
#在没有定义主键的表上，查看索引
USE test1
SHOW INDEX FROM table1
#定义索引，方法1
CREATE INDEX index1 ON table1(id)
#查看索引
SHOW INDEX FROM table1
#定义索引，方法2
ALTER TABLE table2 ADD INDEX index1(id)
#查看索引
SHOW INDEX FROM table2
#定义索引，方法3
CREATE TABLE table3
(
id INT,
NAME VARCHAR(20),
INDEX index1(NAME)
)
#查看索引
SHOW INDEX FROM table3
#在table3上面删除索引
DROP INDEX index1 ON table3
#查看索引
SHOW INDEX FROM table3
#创建视图
CREATE VIEW view1
AS
SELECT * FROM table1
#通过视图查询数据
SELECT * FROM view1
#查看视图
SHOW TABLES
SHOW TABLE STATUS WHERE COMMENT="view"
#修改视图 方法1
ALTER VIEW view1
AS
SELECT * FROM table2
SELECT * FROM view1
SHOW TABLES
#修改视图 方法2
CREATE OR REPLACE VIEW view1
AS
SELECT * FROM table1
SHOW TABLES
SELECT * FROM view1
#通过视图修改原表的属性值
UPDATE view1 SET NAME="tigger" WHERE id=1
SELECT * FROM table1
SELECT * FROM view1
#对原表进行修改，视图中的数据也会修改
DELETE FROM table1 WHERE id=1
SELECT * FROM view1
#通过视图增加一行记录，原表中的记录也会增加
INSERT INTO view1 VALUES(1,"bird")
SELECT * FROM table1
#删除视图
DROP VIEW view1
SHOW TABLES
===================================================================
=========================================================
#查看函数功能的开关是否开启
SHOW VARIABLES LIKE "%func%"
#开启函数功能开关
SET GLOBAL log_bin_trust_function_creators=1
SHOW VARIABLES LIKE "%func%"
#创建函数1，不带函数
#因为sqlyog工具的原因，需要使用定界符，关键字是delimiter 后面定界符，表示程序的开始和结束
DELIMITER &
CREATE FUNCTION helloworld()
RETURNS VARCHAR(20)
BEGIN
RETURN "hello world!";
END&
DELIMITER
#调用函数
SELECT helloworld() AS hello
#创建函数2，带参数
DELIMITER $$
CREATE FUNCTION getdate(riqi DATETIME)
RETURNS VARCHAR(50)
BEGIN
RETURN DATE_FORMAT(riqi,"%Y年%m月%d日 %h时%i分%s秒");
END$$
DELIMITER
#调用函数
SELECT getdate("2017-07-19 10:40:30")
#删除函数
DROP FUNCTION getdate
#调用函数 2
SELECT NOW()
SELECT getdate(NOW())
#查看所有函数
SHOW FUNCTION STATUS
#查看具体某个函数
SHOW CREATE FUNCTION getdate
SHOW CREATE FUNCTION helloworld
——————————————————————————————————
存储过程：
数据准备：
create database test1 default charactor set utf8 collate utf8_general_ci
create table hr
(
id int,
name varchar(20),
deptno int,
salary int
)engine=innodb default charset=utf8
INSERT INTO hr VALUES(1,"zhangsan",1,2000);
INSERT INTO hr VALUES(2,"lisi",1,2100);
INSERT INTO hr VALUES(3,"小明",2,2500);
INSERT INTO hr VALUES(4,"小红",2,2200);
INSERT INTO hr VALUES(5,"小王",3,1800);
INSERT INTO hr VALUES(6,"小陈",3,1900);
INSERT INTO hr VALUES(7,"小赵",3,3000)
#写一个存储过程，统计部门员工人数
DELIMITER $$
CREATE PROCEDURE proce1(IN dno INT,OUT total_number INT)
BEGIN
SELECT COUNT(id) INTO total_number FROM hr WHERE deptno=dno;
END$$
DELIMITER
#调用存储过程
#测试1部门的员工人数
CALL proce1(1,@test)
SELECT @test AS 人数
SELECT @test
#验证2部门的员工人数
CALL proce1(2,@renshu)
SELECT @renshu AS 人数
#验证3部门的员工人数
CALL proce1(3,@test)
SELECT @test
#验证一个不存在的部门的员工人数
CALL proce1(4,@test)
SELECT @test
#查看数据库中所有的存储过程
SHOW PROCEDURE STATUS
#查看具体某一个存储过程
SHOW CREATE PROCEDURE proce1
#删除一个存储过程
DROP PROCEDURE pro;
DROP PROCEDURE pro1;
DROP PROCEDURE pro2
