顯示具有 資料庫 標籤的文章。 顯示所有文章
顯示具有 資料庫 標籤的文章。 顯示所有文章

2017年9月8日 星期五

建立修改MySQL資料表

命令列建立資料庫表格需要三項要素:
一 表格的名字
二 欄位名稱
三 欄位的類型

語法:
CREATE TABLE 表格的名字 (欄位名稱 欄位的類型);

以下是一個建立表格的範例:
Create Table ExTable (
ExId INT NOT NULL AUTO_INCREMENT,
ExName CHAR(20) NOT NULL,
ExTitle VARCHAR(100) ,
ExDate DATE,
PRIMARY KEY (ExId)
);

Create Table ExTable (
ExId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
ExName CHAR(20) NOT NULL,
ExTitle VARCHAR(100) ,
ExDate DATE,
);


ALTER TABLE 表格的名字 CHANGE COLUMN 原來欄位名稱 新欄位的名稱  新欄位的類型;
以下是一個修改表格的範例:
Alter table ExTable Change column ExName NewName char(20) not null;


ALTER TABLE 表格的名字 ADD COLUMN 欄位名稱 欄位的類型;
以下是一個新增表格的範例:
Alter table ExTable ADD column ExAddress varchar(100) not null;


ALTER TABLE 表格的名字 DROP COLUMN 欄位名稱 ;
以下是一個刪除表格欄位的範例:
Alter table ExTable DROP column ExAddress;



alter table ExTable modify column ExId  int, drop primary key;
alter table ExTable modify column ExId int  primary key;
alter table ExTable modify column ExId int auto_increment;

alter table ExTable add primary key(ExId);


所謂NULL值並不是表示數字的零或是字串中的空字串,而是代表未知值或是未適合值。NULL大多在欄位輸入時,尚未獲知正確值的情況下使用。
例如,建立通訊錄表格時,並不知道某人的傳真號碼或沒有傳真號碼,用NULL表示。
如果欄位屬性為NOT NULL(不可以未知)時,使用者在建立一筆資料時,若欄位為空白MySQL會顯示錯誤。

2016年11月7日 星期一

‍資料庫的使用者與權限

檢視使用者
select user,host from mysql.user;

新增使用者
create user 'username'@'host' identified by 'password';
#host can be: localhost, 192.168.1.%, %

修改使用者密碼:
set password for 'user'@'host' = password('password');

 刪除使用者
1. drop user 'username'@'host';
2. delete from mysql.user where user='user' and host='host';

檢視grant權限
show grants for 'username'@'host';

grant權限
概念:
1. 使用指令的權限,使用者對所有資料庫使用指令的權限。
grant select on *.* to 'user'@'host';
資料指令權限有select,insert,update,delete,file
結構指令權限create,alter,index,drop,create temporary tables,show view,create routine,alter routine,excute,create view,event,trigger
系統管理指令權限 grant,super,process,reload,shut down,show database,lock tables,create user
2. 對資料庫的權限,可以專為單一資料庫開放所有權限,但其他資料庫只有select的權限。
ex, grant privileges on *.* to 'username'@'host';
(privileges: all privileges, select,insert,update.........)
(*.* : database.*)
ex, grant all privileges on database.* to 'user'@'host';

給與grant權限 
grant privileges on *.* to 'username'@'host' with grant option; 

取消權限
revoke all privileges on *.* from 'username'@'host';
revoke grant option on *.* from 'username'@'host';

2016年9月16日 星期五

mysql to mariaDB(before version 10)

1. 安裝 MariaDB。你的MySQL所有工具,連接程式都可以如常運作。你也不需要匯出和匯入資料。

格式與檔案名稱都是相同的。

2. 所有客戶端的API、協議和結構都是相同的;

3. 所有文件件​​名、二進製文件、路徑、端口、套接字等……全都是一樣的;

4. 所有的MySQL與其他語言(PHP、Perl、Python、Java、.NET、MyODBC、Rub、MySQL C..)

的連接文件無需任何改動,在MariaDB就可工作;


MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能輕鬆成為MySQL的代替品。

