MySQL Database basic operations

About MySQL

MySQL is a relational database (relational databases Management System), this so-called "relational" can be understood as "tabular" concept, a relational database consists of one or more tables, a table.

· Header (header): The name of each column;

· Column (ROW): A collection of data of the same data type;

· Row (COL): Each line is used to describe the specific information of a person/thing;

· Value: The specific information for the row, each value must be the same as the data type of the column;

· Key: A method in a table that identifies a particular person \ object, and the value of the key is unique in the current column.

Configuration for MySQL under Windows

For the MySQL 5.1 free install version, for example, download Mysql-noinstall-5.1.69-win32.zip

Configuration steps:

1. Unzip the downloaded Mysql-noinstall-5.1.69-win32.zip to the location where it needs to be installed, such as: C:\Program Files;

2. Locate the My-small.ini configuration file under the installation folder, rename it to My.ini, open for editing, and add a line under [client] and [mysqld]: default-character-set= GBK

3. Open the Windows environment variable settings, new variable name mysql_home, the variable value is the MYSQL installation directory path, here is C:\Program Files\mysql-5.1.69-win32

4. Add the path variable to the environment variable;%mysql_home%\bin;

5. Install the MySQL service, open the Windows command prompt, execute the command: mysqld--install mysql--defaults-file= "My.ini" prompt "service successfully installed." Indicate success;

Start, stop, and uninstall of MySQL service

Run at the Windows command prompt:

Startup: Net start MySQL

Stop: net stop MySQL

Uninstall: SC Delete MySQL

Basic composition of MySQL scripts

Like the regular scripting language, MySQL also has a set of rules for the use of characters, words, and special symbols, and MySQL executes SQL scripts to perform operations on the database, which consists of one or more MySQL statements (SQL statement + extension statements). The script file suffix is typically. sql when you save it. Under the console, the MySQL client can also execute a single sentence without saving the. sql file.

Identifier

Identifiers are used to name objects, such as databases, tables, columns, variables, and so on, to be referenced elsewhere in the script. MySQL identifier naming rules are a little cumbersome, and here we use universal Naming conventions: Identifiers consist of letters, numbers, or underscores (_), and the first character must be a letter or an underscore.

The case sensitivity of identifiers depends on the current operating system and is not sensitive under Windows, but for most Linux\unix systems These identifiers are case sensitive.

Key words:

MySQL is a lot of keywords, not listed here, learning in the study. These keywords have their own specific meanings and try to avoid them as identifiers.

Statement:

The MySQL statement is the basic unit that makes up the MySQL script, and each statement can accomplish a specific operation, which consists of the SQL standard statement +mysql extension statement.

Function:

MySQL functions are used to implement some of the advanced functions of database operations, which are broadly divided into the following categories: String functions, mathematical functions, date-time functions, search functions, cryptographic functions, information functions.

Data types in MySQL

MySQL has three major classes of data types, number, date \ Time, string, and more detailed sub-types in the three categories:

· Number Type

· integers: tinyint, smallint, mediumint, int, bigint

· Floating-point numbers: float, double, real, decimal

· Dates and times: date, Time, DateTime, timestamp, year

· String type

· String: char, varchar

· Text: Tinytext, text, Mediumtext, Longtext

· Binary (used to store pictures, music, etc.): Tinyblob, Blob, Mediumblob, Longblob

MySQL Database for use!

Log in to MySQL

When the MySQL service is already running, we can log in to the MySQL database via MySQL's own client tool, first open a command prompt, enter the following format naming:

MYSQL-H host name-u user name-P

· -H: This command is used to specify the MySQL hostname that the client wants to log on, and the parameter can be omitted when logging on to the current machine;

· -U: The name of the user to log in;

· -P: Tells the server that a password will be used to log in, ignoring this option if the user name password you want to log in is blank.

To log in as an example of a MySQL database that has just been installed on this computer, enter Mysql-u root-p at the command line to confirm that if the installation is correct and MySQL is running, you will get the following response:

Enter Password:

