[SQL] Stored Routines

Stored Routines (Stored procedures & Stored functions)
自定義一連串的sql語句與function.

Stored procedures

新增

mysql> delimiter $$
mysql> create procedure procedure_table ()
    -> begin
    -> select* from testtable2 where name = 'lewis';
    -> select* from testtable where car >110;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> ;
ERROR:
No query specified

使用

mysql> call procedure_table();
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
+----+-------+------+-------+-----+-------+
2 rows in set (0.01 sec)

刪除

mysql> drop procedure if exists procedure_table;
Query OK, 0 rows affected (0.00 sec)

mysql> call procedure_table();
ERROR 1305 (42000): PROCEDURE testdb.procedure_table does not exist

Stored functions

有參數

mysql> delimiter $$
mysql> create function sumarg(n1 int, n2 int)  
returns int                 
begin return n1 + n2 ; end$$
Query OK, 0 rows affected (0.00 sec)

mysql> select sumarg(1,3);
+-------------+
| sumarg(1,3) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

無參數

mysql> delimiter $$
mysql> create function datetest() returns varchar(24) 
begin                
declare d,t,w varchar(24); 
set d = date_format(curdate(),'%y/%m/%d'); 
set t = time_format(curtime(),'%y/%m/%d'); 
set w = dayname(curdate()); 
return concat(d,' ', t ,' ',w); end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> ;
ERROR:
No query specified

mysql> select datetest();
+--------------------------+
| datetest()               |
+--------------------------+
| 15/02/14 00/00/00 Saturd |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

[SQL] View

View 

如果在資料庫的應用中，出現很常執行的查詢敘述時， 你可以在MySQL資料庫中建立一種「View」元件，
View元件用來保存一段你指定的查詢敘述：
建立好需要的View元件以後，除了有一些限制外，
它使用起來就像是一個表格 也有很多人稱「View」元件是一種「虛擬表格」，
因為它不是一個真正儲存紀錄資料的表格，
可是它又跟表格的用法類似。
所以如果有需要的話，你也可以使用View元件回傳的紀錄資料，
執行統計、分組與其它需要的處理： 

 下列是MySQL關於View元件的規定與限制： 

在同一個資料庫中，View的名稱不可以重複，也不可以跟表格名稱一樣 View不可以跟Triggers建立聯結 儲存在View中的查詢敘述也有下列的規定： 查詢敘述中只能使用到已存在的表格或View 「FROM」子句中不可以使用子查詢 不可以使用「TEMPORARY」表格 不可以使用自行定義的變數、Procedure與Prepared statement參數 註：「TEMPORARY」表格在「表格與索引、建立表格、建立暫存表格」中討論。「Triggers」、定義變數、「Procedure」與「Prepared statement」在後面都會有章節詳細的討論。

參考來源

新增view

mysql> create view testtable2View as select* from testtable2 order by car;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from testtable2View ;
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  4 | jim   | sw   | 7     |   6 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
+----+-------+------+-------+-----+-------+
4 rows in set (0.00 sec)

修改view

mysql> alter view testtable2View as select* from testtable2 order by price;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from testtable2View ;
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
|  4 | jim   | sw   | 7     |   6 | NULL  |
+----+-------+------+-------+-----+-------+
4 rows in set (0.00 sec)

刪除view

mysql> drop view if exists testtable2View;
Query OK, 0 rows affected (0.00 sec)
mysql> select* from testtable2View ;
ERROR 1146 (42S02): Table 'testdb.testtable2View' doesn't exist

檢查view的狀態

check table testtable2View;
注意:不是: check view testtable2View;

select* from information_schema.views;

[SQL] subquery | 子查詢

select, where , from, 等語句都可以內嵌子查詢的語法.
括號裡面的回傳欄位只能是一個

mysql> select* from testtable2 where price  > (select price from testtable2 where name = 'leo' ) ;
+----+------+------+-------+-----+-------+
| id | name | job  | price | car | title |
+----+------+------+-------+-----+-------+
|  4 | jim  | sw   | 7     |   6 | NULL  |
+----+------+------+-------+-----+-------+
1 row in set (0.00 sec)

IN

mysql> select* from testtable2 where name in ('lewis','leo' ) ;                     
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
+----+-------+------+-------+-----+-------+
3 rows in set (0.00 sec)

[SQL] Alter table | 修改資料表

修改table 屬性:

mysql> alter table testtable2 CHARSET = utf8;
看修改後的結果:
mysql> show create table testtable2;

