Database MySql Basics

1. Function classification? What functions are often used?

lower
upper
substr
length
Trim (remove the leading and trailing spaces and do not remove the spaces in the middle)
str_to_date(%Y-%m-%d)
date_format
Format (decimal)
round
Random number
ifnull (if empty, replace with 0)
Aggregate function / grouping function
Grouping functions automatically ignore null values
count
sum
avg
min
max

2. Conditions for group query?

If order by is used, order by must be placed after group by.
In sql statements, select statements can only be followed by grouping functions + fields participating in grouping.
If you want to filter the grouped data again, you need to use the having clause.

3. How to use limit?

select * from emp limit m,n;

4.mysql common data types?

char: fixed length string, suitable for primary key or foreign key
varchar: variable length string
double/float
int/bigint
date

5. How to delete a table structure

 alter table Table name add Field name data type(length) --Add field
 alter table Table name modify Field name data type(length) --Modify field length
 alter table Table name change The original field name is now the field name data type(length) --Modify field name
 alter table Table name drop Field name --Delete field

6. How to turn on MySQL service and turn off My service

Open service:

service mysqld start

/init.d/mysqld start

safe_mysql &

Shut down service:

service mysqld stop

/etc/init.d/mysqld stop

mysqladmin -uroot -p123456 shutdown

7. Check whether the port is running

lsof -i:3306

netstat -tunlp|grep 3306

ss -tulnp|grep 3306

8. How to set or modify the password for MySQL.

Method 1

mysqladmin -u root -p123456 password 'abc123'    #More commonly used

Method 2( sql Statement modification)

update mysql.user set password=password(123456) where user='root' and host='localhost';

flush privileges;

Method 3( sql Statement modification)

set password=password('abc123');

9. How to log in to MySQL database.

Single instance login

mysql -uroot -p123456

Multi instance login

mysql -uroot -p123456 -S /data/3306/mysql.sock

10. View the character set of the current database

mysql> show variables like "%charac%";

11. How to view the current database version

# mysql -V
mysql> select version();

12. How to view the currently logged in user.

mysql> select user();

13. Check the tables in T1 database

mysql> use T1;

mysql> show tables;

14. Create the database oldboy of GBK character set and view the complete statements of the built database

mysql> create database oldboy default character set gbk;
mysql> show create database oldboy;

15. Create the user oldboy so that it can manage the database oldboy

mysql> grant select,update,insert,delete,alter on oldboy.* to oldboy@'localhost' identified by '123456';

16. Check the permissions of the created user oldboy

mysql> show grants for oldboy@'localhost';

17. Check the users in the current database

mysql> select user,host from mysql.user;

18. How to access oldboy database

mysql> use oldboy();

19. Please write a script: create an innodb GBK table test, field id int(4) and name varchar(16)

mysql> create table test (id int(4),name varchar(16)) engine=InnoDB default charset=gbk;

20. View the table structure and SQL statements of the table structure

mysql> desc test;
mysql> show create table test\G

21. Please use the script to query a piece of data: insert a piece of data "1,oldboy"

mysql> insert into test (id,name) values (1,'oldboy');

22. Insert two rows of data "2, old boy", "3, Oldboy" in batch

mysql> insert into test (id,name) values (2,'Old boy'),(3,'oldboyedu');

23. Query the record named oldboy

mysql> select * from test where name='oldboy';

24. Change the name oldboy with data id equal to 1 to oldgirl

mysql> update test set name='oldgirl' where id=1;

25. Insert the age field before the field name, type tinyint(2)

mysql> alter table test add age tinyint(2) after id;

26. Do not exit the database and complete the backup of oldboy database

mysql> system mysqldump -uroot -p123456 -B -x -F --events oldboy >/opt/bak.sql

27. Delete all data in the test table and view it

mysql> delete from test;