MySQL中数据表的增操作
今天学习到表的增操作,写个博客总结一下,PS:以下所有的凝视都是我在电脑上所有操作完毕后,再拷贝到记事本上加入的。至于在运行的时候可不能够那样加入凝视,就有待考证了。
选择库
mysql> show databases;#查看眼下有哪些数据库存在 +--------------------+ | Database | +--------------------+ | information_schema | | ceshi_ku | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> use ceshi_ku;#选择该数据库,等下用来建表,貌似选择数据库能够不要";" Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
建表
mysql> create table biao -> ( -> id int, -> name char(10), -> gender char(2), -> gongsi char(15), -> gongzi float -> );#先建立一个简单的表,编号、名字、性别、公司、工资,建表眼下不是重点,是方便接下来的操作 Query OK, 0 rows affected (0.13 sec) mysql> select * from biao;#查看表中的内容,眼下是空表 Empty set (0.00 sec)
插入数据(所有列,多行)
mysql> insert into biao#选择要插入哪张表 -> (id,name,gender,gongsi,gongzi)#往哪些列里面插入内容 -> values#接下来開始插入3条,分别用","隔开,最后一个用";" -> (1,'ma yun','n','tao bao',6000.5),#把要插入内容依次写好,不可调换顺序!要一一相应 -> (2,'xiao hong','v','xin lang',5000.7), -> (3,'xiao ming','n','bai du',3000.4); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from biao;#查看表中的内容,如今会发现多了3行 +------+-----------+--------+----------+--------+ | id | name | gender | gongsi | gongzi | +------+-----------+--------+----------+--------+ | 1 | ma yun | n | tao bao | 6000.5 | | 2 | xiao hong | v | xin lang | 5000.7 | | 3 | xiao ming | n | bai du | 3000.4 | +------+-----------+--------+----------+--------+ 3 rows in set (0.01 sec)
插入数据(缺省列,单行)
mysql> insert into biao#这次没有指定往哪些列里面插入,没指定就是往全部列插入 -> values -> (4,'li si','n','sou hu',9000.2);#这次是仅仅插入一行,也能够插入多行,仅仅需用","隔开 Query OK, 1 row affected (0.07 sec) mysql> select * from biao;#查看表内容,会发现比上次多了一行 +------+-----------+--------+----------+--------+ | id | name | gender | gongsi | gongzi | +------+-----------+--------+----------+--------+ | 1 | ma yun | n | tao bao | 6000.5 | | 2 | xiao hong | v | xin lang | 5000.7 | | 3 | xiao ming | n | bai du | 3000.4 | | 4 | li si | n | sou hu | 9000.2 | +------+-----------+--------+----------+--------+ 4 rows in set (0.00 sec)
插入数据(指定列,多行)
mysql> insert into biao -> (id,name,gongsi,gongzi)#这次不是往全部列插入,而是部分列 -> values -> (5,'lao wang','wang yi',1000.5),#依次写好便可,记得相应关系, -> (6,'xiao li','ku gou',700.1); Query OK, 2 rows affected (0.15 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from biao;#查看表内容,这次会上次发现多了2行,可是"gender"是空的 +------+-----------+--------+----------+--------+ | id | name | gender | gongsi | gongzi | +------+-----------+--------+----------+--------+ | 1 | ma yun | n | tao bao | 6000.5 | | 2 | xiao hong | v | xin lang | 5000.7 | | 3 | xiao ming | n | bai du | 3000.4 | | 4 | li si | n | sou hu | 9000.2 | | 5 | lao wang | NULL | wang yi | 1000.5 | | 6 | xiao li | NULL | ku gou | 700.1 | +------+-----------+--------+----------+--------+ 6 rows in set (0.00 sec)