mysql(mariadb)语句

HELP CREATE  查询帮助
DDL:数据定义语言  
CREATE USER 'dyl'@'172.16.52.60' IDENTIFIED BY 'dyl@1234'; 创建用户名


CREATE SCHEMA IF NOT EXISTS testdb ;
CREATE TABLE students(id INT UNSIGNED NOT NULL,name CHAR(30) NOT NULL,age TINYINT UNSIGNED)
CREATE INDEX test_index ON students (name,id); 创建索引
 SHOW INDEX from students;  查看索引
DESC TABLE_NAME;
ALTER TABLE students ADD  gender ENUM('male','female') AFTER name ; 添加字段，列
 ALTER TABLE students CHANGE name stu_name VARCHAR(30); 修改列表名
SHOW ENGINES; 显示数据引擎类型
SELECT DATABASE(); 显示正在使用的数据库
# mysql -e ‘SHOW DATABASES;’
\u DB_NAME; 设定默认数据库
\d CHAR; 设定语句结束符，如果没有结束符也没关系可以使用 \g 结束，而\G也是语句结束标记，但是竖排显示
\s  显示当前数据的属性状态，后面不用跟分号；
\c 表示取消命令
SHOW CHARACTER SET; 显示支持的字符集类型
SHOW COLLATION; 查看所有的排序规则


DML:数据操控语言  INSERT DELETE  SELECT UPDATE
INSERT INTO students VALUES(1,'xiaoming','male');  向列表插入值，字符型一定要加引号，数字一定不能加引号
SELECT id AS stu_id FROM students;  显示时可以用别名
SELECT id AS stu_id FROM students WHERE id=1 ;  如果字段是CHAR类型的话，是不区分大小写的
    =,>,<,>=,!=
    AND,OR,NOT或 !, BETWEEN ... AND ...，LIKE '%_'， RLIKE(正则表达式)，ORDER BY
SELECT * FROM students WHERE stu_name LIKE '%Ming';
SELECT * FROM students WHERE stu_name RLIKE 'ng$';
SELECT * FROM students WHERE stu_name IS NULL;
SELECT * FROM students WHERE stu_name IS NOT NULL;
SELECT * FROM students ORDER BY age;
SELECT * FROM students ORDER BY id ASC;  升序排序
SELECT * FROM students ORDER BY id DESC;  降序排序


DELETE 是以行为单位的
DELETE  FROM  students  ORDER BY id DESC LIMIT 1;  删除年龄最大的，1个 LIMIT限制个数


UPDATE students SET id=id+3 WHERE stu_name NOT LIKE 'dawang';


SHOW GRANTS FOR 'dyl'@'172.16.52.60';  查看用户获得的授权
SHOW GRANTS FOR CURRENT_USER;  查看当前用户获得的权限；
GRANT ALL ON  students.students TO 'dyl'@'172.10.%，%' ; 授权,GRANT 和 REVOKE 处理完后不需要刷新权限，而其他授权语句要FLUSH PRIVILEGES;
GRANT SELECT ON testdb.* TO 'dyl'@'172.16.%.%' IDENTIFIED BY 'dyl@1234';
REVOKE SELECT ON mydb.students FROM 'dyl'@'172.16.%.%'; 收回权限


加固mysql :  mysql_secure_installation