Mysql入门教程--基础命令
Mysql入门教程--基础命令#mysql
-----------------------------------------------------------------------------------------
Welcome to the MySQL monitor.Commands end with ; or \g.
mysql>
// 进入mysql
-----------------------------------------------------------------------------------------
mysql> mysql -u root
-> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
//修改root密码
mysql>show processlist;
//显示了有哪些线程在运行
mysql> show engines;
//看你的mysql现在已提供什么存储引擎
mysql> show variables like '%storage_engine%';
//看你的mysql当前默认的存储引擎
mysql> show charset;
//查看mysql所支持的字符集
mysql> show variables like '%char%';
//查看MySQL数据库服务器和数据库字符集
mysql> show table status from mysql_db like '%tb_name%'
//查看表的字符集
mysql> show full columns from tb_name;
//查看表中所有列的字符集
set character_set_connection=gb2312;
//修改连接层字符集
set character_set_database=gb2312;
//修改当前选中数据库的默认字符集
set character_set_results=gb2312;
//修改查询结果字符集
set character_set_server=gb2312;
//修改默认的内部操作字符集
set character_set_client=gb2312;
//修改客户端来源数据使用的字符集
set character_set_system=gb2312;
//修改系统元数据(字段名等)字符集
set collation_connection=gb2312;
set collation_database=gb2312;
set collation_server=gb2312;
//还有以collation_开头的同上面对应的变量，用来描述字符序
ALTER TABLE tb_name CONVERT TO CHARACTER SET gb2312;
//修改表的字符集
alter table tb_name modify column tb_column varchar(30) character set gb2312 not null;
//修改字段字符集
-----------------------------------------------------------------------------------------
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
//查看数据库登陆所有用户
+----------------------------+
| query |
+----------------------------+
| User: 'root'@'127.0.0.1';|
| User: ''@'localhost'; |
| User: 'root'@'localhost';|
| User: ''@'webq'; |
| User: 'root'@'webq'; |
+----------------------------+
5 rows in set (0.00 sec)
mysql> select * from mysql.user where user='root' \G
//查看数据库中具体某个用户的权限
mysql> desc mysql.user;
//查看mysql.user表结构
mysql> show databases;
//显示所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> use mysql;
//打开数据库mysql;切换数据库
mysql> show tables;
//显示数据库mysql的所有表
-----------------------------------------------------------------------------------------
mysql> insert into mysql.user(Host,User,Password) values("localhost","TesT",password("p@ssw0rd"));
//增加数据库用户
mysql> flush privileges;
//刷新系统权限表；
+----------------------------+
| query |
+----------------------------+
| User: 'root'@'127.0.0.1';|
| User: ''@'localhost'; |
| User: 'TesT'@'localhost'; |
| User: 'root'@'localhost';|
| User: ''@'webq'; |
| User: 'root'@'webq'; |
+----------------------------+
6 rows in set (0.00 sec)
mysql> delete from mysql.user where user='filon' and host='localhost';
//删除数据库用户命令：
+---------------------------+
| query |
+---------------------------+
| User: 'root'@'127.0.0.1'; |
| User: ''@'localhost'; |
| User: 'root'@'localhost'; |
| User: ''@'webq'; |
| User: 'root'@'webq'; |
+---------------------------+
5 rows in set (0.00 sec)
mysql> CREATE USER 'TesT'@'localhost'> //创建数据库用户
+---------------------------+
| query |
+---------------------------+
| User: 'root'@'127.0.0.1'; |
| User: ''@'localhost'; |
| User: 'TesT'@'localhost'; |
| User: 'root'@'localhost'; |
| User: ''@'webq'; |
| User: 'root'@'webq'; |
+---------------------------+
6 rows in set (0.00 sec)
mysql> create database TesT;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TesT |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant all privileges on TesT.* to TesT@localhost> 数据库授权:
mysql> grant select,insert,update,delete on *.* to username@"%">
mysql> grant select,insert,update,delete on test.* to username@localhost>
mysql> grant all privileges on test.* to username@localhost>
//数据库授权格式：grant select on 数据库.* to 用户名@登录主机> mysql>flush privileges;
//刷新系统权限表
mysql> drop user TesT@'localhost';
//删除账户及权限
mysql> create database ;
//创建数据库
mysql> CREATE TABLE IF NOT EXISTS(
column1 INT(11),
column2 VARCHAR(255),
column3 DATE,
.
.
.
//A table must have at least 1 column
);
//创建数据库表
mysql> drop database 库名;
//删除数据库
mysql> drop table 表名;
//删除表
-----------------------------------------------------------------------------------------
mysql> mysqlcheck --all-databases -o
//优化数据库 #optimize（一周使用一次就可以，酌情适当增加使用次数）
mysql> mysqlcheck –all-databases -r
//修复数据库#repair
mysql> mysqlcheck –all-databases -a
//分析数据库#analyze
mysql> mysqlcheck -r steelssc_ccc
//修复单个数据库 #repair
mysql> mysqlcheck -r database1 database2 ···
///修复多个数据库 #repair
-----------------------------------------------------------------------------------------
备份数据库 (命令其实非常简单，可以不需要密码：)
方法一：
mysql> mysqldump TesT_bbs > TesT_bbs.sql
//进入到库目录 备份数据库
格式:mysql> mysqldump -u root -p --opt 数据库名>备份名;
方法二：mysql
>use TesT_bbs;
>source /home/TesT/public_html/TesT_bbs.sql;
恢复数据库
mysql> mysql -u root -p 数据库名
页:
[1]