Basic concepts of databases

What is a database

• A DataBase, “DataBase,” is a repository for storing and managing data
• It’s essentially a file system, or a file that stores data on a computer

Why use a database

storage	advantages	disadvantages
memory	Speed is fast	Data cannot be stored permanently; it is in a temporary state
file	Data can be stored forever	It is inconvenient to use IO stream to operate files
The database	1. Data can be stored forever 2. Convenient data storage and management 3. Use a unified way to operate database (SQL)	Take up resources, some databases have to pay for it (e.g. Oracle database)

Common Databases

The database name	introduce
MySql	Open source free database Because of the free open source, simple operation characteristics, often as a small and medium-sized project database of the first choice. Mysqlstarted operating in 1996 and has since been acquired by Oracle. Mysql6.x starts charging
Oracle	Fee-based large database, the core product of Oracle Corporation. High safety
DB2	IBM’s database product, charging very large database. Often used in banking systems
SQL Server	MicroSoft a medium-sized database that MicroSoft charges for. C#,.net and other languages. However, the database can only run on Windows machines, with mediocre scalability, stability, security and performance.

SQL

The concept of SQL

• Structured Query Language (SQL), a special purpose programming Language, is a database Query and programming Language used to access data and Query, update and manage relational database systems.
• Is a unified query specification for all relational databases, different relational databases support SQL
• All relational databases can use SQL
• There are some differences in SQL between different databases

SQL General syntax

• SQL statements can be written on single or multiple lines, ending with a semicolon
• You can use whitespace and indentation to increase readability
• MySql does not use case sensitive SQL. Generally, the keyword is uppercase and the database name table name is lowercase
• Annotation way

Annotation syntax	instructions
– a space	Single-line comments
/ * * /	Multiline comment
#	Mysql-specific single-line comments

# show databases; Single-line comments
-- show databases; Single-line comments
Show databases; * /
Copy the code

The classification of the SQL

classification	instructions
Data definition language	Data Definition Language (DDL) is used to define database objects such as databases, tables, and columns.
Data manipulation language	Data Manipulation Language (DML) is used to update the records of tables in the database.
Data query language	Data Query Language (DQL) is used to Query the records of tables in the database.
Data control language	Date Control Language (DCL) is used to define database access permissions and security levels, and create users.

DDL operates the database

Creating a database

The command	instructions
Create database Specifies the database name.	Creates a database with the specified name.
Create database Database name character set;	Create a database with the specified name and character set (usually utF-8)

/* The default character set of the database is latin1 */
CREATE DATABASE db1; 

/* Specifies the database name. The character set for the database is usually utF8, which is consistent with the Java encoding */
CREATE DATABASE db1_1 CHARACTER SET utf8;
Copy the code

View/select the database

The command	instructions
Use the database	Switching databases
select database();	View the database currently in use
show databases;
Show create database Database name;	View the definition information of a database

-- Switch database from DB1 to DB1_1
USE db1_1; 

View the database currently in use
SELECT DATABASE(a);Check which databases are available in Mysql
SHOW DATABASES; 

View the definition of a database
SHOW CREATE DATABASE db1_1;
Copy the code

Modifying a Database

The command	instructions
Alter database Alter database name character set;	The character set modification operation of the database

Alter database db1 character set to UTF8
ALTER DATABASE db1 CHARACTER SET utf8; 

View the basic information of the current database and find that the encoding has changed
SHOW CREATE DATABASE db1;
Copy the code

Deleting a Database

The command	instructions
Drop Database Specifies the database name	Permanently delete a database from MySql

Delete a database
DROP DATABASE db1_1;
Copy the code

DDL manipulates data tables

Common data types of MySQL

type	describe
int	The integer
double	floating-point
varchar	String type
date	Date type: YYYY-MM-DD, year, month, day, hour, minute, second

• Note that MySQL char and vARCHar correspond to Java string types.
  
  
  • The char type is fixed length: allocates enough space based on the length of the defined string.
  • Varchar types are variable-length: only the space required for the length of the string is used

Create a table

• Syntax format:

