Developers must have Mysql commands

Keywords: Java Database MySQL SpringBoot github

SpringBoot e-commerce project mall (20k+star) address:


Developers must have common Mysql commands, including data definition statements, data manipulation statements and data control statements, based on Mysql 5.7.

Data Definition Statement (DDL)

Database operation

  • Log in to the database:
mysql -uroot -proot
  • Create a database:
create database test
  • View all databases:
show databases

  • Select the database and use:
use test
  • View all data tables:
show tables
  • Delete the database:
drop database test

Table operation

  • Create tables:
create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2))  
create table dept(deptno int(2),deptname varchar(10))

  • View table definition:
desc emp

  • View Table Definitions (Details):
show create table emp \G

  • Delete the table:
drop table emp
  • Modify table fields:
alter table emp modify ename varchar(20)
  • Add table fields:
alter table emp add column age int(3)
  • Delete table fields:
alter table emp drop column age
  • Field rename;
alter table emp change age age1 int(4)
  • Modify the table name:
alter table emp rename emp1

Data Manipulation Statement (DML)

insert record

  • Specifies name insertion:
insert into emp (ename,hiredate,sal,deptno) values ('zhangsan','2018-01-01','2000',1)
  • Insert without specifying a name:
insert into emp values ('lisi','2018-01-01','2000',1)
  • Batch insertion data:
insert into dept values(1,'dept1'),(2,'dept2')

Modification of records

update emp set sal='4000',deptno=2 where ename='zhangsan'

Delete records

delete from emp where ename='zhangsan'

Query Record

  • Query all records:
select * from emp
  • Query non-duplicate records:
select distinct deptno from emp
  • Conditional Query:
select * from emp where deptno=1 and sal<3000
  • Sort and limit:
select * from emp order by deptno desc limit 2
  • Page-by-page queries (queries start with 10 entries from entry 0):
select * from emp order by deptno desc limit 0,10
  • Aggregation (department number with more than 1 inquiry department):
select deptno,count(1) from emp group by deptno having count(1) > 1
  • Connection query:
select * from emp e left join dept d on e.deptno=d.deptno
  • Subqueries:
select * from emp where deptno in (select deptno from dept)
  • Joint Records:
select deptno from emp union select deptno from dept

Data Control Statement (DCL)


  • Grant operation privileges (grant select and insert privileges to test users for all tables in the test database):
grant select,insert on test.* to 'test'@'localhost' identified by '123'
  • View account permissions:
show grants for 'test'@'localhost'

  • Recovery of operation authority:
revoke insert on test.* from 'test'@'localhost'

  • Grant all permissions to all databases:
grant all privileges on *.* to 'test'@'localhost'
  • Grant all permissions to all databases (including grant):
grant all privileges on *.* to 'test'@'localhost' with grant option
  • Grant SUPER PROCESS FILE privileges (system privileges cannot specify databases):
grant super,process,file on *.* to 'test'@'localhost'
  • Only login permissions are granted:
grant usage on *.* to 'test'@'localhost'

Account Relevance

  • Delete account:
drop user 'test'@'localhost'
  • Modify your password:
set password = password('123')
  • Administrators modify passwords for others:
set password for 'test'@'localhost' = password('123')


Character set correlation

  • View Character Set:
show variables like 'character%'

  • Specify the character set when creating the database:
create database test2 character set utf8

Time zone correlation

  • View the current time zone (UTC is the world unified time, China is UTC+8):
show variables like "%time_zone%"

  • Modify mysql's global time zone to Beijing time, that is, our Eastern Eighth Zone:
set global time_zone = '+8:00';
  • Modify the current session time zone:
set time_zone = '+8:00'

  • Effective immediately:
flush privileges

Public Number

mall project In the whole series of learning courses, we pay attention to the first time acquisition of the public number.

Posted by basil on Sat, 07 Sep 2019 00:02:20 -0700