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);
