mysql的總結(jié)的練習(xí)題

命令行登陸MySQL

在windows系統(tǒng)‘運行’一欄中輸入

mysql -uroot -p123456 test 

就可以直接進入登陸到MySQL服務(wù)器test數(shù)據(jù)庫

執(zhí)行MySQL dump數(shù)據(jù)文件义钉，進入windows cmd命令界面

mysql -uroot -p123456 test <D:\test_db-master\load_employees.dump

翻倍執(zhí)行插入數(shù)據(jù)鸟辅，即快速插入測試數(shù)據(jù),注意侯勉，id可以設(shè)置自增缺猛，這樣不用插入

INSERT into tablename(rate,create_time)  SELECT rate, create_time FROM tablename

修改root用戶密碼

mysql -u root;
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

登陸root用戶，給用戶賦予創(chuàng)建數(shù)據(jù)庫的權(quán)限 色查；用戶：XXX，密碼：XXX
創(chuàng)建用戶：

CREATE USER 'tester'@'localhost' IDENTIFIED BY 'wangan';
grant all privileges on *.* to tester@localhost identified by 'password';

之后登陸tester用戶
mysql -u tester -p
創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE idc DEFAULT CHARACTER SET utf8 ;
student
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'張小明',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李進',67,53,95);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李來財',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'張進寶',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黃蓉',75,65,30);


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就業(yè)班 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)
)

存儲過程

不帶參數(shù)

DROP PROCEDURE  IF EXISTS pro_test;
DELIMITER $ -- 聲明存儲過程的結(jié)束符
  CREATE PROCEDURE pro_test()
  BEGIN 
    SELECT * FROM student ;
    SELECT COUNT(1) FROM student;
  END$

CALL pro_test() -- 執(zhí)行存儲過程

帶輸入?yún)?shù)的

DROP PROCEDURE  IF EXISTS pro_findById;
DELIMITER $
CREATE PROCEDURE pro_findById(IN aid  INT)
  BEGIN 
    SELECT * FROM student WHERE id=aid;
END $

CALL pro_findById(2)

帶輸出參數(shù)

DROP PROCEDURE IF EXISTS  pro_out;
 DELIMITER $
 CREATE PROCEDURE pro_out(OUT str VARCHAR(20))
 BEGIN
    SET str='hell1234';
 END $

DROP PROCEDURE IF EXISTS pro_testOut;
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：輸出參數(shù)
BEGIN
        -- 給參數(shù)賦值
    SET str='helljava';
END $

CALL pro_testOut(@NAME) -- 定義會話變量,接受存儲過程的值
SELECT @NAME -- 接收輸出參數(shù)的值

帶有輸入輸出參數(shù)

DELIMITER $
CREATE PROCEDURE pro_inout(INOUT n INT)
BEGIN 
SELECT n;
SET n=500;
END $

-- 調(diào)用
CALL pro_inout(@n)

-- 查詢
SELECT @n

-- 調(diào)用
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

帶有循環(huán)功能的

DROP PROCEDURE IF EXISTS pro_testwhile;
DELIMITER $
CREATE PROCEDURE pro_testwhile(IN num INT, OUT result INT)
BEGIN 
    DECLARE i INT DEFAULT 1;
    DECLARE vsum INT DEFAULT 0;
    WHILE i<=num DO 
        SET vsum=i+vsum;
        SET i=i+1;
    END WHILE;
    SET result=vsum ;
END $

CALL pro_testwhile(10,@result)
SELECT @result

使用查詢的結(jié)果賦值給變量 into

DELIMITER $
CREATE PROCEDURE pro_testinto(IN eid INT,OUT vname VARCHAR(20))
BEGIN 
    SELECT empname INTO vname FROM emp WHERE id=eid;
END $ 

CALL pro_testinto(1,@vname)
SELECT @vname

觸發(fā)器

當(dāng)操作了某張表時撞芍，希望同時觸發(fā)一些動作/行為秧了，可以使用觸發(fā)器完成！序无！
例如： 當(dāng)向員工表插入一條記錄時验毡，希望同時往日志表插入數(shù)據(jù)
員工日志表

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)

權(quán)限問題

USE mysql
SELECT * FROM USER

修改密碼
UPDATE USER SET PASSWORD('123456') WHERE USER='root'
分配權(quán)限賬戶

GRANT SELECT ON test.emp_log TO root
UPDATE emp_log SET content='hihoa'

創(chuàng)建
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 刪除用戶權(quán)限晶通，然后刪除用戶，mysql5之后drop命令可以刪除用戶的同時刪除用戶的相關(guān)權(quán)限
更改密碼

mysql> set password for zx_root =password('xxxxxx');
mysql> update  mysql.user  set  password=password('xxxx')  where user='otheruser'

查看用戶權(quán)限
mysql> show grants for zx_root;
賦予權(quán)限
mysql> grant select on dmc_db.* to zx_root;
回收權(quán)限
mysql> revoke select on dmc_db.* from zx_root; //如果權(quán)限不存在會報錯
上面的命令也可使用多個權(quán)限同時賦予和回收哟玷，權(quán)限之間使用逗號分隔
mysql> grant select狮辽，update，delete 巢寡，insert on dmc_db.* to zx_root;
如果想立即看到結(jié)果使用
flush privileges ;
命令更新
設(shè)置權(quán)限時必須給出一下信息

• 要授予的權(quán)限
• 授予訪問權(quán)限的數(shù)據(jù)庫或表
• 用戶名

grant和revoke可以在幾個層次上控制訪問權(quán)限

• 整個服務(wù)器喉脖，使用 grant ALL 和revoke ALL
• 整個數(shù)據(jù)庫，使用on database.*
• 特點表抑月，使用on database.table
• 特定的列
• 特定的存儲過程

user表中host列的值的意義
% 匹配所有主機
localhost localhost不會被解析成IP地址树叽，直接通過UNIXsocket連接
127.0.0.1 會通過TCP/IP協(xié)議連接，并且只能在本機訪問爪幻；
::1 ::1就是兼容支持ipv6的菱皆，表示同ipv4的127.0.0.1

grant 普通數(shù)據(jù)用戶须误，查詢挨稿、插入、更新京痢、刪除 數(shù)據(jù)庫中所有表數(shù)據(jù)的權(quán)利
grant select, insert, update, delete on test.* to root@'%'
grant 數(shù)據(jù)庫開發(fā)人員奶甘，創(chuàng)建表、索引祭椰、視圖臭家、存儲過程、函數(shù)方淤。钉赁。。等權(quán)限携茂。
grant 創(chuàng)建你踩、修改、刪除 MySQL 數(shù)據(jù)表結(jié)構(gòu)權(quán)限。
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.%’;