你的位置:首页 > 数据库

[数据库]mysql 增删改查最基本用法小结


目录:

  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)