MYSQL 基础语句

MYSQL 语句

一，DDL语句： CREATE; DESC; DROP; ALTER; SHOW

1，CREATE

CREATE DATABASE mydata;

CREATE TABLE table1(id int(2),name varchar(10),number int(2));

2,DESC

DESC table1;

3,DROP

DROP TABLE table1;

DROP DATABASE mydata;

4,ALTER (MODIFY; CHANGE; ADD; FIRST; AFTER; RENAME; DROP)

ALTER TABLE table1 MODIFY id int(5);

ALTER TABLE table1 CHANGE id myid int(10);

ALTER TABLE table1 ADD age int(2);

ALTER TABLE table1 ADD birth date FIRST;

ALTER TABLE table1 MODIFY myid int(10) FIRST;

ALTER TABLE table1 MODIFY age int(2) AFTER birth ;

ALTER TABLE table1 RENAME tb;

ALTER TABLE tb DROP birth;

5,SHOW

SHOW DATABASES;

SHOW TABLES;

二,DML语句:INSERT; UPDATE; DELETE; SELECT

1,INSERT

INSERT INTO tb(myid,age,name,number) VALUES(1,15,'wang',20);

2,UPDATE

UPDATE tb SET age=18 WHERE name='wang';

3,DELETE

DELETE FROM tb WHERE myid=4;

4,SELECT (ORDER BY; COUNT; SUM; MAX; MIN)

SELECT * FROM tb;

SELECT myid,age FROM tb;

SELECT * FROM tb ORDER BY number;

SELECT * FROM tb ORDER BY number DESC;

SELECT * FROM tb ORDER BY age,number DESC;

SELECT * FROM tb ORDER BY number LIMIT 2;

SELECT * FROM tb ORDER BY number LIMIT 1,3; (注意，不包含第一行)

SELECT COUNT(*) FROM tb;

SELECT SUM(myid),MAX(age),MIN(NUMBER) FROM tb;

三,DCL语句

1，GRANT

GRANT ALL ON mydata.* TO 'lb'@'%' IDENTIFIED BY 'lb123';

2，REVOKE

REVOKE DROP ON mydata.* FROM 'lb'@'%';