JAVAWEB-NOTE01

Cool breeze AAA 2022-02-13 07:40:28 阅读数:420

javaweb-note01 javaweb note

unit01-MySQL

1.1 Database Overview

1.1.1 What is a database ?

Concept : The so-called database refers to the warehouse for storing and managing data .

Extended content 1: What are the classifications of the database ?( understand )

 In the early : Hierarchical database 、 Network database ( Deprecated )
Now? : Relational database 、 Non relational database
summary : A database stores data more directly than a file , More secure , More reliable , Most of them are relational databases .

Extended content 2: Precautions for the use of some tools .

 pdf Can you read or edit
.md file Can edit , Need to install typoa Software

Extended content 3:Dos Basic precautions for Windows .

 Outside Dos Window paste : Copy first stay DOS Right click the blank space of the window
Dos Copy and paste the window outside :
win7 Mark... First ( Right click --> Mark -- Then right-click in the blank space , Where it's copied ctrl v
win8 win 10 No marking
stay DOS Wrong data written in the window cannot be returned to the previous line , You can only go back to . It is suggested to write it out and copy it .
Want to use Dos Window previous commands : Use the up and down arrows to switch ( It can only be the same window )

Extended content 4:typora Black screen solution

 Right click on the typora.exe,“ attribute ”——“ Compatibility ”, Check “ Run this program in compatibility mode ”,
Choose... From the drop-down menu “Windows Vista (Service Pack 1)”, OK and then reopen typora.

1.1.2 What is a relational database ?

The bottom layer is a two-dimensional table ( With rows and columns ) The database that stores data in the form of is a relational database

stu- Student list

Student number full name Age
1001 Liu peixia 35
1002 Chen Zishu 18

Extended content 2: What are the common relational databases ?( understand )

  • Sql Server: Microsoft provides , charge , It is suitable for some medium or large projects , stay java The proportion of using in is not high (.NET It's used more in )

  • Oracle: Oracle offers , charge , It is suitable for some large or super large projects , stay java The proportion of the use of is very high

  • mysql: The Swedish MySQLAB The company offers , Free and open source , It is suitable for some small or medium-sized projects , stay Java The proportion of the use of is relatively high ( Small and light ) By Oracle After the acquisition, it has now become a charge .
    mariadb In fact, that is MySQL A branch of , Usage and MySQL Exactly the same as . free

  • DB2:IBM The company offers , charge , In some banks 、 It is widely used in finance and other industries . stay java The proportion of the use of the Internet is not high .

  • Sqlite: Mini database , Embedded devices ( Android 、 Apple mobile phone 、pad)

1.1.3 Database related concepts

1)、 What is a database server

A database server is a piece of software ( such as mysql Software ) Install the database software on the computer , At present, the computer is a database server . You can provide access to data services .

Multiple databases can be created in one database server (dataBases), Each database is a separate Repository .

2)、 What is a database

A database is a warehouse for storing and managing data , Usually , All data in a website will be stored in a database . for example :

jd.com db_jd( database )
taobao.com db_taobao( database )
...

3)、 What is a watch

Multiple tables can be created in one database , Each table is used to store A kind of information ( database ), for example :

jd.com User data in tb_user( surface )
jd.com Commodity data in tb_product( surface )
jd.com Order data in tb_order( surface )

4)、 What table records ·

A table can contain multiple rows of table records , Each row of table records is used to store a specific data >

Student number full name Age
1001 Liu peixia 35
1002 Chen Zishu 18
... ... ...

1.1.4 What is? SQL Language ?

SQL Is a common language for operating relational databases ( namely Use SQL It can operate all relational databases ).

Use SQL You can manipulate the database 、 surface 、 Table record

(1) Create database 、 Delete database 、 modify the database 、 Query the database

(2) Create table 、 Delete table 、 Modify table 、 Query table

(3) Add table record 、 Delete table record 、 Modification table record 、 Query table record

Use SQL You can also manipulate stored procedures / View / Index, etc. :

Tips :SQL It is a standard and general operation of relational database Language ( mandarin ), Each database manufacturer in order to enhance the function of their own database , All provide their own database language , Call it number According to Kuku's dialect . Dialects are not universal !

1.2 Connect mysql The server

1.2.1 Two connection modes

Through command line tools Or third-party tools such as :Navicat , SQLyog etc. , You can log in. MySQL client , Connect MySQL The server , To access the data in the server .

1)、 Connect mysql The server :

 Display password input :
mysql -uroot -p password -- Note blank space This is not a semicolon special . Other sql Almost every sentence of a sentence ends with a semicolon .
Do not display password input :
Input password Can write -P + enter ( The password is written on the next line )

-u hinder root Is the user name , Super administrator is used here root; (mysql The default username is root)
-p hinder root It's a password , This is installing MySQL When the password has been specified ;
Be careful : there u and p Letter lowercase .

2)、 Connect mysql Server and specify IP And port :

mysql -uroot -proot -h127.0.0.1 -P3306

-h: hinder 127.0.0.1 Is the server hostname or ip Address , The default connection is local (127.0.0.1), This machine can omit .

-P( uppercase P) hinder 3306 It's the connection port , The default connection is 3306 port ,3306 Ports can be omitted ;

3)、 Exit the client command :quit or exit or \q

Didn't quit :DOS The directory window displays on the left MariaDB.

 Insert picture description here
4)、FAQ: common problem :

resolvent : Copy mysql Install under directory bin Path to directory , take bin The path to the directory is added to path In the environment variables !!
hold mysql Put the installation directory at the top use “ ,” separate . win7
win8 win10 There's no semicolon . Move up after editing .

reinstall mysql Stop the service first
Computer — Right click to manage ---- Services and Applications , service ----mysql(mariadb)— Right click — stop it Right click Properties — Start type — Manually or disable . Every time you turn on the computer, it will start automatically with the operating system .

1.2.2 Dos Window write SQL matters needing attention

1.SQL Statements are not case sensitive . Recommended keywords are capitalized , Custom name ( Library name , Table name , Name, etc ) Use lowercase .
2. cmd The window appears syntax , syntax There is something wrong with the grammar ( grammar )
3. stay cmd Middle link mysql After the server , have access to #、/**/、-- Wait for symbols to add comments .( Be careful : -- This comment should be followed by a space )
 Insert picture description here
4. stay cmd Middle link mysql After the server , Writing SQL When the sentence is , Can pass \c Cancels the execution of the current statement .
 Insert picture description here
5. Pay attention to sql The sentences are written in English symbols ,java Also, don't write it in Chinese . You can set punctuation in the input method to English format . Right click the input method – Set up — Check whether English punctuation is used in Chinese — determine .
6. When customizing the name , Don't name humps for more than one word , It's connected with underscores .( for example :tab_name, instead of tabName )
7.SQL The value in the statement is String or date , Use single quotes around the value ( Some versions of database double quotes can also , But single quotes are recommended ).
8.( in the light of cmd window ) Before inserting data , Set code first :set names gbk; because cmd Window default is gbk Or connect with the following command mysql The server :
mysql --default-character-set=gbk -uroot -proot
Equivalent to :
mysql -uroot -proot
set names gbk;

1.3 Database and table operation

1.3.1 establish 、 Delete 、 view the database

Be careful not to delete the database that comes with the system : mysql,information_schema, performance_schema.
Tips :
(1)SQL Statements are not case sensitive . Recommended keywords are capitalized , Custom name ( Library name , Table name , Name, etc ) Use lowercase .

SHOW DATABASES; -- View all libraries in the current database server Multiple s query: Inquire about 
CREATE DATABASE mydb1; -- establish mydb1 library database database 

(2) And when you customize the name , Don't name humps for more than one word , It's connected with underscores .( for example :tab_name, instead of tabName )

– 01. see mysql All databases in the server

show databases; -- View all databases of the server 
show tables; -- Query all tables in the current library table surface 

