目錄:
1.新建數據庫
2.新建數據表
3.查看表結構
4.增刪改查
建立一個數據庫students
建立一塊數據表class1
內容包括:
id 主鍵 自動編號 無符號位 SMALLINT類型 name VARCHAR(30)類型 非空 唯一值 school VARCHAR(30) 非空 默認值chengdu college
代碼如下:
1.建立數據庫
mysql> CREATE DATABASE students; Query OK, 1 row affected (0.07 sec)
2.進入數據庫
mysql> USE students; Database changed
3.新建表
mysql> CREATE TABLE class1 ( -> id SMALLINT UNSIGNED AUTO_INCREMENT , -> name VARCHAR(30) NOT NULL UNIQUE KEY , -> school VARCHAR(30) DEFAULT 'chengdu_collage' , -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.09 sec)
4.查看表結構
mysql> DESC class1; +--------+----------------------+------+-----+-----------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+-----------------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | UNI | NULL | | | school | varchar(30) | YES | | chengdu_collage | | +--------+----------------------+------+-----+-----------------+----------------+ 3 rows in set (0.00 sec)
1.插入(INSERT)
1.INSERT INTO tb_name [(col_name...)] {VALUES|VALUE}({expr | DEFAULT},....),(...)... 例子: mysql> INSERT INTO class1 (name) VALUES ('john'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO class1 VALUES (DEFAULT,'jobs','chengdu_agricultural_college'); Query OK, 1 row affected (0.01 sec) 2.INSERT INTO tb_name SET col_name = {expr | DEFAULT},... 例子: mysql> INSERT INTO class1 SET name='tom'; Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO class1 SET name='lues',school='chengdu_agricultural_college'; Query OK, 1 row affected (0.01 sec)
2.修改(UPDATE)
1.UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... 例子: mysql> UPDATE class1 -> SET name='lues2' WHERE name='lues'; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0
3.刪除(DELETE)
1.DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] 例子: mysql> DELETE FROM class1 WHERE name='lues2'; Query OK, 1 row affected (0.01 sec)
4.查詢(SELECT)
1.簡單查詢 mysql> SELECT * FROM class1; +----+------+------------------------------+ | id | name | school | +----+------+------------------------------+ | 1 | john | chengdu_collage | | 2 | jobs | chengdu_agricultural_college | | 3 | tom | chengdu_collage | +----+------+------------------------------+ 3 rows in set (0.00 sec) 2.簡單的條件查詢 mysql> SELECT * FROM class1 WHERE id > 1; +----+------+------------------------------+ | id | name | school | +----+------+------------------------------+ | 2 | jobs | chengdu_agricultural_college | | 3 | tom | chengdu_collage | +----+------+------------------------------+ 2 rows in set (0.00 sec) 3.簡單的分組查詢 mysql> SELECT * FROM class1 GROUP BY school DESC; +----+------+------------------------------+ | id | name | school | +----+------+------------------------------+ | 1 | john | chengdu_collage | | 2 | jobs | chengdu_agricultural_college | +----+------+------------------------------+ 2 rows in set (0.00 sec) 4.簡單的排序查詢 mysql> SELECT * FROM class1 ORDER BY id DESC; +----+------+------------------------------+ | id | name | school | +----+------+------------------------------+ | 3 | tom | chengdu_collage | | 2 | jobs | chengdu_agricultural_college | | 1 | john | chengdu_collage | +----+------+------------------------------+ 3 rows in set (0.00 sec)