21 minute MySQL Getting Started tutorial _mysql

21 minute MySQL Getting Started tutorial

Directory

• Introduction to the relevant concepts of MySQL
• Second, the configuration of MySQL under Windows
  • Configuration steps
  • Start, stop, and uninstall of MySQL services
• Three, the basic composition of MySQL script
• Iv. data types in MySQL
• V. Use of MySQL Database
  • Log on to MySQL
  • Create a database
  • Select the database that you want to manipulate
  • Create a database table
• Vi. operation of MySQL database
  • Inserting data into a table
  • Data in a query table
  • Updating data in a table
  • Delete data from a table
• Vii. Post-creation modifications
  • Adding columns
  • modifying columns
  • Delete Column
  • Renaming tables
  • Delete entire table
  • Delete entire database
• VIII. Appendix
  • To modify the root user password
  • Visual Management Tools MySQL Workbench

Introduction to MySQL Related concepts

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

MySQL configuration under Windows

For example, MySQL 5.1 installation version, download mysql-noinstall-5.1.69-win32.zip (official download page: http://dev.mysql.com/downloads/mysql/5.1.html)

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< /c3>

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 MySQL services

To run at a Windows command prompt:

Start: net start MySQL

STOP: net stop MySQL

Uninstall: SC Delete MySQL

Basic composition of MySQL scripts

Similar to the regular scripting language, MySQL also has a set of characters, words and special symbols of the use of rules, MySQL by executing SQL script to complete the operation of the database, the script consists of one or more MySQL statements (SQL statements + extension statements), save the script file suffix name generally. s Ql. Under the console, the MySQL client can also execute a single sentence without saving it as a. 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, 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.

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.

Statement:

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.

Function:

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.

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:

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

Using the MySQL Database

Log on to MySQL

When the MySQL service is already running, we can log in to the MySQL database through MySQL's own client tools, first open the command prompt, and enter the following format name:

MYSQL-H host name-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.

Create a database

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

Create database name [other options];

For example, we need to create a database named samp_db, which executes 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: 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 prompts 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.

Select the database that 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:

A: when you log on to the database, specify, command: mysql-d selected database name-h hostname-u username-P

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

Second: after login, use the using statement to specify, command: The name of the usage database;

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

Create a database table

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

CREATE table name (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;

The 5 column contents are declared in parentheses, ID, name, sex, age, and Tel are the names of each column followed by a data type description, separated by a comma (,) between the columns and the description of the column;

The

is described by 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 of-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, and must be filled in, and the default is null if you do not specify this 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 char (8) below the

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.

For more data types see MySQL data type
: 1. Use show tables; command to view the name of a table that has been created; 2. Use describe table name; command to view the details of a table that has been created.

Operating the MySQL database

Inserting data into a table

Insert statements 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, ...);

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

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

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

Data in a query table

A SELECT statement is commonly used to obtain data from a database based on 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;

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.

Example:

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;

Updating data in a table

The UPDATE statement can be used to modify the data in the table, and the basic use is:

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

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

Delete data from 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;

Create changes to the following table

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

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;

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;

Delete Column

Basic form: ALTER TABLE name drop column name;

Example:

Delete birthday column: ALTER TABLE students drop birthday;

Renaming tables

Basic form: ALTER TABLE name rename new table name;

Example:

Rename the Students table to Workmates:alter table students rename workmates;

Delete entire table

Basic form: drop table name;

Example: Delete workmates table: drop table workmates;

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

MySQL Workbench Official Introduction: http://www.mysql.com/products/workbench/

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