1、创建表格
先创建一个数据库movietalk,设置数据库字符集utf8(charset=utf8)
创建一个名为people的表格,指定people_id为primary key,unsigned(字段值为正数)、not null(不为空)、auto _increment(排序自动增加)
2、插入数据 INSERT Syntax
INSERT INTO tbl_name (col_name) VALUES(col_value);
插入的数据名跟值要一一对应,文本类型的数据需添加引号
insert into people (people_name,people_birth) value ('John', '1965-02-24');
3、选择数据 SELECT Syntax
SELECT col_name FROM tbl_name WHERE condition;
硬翻:select语句是用来从一张或多张表格中检索符合条件的行
SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries
select people_name from people where people_birth='1965-02-24';
我们可以用ORDER BY
设置查询结果的排序方式,默认是ASC升序,这里设置成 DESC降序
SELECT * FROM people ORDER BY people_birth DESC;
4、更新数据 UPDATE Syntax
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT}
[, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
更新 表格 设置 col值 where条件
UPDATE people SET people_birth='1965-01-24', people_name='Mark' WHERE people_id = 1;
5、删除数据
DELETE FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
delete from people where people_id=1;
6 、LIMIT 与 OFFSET
LIMIT 限制查询结果数量,比如只显示3条查询结果
SELECT * FROM people WHERE people_location = 'York' LIMIT 3;
OFFSET设置偏移量,比如LIMIT设置成3,OFFSET设置成1,往后偏移1个数,找到的结果从第2行开始显示,id为2,3,4
7 、操作符
比较:大于,小于,大于等于,小于等于,不等于
Comparison Functions and Operators
SELECT * FROM people WHERE people_birth > '1960-01-01';
IN( )
Check whether a value is within a set of values
测试一个值是否在一个值的集合里
SELECT * FROM people WHERE people_location IN ('NEW YORK');
LIKE
Simple pattern matching
简单模式匹配
通配符%表示一个或多个字符, _表示一个字符
SELECT * FROM people WHERE people_name LIKE ('J%');
‘J_’表示找出以J开头两个字符的名字
逻辑 Logical Operators:与AND或OR非NOT
SELECT * FROM people WHERE people_birth > '1960-01-01' AND people_birth < '1970-01-01';
查询出生日期在1960年1月1号与1970年1月1号之间的人
参考:
宁皓网——MySQL基础
官网手册