在存儲引擎方面,10.0.9版起使用XtraDB(名稱代號為Aria)來代替MySQL的InnoDB。


版本方面,MariaDB直到5.5版本,均依照MySQL的版本。

因此,使用MariaDB5.5的人會從MySQL 5.5中了解到MariaDB的所有功能。

從2012年11月12日起發布的10.0.0版開始,不再依照MySQL的版號。

10.0.x版以5.5版為基礎,加上移植自MySQL 5.6版的功能和自行開發的新功能。


相對於最新的MySQL5.6,MariaDB在性能、功能、管理、NoSQL擴展方面包含了更豐富的特性。

比如微秒的支持、線程池、子查詢優化、組提交、進度報告等。

MariaDB 權限管理

概念:
1. 使用指令的權限,使用者對所有資料庫使用指令的權限。
grant select on *.* to 'user'@'host';

資料指令權限有select,insert,update,delete,file
結構指令權限create,alter,index,drop,create temporary tables,show view,create routine,alter routine,excute,create view,event,trigger
系統管理指令權限 grant,super,process,reload,shut down,show database,lock tables,create user

2. 對資料庫的權限,可以專為單一資料庫開放所有權限,但其他資料庫只有select的權限。
grant all privileges on database.* to 'user'@'host';


建立使用者:
create user 'user'@'host' identified by 'password';
顯示使用者:
select user,host from mysql.user;
顯示使用者權限:
show grants for 'user@'host';

修改使用者密碼:
set password for 'user'@'host' = password('password');

刪除使用者:
delete from mysql.user where user='user' and host='host';










2015年7月19日 星期日

資料庫正規化

正規化:
A.欄位唯一性 (Field Uniqueness)
B.主關鍵欄位 (Primary Key)
C.功能關聯性 (Function Dependence)
D.欄位獨立性 (Field Independence)

意思就是:
當成鍵值的欄位不可以重複,
資料重複出現的欄位分解到另一個table,
欄位的關聯要明確!!!

MySQL 的特性

為多種編程語言提供了API。這些編程語言包括C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等

可以處理擁有上千萬條記錄的大型數據庫 。我們正在對某些包含50,000,000 個記錄的資料庫使用MySQL

多種列類型:1、 2、 3、4、和8 字節長度的有符號/無符號整數、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、BLOB、DATE、TIME、DATETIME、 TIMESTAMP、YEAR、SET和ENUM類型

全面支持SQL的GROUP BY和ORDER BY子句,支持聚合函數( COUNT()、COUNT(DISTINCT)、AVG()、STD()、SUM()、 MAX()和MIN() )

支持ANSI SQL的LEFT OUTER JOIN和ODBC語法

每個表允許有16個索引。每個索引可以由1~16個列或列的一部分組成。最大索引長度是256 個字節(在編譯MySQL時,它可以改變)。一個索引可以使用一個CHAR或VARCHAR字段的前綴

你可以在同一查詢中混用來自不同資料庫的表

用C和C++編寫,並用大量不同的編譯器測試

優化的SQL查詢算法,有效地提高查詢速度

使用核心線程的完全多線程。這意味著它能很容易地利用多CPU(如果有)

可運行在不同的平臺上。支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多種操作系統

包括myisamchk,一個檢查、優化和修復數據庫表的快速實用程序

既能夠作為一個單獨的應用程序應用在客戶端服務器網絡環境中,也能夠作為一個庫而嵌入到其他的軟件中提供多語言支持,常見的編碼如中文的GB 2312、BIG5,日文的Shift_JIS等都可以用作數據表名和數據列名

提供TCP/IP、ODBC和JDBC等多種數據庫連接途徑

MySQL 常用的指令


Login MySQL:  Two ways to login mysql
mysql --host=host_name --user=user_name --password=your_password db_name

mysql [-h hostname] [-u username] [-pyourpassword]

ex: mysql -h 192.168.1.1 -u root -proot , notice; -p and password without space

for safety reason, leave -p with blank and system will prompt you enter password.