CREATE TABLETable name (field name1Field type (length), field name2Field type (length) Note that the last column is not comma.Copy the code

• Requirement 1: Create an item classification table

-- Switch to database DB1
USE db1;

- create a table
CREATE TABLE category(
    cid INT,
    cname VARCHAR(20));Copy the code

• Requirement 2: Create test tables

Create test table
CREATE TABLE test1(
    tid INT,
    tdate DATE
);
Copy the code

• Requirement 3: Quickly create a table with the same table structure (duplicate table structure)

Create table New table name like old table name

Create table test2 with the same table structure as test1
CREATE TABLE test2 LIKE test1; 
Look at the table structure
DESC test2;
Copy the code

See the table

The command	instructions
show tables;	View all table names in the current database
Desc table name;	View the structure of the data table

View all table names in the current database
SHOW TABLES; 

-- Displays the structure of the current table
DESC category; 

View the SQL statement that creates the table
SHOW CREATE TABLE category;
Copy the code

Delete table

The command	instructions
Drop table table name;	Delete table (permanently delete a table from a database)
Drop table if exists Specifies the name of the table.	Check whether the table exists, if it exists, delete, do not delete

Drop table test1 directly
DROP TABLE test1; 

Select * from test2
DROP TABLE IF EXISTS test2;
Copy the code

Modify the table

• Modify the name of the table

Rename table Old table name to new table name

• Requirement: Change the category table to Category1

RENAME TABLE category TO category1;
Copy the code

• Modify the character set of the table

Alter table Table name character set CHARACTER set

• Requirement: Change the character set of the category table to GBK

alter table category character set gbk;
Copy the code

• ADD a column to the table with the keyword ADD

Alert Table Table name add Field name Field type

• Cdesc vARCHAR (20) cDESC vARCHar (20)

Cdesc varchar(20)
ALTER TABLE category ADD cdesc VARCHAR(20);
Copy the code

• To MODIFY the data type or length of a column in a table, use the keyword MODIFY

Alter TABLE Table name modify Field name Field type

• Need: Modify the description field of the classification table, type VARCHAR (50)

ALTER TABLE category MODIFY cdesc VARCHAR(50);
Copy the code

• Modify the column name, keyword CHANGE

Alter table name change old column name new column name type (length);

• Description vARCHar (30);

ALTER TABLE category CHANGE cdesc description VARCHAR(30);
Copy the code

• DROP column, keyword DROP

Alter table table_name drop table_name;

• Requirement: Delete the description column from the classification table

ALTER TABLE category DROP description;
Copy the code

DML manipulates data in tables

• DML in SQL is used to add, delete, or modify data in a table

Insert data

Insert into table_name (select * from table_name where table_name = 1 and table_name = 2) Values (1, 2...) ;

1) Code preparation, create a student table:

Create a student table
CREATE TABLE student( 
    sid INT, 
    sname VARCHAR(20), 
    age INT, 
    sex CHAR(1), 
    address VARCHAR(40));Copy the code

2) Add data to the student table in 3 ways

• Method 1: Insert all fields and write all field names

INSERT INTO student (sid,sname,age,sex,address) VALUES(1.'Joe'.25.'male'.'chengdu');
Copy the code

• Method 2: Insert all fields without field names

INSERT INTO student VALUES(2.'bill'.26.'male'.'chongqing');
Copy the code

• Method 3: Insert the value of the specified field

INSERT INTO student (sname) VALUES('Ming');
Copy the code

Note: 1) Values must correspond to the same number of fields and the data type must be the same. 2) Values must be within the specified length of the field. 3) VARCHAR, CHAR, and date values must be enclosed in single or double quotation marks. 5) If the value of a specified field is inserted, the column name must be written

Change the data

Update table_name set table_name = table_name [where table_name = table_name]

1) Change all genders to female without conditions (caution!!)

UPDATE student SET sex = 'woman';
Copy the code

2) With conditional modification, change the gender of the student whose SID is 3 to male

UPDATE student SET sex = 'male' WHERE sid = 3;
Copy the code

3) Modify multiple columns at once, change student sid to 2, age to 20, address to Beijing

UPDATE student SET age = 20,address = 'Beijing' WHERE sid = 2;
Copy the code

Delete the data

Delete from table_name delete from table_name

1) Delete the data whose SID is 1

DELETE FROM student WHERE sid = 1;
Copy the code

2) Delete all data

DELETE FROM student;
Copy the code

3) If you want to delete all data in a table, there are two ways to do it

1. Delete from table name; Not recommended. Delete as many records as possible. Low efficiency
2. Truncate TABLE Table name: Recommended. Delete the entire table and then create an identical one. High efficiency

truncate table student;
Copy the code

DQL Queries data in the table

To prepare data

Create employee table
CREATE TABLE emp(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1),
    salary DOUBLE,
    hire_date DATE,
    dept_name VARCHAR(20));# add data
