MySQL Basics 1

Type	Mysql	Sql server	http	Tomcat	Oracle
Port	320W	1433	80	8080	1521

|----- dos command to start the MySQL service

Stop MySQL: net stop MySQL

Start MySQL: net start MySQL

|----- Connect, quit MySQL

Format: mysql-h host Address-u user name P user password

- There are spaces before, h for the server to enter (the machine can not write), U on behalf of the user name (the user name is the default root), p for the password (the default password is p0) the. mysql prompt is mysql>.

Quit MySQL command: Quit or exit

|----- Change the password.

Set password=password (' New password '); /*mysql Command * /

Mysqladmin-u Username-P original password password new password/*dos command, cannot go into MySQL execution * /

|----- Operation Skills

1 If you hit the command, you find that you forgot to add a semicolon, do not need to re-command, as long as the English status of the semi-colon to enter it. In other words, you can break a complete command into a few lines, and then use a semicolon as the end sign.

2 You can use the cursor up and down keys to bring up previous commands.

3. if your SQL command executes, an error is made. You can find the near word in the error message and it will tell you where the error is.

|-----mysql command comment

/* I can comment * * * I can comment

|-----mysql command

Discard the command being entered: \c

Display command list: \h

Quit MySQL program: \q

View MySQL server status information: \s

Operation of the database:

Show databases; /* Show Database list * /

Create database name; /* Build Library * /

Drop [if exists] database name; /* Delete database * /

Use database name;/* selected database /*

Table Data manipulation:

Show tables; /* View the tables in the database * /

Create table table name ( field name Type [ constraint] [, field 2. .. ]/* Build table * /

describe table name; or desc table name;/* show the structure of the data table * /

drop [if exists] table name;/* Delete datasheet * /

Delete from table name; /* empty data table * /

Select * from table name; /* show the records in the table * /

Insert into table name (field name 1, field name 2. ) .. ) VALUES ( value 1, value 2. ) ..... /* Insert the data from the specified field into the table */

