1.查询一个表中的所有数据
mysql> select * from wz;
+----+--------+--------+------------+
| id | title | author | date |
+----+--------+--------+------------+
| 1 | 学习 | james | 2018-09-13 |
| 10 | 工作 | mike | 2018-09-13 |
+----+--------+--------+------------+
2 rows in set (0.00 sec)
2.where语句,条件判断,可以从多个表中查询,每个表之间用“,”隔开,where语句可用于select,delete,update命令中,可以用AND和OR判断多个条件,可以理解为编程语言中的if,注意判断相等用“=”而不是“==”,在where语句中没有“==”这种操作符。
where性能:依赖索引,对主键进行where判断性能很快
where操作符:
=:检测两个值是否相等
!=:检测两个值是否不等
>:检测左边是否大于右边
<:检测右边是否大于左边
>=:大于等于
<=:小于等于
字符串:默认不区分大小写,使用where binary可以实现区分大小写,即严格匹配
返回值:对于select,若找到了,返回所有满足条件的记录,否则不返回任何数据,例子如下:
mysql> select * from wz where author = 'james';
+----+--------+--------+------------+
| id | title | author | date |
+----+--------+--------+------------+
| 1 | 学习 | james | 2018-09-13 |
+----+--------+--------+------------+
1 row in set (0.01 sec)
3.update语句,更新记录,可以一次更新多项记录,可以使用where锁定行,只修改这一行,否则修改所有记录
mysql> update wz set author = 'edward' where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from wz;
+----+--------+--------+------------+
| id | title | author | date |
+----+--------+--------+------------+
| 1 | 学习 | edward | 2018-09-13 |
| 10 | 工作 | mike | 2018-09-13 |
+----+--------+--------+------------+
4.delete语句,可以加where只删除不符合条件的行,若没有where,会删除表,注意对于自增字段,这里是id,之前删了一个id为10的,后续添加的时候,若不指定id,那么从11开始计数
mysql> delete from wz where id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select * from wz;
+----+--------+--------+------------+
| id | title | author | date |
+----+--------+--------+------------+
| 1 | 学习 | edward | 2018-09-13 |
+----+--------+--------+------------+
1 row in set (0.00 sec)
mysql> insert into wz (title, author, data)
-> values
-> ('工作', 'james', now());
ERROR 1054 (42S22): Unknown column 'data' in 'field list'
mysql> insert into wz (title, author, date)
-> values
-> ('工作', 'edward', now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from wz;
+----+--------+--------+------------+
| id | title | author | date |
+----+--------+--------+------------+
| 1 | 学习 | edward | 2018-09-13 |
| 11 | 工作 | edward | 2018-09-14 |
+----+--------+--------+------------+
2 rows in set (0.00 sec)
5.like语句是一个简单的正则表达式,用“%”表示所有字符,若like语句中没有%,那么效果和“=“是一样的,一般使用where like语句指定条件,效果是查找元字符,如下:
mysql> select * from wz where author like '%ame%';
+----+--------+--------+------------+
| id | title | author | date |
+----+--------+--------+------------+
| 12 | 娱乐 | james | 2018-09-14 |
+----+--------+--------+------------+
1 row in set (0.00 sec)
这里表示查找字符串中间有”ame“的记录,注意%可以表示空字符,比如条件改为"%mes%",也可以查到结果