JDBC simple operation record (1) MySQL operation_mysql

JDBC simple operation record (1)-MySQL operations learned the basic MySQL operations and found the following blog posts, which are simple and easy to use, so I will repost them for reference.

Thanks to the original author wid.

21-minute MySQL Getting Started Tutorial

Directory

• I. INTRODUCTION to MySQL concepts

• II. MySQL configuration in Windows

• Procedure

• Start, stop, and uninstall the MySQL service

• III. basic components of MySQL scripts

• IV. data types in MySQL

• V. use the MySQL database

• Log on to MySQL

• Create a database

• Select the database to operate

• Create a database table

• 6. operate MySQL databases

• Insert data to a table

• Query table data

• Update table data

• Delete table data

• 7. modifications after creation

• Add column

• Modify columns

• Delete column

• Rename a table

• Delete the entire table

• Delete the entire database

• VIII. Appendix

• Modify the root user password

• Visual Management tool MySQL Workbench

Introduction to MySQL concepts

MySQL is a Relational Database Management System. this so-called "Relational" can be understood as a "table". a Relational Database consists of one or more tables:

• Header): Name of each column;

• Column (row): A set of data with the same data type;

• Row (col): Each row is used to describe the specific information of a person/object;

• Value): The specific information of the row. each value must be of the same data type as the column;

• Key): The table is used to identify a specific person/object. The key value is unique in the current column.

MySQL configuration in Windows

Take MySQL 5.1 free installation version as an example, download the mysql-noinstall-5.1.69-win32.zip (official download page: http://dev.mysql.com/downloads/mysql/5.1.html#downloads)

Configuration steps:

1. decompress the downloaded mysql-noinstall-5.1.69-win32.zip to the location to be installed, such as: C:/Program Files;

2. find the my-small.ini profile in the installation folder and rename it to my. ini, open and edit, add a row under [client] and [mysqld]: default-character-set = gbk

3. open Windows environment variable settings, new variable name MYSQL_HOME, variable value for MySQL installation directory path, here is C:/Program Files/mysql-5.1.69-win32

4. add; % MYSQL_HOME %/bin in the Path variable of the environment variable;

5. install the MySQL Service. open the Windows Command Prompt and run the command: mysqld -- install MySQL -- defaults-file = "my. ini". the prompt "Service successfully installed." indicates that the Service is successful;

Start, stop, and uninstall the MySQL service

Run:

Start:Net start MySQL

Stop:Net stop MySQL

Uninstall:SC delete MySQL

Basic components of MySQL scripts

Similar to the conventional scripting language, MySQL also has a set of rules for using characters, words, and special symbols. MySQL performs database operations by executing SQL scripts, the script consists of one or more MySQL statements (SQL statements + extended statements). the suffix of the script file is generally. SQL. In the console, the MySQL client can also execute a single statement without saving it as a. SQL file.

Identifier

Identifiers are used to name objects, such as databases, tables, columns, and variables, so that they can be referenced elsewhere in the script. The naming rules for MySQL identifiers are a little complicated. here we use a universal naming rule: an identifier consists of letters, numbers, or underscores (_), and the first character must be a letter or underline.

Whether the identifiers are case sensitive depends on the current operating system. In Windows, these identifiers are case sensitive for most linux/unix systems.

Keywords:

MySQL has many keywords, which are not listed here. learn from them. These keywords have their own specific meanings and should be avoided as identifiers.

Statement:

A MySQL statement is the basic unit of MySQL scripts. each statement can perform specific operations. it is composed of standard SQL statements and MySQL extension statements.

Function:

MySQL functions are used to implement some advanced functions of database operations. these functions are roughly divided into the following types: string functions, mathematical functions, date and time functions, search functions, encryption functions, and information functions.

Data types in MySQL

MySQL has three types of data: numbers, dates, times, and strings. in these three categories, many subtypes are further divided:

• Numeric type

• Integer: tinyint, smallint, mediumint, int, bigint

• Floating point: float, double, real, decimal

• Date and time: Date, time, datetime, timestamp, year

• String type

• String: char, varchar

• Text: tinytext, text, mediumtext, longtext

• Binary (used to store images and music): tinyblob, blob, mediumblob, and longblob

Here can not be detailed introduction of these types, the length may be very long, see for details: MySQL data type: http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html

Log on to MySQL using the MySQL database

When the MySQL service is running, you can log on to the MySQL database using the client tool that comes with MySQL. first, open the command prompt and enter the name in the following format:

Mysql-h host name-u user name-p

• -H:This command is used to specify the MySQL host name to be logged on to the client. this parameter can be omitted when logging on to the current machine;

• -U:The username to log on;

• -P:Tell the server that a password will be used for logon. if the username and password to be logged on are empty, ignore this option.

Take logging on to the MySQL database just installed on the local machine as an example. enter mysql-u root-p in the command line and press enter to confirm. if the installation is correct and MySQL is running, the following response is returned:

Enter password:

If the password exists, enter the password to log on. if the password does not exist, press enter to log on. according to the installation method described in this article, the root account has no password by default. After successful logon, you will see the prompt message of Welecome to the MySQL monitor.

Then the command prompt will always wait for the command input with a blinking cursor in mysql>, enter exit or quit to log out.

Create a database

You can use the create database statement to create a database. the command format is as follows:

Create database name [other options];

For example, to create a database named samp_db, run the following command on the command line:

Create database samp_db character set gbk;

To display Chinese characters at a command prompt, specify the database character encoding as gbk by using character set gbk during creation. When the creation is successful, the Query OK and 1 row affected (0.02 sec) will be returned.

Note:The MySQL statement ends with a semicolon (;). If a semicolon (;) is not added at the end of the statement, the command prompt will prompt you to continue to input (in some special cases, but the extra points are certainly not wrong );

Tip:You can run the show databases command to check which databases have been created.

Select the database to operate

To operate a database, you must select the database first. Otherwise, an error is prompted:

ERROR 1046 (3D000): No database selected

The following two methods are used to select a database:

I:Specify the command when logging on to the database: mysql-D selected database name-h host name-u user name-p

For example, when logging on, select the database you just created: mysql-D samp_db-u root-p.

II:After logon, use the use statement to specify the command: use database name;

You can run the use samp_db statement to select the Database you just created. after the selection is successful, the system prompts "Database changed ".

Create a database table

You can use the create table statement to create a table. the common form of create table is as follows:

Create table name (column declaration );

Taking creating a students table as an example, the table contains the student id, name, sex, age, and tel:

create table students（id int unsigned not null auto_increment primary key,name char(8) not null,sex char(4) not null,age tinyint unsigned not null,tel char(13) null default "-");

