MYSQL SQL 语句

基础sql语句

库管理

SHOW DATABASES;
CREATE DATABASE wordpress CHARSET utf8mb4;
SHOW CREATE DATABASE wordpress;
ALTER DATABASE wordpress CHARSET utf8;
SHOW CREATE DATABASE wordpress;
DROP DATABASE wordpress;

表管理

USE wordpress;
CREATE TABLE qiaoning (
sid INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '测试专用';

SHOW TABLES;
SHOW CREATE TABLE qiaoning;
DESC qiaoning;
DROP TABLE qiaoning;
ALTER TABLE qiaoning RENAME wangtao;
ALTER TABLE qiaoning CHARSET utf8;
ALTER TABLE qiaoning ADD sname VARCHAR(11) NOT NULL COMMENT '姓名';
ALTER TABLE qiaoning ADD sage CHAR(11) NOT NULL  COMMENT '性别' AFTER sid;
ALTER TABLE qiaoning MODIFY sage VARCHAR(11) NOT NULL COMMENT '性别';
ALTER TABLE qiaoning CHANGE sage qq TINYINT NOT NULL COMMENT 'qq';
ALTER TABLE qiaoning DROP qq;
CREATE TABLE wangtao LIKE qiaoning;

INSERT INTO qiaoning (sid,sname) VALUES (1,'a');
INSERT INTO qiaoning VALUES (2,'b'),(3,'c'),(4,'d');
UPDATE qiaoning SET sname='qiaoning' WHERE sname='a'
DELETE FROM qiaoning WHERE sname LIKE '%qiaoning%';
SELECT * FROM qiaoning;
SELECT * FROM wordpress.qiaoning;
SELECT sid,sname FROM wordpress.qiaoning;
SELECT sid FROM qiaoning;
SELECT  * FROM qiaoning WHERE sname='c';
SELECT * FROM qiaoning WHERE sname>=2;
SELECT  * FROM qiaoning WHERE sname LIKE '%c%';

用户管理：

CREATE USER qiaoning@'localhost' IDENTIFIED BY '123';
CREATE USER qiaoning@'10.0.0.%' IDENTIFIED BY '123';
GRANT ALL ON *.* TO qiaoning@'10.0.0.%';
DROP USER qiaoning@'10.0.0.%';
FLUSH PRIVILEGES;
SELECT USER,HOST,authentication_string,PLUGIN FROM mysql.user;