05.数据表的创建与简单操作

数据表的创建

使用数据库

mysql> USE zoom; Database changed

查看数据库信息

mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | zoom | +------------+ 1 row in set (0.00 sec)

创建表

mysql> CREATE TABLE little( username VARCHAR(20), age TINYINT UNSIGNED, weight FLOAT(5,2) UNSIGNED ); Query OK, 0 rows affected (0.41 sec)

显示表

mysql> SHOW TABLES; +----------------+ | Tables_in_zoom | +----------------+ | little | +----------------+ 1 row in set (0.00 sec)

建表时字段的默认值

mysql> CREATE TABLE default_( -> age TINYINT UNSIGNED DEFAULT 2 -> ); Query OK, 0 rows affected (0.03 sec) mysql> INSERT default_ VALUES(); Query OK, 1 row affected (0.00 sec) mysql> SELECT*FROM default_; +------+ | age | +------+ | 2 | +------+ 1 row in set (0.00 sec)

查看表结构SHOW COLUMNS FROM 或DESC

mysql> SHOW COLUMNS FROM little; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | weight | float(5,2) unsigned | YES | | NULL | | +----------+---------------------+------+-----+---------+-------+ 3 rows in set (0.02 sec)

显示指定数据库的表

mysql> SHOW TABLES FROM mysql; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.01 sec)

简单操作

插入数据（所有字段均要赋值）

mysql> INSERT little VALUES('dog',37,12.77); Query OK, 1 row affected (0.01 sec)

为指定列赋值

mysql> INSERT little(username,age) VALUES('cat',27); Query OK, 1 row affected (0.36 sec)

查询

mysql> SELECT*FROM little; +----------+------+--------+ | username | age | weight | +----------+------+--------+ | dog | 37 | 12.77 | | cat | 27 | NULL | +----------+------+--------+ 2 rows in set (0.00 sec)

转载于:https://www.cnblogs.com/toly-top/p/9782029.html