0.基本概念
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAxNTAwNTc2P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAxNTM0MDQxP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAxNTQ3NTU1P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAxNjA3NDY2P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
1.创建数据库和表,给表添加字段和数据
创建saas数据库,和userInfo表,添加4个字段和4条记录
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAxNDE3MjYwP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
2.select查询语句
SELECT 列名称(字段名) FROM 表名称
SELECT * FROM 表名称
SELECT * FROM userInfo
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAyMTI0Njk4P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
SELECT name FROM userInfo
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAyMjA5NjQ1P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
SELECT name,age FROM userInfo
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAyNzIxMDUyP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
3.distinct查询语句
由上图可以看出,name 为 zhouyu 的信息有两条, 在表中,会包含重复值。这并不成问题,不过,有时您也许希望仅仅列出不同(distinct)的值。
SELECT DISTINCT name FROM userInfo
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTAzMDI4MDE1P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
4.where语句
- 如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句。
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
下面的运算符可在 WHERE 子句中使用:
操作符 |
描述 |
= |
等于 |
<> |
不等于 (某些版本的 SQL 中,操作符 <> 可以写为 !=) |
> |
大于 |
< |
小于 |
‘>=’ |
大于等于 |
<= |
小于等于 |
BETWEEN |
在某个范围内 |
LIKE |
搜索某种模式 |
SELECT * FROM userInfo WHERE name='zhouyu'
SELECT * FROM userInfo WHERE age > 25
SELECT * FROM userInfo WHERE age = 24
SELECT * FROM userInfo WHERE age != 24
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTA0MjE1NDIyP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
5.AND 和 OR 运算符
- AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
- 如果第一个条件和第二个条件都成立,则 AND 运算符显示一条记录。
- 如果第一个条件和第二个条件中只要有一个成立,则 OR 运算符显示一条记录。
SELECT * FROM userInfo WHERE name='zhouyu' and age=28
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTA0NzQyODYzP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
SELECT * FROM userInfo WHERE name='zhouyu' and age>20
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTA0ODU0NTYwP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
SELECT * FROM userInfo WHERE name='zhouyu' or age>20
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTA1MDA1NTM1P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
SELECT * FROM userInfo WHERE (name!='zhouyu' OR age>28) AND height>170
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTA1NDQzNzYxP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
6.ORDER BY 语句
- ORDER BY 语句用于根据指定的列对结果集进行排序。
- ORDER BY 语句默认按照升序对记录进行排序。
- 如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。
SELECT * FROM userInfo ORDER BY name
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTA1ODMyNDk0P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
SELECT name,height FROM userInfo ORDER BY height
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTEwMDEwMTcwP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
SELECT name,height,age FROM userInfo ORDER BY height DESC
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTEwMjQwNTI1P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
7.INSERT INTO 语句
- INSERT INTO 语句用于向表格中插入新的行——–既是,插入新的记录
//插入一条记录
INSERT INTO 表名称 VALUES (值1, 值2,....)
//我们也可以指定所要插入数据的列.
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
//插入一条完整记录
//键值对类型要对应
INSERT INTO userInfo VALUES (8,'xiaohua',18,162)
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTEwODQ5MDkwP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
//插入一条不完整的记录,为设置的值九尾NULL
INSERT INTO userInfo (UserID,name) VALUES (9,'haha')
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTExMTUxMTI1P3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
8.UPDATE 语句
//更新某一行中的一个列
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
![这里写图片描述](https://www.#.com/go/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwODAyMTExNTQ1MjUwP3dhdGVybWFyay8yL3RleHQvYUhSMGNEb3ZMMkpzYjJjdVkzTmtiaTV1WlhRdmEzVmhibWRrWVdOaGFXdDFZVzVuL2ZvbnQvNWE2TDVMMlQvZm9udHNpemUvNDAwL2ZpbGwvSTBKQlFrRkNNQT09L2Rpc3NvbHZlLzcwL2dyYXZpdHkvU291dGhFYXN0)
//更新某一行中的若干列
UPDATE userInfo SET age=10,hljs-number">145 WHERE name='haha'