1.1、 关于表中记录(数据)的操作
1.1.1、 增
-
插入字段<记录>INSERT
语法:insert into 表名values (字段值1,字段值2, 字段值3);
插入记录时要对应相对的类型
先删除students表,再创建
mysql> drop tables students;
Query OK, 0 rows affected (0.01 sec)
mysql> create table students(id int(20),name char(40),age int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into students values(1,‘zhangs’,21);
Query OK, 1 row affected (0.01 sec) -
同时插入多条记录
mysql> insert into students values(2,‘lis’,24),(3,‘wange’,26);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0 -
分开插入表记录
mysql> insert into students (id,name)values(4,‘hangl’);
Query OK, 1 row affected (0.00 sec)
如果表中有4个字段,插入三个字段(可以通过null指定空值)
mysql>insert into students values(4,’lisi’,null,5)
1.1.2、 查
语法:
select * from 表名称; # *号表示查询表中所有字段(列)
查询某个字段为空的记录
mysql>select * from db2.t1 where age is null;
查询某个字段不为空的记录
mysql>select * from db2.t1 where age is not null;
错误示范:
mysql>select * from db2.t1 where age=null; //不符合语法规范
查询student表中所有记录
mysql> select * from students; -
当表中记录比较多时可以使用\G查看
mysql> select * from student\G -
只查询表中某个字段的内容
mysql> select name from students;
mysql> select id,name from students;
- 查看别的数据库的表或者不在本数据库上进行查看
语法:SELECT 字段 FROM 数据库名.表名;
效果等同于先使用use数据库,然后再看看表内容
mysql> select * from HA.students;
1.1.3、 改
7. 把表中id为2的记录age更新为25
mysql> update students set age=‘25’ where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
±-----±-------±-----+
| id | name | age |
±-----±-------±-----+
| 1 | zhangs | 21 |
| 2 | lis | 25 |
±-----±-------±-----+
2 rows in set (0.01 sec)
8. 把表中所有的id都更新为2
mysql> update students set id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select * from students;
- 同时更新多条记录,请使用逗号隔开
mysql> update students set id=1,name=‘zhangsan’ where age=21;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
1.1.4、 删
10. 删除students表中id为3的行
mysql> delete from students where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from students; #发现表中id为3的记录不见了
±-----±-------±-----+
| id | name | age |
±-----±-------±-----+
| 1 | zhangs | 21 |
| 2 | lis | 24 |
| 4 | hangl | NULL |
±-----±-------±-----+
3 rows in set (0.00 sec)
11. 删除age为空的行
mysql> delete from students where age is null;
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
±-----±-------±-----+
| id | name | age |
±-----±-------±-----+
| 1 | zhangs | 21 |
| 2 | lis | 24 |
±-----±-------±-----+
2 rows in set (0.00 sec)