1.创建数据库及表:CREATE DATABASE text; use text;(在text下创建表) CREATE TABLE celebs;
2.表创建列:列:CREATE TABLE celebs(id INTEGER, name TEXT, age INTEGER);
另外增加列:ALTER TABLE celebs ADD COLUMN twitter_handler TEXT;
3.插入数据: INSERT INTO celebs(id, name, age ) VALUES(1, 'Aochen ', 12);
4.查看数据:查看全部:SELECT * FROM celebs;
查看其中一项: SELECT name FROM celebs;
查看数据(没有重复):SELECT DISTINCT genre FROM movies;(genre中没有重复的部分)
5.更新数据: UPDATE celebs SET age = 21 WHERE id = 1;(where是范围,更新id为1的年龄)
6.删除行:DELETE FROM celebs WHERE twitter_handle IS NULL;(删除twitter_handle为空的行)
7.查询 SELECT * FROM celebs WHERE name LIKE 'Aochen';(查询表中所有名字为‘Aochen’的数据)
SELECT * FROM celebs WHERE name LIKE '%man%';(查询表中所有名字中有‘man’的数据)
SELECT * FROM celebs WHERE name BETWEEN 'A' AND 'J';(查询所有开头A到J 的名字)
SELECT * FROM celebs ORDER BY imdb_rating DESC LIMIT 3;(按字母顺序排列3个)
SELECT COUNT(*) FROM celebs;(表中行数)
SELECT price, COUNT(*) FROM celebs ORDER BY price;(查询price的数量及按大小排列)
8.求和 SELECT SUM(price) FROM celebs;
9.最大值、最小值、平均值 SELECT MAX(price) FROM celebs; 最小的话MAX换成MIN 平均值为AVG
10.余数 SELECT price,ROUND(AVG(price), 2) FROM celebs ORDER BY price; 平均值取2位小数,ROUND默认为一位小数。