– 02. Enter a database ( After entering the database first , To operate tables and table records in the library

– grammar :USE Library name ;

use mysql;-- Enter into "mysql" database 
show tables; -- Query all tables in the current library 

– View the entered Library ( understand )

select database(); -- select choice , selected This is a special grammar 

– 03. View all tables in the current database

-- First into a library , Then look at all the tables in the current library 
use test;
show tables;

– 04. Delete mydb1 library

– grammar :DROP DATABASE Library name ;

drop database mydb1;-- Delete mydb1 library , But if the deleted library does not exist , May be an error . drop Reduce 

-- reflection : When the deleted library does not exist , How to avoid mistakes ?

drop database if exists mydb1;
-- If mydb1 If the inventory is in, delete , If it doesn't exist , The deletion operation will not be performed .

– 05. Recreate mydb1 library , And specify the code as utf8

– grammar :CREATE DATABASE Library name CHARSET code ;

It should be noted that ,mysql Horizontal bars are not supported in Chinese (-), therefore utf-8 To write utf8;

create database mydb1 charset utf8;-- Character set and charset

– Create... If it doesn't exist mydb1;
To create a database, you need to first judge whether there is a database with the same name , You need to delete it first ( Because you cannot create a database with the same name ), Then create .

create database if not exists mydb1 charset utf8;

– 06. View the statement when building the database ( And verify the encoding used by the database ) This is mainly used to verify the coding of the database ( understand )

– grammar :SHOW CREATE DATABASE Library name ; Add... Directly before the statement creating the database show

show create database mydb1;

1.3.2 establish 、 Delete 、 See the table

Be careful : To operate the table, you need to enter the database first .

summary : Creation and deletion of databases and tables , see All are create drop show, Both creation and deletion are judged first , Create database does not exist , The table is created after deleting the table .

– 07. Get into mydb1 library , Delete stu Student list ( If there is )

– grammar :DROP TABLE Table name ; Pay attention to multiple sql Statements can be executed together if there is a semicolon (;) separate .

use mydb1;-- Enter into mydb1 library 
drop table if exists stu;-- If there is stu surface , Delete 

– 08. establish stu Student list ( Number [ value type ]、 full name 、 Gender 、 date of birth 、 Test results [ floating-point ]), The grammar of table building :

CREATE TABLE Table name (
Name data type , ( Comma separated )
Name data type ,
...
Name data type
);

SQL sentence : The primary key is not necessarily a numeric value , It can also be a string , Self increment can only be set when the primary key is a number .

-- If there is , Delete stu surface 
drop table if exists stu;
-- establish stu Student list 
create table stu( -- Wrap first (shift+enter, A separate enter The key is to execute sql command ), stay tab Indent 
-- If id Is a primary key and a numeric value , It can be set to self increment . Can guarantee every time id It's not worth the same .
id int primary key auto_increment, -- to id Add primary key constraint , And self increase .
name varchar(50), -- Variable length string ,50 Represents that you can only save at most 50 Characters .
gender varchar(10) not null, -- to gender Add a non empty constraint .
birthday date,
score double -- Finally, you don't need a comma . Use... Between columns , separate 
) ;

– 09. see stu Student table structure

– grammar :desc Table name ;

desc stu;

 Insert picture description here

Null: Indicates whether null values are allowed to be inserted into these columns in the future (yes On behalf of the allowed ) Default: Indicates that if no value is given, it defaults to null

1.4 newly added 、 to update 、 Delete ( Table record , The use of quotation marks ,GBK)

– 10. To the students (stu) Insert record in ( data )

– grammar :INSERT INTO Table name ( Name 1, Name 2, Name 3…) VALUES( value 1, value 2, value 3…);

-- If it's in cmd Execute the insert record statement in the , First set names gbk; Insert the record again !
-- because id Primary key auto increment has been set , So insert data again ,id You don't have to give value , But you can't refuse , Write a null. In the database, use single quotation marks for strings and dates , Don't write double quotes .
insert into stu(id,name,gender,birthday,score) values(null,'tom','male','2000-3-4',89);-- Pay attention to the same sequence .
insert into stu values(null,'john','male','2002-5-4',79);-- Insert multiple rows of data at the same time , Start with the second line, the one in front () The content of can be omitted .
insert into stu values(null,'andy','fmale','2004-5-4',69);-- insert Insert into To ... Inside 
-- Query all records in student table 
select*from stu; -- * On behalf of all 

Tips :

(1) When inserting values for all columns , You can omit the names ( From the second ), But the number and order of values must be consistent with the number and order of columns when declaring !
(2)SQL When the value in the statement is string or date , Use single quotes around the value ( Some versions of database double quotes can also , But single quotes are recommended ).
(3)( in the light of cmd window ) Before inserting data , Set code first :set names gbk; -- because cmd Window default is gbk
Or connect with the following command mysql The server :
mysql --default-character-set=gbk -uroot -proot
Equivalent to :
mysql -uroot -proot
set names gbk;

– 11. Inquire about stu surface all Student information

– grammar :SELECT Name | * FROM Table name

select * from stu; -- * On behalf of all select Almost all of them query tables , In addition to viewing the currently entered Library .

– 12. modify stu The scores of all the students in the table , Add 10 Points of specialty points of

– Modify grammar : UPDATE Table name SET Column = value , Column = value , Column = value …[WHERE Clause ];

update stu set score=score+10; -- update to update ( Not writing conditions means updating all score)
-- score+=10 mysql China does not support it. +=

– 13. modify stu The number in the table is 1 Student achievement of , Change the score to 83 branch .

update stu set score=83 where id=1;-- where Generally used when adding a condition to a statement , For filtering 
-- modify 3 The sex of student No. is 'male', The score is changed to 99;
update stu set score=99,gender='male' where id=3;

Tips :where Clause is used to filter records , Keep records that meet the requirements , Remove unqualified records .

– 14. Delete stu All the records in the table ( The syntax of deletion is the simplest ) Note that addition, deletion, modification and query are all operations on the records of the table

– Delete record Syntax : DELETE FROM Table name [where Clause ]

delete from stu; -- Delete stu All records in the table Delete the records in the table with delete, that 2 One of them is drop

– Delete only the eligible ( Deletion is a direct deletion , Don't consider the displayed data like a query , Just delete it )

delete from stu where id>2; – Delete stu In the table id Greater than 2 The record of

1.5 Query table record

– Prepare the data : The following exercises will use db10 Tables and table records in Library , Please enter first db10 database !!!

1.5.1 Basic query

SELECT Statement to select data from a table . The results are stored in a result table ( Called result set ).

grammar :SELECT Column name | * FROM Table name

Tips :(1) *( asterisk ) For wildcard , Query all columns .

select * from emp;

 Insert picture description here

(2) But use *( asterisk ) Sometimes unnecessary columns are found out , And it's not as efficient as specifying column names directly

– 15. Inquire about emp All employees in the table , Show name , Salary , Bonus

select name,sal,bonus from emp;

 Insert picture description here

– 16. Inquire about emp All departments and positions in the table

select dept,job from emp;

 Insert picture description here
For example, I want to see it department dept and lecturer job The type of , You only need to display one for each category .
reflection : If the result of the query , There are a lot of duplicate records , How to eliminate duplicate records , Keep only one ? */
Eliminate duplicate records in a table
– stay select after 、 Before listing , Use DISTINCT Remove duplicate records

select distinct dept,job from emp; -- distinct Different kinds of 

 Insert picture description here

1.5.2 WHERE Clause query

WHERE Clause query syntax :SELECT Column name | * FROM The name of the table WHERE Column Operator value

WHERE Clause is followed by condition , There can be multiple conditions , Use... Between multiple conditions Conjunctions (or | and) Connect

The following operators can be found in WHERE Used in clauses :

 Insert picture description here

– 17. Inquire about emp In the table 【 Pay more than 3000】 All employees , Show employee name 、 Salary

select name,sal from emp where sal>3000;

 Insert picture description here

– 18. Inquire about emp In the table 【 Total salary ( Salary + Bonus ) Greater than 3500】 All employees , Show employee name 、 Total salary

select name,sal+bonus from emp ; -- Ask for the total salary of all employees 
select name,sal+bonus from emp
where sal+bonus>3500;

 Insert picture description here

– If you will ’ Han Shaoyun ’ Your bonus is updated to null value , In the execution of the above SQL sentence , Is there a problem ?(null+ Any value =null)

update emp set bonus=null where name=' Han Shaoyun ';

Query in progress : It can be seen that Han Shaoyun , Salary sal=5000 should >3500, But the bonus is null, So add up to null value . It doesn't show .
 Insert picture description here

– ifnull( Name , value ) function : Determines whether the specified column contains null value , If there is null value , Replace... With a second value null value

(null It's usually used 0 Replace )

select name,sal+ifnull(bonus,0) from emp
where sal+ifnull(bonus,0)>3500;

 Insert picture description here
– Pay attention to the header in the above query results , How to put the sal+bonus It is amended as follows “dec Total salary ”
Note that column names cannot be used in where In the clause
– Use as You can specify an alias for the header

select name as full name ,sal+ifnull(bonus,0) as Total salary from emp
where sal+ifnull(bonus,0)>3500;

in addition as It can be omitted

select name full name ,sal+ifnull(bonus,0) Total salary from emp
where sal+ifnull(bonus,0)>3500;

 Insert picture description here

– 19. Inquire about emp In the table 【 Salary in 3000 and 4500 Between 】 The employees' , Show employee name and salary

select name, sal from emp
where sal>=3000 and sal<=4500; -- and 

 Insert picture description here

– Tips : between…and… stay … and … Between

select name,sal from emp
where sal between 3000 and 4500; -- Include 3000, Also include 4500. If you don't include the head and tail, you can't use between and.

Be careful : this 2 There is only one difference between : The writing boundary above can control , Is it >= ,> Number .
The following expression must include head and tail .
 Insert picture description here

– 20. Inquire about emp In the table 【 The salary is 1400、1600、1800】 The employees' , Show employee name and salary
Be careful :1.where Multiple conditions in a sentence must have conjunctions .
2. If you use and It means that your logic is wrong : use and It means taking every employee's salary , See if you meet this at the same time 3 Conditions , How can an employee's salary meet this requirement at the same time 3 Conditions . So we should use or

select name,sal from emp
where sal=1400 or sal=1600 or sal=1800; -- or Indicates or 

– Or in another way :in

select name,sal from emp
where sal in(1400,1600,1800); // The query shows all that meet the () Records in the table of any condition 

 Insert picture description here

– 21. Query salary is not 1400、1600、1800 The employees' , Show employee name and salary

select name,sal from emp
where not(sal=1400 or sal=1600 or sal=1800);
-- or 
select name,sal from emp
where sal not in(1400,1600,1800);

 Insert picture description here

– 22.( Do it yourself ) Inquire about emp The salary in the table is greater than 4000 And pay less than 2000 The employees' , Show employee name 、 Salary .
Be careful : Out-of-service and, Because no salary is better than 4000 Big , Than 2000 Still small .

select name,sal from emp
where sal>4000 or sal<2000;

 Insert picture description here

– 23.( Do it yourself ) Inquire about emp The salary in the table is greater than 3000 And the bonus is less than 600 The employees' , Show employee name 、 Salary 、 Bonus .

select name,sal,bonus from emp
where sal>3000 and bonus<600; -- There is an error in the result , One line of data is missing null Which line? 

 Insert picture description here

Handle null value (null Value is compared with any value false)

select name,sal,ifnull(bonus,0) from emp
where sal>3000 and ifnull(bonus,0)<600;

 Insert picture description here

– 24. Look up employees who don't have a department ( That is, departments are listed as null value )

select * from emp where dept=null; -- Condition error , stay where A column name cannot be used in a condition equal to null Value as a condition to filter . Even it itself . solve : use is.
select * from emp where dept is null; -- The information is correct 

 Insert picture description here

– reflection : How to query employees with departments ( That is, departments are not listed as null value )

select * from emp where dept is not null;
-- Or 
select * from emp where not (dept is null);

 Insert picture description here

1.5.3 Fuzzy query ( It also belongs to where Sentence query )

LIKE The operator is used in WHERE Search for the specified pattern in the column .

And wildcards (%、_) In combination with , among "%" Express 0 Or any number of characters ,"_" Represents an arbitrary character .

grammar :SELECT Column | * FROM Table name WHERE Name LIKE value

Example :

– 25. Inquire about emp The names in the table are " Liu " Employees at the beginning of the word , Show employee name .

select name from emp where name like ' Liu %';

 Insert picture description here

– 26. Inquire about emp The names in the table contain " tao " Employees with words , Show employee name .

select name from emp where name like '% tao %';

 Insert picture description here

– 27. Inquire about emp The names in the table are " Liu " start , And the employee with the name in two words , Show employee name .

select name from emp where name like ' Liu _';
select name from emp where name like ' Liu _ _';

 Insert picture description here

1.5.4 Multi line function query

Multiline functions are also called aggregations ( Gather ) function , Count by one or all columns .

Common multiline functions are :

Multiline functions effect
COUNT( Name | * ) Counts the number of rows of records for the specified column in the result set . – Count , Calculation
MAX( Name ) The maximum value of a column in the statistical result set
MIN( Name ) The minimum value of a column in the statistical result set
SUM( Name ) The sum of all values in a column in the statistical result set
AVG( Name ) The average value of a column in the statistical result set

Tips :(1**) Multiline functions cannot be used in where clause .

(2) Multiline functions are related to grouping , Grouping or not will directly affect the execution result of multiline function .

(3) A multiline function will do statistics on null Value to filter , Direct will null Worth discarding , Don't participate in Statistics .

– 28. Statistics emp The salary in the table is greater than 3000 Number of employees ( Is to query the column names , Wrap with keywords )
Be careful : Count how many rows there are in all columns , Count how many rows there are in a column , The result may be the same or different . Because, for example bonus This column : Yes null value , The statistical results are changed from the original 7 Turn into 6.
== It is recommended to write * ==

select count(*)from emp where sal>3000; --count* Count the number of rows for all columns 7
select count(id)from emp where sal>3000; --countid For all id The statistical number of rows 7

 Insert picture description here
 Insert picture description here

– 29. seek emp The highest salary in the table

select max(sal) from emp; -- Return the highest salary 5000
select min(sal) from emp;

 Insert picture description here

– 30. Statistics emp The sum of the salaries of all the employees in the table ( Bonus not included )

select sum(sal) from emp;-- Total salary :39650
select sum(bonus) from emp;-- The sum of bonuses :5900 The value is correct , Multiline functions can be useful for null Value processing , Direct discarding , Don't participate in Statistics .

 Insert picture description here

– 31. Statistics emp Table average salary of employees ( Bonus not included )

select sum(sal)/count(*) from emp;
select avg(sal) from emp;-- 39650/12 This is usually used , The bottom layer is calculated in the above way .

 Insert picture description here

Problems that need to be paid attention to in multiline function :

  • Multiline functions are related to grouping , If the data in the query result is not grouped , By default, the whole query result is a group , The multiline function will count the data of the current group by default . There is only one result .

- If the data in the query results are grouped ( There's more than one group ), Multiline functions are counted according to the groups , How many groups are there , How many results will be counted .

select * from emp;

for example : Statistics emp Table number

select count(*) from emp; -- 12 If there is no grouping, the number of people will be counted in the same group by default . A group can only participate in one result .

 Insert picture description here

The result is emp All the people in the table

Another example is : According to gender emp All employees in the table are grouped , Then count the number of people in each group , Show gender and number of people
Divided by sex 2 Group , Then count the number of people in each group

select count(*) from emp group by gender; --10 2

 Insert picture description here

1.5.5 Group query ( These groups , Sort , Paging is and where Clause juxtaposition relation , Just in order , Not as where Conditions to use .)

GROUP BY Statement to group the result set according to one or more columns . ( Be similar to where Clause , Put it behind the query table )

We can use... On grouped columns COUNT,SUM,AVG,MAX,MIN Such as function .

grammar :SELECT Column | * FROM Table name [WHERE Clause ] GROUP BY Column ;( Note that if there is where The clause is written first where Clause is writing grouping )
 Insert picture description here
Peiyou Department , The Ministry of employment ,null. this 3 Group .
– 32. Yes emp surface , Group employees by Department , See the effect after grouping .

select name,dept from emp group by dept; -- Show only the first person in each group group Group 
-- After statistical grouping , The number of people in each group 
select count(*) from emp group by dept;-- 3 A set of , So there are three results 

Because it is a two-dimensional table, you can only see the first person in each group .
 Insert picture description here
 Insert picture description here

– 33. Yes emp Tables are grouped by position , And count the number of people in each position , Show position and corresponding number of people

-- according to job Grouping , Count the number of people in each group ( Number of people per position )
select job,count(*) from emp group by job;

Of course, not every column is suitable for grouping , If by name ,50 If individuals have different names , It is divided into 50 Group , So it doesn't make any sense .
 Insert picture description here

– 34. Yes emp Table grouped by Department , Ask for the highest salary of each department ( Bonus not included ), Show department name and maximum salary

88888888888

-- If you don't group , Use it directly max(sal), This is the statistics of the whole emp The highest salary in the table 
select max(sal) from emp;-- 5000;
-- If grouped by Department , Can be divided into three groups , In the use of max(sal), Is to count the maximum salary of each group .
select dept,max(sal) from emp group by dept;

Because it is classified by Department , So you can display the Department , If you display names instead of grouping them by name ,2 It doesn't make sense whether there is a mapping between the two .
 Insert picture description here

1.5.6 Sort query

Be careful : where Clause > grouping > Sort First write where Writing groups , Writing sorting . ( Priority order )

Use ORDER BY Clause sorts the records in the result set according to the specified column before returning

grammar :SELECT Name FROM Table name ORDER BY Name [ASC|DESC]

Yes ORDER BY The key word , Don't write in ascending order The descending keyword defaults to ASC Ascending , If even orderby Keywords are not written , By default, the data returned by the query is displayed in the order of adding data .
Don't write ASC( Default ) Ascending , From low to high ;DESC Descending , From high to low .

– 35. Yes emp The salaries of all employees in the table are in ascending order ( From low to high ) Sort , Show employee name 、 Salary .
Be careful : Ascending order refers to the number from top to bottom , From low to high .

select name,sal from emp order by sal ; -- The default is ascending asc It can be omitted order The order 
select name,sal from emp order by sal asc; -- Ascending order by Sort by (ascend rising )

 Insert picture description here

– 36. Yes emp The bonus of all employees in the table is in descending order ( From high to low ) Sort , Show employee name 、 Bonus .

select name,bonus from emp order by bonus desc; -- Descending Don't omit (descend Descending )

 Insert picture description here

1.5.7 Paging query ( The order of these )

First write where grouping Sort Pagination ( Priority order ) Namely front where grouping , Sort , Pages are not writing pages .

summary : They are all placed after the query table .

stay mysql in , adopt **limit Paging queries ,** The query formula is :

limit Position offset , Number of records per page
limit ( The current page number -1)* Each page shows the number of records , Each page shows the number of records

– 37. Inquire about emp All records in the table , Pagination display : Each page shows 3 Bar record , Return data for all pages .
Page number : such as , The first page is 1.

-- Each page shows 3 strip , Query the first 1 Page data limit boundaries , Range 
select * from emp limit 0,3; -- The first page number is 1,(1-1)*3=0 Records per page ;3 Record as :3
-- Each page shows 3 strip , Query the first 2 Page data 
select * from emp limit 3,3;
-- Each page shows 3 strip , Query the first 3 Page data 
select * from emp limit 6,3;

 Insert picture description here

– 38. seek emp At the top of the pay list 3 Employee information , Show name and salary

-- Now, according to the salary descending order ( From high to low ) Sort 
select name,sal from emp order by sal desc;
-- Paging query on the basis of sorting , Only the first page 
select name,sal from emp order by sal desc limit 0,3;

 Insert picture description here

1.5.8 Other functions ( It can be placed in the front or where In the clause )

Function name interpretative statement
curdate() Get current date , The format is : Specific date usage : select curdate();
curtime() Get the current time , The format is : Minutes and seconds ditto
sysdate()/now() Get current date + Time , The format is : Specific date Minutes and seconds ditto
year(date) return date The year in usage : It needs to pass a parameter
month(date) return date Month in select month(now()); obtain now The month in this time
day(date) return date The number of days in
hour(date) return date The hours in the game
minute(date) return date The minutes in
second(date) return date Seconds in
CONCAT(s1,s2…) take s1,s2 When multiple strings are merged into a single string
CONCAT_WS(x,s1,s2…) Same as CONCAT(s1,s2,…) function , Is to add... Between each string x,x The separator is

1.curdate,curtime,sysdate/now, year Usage is similar.
 Insert picture description here
2. year(date) Get the current year from ,month,day,hour,minute,second, Usage is similar.
 Insert picture description here

– 39. Inquire about emp All in the table 【 stay 1993 and 1995 Born between 】 The employees' , Show name 、 Date of birth .
Dates can be compared , But in this table birthday It's a year - month - Data in daily format , So put the date 1993 Convert to date format for comparison :‘1993-1-1’ Or use year Function to extract the date for comparison .

-- Convert the values on both sides of the operator to date type , Comparing 
select name,birthday from emp
where birthday>='1993-1-1' and birthday<='1995-12-31';
-- Or , Extract the year from the date , Compare the year with the year 
select name,birthday from emp
where year(birthday)>=1993 and year(birthday)<=1995;

 Insert picture description here

– 40. Inquire about emp All the employees in the table who have birthday this month

select * from emp
where month(now())=month(birthday);

now(): Get current date year month Japan . month: Extract the date of the current year . Then put the year in the current table month Japan Get the month in . Finally, compare .
 Insert picture description here
 Insert picture description here
Add : Get all employees who celebrate their birthday next month : Pay attention to the first way of writing, if it is 12 month +1 by 13 month , It's not equal to 1 month . The following is better ,2 Take the remainder from each side .
 Insert picture description here
 Insert picture description here
The string splicing exercise is : Add units to the value of the query .
– 41. Inquire about emp The names and salaries of the employees in the table ( The salary format is : xxx( element ) )

select name,concat(sal,'( element )') from emp; -- concat(s1,s2,s3...)

 Insert picture description here

– Supplementary exercises : Inquire about emp The names and salaries of the employees in the table ( The salary format is : xxx/ element )

-- use concat Function implementation 
select name,concat(sal,'/ element ') from emp;
-- Or use concat_ws Realization , concat_ws(x,s1,s2,s3...)
select name, concat_ws('/',sal,' element ') from emp; -- namely ,s1 And s2 Two by two splicing will add a x

 Insert picture description here

 Insert picture description here

1.6 mysql Data type of


1.6.1 value type

MySQL Multiple integers are supported in , In fact, it's largely the same , It's just that the size range of the stored values is different .

tinyint: Occupy 1 Bytes , be relative to java Medium byte

smallint: Occupy 2 Bytes , be relative to java Medium short

int: Occupy 4 Bytes , be relative to java Medium int ( Focus on mastering )

bigint: Occupy 8 Bytes , be relative to java Medium long

The second is floating point type, that is :float and double type

float:4 Byte single precision floating point type , be relative to java Medium float

double:8 Byte double precision floating point type , be relative to java Medium double ( Focus on mastering )

1.6.2 String type

1、char(n) Fixed length string , The longest 255 Characters .n Indicates the number of characters , for example :

– establish user surface , Specify the user name as char type , The length of characters shall not exceed 10

Number of characters : Zhang San ,2 Characters . Tom,3 Characters .

create table user(
username char(10),
...
);

The so-called fixed length , When the length of the inserted data is less than the specified length , The remaining space will be filled with spaces .( shortcoming : This may waste space )

Advantage is : Efficient than varchar Higher .

char Type is often used to store data of fixed length .( Such as : ID number , Student number )

2、varchar(n) Variable length string , Not more than 65535 Bytes ,n Indicates the number of characters , Generally more than 255 Characters , Will use text type , for example : various All kinds of .

iso8859-1 clock : One character takes up 1 Bytes ,1*n <65535, n At most, it's equal to 65535
utf8 clock : A Chinese character occupies 3 Bytes ,3*n<65535,n At most, it's equal to 65535/3
GBK clock : A Chinese character occupies 2 Bytes ,2*n<65535,n At most, it's equal to 65535/2

– establish user surface , Specify the user name as varchar type , Length not exceeding 10 Characters

create table user(
username varchar(10)
);

So called indefinite length , When the length of the inserted data is less than the specified length , The remaining space can be reserved for other data .( Save a space )

summary : Data of fixed length , use char type , It doesn't waste space , It's also more efficient .

If the length is not fixed , Use varchar type , It doesn't waste space .

3、 Large text ( Long text ) type

The longest 65535 Bytes , Generally more than 255 Character columns will use text, No more than char or varchar type .

– establish user surface :

create table user(
resume text
);

another ,text There are also many kinds of , among bigtext The length of the stored data is about 4GB.

Extended content 3:( Interview questions )char(n)、varchar(n)、text Can represent string type , The difference is :

(1)char(n) When saving data , If the length of the stored string is less than the specified length n, I'll fill in the blanks later , Therefore, it may cause waste of space , however char The storage speed of the type is faster than varchar and text fast .

therefore char The type is suitable for storing data of fixed length , So there is no waste of space , Storage efficiency is faster than the last two !

(2)varchar(n) When saving data , Store by the actual length of the data , The remaining space can be left for other data , therefore varchar No waste of space .

therefore varchar Suitable for storing data with variable length , There will be no waste of space .

(3)text It's a big text type , The general text length exceeds 255 Characters , Will use text Type storage .

1.6.3 The date type

date: Specific date

time: Minutes and seconds

datetime: Specific date Minutes and seconds

timestamp: Time stamp ( What is actually stored is a time millisecond value ), And datetime The storage date format is the same . The difference between the two :

  • timestamp The biggest expression is 1970~2038 year , and datetime The scope is 1000~9999

  • timestamp Inserting data 、 When modifying data , It can be automatically updated to the current system time ( Later, I'll explain it later )

1.7 mysql Field constraints for


Field constraints / Column constraints --> constraint : That is, add some restrictions .

1.7.1 Primary key constraint

Primary key constraint : If you add a primary key constraint to a column , So this column is the primary key , The primary key is unique and cannot be empty .

The function of primary key : As a unique identifier , A unique representation of a table record ( The function is similar to the ID number of the person , It can only represent a person .)

Add primary key constraint , For example id Set the primary key : primary key

create table stu(
id int primary key, -- primary The main key key 
...
);

If the primary key is a numeric type , For the convenience of inserting primary keys ( And make sure that when you insert data , The primary key will not report an error due to repetition ), You can set a primary key auto increment policy . – Be careful : Self increment can only be set for numeric type .

The primary key auto increment strategy refers to : Set the primary key of auto increment policy , When you insert a record , Do not give id assignment , Just set up one null value , The database will automatically be id Assign a value (AUTO_INCREMENT Variable , The default from the 1 Start , In turn +1), In this way, we can guarantee id Is the only one. , There's no setting id Trouble .

Be careful : Set the gain After the second number is deleted When inserting a number, it changes back to 3
Set the gain You can also assign new values If it is greater than the self increasing value It will return a +1 Number of numbers because id Can't repeat

take id The primary key is set to auto increment :

create table stu(
id int primary key auto_increment, -- automatic automatic 
... -- increament increase 
);
Be careful : Primary key constraint is not equal to non empty constraint + Unique constraint , Also as a unique sign .

1.7.2 Non empty constraint

Non empty constraint : If you add a non NULL constraint to a column , Then the value of this column cannot be empty , But it can be repeated .

Add non empty constraints , Such as password Add non empty constraints : not null

create table user(
password varchar(50) not null,
...
);

1.7.3 Unique constraint

Unique constraint : If you add a unique constraint to a column , Then the value of this column must be unique ( That is, it can't be repeated ), But it can be empty .

Add unique constraints , Such as username Add unique constraints and non null constraints :

unique: Represents the only constraint

unique not null: representative Only and not empty , It is different from the primary key constraint ( The primary key constraint also serves as an identifier )

create table user(
username varchar(50) unique not null,
...
);

1.7.4 Foreign key constraints ( Usually 2 Zhang or 2 More than one table )

explain : If you need to query multiple tables , Multiple tables need to be associated . Such as : Query employees while querying departments , When querying the class again, query the students . This association is established between tables , This association is realized through foreign key constraints .

Foreign keys are actually used for notice Such a column of the corresponding relationship between the data of two tables in the database . In this way, the database will help us maintain the relationship between the data in the two tables .

foreign key(dept_id) references dept(id) references: Reference resources

Adding foreign keys while creating tables :

create table emp(
id int,
name varchar(50),
dept_id int,
foreign key(dept_id) references dept(id)
);

 Insert picture description here

(1) If you want to show that there is a corresponding relationship between the data of two tables , Just add a column to one of the tables , Save another table Primary key , You can save the relationship between the data of the two tables .

But the added column (dept_id) For a database, it is A normal column , The database will not know that there is any relationship between the two tables , So the database won't help us maintain this relationship .

 Insert picture description here

(2) If you will dept_id Column set to foreign key , Equivalent to notification database , There is a corresponding relationship between the Department table and the employee table ,dept_id The data in the primary key column should be referenced , Once the database knows that there is a relationship between the Department and the employee table , Will help us maintain this relationship .
( It can avoid the destruction of data integrity and consistency , For example, you delete the Sales Department , If Zhao Si and Liu Neng can't find the Department, they will report an error , Prevent you from deleting )
Now I just want to delete ? OK, Zhao Si and Liu Neng id Change to another department id Such as 3:update emp dept_id=3 where is=4 or id=5;
Be careful : Foreign keys can be set in the company , You can also not set foreign keys . The difference is that setting up a foreign key database will help you maintain relationships , If you don't set up this relationship, you can maintain it yourself . Saving this relationship is to add the primary key of another table to one table , Generally, a table with multiple rows of data is added with a smaller one . Because no matter how few parties add primary keys, they can't save all the data , Such as : Show... In one position on the same line 2 Data
 Insert picture description here

reflection : If no foreign key is specified when creating the table , So how to specify foreign keys later ? And how to delete foreign keys ? In the following extension .

1.8 Table relations


There are three common table relationships :

One to many ( For one more )、 one-on-one 、 Many to many

 Insert picture description here
explain : 1 Corresponding 1, It shows that Zhang Fei has been taught by Mr. Wang ,1 Corresponding 2 It shows that Liu Bei has been taught by teacher Qi .
 Insert picture description here
For many to many relationships , Setting a joint primary key means that each individual primary key can be repeated in the third table , however stuid and teaid Add up and don't repeat . Then add a foreign key, and the underlying database will help you automatically maintain this relationship , Without adding foreign keys, you need to maintain this relationship yourself , such as : Does deleting one of the rows affect the other table . Reference extension .

1.9 Multi-table query


1.9.1 Link query

– Prepare the data : The following exercises will use db30 Tables and table records in Library , Please enter first db30 database !!!
Query department table
 Insert picture description here
Look up the employee table
 Insert picture description here
– 42. Query the Department and the employee information corresponding to the Department

select *from dept,emp; -- Is to write multiple tables , Separated by commas .

 Insert picture description here

There is a lot of wrong data in the above query , Generally, we don't use this kind of query directly .

Cartesian product query : The Cartesian product query means , Look up two tables , One of the tables has m Bar record , The other one has n Bar record , The result of the query is m*n strip .

Although Cartesian product query contains a lot of error data , But we Can pass where Clause to weed out the wrong data , What's left is the right data .

-- Employee department number = Department number 
select *from dept,emp -- because id In the Department table and employee table , So add column names to distinguish 
where emp.dept_id=dept.id; -- because dept_id Is only found in emp The columns in the table , So you can write Write dept_id and id There is already emp The table exists again dept surface , therefore dept.id( Table name . Name ) Used to distinguish , Otherwise, the error report will be ambiguous .

 Insert picture description here

So how to find this condition : In the first row, you can see the of the employee table according to the table id, For example, Zhang San's id by 1 The corresponding primary key depe_id by 1, It happens to be the Department table again id by 1 So the data displayed in the query is correct . The second line : Employee table id by 1 The corresponding foreign key is 1, But the corresponding department table id by 2 So this is wrong data .
 Insert picture description here

adopt where Clause to eliminate the wrong data in Cartesian product query , Keep the right data , This is the connection query !

The above query can be replaced by the following query :(, transposition inner join where transposition on )

select *from dept inner join emp
on emp.dept_id=dept.id; -- Internal connection query , Same as the query result above 

1.9.2 Left outer connection query ( Based on the left )

The difference from the above is , The above connection query only queries the employee information corresponding to the Department , The left outer link of this place is the information of all employees in the Department table , Then, in addition to the employee information corresponding to the display department, it should also be displayed. If there is no corresponding information, it will be displayed as null. summary : The difference is that the display satisfies the corresponding relationship, but also displays the data without relationship
– 43. Inquire about 【 All departments 】 And the corresponding staff of the Department , If there are no employees in a department , The employee is shown as null
Be careful :where Turn into on

select *from dept left join emp
on emp.dept_id=dept.id;

 Insert picture description here

Left outer connection query You can query all the records in the table on the left , The table on the right shows only the data corresponding to the left , If some records in the left table have no corresponding data on the right , Right The edges are displayed as null that will do .

( If you want to check the data of all tables on the left , Just use the left outer connection to query )

Connection symbol "," Switch to left join where Switch to on
summary : The left outer connection is to query all the data in the left table , Then the right table displays the data corresponding to the left table .

1.9.3 Right outer connection query ( On the right side )

– 44. Inquire about 【 All employees 】 And the Department to which the employee belongs , If an employee doesn't have a department , The Department is shown as null

select *from dept right join emp
on emp.dept_id=dept.id;

Right outer connection query : You can query all the records in the table on the right , The table on the left shows only the data corresponding to the right , If some records in the table on the right have no corresponding data on the left , It can be shown as null.
summary : The right outer connection is to query all the data in the right table , Then the left table displays the data corresponding to the right table .

All external connection query :
Expand : If you want to find all the data in the two tables ( Left lateral + Right outside and remove duplicate records ), have access to All external connection query , however mysql It doesn't support external connection query .

select *from dept left join emp on emp.dept_id=dept.id
union
select *from dept right join emp on emp.dept_id=dept.id;

have access to union Merge the results of the left outer join query with the results of the right outer join query , And remove duplicate records . for example :

 Insert picture description here

It should be noted that :union You can put two SQL The results of statement execution are merged , But there's a premise :

(1**) Two article SQL Statement query results Number of columns It has to be consistent **

The joint query : hold 2 All the data in one table .
== however dept Listed emp Table has 3 Column , So wrong reporting ==
 Insert picture description here
solve : Only show the same number of columns .* It's a 2 Column , One 3 So it's wrong .
 Insert picture description here

(2) Two article SQL Statement query result column name 、 And the order has to be the same
If not, we can specify the column alias .
 Insert picture description here

also union By default, duplicate records in the two queries will be removed ( If you don't want to remove duplicate records , have access to union all)

1.9.4 Subquery exercise

Will a sql The result of the execution of a query is called a sub condition .

– Prepare the data : The following exercises will use db40 Tables and table records in Library , Please enter first db40 database !!!
 Insert picture description here

– 45. List the pay ratio ’ Wang Haitao ’ All the employees with high salary , Show name 、 Salary
Single table

-- Find out ' Wang Haitao ' Pay for 
select sal from emp where name=' Wang Haitao '; -- 2450
-- Find out the ratio ' Wang Haitao ' Information of all employees with high salary It's not dead to write like this , If you change the salary later, you don't have to change the code Like this, using one statement as a clause of another statement is called subquery .
select name,sal from emp
where sal>(select sal from emp where name=' Wang Haitao ');

 Insert picture description here

– 46. List and ’ Liu peixia ’ All employees in the same position , Show name 、 Position .
Single table

-- Find out the position Liu peixia is engaged in 
select job from emp where name=' Liu peixia ';
-- Then find out the employees who are engaged in the same position as Liu peixia 
select name ,job from emp
where job=(select job from emp where name=' Liu peixia ');

 Insert picture description here

– 47. List the pay ratio ’ Big data Department ’ department ( The known department number is 30) Employee information that all employees are highly paid , Show employee name 、 Salary and department name .= This is a query 2 A watch : Departmental table The employee table

If you don't consider employees without departments

-- First connect and query the Department table and employee table , Show employee name 、 Salary and department name 
select emp.name ,sal, dept.name from dept,emp
where emp.dept_id=dept.id;
-- . List the pay ratio ' Big data Department ' department ( The known department number is 30) Employee information that all employees are highly paid , It can be simplified as :1. Find out ' big data ' The highest salary in the Department 
select max(sal) from emp where dept_id=30;
2.-- List the pay ratio ' big data ' Employee information with the highest salary in the Department 
select emp.name ,sal, dept.name from dept,emp
where emp.dept_id=dept.id and
sal>(select max(sal) from emp where dept_id=30);

 Insert picture description here

If you add employees without departments ( Consider Han Shaoyun ), Ensure that the data of one table can be queried and can be connected externally

-- Use external connection to query department table and employee table 
select emp.name ,sal, dept.name from dept right join emp
on emp.dept_id=dept.id;
-- Find out ' big data ' The highest salary in the Department 
select max(sal) from emp where dept_id=30;
-- List the pay ratio ' big data ' Employee information with the highest salary in the Department 
select emp.name ,sal, dept.name from dept right join emp
on emp.dept_id=dept.id
where sal>(select max(sal) from emp where dept_id=30);

 Insert picture description here

1.9.5 Multi table query exercise ( Table alias usage , Column alias )

– 48. List in ’ Peiyou Department ’ The employees who work , Suppose you don't know ’ Peiyou Department ’ Department number of , Show department name , Employee name .
analysis : The name of the Department and the name of the employee are from 2 So use multiple tables to query .
Be careful :1. You can add column aliases , You can also add table aliases . It's almost the same ,as It can be omitted .

-- Join query department table and employee table Multiple table query First connect and query 
select dept.name,emp.name from dept,emp
where emp.dept_id=dept.id;
-- Find the Department name as ' Peiyou Department ' The employees' 
select dept.name,emp.name from dept,emp
where emp.dept_id=dept.id and dept.name=' Peiyou Department ';

Be careful :2. If you declare an alias without , That's also wrong . such as : in front select in from When specifying an alias for the table , In the rear where There is no need to e.dept_id If you still use the previous one, it's still wrong . In short, the alias after the previous declaration , If it appears later, you must replace it with an alias , Or you declare what it does .

Column names do not have this usage . After specifying the alias of the column , The following columns still use the original .
– Expand : You can add an alias to the indication , After adding an alias , You need to replace the table name with an alias

select d.name,e.name from dept d,emp e
where e.dept_id=d.id and d.name=' Peiyou Department ';

 Insert picture description here

– 49.( Self query ) List all employees and their immediate supervisors , Show employee name 、 Superior number , The name of the superior
Think of a table as 2 Look at it with a watch

/* take emp At the same time, it can be regarded as employee table and superior table ---- Alias as 2 A watch , It's actually a table . * emp e1 As an employee table emp e2 As a superior table * The query table : emp e1, emp e2 * Columns to query : e1.name,e2.id, e2.name * Connection condition : Employee's superior No = Superior number * e1.topid=e2.id */
select e1.name,e2.id, e2.name -- select Generally used for the structure of query table 
from emp e1, emp e2 -- from Just like that watch 
where e1.topid=e2.id; -- where It is generally used to filter conditions 

 Insert picture description here
 Insert picture description here

– 50. List the minimum salary greater than 1500 Of various positions , Display the position and the minimum salary for that position

-- Group by position , In finding the minimum salary for each position 
select job,min(sal) from emp group by job;
-- The minimum wage is greater than 1500 What are your positions 
select job,min(sal) from emp group by job
having min(sal) >1500;

 Insert picture description here

1.10 Supplementary content :where and having The difference between clauses :

(1) The same thing : where and having You can filter records .
(2) difference :where Before grouping , Filter records , also where Multiline functions and column aliases cannot be used in clause ( But you can use table aliases )
(3) difference :having After grouping , Filter records , also having Clause can use multiline functions and column aliases 、 Table alias .

– 51. List the number of employees working in each department 、 Average wage . Show department number 、 Number of employees , Average salary .

-- Group by Department , Count each group ( Every department ) The number of people , Average salary 
select dept_id,count(*),avg(sal)
from emp
group by dept_id;

 Insert picture description here

– 52. Find out the Department with at least one employee , Show department number 、 Department name 、 Department position 、 Number of departments .

-- Join query department table and employee table 
select d.id,d.name,d.loc
from dept d,emp e
where d.id=e.dept_id;
-- Group by Department 
select d.id,d.name,d.loc,count(*)
from dept d,emp e
where d.id=e.dept_id
group by d.name
having count(*)>0; -- This sentence can be added or not , Because the above grouping has met this condition .

 Insert picture description here

– 53. List all employees who are employed before their immediate supervisor , Show employee number 、 Employee name 、 Department name .

/* emp e1 The employee table , emp e2 The superior table * The query table : emp e1, emp e2, dept d * Columns to query : e1.id, e1.name, d.name * Connection condition : e1.topid=e2.id * e1.dept_id=d.id * filter : e1.hdate < e2.hdate */
select e1.id, e1.name, d.name
from emp e1, emp e2, dept d
where e1.topid=e2.id
and e1.dept_id=d.id
and e1.hdate < e2.hdate;

 Insert picture description here

– Add : Query the information of the highest paid employee in the employee table day11pm 223

select name,max(sal) from emp; -- The result of this query is wrong , because name and sal There is no correspondence .
-- You can sort by salary in descending order , One message per page , Check the first page .
select * from emp order by sal desc limit 0,1;
-- Or : You can also use subqueries The efficiency will be lower 
select max(sal) from emp;-- seek emp The highest salary in the table 
select * from emp where sal=(select max(sal) from emp);

 Insert picture description here

1.11 Database backup and recovery


You can also use tools for backup Navicat,Sqlyog

1.11.1 Backup database

stay cmd Window ( Not logged in , namely : No connection to database ), The specified database can be backed up by the following command :

mysqldump -u user name -p Database name > The location of the backup file

– Be careful -p And the name of the database No files will be created for you automatically

Example 1: Yes db40 Data in the library ( surface , Table record ) Make a backup , Backup to d:/db40.sql In file

mysqldump -uroot -p db40 > d:/db40.sql – This has no semicolon

Type in the password , If there is no hint , It means that the backup is successful !

You can also back up all libraries at one time , for example :

Yes mysql Backup all the databases in the server , Backup to d:/all.sql In file

mysqldump -uroot -p --all-database > d:/all.sql

Type in the password , If there is no prompt error ( The warning message is not an error , You can ignore ), It means that the backup is successful !

1.11.2 Recover database ( Backup before recovery )

1、 Recovery of database mode 1 :

stay cmd Window ( Not logged in ), You can restore the specified database through the following command :

mysql -u user name -p Database name < The location of the backup file

Example : take d:/db40.sql The data in the file is restored to db60 In the library

– stay cmd Window ( Logged in ), First create db60 library :

create database db60 charset utf8;

– stay cmd Window ( Not logged in )

mysql -uroot -p db60 < d:/db40.sql

2、 Recovery of database mode 2 :

stay cmd Window ( Logged in ), Can pass source Execute the SQL file :

source sql The location of the file

Example : take d:/db40.sql The data in the file is restored to db80 In the library

– stay cmd Window ( Logged in ), First create db80 library , Get into db80 library :

create database db80 charset utf8;
use db80;

– Re pass source Execute... In the specified position sql file :

source d:/db40.sql

1.12 Navicat Use of software


Navicat Premium Is a set of database management tools with graphical user interface , Let you connect from a single application at the same time MySQL、MariaDB、MongoDB、SQL Server、Oracle、PostgreSQL and SQLite database . Use Navicat Can quickly 、 Easily create 、 Manage and maintain databases .

1、 Use navicat Connect mysql The server ( Use cmd Connect mysql The server )
2、 View all libraries 、 Access to database 、 Create database 、 Delete database 、 modify the database
3、 Create table 、 See the table 、 Modify table 、 Delete table
4、 Add table record 、 Query table record 、 Modification table record 、 Delete table record
5、 Use navicat Writing SQL Statement operation database 、 Tables and table records
...

Bleep bleep video link :https://www.bilibili.com/video/BV1yA41147Vi/

1.13 Extended content


Now create a student table :

use test; -- Get into test library
drop table if exists stu; -- Delete student table ( If there is )
create table stu( -- Create student table
id int, -- Student id
name varchar(20), -- The student's name
gender char(1), -- Student gender
birthday date -- date of birth
);

1.13.1 Modify table — New column

grammar :ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype…];

1、 Go to stu Add... To the table score Column ,double type

alter table stu add score double;

1.13.2 Modify table — Modify the column

grammar :ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY
col_name datatype]…);

1、 modify id Column , take id Set the primary key

alter table stu modify id int primary key;

2、 modify id Column , take id The primary key is set to grow automatically

alter table stu modify id int auto_increment;

1.13.3 Modify table — Delete column

grammar :ALTER TABLE tabname DROP [COLUMN] col_name;

1、 Delete stu In the table score Column

alter table stu drop score;

1.13.4 Add or delete primary key and auto increment

reflection :a) Under table , How to id Specify primary key constraints and auto increment ?

b) Built tables , How to add primary key constraint and auto increment by modifying ?

c) How to delete the primary key constraint and auto increment in a table ?

1、 establish stu Student list , Auto increment without adding primary key , Look at the table results

use mydb1; -- Switch to mydb1 library 
drop table if exists stu; -- Delete stu Student list ( If there is )
create table stu( -- The reconstruction stu Student list , No primary key is self incrementing 
id int,
name varchar(20),
gender char(1),
birthday date
);
desc stu; -- View table structure 

The table structure is as follows : There is no primary key constraint and auto increment .

 Insert picture description here

2、 If the table is not created , Or to delete and rebuild , You can specify primary key or primary key auto increment when creating

drop table if exists stu; -- Delete stu surface 
create table stu( -- Recreate stu Table time , Specifies that the primary key is incremented 
id int primary key auto_increment,
name varchar(20),
gender char(1),
birthday date
);
desc stu; -- View table structure 

The table structure is as follows : Primary key constraints and auto increment have been added .

 Insert picture description here

3、 If you don't want to delete the rebuild table , You can also add primary key or primary key auto increment by modifying the table

Do the following again 1 Step , establish stu Student list , Auto increment without adding primary key , Look at the table results

– for example : take stu In the student list id Set to primary key and auto grow

alter table stu modify id int primary key auto_increment;
desc stu; -- View table structure 

 Insert picture description here

If only the primary key constraint is added , Don't set auto increment

alter table stu modify id int **primary key**;

If you have already added a primary key constraint , Just set auto increment , But pay attention :

(1) If no primary key is set , You can't add auto increment

(2) Only if the primary key is numeric , Then you can add auto increment

alter table stu modify id int **auto_increment**;

4、 If you want to delete the primary key auto increment

– Delete primary key auto increment , You need to delete the auto increment first

alter table stu modify id int;

– Then delete the primary key constraint

alter table stu drop primary key;
desc stu; -- View table structure 

 Insert picture description here

1.13.5 Add a foreign key constraint

1、 Add foreign key mode one : Add foreign key when creating table

The list of existing departments is as follows :

– Create department table

create table dept(
id int primary key auto_increment, -- Department number 
name varchar(20) -- Department name 
);

Ask to create employee table , And add foreign key to associate Department primary key in employee table

– Create an employee table

create table emp(
id int primary key auto_increment, -- Employee number 
name varchar(20), -- Employee name 
dept_id int, -- Department number 
foreign key(dept_id) references dept(id) -- Appoint dept_id For foreign key 
);

2、 Add foreign key mode 2 : Add a foreign key after creating a table

Existing department tables and employee tables :

– Create department table

create table dept(
id int primary key auto_increment, -- Department number 
name varchar(20) -- Department name 
);

– Create an employee table

create table emp(
id int primary key auto_increment, -- Employee number 
name varchar(20), -- Employee name 
dept_id int -- Department number 
);

– If the table already exists , You can use this method :

alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);

among fk_dept_id ( Names are defined by themselves ), Is the foreign key constraint name , Can also be 【constraint fk_dept_id】 Omit ,MySQL Will automatically assign a foreign key name , In the future, foreign keys can be deleted by this name .

foreign key(dept_id) Medium dept_id For foreign key

1.13.6 Delete foreign key constraint

1、 First, through “show create table Table name ” grammar , Query the table building statement with foreign key table , for example :

show create table emp;

The results are as follows :

 Insert picture description here

among ,emp_ibfk_1 When you create a table , A name specified by the database for the foreign key constraint , Delete the name to delete the foreign key relationship , for example :

alter table emp drop foreign key emp_ibfk_1;

 Insert picture description here

Foreign key deleted successfully !

1.13.7 Add a foreign key constraint ( Many to many )

– Existing students (stu) Watch and teacher (tea) surface :

– Create student table

create table stu(
stu_id int primary key auto_increment, -- Student number 
name varchar(20) -- The student's name 
);

– Create a teacher list

create table tea(
tea_id int primary key auto_increment, -- Teacher number 
name varchar(20) -- Teacher's name 
);

– Add third party tables (stu_tea) The relationship between student table and teacher table

– Create a relationship table between students and teachers

create table stu_tea(
stu_id int, -- Student number 
tea_id int, -- Teacher number 
primary key(stu_id,tea_id), -- Set union primary key 
foreign key(stu_id) references stu(stu_id), -- Add foreign keys 
foreign key(tea_id) references tea(tea_id) -- Add foreign keys 
);

In order to prevent duplicate data , take stu_id and tea_id Set as union primary key . The joint primary key is a separate and repeatable key in the third table , But add up and don't repeat .

take stu_id Set to foreign key , Reference resources stu In the table stu_id Column

And will tea_id Set to foreign key , Reference resources tea In the table tea_id Column

1.13.8 update cascade 、 cascading deletion

– establish db20 library 、dept surface 、emp Table and insert record

– Delete db20 library ( If there is ), And recreate db20 library

drop database if exists db20;
create database db20 charset utf8;
use db20;

– Create department table , requirement id, name Field

create table dept(
id int primary key auto_increment, -- Department number 
name varchar(20) -- Department name 
);

– Insert records into the Department table

insert into dept values(null, ' Finance Department ');
insert into dept values(null, ' The personnel department ');
insert into dept values(null, ' Ministry of Science and Technology ');
insert into dept values(null, ' The sales department ');

– Create an employee table , requirement id, name, dept_id

create table emp(
id int primary key auto_increment, -- Employee number 
name varchar(20), -- Employee name 
dept_id int, -- Department number 
foreign key(dept_id) references dept(id) -- Designated foreign key 
on update cascade -- update cascade 
on delete cascade -- cascading deletion 
);
insert into emp values(null, ' Zhang San ', 1);
insert into emp values(null, ' Li Si ', 2);
insert into emp values(null, ' Lao Wang ', 3);
insert into emp values(null, ' Zhao Liu ', 4);
insert into emp values(null, ' Lennon ', 4);

update cascade : Main table (dept surface ) When the primary key in is updated ( For example, the Sales Department id Change it to 40), From the table (emp surface ) The foreign key data recorded in will also follow the table ( That is, the Department numbers of Zhao Liu and Liu Neng will also be updated to 40)

cascading deletion : If you don't add cascading delete , When deleting a department in the Department table ( Delete... For example 4 Department No ), If the Department has a corresponding employee in the employee table ( Zhao Liu and Liu Neng ), Deletion will fail !

If cascade deletion is added , When deleting a department in the Department table , If the Department has a corresponding employee in the employee table , At the same time as deleting the Department , Delete the corresponding employee in the employee table !

1.13.9 where Column aliases cannot be used in

But you can use table aliases , And once the table alias is specified · The following indicates that the table alias should be used to replace .
SQL The writing order of sentences :

select * | Name -- Determine which columns to query 
from Table name -- Determine which table to query 
where Conditions -- By filtering , Eliminate unqualified records 
group by Grouped Columns -- Specify which column to group by 
having Conditions -- Filter the grouped data through conditions 
order by Sorted columns -- Specify which column to sort by 
limit (countPage-1)*rowCount, rowCount -- Specify which page of records to return and how many records to display per page 

SQL The order in which statements are executed :

from Table name -- Determine which table to query 
where Conditions -- By filtering , Eliminate unqualified records 
select * | Name Column alias -- Determine which columns to query ,
group by Grouped Columns -- Specify which column to group by 
having Conditions -- Filter the grouped data through conditions 
order by Sorted columns -- Specify which column to sort by 
limit (countPage-1)*rowCount, rowCount

**** About where You can't use column aliases in, but you can use table aliases ?**

Because , Table aliases are declared in from in ,from Precede where perform , It's OK to declare before using , But column aliases are declared in select in ,where Precede select perform , If you use the column alias first , Declare again , This implementation will report an error !!

unit02-JDBC

Learning goals :

  • Understand what is JDBC? Why learn this technology ?
  • Master through JDBC Connect to and access the database
  • master PreparedStatement The use of transmitters (SQL Injection attack )
  • Learn what connection pooling is ? Why use connection pools ?
  • master C3P0 Connection pool usage
  • Database transactions ( Let's talk about it later , Before frame )

2.1 JDBC summary


2.1.1 What is? JDBC? Why study JDBC?

Because we will the ultimate purpose of the database Is used in software or systems , such as : Do a Jingdong website, there are a lot of data storage , Search a mobile phone , At the same time, millions of people search , It is impossible for background personnel to query manually one by one , It's all executed by program sql Add, delete, modify and check the data . The previous study is just for practice , It is convenient to carry out some simple operations during development . This place lays the foundation for the learning framework .

JDBC(Java DataBase Connectivity) Java Database connection // base: Basics

In fact, that is utilize Java Language / A technique in which programs connect to and access databases

Before we could pass CMD perhaps navicat And other tools to connect to the database

But in enterprise development , More through the program (Java Program ) Connect to and access the database , adopt Java Program access database , It needs to be used JDBC This technology .

 Insert picture description here

2.1.2 How to use JDBC Program access database ?

1、 Put forward requirements :

Create a jt_db database , Create a... In the library account surface , And insert three records , And then use it Java The program finds out account All the records in the table , And print the results of the query on the console .

2、 Development steps :

(1) Prepare the data , establish jt_db library , establish account surface

drop database if exists jt_db;
create database jt_db charset utf8;
use jt_db;
create table account(
id int primary key auto_increment,
name varchar(50),
money double
);
insert into account values(null, 'tom', 1000);
insert into account values(null, 'andy', 1000);
insert into account values(null, 'tony', 1000);

If you have already performed the "SQL Script files ", This step can be skipped .

(2) establish JAVA engineering :

 Insert picture description here

(3) Import jar package ——mysql Drive pack :

 Insert picture description here

(4) Create a class and implement JDBC Program ( Six steps )

 Insert picture description here

Code implementation :

/* * Realization jdbc Quick start program for : Inquire about jt_db.account All records in the table * syst: Print the class name of the current class + Method name */
public static void main(String[] args) throws Exception {

//1. Register database driver ( Parameter is Driver The fully qualified class name of the class ) -- Throw exceptions 
// Need to import a mysql Drive pack , because java Programs cannot directly access other people's databases ( The underlying code knows nothing ), and mysql Manufacturers in order to make java Developers access this database specifically provided jar Package to access the database Such as :mysql-connector-java.5.1.3.jar, Add, delete, modify and check the database . Item right click ----Folder( Catalog : Put some files , Follow src It's a peer relationship )---ctrl+v Occupy this directory . But this step is only in the project , If you want to use it, you need to reference :jar Package right click ---Build Path---Add to Build Path( A referenced library will appear Referenced Libraries) If it needs to be updated in the future jar package , You need to remove the referenced library first ( Right click --Build Path--Remove ...)
Class.forName("com.mysql.jdbc.Driver");
//2. Get database connection ( Connected to the database url Address , The user name of the database , Database password )
// The first parameter explains :jdbc:mysql: Is the connection mysql Fixed writing of , It's actually an agreement .
// //localhost:3306 Local host : Port name 
// /jt_db? Which library is connected 
// characterEncoding=utf-8 Specify a value character set of utf-8 Encoding: hold ... code , This parameter is to prevent garbled code .
//DriverManager: Drive manager Connection : Connect This method will return a connection object , guide sql package 
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8",
"root", "root");
//3. Get the transmitter through the connection Statement explain 
Statement stat = conn.createStatement();
//4. Send... Through the transmitter SQL Go to the server to execute , And return the execution result 
String sql = "select * from account";
ResultSet rs = stat.executeQuery( sql );// executeQuery Used to perform a lookup type sql sentence execute: perform query: Inquire about The returned result is the table , You can use the result set object to receive 
//5. Output results ( Output each line of record to the console )
// In the table of the result set object , There is an arrow in the header ,boolean next()-- Move the arrow down one line , Returns a Boolean value , The first row has data returned true, No data returned false. Get data when you have it . 
while( rs.next() ) {

int id = rs.getInt("id"); // Pass the list in ,id yes int type 
String name = rs.getString("name"); //name yes String type 
double money = rs.getDouble("money");
System.out.println(id+" : "+name+" : "+money);
}
//6. Release resources ( The later you get it, the more you close it first )
rs.close();
stat.close();
conn.close();
System.out.println("TestJdbc.main()....");
}

3、 Execution results :

 Insert picture description here

2.1.3 JDBC API summary 888888888888

1、 Register database driver

Class.forName(“com.mysql.jdbc.Driver”); // Fully qualified class name : Package name + Class name ( Drivers must be registered in the early stage , stay jdbc4.0 Then it will automatically register , You can omit this line , But usually we add it to prevent mistakes ) The full qualified class name is in the package after the reference , Expand the referenced package —com.mysql.jdbc— Then expand Driver Drive class , The bottom layer loads this class through reflection to obtain bytecode objects , Here is the static code block that loads this class and executes it , There is only one line of statement in the static code block to really register the code . The essence of this method is to let the registration driven code execute , It doesn't register itself .

The so-called registration driver , Is to make JDBC Program loading mysql The driver , And manage the driver

The driver implements JDBC API Defined interfaces and the ability to interact with the database server , The purpose of loading drivers is to facilitate the use of these functions .

2、 Get the connected database URL // amount to java The connection channel between the program and the database ( The parameters inside can't be written wrong ), This passage is Connection

// If the port is the default, you can omit :3306, Connected to the local host , Host name ( namely ip Address ) You can omit it , '/' Can't save

Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8",
"root", "root" );

