Java Review basic article--mysql database

The database is the warehouse that stores the data, its essence is a file system, the data is stored in the specific format, the user can add, modify, delete and query the data in the database.

　　

MySQL dos Windows startup shutdown command: net start MySQL and net stop MySQL

Login command: (1) mysql-u user name-p password (2) MySQL--host=ip address--user= username--password= password

　　

DOS Operation data garbled solution:

When we operate Chinese on DOS command line, we will error Error 1366 (HY000): Incorrect string value: ' \xd5\xc5\xc8\xfd ' for column ' username ' at row 1

Cause: Because the problem with MySQL's client code is UTF8, and the system's CMD window code is GBK

Solution (Temporary solution): Modify the MySQL client code.

Show variables like ' character% '; View all MySQL codes

(1) Modify the client code to GBK.

Set CHARACTER_SET_RESULTS=GBK; /set names GBK;

The above operation, only for the current window has an effect, if the server is disabled. If you want to permanently modify it, use the following method:

(2) There are my.ini files in the MySQL installation directory

DEFAULT-CHARACTER-SET=GBK Client encoding Settings

Character-set-server=utf8 server-side encoding settings

Note: Modify the finish profile to restart the service

===============================================================================================================

SQL statements:

To create and modify a table statement:

CREATE DATABASE Madata;
USER Madata;
/*
Create a format for a data table

CREATE TABLE Table name (
Column Name 1 data type constraints,
Column Name 2 data type constraints,
Column name 3 data type constraint
);
Create user table, user number, name, user's address
Sets the number column to be the PRIMARY key constraint, guaranteeing the data uniqueness of the column, non-nullability
Primary KEY Auto_increment
Allow primary key column data for automatic growth
*/
CREATE TABLE Users (
UID INT PRIMARY KEY auto_increment,
Uname VARCHAR (20),
Usex VARCHAR (20),
Uadress VARCHAR (200)
);

/*
Add a column, add a field
ALTER TABLE name add column name data type constraint
*/
ALTER TABLE Users ADD Tel INT;

/*
Modify the constraints of a column, modify it on an existing column
Modify column names, data type constraints
ALTER TABLE table name modify column name data type constraint
*/
ALTER TABLE Users MODIFY Tel VARCHAR (20);

/*
Modify column names
ALTER TABLE name change old column name new column name data type constraint
*/
ALTER TABLE Users Change Tel Newtell DOUBLE;

/*
Delete Column
ALTER TABLE name drop column name
*/
DESC users;
ALTER TABLE users DROP Newtell;

/*
Modify Table Name
Rename table name to new name
*/
RENAME TABLE users to NewUsers;
SHOW TABLES;

/*show tables Show All data sheets
Desc users view structure in table
Drop table users Delete data tables
*/
SHOW TABLES;
DESC users;
DROP TABLE users;

===============================================================================================================

Adding and Deleting statements:

CREATE TABLE Product (
ID INT PRIMARY KEY auto_increment,
Sname VARCHAR () not NULL,
Sprice DOUBLE
);

