執行 cmd
> mysql -u root -p123456
mysql> exit
> mysql -u root -p
Enter password:
mysql>status
mysql>create database DB_學號;
mysql>show databases;
mysql>create USER UID_學號@localhost IDENTIFIED BY "密碼";
mysql>GRANT ALL ON DB_學號.* to UID_學號@localhost;
mysql>exit;
============使用與建立資料庫、資料表==============
>mysql -u UID_學號 -p
mysql>use DB_學號;
mysql>select database();
mysql> create Table Table_學號(name VARCHAR(20), birthday Date, mobilephone VARCHAR(20));
mysql> show tables;
mysql> drop table Table_學號 ;
mysql> create Table Table_學號(name VARCHAR(20), birthday Date, mobilephone VARCHAR(20)) CHARSET=utf8;
mysql> DESC Table_學號;
mysql> INSERT INTO Table_學號 VALUES('jdwang1', 1966/7/7, '0939111222');
mysql> INSERT INTO Table_學號 VALUES('jdwang2', '1966/7/7', '0939111222');
mysql> INSERT INTO Table_學號 (birthday, name, mobilephone) VALUES ('1966/7/7', 'jdwang3', '0939111222');
mysql> INSERT INTO Table_學號 (birthday, name, mobilephone) VALUES ('1966/7/7', 'jdwang3', '0939111222'), ('1966/7/7', 'jdwang4', '0939111222'), ('1966/7/7', 'jdwang5', '0939111222'), ('1966/7/7', 'jdwang6', '0939111222');
mysql> SELECT * FROM Table_學號;
============ 修改資料表============================================
mysql>ALTER TABLE Table_學號 MODIFY name VARCHAR(50);
mysql>DESC Table_學號;
mysql>ALTER TABLE Table_學號 ADD address VARCHAR(100);
mysql>DESC Table_學號;
mysql>ALTER TABLE Table_學號 CHANGE address ADDR VARCHAR(150);
mysql> DESC Table_學號;
mysql>ALTER TABLE Table_學號 ADD Temp VARCHAR(100);
mysql>DESC Table_學號;
mysql>ALTER TABLE Table_學號 DROP Temp;
mysql> CREATE TABLE Table_OLD SELECT * FROM Table_學號 ;
mysql> SELECT * from Table_OLD
mysql> ALTER TABLE Table_OLD MODIFY ADDR VARCHAR( 100) DEFAULT 'unknown';
mysql> SELECT * from Table_OLD;
mysql> INSERT INTO Table_OLD VALUES('jdwang9', 1966/7/7, '0939111222');
mysql> SELECT * from Table_OLD;
============ 主鍵============================================
mysql> create Table Table_學號_PmKey(name VARCHAR(20) PRIMARY KEY, birthday Date, mobilephone VARCHAR(20)) CHARSET=utf8;
mysql>create Table Table_jdwang_PmKey(name VARCHAR(20) PRIMARY KEY, birthday Date, mobilephone VARCHAR(20)) CHARSET=utf8;
mysql> DESC Table_學號_PmKey;
mysql> INSERT INTO Table_學號 VALUES('jdwang1', '1966/7/7', '0939111222');
mysql> SELECT * FROM Table_學號_PmKey;
mysql> INSERT INTO Table_學號 VALUES('jdwang1', '1966/7/7', '0939111222');
mysql> INSERT INTO Table_學號 (name) values('jdwang1');
mysql> INSERT INTO Table_學號 (name) values(NULL);
============ 索引 (Index)============================================
mysql> CREATE INDEX birthday_ind on Table_學號(birthday);
mysql>SHOW INDEX FROM Table_學號;
mysql>SHOW INDEX FROM Table_學號 \G
mysql>DROP INDEX birthday_ind ON Table_學號;
=============複製資料表、複製資料表欄位結構====================================
mysql>CREATE TABLE Table_OLD SELECT * FROM Table_學號 ;
=============匯入(import)======================
mysql> LOAD DATA INFILE 'D:/?.csv' INTO TABLE Table_jdwang FIELDS TERMINATED BY ',';
mysql> select * from Table_jdwang;
=============匯出(export)======================
mysql> SELECT * INTO OUTFILE 'D:/jdwang_out.csv' FIELDS TERMINATED BY ',' FROM Table_jdwang;
DOS> type 'D:/jdwang_out.csv'
=================文字執行SQL========================
mysql> SOURCE D:/sql_jdwang.txt
=================Record Ouput=================
mysql> tee log_jdwang.txt
mysql>select * from Table_jdwang;
mysql> notee;
==================資料庫備份、還原=========
>mysqldummp -u root -p123456 db_jdwang > db_jdwang_out.txt;
>mysqladmin -u root -p123456 CREATE db2
>mysql -u root -p123456 bd2 > db_jdwang_out.txt;