[5-2] database foundation

Keywords: Java Database Big Data SQL

Database foundation

  1. Database: a collection that stores, maintains, and manages data
  2. DataBase Management System (DBMS)

Common database management system

Oracle

  1. Relational type

MySQL

  1. Relational type

DB2

Microsoft SQL Server

Three paradigms

  1. First normal form (1NF): no duplicate columns;
  2. The second normal form (2NF): attributes are completely dependent on the primary key [eliminating some sub function dependencies]. The data of each row cannot be repeated, that is, each instance or row can be uniquely distinguished. In order to distinguish, a column is added to the table to store the unique identification of the instance. This unique attribute column is called the primary key, or primary key, or primary code
  3. The third normal form (3NF): attributes do not depend on other non primary attributes [eliminate transitive dependency].
    If two tables store information, table A has A name and table B does not have A name

Relationship is essentially a two-dimensional table, in which each row is a tuple and each column is an attribute

install

  1. Start the MySQL service with the command net start mysql8.
    Stop the service with the command net stop mysql8.
  2. ​

SQL statement

summary

  1. Structured query language
  2. CRUD(Create, Read, Update, and Delete)
  3. ​

classification

    • DDL (Data Definition Language): Data Definition Language, which is used to define database objects: libraries, tables, columns, etc.
    • DML (data management language): data operation language, which is used to define the addition, deletion and modification of database records (data).
    • DCL (Data Control Language): Data Control Language used to define access rights and security levels.
    • DQL (Data Query Language): a Data Query Language used to query records (data).

  2. Ends with a semicolon and is not case sensitive

DDL operation database

  1. establish
    The CREATE DATABASE statement is used to create a new database:
    Coding method: gb2312,utf-8,gbk,iso-8859-1
//create database database name
CREATE DATABASE mydb1;
//create database database name character set encoding method
CREATE DATABASE mydb2 character SET GBK;  
//create database database name set encoding method collate collation
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

  1. view the database
    show databases; View all databases in the current database server
    show create database database name; View the definition information of the mydb2 database created earlier

  2. modify the database

    alter database database name character set encoding method

    ALTER DATABASE mydb2 character SET utf8; Check the database in the server and modify the character set of mydb2 to utf8;

  3. Delete database
    drop database database name

  4. Other statements
    Select database(); View the database currently in use

USE mydb2; Switch database

DDL operation table

  1. Create a new table

    CREATE TABLE Table name(
Column name 1 data type [constraint],
Column name 2 data type [constraint],
Listing n data type [constraint]
);
//Note: the table name and column name are user-defined. Multiple columns are separated by commas. The comma of the last column cannot be written

    Common data types:
int: integer
double: Floating point, for example double(5,2)Indicates a maximum of 5 digits, of which there must be 2 decimal places, that is, the maximum value is
999.99；Rounding is supported by default
char: Fixed length string type; char(10) 'aaa ' 10 places
varchar: Variable length string type; varchar(10) 'aaa' Third place
text: String type, such as novel information;
blob: Byte type, save file information(Video, audio, pictures)；
date: Date type, format: yyyy-MM-dd；
time: Time type, format: hh:mm:ss
timestamp: Timestamp type yyyy-MM-dd hh:mm:ss It will be assigned automatically
datetime:Date time type yyyy-MM-dd hh:mm:ss

  2. Table operation

    //delete
drop table Table name;
//Show all tables
show tables;
//View the field information of the table
desc Table name;
//Add column
alter table Table name add New column name new data type
//Modify the column to a length of 60
alter table Table name change Old column name new column name new data type(60);
//Change the column name to username
ALTER TABLE user CHANGE name username varchar(100);
//Delete the image column. Only one column can be deleted at a time.
alter table Table name drop Listing
//Modify table name
alter table Old table name rename New table name;
//View table creation details
show create table Table name;
//Modify the character set of the table
alter table Table name character set Coding mode

DML operation

  1. Addition, deletion and modification;

  2. String type and date type should be enclosed in single quotation marks;

  3. //insert
insert into Table name(Listing) values(Data value);
// -Note:
//one 	 Multiple columns and column values are separated by commas 
//two 	 The column name should correspond to the column value one by one. Single quotation marks should be added around the non numeric column value. Empty value: null  
//three 	 You can omit column names - > when adding data to all columns, in order

//Add multiple rows at the same time
insert into Table name(Listing) values(First row of data),(Second row data),(),();
//Query all data
select * from Table name
//Modify (update) operation
UPDATE Table name SET Column name 1=Column value 1,Column name 2=Column value 2 ... WHERE Listing=value
//Delete operation
DELETE from Table name[ WHERE Listing=Value]
- DELETE Delete the data in the table, and the table structure is still in use;The deleted data can be retrieved
- TRUNCATE Delete is to delete the table directly DROP Drop, and then create the same new table.
- Deleted data cannot be retrieved. Execution speed ratio DELETE Come on.
//Null condition: column name is null (no assignment) or column name = '' (empty string)