If the password exists, enter the password login, does not exist then directly press ENTER to log in, according to the installation method in this article, the default root account is no password. Once you're signed in, you'll see Welecome to the MySQL monitor ... The prompt.

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

Create a database

Use the CREATE DATABASE statement to complete the creation of databases, with the following format for creating commands:

Create database name [other options];

For example, we need to create a database named samp_db and execute the following command at the command line:

Create DATABASE samp_db character Set GBK;

To facilitate the display of Chinese at the command prompt, the database character encoding is specified as GBK at creation time by character set GBK. The response to Query OK, 1 row affected (0.02 sec) is obtained when the creation is successful.

Note: The MySQL statement is a semicolon (;) as the end of the statement, if you do not add a semicolon at the end of the statement, the command prompt prompts you to continue typing (there are individual exceptions, but the semicolon is definitely not wrong);

Tip: You can use the show databases command to see which databases have been created.

Select the database you want to manipulate

To operate on a database, you must first select the database, or you will be prompted with an error:

ERROR 1046 (3d000): No Database selected

Two options for using the database:

One: Specified when logging in to the database, command: mysql-d selected database name-h hostname-u user name-P

For example, select the database you just created when you log in: mysql-d samp_db-u root-p

Second: After logging in using the USE statement to specify, command: used database name;

The USE statement can be executed without a semicolon, and a samp_db is used to select the database you just created, and you will be prompted after successful selection: Database changed

Create a database table

Use the CREATE TABLE statement to complete the creation of a table, the common form of CREATE TABLE:

CREATE TABLE table name (column declaration);

To create the students table, for example, the table will hold the number (ID), name, Gender (sex), Age, contact phone (tel) content:

Create Tablestudents

（

ID intunsigned NOT NULL auto_increment primary key,

Namechar (8) NOT NULL,

Sexchar (4) NOT NULL,

Agetinyint unsigned NOT NULL,

Telchar (+) NULL default "-"

);

For some longer statements at the command prompt it may be easy to get wrong, so we can write the statement through any text editor and save it as a createtable.sql file, executing the script through the file redirection at the command prompt.

Open a command prompt, enter: mysql-d samp_db-u root-p <createtable.sql

(Hint: 1. If connecting to a remote host, add the-H command; 2. The Createtable.sql file must specify the full path of the file if it is not in the current working directory. )

Statement Explanation:

CREATE TABLE tablename (columns) is the command that creates a database table, the name of the column and the data type of the column will be completed in parentheses;

In parentheses, 5 columns are declared, ID, name, sex, age, and Tel are the names of each column followed by the data type description, separated by commas (,) between the columns and column descriptions;

The "ID int unsignednot NULL auto_increment primary Key" line is described:

· "id" is the name of the column;

· "int" Specifies that the column is of type int (with a value range of 8388608 to 8388607), which is then decorated with "unsigned" to indicate that the type is unsigned, at which time the column has a value ranging from 0 to 16777215;

· "Not NULL" indicates that the value of the column cannot be empty and must be filled, and the default can be null if the property is not specified;

· The "auto_increment" needs to be used in an integer sequence, and the effect is that if the column is NULL when inserting the data, MySQL will automatically produce a unique identifier value that is larger than the existing values. Only one such value can be in each table and the column must be an indexed column.

· "PRIMARY key" means that the column is the primary key of the table, the value of this column must be unique, and MySQL will automatically index the column.

The following char (8) indicates that the stored character length is 8, the tinyint value range is 127 to +, and the default property specifies a value when the column value is empty.

Tip: 1. Use show tables; command to see the name of the table you have created; 2. Use the describe table name; command to view the details of the table you have created.

Inserting data into a table

The INSERT statement can be used to insert one or more rows of data into a database table, using the following general form:

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

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

INSERT into students values (NULL, "Lao Wang", "Male", 20, "13811371377");

Press ENTER to confirm that if you prompt Query Ok, 1row affected (0.05 sec) indicates that the data was inserted successfully. If the insertion fails, check that the database you want to manipulate is selected.

Sometimes we just need to insert some of the data, or not in the order of the columns, you can insert them in this form:

