Mysql basics easy to learn Mysql command _ MySQL

This article is a basic tutorial on Mysql. it teaches you how to easily learn Mysql commands and master Mysql commands. For more information, see I. 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 or object. The key value is unique in the current column.

II. MySQL configuration in Windows
Take MySQL 5.1 free of installation as an example to download mysql-noinstall-5.1.69-win32.zip.

2.1Configuration 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;

2.2start, stop, and uninstall the MySQL service

Run:

Start:Net start MySQL

Stop:Net stop MySQL

Uninstall:SC delete MySQL

III. 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.

3.1 identifierThe identifier is used to name some 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.

3.2 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.

3.3 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.

3.4 functions: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.

IV. data types in MySQL
MySQL has three types of data: numbers, dates, times, and strings, which are further divided into multiple subtypes:

4.1 numeric type

• Integer: tinyint, smallint, mediumint, int, bigint
• Floating point: float, double, real, decimal

4.2 Date and time:Date, time, datetime, timestamp, year
4.3 String type

• String: char, varchar
• Text: tinytext, text, mediumtext, longtext
• Binary (used to store images and music): tinyblob, blob, mediumblob, and longblob

These types cannot be described in detail here, and the length may be very long. for details, see MySQL data type details:

V. use the MySQL database
5.1 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 seeWelecome 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.

5.2 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 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.

5.3 select the database to operate

To operate a database, you must select the database first. otherwiseError message:

ERROR 1046 (3D000): No database selected

The following two methods are used to select a database:

1) when logging on to the database, specify the command: 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.

2) use the use statement to specify the name of the use database after logon;

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 ".

5.4 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)To create a database table command, the column name and the data type of the column will be completed 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.

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

6. operate MySQL databases
6.1 insert data into 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 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 );

6.2 query table data

Select statements are often used to obtain data from the database according to certain query rules. The basic usage is: 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;

6.3 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;

6.4 update table data

The update statement can be used to modify the data in a table. The basic usage form is: 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 ";

6.5 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;

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

7.1 Add columns

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;

7.2 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;

7.3 delete columns

Basic form: alter table name drop column name;

Example: delete the birthday column: alter table students drop birthday;

7.4 rename a table

Basic form: alter table name rename New table name;

Example: rename the students table to workmates: alter table students rename workmates;

7.5 delete the entire table

Basic form: drop table name;

Example: Delete a workmates table: drop table workmates;

7.6 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 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.

The above is all about this article. it helps you easily learn MySQL databases and the most basic Mysql commands.