创建数据库并设定字符集：
CREATE DATABASE hidb CHARACTER SET ‘utf8’;
使用数据库：
use hidb;
删除数据库：
DROP DATABASE hidb;
SHOW DATABASES LIKE ‘%db’
创建数据库表：
CREATE TABLE TBL2 (id SMALLINT UNSIGND NOT NULL AUTO INCREMENT UNIQUE KEY,name
HELP DESC
DESC tbl2
增加数据库表的字段：
ALTER TABLE tbl3 ADD gender ENUM(‘F’,’M’) after id;
修改字段：
ALTER TABLE tbl3 CHANGE id stuid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; 修改字段名
ALTER TABLE students MODIFIY birthdate DATE; 修改字段的数值类型
删除字段：
ALTER TABLE tbl3 DROP gender;
添加索引：
ALTER TABLE tbl3 ADD INDEX name(name);
CREATE INDEX user ON tbl8(User);
CREATE UNIQUE INDEX host ON tbl8(Host,User);
查看索引：
SHOW INDEXES FROM students;
删除索引：
ALTER TABLE tbl3 DROP INDEX id;
DROP INDEX index_age ON students;
查看select语句执行的细节,即评估索引：
EXPLAIN SELECT * FROM students；
查询索引：
SHOW INDEXES FROM tbl3;
查询表：
SHOW TABLES;
查询表结构：
DESC students;
查询数据库：
SHOW DATABASES;
复制表结构；
CREATE TABLE tbl7 LIKE mysql.user
CREATE TABLE tbl8 SELECT host,user,password FROM mysql.user;
表中插入数据：
INSERT INTO students VALUES (1，’Yang Guo’,’M’,’899-04-06’,3);
INSERT INTO students(name,gender) VALUES(‘Guo Jing’,’M’),(‘Ding Dian’,’M’);
REPLACE INTO students VALUES (); 有此行则代替原有行，没有就插入一行数据；
SELECT * FROM students WHERE classid IS |NOT IS NULL;
SELECT * FROM students WHERE classid IN (1,2,3);
SELECT * FROM students WHERE name LIKE ‘D%’;
SELECT * FROM students WHERE NOT name LIKE ‘D%’; 或许是name NOT LIKE
SELECT * FROM students WHERE name RLIKE ‘^D.*$’; D开头的名字
SELECT * FROM students ORDER BY name DESC; 降序排序
删除100行：
DELETE FROM students ORDER BY age DESC LIMIT 100;
创建用户：
CREATE USER ‘tom’@’172.16.%.%’ IDENTIFIED BY ‘magedu’; 创建tom可以在172.16的网络登录；
修改用户：
RENAME USER 'test'@'localhost' TO 'testuser'@'%';
删除用户：
DROP USER ‘tom’@’172.16.%.%’;
查看系统用户信息：
SELECT user,host FROM user;
查看表结构：desc user;
给用户授权，也能创建用户：
‘tom’@’172.16.%.%’表示：允许tom用哪些IP的客户端登录
GRANT ALL ON hidb.* TO ‘tom’@’172.16.%.%’ IDENTIFIED BY ‘magedu’;
回收权限： mydb数据库的tbl1表
REVOKE DELETE,UPDATE ON mydb.tbl1 FROM ‘tom’@172.16.%.%’;
查看权限：
SHOW GRANTS;
SHOW GRANTS FOR ‘tom’@’172.16.%.%’;
刷新授权表:
FLUSH PRIVILEGES;
#查看关于缓存的变量：
#查询缓存全局变量 MariaDB [(none)]> show global variables like 'query_cache%'; #设置全局的缓存变量的值： MariaDB [(none)]> set global query_cache_limit=1024*1024*2; #统计状态数据： MariaDB [(none)]> show global status like 'Qcache%'; #查询日志文件变量： MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'general_log%'; #开启查询日志； MariaDB [(none)]> SET @@GLOBAL.GENERAL_LOG=ON; [root@mariadb localhost]#cd /var/lib/mysql/
MySQL的-e 使用：
]# mysql –utom –h172.16.0.67 –pmagedu ‘INSERT INTO hidb.students (name,gender,age) VALUES (‘tom’,’M’,18);
#修改密码：
update mysql.user set password=PASSWORD('123456') WHERE user='root';
SET PASSWORD FOR 'test'@'%' = PASSWORD('123456');
#当管理员忘记密码时：
#centos7 [root@~ localhost]#vim /usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking [root@~ localhost]#systemctl daemon-reload [root@~ localhost]#systemctl start mariadb.service [root@~ localhost]#mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 2 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. #在此处修改root的密码： MariaDB [(none)]>
#CentOS6：
[root@~ localhost]#yum -y install mysql-server mysql mysql-devel [root@~ localhost]#service mysqld start [root@~ localhost]#mysql_secure_installation [root@~ localhost]#mysql -p #忘记密码，无法登陆： [root@~ localhost]#service mysqld stop [root@~ localhost]#vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql skip_grant_tables skip_networking [root@~ localhost]#service mysqld start [root@~ localhost]#mysql mysql> update mysql.user set password=PASSWORD('123456') where user='root'; mysql> flush privileges;
#
#锁表： LOCK TABLES hellodb.students WRITE; #解锁： UNLOCK TABLES; #查询二进制文件的日志 SHOW MASTER LOGS; #查询当前使用的二进制日志: SHOW MASTER STATUS; #查看二进制文件： [root@mysql localhost]#pwd /var/lib/mysql [root@mysql localhost]#ls aria_log.00000001 ib_logfile1 mysql-bin.000002 mysql-bin.000006 test aria_log_control localhost.log mysql-bin.000003 mysql-bin.index ibdata1 mysql mysql-bin.000004 mysql.sock ib_logfile0 mysql-bin.000001 mysql-bin.000005 performance_schema [root@mysql localhost]#mysqlbinlog mysql-bin.000006 MariaDB [(none)]> show binlog events in 'mysql-bin.000004'; #开启二进制日志： MariaDB [(none)]> show variables like 'sql_log_bin';