五、表数据操作

1、数据插入

insert into 表名 values(值……)

-- 完整的按照顺序的插入字段
insert into 表名 values(值……)
 
-- 指定字段插入数据
insert into 表名(字段名，……) values (值……);

mysql> insert into t1 values('莫甘娜','女'),('卡尔','Y'); -- 性别只能是X Y 
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
 
mysql> insert into t1 values('莫甘娜','X'),('卡尔','Y');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SHOW TABLES ;
+--------------+
| Tables_in_b1 |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)
 
mysql> SELECT T1;
ERROR 1054 (42S22): Unknown column 'T1' in 'field list'
mysql> SELECT * FROM T1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
+-----------+------+
2 rows in set (0.00 sec)
 
mysql> insert into t1(NAME) values('凯莎'),('华烨');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name  | varchar(4)    | YES  |     | NULL    |       |
| sex   | enum('X','Y') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM T1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
| 凯莎      | NULL |
| 华烨      | NULL |
+-----------+------+

2、字段查询

select * from 表名;

-- 获取整个表中的所有字段
select * from 表名;
 
--获取查询指定字段
select 字段名，…… from 表名;

mysql> SELECT * FROM T1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
| 凯莎      | NULL |
| 华烨      | NULL |
+-----------+------+
 
mysql> select name from t1;
+-----------+
| name      |
+-----------+
| 莫甘娜    |
| 卡尔      |
| 凯莎      |
| 华烨      |
+-----------+
 
mysql> select name ,sex from t1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
| 凯莎      | NULL |
| 华烨      | NULL |
+-----------+------+