MySQL common operation instructions and built-in functions, welcome to add!

1. Build library, select Library

create database class1824; 
use python1804;

1. Delete Library

drop database class1824;

3. Create and delete tables

create table user(userid int(11),username varchar(32),password char(32)); 
drop table user;

1. Change name

alter table user  rename users;

1. Table field rename

alter table user change userid id int(11);

1. Modify field type and location

alter table user modify username char(32) first;

1. Add delete table field

alter table user add column password char(64) after username;
alter table user drop column sex;

1. Create creates an index. Create cannot create a primary key index. Note the difference between create and alter

create index in_id on t1(id);

1. Alter creates an index. Create cannot create a primary key index. Note the difference with alter

Alter table users add unique; 
#Fulltext (field name); primary key (field name); index (field name)

1. Delete index

alter table users drop index username on t1;

1. Delete primary key index, remove increment first

alter table users modify id int(11) unsigned not null;
alter table users drop primary key; 

1. Insert data method 1, all fields in the same order

insert into users values(1,"haha","adfdsfadsf12"),(2,"xixi","afdsf123321");

1. Insert data method 2, set the incrementing field not to operate

insert into users(id,username,password) values(3,"hehe","12333adf123");

1. Replace column data

update scores set nu=replace(nu,1,2);

1. Duplicate removal

select distinct balance from money;

1. Sort, desc descending, asc ascending.

select * from money where age>30 order by balance desc,age asc limit 2 ,5;
asc Ascending order;

17. group by

select count(province),province from money group by province;

18. Condition screening group by

select count(province) as result,province from money group by province with rollup;

19. Condition screening group by

select count(province) as result,province from money group by province having result>1;

20. Table joint query

select user.username as 'User name',order_goods.oid,order_goods.name as "Trade name" from user,order_goods where user.uid=order_goods.uid;

21. Table joint query 2

select user.username as 'user',order_goods.name as "commodity" from user inner join order_goods on user.uid=order_goods.uid;

22.left join syntax

select * from user left join order_goods on user.uid=order_goods.uid; 

23. Attention and 22

select * from user where uid in(select uid from order_goods);
select uid from user union all select uid from order_goods;

24. update data

update user set username="Ha-ha",password="123456" where uid=8;

25. Update two tables at the same time

update money m,users u set m.balance=m.balance*u.id,u.password=m.balance where m.id=u.id;

26.delete from, add ID from the original basis, for example, 1, 2, 3, delete id=2, and id=3 does not change! Note the difference between truncate and 27

delete from money where id>9; 
delete from  Table name; 

1. id starts from 1 after truncate clears the table

truncate table users; 

28. Database remote connection, pay attention to the use of the symbol *

grant Jurisdiction on Library name.Table name to 'User name'@'ip address' identified by Password;

grant all on *.* to 'root'@'localhost' identified by '123456' with grant option; 
grant select,insert on class1824.* to 'jinxingping'@'localhost' identified by '123321';
flush privileges;
service mysql restart；

29. Delete the specified permission

revoke insert on class1824.* from 'jinxingping'@'localhost';  

30. Copy table structure and data

create table user1 like users；
insert into user1 select * from users；

31. Table view

create view v_t1 as select * from t1 where id>2 and id<8; show create/drop view v_t1;

1. mysql common built-in functions

mysql For display content select  
//String function  
mysql> select concat("hello","world") as nihao; #Concatenate string
select lcase("MYSQL");#Turn lowercase
select ucase("mysql"); #Turn capitalization
mysql> select length("haha"); Length of string
mysql> select ltrim("    haha"); Remove left space
+-------------------+
| ltrim("    haha") |
+-------------------+
| haha              |
+-------------------+
1 row in set (0.00 sec)

mysql> select rtrim("    haha      ");Remove right space
mysql> select repeat("Important things are to be repeated for 3 times",6);
select replace("java Is the best language in the world","java","python");
mysql> select substr("python is so good",2,7); 7 from the second character  mysql Starting from 1 
mysql> select concat(space(20),'haha'); #Generate 20 spaces   
//Mathematical function  
mysql> select ceiling(123.1);
mysql> select floor(123.1);
mysql> select rand(); Find random number 0-1 Decimal between 
mysql> select bin(10);


//Date function
mysql> select curdate(); Return date 2018-7-25
mysql> select curtime(); 11:21:33
mysql> select now(); date+Time 2018-07-25 11:22:14  
mysql> select week(now()); Return 29 indicates the current week   
mysql> select year(now()); Return to 2018 
mysql> select unix_timestamp(now()); Timestamp of current time 
mysql> select from_unixtime(1532489069);
+---------------------------+
| from_unixtime(1532489069) |
+---------------------------+
| 2018-07-25 11:24:29       |
+---------------------------+ Format the current timestamp as a date