2. Introduction to database

2.1 introduce
Save data to memory : advantage : 1) Reading and writing are very fast shortcoming : 1) Data loss due to program shutdown Save data to file : advantage : 1) Data can be stored permanently shortcoming :
1) Frequently IO operation , Low efficiency ! 2) Inconvenient data management . For example, to query a certain data, you need to read it all , Rematch . Data saving to database software : advantage : 1) The data is preserved forever
2) Data management is very convenient .( For example, the query is very fast and convenient ) Data can be said to be the soul of an enterprise !!
2.2 What is database software
database , Commonly known as data warehouse . Software for data management ( Or procedure ).
2.3 Database software on the market
Oracle, Oracle products . The most popular and widely used database software at present . and java Language compatibility is very good . Suitable for medium and large scale , CUHK applications . SQL Server:
It's a Microsoft product .window The platform is widely used . and c#,net Platform compatibility is very good . DB2: IBM Products of the company .IBM The server --> UNIX -> DB2- >
Websphere MySQL: Products of open source organizations . Oracle products . Free Admission !!! and java Language compatibility is very good ! Suitable for small and medium enterprises , Medium and small application relational database . MongoDB:
Non relational database . Learn first mysql, Learn later oracle
2.4 MySQL introduction
1) reach mysql Download from official website . 2) install mysql Software 3) use Verify success open cmd -> input mysql -u root -p enter -> Input password
enter C:\Users\APPle>mysql -u root -p Enter password: **** Welcome to the MySQL
monitor. Commandsend with ; or \g. Your MySQL connection id is 2 Server version:
5.5.40 MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its
affiliates. All rights reserved. Oracleis a registered trademark of Oracle
Corporationand/or its affiliates. Other names may be trademarks of their
respective owners. Type 'help;'or '\h' for help. Type '\c' to clear the current
input statement. mysql>
3 MySQL database

3.1 mysql Data storage structure
First database , Again , There are more data
4 Database management

4.1 Query all databases
mysql> show databases; +--------------------+ | Database |
+--------------------+ | information_schema | -- mysql metadata , Basic data | mysql |
--mysql Configuration database , It contains user information .( User name and password , Authority management ) | performance_schema |
--mysql Running data of database software , log information , performance data | test | -- Test database . Empty +--------------------+ 4 rows in
set (0.00 sec)
4.2 Create database
mysql> create database day15 -- Specify the default character set to create the database -> default character set utf8 -> ;
Query OK, 1 row affected (0.00 sec)
4.3 View the default character set for the database
mysql> show create database day15;
+----------+----------------------------------------------------------------+ |
Database | Create Database |
+----------+----------------------------------------------------------------+ |
day15 | CREATE DATABASE `day15` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+ 1
row in set (0.00 sec)
4.4 Delete database
mysql> drop database day15; Query OK, 0 rows affected (0.01 sec)
4.5 modify the database
mysql> alter database day15 default character set gbk; Query OK, 1 row
affected (0.00 sec)
5 Table management
Select database
5.1 View all tables
mysql> show tables; +-----------------+ | Tables_in_day15 | +-----------------+
| student | +-----------------+ 1 row in set (0.00 sec)
5.2 Create table
mysql> create table student( -> sid int, -> sname varchar(20), -> sage int -
> );Query OK, 0 rows affected (0.01 sec)
5.3 View table structure
mysql> desc student; +-------+-------------+------+-----+---------+-------+ |
Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+ | sid | int(11) | YES |
| NULL | | | sname | varchar(20) | YES | | NULL | || sage | int(11) | YES | |
NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set
(0.01 sec)
5.4 Delete table
mysql> drop table student; Query OK, 0 rows affected (0.01 sec)
5.5 Modification table

1) Add field
mysql> alter table student add column sgender varchar(2); Query OK, 0 rows
affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
2) Delete field
mysql> alter table student drop column sgender; Query OK, 0 rows affected (0.03
sec) Records: 0 Duplicates: 0 Warnings: 0
3) Modify field type
mysql> alter table student modify column remark varchar(100); Query OK, 0 rows
affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
4) Modify field name
mysql> alter table student change column sgender gender varchar(2); Query OK, 0
rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
5) Modify table name
mysql> alter table student rename to teacher; Query OK, 0 rows affected (0.01
sec)
6 Add, delete and modify data

