mysql简单_mysql的简单操作

创建数据库并设定字符集：

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 endwith; or \g.

Your MariaDB connection id is2Server 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.000006test

aria_log_control localhost.log mysql-bin.000003 mysql-bin.index

ibdata1 mysql mysql-bin.000004mysql.sock

ib_logfile0 mysql-bin.000001 mysql-bin.000005performance_schema

[root@mysql localhost]#mysqlbinlog mysql-bin.000006MariaDB [(none)]> show binlog events in 'mysql-bin.000004';

#开启二进制日志：

MariaDB [(none)]> show variables like 'sql_log_bin';