Getting started with MySQL

Data types in MySQL

MySQL has three major classes of data types, number, date \ Time, string, and more detailed sub-types in the three categories:

• 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
• String: char, varchar
• Text: Tinytext, text, Mediumtext, Longtext
• Binary (used to store pictures, music, etc.): Tinyblob, Blob, Mediumblob, Longblob

These types cannot be described in detail here, and may be lengthy, in detail see: MySQL Data type: http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html

MySQL command 1, connect MySQL

Format: mysql-h host name-u user name-P

• - H: This command is used to specify the MySQL hostname that the client wants to log on, and the parameter can be omitted when logging on to the current machine;
• - u: The name of the user to log in;
• - P: tells the server that a password will be used to log in, ignoring this option if the user name password you want to log in is blank.

To log in as an example of a MySQL database that has just been installed on this computer, enter Mysql-u root-p at the command line to confirm that if the installation is correct and MySQL is running, you will get the following response:

Enter Password:

If the password exists, enter the password to login, does not exist then directly press ENTER to log in. Once you're signed in, you'll see Welecome to the MySQL monitor ... The prompt.

Then the command prompt will always wait for the command input with mysql> plus a blinking cursor, enter exit or quit to sign out.

You can not use the-h hostname when logging on locally.

2. Change the password Format: Mysqladmin-u username-P Old password password new password

3.1. Create a database

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

Create database name [other options];

Example 1: Establishing a database named Xhkdb
mysql> CREATE DATABASE xhkdb;

Example 2: Creating a database and assigning users

①create database name;

②grant select,insert,update,delete,create,drop,alter on database name. * To database name @localhost identified by ' password ';

③set PASSWORD for ' database name ' @ ' localhost ' = old_password (' password ');

Execute 3 commands in turn to complete the database creation. Note: The Chinese "password" and "database" are the user's own needs to set. 3.2. Display Database command: Show Databases(Note: There is a last s)
Mysql> show databases;3.3, delete database

Command:drop databases < database name >
Example: Delete a database named Xhkdb
mysql> drop Database xhkdb;

Example 1: Delete a database that has been determined to exist
mysql> drop Database drop_database;
Example 2: Deleting a database with an indeterminate existence
Mysql> drop database if exists drop_database;

3.4, connect the database to operate on a database, you must first select the database, otherwise you will be prompted with an error:

Two options for using the database:

One: specified when logging in to the database, command: mysql-d selected database name-h hostname-u user name-P

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

Second: after logging in using the USE statement to specify, command: Used database name;

The USE statement can be executed without a semicolon, and a samp_db is used to select the database you just created, and you will be prompted after successful selection: Database changed

3.5 View the currently selected database and table commands:mysql> select databases ();

The select command in MySQL is similar to print or write in other programming languages, and you can use it to display the results of a string, a number, a mathematical expression, and so on. How do I use the special features of the Select command in MySQL?

Show MySQL version
Mysql> select version ();

Show Current Time
Mysql> Select Now ();

Show Month Day
SELECT DayOfMonth (current_date);

Display string
Mysql> Select "Welecome to my blog!";

When the calculator uses
Select ((4 * 4)/10) + 25;

Threaded string
Select CONCAT (F_name, "", L_name)

4. Create a data table

Command:CREATE table < table name > (< column 1> < type 1> [,.. < column n> < type n>]);

For example, to create a table named MyClass,

field name	Number Type	Data Width	is empty	whether the primary key	Auto Increment	Default Value
ID	int	4	no	primary key	auto_increment
name	char		no
sex	int	4	no			0
degree	double	+	yes		&NB SP;

Mysql> Create table Class (
> ID int (4) NOT NULL primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

The "ID int (4) NOT null primary key Auto_increment" line is described:

• "id" is the name of the column;
• "Int (4)" Specifies that the column is of type int and the data length is 4 bits;
• "Not NULL" indicates that the value of the column cannot be empty and must be filled, and the default can be null if the property is not specified;
• The "auto_increment" needs to be used in an integer sequence, and the effect is that if the column is NULL when inserting the data, MySQL will automatically produce a unique identifier value that is larger than the existing values . 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, the value of this column must be unique, and MySQL will automatically index the column.

5. Manipulate the MySQL database to insert data into the table

The INSERT statement 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, ...);

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

Mysql> INSERT INTO class values (1, ' Tom ', 1,96.45), (2, ' Joan ', 1,82.99), (2, ' Wang ', 1,96.59);

Sometimes we just need to insert some of the data, or not in the order of the columns, you can insert them in this form:

INSERT into Class (name, sex, age) VALUES ("Tiny", 1, 21);

Querying data in a table

Select statements are commonly used to obtain data from a database based on certain query rules, with the following basic usage:

Select Column name from table name [query condition];

Mysql>select id,age from class;

You can also view all the columns in the table:

Mysql>select * from class;

You can also use the qualifying conditions:

Mysql>select id,age from class where degree < 90;

Updating data in a table

The UPDATE statement can be used to modify the data in the table, using the following basic form:

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

Mysql> Update class set name= ' Mary ' where id=1;

Delete data from a table

The DELETE statement is used to delete data from a table, with the following basic usage:

Delete from table name where delete condition;

Example: Deleting a record with number 1 in table MyClass
Mysql> delete from class where id=1;

6. Changes to the post-creation table

The ALTER TABLE statement is used to modify the table after it is created, using the following basic usage:

Adding columns

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

Mysql> ALTER TABLE class add birthday date after age; The format of date is: "Xxxx-xx-xx", the value should be added double quotation marks "";

modifying columns

Basic form: ALTER TABLE name change column Name column new name new data type;

Change the data type of the Name column to char (16):

ALTER TABLE class change name name char (+) not null;

Delete Column

Basic form: ALTER TABLE name drop column name;

Example:

To delete a birthday column:

ALTER TABLE class drop birthday;

Renaming a table

Basic: ALTER TABLE name rename new table name;

Example:

Rename the class table to classmates:

ALTER TABLE class rename classmates;

Delete entire table

Basic form: drop table name;

Example: Delete a classmates table:

drop table classmates;

Delete entire database

Basic form: drop database name;

Example: Delete a samp_db database:

Drop database samp_db;

21 minutes MySQL Getting Started Tutorial-wid-Blog Park

mysql command Daquan-quiet. Zhiyuan-Blog Park

Rubric: MySQL Advanced guide-Blog Channel-Csdn.net http://blog.csdn.net/column/details/mysqlnote.html

Domestic MySQL Technology blog

http://isky000.com/Jenzhiang
http://www.ourmysql.com/
http://www.mysqlsupport.cn/
http://imysql.cn/Ye Jinlong
http://www.mysqlab.net/blog/Tan Junqing
http://www.orczhou.com/
http://www.ningoo.net/(Oracle)
Http://www.taobaodba.com/taobao DBA Team
Http://www.alidba.net/alidba
http://chj733.spaces.live.com/Eight God
http://www.hellodba.net/
http://www.dbanotes.net/
http://dbahacker.com/
http://www.mysqlsystems.com/
Http://www.helpphp.cn/category/php-mysql


http://zhaolinjnu.blog.sohu.com/
Http://hi.baidu.com/dbaeyes
Http://hi.baidu.com/thinkinginlamp Lao Wang
Http://hi.baidu.com/liuzhiqun/blog/category/Mysql
http://zhaolinjnu.blog.sohu.com/
Http://www.yayu.org/categroy.php?id=9
Http://blog.csdn.net/radkitty/category/471279.aspx

Getting started with MySQL