mysql设置用户名密码，存储过程，触发器

mysql的总结的练习题

修改root用户密码 
mysql -u root; 
mysql> use mysql; 
mysql> UPDATE user SET Password = PASSWORD(‘newpass’) WHERE user = ‘root’; 
mysql> FLUSH PRIVILEGES; 
登陆root用户，给用户赋予创建数据库的权限 ；用户：tester，密码：wangan
创建用户： 
CREATE USER ‘tester’@’localhost’ IDENTIFIED BY ‘wangan’; 
grant all privileges on . to tester@localhost identified by ‘wangan’; 
之后登陆tester用户 
mysql -u tester -p 
创建数据库 
CREATE DATABASE idc DEFAULT CHARACTER SET utf8 ;

student
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'name1',19,70,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'name2',60,73,85);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'name3',80,78,97);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'name4',98,23,42);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'name5',55,87,66);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'name6',68,79,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'name7',20,48,29);


SHOW DATABASES

CREATE DATABASE shop DEFAULT CHARACTER SET utf8  
SHOW DATABASES 
SHOW CREATE DATABASE shop
DROP DATABASE shop
student1
CREATE TABLE student1(
    sid INT,
    sname VARCHAR(20),
    sage INT

)
DESC student
DROP TABLE student1

SHOW TABLES
ALTER TABLE student ADD COLUMN sgender VARCHAR(2)
ALTER TABLE student MODIFY COLUMN NAME VARCHAR(40)
ALTER TABLE student  DROP COLUMN sgender

ALTER TABLE student CHANGE COLUMN sgender gender VARCHAR(2)

ALTER TABLE teacher RENAME TO student
DESC teacher        

ALTER TABLE student ADD COLUMN java就业班 VARCHAR(10)

SELECT * FROM student 
SELECT id ,NAME,(english+math) AS '成绩' FROM student

SELECT  DISTINCT  gender FROM student
SELECT * FROM student WHERE gender <> '男'

SELECT COUNT(id) FROM student -- 总记录
SELECT * FROM student LIMIT 1,2 -- 从第2 条记录开始查询,查2条
SELECT * FROM student ORDER BY id DESC LIMIT 2,5 -- 排序

SELECT gender, COUNT(gender) FROM student GROUP BY gender
SELECT gender,COUNT(gender)  gen FROM student GROUP BY gender HAVING gen>2 
ALTER TABLE student ADD hi VARCHAR(10)
ALTER TABLE student DROP hi


CREATE TABLE student1(
  id INT ZEROFILL PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)


SELECT * FROM student1

INSERT INTO student1(NAME) VALUES ('张三')
DELETE FROM student1
TRUNCATE TABLE student1

CREATE TABLE dept(
  id INT PRIMARY KEY,
  deptName VARCHAR(20)
)

CREATE TABLE emp(
  id INT PRIMARY KEY,
  empName VARCHAR(20),
  dept_id INT,
  CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES dept(id)
)

1 存储过程
DROP PROCEDURE IF EXISTS pro_test; 
DELIMITER –声明存储过程的结束符CREATEPROCEDUREprotest()BEGINSELECT∗FROMstudent;SELECTCOUNT(1)FROMstudent;END

CALL pro_test() -- 执行存储过程

2 带输入参数的 
DROP PROCEDURE IF EXISTS pro_findById; 
DELIMITER CREATEPROCEDUREprofindById(INaidINT)BEGINSELECT∗FROMstudentWHEREid=aid;END

CALL pro_findById(2)

3 带输出参数 
DROP PROCEDURE IF EXISTS pro_out; 
DELIMITER CREATEPROCEDUREproout(OUTstrVARCHAR(20))BEGINSETstr=′hell1234′;END

DROP PROCEDURE IF EXISTS pro_testOut;
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数
BEGIN
        -- 给参数赋值
    SET str='helljava';
END $

CALL pro_testOut(@NAME) -- 定义会话变量,接受存储过程的值
SELECT @NAME -- 接收输出参数的值