INSERT INTO emp VALUES(1.'Joe'.'male'.7200.'2013-02-04'.'Teaching Department');
INSERT INTO emp VALUES(2.'bill'.'male'.3600.'2010-12-02'.'Teaching Department');
INSERT INTO emp VALUES(3.'Cathy'.'male'.9000.'2008-08-08'.'Teaching Department');
INSERT INTO emp VALUES(4.'little red'.'woman'.5000.'2015-10-07'.'Marketing Department');
INSERT INTO emp VALUES(5.'cui flower'.'woman'.5000.'2011-03-14'.'Marketing Department');
INSERT INTO emp VALUES(6.'chou-heung'.'woman'.200.'2000-03-14'.'Marketing Department');
INSERT INTO emp VALUES(7.'pomegranate'.'woman'.10000.'2019-10-07'.'Finance Department');
INSERT INTO emp VALUES(8.'da ji'.'woman'.3500.'2011-09-14'.'Finance Department');
INSERT INTO emp VALUES(9.'Daisy'.'male'.20000.'2000-03-04'.NULL);
INSERT INTO emp VALUES(10.'Ming'.'male'.10.'2020-03-14'.'Finance Department');
INSERT INTO emp VALUES(11.Bugs Bunny.'woman'.300.'2010-03-14'.'Finance Department');
Copy the code

A simple query

• Queries do not modify the data in the database. It’s just a way of displaying data SELECT

Select column name from table name

Requirement 1: Query all data in the EMP

SELECT * FROM emp; Use * to indicate all columns
Copy the code

Requirement 2: Query all records in the EMP table, showing only the ID and name fields

SELECT eid,ename FROM emp;
Copy the code

Requirement 3: Query all employee information and change the column name to Chinese

• Alias query, using the keyword as

# alias the column using the AS keyword
SELECT 
    eid AS 'number',
    ename AS 'name',
    sex AS 'gender',
    salary AS 'salaries',
    hire_date 'Entry Time'.-- AS can be omitted
    dept_name 'Department Name'
FROM emp;
Copy the code

Requirement 4: There are several departments for the query

• Use the de-duplicate keyword distinct

- Use the distinct keyword to remove duplicate department information
SELECT DISTINCT dept_name FROM emp;
Copy the code

Requirement 5: Display all employees’ salary +1000 yuan

• Operation query (query results participate in operation)

SELECT ename , salary + 1000 FROM emp;
Copy the code

Conditions of the query

• If no criteria are set in the query statement, all rows are queried. In actual applications, you must specify query criteria to filter records

selectThe column namefromThe name of the tablewhereConditional expressionSelect * from table; select * from table; select * from table
Copy the code

The operator

1. Comparison operator

The operator	instructions
> < <= >= = <> !=	Greater than, less than, greater than (less than) equal to, not equal to
BETWEEN … AND…	A value displayed in a range For example, Between 2000 and 10000: Between 2000 and 10000
IN (set)	Sets represent multiple values separated by commas, for example: Name in (Wukong, Eight Precepts) Each data in in is treated as a condition and is displayed as long as the condition is met
LIKE ‘% a %’	Fuzzy query
IS NULL	Query a column for a value that is NULL, note: cannot write = NULL

2. Logical operator

The operator	instructions
And &&	Multiple conditions hold
Or ||	Any one of these conditions is true
Not	If it’s not true, take the opposite.

Requirements: 1

Select * from employee whose name is Cuihua
Select * from employee where salary = 5000
Select * from employees whose salary is not 5000
Select * from employees whose salary is more than 6000 yuan
Select * from all employees whose salary is between 5000 and 10000
Select * from employees where salary = 3600, salary = 7200, salary = 20000
Copy the code

Code implementation

Select * from employee whose name is Cuihua
SELECT * FROM emp WHERE ename = 'cui flower';

Select * from employee where salary = 5000
SELECT * FROM emp WHERE salary = 5000;

Select * from employees whose salary is not 5000
SELECT * FROM emp WHEREsalary ! =5000;
SELECT * FROM emp WHERE salary <> 5000;

Select * from employees whose salary is more than 6000 yuan
SELECT * FROM emp WHERE salary > 6000;

Select * from all employees whose salary is between 5000 and 10000
SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000;

Select * from employees where salary = 3600, salary = 7200, salary = 20000
SELECT * FROM emp WHERE salary = 3600 OR salary = 7200 OR salary = 20000;
SELECT * FROM emp WHERE salary IN(3600.7200.20000);
Copy the code

Fuzzy query wildcard

The wildcard	instructions
%	Matches any number of strings
_	Matches a character

Requirement 2:

Select * from employee where 'small' = 'small'
Select * from all employees whose names start with 'king
Select * from employee where id = '8'
Select * from employee where no department exists
Select * from department where employee information exists
Copy the code

Select * from employee where 'small' = 'small'
SELECT * FROM emp WHERE ename LIKE 'small % %';

Select * from all employees whose names start with 'king
SELECT * FROM emp WHERE ename LIKE 'the king %';

Select * from employee where id = '8'
SELECT * FROM emp WHERE ename LIKE Eighty percent '_';

Select * from employee where no department exists
SELECT * FROM emp WHERE dept_name IS NULL;

Select * from department where employee information exists
SELECT * FROM emp WHERE dept_name IS NOT NULL;
Copy the code