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

Source: Internet
Author: User
Tags mysql version one table

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.