Update table Name set field name = value [where condition]/* Modify data in table * /

1 Course Review
Custom Tags & Coding combat
1) Custom Label steps:
1.1 Write Tag processor class, Inherit Simpletagsupport class, overwrite Dotag method
1.2 Creating TLD files in the Web-inf directory, configuring labels on TLD
1.3 Import Tag Library on JSP page, use taglib instruction
1.4 Using tags in a tag library in a JSP page
2) Custom Label life cycle:
Simpletag Interface:
Setjspcontext (Jspcontext context)--Incoming Pagcontext object
SetParent (Jsptag tag)--Incoming Parent tag object
Setxxx (parameter)-Assigning a value to a property
Setjspbody (jspframent jspbody)--Incoming tag body content
Dotag ()--Execute label
3) The role of custom Tags:
3.1 Controlling whether to output label body contents
Output: This.getjspbody (). Invoke (NULL)
No output: Do not invoke the Invoke (null) method
3.2 Control whether the remaining contents of the label output
Output: What NOT to do!
No output: Throw Skippageexception exception
3.3 Repeating output tag body contents
Repeat call: This.getjspbody (). Invoke (NULL)
3.4 Modifying label body contents
StringWriter SW = new StringWriter ();
This.getjspbody (). Invoke (SW);
String content = sw.tostring ();
Modify Content
Manual output to Browser
This.getjspcontext (). Getout (). Writer (modified content);
3.5 Labels with attributes
A) Declare member variables and setter methods in the tag processor class to assign values to property variables.
b) Declaring the attribute in the TLD file
c) Using Attributes
4) JavaBean Specification
4.1 must have an argument-free construction method
4.2 All member properties must be privatized (private)
4.3 Public getter and setter methods must be provided
5) MVC Development Model
MVC is Servlet+jsp+javabean's development model.
M,model,javabean implementation, encapsulating business data
v,view,jsp implementation, displaying data
C,controller,servlet implementation, receive parameters, invoke business logic, jump view
6) Three-layer structure development
DAO Layer: Data Access object. Implementing methods related to the operation of the data
Service layer: Business logic object. Implement methods related to the business logic of the project
Web layer: The presentation layer. Deal with the user directly related, receive parameters, processing parameters, jump view, display data.

Today's goal: MySQL database
2 Getting Started with databases
2.1 Introduction
Data is saved to memory:
Advantages:
1) Read and write very quickly
Disadvantages:
1) data loss due to program shutdown

Data saved to file:
Advantages:
1) data can be saved permanently
Disadvantages:
1) Frequent IO operation, not high efficiency!
2) data management is not convenient. For example, querying a data needs to be read out and then matched.

Data is saved to the database software:
Advantages:
1) data is permanently saved
2) data management is very convenient. (e.g. query very fast and convenient)

Data can be said to be the soul of the enterprise!!
2.2 What is a database software
Database, commonly known as the warehouse of data. A software (or program) that facilitates the management of data.
2.3 Database software on the market
Oracle, Oracle Company's products. The most popular database software currently used. Very good compatibility with the Java language.
Suitable for medium-large, medium and large applications.

SQL Server: is a Microsoft company product. The window platform is widely used. Very good compatibility with c#,net platform.

DB2:IBM Company's products. IBM server--UNIX-db2-> Websphere

MySQL: The product of open source organization. Products of Oracle Corporation. Free!!! and Java language compatibility very good! Suitable for SMEs, small and medium-sized applications

relational database.

MongoDB: Non-relational database.

Learn MySQL First, then learn Oracle later
2.4 MySQL Getting Started
1) download to MySQL website.
2) Install MySQL software
3) Use
Verify success
Turn on cmd--input mysql-u root-p Enter password return
C:\users\apple>mysql-u root-p
Enter Password: * * *
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server version:5.5.40 MySQL Community Server (GPL)

Copyright (c), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>
3 MySQL Database
3.1 MySQL data storage structure
First the database, then the table, then the data
4 Database Management
4.1 Querying all databases
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema | --MySQL metadata, basic data
| MySQL | --mysql the configuration database, which contains user information. (User name and password, rights Management)
| Performance_schema | --mysql database software operation data, log information, performance data
| Test | --Test the database. The empty
+--------------------+
4 rows in Set (0.00 sec)

4.2 Creating a Database
Mysql> CREATE DATABASE Day15--Specify the default character set for creating databases
--Default character set UTF8
;
Query OK, 1 row Affected (0.00 sec)

4.3 Viewing the default character set for a database

Mysql> Show CREATE Database day15;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| DAY15 | CREATE DATABASE ' day15 '/*!40100 DEFAULT CHARACTER SET UTF8 */| |
+----------+----------------------------------------------------------------+
1 row in Set (0.00 sec)

4.4 Deleting a database
mysql> drop Database day15;
Query OK, 0 rows affected (0.01 sec)

4.5 Modifying the database

mysql> ALTER DATABASE DAY15 default character set GBK;
Query OK, 1 row Affected (0.00 sec)

5 Table Management
Select Database

5.1 View All Tables
Mysql> Show tables;
+-----------------+
| TABLES_IN_DAY15 |
+-----------------+
| Student |
+-----------------+
1 row in Set (0.00 sec)

5.2 Creating a table
Mysql> CREATE TABLE Student (
-Sid int,
sname varchar (20),
Sage INT
);
Query OK, 0 rows affected (0.01 sec)

5.3 View Table structure

mysql> desc Student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sid | Int (11) | YES | | NULL | |
| sname | varchar (20) | YES | | NULL | |
| Sage | Int (11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in Set (0.01 sec)

5.4 Deleting a table

mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

5.5 Modifying a table
1) Add Field

Mysql> ALTER TABLE student add column Sgender varchar (2);
Query OK, 0 rows affected (0.03 sec)
records:0 duplicates:0 warnings:0

2) Delete Field

mysql> ALTER TABLE student drop column sgender;
Query OK, 0 rows affected (0.03 sec)
records:0 duplicates:0 warnings:0

3) Modify the field type

