数据库语句（练习）

BX_Jobs

于 2022-09-02 17:32:04 发布

阅读量245

点赞数

分类专栏： java 文章标签：数据库 sql java

本文链接：https://blog.csdn.net/BX_Jobs/article/details/126667775

版权

java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了SQL的基本操作，包括创建学生表、修改表结构、更新与删除数据，以及查询操作。通过实例展示了如何进行数据的增删改查，以及聚合函数的使用，最后讨论了数据的分组和排序。这些内容对于理解数据库管理和SQL操作至关重要。

摘要由CSDN通过智能技术生成

CREATE TABLE student(
				id INT PRIMARY KEY AUTO_INCREMENT,-- 主键自动增长
				NAME VARCHAR(30) UNIQUE, -- 唯一约束
				dender CHAR(1) NOT NULL
);

SHOW TABLES;

DESC student;

ALTER TABLE student Add grade VARCHAR(20) NOT NULL;

ALTER TABLE student MODIFY gender INT NOT NULL DEFAULT 1;

ALTER TABLE student CHANGE grade class VARCHAR(20) NOT NULL;

ALTER TABLE student DROP class;

RENAME TABLE student to teacher;

UPDATE product SET price=5000;

UPDATE product SET price=18000 WHERE pname='苹果手机';

UPDATE product SET price=17000,num=5 WHERE pname="苹果手机";

UPDATE product SET price=price+2 WHERE pname='华为手机';

-- 删除数据
DELETE FROM product WHERE pname='苹果手机';

DELETE FROM product WHERE price >5001;

DELETE FROM product;

TRUNCATE TABLE product;

SELECT * FROM product ;
SELECT pname,price,num,pid FROM product ;
-- 去重查询 
SELECT DISTINCT pname,price  FROM product;


-- 给字段取别名 
SELECT p.pid,pname AS '商品名',price AS '商品价格',num AS '商品库存' FROM product p;

SELECT p.pid,pname AS '商品名',price+10 AS 'price',num AS '商品库存' FROM product p;


SELECT * FROM product where price>10000; 
SELECT * FROM product  where pid=1; 
SELECT * FROM product WHERE pid<>1;-- 不等于
SELECT * FROM product WHERE price BETWEEN 3000 AND 6000;

SELECT * FROM product WHERE pid IN (1,3,4);
SELECT * FROM product WHERE pname LIKE '解%';

SELECT * FROM product WHERE price>3000 AND num>30;
SELECT * FROM product WHERE pid=1 OR price>3000;


SELECT * FROM student ORDER BY score DESC;
SELECT * FROM student WHERE sex='女' ORDER BY score DESC,age DESC;

SELECT MAX(score) FROM student;
SELECT MIN(score) FROM student;
SELECT AVG(score) FROM student;
SELECT AVG(IFNULL(score,0)) FROM student;
SELECT COUNT(sid) FROM student;

SELECT SUM(score) FROM student;

SELECT sex '性别',COUNT(sid) '总人数' FROM student GROUP BY sex;

SELECT sex '性别',AVG(score) '平均分' FROM student GROUP BY sex;

SELECT sex '性别',SUM(score) '总分' FROM student GROUP BY sex;