Database foundation

What is a database

A database is a warehouse for storing and managing secretaries
The database we often say is: relational data management system, that is, database server

Java and database

Application: request data from the database and display the results
Database server: storing data

1, SQL overview

1. What is SQL

Structured Query Language
Role: the client uses SQL to operate the server
Standard: developed by IOS, unified operation mode for DBMS

2. sql syntax

• sql statements can be written on the current line or multiple lines, ending with semicolons
• You can use spaces and indents to enhance the readability of statements
• MYSQL is not case sensitive. It is recommended to use uppercase

3. Statement type

DDL: data definition statement, which is used to define database objects, libraries, tables, columns, etc
create, drop, alter, modify: Library and table structure
DML: data operation language, which originally defined database records:
insert,delete,update
DCL: database control language, used to define access rights and security levels
DQL: database query statement, query data
select

4. Data type

• int: shaping
• double: floating point type; double(6.2) indicates a maximum of 6 digits, of which two decimal places are required
• decimal: floating point type; No loss of precision
• char: fixed length string type
• varchar: variable length string type
• Text (clib): string text type
• data: date type, format: yyyy MM DD
• Time: time type, format: hh:mm:ss
• Timestamp: timestamp type

2, Statement

1. Storehouse

show database;
CREATE DATABASE [xxx];
user [xxx]；

//Specifies the encoding of the database
ALTER ADTABASE [xxxx] CHARACTER SET utf-8
//Delete database
DROP DTABASE [if exist] [xxx]

2. Watch

2.1 DDL definition database and table

//Build table
CREATE TABLE (IF NOT EXISTS) Table name(
Column name, column type,
Column name, column type,
...
Column name column type
)

SHOW TABLES;//Show all tables
SHOW CREATE TABLE [Table name];//View the table creation statement of the specified table
DESC [Table name];//View table structure
drop table [Table name];//Delete table

//Modify table name
ALTER TABLE [Table name];
//Add column
	ALTER TABLE [[table name] ADD(
	Column name, column type,
	...
	)
//Modify column type
ALTER TABLE [[table] MODIFY [Column type
//Modify column name
ALYER TABLE [[table] CHANGE[[Original column name] [new column name] column type
//Delete column
ALTER TABLE [[table] DROP [Column]
//Modify table name
ALTER TABLE[[original name] RENAME TO [[new table name]

TRUNCATE TABLE [Table name]; before DROP Watch. again create Table and cannot be rolled back

2.2 DML operation database

INSERT INTO [[table name](Column name 1,Column name 2,.....)
			  VALUES(Column value 1,Column value 2,....)

UPDATE[[table name] SET
			Column name 1=Column value 1,
			Column name 2=Column value 2,
			...
			where condition

DELETE FROM [[table name](where condition)

2.3 DCL data control

A project creates a user, and a project corresponds to a database
This user can only correspond to the permissions of this database

1,Create user
CREATE USER user name@IP address IDENTIFIED BY 'password';//The user logs in on the specified ip
CREATE USER user name@'%' IDENTIFIED BY 'password';//User logs in on any ip

2,Authorize users
GRANT Authority 1,Authority 2, .... ON database.* TO user name@IP address 
>Permissions, users, databases
>Assigns the user the specified permissions on the specified database
>GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE.SELECT ON mydb1.* TO user@IP address
>GRANT ALL ON mydb1.* TO user@IP address

3,Revocation of authorization
REMOVE Permission 1,....,jurisdiction n ON mydb1,* from user name@IP address

4,View permissions
SHOW GRANTS FOR user name@IP address

5,delete user
DROP USER user name@IP address

2.4 DQL data query statement

select * from  [table] 
-- Column operation
 1. Quantity type can do four operations
  select a*2 from table;
 2. String types can be used for continuous operations
 select  concat('str',a) from table;
 3. transformation nul
select IFNULL(a,0)+1000 from table; 
 4. alias
select distinct Column 1 as xxx from [table]

-- Condition control
--- Condition query
=,>,< ,in,bentween etc.
---Fuzzy query
like "_"  Underscore matches one character,% Match 0-N Any character

--Ascending order asc Descending order desc

--Aggregate function:Operation of a column
count:Total number of rows
MAX  :Maximum
MIN : minimum value
SUM:total
AVG:average

--Grouping query
group by

--limit a,b    From the first a Start checking in total b strip

3, MySQL

1. Backup and recovery

1) , export SQL script

• Database connections are exported according to import
• mysqldump -u user name - p password database name > generated script file path: mysqldump - uroot - p123 mydb1 > C: \ mydb1 sql

2) , recovery

• ① Do not log in to mysql. Under the cmd console,

• Delete the mydb1 library before creating the mydb1 library

• mysql -uroot -p123 mydb1<c:\mydb1.sql

• ② Enter the mysql database,

• source c:\mydb1.sql

2. Primary key

• Non empty, unique: when a column of a table is specified as the primary key, the column cannot be empty or duplicate

create table s(
sid char(10) primary key,
name varchar(20)
);

create table s(
sid char(10) ,
name varchar(20),
primary key(sid)
);

alter table s add primary key(sid);
//Delete primary key: alter table s drop primary key;

• Self growth of primary key: since the primary key is not empty and unique, it is often specified that the primary key type is integer. Self growth ensures non empty and unique

create table s(
sid char(10) primary key auto_increment,
name varchar(20)
);
alter table s change sid sid int auto_increment
//Delete primary key: alter table s drop primary key;

• Non empty

Set the fields when creating a table  not null

• only

Set the fields when creating a table  unique

3. Foreign key

• The foreign key must be the value of the primary key of another table
• Foreign keys can be repeated
• Foreign keys can be empty

4. Multi table query

Remove Cartesian product

1. Merge result set

In the table to be merged, the type and number of columns are the same
union remove duplicate lines
union all does not remove duplicate lines

1. join query

• Inner connection

dialect(Current database support):select * from t1,t2 where t1.xx = t2.xx;
standard: select * from t1 inner join t2 on ti.xx = t2.xx
 natural: select * from  t1 natural join t2 

• External connection

Left outer:The left table is checked whether the conditions are met or not,The right table meets the conditions,If the condition is not met null
select * from  t1 left outer join t2 on t1.xx = t2.xx
 natural: select * from  t1 natural left outer join t2 on t1.xx = t2.xx

Right outer:The right table can be checked whether the conditions are met or not,The left table meets the conditions,The condition not met is null
select * from  t1 right  outer  join t2 on t1.xx = t2.xx
 natural:select * from  t1 natural right outer join t2 on t1.xx = t2.xx

Full link:have access to union To achieve

• Subquery: there are also queries (multiple select keywords) in the query

where as a condition
from as a table

select * from t1 where t1.cid  in (select id from c ) 

select *  from (select * from t2 where id =3  ) t2 where