MySQL Basics easy to learn mysql command _mysql

Introduction to the relevant concepts of MySQL
MySQL for relational databases (relational database Management System), this so-called "relational" can be understood as the concept of "table", a relational database consists of one or several tables, as shown in a table:

Header (header): the name of each column;
column (Row): A collection of data with the same data type;
Line (COL): each line is used to describe the specific information of a person/thing;
value: The specific information of the row, each value must be the same as the data type of the column;
Key: a method used in a table to identify a particular person's object, and the value of the key is unique in the current column.

Second, the configuration of MySQL under Windows
For example, the MySQL 5.1 free installation version, download Mysql-noinstall-5.1.69-win32.zip.

2.1 configuration steps:

1. Unzip the downloaded Mysql-noinstall-5.1.69-win32.zip to the location that 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 edit, and 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 for C:\Program Files\mysql-5.1.69-win32

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

5. Install MySQL service, open windows command prompt, execute command: mysqld--install MySQL--defaults-file= "My.ini " prompt "Service successfully ins Talled. " Express success;

Start, stop, and uninstall of 2.2MySQL services

To run at a Windows command prompt:

Start: net start MySQL

STOP: net stop MySQL

Uninstall: SC Delete MySQL

Three, the basic composition of MySQL script
like a regular scripting language, MySQL also has a set of rules for using characters, words, and special symbols, and MySQL completes operations on the database by executing SQL scripts, consisting of one or more MySQL statements (SQL statements + extension statements). The script file suffix name is typically. SQL at save time. Under the console, the MySQL client can also execute a single sentence without saving it as a. sql file.

3.1 identifiers : 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, here we use the Universal Naming convention: Identifiers consist of letters, numbers, or underscores (_), and the first character must be a letter or underscore.

Windows is insensitive to the case that identifiers are case-sensitive depending on the current operating system, but for most Linux\unix systems, these identifiers are case sensitive.

3.2 Key Words : MySQL is a large number of keywords, here are not listed here, learn in learning. These keywords have their own specific meaning, as far as possible to avoid as identifiers.

3.3 Statements : A MySQL statement is the basic unit that makes up a MySQL script, and each statement completes a specific operation, consisting of a SQL standard statement + MySQL extension statement.

3.4 Functions: MySQL function is used to implement some advanced functions of database operations, these functions are roughly divided into the following categories: String functions, mathematical functions, date-time functions, search functions, cryptographic functions, information functions.

Iv. data types in MySQL
MySQL has three categories of data types, the number of characters, date \ Time, string, the three categories in a more detailed division of many subtypes:

4.1 Number types

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

4.2 Dates and times: date, Time, DateTime, timestamp, year
4.3 String type

• Strings: char, varchar
• Text: Tinytext, text, Mediumtext, Longtext
• Binary (available to store pictures, music, etc.): Tinyblob, BLOBs, Mediumblob, Longblob

This is not a detailed description of these types, the length may be very long, detailed introduction see: "MySQL Data type detailed":

V. Use of MySQL Database
5.1 login to MySQL

When the MySQL service is already running, we can log in to the MySQL database with the client tools from MySQL, first open the command prompt, and enter the name in the following format:mysql-h hostname-u user name-P

-H: This command specifies the name of the MySQL host to which the client will log in, and the current machine can be omitted;
-U: The user name to be logged in;
-P: Tells the server to log in with a password, and you can ignore this option if you want to log on to a username with a blank password.
To login just installed in the MySQL database in this computer for example, enter mysql-u at the command line root-p Press ENTER to confirm that if the installation is correct and MySQL is running, you will receive the following response:enter password.

If the password exists, enter password login, do not exist directly by return login, according to the installation method in this article, the default root account is no password. After the login is successful you will see Welecome to the MySQL monitor ... The prompt.

The command prompt then waits for the input of the command with Mysql> plus a blinking cursor, entering exit or quit exit login.

5.2 Creating a Database

Use the CREATE DATABASE statement to complete the creation of databases, creating the following format for the command: Createdatabase name [other options];

For example, we need to create a database named samp_db, execute the following command at the command line:CREATE database samp_db character set GBK;

To make it easier to display Chinese at a command prompt, specify the database character encoding as GBK at creation time by character set GBK. When you create a success, you get a response from Query OK, 1 row affected (0.02 sec).

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

Hint: You can use show databases; command to see which databases have been created.

5.3 Select the database you want to manipulate

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

ERROR 1046 (3d000): No Database selected

Options for using the database in two ways:

1 when you log in to the database, command: mysql-d selected database name-h hostname-u user name-P

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

2 The USE statement is used to specify the command: using the database name;

The USE statement can be executed without a semicolon to select the database that you just created, and you will be prompted for success when you samp_db: Database changed

5.4 Creating a database table

Use the CREATE TABLE statement to complete the creation of a table, the common form of CREATE TABLE: CREATE TABLEname (column declaration);

As an example of creating a students table, the table will hold the school number (ID), name, Gender (sex), Age, contact Phone (tel):

 CREATE TABLE students
 (
 ID int unsigned NOT NULL auto_increment primary key,
 name char (8) is not null,
 sex ch AR (4) Not NULL, age
 tinyint unsigned NOT NULL,
 tel char null default "-"
 );

