MySQL Database common operation syntax

1.? Database initialization Configuration 1.1.? Create a database

create database apps character set utf8 collate utf8_bin;
Create the database "app" and specify the encoding as UTF8

1.2.? Create user

create user ‘apps‘@‘localhost‘ identified by ‘12345‘;
Create user apps, password 12345, set to access local MySQL from localhost only

create user ‘apps1‘ identified by ‘12345‘;
User APPS1 can access the current MySQL from any domain or host

1.3.? Configure user Permissions

grant all privileges on apps.* to ‘apps‘@‘localhost‘;
Gives users all permissions to all tables in database apps when they log on to MySQL from localhost

Grant's 14 permissions Select, INSERT, UPDATE, delete, create, drop, index, alter, Grant, references, reload, shutdown, process, file

grant select, insert, update, delete, create, drop on CTD.posts to [email protected] identified by ‘obligingneighbor‘;
Specify partial permissions for a single table

grant all privileges on *.* to ‘admin‘@‘127.0.0.1‘ identified by ‘administrator‘;Give full permissions to all databases

1.4.? Refresh Permissions Table

flush privileges;quit;  // 退出

1.5.? User Management 1.5.1.? insert a record directly into the Mysql.user table:

insert into user (host,user,password) values (‘%‘,‘jss_insert‘,password(‘jss‘));flush privileges;

1.5.2.? To modify the MySQL user password method:

• use mysqladmin syntax    Mysqladmin-u User Name----old password password new password
  mysqladmin-u root-p 123 password 456;

• Update Mysql.user set Password=password (' New password ') where user= "username" and host= "localhost";
update user set Password=password (' 54netseek ') where user= ' root '; flush privileges;

• Use the Set Password statement to modify the password syntax: SET PASSWORD FOR ‘username‘@‘host‘ = PASSWORD(‘newpassword‘); If you are changing the password of the currently logged on user, use the SET PASSWORD = PASSWORD("newpassword"); instance:

set password for [email protected]=password(‘‘);SET PASSWORD FOR name=PASSWORD(‘new password‘);SET PASSWORD FOR ‘pig‘@‘%‘ = PASSWORD("123456");

1.5.3.? Delete users and revoke permissions:

Cancel an account and its permissions

drop user User;drop User [email protected] '% ' DROP USER [email  protected]  

• un-authorized user

  syntax: revoke privilege on Databasename.tablename from ' username ' @ ' host ';

  example:

  revoke Select On * * from ' pig ' @ '% '; REVOKE SELECT on Test.user from ' pig ' @ '% ', REVOKE all on * * FROM [email protected], REVOKE all on user.* from ' admin ' @‘%‘;     SHOW GRANTS for ' pig ' @ '% '; View Authorization  

• Delete User: syntax:  delete from user where user =" user_name " and host = "host_name";

Example:delete from user where user=‘sss‘ and host=‘localhost‘;

2.? Database Management 2.1.? View all databases

Database default directory:/usr/local/mysql/data

SHOW DATABASES;   // 显示数据库USE abccs         // 进入数据库SHOW TABLES;      // 显示表DESCRIBE mytable; // 显示表结构CREATE DATABASE abccs;    // 创建一个数据库CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20));   //创建表

2.2.? Insert Data

• Use INSERT statement
  insert into mytable values (' Abccs ', ' f ', ' 1977-07-07 ', ' China ');

• Insert data using text mode

  mysql.txt content:

  abccs F 1977-07-07 China Mary F 1978-12-12 USA Tom M 1970-09-02 USA  

  import data file into table  pet : load data LOCAL INFILE "Mytable.txt" into TABLE pet;

2.3.? Modify database or table settings

drop database drop_database;   //删除一个已经确定存在的数据库alter table 表名 ENGINE=存储引擎名；  //修改表的存储引擎alter table 表名 drop 属性名； //删除字段alter table 旧表名 rename to 新表名；  //修改表名alter table 表名 modify 属性名 数据类型；  //修改字段数据类型alter table 表名 change 旧属性名 新属性名 新数据类型； //修改字段名alter table 表名 drop FOREING KEY 外键别名； //删除子表外键约束

2.4.? Modify a table field

alter table example add phone VACGAR(20); //增加无约束的字段alter table example add age INT(4) NOT NULL; //增加非NULL的字段alter table example add num INT(8) PRIMARY KEY FIRST;  //表的第一个位置增加字段alter table example add address VARCHAR(30) NOT NULL AFTER phone;  //表的指定位置之后增加字段alter table example modify name VARCHAR(20) FIRST; //把字段移动到第一位alter table example modify num INT(8) AFTER phone；//把字段移动到指定字段之后

MySQL Database common operation syntax