数据库操作

数据类型

数值型：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)
VALUE
(1,'TOM','1',CURRENT_TIME);

【删除】
【格式：DELETE FROM 表名 WHERe 限定条件;】
【注意DELETe时要添加WHERe条件，不然会删除整个表】
 DELETE FROM students WHERe id = '7';

    【更新】
    UPDATE students
    SET name = '李六' WHERe name = '赵六'
     
    UPDATE students
    SET salary = 1900.5 WHERe id = 1

WHERe

#查询所有
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;  （左连接）

#右连接