USE my_2203;
SHOW TABLES;
---创建表
CREATE TABLE person2(id INT,NAME VARCHAR(20),age INT ,sex VARCHAR(20) ,brithday DATE, math INT,english INT);
---给表中添加值
INSERT INTO person2 VALUES (1,"张三",20,'男','1999-8-4',75,85);
INSERT INTO person2 VALUES (2,"李四",22,'男','1997-8-4',52,61);
INSERT INTO person2 VALUES (3,"王二",25,'男','2000-8-4',96,99);
INSERT INTO person2 VALUES (4,"张笑",28,'男','2001-8-4',85,63);
INSERT INTO person2 VALUES (5,"李晓",29,'男','2002-8-4',76,65);
INSERT INTO person2 VALUES (6,"王冰",25,'男','1998-8-4',98,96);
INSERT INTO person2 VALUES (7,"张斌",35,'男','1997-8-4',79,89);
INSERT INTO person2 VALUES (8,"李冰",22,'男','1999-8-4',86,63);
INSERT INTO person2 (id,NAME)VALUES (9,"王思");
---查询表的所有信息 SELECT *FROM 表名 *表示该表的所有字符段;
SELECT *FROM person2;
---清空表 DELETE FROM 表名;
DELETE FROM person2;
/
*条件查询 SELECT 别名.字段1 "别名",别名.字段2 "别名",....别名.字段n '别名'FROM 表名 别名 WHERE 字段 条件表达式;
*1,可以用算数运算符 =, <,>,<=,>=
*2,可以用逻辑运算符 &&,||
*3,mysql特有is NULL,IS NOT NULL,字段 BETWEEN 值一 AND 值二;AND 和 OR 相当于&&he||;
/
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE math > 80 ;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE math >= 52 && math<= 82 ;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE math >= 52 AND math<= 82 ;
SELECT *FROM person2 WHERE math BETWEEN 52 AND 82;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE math BETWEEN 52
AND 82 ;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE english = 63 || english = 65 || english = 96 ;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE english = 63 OR english = 65 OR english = 96 ;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE english IN(63,65,96);
/
*模糊查询 SELECT * FROM 表名 WHERE 字段 LIKE '';''里使用%或者_
/
---查询表中姓王的人;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE NAME LIKE '%王%';
---查询表中第二个字为冰的人;
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE NAME LIKE '_冰';
/
*聚合函数查询 COUNT 查询数据输入的总次数 一般使用id
SUM 求和
MAX 最大值
MIN 最小值
AVG 平均数
SELECT 函数名(字段) FROM 表名
/
SELECT COUNT(id) FROM person2;
SELECT SUM(english) FROM person2;
SELECT SUM(math) FROM person2;
SELECT MAX(math) FROM person2;
SELECT AVG (math) FROM person2;
---查询数学分数大于平均分的人的信息;
SELECT
*
FROM
person2
WHERE math>(SELECT AVG (math) FROM person2);
/
*排序查询法 ORDER BY 字段 DESC(降序) ASC(升序) 有条件时必须在之前
SELECT
p2.`id` '编号',
p2.`name` '姓名',
p2.`age` '年龄',
p2.`sex` '性别',
p2.`brithday` '生日',
p2.`math` '数学成绩',
p2.`english` '英语成绩'
FROM
person2 p2
WHERE NAME LIKE '_冰'
ORDER BY math DESC ;