【JAVA学习笔记07】数据库

sql语句基本操作

F9

注释

按F9,光标在前面

主键的作用是唯一标识一行

ctrl + s 保存

练习

-- 查询类、创建表、添加、删除、修改

-- 列出所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE java2307 DEFAULT CHARACTER SET utf8;

-- 删除数据库
DROP DATABASE java2307;

-- 切换数据库
USE `java230701`;
-- 创建表
CREATE TABLE `student`(
    `id` INT,
    `name` CHAR(10),
    age INT,
    gender CHAR(1)
);

-- 查看所有表
SHOW TABLES;
-- 删除表
DROP TABLE student;

-- 更改表的结构
-- 添加字段
ALTER TABLE student ADD COLUMN address CHAR(10);
-- 删除字段
ALTER TABLE student DROP COLUMN address;
-- 修改字段
ALTER TABLE student CHANGE address addr CHAR(20);
-- 修改表的名字
ALTER TABLE student RENAME TO stu;


-- PRIMARY KEY=unique key + not null
-- 主键的作用是唯一标识一行
-- AUTO_INCREMENT 主键自动增长
CREATE TABLE `student`(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` CHAR(10) NOT NULL,
    age INT,
    gender CHAR(1)
);

-- 查看表里面数据
SELECT * FROM student;
-- 插入数据(插入部分字段值，前面的列名必须要写上)
INSERT INTO student(`name`,age,gender) VALUES('赵六',23,'男');
-- Duplicate entry '1' for key 'PRIMARY'
INSERT INTO student(id,`name`,age,gender) VALUES(1,'赵六',23,'男');
INSERT INTO student(id,`name`,age,gender) VALUES(6,'赵六',23,'男');
INSERT INTO student VALUES(7,'赵六',23,'男');
-- 插入多条数据
INSERT INTO student(`name`,age,gender) VALUES('赵六1',23,'男'),('赵六2',23,'男');

-- 修改数据
UPDATE student SET age=age+1;
UPDATE student SET `name`='lisi11',age=age+1,gender='女' WHERE id=2;

-- 删除数据
DELETE FROM student; -- 删除表中所有数据（很少使用，是非常危险的）
DELETE FROM student WHERE id=10; -- 因为id是主键是唯一的，所以根据id只能删除一行
DELETE FROM student WHERE age=23 AND `name`='赵六1';
-- TRUNCATE删除表里面所有数据，自增的id会重新初始化为初始值1
TRUNCATE TABLE student;

-- 查询数据
-- 显示所有列(字段)数据
-- 学习时候可以写*，但是在企业开发中需要什么字段就写什么字段，检索不需要的列会降低检索速度和应用程序的性能
SELECT * FROM student;
SELECT id,`name`,age,gender FROM student;
SELECT `name`,id,age FROM student;
-- 查询指定列
SELECT `name`,age FROM student;
-- 查询时候添加常量列，通过as可以起别名， as可以省略
SELECT id,`name`,age,gender '性别','Java230701'AS '班级' FROM student;
-- 查询时候合并列，字段可以当成java里面的变量来运算
SELECT id,`name`,age,gender,(php+java) AS '总成绩' FROM student;


-- 查询时候去掉重复的记录
SELECT DISTINCT address FROM student;
  
-- 条件查询 where ,数据库表一般包含大量的数据，很少需要检索表中所有行
SELECT * FROM student WHERE `name`='李四';
  
-- 逻辑条件: and（同时成立） or(只要有一个成立)
SELECT * FROM student WHERE `name`='李四' AND address='北京';
SELECT * FROM student WHERE `name`='李四' OR address='北京';

-- 比较运算: >  <  >=  <=  != 不等于也可以写成：<>
SELECT * FROM student WHERE java>=70 AND java<=80;
-- between and (等价于>= and <=)
SELECT * FROM student WHERE java BETWEEN 70 AND 80;
-- 查询地址不是青岛的学生信息 <>
SELECT * FROM student WHERE address!='青岛'; 
SELECT * FROM student WHERE address<>'青岛'; 


-- 空值NULL：无值(no value)，它与字段包含0、空字符串或紧紧包含空格不同
SELECT * FROM student WHERE address IS NULL;
SELECT * FROM student WHERE address IS NOT NULL;

  
-- 聚合查询
-- 聚合查询函数：sum(),avg(),max(),min(),count()
-- 统计学生php的总成绩（sum求和）
SELECT SUM(php) AS 'php总成绩' FROM student;
-- 统计学生php的平均值
SELECT AVG(php) AS 'php平均值' FROM student;
-- 统计学生php的最大值
SELECT MAX(php) AS 'php最大值' FROM student;
-- 统计学生表里面一共有多少学生
SELECT COUNT(*) FROM student;
-- 注意：如果指定列明，则COUNT会忽略指定列的值为NULL的行，用*则不忽略。
SELECT COUNT(id) FROM student;
SELECT COUNT(address) FROM student;
  
-- 查询排序 desc:descending asc:ascending
-- 语法：order by 字段  asc/desc  默认是asc升序，可以不写
SELECT * FROM student ORDER BY php;
SELECT * FROM student ORDER BY php ASC;
SELECT * FROM student ORDER BY php DESC;

-- 多个条件排序
-- 需求：先按照php降序，java升序(整体是按照php降序，如果php相同的数据再按照java标准排序)
SELECT * FROM student ORDER BY php DESC,java DESC;
-- order by要放在sql语句的最后
  
-- 分组查询(group by)
-- 需求：查询男女分别有多少人
-- 性别  数量
-- 男    4
-- 女    5
SELECT gender AS '性别',COUNT(*) AS '数量'
FROM student GROUP BY gender;

-- 分组查询后筛选
-- 需求：address大于1
-- group by之后的条件查询使用having
SELECT address,COUNT(*)
FROM student GROUP BY address HAVING COUNT(*)>1;


SELECT UUID();


-- 增删改查分成两类：
-- 1、查询select  返回结果集：ResultSet
-- 2、更新类update、insert、delete  返回影响行数


CREATE TABLE teacher(
    id INT PRIMARY KEY AUTO_INCREMENT,
   `name` CHAR(10) NOT NULL,
    age INT COMMENT '年龄',
    address CHAR(10) DEFAULT '中国', -- 插入数据时候如果不赋值，默认值是"中国"
    UNIQUE KEY(`name`) -- 唯一键，代表这个字段不能重复
);

-- 多对多
-- 班级表
CREATE TABLE banji(
   id INT PRIMARY KEY AUTO_INCREMENT,
   `name` CHAR(10)
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
 
SELECT * FROM banji;

-- 班级表
-- REFERENCES 参考、引用
CREATE TABLE student(
   id INT PRIMARY KEY AUTO_INCREMENT,
   `name` CHAR(10),
   age INT,
   gender CHAR(1),
   banji_id INT,
   FOREIGN KEY(banji_id) REFERENCES banji(id)
);
INSERT INTO student(`name`,age,gender,banji_id) 
VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);
-- Cannot add or update a child row: a foreign key constraint fails (`java230701`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
INSERT INTO student(`name`,age,gender,banji_id) VALUES('赵六',20,'男',3);

-- 课程表
CREATE TABLE course(
   id INT PRIMARY KEY AUTO_INCREMENT,
   `name` CHAR(10),
   credit INT
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);

-- 班级课程表
CREATE TABLE banji_course(
    -- id int PRIMARY key AUTO_INCREMENT,
    banji_id INT,
    course_id INT,
    PRIMARY KEY(banji_id, course_id), -- 联合主键
    FOREIGN KEY(banji_id) REFERENCES banji(id),
    FOREIGN KEY(course_id) REFERENCES course(id)
);

INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);


-- 子查询
-- 子查询：嵌套查询，一个查询语句是另一个查询语句的条件
-- 查询班级是java1812班所有学生信息
SELECT * FROM student WHERE banji_id=2;
SELECT id FROM banji WHERE `name`='Java1812';
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='Java1812');
-- 班级是java1807班或者java1812班所有学生信息
SELECT * FROM student WHERE banji_id IN(1,2);
SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
SELECT id FROM banji WHERE `name`='Java1812' OR `name`='Java1807';
SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='Java1812' OR `name`='Java1807');

SELECT id,
       `name`,
       (SELECT COUNT(*) FROM student WHERE student.banji_id=banji.id) AS total_count
FROM banji;

-- 笛卡尔积
SELECT * FROM student,banji;

-- 查询的结果不是真实的表，但是可以当成一张表使用
SELECT * FROM student,banji WHERE student.banji_id=banji.id;

-- inner join
SELECT * 
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id;

-- id,name,age,gener,banjiName
SELECT s.id,s.name,s.age,s.gender,b.name AS banjiName
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id;

SELECT s.id,s.`name`,b.name,c.name
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
INNER JOIN banji_course AS bc
ON b.id=bc.banji_id
INNER JOIN course AS c
ON bc.course_id=c.id;

SELECT b.id,b.name,COUNT(*)
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
GROUP BY b.id;

SELECT *
FROM student AS s LEFT JOIN banji AS b
ON s.banji_id=b.id;

SELECT *
FROM student AS s RIGHT JOIN banji AS b
ON s.banji_id=b.id;

SELECT * FROM student WHERE `name` LIKE '张%';
SELECT * FROM student WHERE `name` LIKE '张_';
SELECT * FROM student WHERE `name` LIKE '%张%';

SELECT * FROM student WHERE `name` LIKE '%';



-- 1:主键为32的商品
SELECT * FROM goods WHERE goods_id=32;

-- 2:不属第3栏目的所有商品（category中id为3）
SELECT * FROM goods WHERE cat_id!=3;
-- 3:本店价格高于3000元的商品
SELECT * FROM goods WHERE market_price>3000;
-- 4:本店价格低于或等于100元的商品
SELECT * FROM goods WHERE market_price<=100;
-- 5:取出第4栏目或第11栏目的商品
SELECT * FROM goods WHERE cat_id=4 OR cat_id=11;
SELECT * FROM goods WHERE cat_id IN(4,11);
-- 6:取出100<=价格<=500的商品
SELECT * FROM goods WHERE market_price>=100 AND market_price<=500;
-- BETWEEN AND是能取到开始和结束的值，等价于>= and <=
SELECT * FROM goods WHERE market_price BETWEEN 100 AND 500;
-- 7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
SELECT * FROM goods WHERE cat_id!=3 AND cat_id!=11;
SELECT * FROM goods WHERE cat_id NOT IN(3,11);
-- 8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
-- 要适当的加括号(括号的优先级比AND和OR优先级高)，不加括号数据也正确，只是巧合，因为AND优先级要高于OR优先级，写出有歧义的语句并不能显出你多厉害
-- 任何时候使用AND和OR操作符时候，都应该加括号明确的分组操作符，不要过分依赖默认求值顺序，及时它确实如你希望的那样。使用括号没有什么坏处，它能消除歧义。
-- select * from goods where () OR ();
SELECT * FROM goods WHERE (market_price>100 AND market_price<300) 
OR (market_price>4000 AND market_price<5000);
SELECT * FROM goods WHERE market_price>100 AND market_price<300
OR market_price>4000 AND market_price<5000;
-- 9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
SELECT * FROM goods WHERE cat_id=3 AND (market_price<1000 OR market_price>3000) AND click_count>5;
-- 10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
SELECT cat_id FROM category WHERE parent_id=1;
SELECT * FROM goods WHERE cat_id IN(SELECT cat_id FROM category WHERE parent_id=1);

-- 11:取出名字以"诺基亚"开头的商品
-- like 模糊匹配
-- % 通配任意字符
-- _ 通配单一字符
SELECT * FROM goods WHERE goods_name LIKE '诺基亚%';
-- 12:取出名字为"诺基亚nxx"的手机
SELECT * FROM goods WHERE goods_name LIKE '诺基亚n__';
-- 13:取出名字不以"诺基亚"开头的商品
SELECT * FROM goods WHERE goods_name NOT LIKE '诺基亚%';
-- 14:取出第3个栏目下面价格在<1000或者>3000,并且点击量>5 "诺基亚"开头的系列商品
SELECT * FROM goods WHERE cat_id=3 AND (market_price<1000 OR market_price>3000) 
AND click_count>5 AND goods_name LIKE '诺基亚%';


-- concat('HTC','Java','PHP')
SELECT goods_id,CONCAT('HTC',SUBSTR(goods_name,4))
FROM goods WHERE goods_name LIKE '诺基亚%';

-- 15:计算指定分类(cat_id=3)下面商品的平均价格，计算评价价格时候去掉重复价格的
-- 只包含不同的值，指定DISTINCT参数
SELECT AVG(DISTINCT shop_price) AS avg_price
FROM goods WHERE cat_id=3;

-- 16:组合聚集函数，SELECT可以根据需要包含多个聚集函数
-- goods_count price_min  price_max price_avg
SELECT COUNT(*) AS goods_count,
       MIN(shop_price) AS price_min,
       MAX(shop_price) AS price_max,
       AVG(shop_price) AS price_avg
FROM goods;

-- 1、按照栏目由低到高排序，栏目内部按照价格由高到低排序
SELECT * FROM goods ORDER BY cat_id ASC, market_price DESC;

-- 2、取出价格最高的前三名商品
-- limit offset,rowcount
-- limit 偏移到哪个位置,往下数几个
SELECT * FROM goods ORDER BY market_price DESC;
SELECT * FROM goods ORDER BY market_price DESC LIMIT 0,3;
SELECT * FROM goods ORDER BY market_price DESC LIMIT 3;
-- 3、取出点击量第三名到第五名的商品
SELECT * FROM goods ORDER BY click_count DESC LIMIT 2,3;


SELECT SUM(goods_number*shop_price) FROM goods;

-- 2、查询该店每个栏目下挤压的货款
SELECT SUM(goods_number*shop_price) FROM goods GROUP BY cat_id;

-- 3、查询该店每个栏目下挤压的货款 > 100
SELECT cat_id,SUM(goods_number*shop_price) AS total_price
FROM goods GROUP BY cat_id 
HAVING total_price>100;