MySQL command line:
Note that all text commands must be first on line and end with ';'
?                (\?) Synonym for `help'.
clear          (\c) Clear command.
connect     (\r) Reconnect to the server. Optional arguments are db and host.
exit            (\q) Exit mysql. Same as quit.
help          (\h) Display this help.
print           (\p) Print current command.
quit            (\q) Quit mysql.
status        (\s) Get status information from the server.
system      (\!) Execute a system shell command.
tee            (\T) Set outfile [to_outfile]. Append everything into given outfile.
use            (\u) Use another database. Takes database name as argument.

Databases:
show current database:  select database();

create database [database_name];

drop database [database_name];

show databases;

use [database_name];

Tables:
Create Table

Alter Table

show tables;

desc [table_name];

drop table  [table_name];

Others;
show engines; 

show status;

show processlist; 

SHOW INDEX FROM tablename; 顯示表中的索引信息。

list mysql user: 
select user,host from mysql.user;
show grants for 'root'@'host';





MySQL BACKUP:
mysqldump
mysqldump --tab=/path/to/some/dir --opt --full

SELECT INTO OUTFILE
SELECT * FROM [table_name]  into outfile 'filename.csv'





2014年4月2日 星期三

在資料庫中找出重複的資料

select 中 group 是把資料相同的欄位群組起來,也意味著那個欄位相同的資料就不會重複出現。

用法: select * from table group by duplicateColumn

把使用過或群組過得資料找出來,就等於找出重複的資料。所以要使用 HAVING count(*) > 1 (資源被使用次數大於 1 )

用法: select * from table group by duplicateColumn HAVING count(*) > 1

但列出的只是那個欄位重複的資料,並無法把所有重複的資料列出。意思是重複的資料已被群組,所以只有一筆。

這時要列出所有重複必須在加上inner join方法

select table.duplicateColumn
from table
inner join (select duplicateColumn from table group by duplicateColumn HAVING count(*) > 1) DUP
on table.duplicateColumn = DUP.duplicateColumn

此時就會列出所有相同的資料。

但想找的是重複又彼此接續的料又如何?
要使用別名成兩個table在比較上下列
select first.*
from table as(可省略) first
left join table next
on first.id = next.id + 1
where first.duplicateColumn = next.duplicateColumn

但問題是第一筆,也就是剛被重複的那筆不會顯示。所以要反向也比
select first.*
from table as(可省略) first
left join table next
on first.id = next.id + 1 and first.id = next.id - 1
where first.duplicateColumn = next.duplicateColumn

此時多出先幾筆資料,沒關係group起來即可。
select first.*
from table as(可省略) first
left join table next
on first.id = next.id + 1 and first.id = next.id - 1
where first.duplicateColumn = next.duplicateColumn group by id

SQL JOIN 概念(sql join concepts)

定義Definition:

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
INNER JOIN: Returns all rows when there is at least one match in BOTH tables


以下例子examples足以說明用法:
table customer
    id   name
   001   Jack
   002   John
   003   Tom


table shopping
  buy         name
 apple        jack
 orange      jack

 grape        John
 banana     John

 pear           Mary
 strawberry   Mary


SELECT * FROM customer LEFT JOIN shopping ON customer.name = shopping.name
    id    name     name       buy
   001    Jack     Jack      apple
   001    Jack     Jack      orange
   002    John     John      grape
   002    John     John      banana
   003    Tom      null       null
在這裡 LEFT JOIN 的用法比較像,顧客全部有誰,他們買什麼。沒買的人也要知道。

SELECT * FROM customer RIGHT JOIN shopping ON customer.name = shopping.name
    id    name     name       buy
   001    Jack     Jack      apple
   001    Jack     Jack      orange
   002    John     John      grape
   002    John     John      banana
   null     null      Mary      pear
   null     null      Mary      strawberry
在這裡 RIGHT JOIN 的用法比較像,全部賣了什麼,誰買。沒買的人不重要。

SELECT * FROM customer INNER JOIN shopping ON customer.name = shopping.name
    id    name     name       buy
   001    Jack     Jack      apple
   001    Jack     Jack      orange
   002    John     John      grape
   002    John     John      banana
在這裡 INNER JOIN 的用法比較像,什麼人,買了什麼。全部有誰,全部賣什麼不重要。