mysql密码存储过程_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;WH