Java learning diary 20210525 MYSQL DDL + DML + DQL + constraints

The basic concept of the database
* Warehouse for storing and managing data
* The English word is Database, abbreviated as DB
* His storage space is very large, can store hundreds of millions of data.
*Use an agreed method to operate the database-SQL

Insert picture description here

sql introduces
sql: structured query language. In fact, it defines a rule for operating all relational databases.

通用语法规则: 
		sql语句可以单行多行书写,以分号结尾。
		可以用空格和缩进来增强语句的可读性。
		Mysql数据库的SQL语句不区分大小写,关键字建议使用大写
		单行注释 : --注释内容  #注释内容(Mysql特有)
		多行注释: /* 注释内容 */

SQL分类:
Insert picture description here

Query and create databases
Query all databases: SHOW DATABASES;
query database creation statement: SHOW CREATE DATABASE;
create database: CREATE DATABASE database name;
create database (judge if it does not exist, create): CREATE DATABASE IF NOT EXISTS database name;
create database (Specified character set): CREATE DATABASE database name CHARACTER SET character set name;

Modify, delete, use database
Modify database (modify character set): ALTER DATABASE database name CHARACTER SET character set name;
delete database: DROP DATABASE database name;
delete database (judge, delete if it exists): DROP DATABASE IF EXISTS database name;
Use database: USE database name;
often look at the currently used database: SELECT DATABASE();

Operate the data table
Query all data tables: SHOW TABLES;
query table structure: DESC table name;
query table character set: SHOW TABLE STATUS FROM library name LIKE'table name';
create data table: CREATE TABLE table name (
column name data type constraint ,

);
data type
int: integer type
double: decimal type
date: date type. Contains the year, month and day, in the format yyyy-MM-dd
datetime: date type. Contains year, month, day, hour, minute and second, in the format yyyy-MM-ddd HH: MM: SS
timestamp: timestamp type. Contains year, month, day, hour, minute, and second, in the format yyyy-MM-dd HH:mm:ss *If not assigned, the system time assignment
varchar (length) is used by default : string type.

	 修改数据表 : 
	 				修改表名 : ALTER TABLE 表名 RENAME TO 新表名;
	 				修改表的字符集 : ALTER TABLE 表名 CHARACTER SET 字符集名称;
	 				单独添加一列 : ALTER TABLE 表名 ADD 列名 数据类型;
	 				修改某列的数据类型 : ALTER TABLE 表名 MODIFY 列名 新数据类型;
	 				修改列名和数据类型: ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
	 				删除某一列 : ALTER TABLE 表名 DROP 列名;

	数据表的删除 :
				DROP TABLE 表名;
				DROP TABLE IF EXISTS 表名;

DML new table data:
add data to the specified column: INSERT INTO table name (column name 1, column name 2...) VALUES (value 1, value 2...);
add data to all columns: INSERT INTO table name VALUES (value 1 , Value 2...);
batch add data: INSERT INTO table name (column name 1, column name 2...) VALUES (value 1, value 2...), (value 1, value 2...),...;
INSERT INTO Table name VALUES (value 1, value 2....), (value 1, value 2....)...;

			列名的值的数量以及数据类型要对应。除了数字类型,其他数据类型都要加上单引号或双引号。

	
	修改和删除表数据 : 
			修改表中的数据 : UPDATE 表名 SET 列名1=值1,列名2=值2,...[WHERE 条件];
			删除表中的数据 : DELETE FROM 表名 [WHERE条件];
			注意 : 删除和修改语句中必须加条件,如果不加,所有数据都受到影响。

DQL

SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 排序
LIMIT 分页

查询全部:
		查询全部的表数据 : SELECT * FROM 表名;
		查询指定字段的表数据: SELECT 列名1,列名2,...FROM 表名;
		去除重复查询 : SELECT DISTINCT 列名1,列名2,...FROM 表名;
		计算列的值(四则运算): SELECT 列名1 运算符(+-*/) 列名2 FROM 表名;
		起别名查询 : SELECT 列名 AS 别名 FROM 表名;
Insert picture description here

DQL aggregate function query:
introduction of aggregate function: Take a column of data as a whole and perform vertical calculations.

Insert picture description here

DQL table data query-sort query

Insert picture description here
Insert picture description here
Insert picture description here

constraint

Insert picture description here

*Characteristics of
primary key constraint Primary key constraint contains two functions: non-empty and unique by default.
A table can only have one primary key.
Primary keys are generally used to uniquely identify data in the table.

Insert picture description here
Insert picture description here
Insert picture description here
Insert picture description here
Insert picture description here
Insert picture description here