[SQL] MySQL installation and usage and SQL syntax Introduction

Document directory

• MySQL Installation
• Modify the default encoding type of MySQL
• Simple use of database management tool navicat premium
• Appendix: Basic SQL syntax

In Java Development, database applications are very necessary. Below, we will make some necessary preparations for Java for database applications. ,

Java applications for databases are collectively referred to as JDBC.

JDBC (Java Data Base connectivity, Java database connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java.

Install two software: MySQL and navicat premium ).

The two software can be easily searched and downloaded in Baidu. The download link is not provided here.

The installation of MySQL software is relatively simple, and most of them can be done simply by clicking Next. Therefore, here we only mention the areas that need attention. On this page, select the installation type, including "typical (default)", "complete (complete)", and "Custom (custom. To change the installation path, select Custom ". We recommend that you do not place data in the same partition as the operating system to prevent data from being cleared during system backup and restoration. After the installation is complete, the following interface will appear: Here is the MySQL Configuration Wizard, mark the front of "configure the MySQL server now, click "finish" to end software installation and start the MySQL Configuration Wizard. You don't have to manually configure my. ini as before. The following interface selects the configuration method, "detailed configuration (manual precise configuration)", and "standard configuration )". For convenience, we select standard configuration. The following interface sets the MySQL password, which will be frequently used in the future. After that, keep selecting the default value and click Next to complete the configuration of MySQL. Modify the default encoding type of MySQL to Latin1. Latin1 does not support Chinese characters. To support Chinese characters, you must change the default encoding of the database to GBK or utf8. We generally change it to utf8.

Go to the MySQL installation directory, find the my. ini file, and open it in Notepad format. Search for Latin1 and change it to utf8. You need to modify two of them, as shown below. After modification, save and exit. To save the settings, we need to restart MySQL. Log on to the [SERVICE] of your computer, find MySQL, exit, and enable it again. The simple use of the database management tool navicat premium is very easy to install. Now we need to connect navicat premium and MySQL together. Click Connect and enter the password you just set during MySQL installation. Connect. Right-click to open the connection. Click test to create a new form. This parameter is required if null values are not checked. Duplicate content is not allowed in the primary key and index columns. After the form column is created, save and exit. Right-click Open table to edit the form content. Example of SQL addition, deletion, modification, and query

# Add insert into t_userr (username, password, sex) values ('JJ ', '11', 'n'); # delete from t_userr where username = 'JJ '; # update t_userr set ruxueyear = '000000', note = "utilities" where username = 'JJ '; # Check select ID, username from t_userr where sex = 'male '; select username from t_userr; select realname from t_userr where sex = 'male' and school = 'metering in China'; select * From t_userr; # select * From t_userr where sex = 'femal' for all information ';

Appendix: Basic SQL Syntax 1. Description: Create a database
Create Database database-name 2. Description: delete a database
Drop database dbname
3. Description: Back up SQL Server
--- Create a device for the backup data
Use master
Exec sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ mynwind_1.dat'
--- Start backup
Backup database pubs to testback
4. Description: Create a new table.
Create Table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A: Create Table tab_new like tab_old (use the old table to create a new table)
B: Create Table tab_new as select col1, col2... From tab_old definition only
5. Description: delete a new table.
Drop table tabname
6. Description: Add a column.
Alter table tabname add column Col type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.
7. Description: Add a primary key: alter table tabname add primary key (COL)
Delete a primary key: alter table tabname drop primary key (COL)
8. Description: Create an index: Create [unique] index idxname on tabname (COL ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
9. Description: Create view viewname as select statement
Delete view: Drop view viewname
10. Description: several simple basic SQL statements
Select: Select * From Table1 where range
Insert: insert into Table1 (field1, field2) values (value1, value2)
Delete: delete from Table1 where range
Update: Update Table1 set field1 = value1 where range
Search: Select * From Table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information!
Sort: Select * From Table1 order by field1, field2 [DESC]
Total: Select count as totalcount from Table1
Sum: Select sum (field1) as sumvalue from Table1
Average: Select AVG (field1) as avgvalue from Table1
MAX: Select max (field1) as maxvalue from Table1
Min: select Min (field1) as minvalue from Table1
11. Description: several advanced query Operators
A: Union operator
The Union operator combines two other result tables (such as Table1 and table2) and removes any duplicate rows from the table to generate a result table. When all is used together with Union (that is, Union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either Table1 or table2.
B: Random t operator
The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When all is used with distinct T (distinct t all), duplicate rows are not eliminated.
C: intersect Operator
The Intersect operator derives a result table by only including the rows in Table1 and Table2 and eliminating all repeated rows. When all is used with intersect (intersect all), duplicate rows are not eliminated.
Note: The query results of several computation words must be consistent.
12. Note: use external connections
A. Left (outer) join:
Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected table.
SQL: select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C
B: Right (outer) join:
Right Outer Join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table.
C: Full/Cross (outer) join:
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.
12. Group: Group:
A table can only obtain group-related information after the query.
Group-related information: (Statistical Information) Standard of Count, sum, Max, Min, and AVG groups)
When grouping in sqlserver: fields of the text, ntext, and image types cannot be used as grouping bases.
Fields in the Selecte statistical function cannot be put together with common fields;
13. perform operations on the database:
Detaching a database: sp_detach_db; appending a database: sp_attach_db indicates that the complete path name must be appended.
14. How to modify the Database Name:
Sp_renamedb 'old _ name', 'new _ name'