Deja Vu

有時候因為command line並不支援big5編碼
所以此時可以下指令
mysql> set names big5;
就可以以big5編碼方式看見中文

(在命令提示字元下)
dump 出 sql 檔:
mysqldump -u 使用者名稱 -p --databases 資料庫名稱 --tables 資料表名稱 > test.sql
sql 檔 dump 回去:

一、帳號與權限
設定 root 和其他 user 的密碼
mysqladmin -u root password '新密碼'
mysqladmin -u root -p
Enter password: 　　　此時再輸入密碼(建議採用)
use mysql;
mysql> UPDATE user SET password=password('新密碼') where user='root';　
　　只改 root 的密碼，如果沒有用 where ，則表示改全部 user 的密碼
mysql> FLUSH PRIVILEGES;　在 mysql
資料庫內，一定要用 flush 更新記憶體上的資料

Mysql>create database test;
//create the new database test
Mysql>use test;
//use the database test
Mysal>create table student
->(number int not null,
->name char(20) not null,
->grade int not null,
->primary key(number));
//create table
Mysql>show tables;
//show tables
Mysql>describe student
//show the type of table
Mysql>insert into student
->(number,name,grade)
->values(1,"SAM",90);
//key in the value
Mysql>grant all on * to 123@localhost identified by 'SAM';
//give the authority to the 123 user with identified 'SAM'
Mysql>revoke all on * from 123@localhost;
//remove the user 123
Mysql>drop database test;
//remove the database test
Mysql>create table student
->(number int not null,
->name char(20) not null,
->grade int not null,
->primary key(number)) TYPE=MyISAM
//set the primary key for number and set the table type with MyISAM
Mysql>alter table student change column number number int auto_increment;
//alter the data
Mysql>alter table student add column phone char(20) not null;
//add new data
Mysql>alter table student drop column grade;
//drop the old data
Mysql>create table depart_location
->(location char(30) not null,
->number int null,
->foreign key(number) references depart(number));
//set the connetion
/*
Primary key:限制欄位的值必須是唯一，並且不能沒有資料
Foreign key:限制欄位的值必須是來自於其所參考得資料表，使用null可以避免資料關聯不完全
unique:欄位值必須是唯一的，不允許在同一個資料表出現
*/
Mysql>create table work_on
->(essn char(9) not null,
->pno int not null,
->hours decimal(3,1) not null,
->primary key(essn,pno),
->foreign key(essn) references employee(ssn),
->foreign key(pno) references project(pnumber));
//set the multiple key
Mysql>create table work_on
->(essn char(9) not null,
->pno int not null,
->hours decimal(3,1) not null,
->primary key(essn,pno),
->constraint ssn_primary
->foreign key(essn) references employee(ssn),
->constraint pno_primary
->foreign key(pno) references project(pnumber));
//use constraint(強制) to name the 欄位 ;強制給欄位名稱
Mysql>select * from student where number=3
//where==condition
Mysql>select student.name,student.phone from student where number=3
//select the name & phone that who's number is 3
mysql>select data.number,data.name,data.experience as m from data where number>
0 group by number having m=89;
//將experience當作欄位m,並且m=89,且number必須大於0,以number為群組
/*group:會將相同資料合併*/
Mysql>select number,name,experience from data where name like "宋%" and experience>10
order by number desc;
// and用法 %代表萬用字元 表示剩下字串 like+子句
Mysql>select number,name,experience from data where name like "宋%" or "吳%";
//or 用法
Mysql>select data.name,data.experience,COUNT(*) from data where data.number>0 group by experience;
//count(*)取得群組數值 和group by配合使用
Mysql>select data.experience,avg(salary) from data where data.number>0 and data.number<5 group by experience;
//avg()算出該群組的平均值
Mysql>select data.experience,max(salary) from data where number>0 group by experience;
//max()算出群組中得最大值(最多要求多少) min()算出最小值(最少要求多少)
stddev()算出標準差(標準差大表示差異大)
sum()算出總值
/*
having 子句: 限制條件,通常跟在group by之後
order by 欄位:排序欄位
order by 欄位 desc:遞減排序
*/
Mysql>select data.name,work.name from data join work where (data.name==work.name) and
(data.number>0);
//將data中number大於0的name欄位資料加進work裡
Mysql>insert into student values(1,"TOM");
//insert the data to student
Mysql>insert into tutor (name,salary,location) from asir where number>10 and number<50;
//選取插入
Mysql>delete from tutor where number>10;
//delete the data
Mysql>update tutor set depart="k400" where number=3;
//update the data
Mysql>update tutor set salary=salary*3 where number in (1,3,5,7);
//update the set's data
///////conculsion/////////////
-select
-from
-where
-group by
-having
-order by
-insert into
-delete from
-update set
////////////////////////////////