MySql database Quick Start

Directory

· 1. Introduction to MySQL concepts

· II. MySQL configuration in Windows

· Configuration steps

· Start, stop, and uninstall the MySQL Service

· III. Basic Components of MySQL scripts

· IV. Data Types in MySQL

· 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 into a table

· Query table data

· Update table data

· Delete data from a table

· 7. Modifications made after creation

· Add columns

· Modify columns

· Delete Columns

· Rename a table

· Delete the entire table

· Delete the entire database

· VIII. Appendix

· Change 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 "tables". A Relational Database consists of one or more tables.

· Header: the name of each column;

· Column: 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: A table is used to identify a specific person or 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: \ ProgramFiles \ 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 successfullyinstalled." 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, which are further divided into multiple subtypes:

· Number type

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

· Floating point: float, double, real, decimal

· Date and time: date, time, datetime, timestamp, and year

· String type

· String: char, varchar

· Text: tinytext, text, mediumtext, and longtext

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

See the following table.

1. Integer

MySQL Data Type	Meaning (Signed)
Tinyint (m)	1 byte range (-128 ~ 127)
Smallint (m)	2 bytes range (-32768 ~ 32767)
Mediumint (m)	3 bytes range (-8388608 ~ 8388607)
Int (m)	4 bytes range (-2147483648 ~ 2147483647)
Bigint (m)	8 bytes (+-9.22*10 to the power of 18)

If the value range is unsigned, the maximum value is doubled. For example, the value range of tinyint unsigned is (0 ~ 256 ).
In int (m), m indicates the display width in the SELECT query result set. It does not affect the actual value range and does not affect the display width.

2. float (float and double)

MySQL Data Type	Description
Float (m, d)	Single-precision floating point 8-bit precision (4 bytes) Total number of m, d decimal places
Double (m, d)	Double-precision floating point 16-bit precision (8 bytes) Total number of m, d decimal places

Set a field to float (5, 3). If a number of 123.45678 is inserted, the actual database stores 123.457, but the total number is still subject to the actual situation, that is, 6 digits.

3. Count

The floating point type stores approximate values in the database, while the fixed point type stores precise values in the database.
The decimal (m, d) Parameter m <65 is the total number, d <30, and d <m is the decimal place.

4. String (char, varchar, _ text)

MySQL Data Type	Description
Char (n)	Fixed Length, up to 255 characters
Varchar (n)	Fixed Length, up to 65535 characters
Tinytext	Variable Length, up to 255 characters
Text	Variable Length, up to 65535 characters
Mediumtext	Variable Length, up to 2 to 24 to 1 Characters
Longtext	Variable Length, up to 2 to 32 to 1 Characters

Char and varchar:
1. char (n) if the number of characters to be saved is less than n, fill it with space, and then remove the space when querying. Therefore, there cannot be spaces at the end of the string stored in char type. varchar is not limited to this.
2. char (n) has a fixed length. char (4) occupies 4 bytes no matter how many characters are saved. varchar is the actual number of characters saved + 1 byte (n <= 255) or 2 bytes (n> 255), so varchar (4), 3 Characters in storage will occupy 4 bytes.
3. the string retrieval speed of the char type is faster than that of the varchar type.

Varchar and text:
1. varchar can be specified as n, text cannot be specified, and internal storage varchar is the actual number of characters saved + 1 byte (n <= 255) or 2 bytes (n> 255 ), text is the actual number of characters + 2 bytes.
2. The text type cannot have default values.
3. varchar can directly create an index. text must specify the first number of characters to create an index. Varchar queries are faster than text. text indexes do not seem to work when indexes are created.

5. binary data (_ Blob)

1. _ BLOB and _ text are stored in different ways. _ TEXT is stored in text format, and English is case sensitive. _ Blob is stored in binary format, regardless of case.
2. _ the data stored in BLOB can only be read as a whole.
3. _ TEXT can be used to specify character sets. _ BLO does not need to specify character sets.

6. Date and Time types

MySQL Data Type	Description
Date	Date '2017-12-2'
Time	Time '12: 25: 36'
Datetime	Date and Time '2017-12-2 22:06:44'
Timestamp	Automatic Storage record modification time

If a field is defined as timestamp, the time data in this field will be automatically refreshed when other fields are modified. Therefore, this data type field can store the last modification time of this record.

Attribute of Data Type

MySQL keywords	Description
NULL	The data column can contain NULL values.
NOT NULL	The data column cannot contain NULL values.
DEFAULT	Default Value
PRIMARY KEY	Primary Key
AUTO_INCREMENT	Auto increment, applicable to integer type
UNSIGNED	Unsigned
Character set name	Specify a Character Set

 

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.

Use MySQL database

Log on to MySQL

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 user name to log on;

·-P: tells 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, 1 rowaffected (0.02 sec) response is 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 enter the statement (there are 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:

1. Specify the database logon command: mysql-D database name-h host name-u user name-p

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

2. use the use statement after logon. 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 createtable 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:

Createtable students

(

Idint unsigned not null auto_increment primary key,

Namechar (8) not null,

Sexchar (4) not null,

Agetinyint unsigned not null,

Telchar (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;

The following describes how to use the line "id intunsigned not null auto_increment primary key:

· "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;

· "Notnull" 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 integer columns. Its function is to automatically generate a Unique Identifier value 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.

· "Primarykey" indicates that the 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.

Operate MySQL Databases

Insert data to a table

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 the Query is OK, 1row 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 agefrom students statement. The execution result is as follows:

Mysql> select name, age from students;

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

| Name | age |

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

| Wang Gang | 20 |

| Sun Lihua | 21 |

| Wang changyong | 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 * fromstudents;

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 the query of all gender-based female information as an example. Enter the query statement: select * fromstudents 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 * fromstudents where age> 21;

Query all the people whose names contain the word "Wang": select * fromstudents where name like "% Wang % ";

Query the information of all persons whose IDs are less than 5 and whose ages are greater than 20: select * fromstudents 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 "-": updatestudents set tel = default where id = 5;

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

Change the name of the mobile phone number 13288097888 to "Zhang weipeng" and the age to 19: update studentsset 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 fromstudents where id = 2;

Delete all data older than 21: delete fromstudents where age <20;

Delete all data in the table: delete fromstudents;

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 tablestudents );

Insert the column birthday: alter tablestudents 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: altertable students change tel telphone char (13) default "-";

Change the Data Type of the name column to char (16): altertable students change name char (16) not null;

Delete column

Basic Form: alter table Name drop column name;

Example:

Delete the birthday column: alter tablestudents drop birthday;

Rename a table

Basic Form: alter table name rename new table name;

Example:

Rename the students table as workmates: altertable 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 a 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 MySQLWorkbench

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/

MySQLWorkbench download page: http://dev.mysql.com/downloads/tools/workbench/