MySQL Grammar Encyclopedia

1. Add Users

Add a user to all databases to have the function of pruning and checking with the command grant (the meaning of English authorization)

The format is: Grant permissions on the database. * To add user @ Login host identfied by "password";

For example, increase the user paddy password 123,456 only on the local to all databases have add and delete to change the permissions

Grant Select,insert,update,delete on * * to [e-mail protected] identfied by "123456";

If the user wants to log on to the database on any host, only need to change localhost to "%", if the specified IP is logged in, it will be modified to "IP address"

For example: Grant Select,insert,update,delete on * * to [e-mail protected] "127.0.0.1" identfied by "123456";

2. Display, create, delete, select

show databases;

Create DATABASE Paddy;

Drop database Paddy;

Use Paddy;

#判断是否存在数据库paddy, delete some words first
Drop database if exists paddy;

3. Tables Table

Tabular

Show tables;

#创建表
CREATE TABLE Student (
ID int auto_increment PRIMARY key, (ID self-growing meaning)
Name varchar (50),
Sex varchar (20),
Date varchar (50),
Content varchar (100)
) default Charset=utf8;

Delete a table

Dorp table student;

View the structure of a table
describe student; #可以简写为desc student;

Inserting data

INSERT into student values (null, ' AA ', ' Male ', ' 1988-10-2 ', ' ... ');

Querying data in a table

SELECT * from student;

Querying a table for an item of data

Select name from student;

To modify a data somewhere in a table

Update table name set column name and Modified field location

Update student set nane= "Paddy" where id=4;

Delete data;

Delete from student where id=4;

and

Format select * from table name where column name interval and column name interval;

Search for people born between 1980-1-10 and 1985-1-10

SELECT * FROM student where date> ' 1988-1-2 ' and date< ' 1988-12-1 ';

OR OR

SELECT * FROM student where data> ' 1988-1-01 ' or date< ' 1965-01-22 ';

Between

SELECT * from student where date between ' 1988-1-2 ' and ' 1988-12-1 ';

In querying data within a collection

SELECT * from student where ID in {1,5,9};

Table sorted in ascending order ASC table in descending order desc

Format: SELECT * from table name ORDER by ASC;

SELECT * FROM student order by ASC;

SELECT * FROM student ORDER by DESC;

Query the data from article N to article M, not containing n

Format: SELECT * from table name limit m,m;

SELECT * FROM student limit 2, 5; Showing 3-5 data

Total count in statistics (*)

Select COUNT (*) from student;

For example, total statistics ID

Select COUNT (id) from student;

Modify the name of the table

Format ALTER TABLE old table name rename to new table name;

ALTER TABLE A rename to B;

#修改表的名字
#格式: ALTER TABLE tbl_name Rename to New_name
ALTER TABLE test Rename to Test_rename;

#向表中增加一个字段 (column)
#格式: ALTER TABLE tablename add columnname type;/alter table tablename Add (columnname type);
ALTER TABLE test add columnname varchar (20);

#修改表中某个字段的名字
ALTER TABLE tablename change columnname newcolumnname type; #修改一个表的字段名
ALTER TABLE test change name uname varchar (50);

SELECT * from Test;

#表position Add column Test
ALTER TABLE position add (test char);
#表position Modify Column Test
ALTER TABLE position modify Test char (a) not null;
#表position Modify the column test default value
ALTER TABLE position ALTER TEST set default ' system ';
#表position Remove the test default value
ALTER TABLE position ALTER test drop default;
#表position Remove column test
ALTER TABLE position drop column test;
#表depart_pos Delete primary key
ALTER TABLE Depart_pos drop PRIMARY key;
#表depart_pos Increase the primary key
ALTER TABLE Depart_pos add primary key Pk_depart_pos
(department_id,position_id);

#用文本方式将数据装入数据库表中 (e.g. D:/mysql.txt)
Load data local infile "d:/mysql.txt" into table MYTABLE;

#导入. sql file commands (for example, D:/mysql.sql)
SOURCE D:/mysql.sql; #或者/. D:/mysql.sql;

MySQL Grammar Encyclopedia