| testtable2 | CREATE TABLE `testtable2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(35) CHARACTER SET latin1 NOT NULL,
  `price` char(35) CHARACTER SET latin1 NOT NULL,
  `car` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

查看索引資訊:

mysql> show index from testtable2;

增加一個欄位

mysql> alter table testtable2 add job varchar(20) default 'sw'after name;
mysql> select* from testtable2;                             
+----+-------+------+-------+-----+
| id | name  | job  | price | car |
+----+-------+------+-------+-----+
|  1 | lewis | sw   | 2     |   2 |
|  2 | lewis | sw   | 3     | 111 |
|  3 | leo   | sw   | 4     | 123 |
|  4 | jim   | sw   | 7     |   6 |
+----+-------+------+-------+-----+
4 rows in set (0.00 sec)

增加多個欄位

mysql> alter table testtable2 add (job2 varchar(20) , year varchar(20) );
mysql> select* from testtable2;                             
+----+-------+------+-------+-----+------+------+
| id | name  | job  | price | car | job2 | year |
+----+-------+------+-------+-----+------+------+
|  1 | lewis | sw   | 2     |   2 | NULL | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL | NULL |
+----+-------+------+-------+-----+------+------+
4 rows in set (0.00 sec)

修改欄位: 
1.CHANGE 可以修改欄位的名稱與定義.
2.MODIFY 只能修改欄位的定義，不能修改欄位名稱.
 一般來說會覺得change涵蓋兩種功能所以我們用change就好，
MODIFY可以防止不小心改到欄位名的狀況.

CHANGE

mysql> alter table testtable2 change job2 title varchar(30);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;                             
+----+-------+------+-------+-----+-------+------+
| id | name  | job  | price | car | title | year |
+----+-------+------+-------+-----+-------+------+
|  1 | lewis | sw   | 2     |   2 | NULL  | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL  | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL  | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL  | NULL |
+----+-------+------+-------+-----+-------+------+
4 rows in set (0.00 sec)

MODIFY

mysql> alter table testtable2 modify year int(10) after title;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;                            
+----+-------+------+-------+-----+-------+------+
| id | name  | job  | price | car | title | year |
+----+-------+------+-------+-----+-------+------+
|  1 | lewis | sw   | 2     |   2 | NULL  | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL  | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL  | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL  | NULL |
+----+-------+------+-------+-----+-------+------+
4 rows in set (0.00 sec)

刪除其中一個欄位

mysql> alter table testtable2 drop year;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
|  4 | jim   | sw   | 7     |   6 | NULL  |
+----+-------+------+-------+-----+-------+
4 rows in set (0.01 sec)

[SQL] tool of sql

三種sql tool
1.cmd line

2. MySqlWorkbench

3.Aqua Data Studio

[SQL]在同一個Database內 ，查詢多個表格 || search multi table in one database

在同一個db內 查詢多個表格.

資料表內容如下:

inner join
語法(3種):但我偏好用第一種.
第一種

 
    mysql> select * from testtable, testtable2 where
    -> testtable.name = testtable2.name;

這種同上，只是這種會幫 表格再取一個別名(把她想成宣告一個變數出來使用)

mysql> select * from testtable a, testtable2 b where a.name = b.name; 

第二種

mysql> select * from testtable inner join testtable2 
on testtable.name = testtable2.name;

第三種

mysql> select * from testtable inner join testtable2 using(name);

有inner join 就會有outer join 但因為我比較少用所以不做介紹

union
其實就是把兩個select 的結果一起show出來:

mysql> select* from testtable where name = 'lewis' 
union select* from testtable2 where name = 'lewis';

[SQL] install mysql on ubuntu and start it. | create, insert , delete , update ,

sudo apt-get install mysql-server-5.5

/etc/mysql$ vim my.cnf

如果需要讓MySQL從遠端連進來的話，把 bind_address 這個參數從 127.0.0.1 改成主機的實體 IP 位址。

$ sudo service mysql start

sudo /etc/init.d/mysql start

進入mysql

 mysql -u root -p

change the password of root to "lewis"

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "lewis";

create database testdb；

create accoount: "lewis" , password:"lewis"

GRANT ALL PRIVILEGES ON testdb.* TO lewis@localhost IDENTIFIED BY "lewis" with grant option;

查看該使用者有無權限:

SHOW GRANTS FOR lewis@localhost;

use testdb;

create table testtable 
( id int not null auto_increment primary key, 
name char(35) not null, school char(35) not null, 
age int(11) not null);

發現用aqua一值連不到我vm的sql，最後發現是 權限問題，真是....

 sudo chmod -R 755 /var/lib/mysql/

這樣就可以了.



show出目前有的database

 SHOW DATABASES;

使用哪個db

  use testdb;

show出這個db裡面有哪些table

   show tables;

show出這個table裡面的內容 (select)

 select * from testtable;

新增一筆資料到table

 insert into testtable (name,school,age) values('jim','acer','35');

insert into testtable (name,school) values('jim','acer');

更新一筆資料到 table

 update testtable set age = '30' where name = 'leo'; 

刪除資料

 delete from testtable where age > '1111';

刪除table中所有資料

 truncate from testtable ;

名詞解釋:
索引:

primary key:一個表格只能有一個。
在一個表格中，設定為主索引鍵的欄位值不可以重複，且不可以儲存「NULL」值。
適合使用在類似編碼、代號或身份證字號這類欄位。

unique index(不可重複索引):表格中，設定為唯一索引的欄位值不可以重複，但是可以儲存「NULL」值。這種索引適合用在類似員工資料表格中儲存電子郵件帳號的欄位，因為員工不一定有電子郵件帳號，所以允許儲存「NULL」值，每一個員工的電子郵件帳號都不可以重複。

non-unique index:只是用來增加查詢與維護資料效率的索引。設定為非唯一索引的欄位值可以重複，也可以儲存「NULL」值。


ref:
http://www.codedata.com.tw/database/mysql-tutorial-getting-started
http://www.1keydata.com/sql/alter-table-drop-constraint.html

續:lampp 

ref:http://www.rackspace.com/knowledge_center/article/installing-mysql-server-on-ubuntu
補充: 這種安裝法會在/etc/init.d, 會註冊到service.
開機順序在etc/rc, s=start,k = stop,
mount point 要先起來，如果有其他要用到.
系統有需要的都要close/open.