Some long statements may be prone to errors at the command prompt, so we can use any text editor to input the statements and save them as createtable. in the SQL file, execute the script through file redirection at the command prompt.

Open the command prompt and enter mysql-D samp_db-u root-p <createtable. SQL

(Tip: 1. add the-h command to connect to the remote host; 2. if the createtable. SQL file is not in the current working directory, specify the full path of the file .)

Statement description:

Create table tablename (columns) is the command used to create a database table. the column name and the data type of the column are in parentheses;

Five columns of content are declared in parentheses. id, name, sex, age, and tel are the names of each column, followed by the data type description, the description of a column is separated by commas;

This section uses the "id int unsigned not null auto_increment primary key" line to introduce:

• "Id" is the column name;

• "Int" specifies that the column type is int (value range:-8388608 to 8388607). We will use "unsigned" to modify it to indicate that the column type is unsigned, the value range of this column is 0 to 16777215;

• "Not null" indicates that the column value cannot be blank and must be filled in. if this attribute is not specified, it can be blank by default;

• "Auto_increment" must be used in the integer column. it is used to create a unique identifier value that is greater than the existing value if the column is NULL when data is inserted. There is only one such value in each table and the column must be an index column.

• "Primary key" indicates that this column is the primary key of the table, and the value of this column must be unique. MySQL will automatically index this column.

The following char (8) indicates that the stored characters are 8 characters in length and the value range of tinyint is-127 to 128. the default attribute specifies the default value when the column value is null.

For more data types, see MySQL data types: http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html

Tip:1. use the show tables command to view the name of the created table; 2. use the describe table name command to view the details of the created table.

Insert data into a table using the MySQL database

The insert statement can be used to insert one or more rows of data into a database table. the general format is as follows:

Insert [into] table name [(column name 1, column name 2, column name 3,...)] values (value 1, value 2, value 3 ,...);

The content in [] is optional. for example, to insert a record to the students table in the samp_db database, execute the statement:

Insert into students values (NULL, "Wang Gang", "male", 20, "13811371377 ");

