MySQL Basic operation

1. Database operation

Go to command line: mysql-uroot-p123456

View database: show databases;

View Database creation statement: show create DATABASE test;

Creating the database: Create DB test CharSet UTF8MB4;

Select database: Use test;

Delete database: Drop DB test;

Backup specified database: mysqldump-uroot-p Test >test.sql Note: Because the mysql5.6 version does not support the command line interface to enter the password, so do not enter the password, enter the password after entering.

Back up all databases: Mysqldump-uroot-p-A >test.sql

Restore the specified database: Mysql-uroot-p test<test.sql

Recover all databases: mysql-uroot-p-A <test.sql

2. Table Operation:

Table constraints:

Self-increment: aotu_increment

Primary key: Primary key

Non-empty: NOT NULL

Default value: ' XX '

Unique: Unique

Specify character set: CharSet UTF8MB4

View table:

View table: Show tables;

View CREATE TABLE statements: show create TABLE user;

View table structure: DESC user;

To create a table:

Create TableUser (IDintAuto_incrementPrimary Key, #ID自增并设置为主键 namevarchar(Ten) not NULL, #姓名不允许为空 sexvarchar(5)default 1, #性别默认1 phonebigint  not NULL Unique, #电话不允许为空并且唯一 create_timedatetime defaultNow (), #创建时间默认当前时间 addrvarchar( -));

To delete a table:

Delete tables: drop table user;

To modify a table:

Modify table name: ALTER TABLE user rename User1;

Modify the field type and rename: Alter TABLE user change name names varchar (15);

Modify field type: ALTER TABLE user modify name varchar (15);

Add field at specified location: ALTER TABLE user add password varchar (a) not NULL after name;

3. Data manipulation

　Add Data:

Table new data: INSERT into user values (' ', ' Zhang San ', ' Male ', ' 1234567890 ', now (), ' Haidian District, Beijing ');

Add data to the specified field in the table: INSERT into User (Name,phone,create_time) VALUES (' Zhang San ', ' 12348567890 ', now ()); Note: If the field settings are not empty, you must add

Delete data:

Delete data from table: delete from user; Note: The self-growth ID will not be emptied and will continue to grow from the original ID.

Empty table: Truncate user;

Delete the data specified in the table: delete from user where id=1;

Change data:

Change all data in table: Update user set sex= ' man ';

Change the data specified in the table: Update user set sex= ' male ' where Name= ' Zhang San ';

Change multiple fields in a table: Update user set sex= ' man ', Addr= ' Chaoyang District, Beijing ' where Name= ' Zhang San ';

Changes based on the original data: Update user set phone=phone+1 where name= ' Zhang San ';

Query data:

Query the first 5 data: SELECT * from user limit 5;

Query 3rd to 6th data: SELECT * from User limit 2, 4; Note: Starting from the first few queries (subscript starting from 0), query how many bars

Query the specified field: select Name,sex,phone from User;

Single-Table query: SELECT * from user where name= ' Zhang San ' and sex= ' male '; Note: and means that multiple conditions must satisfy both

SELECT * from user where name= ' Zhang San ' or sex= ' male '; Note: or indicates that one of the conditions is satisfied

SELECT * from user where sex! = ' male '; #查询不等于男生的信息, you can also use <>

SELECT * from user where name like ' sheet% '; #查询姓张的用户

SELECT * from the user where name like ' Zhang _ '; #查询姓张并且姓名为2个字的用户

SELECT * from the user where name in (' Zhang San ', ' John Doe ', ' Harry '); Information on #查询姓名为张三, John Doe, Harry

SELECT * from user where phone between 13700000000 and 13712345678; #查询手机号在13700000000和13712345678之间的用户

SELECT * from the user order by create_time Desc; #查询按照用户创建时间倒序显示, default ascending ASC

SELECT * from user where addr= "or addr is null; #查询地址为空或为null的用户

Select distinct name from user; #不显示重复的姓名

Select COUNT (*) from user where sex= ' female '; #统计女生有多少人

Select Max (age), min (age), AVG (age), sum (age) from user where sex= ' man '; #查找男生年龄最大, minimum, average, sum

Select *,count (Sex) from the user GROUP by sex has sex= ' woman '; #按照性别分组, and show how many girls there are

Multi-Table query:

SELECT * from user U,user_group g where u.id=g.id #查询两张表共有的数据

SELECT * from the user u inner join user_group g on u.id=g.id; #查询两张表共有的数据

SELECT * from the user u left joins User_group G on u.id=g.id; #左边所有的数据都查出来, if there's a match on the right, find it.

SELECT * from the user U right joins User_group G on u.id=g.id; #右边所有的数据都查出来, if there's a match on the left, find it.

SELECT * from user u left joins User_group G on u.id=g.id
Union
SELECT * from the user U right joins User_group G on u.id=g.id; #左边和右边匹配的数据全部查出来并去重 (union All does not go heavy), equivalent to Oracle's full connectivity

SELECT * FROM (select Id,name,sex from user where sex= ' female ') user; #把查询结果作为一张表查询

SELECT * from the user where ID in (the Select ID from user_group where g_name= ' computer '); #子查询, querying the computer for user information

4. User Management

Add Users:

Insert into User (User,host,password) VALUES (' xiaoxitest ', '% ', password (' 123456 ')); #添加xiaoxitest用户并允许远程计算机登录, the password is: 123456

change User:

Update user set Password=password ("654321") where user= ' xiaoxitest '; #更改用户密码

Update user set user= ' xiaoxi ' where user= ' xiaoxitest '; #更改用户名xiaoxitest为xiaoxi

To delete a user:

Delete from user where user= ' Xiaoxi '; #删除xiaoxi用户

5. Rights Management

User authorization:

Authorization format: Grant permissions on database. * To username @ Login host identified by "password" with GRANT option;

Grant all on * * to ' xiaoxi ' @ '% ' identified by ' 123456 ' with GRANT option; #表示为xiaoxi用户添加所有数据库所有权限, and can give permission to others.

Grant all on test.* to ' Xiaoxi ' @ ' percent ' identified by ' 123456 '; #表示为xiaoxi用户添加test数据库所有权限

Grant SELECT On * * to ' xiaoxi ' @ ' percent ' identified by ' 123456 '; #表示为xiaoxi用户添加所有数据库查询权限

To cancel the authorization:

Revoke Select On * * from ' xiaoxi ' @ '% '; #表示为xiaoxi用户取消所有数据库查询权限

Revoke all on * * from ' xiaoxi ' @ '% '; #表示为xiaoxi用户取消所有数据库所有权限

Refresh permissions:

Flush privileges;

　　　　　　　　

MySQL Basic operation