DriverManager.getConnection() Used to get data connections , Back to Connection The connection object is JDBC The program connects to an object of vital importance to the database .

Parameters 2 and Parameters 3 They are the user name and password of the connected database .

Parameters 1:“jdbc:mysql://localhost:3306/jt_db” It's connected to the database URL, Used to specify where to access the database server and which database in the server , It's written as :
 Insert picture description here

When connecting to a local database , And the port is 3306, It can be abbreviated as follows :

jdbc:mysql:///jt_db

3、Statement Transmitter object

Statement stat = conn.createStatement();
This method returns for sending sql Of the statement Statement Transmitter object

Send... Is provided on this object sql Methods :

executeQuery(String sql) -- Only queries return a result set
Used to send query types to a database sql sentence , Return to one ResultSet In the object
executeUpdate(String sql) -- Addition, deletion and modification return a value, not an object
Used to send updates to the database ( increase 、 Delete 、 modify ) Type of sql sentence , Return to one int value , Represents the number of record rows affected

4、ResultSet Result set object

ResultSet Objects are used to encapsulate sql Statement query results , It's also a very important object . This object provides the method of traversing data and getting data .

(1) How to traverse data rows

next() – Move the arrow pointing to the data row down one line , And return a boolean result ,true Indicates that the arrow points to a row of data ,false Indicates that the arrow does not point to any data ( There's no more data in the back )

(2) How to get data

getInt(int columnIndex) //column index Column index 
getInt(String columnLable)
getString(int columnIndex)
getString(String columnLable)
getDouble(int columnIndex)
getDouble(String columnLable)
getObject(int columnIndex)
getObject(String columnLable)

5、 Release resources

rs.close();
stat.close();
conn.close();

Resources released here must be released in a certain order , The later you get it, the more you close it first . So shut down
rs object , To shut down stat object , Finally close conn object .

another , To avoid the above program throwing an exception , The code that releases the resource does not execute , The code that releases resources should be placed in finally In block .

try{
 // Learning the framework will help you release 
...
}catch(Exception e){

...
}finally{

if (rs != null) {

try {

rs.close();
} catch (SQLException e) {

e.printStackTrace();
} finally {

rs = null;
}
}
if (stat != null) {

try {

stat.close();
} catch (SQLException e) {

e.printStackTrace();
} finally {

stat = null;
}
}
if (conn != null) {

try {

conn.close();
} catch (SQLException e) {

e.printStackTrace();
} finally {

conn = null;
}
}
}

2.2 Additions and deletions


2.2.1 JDBC Additions and deletions

1、 newly added : Go to account Add a name to the table john、money by 3500 The record of

/* 1、 newly added : Go to account Add a name to the table john、money by 3500 The record of */
@Test
public void testInsert() {

Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {

// Register the driver and get the connection 
conn = JdbcUtil.getConn();
// Get the transmitter 
stat = conn.createStatement();
// send out sql Statement to the server to execute , And return the execution result id The primary key is automatically incremented without giving a value , There's no semicolon in it 
String sql = "insert into account values(null, 'john', 3500)";
int rows = stat.executeUpdate( sql ); // Returns the influence function 
// Processing results 
System.out.println( " Number of rows affected : "+rows );
} catch (Exception e) {

e.printStackTrace();
} finally {

// adopt JdbcUtil In a tool class close Method to release resources , Why release resources , Connection is a channel , After configuration, the connection is fixed , Keep getting connections and run out sooner or later , The back is not connected . When testing, test one by one , Your direct run as Is to test all methods , And the order is not in the order you wrote .
JdbcUtil.close(conn, stat, rs);
}
}

2、 modify : take account The name in the table is john The record of ,money It is amended as follows 1500

/* 2、 modify : take account The name in the table is john The record of ,money It is amended as follows 1500 */
// To reduce code front 2 The steps are almost the same , Except that the host name and port may change in the future ( You can set the parameter type ), So we can extract it into a tool class .
package com.tedu.util;
import java.sql.Connection;
import java.sql.DriverManager;
/** * jdbc Tool class of * @author Administrator * */
public class JdbcUtil {

// Get database connection object It's from Beijing Default user password 
public static Connection getConn() {
 // In order to facilitate the call, set as static public 
try {

// Registration drive 
Class.forName("com.mysql.jdbc.Driver");
// Get the connection 
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8","root","root");
return conn;
} catch (Exception e) {

// TODO: handle exception
e.printStackTrace();// Print the type of exception 
}
return null;// There is a return value 
}
/* It says dead , In order to facilitate later modification, it is changed to parameter type * Get database connection object * dbName The name of the connection database * user User name of the connected database * psw The password of the connected database * Connection Connection object */
public static Connection getConn(String dbName,String user,String psw) {

try {

// Registration drive 
Class.forName("com.mysql.jdbc.Driver");
// Get the connection hold dbName Spliced inside "+dbName +", It's a string directly. It has nothing to do with variables hinder 2 The quotation marks should also be removed 
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/"+dbName+"?characterEncoding=utf-8",user,psw);
return conn;
} catch (Exception e) {

// TODO: handle exception
e.printStackTrace();
}
return null;
}
}
// Test class 
@Test
public void testupdate() throws Exception{

// Register to get a connection 
Connection conn=JdbcUtil.getConn();
// Get the transmitter , And implement sql sentence , Return execution result 
Statement stat=conn.createStatement();
//john It's a string , stay sql There are single quotes in the statement , Think of it as a column here 
String sql="update account set money=1500 where name='john'";
int rows=stat.executeUpdate(sql);
System.out.println(" Number of rows affected :"+rows);
stat.close();
conn.close();
}

3、 Inquire about : Inquire about account The name in the table is john The record of

Only Inquire about What is returned is result , Additions and deletions All of them Number of rows affected .

/* 3、 Inquire about : Inquire about account In the table id by 1 The record of */
@Test
public void testFindById() {

Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {

// Register the driver and get the connection 
conn = JdbcUtil.getConn();
// Get the transmitter 
stat = conn.createStatement();
// perform sql sentence , Return execution result 
String sql = "select * from account where id=1";
rs = stat.executeQuery( sql );
// Processing results 
if( rs.next() ) {

int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println( id+" : "+name+" : "+money);
}
} catch (Exception e) {

e.printStackTrace();
} finally {

JdbcUtil.close(conn, stat, rs);
}
}

4、 Delete : Delete account The name in the table is john The record of

/* 4、 Delete : Delete account The name in the table is john The record of */
@Test
public void testdelete() throws Exception{

// Register to get a connection Call the method of the tool class 
Connection conn=JdbcUtil.getConn();
// Get the transmitter , And implement sql sentence , Return execution result 
Statement stat=conn.createStatement();
String sql="delete from account where name='john'";
int rows=stat.executeUpdate(sql);
System.out.println(" Number of rows affected :"+rows);
stat.close();
conn.close();
}
}