After you press the Enter key to confirm, if Query OK is displayed, 1 row affected (0.05 sec) indicates that the data is successfully inserted. If insertion fails, check whether the database to be operated is selected.

Sometimes we only need to insert part of the data or insert it in the column order. you can insert it in this form:

Insert into students (name, sex, age) values ("Sun Lihua", "female", 21 );

Query table data

Select statements are often used to obtain data from the database according to certain query rules. The basic usage is as follows:

Select column name from table name [query condition];

For example, to query the names and ages of all students in the students table, enter the select name and age from students statement. The execution result is as follows:

Mysql> select name, age from students; + -------- + ----- + | name | age | + -------- + ----- + | Wang Gang | 20 | Sun Lihua | 21 | Wang Yongheng | 23 | Zheng Junjie | 19 | Chen Fang | 22 | Zhang Weipeng | 21 | + -------- + ----- + 6 rows in set (0.00 sec) mysql>

You can also use the wildcard * to query all the table content. statement: select * from students;

Query by specific conditions:

The where keyword is used to specify query conditions. The format is: select column name from table name where condition;

Take querying information about all gender women as an example, enter the query statement: select * from students where sex = "female ";

The where clause not only supports the query form of "where column name = value", but also supports general comparison operators, example =,>, <,> =, <,! = And some extension operators are [not] null, in, like, and so on. You can also use or and for combined query of query conditions. in the future, you will learn more advanced query conditions.

Example:

Query information of all persons aged 21 and above: select * from students where age> 21;

Query the information of all persons whose names contain the word "wang": select * from students where name like "% Wang % ";

Query the information of all persons whose IDs are less than 5 and whose ages are greater than 20: select * from students where id <5 and age> 20;

Update table data

The update statement can be used to modify data in a table. The basic usage format is as follows:

Update table name set column name = new value where update condition;

Example:

Change the mobile phone number with id 5 to the default "-": update students set tel = default where id = 5;

Increase the age of all people by 1: update students set age = age + 1;

Change the name of the mobile phone number 13288097888 to "Zhang Weipeng" and the age to 19: update students set name = "Zhang Weipeng", age = 19 where tel = "13288097888 ";

Delete table data

The delete statement is used to delete data in a table. The basic usage is as follows:

Delete from table name where delete condition;

Example:

Delete row with id 2: delete from students where id = 2;

Delete all data younger than 21: delete from students where age <20;

Delete all data in the table: delete from students;

Table modification after creation

The alter table statement is used to modify a table after creation. The basic usage is as follows:

Add column

Basic format: alter table name add column name data type [after insert location];

Example:

Add address char (60) to the last appended column of the table: alter table students );

Insert the column birthday: alter table students add birthday date after age after the column named age;

Modify columns

Basic form: alter table name change column name column new name new data type;

Example:

Rename the table tel column to telphone: alter table students change tel telphone char (13) default "-";

Change the data type of the name column to char (16): alter table students change name char (16) not null;

Delete column

Basic form: alter table name drop column name;

Example:

Delete the birthday column: alter table students drop birthday;

Rename a table

Basic form: alter table name rename New table name;

Example:

Rename the students table as workmates: alter table students rename workmates;

Delete the entire table

Basic form: drop table name;

Example:Delete a workmates table: drop table workmates;

Delete the entire database

Basic form: drop database name;

Example:Delete the samp_db database: drop database samp_db;

Appendix modify the root user password

According to the installation method in this article, the root user does not have a password by default, and there are many ways to reset the root password. here is only a common method.

Use mysqladmin:

Open the command prompt interface and execute the command: mysqladmin-u root-p password new password

After the command is executed, the system prompts you to enter the old password to change the password. if the old password is empty, press enter to confirm the password.

Visual Management tool MySQL Workbench

Although we can run mysql statements through a line of input or a redirection file at a command prompt, this method is inefficient because the pre-execution syntax is not automatically checked, the possibility of errors caused by input errors is greatly increased. in this case, try some visual MySQL database management tools. MySQL Workbench is a visual management tool officially provided by MySQL, you can directly manage the content in the database in a visualized manner, and the SQL script editor of MySQL Workbench supports syntax highlighting and syntax check during input. of course, it is powerful, it is not limited to these two points.

MySQL Workbench official introduction: http://www.mysql.com/products/workbench/

MySQL Workbench Download: http://dev.mysql.com/downloads/tools/workbench/

My ubuntu comes with mysql, and the root login password is the login password of the ubuntu user.