第08讲 mysql添加数据

帮助命令的使用：

mysql> ? createMany help items for your request exist.To make a more specific request, please type 'help <item>',where <item> is one of the followingtopics:   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 tableName: '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_statementOr: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_typeindex_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 ACTIONtable_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 theCREATE privilege for the table.Rules for permissible table names are given inhttp://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,the table is created in the default database, using the InnoDB storageengine. An error occurs if the table exists, if there is no defaultdatabase, or if the database does not exist.URL: http://dev.mysql.com/doc/refman/5.5/en/create-table.htmlmysql>

数据库的CRUD语句

• Insert
• Update
• delete
• select

插入语法：


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

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 (100,'盛世','男','2011-1-2','开发','100.23','英语好');Query OK, 1 row affected (0.04 sec)mysql>

如果出现如图编码问题那么解决方式就如下：

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

mysql> \s--------------mysql  Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)Connection id:          11Current database:       db100Current user:           root@localhostSSL:                    Not in useUsing delimiter:        ;Server version:         5.5.38 MySQL Community Server (GPL)Protocol version:       10Connection:             localhost via TCP/IPServer characterset:    utf8Db     characterset:    utf8Client characterset:    gbkConn.  characterset:    gbkTCP port:               3306Uptime:                 6 hours 49 min 16 secThreads: 1  Questions: 154  Slow queries: 0  Opens: 64  Flush tables: 1  Open tables: 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)