//Get current system time:
select now();

    insert into manager values(1, 'Prince', 18, 'male', '123', 'Beijing', '110'), (2, 'princess', 20,' female ',' 456 ',' Shanghai ',' 220 '), (3,' Prince ', 23,' male ',' 789 ',' Nanjing ',' 330 ');

    select*from manager;

DCL

  1. Access rights and security levels;

  2. //Create user
create user user name@appoint ip identified by password;// Only the specified IP can log in (user name without quotation marks and password with quotation marks)
create user user name@'% ' identified by password //Any IP can log in

//User authorization
grant Authority 1,Authority 2,........,jurisdiction n on Database name.* to user name@IP; Grant the specified database permissions to the specified user
//For example: (user name in quotation marks)
grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';

grant all on *.* to 'test456'@'127.0.0.1'//Grant all database permissions to the specified user

//User authority query
show grants for user name@IP;(User name (quoted)

//Revoke user rights
revoke Authority 1,Authority 2,........,jurisdiction n on Database name.* from user name@IP;

//Delete user:
drop user user name@IP;

DQL

  1. Query record (data)

  2. The returned result set is a virtual table

  3. Query keyword: SELECT

  4. SELECT Listing FROM Table name[ WHERE --> BROUP BY-->HAVING--> ORDER BY]
* Represents all columns

  5. //Query all columns
SELECT * FROM stu;
//Query specified column
SELECT sid, sname, age FROM stu;

//Condition query
 give WHERE Clause, in WHERE The following operators and keywords can be used in clause:
=,!=,<>,<,<=,>,>=; BETWEEN...AND； IN(set)； IS NULL； AND；OR； NOT；
//Range query
 Listing in (Column value 1,Column value 2)
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');
SELECT * FROM tab_student WHERE sid NOT IN('S1001','S1002','S_1003');
//Query records with null age
SELECT * FROM stu WHERE age IS NULL;
//Interval query
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;//With critical values

//Query the records of non male students
SELECT * FROM stu WHERE gender!='male';
//or
SELECT * FROM stu WHERE NOT gender='male';

  6. Fuzzy query

    //Use keyword LIKE
 Listing like 'expression' //The expression must be a string

//Wildcard:
_(Underline): Any character
%: Any 0~n Characters,'Zhang%'

//Query the student record whose name is composed of five letters and the fifth letter is "i"
SELECT * FROM stu  WHERE sname LIKE '____i';
//)Query student record 'z%' whose name starts with "z"
//The second letter is "I" '_i%'
//Contains' a ''% a% '

  7. Field control query

    //(1) 	 Remove duplicate records
SELECT DISTINCT sal FROM emp;
//(2) 	 View the sum of sal and comm columns
SELECT *,sal+comm FROM emp;
//	Convert NULL to a value of 0 (anything added to NULL will still be NULL)
SELECT *,sal+IFNULL(comm,0) FROM emp;
//(3) 	 Add the alias ass(sal+IFNULL(comm,0) to the column name, which is not beautiful)
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;   //AS can be omitted

  8. sort

    //Sort and query
select *from stu order by Listing asc((default)/desc;

//Multi column sorting: when the values of the previous columns are the same, the subsequent column values will be sorted
SELECT * FROM emp ORDER BY sal DESC,empno ASC;

  9. Aggregate function

    A function used to do vertical operations

    COUNT(Listing): Statistics specified column is not NULL Number of record lines;
SELECT COUNT(*) FROM emp;//All columns

  10. Grouping query

    • GROUP BY
    //Query the department number and salary of each department:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
    • HAVING clause
    SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

    The difference between having and where:
    1.having is to filter data after grouping, and where is to filter data before grouping
    2. Grouping function (statistical function) can be used after having, but not after where

  11. LIMIT

    Limit the starting row of the query result and the total number of rows

    Subscript starts at 0

    //	Query 5 rows of records, starting from 0
 Note that the starting line starts from 0, that is, the first line!
2.Query 10 rows of records, starting from 3
3.8.1 Paging query
 If there are 10 records on one page, how should I check the records on page 3?
l The starting behavior of the first page record is 0, and a total of 10 rows are queried; limit 0,10
l On the second page, the record start behavior is 10, and a total of 10 lines are queried; limit 10,10
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
SELECT * FROM emp LIMIT 0, 5;

##Development tools to achieve database operation

Navicat，SQLyog

Multi table associated query?

Posted by sandrob57 on Tue, 21 Sep 2021 16:14:57 -0700

Hot Keywords

©2024 Programmer Group