Deja Vu

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
////////////////////////////////