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

View the database

SHOW DATABASES

Create a database

CREATE DATABASE IF NOT EXISTS database name

Select a database

USE database name

View the data table in the database

SHOW TABLES

Delete database

DROP DATABASE IF EXISTS database name

Create a simple database table

CREATE TABLE IF NOT EXISTS table name (

Id INT UNSTGND AUTO_INCREMENT PRIMARY KEY

Name VARCHAR (255) NOT NULL

Sex TINYINT NOT NULL DEFAULT 1

Age TINYINT NOT NULL DEFAULT 0

) ENGINE = MyISAM DEFAULT CHARSET=utf8

Add data

INSERT INTO table name VALUES (NULL,'cendxia',1,22)

Query data

SELECT * FROM table name

Modify data

UPDATE table name SET field 1 'value 1', field 1 'value 2' WHERE condition

Delete data

DELETE FROM table name WHERE condition

Create a new normal user

GRANT permission ON library name. Table name TO 'username' @ 'hostname' IDENTIFIED BY 'password'

Query all users

SELECT user,host FROM mysql.user

Delete an ordinary user

DROP USER 'username' @ 'hostname'

Modify root user password

SET PASSWORD = PASSWORD ('new password')

Root users modify passwords of ordinary users

SET PASSWORD FOR 'username' @ 'hostname' = PASSWORD ('new password')

Authorization

GRANT permission ON library name. Table name TO 'username' @ 'hostname' IDENTIFIED BY 'password'

GRANT SELECT,INSERT,UPDATE,DELETE ON cendxia.user TO 'username' @ 'hostname' IDENTIFIED BY 'password'

View permissions

SHOW GRANTS FOR 'username' @ 'hostname'

Take back the authority

REVOKE permission ON library name. Table name FROM 'username' @ 'hostname'

Backup

Mysqldump-uroot-p database name > location to save

Restore data

Mysql-uroot-p database name

Create a datasheet

CREATE TABLE IF NOT EXISTS table name (

Field 1 Type (length) attribute Index

Field 2 Type (length) attribute Index

Field 3 Type (length) attribute Index

Field 4 Type (length) attribute Index

Field n. Don't put a comma after the last field

) ENGINE=MyISAM DEFAULT CHARSET=UTF8

Table engine

MyISAM-read fast and does not support transactions

InnoDB-slightly slower read speed supports transaction rollback

Some common attributes

UNSTGND unsigned attribute

AUTO_INCREMENT self-increasing attribute (commonly used on id fields)

ZEROFILL zero filling

String type

CHAR fixed-length string type (0-255characters)

VARCHAR variable length string type, 0-255characters before 5.0and 0-65535 characters after version 5.0

View table structure

DESC table name; (abbreviated version)

DESCRIBE table name

View table creation statement

SHOW CREATE TABLE table name

Modify table name

ALTER TABLE original table name RENAME TO new table name

Modify the data type of a field

ALTER TABLE table name MODIFY field name data type attribute index

Modify field name

ALTER TABLE table name CHANGE original field name new field name data type attribute index

Add field

ALTER TABLE table name ADD field name data type attribute index

-- [FIRST | AFIER field name]

-- (FIRST adds the field at the beginning. AFIER field name is added after a field)

Delete a field

ALTER TABLE table name DROP field name

Modify the arrangement position of the field

ALTER TABLE table name MODIFY field name data type attribute index AFIER field name

Modify table engine

ALTER TABLE table name ENGINE= engine name;-- MyISAM or InnoDB

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.