美文网首页
第08讲 mysql添加数据

第08讲 mysql添加数据

作者: 有时有晌 | 来源:发表于2019-04-29 17:54 被阅读0次

    帮助命令的使用:

    mysql> ? create
    Many help items for your request exist.
    To make a more specific request, please type 'help <item>',
    where <item> is one of the following
    topics:
       CREATE DATABASE
       CREATE EVENT
       CREATE FUNCTION
       CREATE FUNCTION UDF
       CREATE INDEX
       CREATE LOGFILE GROUP
       CREATE PROCEDURE
       CREATE SERVER
       CREATE TABLE
       CREATE TABLESPACE
       CREATE TRIGGER
       CREATE USER
       CREATE VIEW
       SHOW
       SHOW CREATE DATABASE
       SHOW CREATE EVENT
       SHOW CREATE FUNCTION
       SHOW CREATE PROCEDURE
       SHOW CREATE TABLE
       SPATIAL
    

    还有一种方式

    ? create table
    执行后整个疯了 一大堆东西

    mysql> ? create table
    Name: 'CREATE TABLE'
    Description:
    Syntax:
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
        (create_definition,...)
        [table_options]
        [partition_options]
    
    Or:
    
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
        [(create_definition,...)]
        [table_options]
        [partition_options]
        select_statement
    
    Or:
    
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
        { LIKE old_tbl_name | (LIKE old_tbl_name) }
    
    create_definition:
        col_name column_definition
      | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
          [index_option] ...
      | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
          [index_option] ...
      | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
          [index_name] [index_type] (index_col_name,...)
          [index_option] ...
      | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
          [index_option] ...
      | [CONSTRAINT [symbol]] FOREIGN KEY
          [index_name] (index_col_name,...) reference_definition
      | CHECK (expr)
    
    column_definition:
        data_type [NOT NULL | NULL] [DEFAULT default_value]
          [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
          [COMMENT 'string']
          [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
          [STORAGE {DISK|MEMORY|DEFAULT}]
          [reference_definition]
    
    data_type:
        BIT[(length)]
      | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
      | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
      | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
      | INT[(length)] [UNSIGNED] [ZEROFILL]
      | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
      | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
      | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
      | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
      | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
      | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
      | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
      | DATE
      | TIME
      | TIMESTAMP
      | DATETIME
      | YEAR
      | CHAR[(length)]
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | VARCHAR(length)
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | BINARY[(length)]
      | VARBINARY(length)
      | TINYBLOB
      | BLOB
      | MEDIUMBLOB
      | LONGBLOB
      | TINYTEXT [BINARY]
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | TEXT [BINARY]
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | MEDIUMTEXT [BINARY]
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | LONGTEXT [BINARY]
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | ENUM(value1,value2,value3,...)
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | SET(value1,value2,value3,...)
          [CHARACTER SET charset_name] [COLLATE collation_name]
      | spatial_type
    
    index_col_name:
        col_name [(length)] [ASC | DESC]
    
    index_type:
        USING {BTREE | HASH}
    
    index_option:
        KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'
    
    reference_definition:
        REFERENCES tbl_name (index_col_name,...)
          [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
          [ON DELETE reference_option]
          [ON UPDATE reference_option]
    
    reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION
    
    table_options:
        table_option [[,] table_option] ...
    
    table_option:
        ENGINE [=] engine_name
      | AUTO_INCREMENT [=] value
      | AVG_ROW_LENGTH [=] value
      | [DEFAULT] CHARACTER SET [=] charset_name
      | CHECKSUM [=] {0 | 1}
      | [DEFAULT] COLLATE [=] collation_name
      | COMMENT [=] 'string'
      | CONNECTION [=] 'connect_string'
      | DATA DIRECTORY [=] 'absolute path to directory'
      | DELAY_KEY_WRITE [=] {0 | 1}
      | INDEX DIRECTORY [=] 'absolute path to directory'
      | INSERT_METHOD [=] { NO | FIRST | LAST }
      | KEY_BLOCK_SIZE [=] value
      | MAX_ROWS [=] value
      | MIN_ROWS [=] value
      | PACK_KEYS [=] {0 | 1 | DEFAULT}
      | PASSWORD [=] 'string'
      | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
      | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
      | UNION [=] (tbl_name[,tbl_name]...)
    
    partition_options:
        PARTITION BY
            { [LINEAR] HASH(expr)
            | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
            | RANGE{(expr) | COLUMNS(column_list)}
            | LIST{(expr) | COLUMNS(column_list)} }
        [PARTITIONS num]
        [SUBPARTITION BY
            { [LINEAR] HASH(expr)
            | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
          [SUBPARTITIONS num]
        ]
        [(partition_definition [, partition_definition] ...)]
    
    partition_definition:
        PARTITION partition_name
            [VALUES
                {LESS THAN {(expr | value_list) | MAXVALUE}
                |
                IN (value_list)}]
            [[STORAGE] ENGINE [=] engine_name]
            [COMMENT [=] 'comment_text' ]
            [DATA DIRECTORY [=] 'data_dir']
            [INDEX DIRECTORY [=] 'index_dir']
            [MAX_ROWS [=] max_number_of_rows]
            [MIN_ROWS [=] min_number_of_rows]
            [TABLESPACE [=] tablespace_name]
            [NODEGROUP [=] node_group_id]
            [(subpartition_definition [, subpartition_definition] ...)]
    
    subpartition_definition:
        SUBPARTITION logical_name
            [[STORAGE] ENGINE [=] engine_name]
            [COMMENT [=] 'comment_text' ]
            [DATA DIRECTORY [=] 'data_dir']
            [INDEX DIRECTORY [=] 'index_dir']
            [MAX_ROWS [=] max_number_of_rows]
            [MIN_ROWS [=] min_number_of_rows]
            [TABLESPACE [=] tablespace_name]
            [NODEGROUP [=] node_group_id]
    
    select_statement:
        [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)
    
    CREATE TABLE creates a table with the given name. You must have the
    CREATE privilege for the table.
    
    Rules for permissible table names are given in
    http://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,
    the table is created in the default database, using the InnoDB storage
    engine. An error occurs if the table exists, if there is no default
    database, or if the database does not exist.
    
    URL: http://dev.mysql.com/doc/refman/5.5/en/create-table.html
    
    
    mysql>
    

    数据库的CRUD语句

    • Insert
    • Update
    • delete
    • select
    image.png

    插入语法:


    image.png
    image.png

    向数据库中插入数据时候应该保持数据一致
    如果忘记可以查看标的字段类型
    命令如下:

    mysql> desc employee;
    +----------+------------------+------+-----+---------+-------+
    | Field    | Type             | Null | Key | Default | Extra |
    +----------+------------------+------+-----+---------+-------+
    | id       | int(10) unsigned | YES  |     | NULL    |       |
    | name     | varchar(100)     | NO   |     |         |       |
    | sex      | char(1)          | NO   |     |         |       |
    | brithday | date             | YES  |     | NULL    |       |
    | job      | varchar(30)      | NO   |     |         |       |
    | salary   | decimal(10,2)    | NO   |     | 0.00    |       |
    | jieshao  | text             | YES  |     | NULL    |       |
    +----------+------------------+------+-----+---------+-------+
    7 rows in set (0.09 sec)
    

    插入数据:

    mysql> desc employee;
    +----------+------------------+------+-----+---------+-------+
    | Field    | Type             | Null | Key | Default | Extra |
    +----------+------------------+------+-----+---------+-------+
    | id       | int(10) unsigned | YES  |     | NULL    |       |
    | name     | varchar(100)     | NO   |     |         |       |
    | sex      | char(1)          | NO   |     |         |       |
    | brithday | date             | YES  |     | NULL    |       |
    | job      | varchar(30)      | NO   |     |         |       |
    | salary   | decimal(10,2)    | NO   |     | 0.00    |       |
    | jieshao  | text             | YES  |     | NULL    |       |
    +----------+------------------+------+-----+---------+-------+
    7 rows in set (0.01 sec)
    
    mysql> INSERT INTO employee (id,name,sex,brithday,job,salary,jieshao) VALUES (10
    0,'盛世','男','2011-1-2','开发','100.23','英语好');
    Query OK, 1 row affected (0.04 sec)
    
    mysql>
    

    如果出现如图编码问题那么解决方式就如下:
    [图片上传失败...(image-c973e2-1556531627062)]

    注意 \s 可以查看当前mysql数据库信息

    mysql> \s
    --------------
    mysql  Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)
    
    Connection id:          11
    Current database:       db100
    Current user:           root@localhost
    SSL:                    Not in use
    Using delimiter:        ;
    Server version:         5.5.38 MySQL Community Server (GPL)
    Protocol version:       10
    Connection:             localhost via TCP/IP
    Server characterset:    utf8
    Db     characterset:    utf8
    Client characterset:    gbk
    Conn.  characterset:    gbk
    TCP port:               3306
    Uptime:                 6 hours 49 min 16 sec
    
    Threads: 1  Questions: 154  Slow queries: 0  Opens: 64  Flush tables: 1  Open ta
    bles: 1  Queries per second avg: 0.006
    --------------
    
    mysql>
    

    查询

    mysql> select * from employee;
    +------+------+-----+------------+------+--------+---------+
    | id   | name | sex | brithday   | job  | salary | jieshao |
    +------+------+-----+------------+------+--------+---------+
    |  100 | 盛世 | 男  | 2011-01-02 | 开发 | 100.23 | 英语好  |
    +------+------+-----+------------+------+--------+---------+
    1 row in set (0.05 sec)
    
    mysql>
    

    添加数据时候如果是全字段添加那么可以省略前边的字段名如下(如果不是添加的全字段,那么对应的字段名一定要写清楚):

    mysql> INSERT INTO employee VALUES ('106','盛世7','男','2011-2-2','开发','110.23
    ','英语好') ;
    Query OK, 1 row affected (0.08 sec)
    
    

    相关文章

      网友评论

          本文标题:第08讲 mysql添加数据

          本文链接:https://www.haomeiwen.com/subject/uqjunqtx.html