目录：
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) |