– One , Add, delete and modify data * —

6.1.1 Add data

– Insert all fields . Be sure to insert them in order
INSERT INTO student VALUES(1,' Zhang San ',' male ',20);
– Note that you cannot have less or more field values
INSERT INTO student VALUES(2,' Li Si ',' female ');
– Insert partial fields
INSERT INTO student(id,NAME) VALUES(2,' Li Si ');
6.1.2 Modify data

– Modify all data ( Suggest less use )
UPDATE student SET gender=' female ';
– Conditional modification ( Recommended )
UPDATE student SET gender=' male ' WHERE id=1;
– modify id by 1 Students of , Change gender to male
– Modify multiple fields , be careful : SET Field name = value , Field name = value ,….
UPDATE student SET gender=' male ',age=30 WHERE id=2;
6.1.3 Delete data

– Delete all data ( Suggest less use )
DELETE FROM student;
– Conditional deletion ( Recommended )
DELETE FROM student WHERE id=2;
– Another way
– delete from: All tables can be deleted
1) It can be deleted with conditions
2) Only table data can be deleted , Cannot drop constraints on table
3) use delete from Deleted data can be rolled back ( affair )

– truncate table: All tables can be deleted
1) Cannot delete with condition
2) That is, the data of the table can be deleted , You can also remove constraints from a table
3) use truncate table Deleted data cannot be rolled back
TRUNCATE TABLE student;

7 Query data ( a key )

7.1 Query all columns

7.2.1 Query all columns
SELECT * FROM student;
7.2 Query the specified column

7.2.2 Query the specified column
SELECT id,NAME,gender FROM student;
7.3 Add constant columns when querying

7.2.4 Add constant columns when querying

– demand : In the query student Add a class column to the table , The content is “java Employment class ”
SELECT id,NAME,gender,age,'java Employment class ' AS ' grade ' FROM student;
7.4 Merge columns on query

7.2.5 Merge columns on query

– demand : Query each student's servlet and jsp Total score of
SELECT id,NAME,(servlet+jsp) AS ' Total score ' FROM student;
– be careful : Merge columns can only merge fields of numeric type
SELECT id,(NAME+servlet) FROM student;
7.5 Remove duplicate records when querying

7.2.6 Remove duplicate records when querying (DISTINCT)

– demand : Query student's gender male female
SELECT DISTINCT gender FROM student;
– Another grammar
SELECT DISTINCT(gender) FROM student;
– demand : Query the student's district
SELECT DISTINCT address FROM student;
7.6 Condition query

7.2.7 Condition query (where)

7.2.7.1 Logical condition : and( And ) or( or )

– demand : query id by 2, A student whose name is Li Si
SELECT * FROM student WHERE id=2 AND NAME=' Li Si '; -- intersection
– demand : query id by 2, Or the student whose name is Zhang San
SELECT * FROM student WHERE id=2 OR NAME=' Zhang San '; -- Union
7.2.7.2 Comparison conditions : > < >= <= = <>( Not equal to ) between and ( Equivalent to >= And <=)

– demand : query servlet Score greater than 70 Students with a high score
SELECT * FROM student WHERE servlet>70;
– demand : query jsp Score greater than or equal to 75, And less than or equal to 90 Students with a high score
SELECT * FROM student WHERE jsp>=75 AND jsp<=90;
– Another grammar
SELECT * FROM student WHERE jsp BETWEEN 75 AND 90; -- ( Before and after ) SELECT * FROM
studentWHERE gender<>' male ';
7.2.7.3 Null condition (null Empty string ): is null / is not null / =” / <>”
– demand : Query students whose address is empty ( include null And empty string )
– null vs Empty string
– null: Indicates that there is no value
– Empty string : Valuable !
– judge null
SELECT * FROM student WHERE address IS NULL ;
– Judge empty string
SELECT * FROM student WHERE address=''; SELECT * FROM student WHERE address IS
NULL OR address=''; -- ( include null And empty string )
– demand : Search for students with addresses ( barring null And empty string )
SELECT * FROM student WHERE address IS NOT NULL AND address<>'';
7.2.7.4 Fuzzy condition : like
– The following substitution marks are usually used :
– % : Represents any character
– _ : Represents a character
– demand : Search last name ‘ Zhang ’ Students of
SELECT * FROM student WHERE NAME LIKE ' Plum %';
– demand : Search last name ‘ Plum ’, A student whose name is only two words
SELECT * FROM student WHERE NAME LIKE ' Plum _';
7.7 Aggregate query