Some of the longer statements may be easily lost at the command prompt, so we can use any text editor to enter the statement and save it as a Createtable.sql file, and execute the script at the command prompt for file redirection.

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

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

Statement narration:

Create Table tablename (columns) is the command that creates a database table, and the name of the column and the data type of the column are completed in parentheses;

5 column contents are declared in parentheses, ID, name, sex, age, tel is the name of each column, followed by the data type description, the column and the description of the column are separated by commas (,);

Described in the "ID int unsigned NOT NULL auto_increment primary key" line:

• "id" is the name of the column;
• "int" Specifies that the column is of type int (with a value ranging from 8388608 to 8388607), followed by a "unsigned", which indicates that the type is unsigned, with a value range of 0 to 16777215 for that column;
• ' NOT NULL ' indicates that the value of the column cannot be empty, it must be filled in, and the default can be null if you do not specify the property;
• "Auto_increment" needs to be used in an integer series that, if the column is NULL when inserting data, MySQL automatically produces a unique identifier value that is larger than the existing one. 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, and 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 and the tinyint value ranges from 127 to 128, and the default property specifies the defaults when the column value is empty.

hint: 1.: use show tables; command to view the name of the table that has been created; 2. Use describe table name; command to view the details of a table that has been created.

Vi. operation of MySQL database
6.1 Inserting data into the table

Insert statements can be used to insert one or more rows of data into a database table. The general form used 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 within [] is optional, for example, to insert a record into the students table in the SAMP_DB database, executing the statement:INSERT INTO students values (NULL, "Wang Gang", "male", 20, "13811371 377 ");

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

Sometimes we just need to insert part of the data, or we don't insert it in the order of the columns, and we can insert it in this form:

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

6.2 Data in a query table

The SELECT statement is commonly used to obtain data from the database according to certain query rules, and its basic usage is:Select column name from table name [query condition];

For example, to query the names and ages of all the students in the students table, enter the statement select name, age from students; The results of the implementation are as follows:

 Mysql> select name, age from students;
 +--------+-----+
 | name |
 +--------+-----+
 | |
 Sun Lihua | |
 | Wang Yongheng |
 | | Zheng Junjie | |
 | JL | |
 | Zhang Weibong | |
 +--------+-----+
 6 rows in Set (0.00 sec)

 mysql>

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

6.3 Query by specific criteria:

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

For example, for all gender-sensitive information, enter a query statement: SELECT * from students where sex= "female";

The WHERE clause supports not only the "where Column name = value", which is equal to the value of a query, but also the operators of general comparison operations, such as =, >, <, >=, <,!=, and some extension operators are [not] null, in, and like Wait a minute. You can also combine queries with OR and with the query criteria, and you'll learn more advanced conditional query methods, no more introductions.

Use examples:

Query for all persons aged 21 or older: SELECT * from students where age > 21;

Query name with the word "king" of all the information: SELECT * from students where the name like "% Wang";

Owner information with a query ID less than 5 and older than 20: SELECT * from students where id<5 and age>20;

6.4 Update the data in the table

The UPDATE statement can be used to modify the data in the table in the form of the Update table name set column name = new value where the condition is updated;

Use examples:

Change the phone number of ID 5 to the default "-": Update students set tel=default where id=5;

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

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

6.5 Deleting data in a table

The DELETE statement is used to delete the data in the table, and the basic usage is:

Delete from table name where delete condition;

Use examples:

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 in the table: delete from students;

Vii. modifications to the table after creation
The ALTER TABLE statement is used to modify the table after it is created, and the underlying usage is as follows:

7.1 Adding columns

Basic form: ALTER TABLE name add column list data type [after insertion position];

Example:

Append the column at the end of the table Address:alter table students add address char (60);

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

7.2 Modifying 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 () default "-";

Changes the data type of the Name column to char: ALTER TABLE students change name name char (n) not null;

7.3 Deleting columns

Basic form: ALTER TABLE name drop column name;

Example: Delete birthday column: ALTER TABLE students drop birthday;

7.4 Renaming tables

Basic form: ALTER TABLE name rename new table name;

Example: Renaming students table as Workmates:alter table students rename workmates;

7.5 Delete entire table

Basic form: drop table name;

Example: Delete workmates table: drop table workmates;

7.6 Delete the entire database

Basic form: drop database name;

Example: Delete samp_db database: Drop DB samp_db;

Appendix
To Modify the root user password

According to the installation method of this article, the root user default is no password, the way to reset the root password is also more, here only a more common way.

• Use the Mysqladmin method:

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

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

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, however, the efficiency of this approach is low, because there is no implementation of the grammar automatically check, input errors caused by the possibility of some errors will be greatly increased, then may wish to try some visual MySQL database management tools , MySQL Workbench is the MySQL official for MySQL provides a visual management tool, you can visually manage the contents of the database directly, and the MySQL Workbench SQL Script Editor support syntax highlighting and input language Law check, of course, it's powerful, not limited to these two points.

The above is the entire content of this article, to help you easy to learn the MySQL database, as well as the most basic MySQL command, I hope to help you learn.