/*
To add data to a data table insert
Format:
Insert into table name (column name 1, column name 2, column name 3) VALUES (value 1, value 2, value 3)
Attention:
Column name, table name problem
corresponding problems,
Number, data type
The primary key ID is auto-grow without assigning a value
{Add data format without regard to primary key
Format:
Insert into table name (column name) values (value)
[When the designation is added to the 20-bit position, no value is assigned to the ID.
The primary key still grows automatically (not after 2, but grows after you specify the ID)
The effect is as follows:
]
*/
INSERT into Product (Id,sname,sprice) VALUES (1, ' Notebooks ', 5888.99);
INSERT into Product (sname,sprice) VALUES (' TV ', 1888.88);
INSERT into Product (sname,sprice) VALUES (' Apple ', 1888.88);
INSERT into Product (Id,sname,sprice) VALUES (20, ' washing machine ', 888.22)
INSERT into Product (sname,sprice) VALUES (' phone ', 888);
INSERT into Product (sname,sprice) VALUES (", 777);
/*
Add data format, all values are given
Format:
Insert into table name values (full column values)
*/
INSERT into Product VALUES (5, ' microwave ovens ', 788);

/*
Add data format, bulk write
Format:
Insert into table name (column name 1, column name 2, column name 3) VALUES (value 1, value 2, value 3), (value 1, value 2, value 3)
*/
INSERT into Product (Id,sname,sprice) VALUES (6, ' millet ', 4888),
(7, ' Bumblebee ', 7899),
(8, ' Red rice ', 7854);

/*
！！！ When manually deleting the data ID (primary key) added by the selected item, it will continue to grow automatically.
The missing ID (primary key) can continue to specify to add data
*/


/*
Update table name set column 1= value 1, column 2= value 2 where condition
Where Condition: uniqueness in data
*/

--Change the TV set, the price rises to 5999
UPDATE product SET sprice=5999 WHERE id=2;
UPDATE product SET sprice=5888 WHERE sname= ' TV ';

--Change the TV, name to black and white machine, price, 100
UPDATE product SET sname= ' black and white Machine ', sprice=100 WHERE id=2;
UPDATE product SET sname= ' color TV ', sprice=200 WHERE sname= ' black and white machine ';

/*
Modifying the wording of a condition
Id=6
Id<>6 Not equal to
Id<=6
With or not (&& | |!)
&& and
|| Or
! Not

ID in (1,3,4,5,6) contains [all data representing ID in 13456]
*/
--The price of the notebook, and the price of the microwave oven, all modified to 2000
UPDATE product SET sprice=2000 WHERE id=1 OR id=5;
UPDATE product SET sprice=50000 WHERE sname= ' notebook ' OR sname= ' microwave oven ';

/*
Delete data from a table
Format:
Delete from table name where condition

drop table name deletes entire data table
*/
--delete Xiaomi Delete bumblebee
DELETE from product WHERE id=6;
DELETE from product WHERE sname= ' bumblebee ';

===============================================================================================================

Query statement:

CREATE TABLE Zhangwu (
Sid INT PRIMARY KEY auto_increment,
Sname VARCHAR (+) not NULL,
Smoney DOUBLE
);

INSERT into Zhangwu (Sid,sname,smoney) VALUES (1, ' eating expenses ', 350.22);
INSERT into Zhangwu (Sid,sname,smoney) VALUES (2, ' costume expense ', 8555);
INSERT into Zhangwu (Sid,sname,smoney) VALUES (3, ' Ordinary income ', 5777);
INSERT into Zhangwu (Sid,sname,smoney) VALUES (4, ' beverage expenditure ', 5453);
INSERT into Zhangwu (Sid,sname,smoney) VALUES (5, ' sell clothes income ', 46456);
INSERT into Zhangwu (Sid,sname,smoney) VALUES (6, ' eating expenses ', 4654);

/*
Querying data for a specified column
Format:
Select Column Name 1, column name 2 from table name
*/
SELECT Zname,zmoney from Zhangwu;

/*
Querying data for all columns
Format:
SELECT * FROM table name
*/
SELECT *from Zhangwu;

/*
Query removes duplicate records
SELECT DISTINCT the name (key) to query from table name
*/
SELECT DISTINCT sname from Zhangwu;
SELECT DISTINCT Smoney from Zhangwu;
SHOW TABLES;
DESC Zhangwu;
SELECT *from Zhangwu;
/*
Query renaming columns
As keyword
[SELECT to query the name as ' temporary change of name ' (' can be added without!!! ) from table name]
*/
SELECT sname as ' name ' from Zhangwu;
SELECT * from Zhangwu as ZW;
/*
In the query data, direct mathematical calculation
column to calculate the number
*/
SELECT sname,smoney+1000 as ' sum ' from Zhangwu;

--Check all meal expenses.
SELECT * from Zhangwu WHERE sname = ' eating expenses ';

--query amount greater than 1000
SELECT * from Zhangwu WHERE Smoney >1000;

--query amount from 4000 to 7000
SELECT * from Zhangwu WHERE Smoney <7000 and Smoney >4000;

--transform into between and mode
SELECT * from Zhangwu WHERE Smoney between 4000 and 7000;

--Query amount is one of 5777,8555,5453
SELECT * from Zhangwu WHERE Smoney = 5777 or Smoney =8555 or Smoney = 5453;

--Transforming into in mode
SELECT * from Zhangwu WHERE Smoney in (5777,8555,5453);


--like fuzzy query mates wildcard
--Query all expenses
SELECT * from Zhangwu WHERE sname like '% expenditure% ';


--Check the account name, five-character
SELECT * from Zhangwu WHERE sname like ' _____ ';

--Check the account name, not empty
SELECT * from Zhangwu WHERE sname are not NULL;
SELECT * from Zhangwu WHERE isn't (sname is NULL);

/*
Query sort
*/

/*
Query, sort the result set
Ascending, descending, sorting on the specified column
Order BY column name [DESC][ASC]
Desc Descending
ASC in ascending order, can not write
*/
--Query the account form, price in ascending order
SELECT * from Zhangwu ORDER by Smoney ASC;

--Query the accounting table, price in descending order
SELECT * from Zhangwu ORDER by Smoney DESC;

--Check the account form, check all expenses, and arrange the amount in descending order
--First filter condition where query results reorder
SELECT * from Zhangwu WHERE sname like '% expenditure ' ORDER by Smoney DESC;


/*
Aggregate function Query
*/
/*
Querying calculations using aggregate functions
*/

--Count sum, sum count of the number of data in the table (column name)
--How many data are there in the Query Statistics account table?
SELECT COUNT (*) from ZHANGWU;
SELECT count (SID) as ' number ' from Zhangwu;

-Sum sum, sum the data in a column sum (column name)
--The Accounting table query, the sum of all the sums calculated
SELECT SUM (Smoney) from Zhangwu;
SELECT SUM (Smoney) as ' total amount ' from Zhangwu;

--summation, the total amount of all expenses to be counted
SELECT SUM (Smoney) from Zhangwu WHERE sname like '% expenditure ';
SELECT SUM (Smoney) as ' expenditure amount ' from Zhangwu WHERE sname like '% expenditure ';

--Max min function, gets the maximum minimum value for a column of data
SELECT MAX (Smoney) from Zhangwu;
SELECT MIN (Smoney) from Zhangwu;

--Avg function to calculate the average of all data in a column
/* If there is a null value then do not count the number of not participating in the calculation */
SELECT AVG (Smoney) from Zhangwu;
SELECT AVG (Smoney) from Zhangwu WHERE sname like '% expenditure ';



/*
Group queries
*/

/*
Querying all the data
What is the total cost of meals
What is the total wage income?
Total clothing expenditure
How much does the stock income total
How much does it cost to play mahjong?

Grouped queries: Group By column names
Must follow the aggregation function
When a select query is selected, the grouped columns appear after the Select Selection column
*/
SELECT SUM (Zmoney), zname from Zhangwu GROUP by Zname

--Group queries for zname content are summed, but as long as the expense
SELECT SUM (Zmoney) as ' Getsum ', zname from Zhangwu WHERE zname like '% expenditure% '
GROUP by Zname
ORDER by Getsum DESC

--Zname The contents of the group query sum, but as long as the expenditure, show the amount of more than 5000
--After the result set is a group query, filter again, cannot use where, filter again after grouping, the keyword has
SELECT SUM (Zmoney) as ' Getsum ', zname from Zhangwu WHERE zname like '% expenditure% '
GROUP by Zname have getsum>5000
/* If you do not make a temporary change to the name of the money, you must write the name full sum (Smoney) to filter the filter again
*/
SELECT SUM (Smoney), sname from Zhangwu WHERE sname like '% payout ' GROUP by sname have SUM (Smoney) >6000;

===============================================================================================================
Multi-Table query:

• Ways to query Multiple tables:

* Cross Connection:

* SELECT * from a B; ---obtained is the Cartesian product of two tables.

* Inner Connection: INNER JOIN--inner can be omitted

* Explicit Intra-connection: SELECT * from A inner join B on condition;

* SELECT * FROM customer C INNER JOIN orders o c.cid = o.cid;

* Implicit internal connection: SELECT * from a a Where condition;

* SELECT * FROM customer C, orders o WHERE c.cid = o.cid;

* Outer connection: OUTER JOIN--outer can be omitted

* LEFT outer connection: OUTER JOIN--select * from A ieft outer join B on condition;

* SELECT * FROM customer C left OUTER JOIN orders o c.cid = o.cid;

* Right outer connection: OUTER JOIN--select * from A outer join B on condition;

* SELECT * FROM customer C right OUTER JOIN orders o c.cid = o.cid;

Subqueries for multi-table queries:

• One SQL statement query needs to rely on another query statement.

SELECT * FROM Customer c,orders o where c.cid = O.cid and C.cid in (SELECT CID from orders WHERE addr like ' Guizhou% ');

Java Review basic article--mysql database