2.2.2 Unit test supplement

Report errors : It's the wrong message , Here is the wrong location .

unit testing : No need to create new classes , No need to provide main function , You don't have to create an instance of a class , You can execute a method directly ( For testing purposes )

added @Test Method of annotation , Can pass unit testing (junit) The framework tests the method . The underlying layer creates an instance of the class in which the method resides , Call the method through an instance . ( The first time you need to add a library , The second time, you only need to guide the package Only need Test Click on the comment )

Run in the normal way : Right click run as–java Application

@Test -- function : Choose ( Double click to select ) Method name , Right click run as --junit test You need to add a library for unit tests ,junit4,5 Will do
public void testInsert() {

System.out.println("TestPreparedStatement.testInsert()");
}

Able to use @Test The method of unit testing must satisfy the following conditions :

(1) Methods must be public
(2) Methods must be non static
(3) Method must have no return value
(4) Methods must be parameterless
(5) Methods or classes for unit testing , Don't name it as Test/test

2.3 PreparedStatement


In the above operation of adding, deleting, modifying and querying , It uses Statement Transmitter object , The more transporter objects we use in development are PreparedStatement object ,PreparedStatement yes Statement Sub interface of , Than Statement More secure , And it can improve the efficiency of program execution .

Statement parent object

PreparedStatement Sub object

2.3.1 Simulate user login cases

(1) Prepare the data

use jt_db;
create table user(
id int primary key auto_increment,
username varchar(50),
password varchar(50)
);
insert into user values(null,' Zhang San ','123');
insert into user values(null,' Li Si ','234');

(2) establish LoginUser class , Provide main Method and login Method .

public class LogininUser {

public static void main(String[] args) throws Exception {

Scanner sc=new Scanner(System.in);
//1.t Prompt the user to log in , Prompt the user to enter a user name , And accept the user name 
System.out.println(" Please log in :");
System.out.println(" Please enter a user name :");
String user=sc.nextLine();
//2. Prompt the user for login password , And accept the password 
System.out.println(" Please input a password ");
String psw=sc.nextLine();
//3. Pass the accepted user name and password to login Method , To realize the login 
login(user,psw);
}
/* * Search by user name and password user surface , Log in * user user name * psw password */
private static void login(String user, String psw) throws Exception {
// Because it is used in this class, you can set private .
// TODO Auto-generated method stub
// Get the driver and connect 
Connection conn=JdbcUtil.getConn();
// Get the transmitter , And implement SQL, Return execution result 
Statement stat=conn.createStatement();
String sql="select * from user where username='"+user+"' and password='"+psw+"'"; // Add special symbols to make sql The meaning of the sentence has changed , This is the injection attack . Pay attention to using single quotation marks , If double quotation marks change the meaning ( They are all double quotation marks. I don't know where to separate them )
// Such as : Zhang Fei '#' # It's a comment , None of the latter will execute 
// Zhang Fei 'or' 2=2 If one sql At the same time and and or, and priority 
System.out.println(sql);
ResultSet rs=stat.executeQuery(sql);
// Processing results 
if(rs.next()) {
 //true, Indicates that there is data -- The user name and password are correct , Allow to log in 
System.out.println(" Congratulations on your successful login ");
}else {
//false. Indicates that there is no data -- Wrong user name or password , Login failed 
System.out.println(" Wrong user name or password , Login failed !");
}
rs.close();
stat.close();
conn.close();
}
}

Execution time , Input :

 Please log in :
Please enter a user name :
Zhang Fei '#'
Please input a password :
select * from user where username=' Zhang Fei '#'' and password=''
Congratulations on your successful login !

Or input

 Please log in :
Please enter a user name :
Zhang Fei ' or '1=1
Please input a password :
select * from user where username=' Zhang Fei ' or '1=1' and password=''
Congratulations on your successful login !

Or input

 Please log in :
Please enter a user name :
Please input a password :
' or '2=2
select * from user where username='' and password='' or '2=2'
Congratulations on your successful login !

2.3.2 SQL Injection attack

Through the above case , We found that in execution , You can log in successfully without entering a password and only a user name . This is it. SQL Injection attack .

SQL The reason for the injection attack : because Executed in the background SQL The sentence is a concatenation

select * from user where username='"+user+"' and password='"+pwd+"'

