程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql 增刪改查最基本用法小結,mysql增刪用法小結

mysql 增刪改查最基本用法小結,mysql增刪用法小結

編輯:MySQL綜合教程

mysql 增刪改查最基本用法小結,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)

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved