CREATE DATABASE `mybank`;/*創建表*/USE mybank;CREATE TABLE `bank`( `customerName` CHAR(10), #用戶名 `currentMoney` DECIMAL(10,2) #當前餘額);/*插入數據*/INSERT INTO ...
- /*創建資料庫*/
CREATE DATABASE `mybank`;
/*創建表*/
USE mybank;
CREATE TABLE `bank`(
`customerName` CHAR(10), #用戶名
`currentMoney` DECIMAL(10,2) #當前餘額
);
/*插入數據*/
INSERT INTO `bank` (`customerName`,`currentMoney`) VALUES ('張三',1000);
INSERT INTO `bank` (`customerName`,`currentMoney`) VALUES ('李四',1);
2. --轉賬測試:張三希望通過轉賬,直接匯錢給李四500元
UPDATE `bank` SET `currentMoney` =`currentMoney`-500
WHERE `customerName` = '張三';
UPDATE `bank` SET `currentMoney` =`currentMoney`+500
WHERE `customerName` = '李四';
3. 執行事務 並提交事務
USE mybank;
/*設置結果集以gbk編碼格式顯示*/
SET NAMES gbk;
/*開始事務*/
BEGIN;
UPDATE `bank` SET `currentMoney` =`currentMoney`-500
WHERE `customerName` = '張三';
UPDATE `bank` SET `currentMoney` =`currentMoney`+500
WHERE `customerName` = '李四';
COMMIT; #提交事務,寫入硬碟
4. 開始事務 回滾事務
BEGIN;
UPDATE `bank` SET `currentMoney` = `currentMoney` -1000 WHERE `customerName` = '張三';
/*回滾*/
ROLLBACK;
5.設置自動提交關閉或開啟
/*關閉事務自動提交*/
SET autocommit = 0;
/*轉賬*/
UPDATE `bank` SET `currentMoney` =`currentMoney`-500
WHERE `customerName` = '張三';
UPDATE `bank` SET `currentMoney` =`currentMoney`+500
WHERE `customerName` = '李四';
/*提交*/
COMMIT;
UPDATE `bank` SET `currentMoney` = `currentMoney` -1000 WHERE `customerName` = '張三';
/*回滾事務*/
ROLLBACK;
6. 創建視圖
/*當前資料庫*/
USE myschool;
DROP VIEW IF EXISTS `view_student_result`;
/*創建視圖*/
CREATE VIEW `view_student_result`
AS
/*需要創建視圖的數據*/
SELECT `studentName` AS 姓名,student.`studentNo` 學號,`studentResult` AS 成績,`subjectName` AS 課程名稱.`examDate` AS 考試日期
FROM `student`
INNER JOIN `result` ON `student`.`studentNo` = `result`.`studentNo`
INNER JOIN `subject` ON `result`.`subjectNo` = `subject`.`studentNo`
WHERE `subject`.`subjectNo`=(
SELECT `subjectNo` FROM `subject` WHERE `subjectName` = 'Logic Java'
)AND `examDate` =(
SELECT MAX(`examDate`) FROM `result`.`subject` WHERE `result`.`subjectNo` = `subject`.`subjectNo`
AND `subjectName` = 'Logic Java'
);
7. 創建索引
USE myschool;
/*創建索引*/
CREATE INDEX `index_student_studentName`
ON `student`(`studentName`);
8. 使用DOS命令備份資料庫
mysqldump -u username -h -ppassword
dbname[tbname1 [,tbname2]........] > filename.sql
例:
mysqldump -u root -p myschool student > d:\backup\myschool.sql
EnterPassword:
9. 使用後DOS命令恢複數據庫
1) .恢複數據庫前先在MySQL伺服器中創建新的資料庫.
2). 執行 mysql -u username -p [dbname] <filename.sql
例:
mysql -u root -p schoolDB < d:\backup\myschool.sql
10. 使用source命令恢複數據庫
語法: source filename;
例:
/*創建資料庫*/
CREATE DATABASE myschoolDB2;
USE myschoolDB2;
/*恢複數據庫*/
source 'd:\backup\myschool.sql';
11. 將表數據導出到文本文件
語法: select 列名 from 表名 [where 條件,條件2......]
into outfile 'filename' ;
例:
SELECT * FROM `result` WHERE `subjectNo` =
(SELECT `subjectNo` FROM `subject` WHERE `subjectName` = 'Logic Java')
INTO OUTFILE 'd: /backup/result.txt';
12. 將文本文件導入到數據表
語法: LOAD DATA INFILE 'filename' INTO TABLE 'tablename ';
1) . 先創建導入數據的表結構
USE myschoolDB2;
/*創建result表結構*/
CREATE TABLE `result`
(
`studentNo` INT(4) NOT NULL COMMENT '學號',
`subjectNo` INT(4) NOT NULL COMMENT '課程編號',
`examDate` DATETIME NOT NULL COMMENT '考試日期',
`studentResult` INT(4) NOT NULL COMMENT '考試成績',
PRIMARY KEY (`studentNo`,`subjectNo`,`examDate`)
)ENGINE = INNODB DEFAULT CHARSET utf8;
2).導入數據
LOAD DATA INFILE '路徑.txt' INTO TABLE result;
/*查看result表數據*/
SELECT * FROM result;
