美文网首页
第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添加数据

    帮助命令的使用: 还有一种方式 ? create table执行后整个疯了 一大堆东西 数据库的CRUD语句 In...

  • Mysql学习——数据库基础操作(1)

    Mysql创建数据库Mysql删除数据库Mysql创建表Mysql删除表Mysql添加表数据Mysql修改表数据M...

  • MySQL+关联(上)

    飞谷云 MySQL 第3讲 MySQL+表连接 课程概要: 回顾第二讲 表关联 文件关联 把数据导入Spark-S...

  • ARK-MySQL插件

    一、MySql数据库配置、 添加MySql数据库表文件 1.创建数据库,添加表。 sql文件目录:Bin\Serv...

  • Mac终端使用MySql

    连接MySql数据库 添加MySql命令路径 PATH="$PATH":/usr/local/mysql/bin ...

  • MySQL-数据备份与恢复

    数据备份 示例 数据恢复 MySQL的用户账户管理 开启MySQL远程连接 添加授权用户

  • mysql之管理

    MySQL 用户设置 如果你需要添加 MySQL 用户,你只需要在 mysql 数据库中的 user 表添加新用户...

  • MySQL(上)

    管理用户添加MySQL用户:在mysql数据库的user表中添加新用户/GRANT命令 use 数据库名;使用该命...

  • Linux下Zabbix监控之MySQL监控添加

    Mysql数据库监控 zabbix页面添加Mysql监控默认模板 创建mysqladmin链接配置 Mysql监控...

  • Public Key Retrieval is not allo

    在mysql数据库连接后面添加 例如:

网友评论

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

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