Java training notes (7) mysql

Java training (7) mysql article after a tense six-day training, today is the seventh day, today also enters the Database Study, here using the mysql database. Before learning about mysql, you must first understand several concepts. In the world we are in contact with, we need to store a large amount of information, such as the storage of bank data and Forum information.

Java training (7) mysql article after a tense six-day training, today is the seventh day, today also enters the Database Study, here using the mysql database. Before learning about mysql, you must first understand several concepts. In the world we are in contact with, we need to store a large amount of information, such as the storage of bank data and Forum information.

Java training (7) mysql

After six days of intense training, today is the seventh day. Today, I am also learning about the database. Here I will use the mysql database.

Before learning about mysql, you must first understand several concepts.

In the world we are in contact with, we need to store a large amount of information, such as bank data storage and Forum information storage. There are two ways to store information: databases and files. Now we need to understand the differences between the database and the file method, and the file method lacks security.

Database: a set of data. Another preparation point is the set of tables. A relational database is a set of associated tables.

A table consists of rows and columns.

Relational Database Management System: A software used to manage relational databases. RDBMS for short, the vast majority of database products currently used in the market are RDBMS.

Next we will learn how to create a table through the function? After learning this, we can see at least a function description to understand how many tables need to be created to store information.

For example, to implement the basic functions of a Forum: User Registration, posting, replying, and adding forum.

Based on the preceding descriptions, we need to create the following tables: User table, post table, reply table, and Forum table.

That is based on what table is created: the simplest way to create a table: nouns. First, find the nouns in the function description. At the same time, only one thing can be described in a table.

Next, go to mysql learning and usage. First, go to mysql. You can go to the mysql installation directory through the mysql client or through a command prompt. in the bin folder, then enter the mysql command.

Syntax: mysql-u username-p

Example: mysql-u root-p

Set the character set below

Set the character set. You can change the "my. ini" file under the mysql installation directory, change the "default-character-set" option in the file, change the "Chinese" option to "gbk", and restart the service.

Enter mysql and enter the command show variables like 'Char % '. (show is required for displaying information in mysql, and like fuzzy operation is also used here, in like, % represents any character. This command is used to view character settings)

To view the lantin1 character set in the displayed information, you must change it to gbk. Command Format: set character option = gbk, or use set names gbk;

For example: set character_set_server = gbk;

The following describes how to create and use databases and tables.

In the creation table, you need to know the mysql DATA type. The data types are roughly divided:

1. numeric type: integer and Uncle type, integer: tinyint, smallint, mediumint, bigint, precise decimal, numeric, float, and real ), exact decimal places can specify the bandwidth of the data and the number of decimal places.

2. Character Type: char, varchar, and text ).

3. binary data: blob type.

4. date type: datetime, date, time, and timestamp.

After specifying the data type, the ultimate goal is to know which data type to choose.

When creating a table, you must specify constraints: Constraints: primary key constraints: used to differentiate entities. Foreign key constraints: Implement constraints between tables to ensure the value of foreign key fields, must exist in the master table.

Code:

ALTER TABLEstdinfo drop column sex and column address (delete the sex and address columns)

ALTER TABLEstdinfo MODIFY address varchar (100) (Change columns)

Alter table stdinfoCHANGE address varchar (100) (rename a column)

ALTER TABLEstdinfo drop primary key (delete primary key)

ALTER TABLEstdinfo auto_increment = 5 (change the starting value of the auto-increment column)

After creating a table, we need to perform data operations. In the standard SQL language, we use insert, update, delete, and select to add, update, delete, and view tables.

Their respective syntaxes and examples are as follows:

Add: insert into Table Name (column name ....) Values (value .....)

In the insert statement, into is optional and the column name is optional. If the column name is not written, a complete data record is inserted. When the column name is written, the values of a specific column are inserted.

Example: insert into stdinfo values ('20170901', 'zhangfe', 'male', 'hebei ');

Insert into stdinfo (sid, sname, sex) values ('20140901', 'guan Yu ', 'male ')

Update: update table name set column name = value ...... Where condition

Delete * from table name where Condition

View: select column name... From table name where condition group by field order by field asc | desc limit start position, number of items

Use and or to connect multiple conditions.

Queries are classified into exact search and fuzzy search. Exact search uses the equal operator. Fuzzy search uses the like operator. In the like operator, two wildcards, one % and one _ can be used _, % represents any character, and _ represents a character. You can also use regular expressions in like, which will be described in detail later.

After learning about the SQL statement in detail, the following describes how to import and export data.

To export data, you need to use the mysqldump command. Here, the exported SQL file is located in the current directory. You can also specify the specific location.

Mysqldump-u root-pstudent> c: \ student. SQL (student indicates the database name)

To import data, you must first enter the mysql environment, and then enter the specific database and enter the source command. Note that the mysql Command Terminator is not entered in the import and export operations.