linux 中資料庫的常用操作

標籤：表資料   建立   其他   root   localhost   pass   neon   nec   整理   

1-串連資料庫：

 mysql -h localhost -u jiangbiao -p

xxxxx@xxx:~$ mysql -h localhost -u jiangbiao -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2444832Server version: 5.5.40-log Source distribution

2-顯示表的資訊，包括所用引擎，對於mysql，只有InnoDB引擎支援觸發器操作

SHOW CREATE TABLE tbl_buniness;

3-顯示資料庫 : 

show databases;

4-使用資料庫：

use databasename;

5-顯示資料表：

show tables;

6-顯示具體的表結構 :

describe tablename;

7-插入操作：

INSERT INTO `root_trigger` (`id`,`data`)  VALUES (‘1‘, ‘test root line 1‘),         (‘2‘, ‘test root line 2‘),         (‘3‘, ‘test root line 3‘);  

8 - 通過DELETE CASCADE外鍵約束的處理機制來處理串聯刪除操作：

-- 建立測試主表. ID 是主鍵.CREATE TABLE test_main (  id      INT   NOT NULL,  value   VARCHAR(10),  PRIMARY KEY(id)  ); -- 建立測試子表. CREATE TABLE test_sub (  id      INT  NOT NULL,  main_id INT ,  value   VARCHAR(10),  PRIMARY KEY(id)  ); -- 插入測試主表資料.INSERT INTO test_main(id, value) VALUES (1, ‘ONE‘);INSERT INTO test_main(id, value) VALUES (2, ‘TWO‘); -- 插入測試子表資料.INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, ‘ONEONE‘);INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, ‘TWOTWO‘);-----通過  DELETE CASCADE  外鍵約束的處理機制來處理mysql> ALTER TABLE test_sub    ->   ADD CONSTRAINT main_id_cons    ->   FOREIGN KEY (main_id)    ->   REFERENCES  test_main(id)    ->   ON DELETE CASCADE//Query OK, 2 rows affected (0.16 sec)Records: 2  Duplicates: 0  Warnings: 0 mysql> DELETE FROM    ->   test_main    -> WHERE    ->   id = 1;    -> //Query OK, 1 row affected (0.02 sec) mysql> SELECT    ->   *    -> FROM    ->   test_sub;    -> //+----+---------+--------+| id | main_id | value  |+----+---------+--------+|  2 |       2 | TWOTWO |+----+---------+--------+1 row in set (0.00 sec)

9 - 如果你非要使用觸發器來處理， 那麼下面是一個例子：

-- 建立測試主表. ID 是主鍵.CREATE TABLE t_test_main (  id      INT   NOT NULL,  value   VARCHAR(10),  PRIMARY KEY(id)  ); -- 建立測試子表. CREATE TABLE t_test_sub (  id      INT  NOT NULL,  main_id INT ,  value   VARCHAR(10),  PRIMARY KEY(id)  ); -- 插入測試主表資料.INSERT INTO t_test_main(id, value) VALUES (1, ‘ONE‘);INSERT INTO t_test_main(id, value) VALUES (2, ‘TWO‘); -- 插入測試子表資料.INSERT INTO t_test_sub(id, main_id, value) VALUES (1, 1, ‘ONEONE‘);INSERT INTO t_test_sub(id, main_id, value) VALUES (2, 2, ‘TWOTWO‘); DELIMITER // CREATE TRIGGER tr_t_test_main_Del  BEFORE DELETE ON t_test_mainFOR EACH ROWBEGIN  DELETE FROM t_test_sub WHERE main_id = OLD.id;END;// DELIMITER ;mysql> delete from  t_test_main  where id = 1;Query OK, 1 row affected (0.01 sec) mysql> select * from t_test_sub;+----+---------+--------+| id | main_id | value  |+----+---------+--------+|  2 |       2 | TWOTWO |+----+---------+--------+1 row in set (0.00 sec)

關於8、9兩點的原文連結：https://zhidao.baidu.com/question/562372469.html

10 - 其他：
這裡有個已經整理的部落格連結

linux 中資料庫的常用操作