MySQL Common commands

MySQL common command one, user management

When the IP address is a paragraph, you can use the wildcard%, i.e. ' 192.1638.1.% '

Create user

Create user ' username ' @ ' IP address ' identfied by ' password ';

Delete User

Drop user ' username ' @ ' IP address ';

Modify User

Remane user ' username ' @ ' IP address ', to ' new user name ' @ ' IP address ';;

Change Password

Set password for ' username ' @ ' IP address ' =password (' new password ');

Second, user rights:

By default, there's nothing.

Show grants for ' username ' @ ' IP address ' #查看权限

Grant permissions on the database. Table to ' username ' @ ' IP address ' #授权

Remove permissions on the database. Table to ' username ' @ ' IP address ' #取消授权

Allprivileges #除了grant外的所有权限即除了不能给其他用户设置权限外, all other root privileges it has

Select #査权限

Database. Table

TEST.TB1 #只允许査test数据库中的tb1表

Test.* #允许査test数据库中所有表

*. * #允许查看所数据库和数据库里的所有表

Select,insert #査和插入数据权限

III. SQL statement Database level

show databases; #查看当前Mysql都有哪些数据

Create database name; #创建数据库

Create database name default charset UTF8 collate utf8_general_ci;

Use database name; #使用该数据库

drop database name; #删除数据库

Table level

Show tables; #查看数据库中所有表

DESC table name; #查看数据表的数据结构

CREATE TABLE table name (column name data type, column name data type) #创建表

Eg:create table TD1 (ID int,name varchar (20));

Eg:create Table Name (

Whether the column name type can be empty

Whether the column name type can be empty

) Engine=innodb default Charset=utf8;

A. Default value B. Whether it can be null, NULL is set to NULL, and cannot be set to not nullc. Self-increment column (only one, number, must be index-primary key) d. Primary KEY index:

A table can have only one primary key, unique cannot be duplicated, cannot be null, and in general, the self-increment column sets the primary key

Unique index:

can be null, a table can have more than one unique column

--constraints

--indexing, accelerating search

Primary key:

cannot be null, cannot be duplicated, a table has only one primary key (multiple columns can make up a primary key)

#一般用法:

ID int auto_increment PRIMARY key,

E, foreign key:

ALTER TABLE is restricted to table name add constraint foreign key name foreign key is restricted table (restricted column name) references table name of another table (provides a column name for the restriction).

The effect is: two tables to establish constraints, to achieve a one-to-many

F, data type: Numeric, time, and string values:

Bit binary

Integer:

tinyint

smallint

Int

bigint-the difference between the above four is different for the respective range

Decimal:

Decimal--accurate, preserving the original value

Float--the least accurate

Double--not very precise

String:

CHAR (m)-fixed-length string, stored as a constant, relatively space-saving, fast lookup rate

varchar (m)-variable-length string, storing up to a set value, and finding slow

Text--

Mediumtext

Longtext

Binary data:

Tinyblob,blob,mediumblob,longblob

Time:

DATE:

YYYY-MM-DD (1000-01-01/9999-12-31)

Time:

HH:MM:SS (' -838:59:59 '/' 838:59:59 ')

Year:

YYYY (1901/2155)

Datetime:

Yyyy-mm-dd HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)

TIMESTAMP:

YYYYMMDD HHMMSS (1970-01-01-00:00:00)

The differences between enumerations and collections:

Enumeration is a single selection, and the collection is multiple

drop table name; #直接删除表

Delete from TD1, #清空表内容, increment will be followed by the value added before deletion

TRUNCATE table name; #清空表内容, fast, self-increment back to the origin.

To modify a table:

To add a column:

ALTER TABLE name add column name type;

To delete a column:

ALTER TABLE name drop column name;

To modify a column:

ALTER TABLE name modify column name type; #只能改类型

ALTER TABLE name change original column name new column name type; #既能改列名又能改类型

To add a primary key:

ALTER TABLE name add primary key (column name);

To delete a primary key:

ALTER TABLE name drop PRIMARY key;

ALTER TABLE table name modify column name Int,drop primary key;

To add a foreign key:

ALTER TABLE name add constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key column name) references Main Table (primary key field);

To delete a foreign key:

ALTER TABLE name dropforeign key foreign key name;

Data row level: A, increase

Insert into table name (column name, column name) VALUES (' Value 1 ', ' Value 2 '); #一次性增加一条数据

Insert into table name (column name, column name) VALUES (' Value 1 ', ' Value 2 '), (' Value 3 ', ' Value 4 '); #一次性增加多条数据

Insert INTO table 1 name (column name, column name) Select column name, column name from table 2; #将表2中的数据插入表1

