Mysql學習筆記2--使用入門

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use kakaxi;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| kakaxi     |
+------------+
1 row in set (0.00 sec)

---刪除資料庫
mysql> drop database kakaxi;
Query OK, 0 rows affected (0.56 sec)

---使用use dbname 選擇要連線的資料庫
mysql> use mysql;
Database changed

---檢視資料庫中存在的表
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.04 sec)

---建立表，由於mysql 的表名是以目錄的形式存在磁碟上的，所以表名要符合任何目錄允許的字元
mysql> create table delp(deptno int(4),deptname varchar(50));
Query OK, 0 rows affected (0.74 sec)

---檢視錶結構
mysql> desc delp
    -> ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno   | int(4)      | YES  |     | NULL    |       |
| deptname | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.11 sec)
mysql> create table employee(eid int(4),ename varchar(50),birthday date,deptno i
nt (4));
Query OK, 0 rows affected (0.38 sec)

---檢視構造表的ddl語句

mysql> show create table employee \G;
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `eid` int(4) DEFAULT NULL,
  `ename` varchar(50) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `deptno` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

ERROR:    （這裡有個ERROR,暫時還不明白是什麼意思）
No query specified

---alter table的使用方法

mysql> alter table employee rename emp;
Query OK, 0 rows affected (0.22 sec)

mysql> desc emp
    -> ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid      | int(4)      | YES  |     | NULL    |       |
| ename    | varchar(50) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| deptno   | int(4)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

---修改表中列名
alter table tabname change column old_col_name new_col_name col_definition;
---將birthday 改為birthdate

mysql> alter table emp change birthday birdate date;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| eid     | int(4)      | YES  |     | NULL    |       |
| ename   | varchar(50) | YES  |     | NULL    |       |
| birdate | date        | YES  |     | NULL    |       |
| deptno  | int(4)      | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

---修改表中欄位的型別
mysql> alter table emp modify eid int(5);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
alter table tabname modify [column] column_definition [FIRST|AFTER col_name]
mysql> desc emp;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| eid     | int(5)      | YES  |     | NULL    |       |
| ename   | varchar(50) | YES  |     | NULL    |       |
| birdate | date        | YES  |     | NULL    |       |
| deptno  | int(4)      | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
比較一下change 和modify 的區別：
1. change 和modify 都可以修改表的定義，不同的是change 後面需要寫兩次列名，不方便。
2. change的優點是可以修改表中的列名稱，而modify則不能。