The parameters are submitted by the user , If the user submits the parameters , Some of the parameters are doped with SQL keyword ( such as or) Or special symbols (#、-- 、’ etc. ), It may lead to SQL The change of sentence semantics , To perform some unexpected operations ( Incorrect user name or password can also login successfully )!

2.3.3 prevent SQL Injection attack

How to prevent SQL Injection attack ?

(1) Use regular expressions to verify the parameters submitted by users . If there is (# – ’ or etc. ) These symbols end the program directly , Inform the user that the input parameter is illegal

(2) Use PreparedStatement Object to replace Statement object . (prepare: Ready )

The second way is to solve the problem SQL Injection attack : add to loginByPreparedSatement Method , In the method , Use PreparedStatement Instead of Statement Use as a transmitter object , Code example :

// Copy file class : direct ctrl+c ctrl+v

After operation : Red is not necessarily an error message eror exception
Change the file name On the file f2

package com.tedu.ps;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import com.tedu.util.JdbcUtil;
/** * Simulate user login cases -- May produce SQL Injection attack security issues * PreparedStatement Object to replace Statement object . * @author Administrator * */
public class LogininUser2 {

public static void main(String[] args) throws Exception {

Scanner sc=new Scanner(System.in);
//1.t Prompt the user to log in , Prompt the user to enter a user name , And accept the user name 
System.out.println(" Please log in :");
System.out.println(" Please enter a user name :");
String user=sc.nextLine();
//2. Prompt the user for login password , And accept the password 
System.out.println(" Please input a password ");
String psw=sc.nextLine();
//3. Pass the accepted user name and password to login Method , To realize the login 
login(user,psw);
}
/* * Search by user name and password user surface , Log in * user user name * psw password */
private static void login(String user, String psw) throws Exception {

// TODO Auto-generated method stub
// Get the driver and connect 
Connection conn=JdbcUtil.getConn();
// Get the transmitter , And implement SQL, Return execution result 
// Statement stat=conn.createStatement();
// String sql="select * from user where username='"+user+"' and password='"+psw+"'"; // Add special symbols to make sql The meaning of the sentence has changed 
// System.out.println(sql);
// ResultSet rs=stat.executeQuery(sql);
// This place will pass the skeleton , If it is a character or date, a single quotation mark will be automatically added , If it is a string, there will be no single quotation marks 
String sql="select * from user where username=? and password=?";
// take SQL Pass the skeleton to PreparedStatement Method , obtain ps Transmitters 
PreparedStatement ps=conn.prepareStatement(sql);// When you create this transmitter, you will pass a parameter , So you need to declare .(? It's a placeholder , This place can't be spliced directly like the top , Direct splicing has the same injection attack problem as the above problem .)
// Set up SQL Parameters in ( A few question marks , Just set a few parameters , The first parameter indicates the number of question mark setting parameters , from 1 Start )
ps.setString(1, user);// Only placeholders can be used to set parameters 
ps.setString(2, psw);// Pass the skeleton first ( A single skeleton cannot perform ), Then pass the parameters , Make sure the skeleton won't change again The type depends on the parameters you need to pass in 
// perform sql sentence 
ResultSet rs=ps.executeQuery();// Don't pass in sql sentence ! Will report an error and put the front sql Cover 
// Processing results 
if(rs.next()) {
 //true, Indicates that there is data -- The user name and password are correct , Allow to log in 
System.out.println(" Congratulations on your successful login ");
}else {
//false. Indicates that there is no data -- Wrong user name or password , Login failed 
System.out.println(" Wrong user name or password , Login failed !");
}
// Release resources When talking about the framework in the future, it will help you release 
rs.close();
ps.close();
conn.close();
}
}

Execute the program again , Log in as above . here , It has been successfully prevented SQL Injection attack problem .

PreparedStatement The object is how to prevent SQL Inject the attack

Use PreparedStatement The object is to put SQL Of the statement The skeleton is sent to the server for compilation and determination , After the compilation ,SQL The skeleton and semantics of the statement will no longer be changed , then SQL The parameters in the statement are sent to the server , Even if the parameter contains SQL Keywords or special symbols , It will not lead to SQL The skeleton or semantics of a statement is changed , Will only be treated as normal text !

 String sql = "select * from user where username=? and password=?";
// take SQL Pass the skeleton to prepareStatement Method , obtain ps Transmitters 
PreparedStatement ps = conn.prepareStatement( sql );
// Set up SQL Parameters in statement ( A few question marks , Just set a few parameters , The first parameter represents the parameter set for the number of question marks , from 1 Start )
ps.setString( 1 , user );
ps.setString( 2 , psw );
// perform SQL sentence 
ResultSet rs = ps.executeQuery( );

In the execution of the above SQL When the sentence is , The server will SQL Parameters and SQL skeleton Bind together , Finally, a complete SQL sentence .

When replacing placeholders with parameters (?), If the parameter is a string or date type value , Single quotes are placed around the string or date value !, If it is a numerical value, it will not be enclosed in single quotation marks .

advantage :

Use PreparedStatement Objects can prevent SQL Injection attack

And it is more convenient and error free to set parameters through methods !

It can also improve the efficiency of program execution in some ways !

Be careful 1. When to use Statement When to use PreparedStatement?

If it is Inside sql When the statement has no parameters, that is the same , For example, when making a query .

  1. If sql If the statement has no placeholder , Under the transfer skeleton, you don't need to set parameters, and you can execute it directly .

2.4 Database connection pool

2.4.1 What is connection pooling

pool : A piece of space in memory ( Containers , For example, array 、 aggregate )

Connection pool : It's storing the connection in a container , For the entire program to share , It can realize connection reuse , Reduce the number of connections created and closed , So as to improve the efficiency of program execution !
 Insert picture description here

2.4.2 Why use connection pools

1、 The traditional way to operate the database

 Insert picture description here

Connection conn = DriverManager.getConnection( url, user, pwd );
// Create connection objects
conn.close(); // Close the connection , Destroy connection

In the traditional way , Every time a user needs to connect to a database , All are Create a connection object , Access the database based on this connection object , After using the connection , Will Connection is closed (conn.close).

Because it takes a lot of time and resources to create and close a connection each time , Therefore, it will lead to the inefficiency of program execution .

2、 Use connection pooling to operate the database

 Insert picture description here

When the program starts , Just create a batch of connections and put them in a connection pool ( Containers ), When users need to connect , Get a connection object from the connection pool , After using the connection , Do not shut down. , Instead, return the connection back to the connection pool , thus , It is used to use this batch of connections in the pool , The reuse of connection is realized , Reduces the number of connection creation and closure , Thus, the efficiency of program execution is improved !

2.4.3 How to use C3P0 Connection pool

dbcp/c3p0/druid The first 3 Provided by , No java It comes with it, so Need to guide package , All connection pooling technologies need to be implemented Sun A standard formulated by the company is interface .

All connection pools implement an interface ——DataSource( data source ), So connection pools are also called data sources

Use C3P0 Connection pool development steps :

1、 Import the development package

 Insert picture description here

2、 Create a database connection pool ( object )

ComboPooledDataSource cpds = new ComboPooledDataSource();

3、 Set the basic information of the connection database

Total test : Note that the red message is not necessarily wrong , Generally, there are Exception Error It's a mistake

syntax There is something wrong with the grammar ( grammar ) File: file Folder: Folder Change file name : Choose — Right click –Rename( Rename ) Or select... On the file F2

package com.tedu.c3p0;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.tedu.util.JdbcUtil;
/*
* test c3p0 The use of connection pools Usage and the above jar The bag is almost , Copy first Folder Right click to reference
* (1) Import c3p0 Development package for (jar package ) Early download on the official website , Now use project management tools Maven Unified management download jar package .
* (2) Create a connection pool object in the program ( Containers : There will be a batch of connections )
* (3) Set the basic information of the connection data ( The full path of the driver class , Link library url Address , User name and password of the link library )
* Mode one : Write parameters directly into the program , If there are changes in the future , You need to change the program , It will bring a lot of unnecessary trouble
* Mode two : Connect the basic configuration information to the database c3p0.properties Files in src The root directory , Information that needs to be modified in the future , Just change what's in this file . The name cannot be changed , This is the individual .
* Mode three : Connect the basic configuration information to the database c3po-config.xml In file , Files in src Under the table of contents , Click on --source Enter edit mode .( The teacher has given this document to )
* The file name positions in mode 2 and mode 3 are fixed , Can't change , because c3p0 Will automatically read these questions
Mode two and 3、 ... and Our files don't need to be read by ourselves , from c3p0 Automatically to this src Read from the directory under , Just put the written document in this src Just under the root directory
*
* (4) Get a connection object from the connection pool and use it
* ....
* (5) Switch the connection back to the connection pool
*/
public class TestC3p0 {
// Create a connection pool object It can be placed inside the method , Class internal If you import into a package ,ComboP+alt /, There will be hints. .
static ComboPooledDataSource pool =new ComboPooledDataSource(); // Static for convenience of calling
// Set the basic information of the connection database ( Mode one , It is not recommended to use !)
// static {// Put it in the static block because it can be called in the entire class. , It can also be put in the method, but it can only be used in the method .
// try { Create an object above and add static Because in static code blocks , Static can only be adjusted
// pool.setDriverClass("com.mysql.jdbc.Driver");
// pool.setJdbcUrl("jdbc:mysql:///jt_db?characterEncoding=utf-8");
// pool.setUser("root");
// pool.setPassword("root");
// } catch (PropertyVetoException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();// Displays the exception type
// }
// }
// Query all user information in the table
@Test
public void testFindAll() throws Exception {
// Registration drive , And get the connection ( Create an object ), Instead, get an existing connection object from the connection pool
//Connection conn=JdbcUtil.getConn();
Connection conn=pool.getConnection();
// Get the transmitter
PreparedStatement ps=conn.prepareStatement("select*from user");
// perform aql sentence , Return execution result
ResultSet rs=ps.executeQuery(); // Execute the query
// Output results
while(rs.next()) {
int id=rs.getInt(1); //1 Indicates to get the first column of data in the current row
String username=rs.getString(2); //2 Indicates to get the second column of data in the current row
String psw=rs.getString(3); //3 Indicates to get the data of the third column in the current row
System.out.println(id+","+username+","+psw);
}
// Release resources
rs.close();
ps.close();
conn.close();
/*
* If the current connection object , It is a connection object created by ourselves , call conn.close The default method is to connect
* close ( That is to release )
* If this connection object , It is obtained from the connection pool , When the connection pool returns this connection object , Will transform the function of the object ( Method )-- take conn Of close The method is modified to also connect to the connection pool , Instead of closing the connection . In other words, the original closed connection is returned to the connection pool instead of being closed , It will automatically transform .
*/
}
}
Method 2: content of file configuration , Like key value pairs, but not :
# key=value // This place can't be quoted , Corresponding 4 Parameters in a method On the left is fixed writing dead
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUr1=jdbc:mysql:///jt_db?characterEncoding=utf-8
c3p0.user=root
c3p0.passworld=root

(1) Mode one :( Not recommended ) Pass the parameter directly through pool.setXxx Method is set to c3p0 Program

In this way, the parameters are directly written into the program , Later, once the parameters change , It's going to change the program , To recompile the project 、 Republish project , Very trouble .

// Set the basic information of the connection database 
pool.setDriverClass( "com.mysql.jdbc.Driver" );
pool.setJdbcUrl( "jdbc:mysql:///jt_db?characterEncoding=utf-8" );
pool.setUser( "root" );
pool.setPassword( "root" );

(2) Mode two : Extract the connection parameters to properties In file ( recommend ) establish File file

Documents must be placed in src( Source root ) Under the table of contents !

The file name must be called c3p0.properties !

In the class directory ( It can be developed in src Or similar source directory ), Add one c3p0.properties file , The configuration is as follows : ( Cannot quote

Sinister key Fixed , Dexter value Depending on the situation . notes :#

#key=value This is a properties The notes in the file
c3p0.driverClass=com.mysql.jdbc.Driver # Set the full class name of the driver class
c3p0.jdbcUrl=jdbc:mysql:///jt_db?characterEncoding=utf-8 # Connected to the database url Address
c3p0.user=root // User name to connect to the database
c3p0.password=root // Password to connect to the database

This way is because c3p0 Go to the specified location to find the object with the specified name properties file , So the location of the file must be src Or other source code root directory , The file name must be c3p0.properties.

(3) Mode three : Extract the connection parameters to xml In file ( recommend )

Documents must be placed in src( Source root ) Under the table of contents !

The file name must be called c3p0-config.xml

In the class directory ( It can be developed in src Or similar source directory ), Add one c3p0-config.xml file , The configuration is as follows : Click on source Enter edit mode .

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///jt_db?characterEncoding=utf-8</property>
<property name="user">root</property>
<property name="password">root</property>
</default-config>
</c3p0-config>

This way is because c3p0 Go to the specified location to find the object with the specified name xml file , So the location of the file must be src Or other source code root directory , The file name must be c3p0-config.xml.

4、 Get a connection object from the connection pool and use it

Connection conn = pool.getConnection();

5、 Return the connection back to the connection pool after using it

JdbcUtil.close(conn, ps, rs);
//conn.close()
/* If it's a self created connection object , This connection object has not been changed , call * conn.close Method , Is to close the connection object * If it is a connection object obtained from the connection pool , The connection object has been pooled on return * Transformed , Will connect the close Method to also connect to the connection pool */

2.5 Expand : Switching workspaces , Change the default encoding


2.5.1 Switch to a new workspace , Set the workspace code to utf-8

(1) Switch to a new workspace destination : If the old workspace has too much content , May lead to eclipse Don't compile , Even go to sleep .

(2) Set the workspace code to utf-8, After that, the project code created in the current workspace also defaults to utf-8.

 Insert picture description here

2.5.2 Set up JSP The code of the file is utf-8

 Insert picture description here

2.6 Expand :JDBC Realize the student information management system


B Station video link :https://www.bilibili.com/video/BV1ka4y1x7M6

2.6.1 Prepare the data

The statement of creating database and table is as follows :

-- 1、 Create database jt_db database ( Create... If it doesn't exist )
create database if not exists jt_db charset utf8;
use jt_db; -- choice jt_db database 
-- 2、 stay jt_db Create... In the library stu surface ( Student list )
drop table if exists stu;
create table stu(
id int,
name varchar(50),
gender char(2),
addr varchar(50),
score double
);
-- 3、 Go to stu In the table , insert record 
insert into stu values(1001,' Zhang San ',' male ', ' Beijing ', 86);
package com.tedu.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/** JDBC Tool class */
public class JdbcUtil {
/**
* Get the database connection object and return
* @return Connection object
* @throws Exception
*/
public static Connection getConn() throws Exception{
//1. Registration drive
Class.forName( "com.mysql.jdbc.Driver" );
//2. Get the connection
Connection conn = DriverManager.getConnection(
"jdbc:mysql:///jt_db?characterEncoding=utf-8",
"root",
"root");
return conn;
}
/**
* Release JDBC Resources in the program
* @param conn Connection object
* @param stat Transmitter object
* @param rs Result set object
*/
public static void close(Connection conn,
Statement stat, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally{
rs = null;
}
}
if(stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally{
stat = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally{
conn = null;
}
}
}
}
package com.tedu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
import com.tedu.util.JdbcUtil;
/**
* Student information management system
* Run the program , According to the options , You can add, delete, modify and check student information .
*/
public class StudentManager {
private static Scanner sc = new Scanner(System.in);
public static void main(String[] args) {
while (true) {
//1. Prompt to select operation options
System.out.print("a: Search for student information ");
System.out.print("b: Add student information ");
System.out.print("c: Modify student information ");
System.out.println("d: Delete student information ");
System.out.print(" Please input the operation ,abcd Choose any one :");
String opt = sc.next();
//2. Depending on the options
if( "a".equalsIgnoreCase( opt ) ){
findAll(); // Search for student information
}else if( "b".equalsIgnoreCase( opt ) ){
addStu(); // Add student information
}else if( "c".equalsIgnoreCase( opt ) ){
updateStu(); // Modify student information
}else if( "d".equalsIgnoreCase( opt ) ){
deleteStu(); // Delete student information
}else{
System.out.println(" Input error , Please re-enter !");
}
}
}
/** a. Check all student information */
private static void findAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConn();
String sql = "select * from stu";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String gender = rs.getString("gender");
String addr = rs.getString("addr");
double score = rs.getDouble("score");
System.out.println( id+","+name+","+gender
+","+addr+","+score );
}
System.out.println("------------------------------------------------------------------------------");
} catch (Exception e) {
e.printStackTrace();
}
}
/** b. Add student information */
private static void addStu() {
System.out.println(" Please enter the student number you want to add 、 full name 、 Gender 、 Address 、 achievement ( Use spaces to separate ):");
int id = sc.nextInt();
String name = sc.next();
String gender = sc.next();
String addr = sc.next();
double score = sc.nextDouble();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConn();
String sql = "insert into stu values(?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ps.setString(2, name);
ps.setString(3, gender);
ps.setString(4, addr);
ps.setDouble(5, score);
int rows = ps.executeUpdate();
if (rows > 0) {
System.out.println(" Add success !");
}
} catch (Exception e) {
e.printStackTrace();
}
}
/** c. according to id Modify student information */
private static void updateStu() {
System.out.println(" Please enter the student number you want to modify 、 full name 、 Gender 、 Address 、 achievement ( Use spaces to separate ):");
int id = sc.nextInt();
String name = sc.next();
String gender = sc.next();
String addr = sc.next();
double score = sc.nextDouble();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConn();
String sql = "update stu set name=?,gender=?"
+ ",addr=?,score=? where id=?";
ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, gender);
ps.setString(3, addr);
ps.setDouble(4, score);
ps.setInt(5, id);
int rows = ps.executeUpdate();
if (rows > 0) {
System.out.println(" Modification successful !");
}
} catch (Exception e) {
e.printStackTrace();
}
}
/** d. according to id Delete student information */
private static void deleteStu() {
System.out.println(" Please enter the student number you want to delete :");
int id = sc.nextInt();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConn();
String sql = "delete from stu where id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
int rows = ps.executeUpdate();
if (rows > 0) {
System.out.println(" Delete successful !");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

2.6.2 Function realization

Run the program console prompt as follows :

 Insert picture description here

Input a: Check all student information

Input b: Add student information

Input c: according to id Modify student information

Input d: according to id Delete student information

2.6.3 Check all student information

Enter the operation code in the console "a", The effect is as follows :

 Insert picture description here

2.6.4 Add student information

Enter the operation code in the console "b", The effect is as follows :

 Insert picture description here

2.6.5 according to id Modify student information

Enter the operation code in the console "c", The effect is as follows :

 Insert picture description here

2.6.6 according to id Delete student information

Enter the operation code in the console "d", The effect is as follows :

 Insert picture description here

unit03-HTML、CSS

HTML: A technique for developing web pages

CSS: Used to modify 、 A technique for rendering web pages ( To beautify the web )

HTML+CSS Can develop a very beautiful one 、 Very beautiful web page

Develop web pages Build a house

HTML Tags build the structure of a web page brick ( Building the structure of the house )

CSS attribute lime 、 Paint, etc

3.1 HTML summary


3.1.1 HTML What is it?

HTML: Execution order , Browser loading from top to bottom , From left to right .

HTML(Hyper Text Markup Language): Hypertext markup language

Hypertext : Hypertext 、 Beyond the text ( It can contain data other than text , For example, pictures 、 Audio 、 Video and other formats )

Mark : It's also called a label 、 Elements 、 Nodes etc. , It's using angle brackets (<>) It's a set of things to wrap up , for example :

<head> <body> <div> <span> <table> etc.

HTML Is the most basic language for developing web pages .

HTML from W3C Provided by organization (CSS/xml): The organization defines... As an international neutral organization html,css,DOM,xml,js Some codes and standards .

World Wide Web address :W3Cschoole, Novice tutorial , You can learn the previous knowledge on this website .

development tool : In the early : Notepad ,Dreamweaver. Now? :IDEA( The back end also integrates the front segment ),WebStorm( front end ), this 2 All the software is from Czech company , Of course, there are other software and so on .

About HTML The details of the :

(1) Use HTML Web pages are usually developed with .htm or .html For the suffix !

(2) Use HTML The developed web page file is parsed and displayed by the browser ( The browser is a html Parser )

(3)HTML It's a kind of document (txt/word/ppt/pdf etc. )

summary : HTML It's a language for developing web pages !!

alt+/ Write part of the code , Prompt shortcut

If it is :html,css,js Just use Static Web Project

If it is :servlet,jsp Just use Dynamic Web Project

You can use... For the time being eclipse Tools : new --File–other— Search for web----Static Web Project( Whether it is a static project or a dynamic project depends on the requirements , It's used in )— Write a project name -----Finish -- Open the view (opten…),

After it is built, there will be a default root directory (WebCount)---- Right click —new—HTML File( Give a file name : Such as ,01- First page .html)

Precautions for using browser :

​ Select the open file ----Open With---- The default is HTML Editor----- Change it to Web Browser,

But this browser is eclipse Self contained ( Not recommended , There may be mistakes , Use native )

​ How to open locally :1, Selected items —Show In—System Explorer( Open the hard disk location where the project is located , Inside .metadata yes eclipse The environment of your workspace , Deleting this item will restore the factory settings )— Find the created file according to the path — Right click — Open mode ( Too much trouble )

​ 2.Wnidow—Web Brower( Google is generally used , firefox , But it's better not to IE, And local , Not professional enough ), At this time in Select the open file ----Open With----Web Browser, Use an external browser .

If a browser is installed on the computer, for example, how to add it :

windows—preferences—General—Web Browser( second to last )— The second option external External browser ,

Remove( Yes remove )

Add in :new----(Name: Give a name ,Location: Specify the location of the browser , First, you can select Google — Right click to open the file location ---- Click on Browse---- Click enter to paste on the website bar ---- In the selected browser ---- open ,Ok, And then there is )

3.1.2 HTML Structure

1、 Case study : Write my first HTML Webpage , And open it with a browser

Create a new one txt file , Change the suffix to .html, The code implementation is as follows :( Save to take effect )

<!DOCTYPE html>
<html> // The contents below the first line will be placed in , Under the root tag
<head>
<title> The title of the page </title>
</head>
<body>
<h1>Hello CGB2003...</h1> The title inside the page : For example, a news headline , It's a title tag
</body>
</html>
test :
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title> My first web page </title>
</head>
<body>
<h1> Big news : Has Ma Yun ever been to heaven and earth ?</h1>
<!-- 1 The level title is the largest 6 The level title is the smallest <h1> Big news : Has Ma Yun ever been to heaven and earth ?</h1> <h2> Big news : Has Ma Yun ever been to heaven and earth ?</h2> <h3> Big news : Has Ma Yun ever been to heaven and earth ?</h3> <h4> Big news : Has Ma Yun ever been to heaven and earth ?</h4> <h5> Big news : Has Ma Yun ever been to heaven and earth ?</h5> <h6> Big news : Has Ma Yun ever been to heaven and earth ?</h6> -->
<!-- How to add line breaks to web pages : Wrap the content with block level elements , Default exclusive line Or use <br/> label , You can wrap lines <hr/> Is used to draw a horizontal line -->
<div>aaa</div>
<div>bbb</div>
<div>ccc</div>
aaa<br/>
bbb<br/>
ccc<br/>
<!-- How to add spaces to web pages : use &nbsp; perhaps &emsp; Instead of -->
user name :<input/> <br/>
The secret &emsp; code :<input/> <br/>
</body>
</html>

2、HTML The structure,

(1)<!DOCTYPE HTML> The document statement , Used to declare HTML The document follows HTML Specification and version
It's on it html 5.0 Statement of , It's also the most commonly used version ( The default is 5.0), This version can be abbreviated .
(2)<html><html> Root tag , Used to wrap all web content ( Except for the document statement )
(2)<head></head> The first part , For storage HTML Basic attribute information of the document , For example, the title of a web page , The code used by the document, etc , This information will be loaded by the browser first .
(3)<body></body> The body part , Used to store web page visualization data . That's real Web Data
(4)<title></title> Declare the title of the page
(5)<meta charset="utf-8" > Used to inform the browser which code to use to open HTML file , This code must be consistent with the code when the file is saved , There will be no Chinese garbled problem .

3.1.3 HTML grammar ( understand )

1、html label

label : It's also called a marker 、 Elements, etc. , The label is divided into The start tag , for example :

<head><body>

And the end tag , for example : Be careful / in front

</head></body>

There can be other content between the start tag and the end tag .

<head>
<titile> Declare the title of the page </title>
<meta charset="utf-8">
</head>

Some tags have no connection between the start tag and the end tag There's something to wrap , It can usually be written as autistic label , for example :5 individual

<br/> <hr/> <input/> <img/> <link/> etc. Be careful ; This / Put it in the back
Equivalent to <img> </img> But it doesn't usually say that

2、html attribute

You can declare properties on tags ( Attributes cannot exist on their own , Must be declared on the label )

<div id="d1" class="c1" style="color:red;"> This is a div Elements </div>

Multiple attributes can be declared on the tag , Use... Between multiple attributes The blank space to separate

On the label The value of the property Sure Use single or double quotation marks

<meta charset="UTF-8" id="m1">
<meta charset='UTFzs-8' id='m1'>

3、html notes

Format : <!-- The comment --> ctrl shift +/

The function of annotation : (1) Add an explanation to the code

(2) Comment on some code that you don't need to execute for the time being

Browser for html The content in the comment will not parse , It doesn't show !

4、html Spaces and line breaks

In the browser , Multiple consecutive white space characters ( Space 、 tabs tab、 Line break ) Will be displayed as a space by the browser . that :

How to make a new line in a web page : have access to <br/> Tag line breaks

It can also be used.

aaa
Label it up , It's a line of monopoly .


Is to draw a horizontal line

How to make a space in a web page : have access to &nbsp( English space ); or &emsp; Make a space

Write a few, there are a few spaces , &emsp It's a Chinese space , A width equivalent to a Chinese character

Add : HTML Is case insensitive !

HTML Chinese grammar is very lax !( For example, case mixing , Or the label has only the beginning and no end , Or unreasonable nesting of tags ), But we're writing HTML Write according to the specification .

3.2 HTML label


3.2.1 Title Tag

<!-- 1 The level title is the largest 6 The level title is the smallest
<h1> Big news : Has Ma Yun ever been to heaven and earth ?</h1>
<h2> Big news : Has Ma Yun ever been to heaven and earth ?</h2>
<h3> Big news : Has Ma Yun ever been to heaven and earth ?</h3>
<h4> Big news : Has Ma Yun ever been to heaven and earth ?</h4>
<h5> Big news : Has Ma Yun ever been to heaven and earth ?</h5>
<h6> Big news : Has Ma Yun ever been to heaven and earth ?</h6>
-->

3.2.2 Paragraph Tags

<p> This is a paragraph tag </p>

3.2.3 Line wrap label ( Autistic )

<br/>

3.2.4 Horizontal label ( Autistic )

<hr/>

3.2.5 bold Italics

 bold : <Strong>i love you</strong>
Italics : <em>i love you</em>
The following style uses css To do it .

3.2.6 Special symbol label

 Space : &nbsp;&nbsp;&nbsp; ( Entering multiple spaces directly on the keyboard will only display one )
>: &gt;
<: $lt;
( Copyright symbols ):&copy;

3.2.7 Image tag ( Autistic )

adopt img Tags can insert an image into a web page

<!-- Select the image --- Right click ----prperties--Location( Show the location of the picture ) It is not recommended to write an absolute path with a drive letter , Because the project will be released in the future , The position will change , Then we have to change the path of the picture <img src="D:/JavaDevelop/WS_CGB2003_WEB/day05-htmlcss/WebContent/imgs/meinv.jpg"/> -->
<!-- ./: Represents the current directory ( The directory where the current file is located ), Due to the current html stay WebContent Under the table of contents therefore ./ Call means WebContent Catalog , in addition ./ It can be omitted ! ../: Indicates that the current directory is one level above the current directory ( This is the parent directory of the current directory ) ../../: Represents the level above the current directory ( That is, the parent directory of the parent directory of the current directory ) -->
<img src="./imgs/meinv.jpg" width="50%"/> Self closing label
<img src="./imgs/lpx.jpg" alt=" Show failure " title=" Hover text " width="50%"/>
Copy the directory containing the pictures to WebContent Next

among src Property is used to specify the path of the image ( The path of the picture should not be an absolute path with a drive letter , Relative path is recommended )

srch: Search for , Search for

width Property is used to specify the width of the picture

height Property is used to specify the height of the image

alt attribute : The content of this attribute will be displayed when the picture display fails .

title: The text prompt displayed when the mouse is placed on the picture indicates the value set for this property .

3.2.8 Hyperlink label

Hyperlinks are a label , adopt a Tags can create a hyperlink to another document in a web page

Click the hyperlink to jump to another page ( picture / Download path, etc ), Example :

<!-- 1. Jump to a local web page -->
<a href="01- First page .html">01- First page .html</a>
<!-- 2. Jump to Baidu home page -->
<a href="http://www.baidu.com"> use Baidu Search , You don't know </a>
<br/>
<!-- 3. Click on the picture to jump to tmooc Set a hyperlink to the image -->
<a target="_blank" href="http://www.tmooc.cn">
<img alt="tmooc" src="imgs/tmooc.png" />
</a>
<!-- Wrap a hyperlink around the picture , It's both a picture and a hyperlink . This address should be written in full , Writing an address in the browser may help you complete ( namely https Will help you make up ).-->

href attribute : Used to specify the to jump to after clicking the hyperlink URL Address ( Jump URL )

target attribute be used for : Specify how to open hyperlinks (target: The goal is , object . Click the hyperlink in the current window , The old browser can't see , Yes 2 It's worth .)

​ _self: The default value is , Means to open a hyperlink in the current window ( Don't write target attribute , Default to the current page )

​ _blank: Indicates that hyperlinks are opened in a new window (blank: blank )

 Total test :
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title> Image and hyperlink tags </title>
</head>
<body>
<h3> Create a hyperlink below </h3>
<a href="http://www.baidu.com"> use Baidu Search , You don't know !</a>
But the place to open the hyperlink is on the current page , I can't read the previous page when I want to see it , If you want to open a new web page, you need to set a property value :target, The goal is , object
<a href="http://www.tmooc.cn" target="_blank">TMOOC once , You will know !</a>
<hr/><hr/>
<h3> Insert an image below </h3>
<!-- src attribute , Used to specify the path of the picture When introducing pictures , Don't drive the path... Absolutely , Because in the future, just change the release environment , This path is likely to It's wrong. , Then we have to go back and change the path , Will cause unnecessary trouble ! It is recommended to write relative paths ( That is, a path that does not start with a slash and a drive letter ) Because it is “02- Image and hyperlink tags .html" Write path to file , Bring in a picture , So relative to the current 02 The location of this web page file , To find the location of the picture . ./: Represents the directory of the current file , such as ,02 Now? WebContent Under the table of contents , ./ It means this directory ./ You can omit it ! ../: Indicates to jump out of the current directory , Go up to the directory , Such as now ./ Express WebContent Under the table of contents , that ../ It means WebContent The parent directory of the directory , That is to say "day05-htmlcss" Catalog This can't save -->
<img src="./imgs/meinv01.jpg" width="45%"/>
<img src="imgs/meinv02.jpg" width="45%"/>
</body>
</html>

3.2.9 Anchor links ( It's a hyperlink tag with some attributes added )

 step :1, Need an anchor tag .2, adopt # Jump to tag .
<!-- Use name As a mark -->
<a name="top"> Top </a>
<!-- Jump -->
<a href="#top"> Back to the top </a>
<!-- Be careful : Can be used on the same page , It can also be used to jump to different pages .-->

3.2.10 Functional connectivity

<!-- Email links : Click this connection to send mail to the mailbox with an address , The premise is that you have installed the mail sending software locally This property is : Jump to the email address -->
<a href="mailto:[email protected]"> Click contact me </a>
<!--QQ link -->
<a target="_blank" href="http://wpa.qq.com/msgrd?v=3&uin=&site=qq&menu=yes"><img border="0" src="http://wpa.qq.com/pa?p=2::53" alt=" Hello , Plus I get free movies " title=" Hello , Plus I get free movies "/></a>

3.2.11 list

<!-- list : A display of information resources . species : Orderly , disorder , Customize -->
<!-- Orderly : The display results are preceded by a number... For each value ,1 2 3 4 Range of application : The examination paper , Question and answer .....-->
<ol>
<li>java</li>
<li>web</li>
<li>linux</li>
<li>aaa</li>
</ol>
<!-- Unordered list : The display results are preceded by small black dots Range of application : Navigation , Sidebar use css It can also be beautified as hollow points and so on -->
<ul>
<li>java</li>
<li>web</li>
<li>linux</li>
<li>aaa</li>
</ul>
<!-- Custom list dl: label dt: List name dd: List content Range of application : At the bottom of the company's website -->
<dl>
<dt> Discipline </dt>
<dd>java</dd>
<dd>linux</dd>
<dd>c</dd>
<dd>python</dd>
</dl>

Customize the list display effect :

 Insert picture description here

3.2.12 Media element labels ( Audio , video )

<!-- Audio and video src: Resource path controls: Control bar autoplay: Auto play -->
<video src="/ Titles .mp4" controls autoplay></video>
<!-- Audio -->
<video src="/ Titles .mp3" controls autoplay></video>

3.2.13 iframe Inline framework

<!-- Show another page in one page -->
<!--iframe Inline framework src: Address name: The frame can be named casually w-h: Width Height -->
Use a : adopt src Direct jump address display
<iframe src="https://blog.kuangstudy.com" name="hello" width="1000px" height="500px">
</iframe>
usage 2: Click hyperlink , Hyperlink to iframe Add properties to the frame to jump
<iframe src="" name="hello" width="1000px" height="500px">
</iframe>
<!--target Property can also jump to the specified -->
<a href="https://blog.kuangstudy.com" target="hello"> Click the jump </a>

3.2.14 Tabular label

effect : Mainly used for display , Display data , Because it can make the data display very complete , Readability is very good . Especially when displaying data in the background , It's important to be able to use forms skillfully . A fresh and simple table can show the cumbersome data in a very organized way .

namely : Tables are not used to lay out pages , It's used to display data .

Table basic label

1. Case study : Insert a 3*3 Table for

<h1> Case study 1: Insert a 3*3 Table for </h1>
<table><!-- Used to define a table in a web page -->
<tr>
<td>11</td><!-- Used to define a cell -->
<td>12</td>
<td>13</td>
</tr>
<tr>
<td>21</td>
<td>22</td>
<td>23</td>
</tr>
<tr>
<td>31</td>
<td>32</td>
<td>33</td>
</tr>
</table>
<h1> Case study 2: Insert a 3*3 Table for enjambment , Cross column </h1>
<table border="1px"> <!-- Set borders -->
<tr>
<!--colspan Cross column : Horizontal merger -->
<td colspan="3">11</td><!-- Originally shared 3 Column , be equal to 3 Indicates that this column is exclusive 3 Column , So put the original 3 Column delete 2 Column -->
</tr>
<tr>
<!--rowspan enjambment : Merge up and down -->
<td rowspan="2">21</td><!-- hold 21 Exclusive column , It will 33 Squeeze out , One more column , So the 33 Delete -->
<td>22</td>
<td>23</td>
</tr>
<tr>
<td>31</td>
<td>32</td>
</tr>
</table>
<h1> Case study 3: Insert a 3*3 Table for , Include table header </h1>
<table><!-- Used to define a table in a web page -->
<tr><!-- Used to define a table row -->
<th> Header 1</th><!-- Used to define a table header -->
<th> Header 2</th>
<th> Header 3</th>
</tr>
<tr>
<td>11</td><!-- Used to define a table column -->
<td>12</td>
<td>13</td>
</tr>
<tr>
<td>21</td>
<td>22</td>
<td>23</td>
</tr>
<tr>
<td>31</td>
<td>32</td>
<td>33</td>
</tr>
</table>

The display effect in the browser is as follows :

Case study 1:

 Insert picture description here

Case study 2:

 Insert picture description here

Case study 3:

stay head Add the following to the tag :

<style> /* style Only... Can be written in the label css Annotation and css Code */ table{
 border:*2px solid red; /* Add a border to the table */ border-collapse: collapse; /* Set border merge */ background-color: pink; /* Set the background color for the table */ width: 70%; /* Set the width for the table */ /* margin-left: 15%; */ /* Set the table's left margin and right margin adaption ( Make sure the two are always equal ) */ margin-left: auto; margin-right: auto; } td,th{
 border:2px solid red; /* Add borders to cells */ border-collapse: *collapse*; /* Set border merge */ padding: 5px; /* Set the distance between the cell border and the content ( padding ) */ } h1{
 /* border: 2px solid blue; */ text-align: *center*; /* Set the content in the element to center horizontally */ } </style>

Refresh the browser again, the display effect is :

 Insert picture description here

2、 Introduction to the basic labels of the form

table -- Used to define a table in a web page
tr -- Used to define rows in a table
td -- Used to define cells in a table
th -- Used to define the cells in the header row (th The text in is centered by default , And make it bold )

3、 practice : Use table tags to create a table in a web page , Here's the picture :

Requirements are as follows :

(1) The contents of the table are as follows , And set the table border
(2) Set no gaps between cells , And set the distance between cell border and content to 5px
(3) Set the background color of the table to pink, And set the width of the table to 70%
(4) Set the table to center in the web page , And add a header and a title to the table

 Insert picture description here

Table structure label

Use scenarios : Because the form can be very long , In order to better represent the semantics of the table , You can split a table into table headers and table topics 2 Most of the .

In the table label , Use them separately :< thead > The label represents the header area of the table ,< tbody> Represents the subject area of the table . This can better distinguish the table structure .

1.< thead>< /thead>: Used to define the header of a table .< thead> Inside, you have to have < tr> label . It's usually on the first line .

2.< tbody>< /tbody>: Generally used to define the subject of the table , It is mainly used for data ontology .

3. The above labels are all placed in :< table></ table>

<!-- Pay attention to this 2 Both labels are there <table> Inside the label -->
<table><!-- Used to define a table in a web page -->
<thead><!-- head -->
<tr>
<td>11</td><!-- Used to define a cell -->
<td>12</td>
<td>13</td>
</tr>
</thead>
<tbody><!-- The body -->
<tr>
<td>21</td>
<td>22</td>
<td>23</td>
</tr>
<tr>
<td>31</td>
<td>32</td>
<td>33</td>
</tr>
</tbody>
</table>

3.2.15 Form labels

1、 What forms do : Used to submit data to the server

There are two ways to submit data to the server :

(1) Submit data to the server through a form
 Insert picture description here

Forms can contain form item tags , You can fill in data in the form item ( For example, user name 、 Password etc. ), Fill in and submit the form , The data in the form can be submitted to the corresponding server .

(2) Number of submissions to the server through hyperlinks 000000000000 According to the

http://www.baidu.com?username= Zhang San &pwd=123&like= Basketball
<a href="http://www.baidu.com?username= Zhang San &pwd=123&like= Basketball " target="_blank"> use Baidu Search , You don't know !</a>

In the address bar URL The address is followed by a question mark (?) You can splice parameters , Parameters can have multiple , Use... Between multiple parameters & Separate , Parameters are also divided into parameter names ( for example :username/pwd/like) And parameter values ( for example : Zhang San /123/ Basketball ), At the same time of accessing Baidu server after carriage return , You can take the parameters stitched after the question mark to Baidu server .

If the browser version is too low, it may display yes url Coding format , It's not a mistake .

2、 Form labels

<form action="url Address " method=" submission "></form> Note that the word is form

among action Property is used for ** Specify the submission address of the form ,** for example , take action Point to Baidu server , It means that when you fill out the form in the future , Submitting the form will submit the data in the form to Baidu server .

method=“GET/POST” Attributes are used for Specify how the form should be submitted , The common one is GET and POST Submit .

The back can speak .

3.2.16 Form item labels ( Autistic )

Written in form The label in which data can be submitted , You can also use it ajax.

1、input Elements The type depends on ,type Value

(1) Normal text input box ( such as : user name / nickname / mailbox / Verification code, etc )

 Insert picture description here

<!-- type="text": Represents a text input box , The default is text. value=" Handsome " The default initial displayed value of the text box . maxlength="8" How many characters can I write at most size="30" Length of text box name="username": Is the parameter name before submitting the parameter , Parameter value refers to the value written in the page input box . If you want to submit parameters, you must have name attribute . -->
<input type="text" name="username"/>

(2) Password input box ( such as : password / Confirm password, etc )

<!-- The usage is the same as the text input box , It's just that the value inside is hidden * Express .-->
<input type="password" name="pwd"/>

(3) Radio buttons ( such as : Gender / Department, etc )

<!--type by radio Yes, you must specify a value value, Because a radio box cannot be submitted by entering a value like a text box , If you want to submit, you must pass value Property to set the submitted value . type="radio": Represents a radio box name="gender": Set the submission parameter name ,name If the values are the same, you can only select one , You can choose single or multiple choices for different . value="male": Indicates that the value submitted by the radio box is male -->
<input type="radio" name="gender" value="male"/> male
<input type="radio" name="gender" value="fmale"/> Woman
<!-- adopt get The display format of the result submitted by the request is : ?gender=male -->

(4) Check box / Checkbox ( such as : hobby / Post, etc )

<!-- type="checkbox": Represents a multiple choice box value="sleep": Set the submitted value name="like": Only the function of setting parameter name , You can choose one or more . The values inside should be the same , Different can also . -->
<p> hobby :
<input type="checkbox" name="like" value="sleep"/> sleep
<input type="checkbox" name="like" value="code"/> Knock on the code
<input type="checkbox" name="like" value="game"/> Play a game
<input type="checkbox" name="like" value="chat"/> Chat
</p>

(5) General button ( such as : Change a picture )

<!-- type="button": Represents a normal button value=" In a ": Set the content displayed on the button -->
<input type="button" value=" In a "/>

The normal button itself has no function , But we can pass js Add function or behavior to a button , Like the click event .

(6) Picture button

<!-- type="image": Picture button src="./1.jpg": Path to picture By default, there is a submission function .-->
<input type="image" src="./1.jpg"/>

(7) Reset button

<!-- Used to empty all the contents of the form type="reset": Reset button value=" Empty the form ": The value displayed on the button -->
<input type="reset" value=" Empty the form "/>

(8) Submit button ( such as : Submit / register / Sign in )

<input type="submit" value=" Submit / register / Sign in "/>

The submit button is used to submit the data in the form to the server !

(9) Upload file button

<!-- Click this button to select the file on the computer to upload . name: Used to set the parameter name , The value is the selected file name . -->
<form action="aa.html" method="get">
<p>
<input type="file" name="files">
</p>
<input type="submit" value=" Submit / register / Sign in "/>
</form>

(10) Verify button

<!-- These buttons come with some basic and primary functions to verify the format of input , When developing, we use js Write your own form verification function .-->
<!-- Mail verification -->
<p> mailbox :
<input type="email" name="email">
</p>
<!--URL verification -->
<p>URL:
<input type="url" name="url">
</p>
<!-- Digital Verification ,max: Digital maximum min: Numeric minimum step: Interval value of numerical change -->
<p> Numbers :
<input type="number" name="num" max="100" min="0" step="1">
</p>

(11) Slider button

<!-- Same as above -->
<input type="range" name="num" max="100" min="0" step="2">

(12) Search box button

<input type="search" name="search">

2、select、option label :

<!-- select label : Represents a drop-down box . name="city": Indicates the submitted parameter name . option label : Represents the options of each line in the drop-down box The displayed value is Beijing , Shanghai . The value in the tag . selected="selected": Set up current option Options are selected by default , If not set, the first item will be displayed by default . value="beijing": Set the submitted value . adopt form Forms get Submit 2 In this case : 1. No settings selected The default property is selected , The result is :?city=beijing 1. No settings selected The default property is selected , The result is :?city= Guangzhou -->
<select name="city">
<option value="beijing"> Beijing </option>
<option value="shanghai"> Shanghai </option>
<option selected="selected"> Guangzhou </option>
<option> Shenzhen </option>
</select>

3、textarea Multi line text input area :

<!-- textarea label : Multi line text input area name="description": The name of the submitted parameter cols="30": Set the number of columns in the text input box ( Width ) rows="5": Set the number of lines in the text input box ( Height ) placeholder=" Please enter the description information ...": Set the prompt message in the input box ! You can write the content in the text area directly between the labels , Of course, you can also write on the page -->
<textarea name="description" cols="30" rows="5"placeholder=" Please enter the description information ...">
Text content
</textarea>

3.2.17 Form details

1、 When you submit the form , Why is the data in the form not submitted ?

For form item tags in a form , As long as you need to submit data to the server ,** The form item must be added with name attribute ;** If the form item label doesn't have name attribute , When the form is submitted , This item will be ignored . for example :

<input type="text" name="username"/>
<input type="password" name="psw"/>

2、 How to make multiple radio boxes only have one selected ?

requirement Multiple radio boxes must have the same name Property value , If there are multiple radio boxes name Same property value , It means the content of a group , Only one of the radio boxes in a group can be selected

<td> Gender :</td>
<td>
<input type="radio" name="gender"/> male
<input type="radio" name="gender"/> Woman
</td>

3、 Radio box why 、 The values submitted after selecting an item in the check box are on?

Because the radio box 、 The check box can only select , Different from the user name 、 Password input box , You can enter content .

So we need to pass value Properties are radio boxes or check boxes Set the submitted value ( If you don't set the default values are on), for example :

If multiple radio boxes name Same value , Represents the same group , You can only choose one in the same group .

<input type="radio" name="gender" value="male"/> male
<input type="radio" name="gender" value="female"/> Woman

4、 How to set a radio box or check box to select an item by default ?

You can add... To radio boxes or check box labels One checked="checked" attribute , You can make the current radio box or check box selected by default . for example :checked: Check

<input type="radio" checked="checked" name="gender" value="male"/> male
<input type="radio" name="gender" value="female"/> Woman
<!-- Hobbies check box / Checkbox -->
<input type="checkbox" name="like" value="basketball"/> Basketball
<input type="checkbox" checked="checked" name="like" value="football"/> football
<input type="checkbox" name="like" value="volleyball"/> Volleyball

5、 How to set the drop-down box to select an item by default ?

stay option Add a... To the label selected="selected" attribute , Can make the present option Options are selected by default , example :

<select name="city">
<option> Beijing </option>
<option> Shanghai </option>
<option selected="selected"> Guangzhou </option>
<option> Shenzhen </option>
</select>

6、 Drop down the box option Options value What is the function of attributes ?

<select name="city">
<option value="beijing"> Beijing </option>
<option value="shanghai"> Shanghai </option>
<option selected="selected"> Guangzhou </option>
<option> Shenzhen </option>
</select>

If an option is selected , And add... To this option value attribute , When you submit the form , Will be submitted value The value of the property .

If an option is selected , There is no add on this option value attribute , When you submit the form , The content of the tag will be submitted ( Only suitable for drop-down boxes )

Be careful : The browser submits the data to the server , Can translate Chinese characters url code . It's not a mistake

3.2.18 Application of forms ( hide , read-only , Ban , Usability enhancements )

<!-- Can be used for text box labels readonly Read only tags : Read only cannot be modified -->
<input type="text" name="username" value=" Handsome " readonly/>
<!-- Can be used for any label checked disabled Disable label : Only female , Male tags cannot be selected -->
<input type="radio" name="gender" value="male" checked disabled/> male
<input type="radio" name="gender" value="fmale"/> Woman
<!-- You can hide the password box , But you can still submit -->
<input type="password" name="pwd" value="124155" hidden/>
<!-- Enhance mouse availability : For example, you can only click the text box to modify the content , After adding this label, click the text in front of it to modify it .-->
<p>
<label for="mark"> You click to try </label>
<input type="text" id="mark"/>
</p>

3.2.19 Form primary validation

Think about why forms need to be validated :

1, Reduce the access pressure of the server , For example, Taobao receives hundreds of thousands of requests every day , The received wrong data should also be verified, which will increase the pressure on the server .

2, Ensure data security .

Commonly used way :

1, Tag with verification function ( primary )

2, Add verified properties ( primary , Query regular expression URL :https://www.jb51.net/tools/regexsc.htm)

3, adopt js To verify ( senior )

<!--placeholder: Default prompt . Used for input box : The text box , Text fields, etc -->
<input type="text" name="username" value=" Handsome " placeholder=" Please enter a user name "/>
<!--required: Judge not empty , When clicking submit , The text box cannot be empty .-->
<input type="text" name="username" placeholder=" Please enter a user name " required/>
<!--pattern: Regular expression judgment , Regular expressions are too cumbersome to write. You can use them and check them whenever you want , Search for common regular expressions directly in Baidu .-->
<input type="text" name="mail" pattern="^[1-9]\d*$ or ^([1-9][0-9]*){1,3}$ or ^\+?[1-9][0-9]*$">

3.3 Registration form case



<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title> Forms and form labels </title>
<style> h1{
 /* Check all h1 Elements */ text-align:center; /* Set the element content to be centered */ color:red; /* #FF0000 */ When the color does not know the specific value : have access to EverEdit Tools --- Tools --- Color picker , A small straw and glue the value , Before the value + individual # Number 16 The value of base . } table{
 /* All selected table Elements */ border:2px solid red; border-collapse: collapse;/* Set border merge */ background:lightgrey; /* Set the background color for the table */ /* Set the left and right outer margins of the table to be adaptive */ margin-left: auto; margin-right: auto; } td{
 /* Check all td Elements */ /* Sets the border for the current element to :2 Pixels 、 Solid line 、 Red */ border:2px solid red; border-collapse: collapse; /* Set border merge */ (collapase: Collapse ) padding:5px; /* Set the distance between the border and the content */ padding } </style>
</head>
<body>
<!-- 0、 Add a first level title , The content is “ Welcome to register ”, And set the content to be centered 1、 Use table、tr、td Create a label 7*2 Table for For table 、 Cell set border , And set the border merge Set the background color for the table 、 Set the distance between the cell and the content Set the table itself to be centered horizontally Be careful : The width of the contents in the table will be automatically filled according to the contents . 2、form A tag is a form tag , The function is to send... To the server / Submit data ( Wrap up the whole form , Put it in table outside ) action Property specifies which server the data in the form will be submitted to (action Behavior , action ) <input type="text"/>: Text input box (type type ,text, It indicates that it is a text type input box ) input: Input -->
<h1> Welcome to register </h1>
<form action="#"> # Equivalent to a placeholder that will be submitted to itself , Equivalent to not submitting . When developing web pages, you can use this symbol to preempt
<table>
<tr>
<td> user name :</td> Every form that needs to submit data should add name.
<td><input type="text" name="user"/></td>
</tr>
<tr>
<td> password :</td> (password password , This box will not show the password )
<td><input type="password" name="psw"/></td>
</tr>
<tr>
<td> Gender :</td>
<td>
<!-- name The same value is the radio box of a group , Only one... Can be chosen -->
<input type="radio" name="gender" value="male" checked="checked"/> male checked By default, check this box , Give users a better experience . With a consistent name .
<input type="radio" name="gender" value="female" /> Woman
</td> Set up value Because the radio box provides by default on, men and women The server can't distinguish , You can set different value Value of , When resubmitting the form On Replace with value value .
</tr>
<tr>
<td> hobby :</td>
<td>
<!-- The multi selection box itself supports multi selection , and name Whether the values are the same or not is irrelevant checkbox: Check box -->
<input type="checkbox" name="like" value="basketball"/> Basketball
<input type="checkbox" name="like" value="football" checked="checked"/> football
<input type="checkbox" name="like" value="velloball"/> Volleyball
</td>
</tr>
<tr>
<td> City :</td>
<td>
<select name="city"> select Represents the definition of a drop-down box ,option Responsible for the options inside In the drop-down box option Medium value, If you don't want to submit the value inside , Set up a value It was submitted vauue In the value of the
<option value="beijing"> Beijing </option>
<option value="shanghai"> Shanghai </option>
<option selected="selected"> Guangzhou </option>
selected: The default option is to display Guangzhou
<option> Shenzhen </option>
</select>
</td>
</tr>
<tr>
<td> Self description :</td>
<td> <!-- cols: Number of columns , rows: Row number ,textarea Even if there is no content , And don't write it as an autistic label -->
<textarea name="description" cols="30" rows="5" placeholder=" Please enter the description information ..."></textarea> And if there is content in it, even if it is a blank space, there will be no prompt information , Inside angle brackets . The prompt for entering something disappears . Multi text input box (textarea, Note that it's not an autistic label , The default is 2 That's ok The number of columns and rows refers to , The larger the number, the wider the length placeholder( Place holder ) : Write a hint ,)
</td>
</tr>
<tr>
<!-- colspan: Set the number of columns the cell spans ( Horizontal merger ) colspan: Merge Columns rowspan: Set the number of rows that the cell spans vertically ( Vertical merger ) rowspan: Merger line value: Set the text displayed on the button -->
<td colspan="2" style="text-align:center;">
<input type="submit" value=" Submit "/> (submit: Submit button align: calibration , Make consistent It's just to submit data. There's no need to name)
</td>
</tr>
</table>
</form>
</body>
</html>

3.4 Page structure analysis

What parts does a web page usually contain

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-Iff8Gxi5-1637771955370)(C:\Users\Think\Desktop\QQ Screenshot 20210517144119.png)]

<header>
<h2> Page header </h2>
</header>
<section>
<h2> Web Theme </h2>
</section>
<footer>
<h2> Web footsteps </h2>
</footer>

effect :

 Insert picture description here

3.5 CSS summary ( Continue to learn )


3.5.1 What is? CSS?( understand )

CSS: Cascading style sheets , Used to modify 、 A technique for rendering web pages .

The general function of beautification is : typeface , Color , Margin , Height , Width , Background image , Web positioning , Web floating …

open – More tools ---- Developer tools or f12, by web A tool provided by developers . Can be used to better view the web source code , style , debugging , And so on. .

Right click to open the web page source code : When browsing a web page, you sometimes need to view, learn or modify the web page source code . Why can web pages view source code , Not afraid of plagiarism ? You can only see the front end html/js The content such as , You can't see the back-end code . Many websites are front-end rather than back-end , The front end just gives you an interactive interface . It doesn't hurt to show you , You can see Alipay's Web front-end code. , You can reproduce a full-featured Alipay. ? It's obviously not realistic .

 Insert picture description here

eclipse Open page editing mode : Select Page —new—HTML Editor

Be careful :Open with—HTML Editor( Is to open the file for editing ) editor compile .

3.5.2 css The history of

css1.0

css2.0 DIV( block )+css,HTML And CSS Implementation of structure separation , The web becomes simple ,SEO

css2.1 float location

css3.0 Round corners , shadow , Animation … Browser compatibility .

Be careful :css and js The creation of files is an ordinary file file.

Catalog :sts :folder idea:directory

3.5.3 css grammar

 Selectors {

/*css notes */
Statement 1;
Statement 2;
Statement 3;
}

3.5.4 stay HTML Introduction in CSS

introduce priority : Nearby principle

The way 1: adopt style Attribute introduction css( Not recommended )

<!-- 1. By... On the label style Attribute to div Set the style Frame :2px solid red font size :26px The background color is :pink Multiple attributes use semicolons (;) separate -->
<div style="border:2px solid red;font-size:26px; background:pink;"> This is a div...</div>

Because the above way is to css Style code , Written directly on the label style Attribute internal , If there are too many attributes , Easy to cause page structure confusion , Not conducive to later maintenance .

Write the style code on the label , The style code only works for the current tag , Code can't be reused !

And in this way, you can't make html Labels and css Style separation !

This is not recommended !( Such passage style Attribute added style , It's called in line styles / inline style )

The way 2: adopt style Tags introduced css( This is in head Inside the label and script Dissimilarity )

stay head You can add a style label , stay style The inside of the label can be written directly css style

In this way, all of the css Style code is concentrated in a style Internal unified management of labels , This method will not cause confusion of page structure , And you can reuse the code !

Preliminary implementation of take html Tag code and css The style code is separated !

Code example :

<!-- 2. adopt style Label to span Set the style as follows : Frame : 2px solid green font size : 30px Bold font type: Specified as css style , It can be omitted -->
<head>
<meta charset="utf-8">
<style type="text/css"> /* ****** CSS style ****** */ span{
 /* For the current html All in span Label setting style */ border:2px solid green; font-size:30px; font-weight:bolder; /* bold/bolder */ } </style>
</head>

The way 3:css3.o adopt link Links introduce external css file ( recommend )

stay head The label inside , Through one link Tags can introduce external CSS file ( Choose Web project —new–other—css File)

In this way, all of the css The code is concentrated in a single css Unified management in documents , The real realization will be css Code and html Code separation , Realize the reuse of code . link: Connect ; Button .

Code example :html Introduction in demo.css file

<!-- 3. adopt link The label introduces the external css file , by p The element settings are as follows : Frame : 2px solid blue; The font color :red The font is set to Chinese amber Set the first line of text indentation 50px -->
<!-- css3.o Linked introduce demo.css file ( Style in ) -->
<link rel="stylesheet" href="demo.css" />
<!-- css2.o Import type : A little -->
demo.css file
/* Specify the encoding format */
@charset "UTF-8";
p{
border: 2px solid blue;
color: red;
font-family: " Amber in Chinese ";
text-indent: 50px;
}

Total test :


<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<!-- The second is the introduction of css The way : Will all css The code is concentrated in one style Internal unified management of labels , Preliminary implementation of css Code and html Code separation , Realize the reuse of code -->
<style type="text/css"> /* ****** CSS style , You can omit it ****** */ span{
 /* Check all span Elements */ border:2px solid green; font-size:30px; font-weight:bolder; } </style>
<!-- The third way is to introduce css The way : adopt link The label introduces the external css file In this way, all of the css The code is concentrated in one css Unified management in documents , Really realized css and HTML The way the code works , Realize the reuse of code ! rel: Specify the file type to be imported , The value is a fixed value --stylesheet: Style sheets href: Specify the css The location of the file ./demo.css current path + file name ./ It can be omitted -->
<link rel="stylesheet" href="demo.css"/>
</head>
<body>
<!-- 1. By... On the label style Attribute to div Set the style Frame :2px solid red font size :26px The background color is :pink -->
<!-- 2. adopt style Label to span Set the style as follows : Frame : 2px solid green font size : 30px Bold font -->
<!-- 3. adopt link The label introduces the external css file , by p The element settings are as follows : Frame : 2px solid blue; The font color :red The font is set to Chinese amber Set the first line of text indentation 50px -->
<!-- The first is the introduction of css The way : It is not recommended to use ! This way is to write the style code directly on the label , Can't reuse code , And it can easily cause the confusion of page structure , Not conducive to maintenance . -->
<div style="border:2px solid red;font-size:26px;background:pink;">
This is a div...
</div>
<br/>
<span>span111</span>
<span>span222</span>
<span>span333</span>
<p> This is a p label 1</p>
<p> This is a p label p2</p>
<p> This is a p label p3</p>
</body>
</html>
demo.css file
@charset "UTF-8"; Can't delete
p{ /* Check all p Elements */
border:2px solid blue;
color:red;
/* Set the first line indent 50px */
text-indent:50px;
/* Set the font */
font-family:" Amber in Chinese ";
}

3.6 CSS Selectors ( a key )


The so-called selector is able to be in html A technique that helps us select elements for decoration .

Open developer tools on the web page , F12.

The arrow in the upper left corner turns blue when selected , Then click the corresponding area , Will be displayed html and style The code corresponding to the style .

After being marked with a deleted line, it indicates that this line of code is not effective .( Being covered may )

3.6.1 Tag name selector ( Basic selector )

By element name ( Or tag name ) Check the... With the specified name All tags

Format : Element name / Tag name { css style … }

/* ----- 1. Label name selector exercise ----- Will all span The background color of the label is set to #efbdef, Set the font size to 22px, Bold font ;*/
span{
 /* Check all span Elements */
background-color:#efbdef;
font-size: 22px;
font-weight: bolder;
}

3.6.2 class Selectors ( Cross label )( Basic selector )

You can add a generic attribute to the element – class, adopt class Set the attribute of the element to which it belongs ,class Elements with the same value are a group of . adopt class Value can select the elements of this group , Add styles to elements , Labels can be classified .

Format :.class value { css style … }

example :

/* ----- 2. Class selector exercise ----- (1) Will all span( But not including div and p Label under span) The background color of is set to #faf77b, Border changed to 2px solid cyan; (2) take div Under the span And the contents are "span111" Of span, The background color is set to #5eff1e、 Font color settings #ec0e7e;*/
.s1{
 /* Select all class The value is s1 The elements of */
background: #faf77b;
border: 2px solid cyan;
}
.s2{
 /* Select all class The value is s2 The elements of */
background: #5eff1e;
color: #ec0e7e;
}

in addition , An element can also be set to more than one class value , Multiple class Values are separated by spaces , for example :

<span class="s1 s2" >span111</span>

Indicates that the current element belongs to multiple groups at the same time , Styles set on multiple groups also work on the current element .

If multiple groups have the same style ( But the value is different ), Styles conflict , The style written after will override the previous style !


Content supplement : Selector priority order

(1) If it's the same kind of selector , At the same time, some elements are styled , If styles conflict , that The style written after will override the previous style .

(2) If it is Selectors are different , Set the style priority order as follows : Write directly on the element >id Selectors (100) > Category selection It's a tool (10) > Element name Selectors (1)


3.6.3 id Selectors ( Basic selector )

Through the common attributes on the tag id, You can set... For labels A unique Of ( In the whole web page ) Number (id The value should be unique ), adopt id Value can uniquely select an element .

Format :#id value { css style }

/* ----- 3.id Selector practice ----- use id The selector will be the first p Label set font size to 24px, The font color is #a06649, Indent the first line of text 20px.*/
#p1{
 /* Choose id The value is p1 The elements of */
font-size:24px;
color: #a06649;
text-indent: 20px;
}
<p id="p1"> This is a p Elements !!!</p>

3.6.4 Descendant selector ( Hierarchy selector )

Check the inside of the specified element The specified descendant element of

Format : The ancestors Progeny { css style … } Notice the space in the middle .

/* ----- 4. Progeny selector exercise ----- by p Everything inside the element span Elements , Set the font size to 18px, The font color is red , The background color is pink;*/
p span{
 /* Match all p Everything inside the element span Elements */
font-size:18px;
color: red;
background: pink;
}
/* p,span{} Match all p Elements and all span Elements */
<p id="p1">
This is a p Elements !!!
<span> This is a span Elements !!!</span>
</p>

3.6.5 Attribute selector ( Hierarchy selector )

Based on the element selected by the selector , Filter according to the attribute conditions of elements / Filter element

Format : Selectors [ Property conditions ]…{ css style } In front is a selector , Followed by a square bracket , Write a complete attribute condition inside , If one cannot be distinguished, multiple attributes can be used . Note that the condition attribute here is completely written .

/* ----- 5. Attribute selector ----- For all text input boxes , Set the background color to #FF7CCC、 font size 22px, Indent the first line of text 15px;*/
input[type='text']{
 /* Match all input also type The value is text The elements of */
background: #FF7CCC;
font-size: 22px;
text-indent: 15px;
}
input[type='text'][name='email']{

/* Check all input also type The value is text、 also name by email The elements of */
background : yellow;
}

3.6.6 Child selectors ( Hierarchy )

body>p{

background:#3cbda6;
}

3.6.7 Adjacent Sibling Selectors ( Hierarchy )

.active+p{

background:#a13d3o;
}

3.6.8 With selector ( Hierarchy )

.active~p{

background:#a13d3o;
}

3.6.9 Structure pseudo class selector ( A little )

b Station address :
https://www.bilibili.com/video/BV1YJ411a7dy?p=7&spm_id_from=pageDriver

Selector test :

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-VsgRIQYd-1637771955377)(C:\Users\Administrator\Desktop\ Cut the figure of \ Capture 02.PNG)]


<!DOCTYPE HTML>
<html>
<head>
<title>CSS Selectors </title>
<meta charset="UTF-8" />
<style type="text/css" >
/* Border all labels for convenience , And set some default styles */
body,input{font-size:18px;}
input{border: 2px solid green;}
div,span,p{border: 2px solid red;font-size:18px;}
span{border-color: blue;margin-right:10px;}
div,p{ width:350px;height:80px;padding:5px;}
/* ----- 1. Label name selector exercise -----
Will all span The background color of the label is set to #efbdef, Set the font size to 22px, Bold font ;*/
span{
background:#efbdef;
font-size:22px;
font-weight:bolder;
}
/* ----- 2. Class selector exercise -----
(1) Will all span( But not including div and p Label under span) The background color of is set to #faf77b, Border changed to 2px solid cyan;
(2) take div Under the span And the contents are "span111" Of span, The background color is set to #5eff1e、 Font color settings #ec0e7e;*/
.s1{ /* spot (.) Followed by class Value , So here is to select all class The value is s1 The elements of */
background:#faf77b;
border:2px solid cyan;
}
.s2{
background:#5eff1e;
color:#ec0e7e;
}
/* ----- 3.id Selector practice -----
use id The selector will be the first p Label set font size to 24px, The font color is #a06649, Indent the first line of text 20px.*/
#p1{ /* Choose id by p1 The elements of */
font-size:24px;
color:#a06649;
text-indent:20px;
}
/* ----- 4. Progeny selector exercise -----
by p Everything inside the element span Elements , Set the font size to 18px, The font color is red , The background color is pink;*/
p span{ /* Choose p Inside the element span Elements , There's a space in the middle */
font-size:18px;
color:red;
background:pink;
}
/* ----- 5. Attribute selector -----
For all text input boxes (input type='text'), Set the background color to #FF7CCC、 font size 22px, Indent the first line of text 15px;*/
input[type='text']{ /* Check all type The value is text Of input Elements */
background:#FF7CCC;
font-size:22px;
text-indent:15px;
}
/* <input type="text" name="email"/> */
input[type='text'][name='email']{ /* Match all type by text、 also name by email Of input Elements There is no space in the middle */ }
</style>
</head>
<body>
<br/>
<div>
div111
<span class="s2"> This is a span</span>
This is a div
</div> <br/>
<!-- Have the same class The value element is a group element , Space in the middle -->
<span class="s1 s2">span111</span>
<span class="s1">span222</span>
<span class="s1">span333</span>
<p id="p1">
ppp111
<span> This is a span</span>
This is a P Elements
</p>
<p>
ppp222
<span> This is a span</span>
This is a P Elements
</p>
<input type="text" value=" user name "/>
<input type="button" value=" Button 1" />
<input type="button" value=" Button 2" />
</body>
</html>

3.7 Summary of common attributes


3.7.1 Text attribute

1、text-align: Set the horizontal alignment of text within the element , Its common value is :

left; The default value is . Align left
right; Right alignment
center; Align center
justify; full-justified

2、text-decoration: Set the underline style of the text , Its common value is :

underline; Underlined
none; No underline

3、text-indent:2em ; Set the first line of text to indent , Company : Pixels / percentage

4、letter-spacing: Set character spacing / spacing , Its common value is :

normal

5、text-shadow: Set font shadow , Its value is :

 Pixel values Pixel values Pixel values Color value
The first value is the shadow horizontal shift , The second value is the shadow vertical shift , The third value is the shadow spread value , The fourth value is the shadow color
explain : The larger the first value, the more offset to the right

3.7.2 Font properties

font-size: Set font size

font-weight: Set font thickness normal、bold、bolder 100/200/300…/900

font-family: Set the font , For example, Microsoft YaHei 、 In black 、 Regular script, etc

color: Set font color

3.7.3 Background properties

background-color: Set the background color

background-image: Set the background image ,url(‘ Path to picture ’); // The path can be quoted or not

background-repeat: Set or retrieve whether and how the background image of the object is arranged , Common values :

repeat( The default value is , To arrange repeatedly )
repeat-x( Repeat horizontally , But not vertically )
repeat-y( Repeat it vertically , But not horizontally )
no-repeat( No repetition )

background-position: Set or retrieve the background image position of the object

background: The background color Background image Whether the background image is repeated The location of the background image

3.7.4 Frame (border)

border:2px solid red; – Set the border of the element ( You can also set the width of the border 、 style 、 Color )

Compound attribute : Multiple properties can be set at the same time

border Properties can be split into the following settings :

border-width: 2px; -- Set the width of the element border
border-style: solid; -- Set the style of the element border
border-color: red; -- Set the color of the element border

among border-width、border-style( such as , Solid line , Dotted line )、border-color It can also be split according to the upper right lower left direction , With border-width For example : These three attributes can be written like this

border-top-width: 2px; -- Set the width of the upper border
border-left-width: 2px; -- Set the width of the left border
border-right-width: 2px; -- Set the width of the right border
border-bottom-width: 2px; -- Set the width of the lower border
Or : On Next Right Left
border width: 5px 10px 15px 20px;
border width: 5px 10px 20px; On about Next
border width: 5px 20px; Up and down about
border width: 5px ; Up, down, left and right 5px;

3.7.5 Other attributes

width: Set the width of the element

height: Set the height of the element

margin: Set the element's Margin

margin-top: 10px; -- Set the top and outer margins of the element to 10px;
margin-right: 20px; -- Set the outer right margin of the element to 20px;
margin-bottom: 30px; -- Set the bottom margin of the element to 30px;
margin-left: 40px; -- Set the left margin of the element to be 40px;
---------------------------------------------
margin: 10px 20px 30px 40px; -- On Right Next Left , Clockwise corresponds to
margin: 10px 20px 30px; -- On about Next
margin: 10px 20px; -- Up and down about
margin: 10px; -- Up, down, left and right 10px;

padding: padding , padding Property accepts a length value or percentage value , But negative values are not allowed .

h1 {
padding: 10px 0.25em 2ex 20%;
} <!-- Same as above -->

3.7.6 Add : color setting

There are three common ways of color selection :

Mode one : Set color name , for example :

red、green、blue、yellow、cyan、pink、white、black etc.
/* stay color Property, you can set the color 3 Ways of planting .*/

Mode two : Set up # Add six hexadecimal numbers You can directly use the color finder in that tool. You need to add #:

Tools ---- Color picker — Click the small straw to pick the color .

#FF0000(red)、#00FF00(green)、#0000FF(blue)、#FFFF00(yellow)、#00FFFF(cyan) etc.

Mode three : Set up rgb Color value ctrl +alt+a c This is to get rgb Trichromatic value of .

The essence is the same as the second way , Decimal representation .

rgb(255,0,0) 、rgb(0,255,0) 、rgb(0,0,255) 、rgb(255,255,0) 、rgb(0,255,255) etc.
(red) ( green) (blue) (yellow) (cyan)

3.8 Web development practice


complete JT The landing page in the project !

test :

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-EhbK0xjo-1637771955378)(C:\Users\Administrator\Desktop\ Cut the figure of \ Capture 03.PNG)]

 You can set some styles to distinguish
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title> Welcome to login </title>
<!-- introduce login.css Stylesheet file -->
<link rel="stylesheet" href="login.css"/>
</head>
<body>
<!-- The first part -->
<div id="header">
<img src="./imgs/logo.jpg"/>
<span class="line"></span> Because in the same line , Other block elements are exclusive lines
<span class="wel-login"> Welcome to login </span>
</div>
<!-- Main content -->
<div id="middle">
<img src="./imgs/show.jpg"/>
<form action="#"> In order to submit the content
<h1> Sign in </h1>
<div class="err-msg">
The account name and password do not match. Please re-enter
</div>
<input type="text" name="user" placeholder=" user name / Verified phone "/>
<input type="password" name="psw" placeholder=" password "/>
<input type="submit" value=" deng record "/>
</form>
</div>
<!-- The tail part -->
<div id="footer">
<p>
About us | Contact us | Talent recruitment | Businesses enter | Marketing center | Mobile Jingtao | Friendship connection | Sales alliance | Jingtao community | Jingtao public welfare | English Site
<br/>
Copyright 2015 - 2019 Jingtao jingtao.com copyright
</p>
</div>
</body>
</html>

.css file

@charset "UTF-8";
/* The style of the head */
#header{
/* background:pink; 333 */
height:72px;
width:1200px;
/* Margin : On about Next */
margin: 42px auto 0px;
}
#header img{ /* logo picture */
margin-left:50px;
margin-right:33px;
vertical-align: middle; Set the centerline alignment
}
#header .line{ /* A vertical bar */ Add a border to set the width and height, which is the vertical line
border:2px solid #676767;
height:68px;
/* span yes inline Elements , Can't set width and height , take span Set to
Inline block element , You can set the width and height */
display:inline-block;
margin-right: 22px;
vertical-align: middle;
background:#676767; // Add the same color as the border to the background color , So it won't show up , There is a blank in it .
}
#header .wel-login{ /* Welcome to login */
font-size:36px;
color:#676767;
vertical-align: middle;
}
/* The style of the body part */
#middle{
width:1200px;
margin: 42px auto 0px;
border: 7px solid #f0f0f0;
/* Set the width of the border , Up and down for 9px, To the left and right 7px */
border-width:9px 7px;
/* border-top-width:9px;
border-bottom-width:9px; */
}
#middle form{ /* The form in the body */
/* background:cyan; 333 */
width:385px;
margin-left:110px;
/* because form It's an element , Cannot display with other elements , therefore
take form Set to inline block element , In this way, both width and height can be set , Can also be combined with
Other in-line ( block ) Peers show */
display: inline-block;
/* Give Way form The top of is aligned with the top of the highest element in the current row */
vertical-align: top;
}
#middle form h1{ /* Login title in the form */
font-size:26px;
color:#333; /* # Six hexadecimal numbers ,AABBCC->ABC Every two can be abbreviated */
margin: 87px 0px 20px;
text-align:center;
}
#middle form .err-msg{ /* Error prompt after login failure */
font-size:16px;
color:#f00;
margin-bottom:9px;
text-align:center;
}
#middle form input{ /* All... In the form input */
width:381px;
height:49px;
border:2px solid #ededed;
font-size:16px;
color:#999;
text-indent:46px;
margin-bottom:32px;
}
#middle form input[name='user']{ /* User name input box */
/* background-image:url(./imgs/u_ico.jpg);
background-repeat:no-repeat;
background-position:15px 15px; */
/* background Is a composite attribute , It can be set at the same time The background color 、 Background image 、
Whether and how the background pictures are arranged repeatedly 、 The location of the background image */
background:#fff url(./imgs/u_ico.jpg) no-repeat 14px 14px;
}
#middle form input[name='psw']{ /* Password input box */
background:#fff url(./imgs/p_ico.jpg) no-repeat 14px 14px;
}
#middle form input[type='submit']{ /* The login button */
width:385px;
height:52px;
background-color:#f00;
font-size:20px;
color:#fff;
/* Override the first line indent set above */
text-indent:0px;
}
/* The style of the tail */
#footer{
/* background:yellow; 333 */
width:1200px;
margin: 144px auto 0px;
}
#footer p{ /* Links and copyright information */
text-align:center;
font-size:14px;
color:#999;
line-height:34px;
} After setting, you can zoom to the same proportion for comparison. What's the difference .

