Shulou(Shulou.com)06/01 Report--

This article mainly introduces the basic basic knowledge of mysql, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the basic basic knowledge of mysql.

Mysql operation

First, connect to the database

Mysql-u user-p example: mysql-u root-p

Common errors are:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.

Exit the connection:

QUIT or Ctrl+D

Second, view the database, create the database, and use the database to view the database: show databases

Default database:

Mysql-data related to user rights

Test-for user test data

Information_schema-MySQL itself constructs relevant data

Create the database:

Create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8 coding

Create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk coding

Use database: use db1

Displays all tables in the database currently in use: SHOW TABLES

III. User management

Create a user

Create user 'username' @'IP address' identified by 'password'

Delete user

Drop user 'user name' @'IP address'

Modify user

Rename user 'user name' @'IP address'; to 'new user name' @'IP address'

Modify the password

Set password for 'username' @'IP address'= Password ('new password')

Note: user rights-related data is saved in the user table of the mysql database, so it can also be manipulated directly (not recommended)

IV. Authority management

Mysql has the following restrictions on permissions:

For database and other internal permissions are as follows:

Database name. * all in the database

Database name. Table specifies a table in the database

Database name. Stored procedure specifies the stored procedure in the database

*. * all databases

The permissions for users and IP are as follows:

User name @ IP address users can only access it under the changed IP

User name @ 192.168.1% users can only access it under the changed IP segment (wildcard% indicates any)

User name @% user can be accessed under any IP (default IP address is%)

1. View permissions:

Show grants for 'user' @'IP address'

2. Authorization

Grant permissions on database. Table to 'user' @'IP address'

3. Cancel authorization

Revoke permissions on database. Table from 'user' @'IP address'

Examples of authorization are as follows:

Grant all privileges on db1.tb1 TO 'user name' @ 'IP'

Grant select on db1.* TO 'user name' @ 'IP'

Grant select,insert on *. * TO 'user name' @ 'IP'

Revoke select on db1.tb1 from 'user name' @ 'IP'

Mysql table operation

1. View the table

Show tables; # View all tables in the database

Select * from table name; # View all table contents

2. Create a table

Create table table name (

Whether the column name type can be empty

Whether the column name type can be empty

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Let's give an example to explain in detail.

CREATE TABLE `tab1` (

`nid` int (11) NOT NULL auto_increment. # not null means it cannot be empty. Auto_increment means self-increment.

`name` varchar (255) DEFAULT zhangyanlin, # default indicates the default value

`email` varchar (255)

PRIMARY KEY (`nid`) # set the nid column as the primary key

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Note:

Default value, which can be specified when the column is created, and will be added automatically if it is not actively set when inserting data

Self-increment, if you set a self-increment column for a column, you do not need to set this column when inserting data, and it will be self-incrementing by default (there can only be one self-increment column in the table) Note: 1. For self-increment column, it must be index (including primary key) 2. For self-increment, you can set step size and starting value.

The primary key, a special unique index, does not allow null values, if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique.

3. Delete the table

Drop table table name

4. Clear the contents of the table

Delete from table name

Truncate table table name

5. Modify the table

Add column: alter table table name add column name type

Delete column: alter table table name drop column column name

Modify the column:

Alter table table name modify column column name type;-- type

Alter table table name change original column name new column name type;-- column name, type

Add a primary key:

Alter table table name add primary key (column name)

Delete the primary key:

Alter table table name drop primary key

Alter table table name modify column name int, drop primary key

Add foreign key: alter table slave table add constraint foreign key name (such as: FK_ slave table _ master table) foreign key slave table (foreign key field) references master table (primary key field)

Delete foreign key: alter table table name drop foreign key foreign key name

Modify the default value: ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000

Delete default: ALTER TABLE testalter_tbl ALTER i DROP DEFAULT

For these operations do not seem very troublesome, it is a waste of time, do not panic! There is a special software that can provide these functions, and it is very easy to operate. this software is called Navicat Premium, and you can download and practice it on the Internet, but the following is about table content operation or it is recommended to write your own commands.

6. Basic data types

The data types of MySQL are roughly divided into: numeric value, time, and string.

Bit [(M)]

Binary bit (101001). M represents the length of the binary bit (1-64). The default is msq1.

Tinyint [(m)] [unsigned] [zerofill]

A small integer, a data type used to hold a range of integer values:

Signed:

-128 ~ 127.

Unsigned:

0 ~ 255

Special: no Boolean value in MySQL, constructed using tinyint (1).

Int [(m)] [unsigned] [zerofill]

An integer, a data type used to hold a range of integer values:

Signed:

-2147483648 ~ 2147483647

Unsigned:

0 ~ 4294967295

Special: the m in the integer type is used for display only and there is no limit to the storage range. For example: int (5), when inserting data 2, the data is displayed as: 00002 when select

Bigint [(m)] [unsigned] [zerofill]

Large integers, data types used to hold a range of integer values:

Signed:

-9223372036854775808 ~ 9223372036854775807

Unsigned:

0 ~ 18446744073709551615

Decimal [(m [, d])] [unsigned] [zerofill]

The exact decimal value, m is the total number of numbers (minus sign is not counted), d is the number after the decimal point. The maximum value of m is 65 and the maximum value of d is 30.

Special: this type is required for accurate numerical calculations

The reason why decaimal can store exact values is that it is stored internally as a string.

FLOAT [(MMagar D)] [UNSIGNED] [ZEROFILL]

Single-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point.

Unsigned:

-3.402823466E+38 to-1.175494351E-38

0

1.175494351E-38 to 3.402823466E+38

Signed:

0

1.175494351E-38 to 3.402823466E+38

* the higher the number, the less accurate it is.

DOUBLE [(MMagar D)] [UNSIGNED] [ZEROFILL]

Double-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point.

Unsigned:

-1.7976931348623157E+308 to-2.2250738585072014E-308

0

2.2250738585072014E-308 to 1.7976931348623157E+308

Signed:

0

2.2250738585072014E-308 to 1.7976931348623157E+308

* the higher the number, the less accurate it is.

Char (m)

The char data type is used to represent a fixed-length string that can contain up to 255characters. Where m represents the length of the string.

PS: even if the data is less than m length, it will occupy m length.

Varchar (m)

The varchars data type is used for variable-length strings that can contain up to 255characters. Where m represents the maximum length of the string allowed to be saved by the data type, as long as the length is less than the maximum value, the string can be saved in the data type.

Note: although varchar is flexible to use, the processing speed of the char data type is faster from a system-wide performance perspective, sometimes exceeding 50% of the varchar processing speed. Therefore, users should comprehensively consider all aspects of factors when designing the database in order to achieve the best balance.

Text

The text data type is used to hold long large strings, which can be grouped up to 65535 (2 characters) characters.

Mediumtext

A TEXT column with a maximum length of 16777215 (2 hours 24 1) characters.

Longtext

A TEXT column with a maximum length of 4294967295 or 4GB (2x 321) characters.

After reading the above basic knowledge about mysql, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.