帮助命令的使用:
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
向数据库中插入数据时候应该保持数据一致
如果忘记可以查看标的字段类型
命令如下:
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)
网友评论