MySQL Tutorial 2nd install and introduce MySQL

2nd installation and introduction MySQL setting MySQL character set

The Chinese-enabled character set is UTF8, which can change the MySQL configuration file for global settings or for database settings or for table settings or for columns. after the character set changes, the newly inserted data takes effect, not previously.

Exercise 1: Change the default character set for MySQL

Directly modify the MY.CNF, add a line of content, and then restart MySQL to make it effective globally, affecting all new databases.

Default-character-set = UTF8

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image001 "border=" 0 "alt=" clip_image001 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515523iv4l.png "width=" 650 "height=" 465 "/>

Restart MySQL Service

[Email protected]:~#/etc/init.d/mysql Restart

View the global default character set

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image002 "border=" 0 "alt=" clip_image002 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515523vm6r.png "height=" 177 "/>

To view all supported character sets

Mysql> show Character set;

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image003 "border=" 0 "alt=" clip_image003 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515524wvvj.png "height=" 673 "/>

Exercise 2: Specifying a character set for a database

After you specify the database character set, the tables created under the database use the character set specified by the library by default.

1. When creating a database, specify its character set

CREATE DATABASE db DEFAULT CHARSET UTF8;

2, or use ALTER syntax to convert a field's character set

ALTER DATABASE db DEFAULT CHARSET UTF8;

You can also specify a character set for a database using the graphical management interface

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image004 "border=" 0 "alt=" clip_image004 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515524rb0r.png "height=" 313 "/>

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image005 "border=" 0 "alt=" clip_image005 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515525mx5j.png "height=" 414 "/>

Exercise 3: Specifying a character set for a data table

When you specify a data table character set, all character fields in the datasheet use the character set specified by the table by default.

1. When creating a data table, specify its character set

CREATE TABLE TBL

(

......

) ENGINE = MyISAM DEFAULT CHARSET UTF8;

2, or use ALTER syntax to convert a field's character set

ALTER TABLE tbl CONVERT to CHARACTER SET UTF8;

3, using other client tools to set, such as Navicat/mysql front/phpmyadmin

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image006 "border=" 0 "alt=" clip_image006 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515527pnoz.png "width=" 650 "height=" 536 "/>

Exercise 4: Specifying a character set for a field

Although the default character set is specified when the data table is created, the fields inside the table can also specify their own character set.

1. When creating a data table, specify its character set

CREATE TABLE TBL

(

......

Name CHAR (CHARACTER) SET UTF8,

......

) ENGINE = MyISAM DEFAULT CHARSET UTF8;

2, or use ALTER syntax to convert a field's character set

ALTER TABLE tbl Change name name CHAR (CHARACTER) SET UTF8;

3, using other client tools to set, such as Navicat/mysql front/phpmyadmin

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image007 "border=" 0 "alt=" clip_image007 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515528pilg.png "width=" 650 "height=" 532 "/>

Exercise 5: Setting the client character set

Specifies the character set for client connections, preferably the character set of the server.

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image008 "border=" 0 "alt=" clip_image008 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515529m1t7.png "height=" 458 "/>

Setting the Putty character set

View name is garbled

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image009 "border=" 0 "alt=" clip_image009 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 13615155294lt2.png "width=" 650 "height=" 417 "/>

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image010 "border=" 0 "alt=" clip_image010 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515530egyl.png "height=" 367 "/>

Click "Apply".

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image011 "border=" 0 "alt=" clip_image011 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515530dhsi.png "height=" 439 "/>

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image012 "border=" 0 "alt=" clip_image012 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515531ypwt.png "width=" 650 "height=" 434 "/>

MySQL storage engine MySQL storage engine overview

Plug-in storage engine is one of the most important features of MySQL database, users can choose how to store and index database, use things and so on according to the needs of the application. MySQL supports a variety of storage engines by default to suit the needs of different domains of database applications. Users can use different storage engines to improve the efficiency of their applications, provide flexible storage, and user settings to customize and use their own storage engine to achieve maximum customization, as needed.

