SpringBoot e-commerce project mall (20k+star) address: https://github.com/macrozheng/mall

abstract

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)

Permission-related

• 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')

Other

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.