資料庫系統導論筆記備份

1.直接照表格的打就出來了    要先登入學號的那個帳號

select now(), user(), database();

2.先創一個學號的資料庫 在轉到 學號的底下

create database 998G104;

use 998G104;

打出一個要求表格

create table std_info (

no int(3) not null extra auto_increment,

std_id varchar(15) not null,

name varchar(15) not null,

gender enum('M','F') not null ,

primary key (no)

);

3.把資料打入表格內部

insert into std_into values(1,'998j001','Mary Chen','F'),(2,'998j002','Joe Lin','M'),(3,'998j003','Jason Tang','M');

select no,std_id,name,gender from std_into;  在資料夾std_into只顯示no,std_id,name,gender的資料

4.在表格中加入place把他加入到 name底下

alter table std_into add place varchar(15) not null after name;

5.從檔案中把資料匯進檔案

mysql -u root -p 998G104<XXX.sql

6.匯入資料

load data local infile 'c:/XXX.txt' into table XXX.sql fields terminated by ',' ;

期末

1.

create database 998G020; 

grant all privileges on 998g020.* to '998g020'@'localhost' identified by '1234';

quit

mysql -u 998G020 -p

grant all on 998G020.* to '998G020'@'localhost' identified by '1234';

use 998g020

select now(), user(), database();

2.