INSERT into students (name, sex, age) VALUES ("Sun Xiaohua", "female", 21);

Querying data in a table

Select statements are commonly used to obtain data from a database based on certain query rules, with the following basic usage:

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 statement select name, age from students; The results of the implementation are as follows:

Mysql> Selectname, age from students;

+--------+-----+

| name | Age |

+--------+-----+

|  Lao Wang | 20 |

|  Sun Xiaohua | 21 |

|  Engineering | 23 |

|  Zheng June | 19 |

|  Chen Fang | 22 |

|  Zhang Weibong | 21 |

+--------+-----+

6 rows in Set (0.00 sec)

Mysql>

You can also use the wildcard character * to query all the contents of the table, statements: SELECT * from students;

Query by specific criteria:

The WHERE keyword is used to specify the query condition, in the form of the Select Column name from the table name where condition;

To query all gender-based information as an example, enter a query statement: SELECT * from students where sex= "female";

The WHERE clause supports not only the "where Column name = value" Query form, which is named equal to the value, which is supported for the operators of the general comparison operation, such as =, >, <, >=, <,! =, and some extension operators are [not] null, in, like, and so on 。 You can also combine queries with OR and and for query criteria, and later learn more advanced conditional query methods, which are no longer introduced.

Example:

Find information for everyone over the age of 21: SELECT * from students where ages > 21;

Query everyone with the word "King" in the name: SELECT * FROM students wherename like "% king";

Information for anyone with a query ID of less than 5 and older than 20: SELECT * from students where id<5 and age>20;

Updating data in a table

The UPDATE statement can be used to modify the data in the table, using the following basic form:

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

Examples of Use:

Change the phone number with ID 5 to the default "-": Update students settel=default where id=5;

Increase the age of all 1:updatestudents set age=age+1;

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

Delete data from a table

The DELETE statement is used to delete data from a table, with the following basic usage:

Delete from table name where delete condition;

Examples of Use:

Delete the row with ID 2: Delete from students where id=2;

Delete all data older than 21 years: Delete from students where age<20;

Delete all data from the table: delete fromstudents;

To create a table modification

The altertable statement is used to create subsequent modifications to the table, using the following basic usage:

Adding columns

Basic form: Altertable table name add column list data type [after insertion position];

Example:

The last appended column of the table address:altertable students add address char (60);

Inserts a column after the column named Age birthday:alter table students add birthday date after age;

modifying columns

Basic form: Altertable table name change column Name column new name new data type;

Example:

Rename Table Tel column to Telphone:alter table students Change Tel telphone char (+) Default "-";

Change the data type of the Name column to char: ALTER TABLE students changes name name char (+) not null;

Delete Column

Basic form: Altertable Table name drop column name;

Example:

Delete birthday column: ALTER TABLE students drop birthday;

Renaming a table

Basic form: Altertable table name rename new table name;

Example:

Rename students table for workmates:alter table students rename workmates;

Delete entire table

Basic form: drop table name;

Example: Deleting a workmates table: drop-table workmates;

Delete entire database

Basic form: Dropdatabase database name;

Example: Delete a samp_db database: Drop the DB samp_db;

To modify the root user password

According to the installation of this article, the root user does not have a password by default, there are more ways to reset the root password, this is only a more common way.

Using the Mysqladmin method:

Open command Prompt interface, execute command: mysqladmin-u root-p password New password

After executing prompts to enter the old password to complete the password modification, when the old password is empty directly press ENTER to confirm.

Visual Management Tools MySQL Workbench

Although we can execute the MySQL statement at the command prompt through a line of input or through a redirected file, this is inefficient, because there is no automatic check of the syntax before execution, and the likelihood of some errors caused by input errors is greatly increased, so try some visual MySQL database management tools , MySQL Workbench is a visual management tool that MySQL officially provides to MySQL, where you can directly manage the contents of the database in a visual way, and the SQL Script Editor of MySQL Workbench supports syntax highlighting and input language The law examines, of course, its powerful, not limited to these two points.

MySQL Database basic operations