Abstract

Data definition statement, data manipulation statement, and data control statement, based on Mysql5.7

Data Definition Statement (DDL)

Database operations

  • Logging in to the database:
mysql -uroot -proot
Copy the code
  • Create database:
create database test
Copy the code
  • View all databases:
show databases
Copy the code

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

Table operation

  • Create a table:
create table emp(ename varchar(10),hiredate date,sal decimal(10.2),deptno int(2))  
Copy the code
create table dept(deptno int(2),deptname varchar(10))
Copy the code

  • View the table definition:
desc emp
Copy the code

  • View table definitions (details) :
show create table emp \G
Copy the code

  • Delete table:
drop table emp
Copy the code
  • Alter table table_name;
alter table emp modify ename varchar(20)
Copy the code
  • Add table field:
alter table emp add column age int(3)
Copy the code
  • Alter table drop table drop table drop
alter table emp drop column age
Copy the code
  • Field name changed;
alter table emp change age age1 int(4)
Copy the code
  • Alter table name:
alter table emp rename emp1
Copy the code

Data manipulation Statement (DML)

Insert records

  • Specify name to insert:
insert into emp (ename,hiredate,sal,deptno) values ('zhangsan'.'2018-01-01'.'2000'.1)
Copy the code
  • Insert without specifying name:
insert into emp values ('lisi'.'2018-01-01'.'2000'.1)
Copy the code
  • Batch insert data:
insert into dept values(1.'dept1'), (2.'dept2')
Copy the code

Modify the record

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

Delete records

delete from emp where ename='zhangsan'
Copy the code

Query log

  • Query all records:
select * from emp
Copy the code
  • Query non-duplicate records:
select distinct deptno from emp
Copy the code
  • Conditional query:
select * from emp where deptno=1 and sal<3000
Copy the code
  • Sort and limit:
select * from emp order by deptno desc limit 2
Copy the code
  • Paging query (query 10 entries starting from 0) :
select * from emp order by deptno desc limit 0.10
Copy the code
  • Aggregate (Query the department number where the number of departments is greater than 1) :
select deptno,count(1) from emp group by deptno having count(1) > 1
Copy the code
  • Connection query:
select * from emp e left join dept d on e.deptno=d.deptno
Copy the code
  • The subquery:
select * from emp where deptno in (select deptno from dept)
Copy the code
  • Record union:
select deptno from emp union select deptno from dept
Copy the code

Data Control Statement (DCL)

Permissions related

  • Grant select and INSERT privileges on all tables in the test database to the test user:
grant select.insert on test.* to 'test'@'localhost' identified by '123'
Copy the code
  • View account permissions:
show grants for 'test'@'localhost'
Copy the code

  • Recall operation permission:
revoke insert on test.* from 'test'@'localhost'
Copy the code

  • Grant all permissions to all databases:
grant all privileges on*. *to 'test'@'localhost'
Copy the code
  • Grant all privileges to all databases (including grant) :
grant all privileges on*. *to 'test'@'localhost' with grant option
Copy the code
  • Grant SUPER PROCESS FILE permission (system permission cannot specify database) :
grant super,process,file on*. *to 'test'@'localhost'
Copy the code
  • Grant only login permissions:
grant usage on*. *to 'test'@'localhost'
Copy the code

Account related

  • Delete an account:
drop user 'test'@'localhost'
Copy the code
  • Change your own password:
set password = password('123')
Copy the code
  • The administrator changes the password of others:
set password for 'test'@'localhost' = password('123')
Copy the code

other

Character set correlation

  • View character set:
show variables like 'character%'
Copy the code

  • SQL > alter database create character set
create database test2 character set utf8
Copy the code

Time zone related

  • View the current time zone (UTC+8 in China) :
show variables like "%time_zone%"
Copy the code

  • Mysql > alter mysql’s global time zone to Beijing time
set global time_zone = From the '+';
Copy the code
  • Change the current session time zone:
set time_zone = From the '+'
Copy the code

  • Effective immediately:
flush privileges
Copy the code

The public,

Mall project full set of learning tutorials serialized, attention to the public number the first time access.