mysql> ALTER TABLE student Modify column remark varchar (100);
Query OK, 0 rows affected (0.07 sec)
records:0 duplicates:0 warnings:0

4) Modify the field name

mysql> ALTER TABLE student change column sgender gender varchar (2);
Query OK, 0 rows affected (0.03 sec)
records:0 duplicates:0 warnings:0

5) Modify Table name

mysql> ALTER TABLE student rename to teacher;
Query OK, 0 rows affected (0.01 sec)

6 Adding and deleting changes data

--******** Data *********---
--1.1 Additional data
--Insert all fields. Must sequentially insert
INSERT into student VALUES (1, ' Zhang San ', ' Male ', 20);
--Note that you cannot have fewer or more field values
--INSERT into student VALUES (2, ' John Doe ', ' female ');
--Insert Some fields
INSERT into student (Id,name) VALUES (2, ' John Doe ');

--1.2 Modifying data
--Modify all data (less recommended)
UPDATE student SET gender= ' female ';
--Modified with conditions (recommended)
UPDATE student SET gender= ' man ' WHERE id=1; --Modify the student with ID 1, change gender to male
--Modify multiple fields, note: SET field name = value, field name = value,....
UPDATE student SET gender= ' male ', age=30 WHERE id=2;

--1.3 Delete data
--Delete all data (less recommended)
DELETE from student;
--Conditional removal (recommended)
DELETE from student WHERE id=2;
--Another way
--Delete from: can delete all tables 1) can be conditionally deleted 2) can only delete the table's data, cannot delete the table's constraints 3) using delete from the deleted data may be rolled back (transaction)
--TRUNCATE TABLE: can delete all tables 1) cannot be conditionally deleted 2) that is, you can delete data from a table, or you can delete a table constraint 3) data deleted using TRUNCATE TABLE cannot be rolled back
TRUNCATE TABLE student;

7 Querying data (highlights)
7.1 Querying all Columns
--2.1 Querying all columns
SELECT * from student;

7.2 Querying the specified column
--2.2 Query the specified column
SELECT Id,name,gender from student;

7.3 Adding a constant column when querying
--2.4 Adding a constant column when querying
--Demand: Add a class column when querying the student table for "Java Employment class"
SELECT id,name,gender,age, ' Java Employment class ' as ' grade ' from student;

7.4 Merging columns when querying
--2.5 merging columns when querying
--Demand: Query the overall scores of each student's servlet and JSP
SELECT Id,name, (servlet+jsp) as ' total ' from student;
--Note: Merge columns can only merge fields of numeric types
SELECT ID, (name+servlet) from student;

7.5 Removing duplicate records when querying
--2.6 Removal of duplicate records when querying (DISTINCT)
--Demand: Check the gender of the students
SELECT DISTINCT gender from student;
--Another syntax
SELECT DISTINCT (gender) from student;
--demand: Find out where students are located
SELECT DISTINCT address from student;

7.6 Item Query
--2.7-piece query (where)
--2.7.1 Logical condition: and (with) or (or)
--Demand: Query ID 2, and the student named John Doe
SELECT * FROM student WHERE id=2 and name= ' John Doe '; --Intersection

--Demand: Query ID 2, or student named Zhang San
SELECT * FROM student WHERE id=2 OR name= ' Zhang San '; --and set

--2.7.2 Comparison conditions: > < >= <= = <> (not equal to) between and (equivalent to >= and <=)
--Demand: query for students with a servlet score greater than 70 points
SELECT * FROM student WHERE servlet>70;

--demand: Query JSP score is greater than or equal to 75, and less than or equal to 90 points of students
SELECT * FROM student WHERE jsp>=75 and jsp<=90;
--Another syntax
SELECT * FROM student WHERE JSP between and 90; --(after the package before package)

SELECT * FROM student WHERE gender<> ' man ';

--2.7.3 empty condition (null null string): is null/is not null/= '/<> '
--Requirements: Students who are queried for empty addresses (both null and empty strings)
--Null vs empty string
--Null: Indicates no value
--empty string: a value!
--Judging null
SELECT * FROM student WHERE address is NULL;
--Judging the empty string
SELECT * FROM student WHERE address= ';

