主要内容
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)