MySQL user, library, table operation syntax about the user's syntax

MySQL user, library, table operation syntax about the user's syntax

查看当前系统里的用户:select user,host from mysql.user;查看当前登陆的用户select user();创建用户语法：create user ‘用户‘@‘主机‘ identified by ‘密码‘;示例：create user ‘anuo‘@‘localhost‘ identified by ‘123‘创建用户并授权grant 权限 on 库.表  to ‘用户名‘@‘主机域‘ identified by ‘用户密码‘   示例：grant all on *.* to ‘anuo‘@‘localhost‘ identified by ‘123‘;查看某用户的权限show grants for ‘用户‘@‘主机‘\G示例：show grants for ‘anuo‘@‘10.0.0.0/24‘\G回收权限revoke 权限 on 库.表 from 用户名@主机;示例：revoke insert on *.* from [email protected];删除用户drop user ‘用户名‘@‘主机‘示例：drop user ‘anuo‘@‘10.0.0.0/24‘;

Library manipulation Syntax

View the database – View all mysql> show databases; see your related help mysql>? show databases; creating a database syntax: Create library name;mysql> CREATE database haha; Define character encoding syntax when creating databases: Create Library name Charser  Character encoding;mysql> create database haha charset UTF8; query database definition information syntax: Show CREATE DATABASE name;mysql> show CREATE database haha; Existing database modified character encoding syntax: Alter library name Charser character encoding mysql> ALTER DATABASE haha charset GBK; standard modification system ALTER DATABASE [library name] Characte R set Character Set name COLLATE proofing rules; ALTER DATABASE haha CHARACTER set utf8mb4 COLLATE utf8mb4_general_ci; View supported character sets and proofing rules. 1, mysql> show CHARACTER set;2, Mysql> show collation; To view the status of the current database:mysql> status;--------------mysql Ver 14.14 distrib 5.6.25, for Linux (x86_64) usi ng Editline wrapperconnection id:2--ID number of the link is current Database:test--Currently the library name is User: [Email&nbs P;protected]--Login user Ssl:not in use--whether sslcurrent pager:stdoutusing outfile: ' Using D       Elimiter:; --session Terminator server Version:5.6.25-log Source DistributiOn--mysql version Number Protocol Version:10-protocol version connection:localhost via UNIX socket-the type of link used is linked via the socket file Server Characterset:utf8--the character set used by the server DB Characterset:utf8--the character set used by the current database client Characterset:utf8--  The character set Conn is used by the current client.  Characterset:utf8--The character set used by the current link UNIX socket:/data/3306/mysql.sock--socket file path uptime:12 min 10 Sec--Database run time threads:1 questions:22 Slow queries:0 opens:71 Flush tables:1 Open tables:64 queries per Seco ND avg:0.030 Delete database syntax: Drop library name mysql> drop db haha; çeku mysql> use haha;

Syntax for table operations

View the table inside the library (note: SELECT into the library to see the table in the vault) mysql> show tables; View all table status information in the Library mysql> Show Table status\g View the status syntax for a single table: Show Table Status lik E ' table name ' \gmysql> Show table ' status like ' Xixi ' \g CREATE TABLE syntax: the name (structure) of the Created Tables table;mysql> CREATE TABLE Xixi (id int); View Table Mysql&gt ; Show tables; Delete table syntax: Drop the table name;mysql> drop table Xixi; create more table structures; Syntax: CREATE TABLE table name (Field 1, Field 2, ...).    --note that each field is separated by commas mysql> CREATE TABLE hehe (id int,name char, sex char (4)); Field definition: Field name, data type, constraints commonly used data type INT--integer value Char --Fixed long character varchar--variable long character date--date time--year (2|4)--years common constraint: null--Indicates that the field can be empty (not set by default for this type) NOT null--Indicates that the field is non- Empty aoto_increment-Self-growth, for numeric definition primary key primary key (field 1, Field 2, ...) )--(a table can have only one primary key, one primary key can contain multiple fields) ALTER TABLE Xixi add Primary.key (ID)--Define the primary key 1 when the ID field under the Xixi table is set as primary key, and create table Xixi   (id int (4) Primary key,name Chat (11)); --defines a single primary key 2, CREATE TABLE Xixi (ID int (4), name char (one), primary key (Id,name)); --Define the federated primary key unique key (field 1, Field 2, ...). )--Define unique keys, one table can have more unique keys to create indexes index index_name (field) ALTER TABLE Xixi add index index_name (name) --Define the index when creating a table with the Name field under the Xixi table: Create tables Xixi (ID int (4), name char (one), primary key (ID), index index_name (name));   View index show index from Xixi;     --Represents a partial index in the view table of the Xixi table. Show index from Xixi where key_name like ' ind '; --View the index in the Xixi table starting with IND NOTE: The following syntax format is also possible, and more clearly defined statements push this usage.    Mysql> CREATE table Anuo, ID int (4), Name Char (one), primary key (ID), index index_name (name); Query OK, 0 rows affected (0.27 sec) when you create a new table, copy the structure syntax of another old table: Create table with a new table name like the old table name; example: Create table anuo like Xixi; View Build Table statement 1, my  Sql> Show CREATE Table hehe;2, mysql> Show create table hehe\g modify table name 1, Syntax: Rename table table name to new name;mysql> rename tables hehe to WAHAHA;2, Syntax: ALTER TABLE table name rename to new table name;mysql> ALTER TABLE Wahaha Rename to Xixi; View table structure mysql> desc xixi; Modify table Structure mysql> ALTER TABLE Xixi add char (+) not null;mysql> desc People;after adds syntax after the specified field: ALTER TABLE name add new field after specified field example: ALTER TABLE Xixi aDD Age int (4) After Name;first added in front of the specified field (default added in front) ALTER TABLE XIXI add ID1 int First, add Shouji char (one) after name; Delete table field :mysql> ALTER TABLE Xixi drop sex; Modify the definition of a table field mysql> ALTER TABLE Xixi modify name char (20); Modify column name:mysql> ALTER TABLE X Ixi Change name A_name char (30); Specify multiple options when creating a table such as: Storage engine, self-growth, Character set example:mysql> CREATE TABLE Xixi, ID int primary key auto_increment, Nam e char (one) not NULL, index index_name (name), Engine=innodb auto_increment=2 default Charset=ut f8;--Specifies the InnoDB storage engine, the ID field is set to self-grow starting from 2, and the default character set is UTF8

MySQL user, library, table operation syntax about the user's syntax