7.2.8 Aggregate query ( Queries using aggregate functions )

– Common aggregate functions : sum() avg() max() min() count()
– demand : Query student's servlet Total score of (sum() : Summation function )
SELECT SUM(servlet) AS 'servlet Total score of ' FROM student;
– demand : Query student's servlet Average score of
SELECT AVG(servlet) AS 'servlet Average score of ' FROM student;
– demand : Query current servlet Highest score
SELECT MAX(servlet) AS ' Highest score ' FROM student;
– demand : Lowest score of query
SELECT MIN(servlet) AS ' Lowest score ' FROM student;
– demand : How many students are there now (count( field ))
SELECT COUNT(*) FROM student; SELECT COUNT(id) FROM student;
– be careful :count() The number of function statistics does not contain null Data for
– use count Number of records in statistical table , To use do not include null The field of the value
SELECT COUNT(age) FROM student;
7.8 Paging query

7.2.9 Paging query (limit Start line , Look up a few lines )

– Start line from 0 start
– paging : Current page How many items are displayed on each page
– Paging query of the current page data sql: SELECT * FROM student LIMIT ( Current page -1)* How many items are displayed on each page , How many items are displayed on each page ;

– demand : Query No 1,2 Records ( The first 1 Page data )
SELECT * FROM student LIMIT 0,2;
– Query No 3,4 Records ( The first 2 Page data )
SELECT * FROM student LIMIT 2,2;
– Query No 5,6 Records ( The first 3 Page data )
SELECT * FROM student LIMIT 4,2;
– Query No 7,8 Records ( No record will not be displayed )
SELECT * FROM student LIMIT 6,2;
7.9 Query sorting

– 2.10 Query sorting (order by )
– grammar :order by field asc/desc
– asc: order , positive sequence . numerical value : Increasing , letter : Natural order (a-z)
– desc: Reverse order , Reverse order . numerical value : Decline , letter : Natural reverse order (z-a)

– By default , Sort by insertion order
SELECT * FROM student;
– demand : according to id Ordering
SELECT * FROM student ORDER BY id ASC; SELECT * FROM student ORDER BY id; --
Default positive order SELECT * FROM student ORDER BY id DESC;-- Reverse order
– be careful : Multiple sorting conditions
– demand : according to servlet positive sequence , according to jsp Reverse order of
SELECT * FROM student ORDER BY servlet ASC,jsp DESC;
7.10 Group query

– 2.11 Group query (group by)
– demand : Inquire about the number of men and women
– Expected results :
– male 3
— female 2
– 1) Group students by gender (GROUP BY gender)
– 2) Count the number of people in each group (COUNT(*))
SELECT gender,COUNT(*) FROM student GROUP BY gender;
7.11 Filter after group query

7.2.12 Filter after group query

– demand : The total number of queries is greater than 2 The gender of
– 1) Inquire about the number of men and women
– 2) The number of people screened out is greater than 2 Record of (having)
— be careful : Conditional use before grouping where keyword , Conditional use before grouping having keyword
SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;

Technology
©2019-2020 Toolsou All rights reserved,
SpringBoot JpaRepository Database addition, deletion, modification and query ( Essence 2020 year 6 month 2 Daily update ) TypeScript Function explanation Thread.getContextClassLoader And Thread.getClassLoader() difference ( Essence )2020 year 7 month 15 day Wechat applet import and include difference be based on RK3399 PWM Drive development stay Vue Use in Web Worker experiment 11-1-6 Output string at specified position (20 branch )Vue Transfer parameters and receiving of page Jump ( Essence )2020 year 6 month 26 day C# Class library Log help class [work] python read txt Last line of file