The common storage engine for MySQL is MyISAM, InnoDB, memory, MERGE, where InnoDB provides transaction security tables, and other storage engines are non-transactional security tables.

MyISAM is the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, but it has fast access and no requirement for transactional integrity.

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared with the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes. MySQL supports the foreign key storage engine only InnoDB, when creating foreign keys, requires that the schedule must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.

Some features of the storage engine

The four storage engines mentioned above have their respective environments, depending on their unique characteristics. Mainly embodied in performance, transaction, concurrency control, referential integrity, caching, failure recovery, backup and back-up, and several other aspects

At present, the more popular storage engine is MyISAM and InnoDB. And MyISAM is the first choice for most Web applications. The main difference between MyISAM and InnoDB is in performance and transaction control.

MyISAM is an early ISAM (Indexed sequential Access Method, I'm using an extended implementation of MySQL5.0 that doesn't support ISAM anymore, and ISAM is designed to handle a situation where read frequency is much larger than write frequency, so ISAM and later MyISAM do not consider support for things, do not need transaction records, ISAM query efficiency is considerable, and memory consumption is very small. MyISAM, while inheriting this kind of advantage, has provided a lot of practical new features and related tools with the times. For example, given concurrency control, table-level locks are provided. And because MyISAM is each table using its own separate storage files (myd data files and myi index files), it makes backup and recovery very convenient (copy overwrite), but also supports online recovery.

So if your application does not require transactions, foreign keys are not supported. Only the basic crud (add-and-revise) operation is handled, then MyISAM is the choice.

1. Set the default storage engine for MySQL

Edit the MY.CNF configuration file and add it under Server-side configuration information [mysqld]:

Default-storage-engine = MyISAM

The default engine for setting up the MySQL database is MyISAM

2. Specify the storage engine for the table

Such as:

CREATE TABLE T_innodb (ID int (3))

engine = InnoDB;

Then use

Mysql> Show Table status like ' T_innodb ';

View the details of the table.

3. Modify the table's storage engine

ALTER TABLE t_name ENGINE = InnoDB;

Change the storage engine for the default storage engine and table

4. Change the default storage engine

View the default storage engine

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image013 "border=" 0 "alt=" clip_image013 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515531qdw4.png "height=" 177 "/>

Change the default storage engine

Editing a configuration file

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image014 "border=" 0 "alt=" clip_image014 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515532exxk.png "height="/>

Add under [MySQL]

Default-storage-engine = InnoDB

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image015 "border=" 0 "alt=" clip_image015 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515532zip6.png "width=" 650 "height=" 544 "/>

Review the default engine again to see that it has been changed

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image016 "border=" 0 "alt=" clip_image016 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 13615155332npy.png "height=" 177 "/>

Restart MySQL Service

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image017 "border=" 0 "alt=" clip_image017 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515533puxi.png "height=" 145 "/>

5. More Table Storage Engine

Double-click a table to change the storage engine for the table

Using the command to change

mysql> use SCHOOLDB;

mysql> ALTER TABLE tstudent ENGINE=INNODB;

Using Administrative tools to change

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image018 "border=" 0 "alt=" clip_image018 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515535q3tc.png "width=" 650 "height=" 536 "/>

Features of InnoDB

6. Auto-Grow column

The auto-grow column can be sent to the insert, but if it is empty or 0, the actual inserted value is the automatically growing value.

Create a table that specifies the auto-grow column, the storage engine InnoDB.

CREATE TABLE AU

(

StudentID int NOT NULL auto_increment,

Name varchar (10),

Primary KEY (StudentID)

)

Engine=innodb CHARACTER SET UTF8,

Insert record with null value and self-increment column not in order

Insert AU values (1, ' Han Li just '), (3, ' Han Lihui '), (2, ' Zhangjing '), (null, ' Yang Shuai ')

You can see the value of the self-increment column

select * FROM AU

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image019 "border=" 0 "alt=" clip_image019 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515536faod.png "height="/>

FOREIGN KEY constraints

MySQL supports the foreign key storage engine only InnoDB, when creating foreign keys, requires the parent table must have the corresponding index, the child table will create the foreign key when the index is automatically created

When you delete an updated parent table, the child tables are appropriately manipulated, including restrict, Cascade, set NULL, and no action

Using InnoDB storage engine data in primary key order

If you do not create an index, the data is stored in the insert order

CREATE TABLE Au1

(

StudentID int,

Name varchar (10),

)

Engine=innodb

Insert AU1 VALUES (1, ' Han Li just '), (3, ' Han Lihui '), (2, ' Zhangjing '), (8, ' Yang Shuai '), (6, ' yangliuqing ')

SELECT * FROM AU1

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image020 "border=" 0 "alt=" clip_image020 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515536pud2.png "height=" 134 "/>

Add a primary key to a table

Alter TABLE ' AU1 ' ADD PRIMARY KEY (StudentID)

SELECT * FROM AU1

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image019[1] "border=" 0 "alt=" clip_image019[1] "src=" http://img1.51cto.com/attachment/201302/22/ 400469_1361515536v0ka.png "height="/>

Create a database and table Exercise 7: Creating a Database

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image021 "border=" 0 "alt=" clip_image021 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515537rbgs.png "height=" 419 "/>

You can also use commands to create a database

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image022 "border=" 0 "alt=" clip_image022 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515537zulp.png "height=" 420 "/>

Enter the following command to create database schooldb; Select the Execute

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image023 "border=" 0 "alt=" clip_image023 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515538hkl7.png "width=" 650 "height=" 443 "/>

Exercise 8: Create a table

Select the database you just created to execute the statement that created the table

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image024 "border=" 0 "alt=" clip_image024 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515539yord.png "width=" 650 "height=" 608 "/>

Create student Tables

CREATE TABLE Tstudent

(StudentID nvarchar (15),

Sname nvarchar (10),

Sex nchar (1),

CardID nvarchar (20),

Birthday datetime,

Email nvarchar (40),

Class nvarchar (20),

Entertime datetime)

Create a curriculum

CREATE TABLE Tsubject

(

Subjectid nvarchar (10),

Subjectname nvarchar (30),

BookName nvarchar (30),

Publisher nvarchar (20)

)

Create a score table

CREATE TABLE Tscore

(

StudentID nvarchar (15),

Subjectid nvarchar (10),

Mark Decimal

)

Exercise 9: Generating a table's SQL statement using the graphical interface

Use the graphical interface to generate SQL statements that create, delete, and change objects.

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image025 "border=" 0 "alt=" clip_image025 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515540emqi.png "width=" 650 "height=" 551 "/>

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image026 "border=" 0 "alt=" clip_image026 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515541rmqx.png "width=" 650 "height=" 548 "/>

Use MySQL help to view MySQL help exercise 10: View Help

If you don't know what help can offer? input

? Contents

You can view the available Help

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image027 "border=" 0 "alt=" clip_image027 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515542epy4.png "height=" 417 "/>

Input? Data definition

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image028 "border=" 0 "alt=" clip_image028 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515545sp5j.png "height=" 673 "/>

To view the syntax for creating a table

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image029 "border=" 0 "alt=" clip_image029 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 13615155469g4u.png "height=" 673 "/>

Quick Check Help

In real-world applications, you can use keywords for quick queries if you need to see a grammar quickly. For example, if you want to know what the show command can see, you can use the following command.

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image030 "border=" 0 "alt=" clip_image030 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515547ursl.png "height=" 705 "/>

View Database

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image031 "border=" 0 "alt=" clip_image031 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515550mxst.png "height=" 305 "/>

To view the commands for creating a database

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image032 "border=" 0 "alt=" clip_image032 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515554v88q.png "width=" 650 "height=" 257 "/>

View commands for creating tables

650) this.width=650; "style=" background-image:none;padding-left:0px;padding-right:0px;padding-top:0px; "title=" clip_image033 "border=" 0 "alt=" clip_image033 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515554w4u1.png "width=" 650 "height=" 673 "/>

This article is from "Ghost" blog, please make sure to keep this source http://caizi.blog.51cto.com/5234706/1543125