mysqlsql語句學習（一）

     為了學習數(shù)據(jù)庫，先熟悉一下sql語言，也就以MySQL為例子開始學習了！

目前成都創(chuàng)新互聯(lián)已為超過千家的企業(yè)提供了網(wǎng)站建設、域名、網(wǎng)站空間、網(wǎng)站托管運營、企業(yè)網(wǎng)站設計、十堰網(wǎng)站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

（參考書籍《深入淺出mysql 數(shù)據(jù)庫開發(fā)、優(yōu)化、管理維護》）

   下邊是執(zhí)行的sql語句，主要是建立表和修改表：

首先進入windows命令提示符下：

Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.02 sec)

mysql> use test;
Database changed
mysql> create table emp(ename varchar(20) not null primary key,hirdate date,sal
decimal(10,2),deptno int(2));
Query OK, 0 rows affected (0.08 sec)

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ename   | varchar(20)   | NO   | PRI | NULL    |       |
| hirdate | 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;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------------+
| Table | Create Table

                                                                 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `ename` varchar(20) NOT NULL,
  `hirdate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`ename`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table emp modify ename varchar(30);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ename   | varchar(30)   | NO   | PRI |         |       |
| hirdate | date          | YES  |     | NULL    |       |
| sal     | decimal(10,2) | YES  |     | NULL    |       |
| deptno  | int(2)        | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ename   | varchar(30)   | NO   | PRI |         |       |
| hirdate | date          | YES  |     | NULL    |       |
| sal     | decimal(10,2) | YES  |     | NULL    |       |
| deptno  | int(2)        | YES  |     | NULL    |       |
| age     | int(3)        | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp change age agenum int(4);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ename   | varchar(30)   | NO   | PRI |         |       |
| hirdate | date          | YES  |     | NULL    |       |
| sal     | decimal(10,2) | YES  |     | NULL    |       |
| deptno  | int(2)        | YES  |     | NULL    |       |
| agenum  | int(4)        | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp drop agenum;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ename   | varchar(30)   | NO   | PRI |         |       |
| hirdate | date          | YES  |     | NULL    |       |
| sal     | decimal(10,2) | YES  |     | NULL    |       |
| deptno  | int(2)        | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp add column birth date after ename;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ename   | varchar(30)   | NO   | PRI |         |       |
| birth   | date          | YES  |     | NULL    |       |
| hirdate | date          | YES  |     | NULL    |       |
| sal     | decimal(10,2) | YES  |     | NULL    |       |
| deptno  | int(2)        | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp modify sal first;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'first
' at line 1
mysql> alter table emp modify sal decimal(10,2) first;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| sal     | decimal(10,2) | YES  |     | NULL    |       |
| ename   | varchar(30)   | NO   | PRI |         |       |
| birth   | date          | YES  |     | NULL    |       |
| hirdate | date          | YES  |     | NULL    |       |
| deptno  | int(2)        | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp change ename name varchar(20) first;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| name    | varchar(20)   | NO   | PRI |         |       |
| sal     | decimal(10,2) | YES  |     | NULL    |       |
| birth   | date          | YES  |     | NULL    |       |
| hirdate | date          | YES  |     | NULL    |       |
| deptno  | int(2)        | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp change birth birthday date after hirdate;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | varchar(20)   | NO   | PRI |         |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hirdate  | date          | YES  |     | NULL    |       |
| birthday | date          | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

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

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| emptable       |
+----------------+
1 row in set (0.00 sec)

mysql> drop table emptable;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> exit;
Bye

C:\Documents and Settings\Administrator>

網(wǎng)頁標題：mysqlsql語句學習（一）
本文來源：http://muchs.cn/article32/ihdppc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供微信小程序、ChatGPT、網(wǎng)站設計公司、品牌網(wǎng)站建設、建站公司、做網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)