complete JT Registration page in project !( Selected as !)

Registration page video :https://www.bilibili.com/video/BV16K4y1x7wX

3.9 Extended content


3.9.1 display attribute

display Used to set the type of the element , Common values : display: Display , show

block: Default values for block level elements (block: block )
By default, one row is exclusive
Width and height can be set
inline: Default values for inline elements (inline: A column of )
By default, multiple in line elements can be on the same line
Generally, width and height cannot be set
inline-block: Inline block element
Multiple elements can be displayed on the same line , You can also set the width and height
none: Represents hidden elements

3.9.2 Element type ( understand )

div/span/p They are all container labels , For wrapping other content ( These elements themselves don't have much style !)

p: Block-level elements , Default exclusive line , Used to wrap a piece of text ( Paragraph Tags , Use for writing articles p Label each piece of content

div: Block-level elements , Default exclusive line , For wrapping other content , Set the style to div On , Can act on div On the content of .

span: Inline elements **, By default, it can be displayed on the same line as other elements .**

(1) Block-level elements (block)

By default , No matter how much content , Block level elements have one row exclusive

You can set the width and height , If it is set, use the set width and height

If you don't set the width and height , Where width is the default fill parent element , And the height is determined by the content ( Supported by content )

such as : div/p/h1~h6/form/table And so on are block level elements

(2) Inline elements (inline)

By default , Multiple in line elements can be on the same line

Cannot set width and height

such as : span/input/img/i/b/a And so on are in line elements

(3) Inline block element (inline-block)

It has the characteristics of in line elements ( Can be displayed in the same row ), It also has the characteristics of block level elements ( You can set the width and height )

3.10 css Box model

CSS box-model

css The box model Content +padding+border+margin

1. Standard box model ( Frame model )

width attribute = The width of the content

2. Weird box model ie6 as well as ie6 The following browsers Don't write When the document is declared

width attribute = Content width +padding +border

If you write a document statement , That is, the weird box model appears in the standard case :

box-sizing:border-box; // Weird box model

​ center-box;// Standard box model

3.11 css The way of positioning (5 Kind of )

Concept : Determine the location of the label

postion Attributes need to be associated with left,right,top,bottom( Bottom , At the bottom ) Continuous use

attribute :postion

Value :

1:relative: Relative positioning , Positioning relative to its default position .

2.absolute: Absolute positioning , Relative to the whole web Page positioning .

3.static: The default location method ( top left corner )

4.fixed: Fixed position , Relative to the whole web Page positioning , The position does not change with the position of the page .

5. Adhesive positioning :sticky yes CSS3 A new attribute , It can be said to be relative positioning relative And fixed positioning fixed The combination of , It is mainly used for scroll On the monitoring of events , Simply speaking , During scrolling , The distance between an element and its parent element is up to sticky Viscous positioning requirements when ( such as :top: 40px;)position: sticky The effect is equivalent to fixed location , Fix in place ( such as : Fixed above the distance screen 40px It's about )

Such as :

#pp1{

​ color: blue;

​ postion:absolute;

​ left:100px;

​ top:100px;

​ right:50px;

}

4 unit00- Introduction to the second stage of the course

4.1 Introduce yourself

Zhang Shenzheng

QQ: 731471199

Tel: 18518923147

 Insert picture description here

4.2 Web Course introduction


1、 Learning databases (MySQL) Knowledge about

2、 Learn how to use Java Program access database , Realize the operation of adding, deleting, modifying and querying (JDBC

3、 Learn the related technology of front-end page development (html,css,JavaScript,jQuery

4、 Study Servlet/JSP Related knowledge , Lay the foundation for the later frame

5、 Learn conversational techniques (Cookie and Session

5、 Learn enterprise level development project management tools -Maven, utilize Maven Build the project

6、 Study Mybatis frame ( encapsulation JDBC、 simplify JDBC)、SpringMVC frame ( encapsulation Servlet)、spring The basic operation of the three frameworks including the framework

7、 Study SSM Integration of the three frameworks , Use SSM The framework completes the Yonghe King store system

About SSM The three framework ,Web Stage is just the foundation of a framework , Just learn how to use and integrate simple frameworks , The high-level and low-level implementation of the framework will be in the third stage ( Frame stage ) To study !!!

4.3 Suggestions for learning methods


1、 The class focuses on listening , On the basis of understanding the knowledge points , Or after understanding the implementation idea of the program , Then write the code .

2、 Meet someone who doesn't understand 、 Mark those who fail to keep up , Insist on listening to the class completely , Solve the problem after class or make up the missed content in class .

3、 Having problems or BUG, By checking the error message , Analyze the cause of the problem ( Find... Yourself 5~10min), If you can't find the reason , Turn to others .

~ Ask the students around you
~ Ask the project manager
~ Ask the instructor

4、 Knock more code : Knock more code to achieve a certain degree of proficiency , Make the analogy !!!( myth : When you understand it, you think you will )

~ The amount of code is directly proportional to the salary !!!

5 unit00-MySQL Environmental testing

5.1 Check MySQL or mariadb Environmental Science


mariadb yes MySQL A branch of , It's developed by MySQL One of my team members , Database products developed after separation , Usage and MySQL Exactly the same , also mariadb Installation is better than MySQL Less prone to environmental problems .

There will be no more mariadb, Unified use MySQL Instead of ( namely : It can be understood as mariadb Namely Mysql)

mysql --verssion Check the installation MySQL Version of

(1) open CMD window , stay cmd Input in :mysql -uroot -proot (window+r , u p It represents the user name and password of the database you have installed )

If prompted, the following effects :

 Insert picture description here

shows MySQL The environment is normal , That is, the current computer has MySQL Software , And it's also configured with !!

(2) If you are prompted “mysql Not an internal or external command ” shows MySQL There's something wrong with the environment

 Insert picture description here

There are two possibilities :

a) The current computer has installed MySQL , But the environment variable is not configured

b) The current computer is not installed MySQL , No environment variables are configured

Let's verify the first case first

5.2 Check if... Is installed on the computer MySQL


By default ,mysql Will be installed in :C:\Program Files\ Under the table of contents ,

stay C:\Program Files\ Check whether there is MySQL Catalog , If there is , And the directory structure is as follows , It indicates that the database software is installed on the current computer .

 Insert picture description here

If database software is installed on the current computer , Then jump to step 3 , Direct execution 4 Step , To configure mysql Environment variables of ;

If the database software is not installed on the current computer , Then go to step three , Install the database software first , Then execute the 4 Step ;

5.3 install MySQL/mariadb database


stay ftp://cgb2xxx/02-web/unit00-software/ It has been uploaded mariadb Software , You can download it and install it , The installation steps are as follows .

mysql Every time you turn on the computer , It will follow windows Start the system automatically . You don't need to open it yourself ,

If you need to reinstall after installation , Then stop the service first ( Computer — Right click to manage — Services and Applications – service ---- Found installed mysql You can see that it is running ---- Right click — stop it , Then right click – attribute — Start type — Ban / Manual )

Find... In the software in the pre class materials MAriaDB Double-click on the run :

(1) Click Next directly in the following window

 Insert picture description here

(2) Check the check box in the window below , Agree to the agreement

 Insert picture description here

(3) The following window requires setting the software installation path , Keep the default

 Insert picture description here

(4) Set the password and confirm the password in the following window , It can be set to root

 Insert picture description here

(5) Set the name of the service in the following window ( As long as it does not conflict with the service name existing in the system ) and
port
( As long as it does not conflict with the ports occupied by other processes in the system )

A thread can only occupy one port

 Insert picture description here

(6) Click Next directly in the window below

 Insert picture description here

(7) Click on instal Can be installed

 Insert picture description here

5.4 To configure MySQL environment variable


(1) stay Computer / This computer On the icon Right click to find the attribute —> Advanced system setup —>
environment variable , Found in the pop-up window System variables , Find... In the system variables window
Path Variable , As shown in the figure :

 Insert picture description here

(2) find mysql Install under directory bin Catalog , Copy bin Path to directory , for example :( To ensure that this command can be used in any directory ,

Put this bin Directory configuration to path In the environment variables )
 Insert picture description here

(3) Path to be copied , Configuration to path In the environment variables . If it is win7 System , The configuration is as follows :

 Insert picture description here

(4) If it is win8 or win10 System :

 Insert picture description here

6 unit00-typora software configuration

6.1 Set the sequence number before the code block

How to set the line number in front of the code block , As shown in the figure below : ( To open notes, you need to use the soft key of pre class materials to open them after installation )

 Insert picture description here

The setup steps are as follows : stay typora The upper left corner of the software , Find... In the menu file —> preferences ( Or directly ctrl+ comma ), The following window will pop up , choice markdown, Find... On the right Code block , Check Show line number options .

After setting up , Close the preferences window , restart typora Software can !

 Insert picture description here

6.2 Set the number before the title

How at the first level 、 second level 、 The number is displayed in front of the third level title , The effect is shown below :

 Insert picture description here

The setup steps are as follows : stay typora The upper left corner of the software , Find... In the menu file —> preferences ( Or directly ctrl+ comma ), The following window will pop up , choice appearance , Find... On the right Open the theme folder , And click the .

 Insert picture description here

After clicking , The following folder will appear (themes), Find and open the base.user.css file ( If not, create the file ):

 Insert picture description here

Overwrite the contents of the file with the following contents :

@charset "utf-8";
#write{
counter-reset:h1}
h1{
counter-reset:h2}
h2{
counter-reset:h3}
h3{
counter-reset:h4}
h4{
counter-reset:h5}
h5{
counter-reset:h6}
#write h1:before{
counter-increment:h1;content:counter(h1) " "}
#write h2:before{
counter-increment:h2;content:counter(h1) "." counter(h2) " "}
#write h3:before, h3.md-focus.md-heading:before{
counter-increment:h3;content:counter(h1) "." counter(h2) "." counter(h3) " "}
#write h4:before, h4.md-focus.md-heading:before{
counter-increment:h4;content:counter(h1) "." counter(h2) "." counter(h3) "." counter(h4) " "}
#write h5:before, h5.md-focus.md-heading:before{
counter-increment:h5;content:counter(h1) "." counter(h2) "." counter(h3) "." counter(h4) "." counter(h5) " "}
#write h6:before, h6.md-focus.md-heading:before{
counter-increment:h6;content:counter(h1) "." counter(h2) "." counter(h3) "." counter(h4) "." counter(h5) "." counter(h6) " "}
#write>h3.md-focus:before, #write>h4.md-focus:before, #write>h5.md-focus:before, #write>h6.md-focus:before, h3.md-focus:before, h4.md-focus:before, h5.md-focus:before, h6.md-focus:before{
 color:inherit;border:inherit;border-radius:inherit;position:inherit;left:initial;float:none;top:initial;font-size:inherit;padding-left:inherit;padding-right:inherit;vertical-align:inherit;font-weight:inherit;line-height:inherit}
img{
border:2px dashed #333;border-radius:10px;zoom:80%}

Or go straight to :C:/ user /{ Current system users }/AppData/Roaming/Typora/themes/ Under this catalog , Find and open the base.user.css file ( If not, create the file ), And overwrite the content as above .

After setting up , Close the preferences window , restart typora Software can !

copyright:author[Cool breeze AAA],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130739150631.html