MySQL Basic operation

I. MySQL installation, startup

Yum-y install mysql-sever MySQL

Service mysqld Start

Two. Common settings

1.mysql

-u username user account is all called: User name @ host

Username are divided into two categories:

Root

127.0.0.1

localhost

Hostname

:: 1

";(anonymous user)

localhost

Hostname

-p password

-H hostname

-e ' SQL command; ' do not log on to the MySQL client directly execute SQL commands

2.mysql Client Commands

\q exit

Status View state information

\? Help

\g Direct the command to the server-side execution

\G Information Vertical Display

\c terminating command execution

Use DatabaseName to set the default database

3. The server-side command requires a terminator, the default is a semicolon, you can modify

Select User (); Gets the user () function return value

Create database DatabaseName;

show databases; Show all databases

Show tables; table showing the database

Drop database databasename;

DESC tablename; Show Table structure

Select User,host,password from user, Show table field

Drop user ' username ' @ '%.example.org '; Delete User

Drop user ' @ ' localhost '; Delete anonymous users

Set password for ' username ' @ ' hostname ' = password (' cleartext password '); Set Password

4.Mysql batch processing mode and interactive mode

Myql-u username-p Password >test.sql

5. Key

Primary key: A unique combination of fields or fields that uniquely identify each record in a table and cannot be null

Candidate key: A unique combination of fields or fields that identify each record in the table (which can be used as a primary key) and can be null

FOREIGN key:

6. Constraint constraint

PRIMARY KEY constraint: Only one primary key in a table, record unique, cannot be null

FOREIGN KEY constraint: The field record in the table associated with the reference table does not appear if the associated field record is not present in the referenced table

Unique key constraint: can have multiple, record unique, can be null

Check constraint: User-defined valid range of values

Non-null constraint: Record is not allowed to be empty

7. Table Operations

Two-dimensional relationship consisting of rows and columns

Field type:

Character type

char (#) fixed length

varchar (#) Variable length

Binary (#) case-sensitive, fixed-length

varbinary (#) Case-sensitive, variable-length

Text Large segment character

Blob large segment character, case-sensitive

Numeric type

Precise numerical type

Int

tinyint

smallint

Mediumint

Int

bigint

Decimal

Approximate numerical type

Float

Double

Date-Time Type

Date

Time

Datetime

Timestamp

Boolean type

Null

Built-in types

enum enum, selecting from a predefined type

Set collection, selecting multiple combinations from a predefined type

Data type

Comparison mode

Storage space: Range of values

Participate in the operation

Create a table

CREATE TABLE TableName (Col1_name,col1_type,...);

Eg:create table Test (name char (ten), Age Tinyint,gender ENUM (' F ', ' M ');

field or field type can also have modifiers

Not NULL

Null

UNSIGNED can only be used for shaping

DEFAULT

Auto_increment fields for autogrow type must be primary key and unique key

PRIMARY KEY

UNIQUE KEY

Eg:create table Test (name char (ten), age tinyint UNSIGNED not null,gender ENUM (' F ', ' m ' DEFAULT ' m ');

Eg:create table Test (Studyid int UNSIGNED not NULL auto_increment PRIMARY KEY);

Delete a table

Drop TableName;

Inserting data

INSERT INTO TableName (col1,col2,...) value|values (val1,val2,...);( Character-type records quoted)

Eg:insert into Test (Name,age,gender) VALUES (tom,18, ' F '), (jeff,16, ' M ');

INSERT INTO tablename values ();

Inquire

Select Col1,col2 from TableName where condition

Like wildcard character fuzzy matching

%: matches any character

_: Matches any single character

Rlike Regular Expression Slimy match

Update data

Updata TableName set col1=val where condition;

Delete data

Delete from TableName

Delete from TableName where condition

MySQL Basic operation