4.带有输入输出参数 
DELIMITER CREATEPROCEDUREproinout(INOUTnINT)BEGINSELECTn;SETn=500;END

-- 调用
CALL pro_inout(@n)

-- 查询
SELECT @n

-- 调用
SET @n=10

-- 带条件判断的存储过程
DELIMITER $
CREATE PROCEDURE pro_testif(IN num INT, OUT str VARCHAR(20))
BEGIN 
    IF num=1 THEN 
        SET str='星期一';
    ELSEIF num=2 THEN 
        SET str='星期二';
    ELSEIF num=3 THEN 
        SET str='星期三';
    ELSE
        SET str='输入有误';
    END IF;
END $

CALL pro_testif(1,@str)
SELECT @str

带有循环功能的 
DROP PROCEDURE IF EXISTS pro_testwhile; 
DELIMITER CREATEPROCEDUREprotestwhile(INnumINT,OUTresultINT)BEGINDECLAREiINTDEFAULT1;DECLAREvsumINTDEFAULT0;WHILEi<=numDOSETvsum=i+vsum;SETi=i+1;ENDWHILE;SETresult=vsum;END

CALL pro_testwhile(10,@result)
SELECT @result

使用查询的结果赋值给变量 into 
DELIMITER CREATEPROCEDUREprotestinto(INeidINT,OUTvnameVARCHAR(20))BEGINSELECTempnameINTOvnameFROMempWHEREid=eid;END

CALL pro_testinto(1,@vname)
SELECT @vname

触发器

当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！ 
例如： 当向员工表插入一条记录时，希望同时往日志表插入数据 
员工日志表 
CREATE TABLE emp_log( 
content VARCHAR(100) 
)

CREATE TRIGGER tri_empAdd AFTER INSERT ON emp FOR EACH ROW  -- 删除和修改一样,将INSERT改成UPDATE 和 delete
INSERT INTO emp_log(content) VALUES ('员工表插入了一条记录') 

INSERT INTO emp(empName,dept_id) VALUES ('刘备',1)

权限问题 
USE mysql 
SELECT * FROM USER 
修改密码 
UPDATE USER SET PASSWORD(‘123456’) WHERE USER=’root’ 
分配权限账户 
GRANT SELECT ON test.emp_log TO root 
UPDATE emp_log SET content=’hihoa’

创建 
mysql> create user zx_root IDENTIFIED by ‘xxxxx’; //identified by 会将纯文本密码加密作为散列值存储 
修改 
mysql>rename user feng to newuser；//mysql 5之后可以使用，之前需要使用update 更新user表 
删除 
mysql>drop user newuser; //mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限 
更改密码 
mysql> set password for zx_root =password(‘xxxxxx’); 
mysql> update mysql.user set password=password(‘xxxx’) where user=’otheruser’ 
查看用户权限 
mysql> show grants for zx_root; 
赋予权限 
mysql> grant select on dmc_db.* to zx_root; 
回收权限 
mysql> revoke select on dmc_db.* from zx_root; //如果权限不存在会报错 
上面的命令也可使用多个权限同时赋予和回收，权限之间使用逗号分隔 
mysql> grant select，update，delete ，insert on dmc_db.* to zx_root; 
如果想立即看到结果使用 
flush privileges ; 
命令更新 

设置权限时必须给出一下信息
1，要授予的权限
2，被授予访问权限的数据库或表
3，用户名
grant和revoke可以在几个层次上控制访问权限
1，整个服务器，使用 grant ALL 和revoke ALL
2，整个数据库，使用on database.*
3，特点表，使用on database.table
4，特定的列
5，特定的存储过程

user表中host列的值的意义
% 匹配所有主机
localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接
127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问；
::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利

grant select, insert, update, delete on test.* to root@’%’

9.grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。
grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on test.* to developer@’192.168.0.%’;

grant alter on test.* to developer@’192.168.0.%’;

grant drop on test.* to developer@’192.168.0.%’;

转载于:https://my.oschina.net/sicongwang/blog/1590208