数据类型
数值型:int float double decimal
日期型:date datetime
字符型:char varchar text
新建查询(注意指向的库的位置)
#规范写法是大写
#新建表 TABLE
#括号里面写属性,也就是列名
建库 删库
#建
CREATE DATABASE course;
CREATE DATABASE courses DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
#删
DROP DATABASE course;
建表 清表 删表
#建表
CREATE TABLE student(
id int(5),
name VARCHAR(10),
sex CHAR);
#清表
TRUNCATE TABLE student;
#删表
DROP TABLE student;
增(插单个/多个数据)
INSERT INTO student
(id,name,sex,times)
VALUES
(1,'TOM','1',CURRENT_TIME);
如果只有id,name,sex,times几个属性,可以这样写:
INSERT INTO student
VALUES
(1,'TOM','1',CURRENT_TIME);
批量插入:
INSERT INTO student
(id,name,sex,times)
VALUES
(1,'TOM','1',CURRENT_TIME),
(2,'Jerry','1',CURRENT_TIME),
(3,'John','1',CURRENT_TIME);
删
【删除】
【格式:DELETE FROM 表名 WHERE 限定条件;】
【注意DELETE时要添加WHERE条件,不然会删除整个表】
DELETE FROM students WHERE id = '7';
改 更新(Update)
【更新】
UPDATE students SET name = '李六' WHERE name = '赵六'
查
WHERE子句(结果是记录):=、>、<、>=、<=、BETWEEN AND、LIKE'%_'、IS(NOT)NULL
SELECT子句:聚合函数(结果是数值):SUM()、COUNT()、MAX()、MIN()、AVG()
排序:SELECT 字段,字段 FROM 表 ORDER BY 字段 ASC(默认从小到大) DESC
分组:SELECT 字段,聚合函数 FROM 表 GROUP BY 字段 HAVING 条件
联合表查询:
笛卡尔积(两个表的数量乘积)
内关联(只能查询两个表关联的数据,其他数据舍弃)
左关联(查询出主表所有数据,关联从表对应数据)
#查询所有
SELECT * FROM student ;
#模糊查询 #格式 LIKE '%字符%'
SELECT * FROM student WHERE name LIKE '张%';
#查询年龄大于20岁
SELECT * FROM student WHERE age>20;
#查询年龄大于20岁小于30岁
SELECT * FROM student WHERE age>20 && age<30;
SELECT * FROM student WHERE age>20 AND age<30;
SELECT * FROM student WHERE age BETWEEN 20 AND 29;
#查询年龄大于30岁小于20岁
SELECT * FROM student WHERE age<20 || age>30;
SELECT * FROM student WHERE age<20 OR age>30;
SELECT * FROM student WHERE age NOT BETWEEN 20 AND 30;
#插一条无名字的数据
INSERT INTO student
(id,sex,age,salary)
VALUES
(6,'1',30,2300.5);
#查询名字为空
SELECT * FROM student WHERE name IS NULL;
SELECT * FROM student WHERE ISNULL(name);
#查询名字不为空
SELECT * FROM student WHERE name IS NOT NULL;
SELECT
# 查年龄最小值
SELECT MIN(age) FROM student;
# 查年龄最大值
SELECT MAX(age) FROM student
# 查年龄总值
SELECT SUM(age) FROM student;
# 计数
SELECT COUNT(age) FROM student;
SELECT COUNT(1) FROM student;
SELECT COUNT(*) FROM student;
# 查年龄平均值
SELECT AVG(age) FROM student;
SELECT SUM(age)/COUNT(age) FROM student;
#去除列的重复值
SELECT DISTINCT sex FROM student;
#字段 别名
SELECT SUM(age)/COUNT(age) AS avgs FROM student;
SELECT SUM(age)/COUNT(age) avgs FROM student;
#表 别名 - 用于多表联查
SELECT SUM(s.age)/COUNT(s.age) avgs FROM student s;
排序
#排序(默认从小到大)【尽量不要用ORDER BY它耗时长,一千以内还可以】
SELECT * FROM student ORDER BY age;
SELECT * FROM student ORDER BY salary ASC;
#排序(从大到小)
SELECT * FROM student ORDER BY salary DESC;
#排序,多字段。
SELECT * FROM student ORDER BY age,salary;
聚合
#聚合函数[去重后,统计个数,平均数等情况]
#各个年龄的人数
SELECT age,COUNT(age) FROM student GROUP BY age;
#各个性别的人数
SELECT sex,COUNT(sex) FROM student GROUP BY sex;
#各个年龄的平均收入
SELECT age,AVG(salary) FROM student GROUP BY age;
#男女中最大年龄分别是
SELECT sex,MAX(age) FROM student GROUP BY sex;
#男女各有多少人
SELECT sex,COUNT(1) FROM student GROUP BY sex;
过滤
# 先where过滤,再提取。where效率更高,优先用它
SELECT sex,MAX(age) FROM `student` WHERE sex='1';
# 先分组,再通过having筛除
SELECT sex,MAX(age) FROM `student` GROUP BY sex HAVING sex='1';
#指定年龄段里,各个年龄有多少人。
SELECT age,COUNT(1) FROM student GROUP BY age HAVING age>=21&&age<=26;
SELECT age,COUNT(1) FROM student WHERE age>=21&&age<=26 GROUP BY age;
关联
#关联查询
#没有限制条件产生 笛卡尔积,两个表数据相乘6*6=36条
SELECT * FROM student,object;
#内连接(只取有对应关系的数据,舍弃没有关联的数据)
SELECT * FROM student s,object o WHERE o.user_id = s.id;
SELECT s.*,o.* FROM student s INNER JOIN object o ON o.user_id = s.id;
#外连接(取所有表的所有数据,不舍弃任何数据)
#左连接(用的较多,取左边的表)
【查询主表(左边表)中的所有数据,然后与从表(右边表)关联,若从表无关联对应数据则填null】
SELECT s.*,o.*
FROM students s LEFT JOIN object o ON s.id = o.user_id; (左连接)
#右连接