SELECT * FROM student WHERE address is NULL OR address= '; --(including null and empty strings)

--Requirements: Querying students with addresses (not including null and empty strings)
SELECT * FROM student WHERE address was not NULL and address<> ';

--2.7.4 fuzzy condition: Like
--Usually the following replacement tags are used:
--%: denotes any character
--_: Represents a character
--demand: The student who inquires the surname ' Zhang '
SELECT * FROM student WHERE NAME like ' li% ';

--demand: A student whose name is ' Li ' and has only two characters
SELECT * FROM student WHERE NAME is like ' li _ ';

7.7 Aggregate Queries
--2.8 Aggregate queries (queries using aggregate functions)
--Common aggregate function: SUM () avg () max () min () count ()
--Demand: Query the student's servlet's Total (sum (): Sum function)
SELECT SUM (servlet) as ' servlet total ' from student;

--Demand: Query the average score of a student's servlet
The average of SELECT avg (servlet) as ' servlet ' from student;

--Demand: Query current servlet highest score
SELECT Max (servlet) as ' highest score ' from student;

--Demand: Query the lowest score
SELECT min (servlet) as ' lowest score ' from student;

--Demand: Statistics How many students are currently (count field)
SELECT COUNT (*) from student;

SELECT COUNT (ID) from student;

--Note: the count () function counts the number of data that does not contain null
--Use the Count tab to use a field that does not contain a null value
SELECT COUNT (age) from student;

7.8 paged Query
--2.9 paged query (limit start line, query a few lines)
--start line starting from 0
--Pagination: The current page shows how many bars per page
--Paged Query the current page of the data Sql:select * from Student LIMIT (current page-1) * How many bars per page, how many bars per page;

--demand: Inquiry 1th, 2 (Data on page 1th)
SELECT * FROM student LIMIT 0, 2;
--Query 3rd, 4 records (Data on page 2nd)
SELECT * FROM Student LIMIT 2, 2;
--Query 5th, 6 Records (data on page 3rd)
SELECT * FROM Student LIMIT 4, 2;
--Query 7th, 8 Records (no record does not show)
SELECT * FROM student LIMIT 6, 2;

7.9 Query Sort
--2.10 Query sort (order by)
--Syntax: ORDER BY Field Asc/desc
--ASC: Sequential, positive sequence. Value: Increment, Letter: Natural order (A-Z)
-desc: Reverse, reverse order. Value: Decrement, Letter: Natural Inverse (z-a)

--By default, sort by insert record order
SELECT * from student;

--Requirements: Sort by ID Order
SELECT * FROM student ORDER by ID ASC;
SELECT * FROM student the ORDER by ID; --Default positive order

SELECT * FROM student ORDER by ID desc;--reverse order

--Note: multiple sort criteria
--demand: According to the servlet positive sequence, according to the reverse of the JSP
SELECT * FROM student ORDER by servlet asc,jsp DESC;

7.10 Group queries
--2.11 Packet Query (group BY)
-Demand: The number of men and women to inquire
-Expected results:
--Male 3
---female 2
-1) Grouping students by gender (group by gender)
--2) count the number of people per group (COUNT (*))
SELECT Gender,count (*) from student GROUP by gender;

7.11 Filtering after grouping queries
--2.12 Filter After grouping query
--demand: Search for the total number of people greater than 2 gender
--1) Number of men and women in search
-2) screening of records with a population greater than 2 (having)
---Note: Before grouping conditions using the WHERE keyword, before grouping conditions using the HAVING keyword
SELECT Gender,count (*) from student WHERE GROUP by gender have COUNT (*) >2;

Summarize:
MySQL Basics
1) MySQL database function: Manage data
2) MySQL storage structure:
Databases: Managing Databases (CRUD)
Table: Management tables (CRUD)
Data: Managing Data
Change and delete:
Inquire:
12 Types of queries (single-table query)

MySQL Basics 1