Eg:insert to TB1 (name,age) select Class,people from TB2; #将tb2中class和people列中的数据插入到tb1中的name和age列

Note: When inserting data, be aware that the data types of the corresponding two columns must be consistent

Insert into table name (column name, column name) Select column name, column name the name of the table in which the from data is located the where condition; #将表2中符合要求的数据插入表1

Eg:insert into TB1 (name,age) Select Class,people from TB2 where id>2; #将tb2中id大于2的class和people列中的数据插入到tb1中的name和age列

B. by deleting

Delete from table name where condition; #删除满足条件的数据行

Delete from table name where Condition 1 and condition 2; #删除满足条件1和条件2的数据行

Delete from table name where Condition 1 or condition 2; #删除满足条件1或条件2的数据行

C, change

Update table name set column name = value; #将该表中指定列的值全部改为指定值

Update table name set column name = value where condition; #将该表中满足条件的指定列的值全部改为指定值

D, survey

SELECT * from table name; #查看该表内所有数据

Select Column Name 1, column name 2 from table name, #查看该表中列名1和列名2的数据, display is displayed in the order of column name 1 and column name 2, rather than the order of the build table

Select Column Name 1, column name 2 from table name where condition, #查看该表中满足条件的列名1和列名2的数据, display is displayed in the order of column name 1 and column name 2, rather than the order in which the table is built

E, other 1, conditions

SELECT * FROM table name where condition; #查看该表满足条件的所有数据

SELECT * FROM table name where Condition 1 and condition 2; #查看该表同时满足条件和条件2的所有数据

SELECT * FROM table name where Condition 1 or condition 2; #查看该表满足条件或条件2的所有数据

SELECT * FROM table name where column name between value 1 and value 2; #查看该表中指定列的值在值1和值2之间的所有数据

SELECT * FROM table name where ID in (1,4,5); #查看该表中id是1, all values of 4,5

SELECT * FROM table name where ID not in (1,4,5); #查看该表中id不是1, all values of 4,5

SELECT * FROM table name where ID in (select Nid from Table 2); #查看该表中id是表2中nid的所有值

2. Wildcard characters

SELECT * from table name where user like ' sdf% '; #查看该表中user列中以sdf开头的所有值 (Multi-string)

SELECT * from table name where user like ' sdf_ '; #查看该表中user列中以sdf开头的所有值 (A string)

3. Restrictions

SELECT * FROM table name limit 5; #前5行

SELECT * FROM table name limit 6,5; #从第6行开始的5行 (not recommended)

SELECT * FROM table name limit 5 offset 6; #从第6行开始的5行 (recommended)

4. Sorting

SELECT * FROM table name order by column name ASC; #根据列从小到大排列

SELECT * FROM table name order by column name Desc; #根据列从大到小排列

SELECT * FROM table name order BY column 1 desc, column 2 asc; #根据列1从大到小排列, if same, rank by column 2 from small to large

5. Grouping

Merges data into one piece of data with the same values in a column.

SELECT * FROM table name Group by column name; #以指定列将该表数据进行分组

Use aggregate functions:

Select Age, Max (part), Min (part), SUM (part), Count (part) from TB3 group by age; #以age列分组, Max (part) displays the largest value in the part column in the group, and Min (part) displays the smallest value in the group in the section, and sum (part) shows the sums in the group that are added together Count (part) shows that the group is a grouping of several data merges,

Select Age, Max (part) as A,min (part) as B from Tb3 group by age; #以age列分组, a displays the maximum value in the part column in the group, and B displays the minimum value in the part column in the group.

Select Age, Count (part) from TB3 group by age have Count (part) >2; #显示出count (part) >2 data with age grouping criteria

6. Combination

Select column 1 from table 1 Union Select column 2 from table 2; #将表2中列2的数据在表1中列1的数据后面显示, the default is to do the redo, if you do not want to go to the weight, then add all after the Union

7. Even table

SELECT * FROM table 1, table 2 where table 1. column 1= table 2. column 2; #将表2中的数据根据表1. column 1= table 2. Column 2 matches the corresponding display on the right side of table 1, with table 1 as the Datum

SELECT * FROM table 1 left JOIN table 2 on table 1. Column 1= table 2. column 2; #将表2中的数据根据表1. column 1= table 2. Column 2 matches the corresponding display on the right side of table 1, with table 1 as the Datum

SELECT * FROM table 1 inner JOIN table 2 on table 1. Column 1= table 2. column 2; #两张表的数据根据表1. column 1= table 2. Column 2, after matching the display, clears the row containing null, actually filtering the results of the LEFT join

MySQL Common commands