In liunx (mysql database usage)

Mysql database is a very good database suitable for small and medium-sized enterprises. It is highly practical and is open-source and free.

I hope you can learn it well. I am declaring that all the content here is recorded in one word. I hope you can see it well without plagiarism.

Mysql content

Database Management: create databases, check databases, and delete Databases

Table Management: Create a Table query table and delete a table

SQL query

Data Import and Export

User authorization and permission Revocation

Database backup and recovery

Binlog Incremental Backup

AD

Read/write splitting

Mysql Cluster

LAMP

Service mysqld status check whether the service is started

-------------------------------

Mysql

Vim/etc/my. cnf configuration file

Ps aux | grep mysqld view mysql installation information

Mysql directory of cd/var/lib/mysql/rpm package

REFERENCES

The host at the mysql-h logon address must be authorized before you can log on to the remote address)

Mysql-hlocalhost-uroot-p123

The database name must be unique.

Database names are case sensitive

You cannot create a database name with a pure number.

The specified database name cannot be used.

You cannot use keywords to create a database name.

\ C to end the SQL command, but it must be without a semicolon

The database in mysql is consistent with the file permission in the system.

For the sake of security, we can change the mysql user's shell to sbin/nologin to regard it only as a group, saving the risk of using the mysql user to delete the mysql directory.

-------------------------------------

View Database

Show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Discuz |

| Farm |

| Mysql |

| Test |

| Ucenter |

+ -------------------- +

The test database is a public database that grants permissions at will.

The user information of the mysql authorization database cannot be deleted)

Information_schema schema Database Virtual database the table here does not occupy your disk space and the content is stored in the memory)

[Root @ xu db1] # vim yg5.

Yg5.frm table structure

Data files in the yg5.MYD table

Index information in the yg5.MYI table

-----------------------------------

Create a database

Create database studb;

Used to switch the database

Use studb;

View the current database

Select database ();

Check whether the account currently logged on is

Select user ();

View User Permissions

Show grants;

Ll-d/var/lib/mysql/library folder must have write permission

----------------------------------

Delete files directly from the database. Is it empty)

Drop database studb;

Delete table

Drop table yg11;

View tables in the database

Show tables;

---------------------------

Create a table column called a field in the database. The field name type is a condition such as a male or female name)

Create teble db1.xu (

<Field Name> <type> width) [constraints],

<Field Name> <type> width) [constraints],

<Field Name> <type> width) [constraints]

);

---------------------------------------

Common types in mysql

Numeric type: integer int) floating point float)

Character type char fixed length) varchar variable length) can store a maximum of 255 characters

Date and time type year data time datetime

Enumeration menu multiple choice set multiple choice

Int 3) the value here is the display width and char limit are different. The display width does not limit the length of characters)

The rounded digits are rounded to the decimal places for storage.

Float is a single precision floating point value and double precision floating point value.

---------------------------------

Create fields in the table

Mysql> create table db1.game (

-> Name char (4 ),

-> Level tinyint (3)

-> );

Show tables; View tables

1 byte = 8 bits

----------------------------------

Desc game; view table structure

Create table a (id int); create a table

Show create table a; view the table creation process

---------------------------

Insert content in the field

Mysql> insert into db1.game (name, level)

-> Values

-> ("Ts", 0), ("swk, 300 ");

View contents in the table

Select * from game;

Create a table with Constraints

Create table game2 (level tinyint unsigned );

----------------------------

Create table f (id int (4) zerofill); Use 0 to fill

Numbers do not need to be framed

Difference between single quotes and double quotes

The ticket does not matter

The pair contains symbols or spaces.

----------------------------

Floating Point Type

Create a decimal point. A total of 7 decimal places are 2 decimal places.

Create table gz (gz float (7.2 ));

----------------------

Create a table structure!

Display current date and time

Select now ();

Date and Time Type

Mysql> create table yg4 (

-> Name varchar (10 ),

-> Wrok year,

-> Birthday date,

-> Worktime time,

-> Metting datetime

-> );

Save the current time to the yg4 table through the Function

Insert into yg4 values ("jim", now ());

View

Select * from yg4;

Manual insert

Mysql> insert into yg4 values ("tom", 1998,17560101, 100000,20130218103000 );

View

Mysql> select * from yg4;

Insert value

Mysql> insert into yg4 (name, wrok) values ("lucy", 98 );

01-69 starts with 20

70-99 starts with 19

Inserting 00 is incorrect.

---------------------------------------

Multiple choice for Enumeration type)

Name char4) not null,

Age tinyint 2) unsigned default 24,

Sex enum "boy", "gril", "no") default "boy ",

Loves set "a", "B", "c", "d") default "a, B"

---

Create Table Structure

Mysql> create table yg5 (

-> Name char (4) not null,

-> Age tinyint (2) default 24,

-> Sex enum ("boy", "gril") default "boy ",

-> Loves set ("a", "B", "c", "f") default "a, B"

-> );

Add jim

Insert into yg5 (name) values ("jim ");

View

Mysql> select * from yg5

->;

+ ------ + ------- +

| Name | age | sex | loves |

+ ------ + ------- +

| Jim | 24 | boy | a, B |

+ ------ + ------- +

1 row in set (0.00 sec)

Insert lili

Mysql> insert into yg5 values ("lili", 21, "gril", "");

View

Mysql> select * from yg5

->;

+ ------ + ------- +

| Name | age | sex | loves |

+ ------ + ------- +

| Jim | 24 | boy | a, B |

| Lili | 21 | gril | a |

+ ------ + ------- +

2 rows in set (0.00 sec)

-----------------------------------

Delete records in a table

Delete from a; this means to delete all the content in Table a with caution)

Add a condition to delete a NULL value.

Mysql> delete from yg5 where name = "NULL ";

Delete NULL Fields

Mysql> delete from yg5 where age is null;

--------------------------------

View table structure

Mysql> desc yg5;

Modify Table Structure

Alter table data name, table name action;

Action:

Add field name type width) Constraints

Add

Mysql> alter table yg5 add stu_id char (8) not null,

-> Add mail varchar (30) default "tea@uplooking.com ";

Delete Field

Mysql> alter table yg5 drop sex;

Add to first line

Mysql> alter table yg5 add stu_id char (8) not null first;

Add QQ to sex

Mysql> alter table yg5 add QQ varchar (8) not null after sex;

---------------------------------

Modify the field type

Modify

View table structure

Desc game;

Modify type

Mysql> alter table yg5 modify loves set ("muisc", "football") not null defalut "muisc ";

--------------------------

Modify Field name

Change

Change the field name to email

Mysql> alter table yg5 change mail email varchar (30) default "tea@uplooking.com ";

------------------------------

To modify the records in the ro update table, you must add the conditions)

Uptate table name set field list where Condition

Modify

Mysql> update yg5 set stu_id = "88888888" where name = "lucy ";

Modify multiple records

Mysql> update yg5 set stu_id = "99999999", QQ = "2348444", loves = "book, film" where name = "lili ";

------------------------------------

When copying a table, the original table key field is not copied to the new table)

Mysql> create table yg6 select * from yg5;

Copy the empty table from the original table structure) the error query displays a null value.

Mysql> create table yg7 select * from yg5 where name is null;

-----------------

Rename a table

Mysql> alter table yg7 rename txt;

-------------------------

This article is from the "history_xcy" blog, please be sure to keep this http://historys.blog.51cto.com/7903899/1296700