table 的简单操作

主要内容

SET NAMES GBK; //修改客户端的编码方式
SHOW DATABASES //查看数据库
SHOW TABLES //查看表
USE database_name //使用 数据库
SELECT DATABASE(); // 查看当前数据库
SHOW TABLES FROM database_name //查看指定数据库的表
SHOW COLUMNS FROM tab_name; //查看表的结构

//创建表
CTREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type,

)
//插入数据
INSERT [INTO] tal_name [(col_name,…)] VALUES(val,…)

//查看数据
SELECT * FROM tab_name;

col的约束,属性
primary key 主键约束
UNUQUE KEY 唯一约束
default 默认约束
NOT NULL or null 约束

UNSGINED 无符号

AUTO_INCREMENT 自增 必须是数值型和主键组合使用

查看,使用 数据库

mysql> SHOW DATABASES
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| ti                 |
+--------------------+
5 rows in set (0.02 sec)

mysql> USE ti
Database changed


//查看当前数据库
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| ti         |
+------------+
1 row in set (0.00 sec)

创建表

CTREATE TABLE [IF NOT EXISTS] table_name (

column_name data_type,

)


mysql> CREATE TABLE tab1(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8, 2) UNSIGNED
    -> );
Query OK, 0 rows affected (0.20 sec)

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

删除表

DROP TABLE tbl_name;

DROP TABLE IF EXISTS tbl_name;

查看表

////查看mysql 的表
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.00 sec)

查看表的结构


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

添加数据


// 这里添加数据有两种方法
//方法1. 省略列名称 必须为所有的字段赋值
mysql> INSERT tab1 VALUES('Tom',25,78996.12);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT tab1 VALUES('Tom',25);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

//方法2. 指定列名称
mysql> INSERT tab1(username,salary) VALUES('json', 55555.1);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM tab1;
+----------+------+----------+
| username | age  | salary   |
+----------+------+----------+
| Tom      |   25 | 78996.12 |
| json     | NULL | 55555.10 |
+----------+------+----------+
2 rows in set (0.00 sec)

NOT NULL or null


mysql> CREATE TABLE tab2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> SHOW COLUMNS FROM tab2;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT tab2 (username,age) VALUES('Tom', NULL);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT TAB2(username) VALUES('Job');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tab2;
+----------+------+
| username | age  |
+----------+------+
| Tom      | NULL |
| Job      | NULL |
+----------+------+
2 rows in set (0.00 sec)

AUTO_INCREMENT


mysql> CREATE TABLE tab3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(30) NOT NULL
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key  //AUTO_INCREMENT 必须用在primary key 上

AUTO_INCREMENT PRIMARY KEY


mysql> CREATE TABLE tab3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> SHOW COLUMNS FROM tab3;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT tab3(username) VALUES('TOM');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT tab3(username) VALUES('TSS');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM TAB3;
+----+----------+
| id | username |
+----+----------+
|  1 | TOM      |
|  2 | TSS      |
+----+----------+
2 rows in set (0.00 sec)

PRIMARY KEY


mysql> CREATE TABLE tab4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> SHOW COLUMNS FROM TAB4
    -> ;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT tab4 VALUES(1, 'tOM');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT tab4 VALUES(2, 'sss');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT tab4 VALUES(2, '444');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> SELECT * FROM TAB4;
+----+----------+
| id | username |
+----+----------+
|  1 | tOM      |
|  2 | sss      |
+----+----------+
2 rows in set (0.00 sec)

UNIQUE KEY


mysql> CREATE TABLE tab5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.20 sec)

mysql> SHOW  COLUMNS FROM TAB5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT tab5(username,age) VALUES('Tom',25);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT tab5(username,age) VALUES('Tom',23);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'
mysql> INSERT tab5(username,age) VALUES('Bob',23);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM tab5;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Tom      |   25 |
|  3 | Bob      |   23 |
+----+----------+------+
2 rows in set (0.00 sec)

default


mysql> CREATE TABLE tab6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2', '3') DEFAULT '3'
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> SHOW COLUMNS FROM tab6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | YES  |     | 3       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT tab6(username) VALUES('Tom');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tab6;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | Tom      | 3    |
+----+----------+------+
1 row in set (0.00 sec)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值