create table price_table(

supplyid char(10) not null,

productid char(10) not null,

price int(4) not null,

primary key (supplyid,productid),

key productid (productid)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

create table supply_table(

supplyid char(10) not null,

supplier varchar(30) not null,

contact varchar(30) not null,

areaid char(10) not nll,

primary key (suplyid,areaid),

key 

cd c:\wamp\bin\mysql\mysql5.5.24\bin

mysql -u root -p test<mid.sql

mysql -u root -p test<std1.sql

mysql -u root -p test<test2.sql

mysql -u root -p

use test

load data local infile 'c:/data.txt' into table test fields terminated by ',' ;

delete from test where c_score = 0;

create database 998G020; //新增資料庫

drop database 998G020; //刪除資料庫

show databases; //顯示資料庫
grant all privileges on test.* to 'test'@'%' identified by '123'; //創帳號

grant select,insert,update on 998G020.* to 'test'@'%' identified by '123'; //修改權限
mysql -u user -p -h ip   //遠端登入

show grants for current_user();  //查看目前登入帳號之權限

 

10/19

create table name(
bid int(10) not null primary key,
title varchar(30) not null,
auther char(30) not null,
publish_slate datetime default '0000-00-00',
gender enum('F','M')
);

 

(quit)mysql -u root -p 資料表名稱<檔案名稱 //匯入資料表 

drop table name; //刪除資料表

show tables; //顯示資料表

use name //使用資料表

desc name; //資料表內容

 

10/27

alter table tbname add newfield datatype after ‘field’;  //新增欄位

alter table tbname change oldfield newfield datatype; //修改欄位

alter table tbname drop oldfield ; //整欄刪除

Alter table test change stdname new_s_name varchar(20) not null;

11/2

create table students(

sid int(10) not null,

name varchar(5) not null,

birthday date,

GPA int(2),

tel int(10),

primary key(sid));

create table Std_Address(

sid int(10) not null primary key,

address char(100));

insert into students 

values

(99800001,'Calvin Chen','1990-01-01',5,'02-1234-5678'),

(99800002,'Calvin Lin','1990-12-01',9,'03-1234-5678'),

(99800003,'Calvin Chang','1990-10-01',7,'04-1234-5678');

select * from students; //顯示資料表欄位內容

delete from students; //字面上意思

load data local infile '路徑' into table students fields terminated by ',';

/*

筆記本內容

99800004,Calvin Lee,1990-11-01,9,05-1234-5678

99800005,Calvin Wen,1993-11-01,2,06-1234-5678

*/

alter table Std_Address add foreign key (sid) references students (sid);

 //students為Std_Address父類別

 

11/23

 

create table t1(
code char(5),
title varchar(10),
pirce int(5)
);

insert into t1 values
('P657','C',490),
('P659','ASP',600),
('L333','PHP',580);

alter table t1 add place varchar(30); //新增欄位

update tbname set dataname='data' where dataname='data';  //修改欄位內容 where 條件

11/30

mysql -H -e "select * from test.std" -p > test.htm

mysql -X -e "select * from test.std" -p 

12/7

select * from inst where eid between 'e001' and 'e003'; //搜尋table(inst) eid e001~e003

select * from inst where eid not between 'e001' and 'e003';

select * from inst where eid in ('e001','e003');

create table new_table select * from old_table;

elect * from test union select * from testbak; //聯集

insert into testbak values('998g004','Jeremy','Lin','m','Yuan-Lin',56,48,52); //交集

select distinct test.* from test,testbak where test.std_id not in (select testbak.std_id from testbak); //差集test為主

select * , (c_score+e_score+m_score)/3 as average from testbak order by average desc; //計算c,e,m平均、排序

12/14

mysql> select gender, avg(c_score),avg(e_score),avg(m_score),count(*) from test group by gender='m';

+--------+--------------+--------------+--------------+----------+

| gender | avg(c_score) | avg(e_score) | avg(m_score) | count(*) |

+--------+--------------+--------------+--------------+----------+

| f      |      70.0000 |      64.0000 |      69.0000 |        2 |

| m      |      64.8000 |      75.4000 |      64.8000 |        5 |

+--------+--------------+--------------+--------------+----------+

mysql> select count(*),avg(c_score),avg(e_score),avg(m_score) from test where std_id in('998g001','998g002','998g004','998g007');

+----------+--------------+--------------+--------------+

| count(*) | avg(c_score) | avg(e_score) | avg(m_score) |

+----------+--------------+--------------+--------------+

|        4 |      61.5000 |      67.7500 |      68.2500 |

+----------+--------------+--------------+--------------+

mysql> select inst.*, std.* from std left outer join inst on inst.city=std.city;

+------+-----------------+----------+---------------------+------------+------+----------------+-----+----------+

| eid  | name            | city     | rank                | department | sid  | name           | gpa | city     |

+------+-----------------+----------+---------------------+------------+------+----------------+-----+----------+

| E001 | Lee,Ann         | Taipei   | Professor           | CS         | s001 | Chen,Huei-Ann  | 3.7 | Taipei   |

| E002 | Yang,Jing-Thurm | Tao-Yuan | Associate Professor | CIS        | s002 | Jang,Hsiao-Yu  |   3 | Tao-Yuan |

| E003 | Lee,Hong-Jhang  | Taichung | Assistant Professor | MATH       | s003 | Chang,San-Fung | 3.2 | Taichung |

| NULL | NULL            | NULL     | NULL                | NULL       | s004 | Lee,Se-Fang    | 2.9 | Tainan   |

+------+-----------------+----------+---------------------+------------+------+----------------+-----+----------+

mysql> select inst.*, std.* from std right outer join inst on inst.city=std.city;

+------+-----------------+----------+---------------------+------------+------+----------------+------+----------+

| eid  | name            | city     | rank                | department | sid  | name           | gpa  | city     |

+------+-----------------+----------+---------------------+------------+------+----------------+------+----------+

| E001 | Lee,Ann         | Taipei   | Professor           | CS         | s001 | Chen,Huei-Ann  |  3.7 | Taipei   |

| E002 | Yang,Jing-Thurm | Tao-Yuan | Associate Professor | CIS        | s002 | Jang,Hsiao-Yu  |    3 | Tao-Yuan |

| E003 | Lee,Hong-Jhang  | Taichung | Assistant Professor | MATH       | s003 | Chang,San-Fung |  3.2 | Taichung |

| E004 | Kobe Brian      | Kellung  | Lecturer            | PHYS       | NULL | NULL           | NULL | NULL     |

+------+-----------------+----------+---------------------+------------+------+----------------+------+----------+

 creat view viewname as select * test; //create view...

12/21

delimiter % //修改結束符號

create procedure pro1()

begin

select * from test where last_name = 'Lin';

end %

call pro1%

create procedure pro2(a varchar(15))

begin

select * from test where last_name =a;

end % 

select note_text,match (note_text) against('rabbit') as rank from testfull%

create procedure pro3(tmp varchar(20))

begin

select * from testfull where match(note_text) against(tmp);

end % 

call pro3('food')%

show procedure status;

drop procedure pro1;

 

102/1/4
create table test(std_id char(15) not null,
first_name varchar(15) not null,
last_name varchar(15) not null,
gender enum('m','f') not null,
place varchar(30) not null,
c_score tinyint(3) not null,
e_score tinyint(3) not null,
m_score tinyint(3) not null,
primary key (std_id),
fulltext(std_id,first_name,last_name,place))engine=MYISAM;

 select * from test where match (std_id,first_name,last_name,place) against ('Paul'); 索引Paul

要是忘了在表格裡打fulltext 可以在程式理打
alter table tbname ENGINE=MYISAM;
alter table tbname add fulltext(std_id,first_name,last_name,place);