Java Development Series-mysql

Overview

Currently the company's common database MySQL, Oracle. MySQL belongs 关系型数据库 to, and Oracle belongs to, a non-relational database (direct storage object). The following is a list of some common databases.

• Mysql Oracle Open Source Database Community Edition Free Commercial edition is charged
• Oracle Oracle Large, toll-free database
• DB2 IBM's large-scale database is typically used for banking system features its hardware is relatively stable
• SQL Server Microsoft medium fee for database C + + developers using

MySQL Statement base statement

-- 创建数据库CREATE DATABASE mydb;-- 创建表CREATE TABLE USER (    uid INT PRIMARY KEY AUTO_INCREMENT,    username VARCHAR(20) DEFAULT NULL);-- 插入数据INSERT INTO USER VALUES(NULL, 'tom');-- 更新数据UPDATE USER SET username = 'rose' WHERE id = 1;-- 删除表DROP TABLE USER;-- 删除表中的所有数据DELETE FROM USER;-- 查询表中所有数据SELECT *FROM USER;

Aggregation functions

对一列进行计算 返回值是一个,忽略null值, sum(), avg(), max(), min(), count();

-- .获得所有商品的价格的总和SELECT SUM(price) AS totalPrice FROM product;-- 获得商品表中价格的平均数：SELECT ROUND(AVG(price), 2) AS vagprice FROM product;-- 获得商品表中有多少条记录SELECT COUNT(*) FROM product;

Group queries

-- 根据cno字段分组，分组后统计商品的个数SELECT cno, SUM(price)FROM product GROUP BY cno;-- 根据cno分组，分组统计每组商品的总数量，并且总数量> 200;SELECT cno, SUM(pnum) FROM product GROUP BY cno HAVING SUM(pnum) > 200;-- 获得商品表中有多少条记录：select count(*) from products;

Note: The difference between having and where 1. 1.where is to filter the data before grouping, having is to filter the data after grouping 2. You cannot use an aggregate function after a where, having

Java Development Series-mysql