Mysql 基礎操作 DDL DML DCL
mysql 基礎操作 包括DDL DML DCL示例
c:\Program Files\MySQL\MySQL Server 5.1>cd bin
c:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root -pmysql --登陸
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.62-community MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; --顯示所有資料庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| yws |
+--------------------+
4 rows in set (0.07 sec)
mysql> use yws
Database changed
mysql> show tables \g
+---------------+
| Tables_in_yws |
+---------------+
| userinfo |
| zzm |
+---------------+
2 rows in set (0.04 sec)
mysql> use mysql --選擇某個資料庫
Database changed
mysql> show tables \g 檢視本資料庫下所有表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.29 sec)
mysql>
mysql> use yws \g
Query OK, 0 rows affected (0.00 sec)
mysql> create table emp(ename varchar(10) ,hiredate date,sal decimal(10,2),deptno int(2)); --建立表
Query OK, 0 rows affected (0.16 sec)
mysql> desc emp; --描述表結構
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> show create table emp \G; --檢視建立表SQL 利用\G選項更加美觀
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table emp modify ename varchar(20); --修改欄位型別
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp add column age int(3); --新增新列
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp drop column age; --刪除列
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp add column age int(4);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp change age age1 int(4); --修改列名稱和列型別
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
note:change和modify都可以修改，不同的是 change需要寫兩次列名稱，但是可以修改列的名稱，modify只可以修改型別。
mysql> alter table emp add birth date after ename; --修改列欄位順序
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
note：mysql獨特的功能，可以調整表欄位順序。
mysql>
mysql> alter table emp modify age1 int(3) first;--修改列欄位順序
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age1 | int(3) | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql>
mysql> alter table emp rename y_emp; --修改表名稱
Query OK, 0 rows affected (0.06 sec)
mysql> alter table y_emp modify age1 int(3) after deptno;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc y_emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age1 | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table y_emp change age1 age int(4);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc y_emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table y_emp drop column birth;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table y_emp drop column age;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> insert into y_emp values ('yws','2015-03-16',5000,2); --插入資料
Query OK, 1 row affected (0.07 sec)
mysql> insert into y_emp values ('yws','2015-03-16',5000,2),('xy','2015-03-02',3000,2); --可以插入多列資料
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select count(*) from y_emp;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
limit的簡單用法
limit （start,start_offerset）
mysql>
mysql> select * from y_emp limit 1;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
+-------+------------+---------+--------+
1 row in set (0.00 sec)
mysql> select * from y_emp limit 1,2;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
| xy | 2015-03-02 | 3000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
mysql> select * from y_emp limit 3;;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
| yws | 2015-03-16 | 5000.00 | 2 |
| xy | 2015-03-02 | 3000.00 | 2 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> select * from y_emp order by sal limit 2; --配合order by 實現排序和分頁
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| xy | 2015-03-02 | 3000.00 | 2 |
| yws | 2015-03-16 | 5000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.03 sec)
mysql> select * from y_emp order by sal desc limit 2;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
| yws | 2015-03-16 | 5000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
mysql>
mysql> create table dept (deptno int,ename varchar(20));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into dept select deptno,ename from y_emp; --複製記錄
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
union all和 union 寫法
mysql> select deptno from y_emp
-> union all
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
+--------+
6 rows in set (0.02 sec)
mysql> select deptno from y_emp
-> union
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
DCL授權
mysql>
mysql> grant select,insert on yws.* to 'scott'@'localhost' identified by '123'; --建立使用者並授權
Query OK, 0 rows affected (0.04 sec)
mysql幫助文件可透過 help ?command方式
?show
mysql> ? data types;
You asked for help about help category: "Data Types"
For more information, type 'help- ', where
- is one of the following
topics:
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE
c:\Program Files\MySQL\MySQL Server 5.1>cd bin
c:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root -pmysql --登陸
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.62-community MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; --顯示所有資料庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| yws |
+--------------------+
4 rows in set (0.07 sec)
mysql> use yws
Database changed
mysql> show tables \g
+---------------+
| Tables_in_yws |
+---------------+
| userinfo |
| zzm |
+---------------+
2 rows in set (0.04 sec)
mysql> use mysql --選擇某個資料庫
Database changed
mysql> show tables \g 檢視本資料庫下所有表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.29 sec)
mysql>
mysql> use yws \g
Query OK, 0 rows affected (0.00 sec)
mysql> create table emp(ename varchar(10) ,hiredate date,sal decimal(10,2),deptno int(2)); --建立表
Query OK, 0 rows affected (0.16 sec)
mysql> desc emp; --描述表結構
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> show create table emp \G; --檢視建立表SQL 利用\G選項更加美觀
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table emp modify ename varchar(20); --修改欄位型別
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp add column age int(3); --新增新列
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp drop column age; --刪除列
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp add column age int(4);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table emp change age age1 int(4); --修改列名稱和列型別
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
note:change和modify都可以修改，不同的是 change需要寫兩次列名稱，但是可以修改列的名稱，modify只可以修改型別。
mysql> alter table emp add birth date after ename; --修改列欄位順序
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
note：mysql獨特的功能，可以調整表欄位順序。
mysql>
mysql> alter table emp modify age1 int(3) first;--修改列欄位順序
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age1 | int(3) | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql>
mysql> alter table emp rename y_emp; --修改表名稱
Query OK, 0 rows affected (0.06 sec)
mysql> alter table y_emp modify age1 int(3) after deptno;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc y_emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age1 | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table y_emp change age1 age int(4);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc y_emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table y_emp drop column birth;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table y_emp drop column age;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> insert into y_emp values ('yws','2015-03-16',5000,2); --插入資料
Query OK, 1 row affected (0.07 sec)
mysql> insert into y_emp values ('yws','2015-03-16',5000,2),('xy','2015-03-02',3000,2); --可以插入多列資料
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select count(*) from y_emp;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
limit的簡單用法
limit （start,start_offerset）
mysql>
mysql> select * from y_emp limit 1;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
+-------+------------+---------+--------+
1 row in set (0.00 sec)
mysql> select * from y_emp limit 1,2;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
| xy | 2015-03-02 | 3000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
mysql> select * from y_emp limit 3;;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
| yws | 2015-03-16 | 5000.00 | 2 |
| xy | 2015-03-02 | 3000.00 | 2 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> select * from y_emp order by sal limit 2; --配合order by 實現排序和分頁
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| xy | 2015-03-02 | 3000.00 | 2 |
| yws | 2015-03-16 | 5000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.03 sec)
mysql> select * from y_emp order by sal desc limit 2;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| yws | 2015-03-16 | 5000.00 | 2 |
| yws | 2015-03-16 | 5000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
mysql>
mysql> create table dept (deptno int,ename varchar(20));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into dept select deptno,ename from y_emp; --複製記錄
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
union all和 union 寫法
mysql> select deptno from y_emp
-> union all
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
+--------+
6 rows in set (0.02 sec)
mysql> select deptno from y_emp
-> union
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
DCL授權
mysql>
mysql> grant select,insert on yws.* to 'scott'@'localhost' identified by '123'; --建立使用者並授權
Query OK, 0 rows affected (0.04 sec)
mysql幫助文件可透過 help ?command方式
?show
mysql> ? data types;
You asked for help about help category: "Data Types"
For more information, type 'help
topics:
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29477587/viewspace-1457876/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle DDL,DML,DCL,TCL 基礎概念Oracle
- MySQL--基礎知識點--DDL/DCL/DML/DPL/DQL/CCLMySql
- DDL、DML、DCL、DQL相關操作
- DML、DDL、DCL區別
- DDL,DML,DCL區別
- 資料庫：淺談DML、DDL、DCL的區別資料庫
- DDL,DML,DCL,TCL四種語言的簡介
- DML操作 DDL觸發器觸發器
- 【開發篇sql】 基礎概述（三） DDL和DMLSQL
- MySQL基礎之DML語句MySql
- Oracle DBLINK 抽數以及DDL、DML操作Oracle
- 使用Logminer工具分析DML和DDL操作
- DML, DDL操作的自動提交問題
- 配置支援DML和DDL操作同步的GoldenGateGo
- DDL,DML操作對結果快取的影響快取
- Mysql資料庫學習（一）：資料庫基本概念、關係型資料庫、Mysql資料庫安裝配置、DDL/DCL/DML語句MySql資料庫
- go mysql 基礎操作 （CURD)GoMySql
- SQL學習___02：DDL+DCL語法SQL
- 04 MySQL 表的基本操作-DDLMySql
- MsSql資料庫使用SQL plus建立DDL和DML操作方法SQL資料庫
- MySQL — DCL語言MySql
- MySQL全面瓦解5：資料操作-DMLMySql
- MsSql 資料庫使用sqlplus建立DDL和DML操作方法SQL資料庫
- Oracle 19C OGG基礎運維-03DML操作同步Oracle運維3D
- Mysql資料庫基礎操作命令MySql資料庫
- MySQL基礎操作(增刪改查)MySql
- mysql 索引的基礎操作彙總MySql索引
- JDBC-MySql基礎操作詳解JDBCMySql
- 學習筆記【MySQL基礎操作-第一節：MySQL基本操作】筆記MySql
- Oracle 19C OGG基礎運維-05DDL操作同步Oracle運維
- oracle support nologging ddl dmlOracle
- performing DML/DDL operation over object in binORMObject
- Begin end程式碼段裡面有DDL和DML，如果DDL成功了而DML失敗了，則DDL的程式碼也會回滾
- oracle goldengate 配置DML&DDL實驗OracleGo
- SQL基礎——DML(插入、修改和刪除)SQL
- Linux下MySQL基礎及操作語法LinuxMySql
- 【Flashback】回收站中被標記刪除的表不可以DML和DDL操作
- 【學習】SQL基礎-013-DDLSQL