mysql 查看 mata_MySql基本命令

登陆

1、登陆：mysql -uroot -p123456

database

1、查看数据库：show databases;

2、创建数据库：create database test;

3、查看数据库信息：show create database test;

4、选择数据库：use test;

5、删除数据库：drop database test;

查看当前数据库

1、status

2、select database();

3、show tables;#列名中in后面的名称，例如：Tables_in_test

4、show variables like '%table%';

tables

1、查看数据库表：show tables like '%dept%';

2、创建表：

CREATE TABLE `dept` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`bActive` tinyint(1) DEFAULT '1',

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Table: CREATE TABLE `employee` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`deptid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`),

KEY `deptid` (`deptid`),

CONSTRAINT `fk_employee_dept` FOREIGN KEY (`deptid`) REFERENCES `dept` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注：外键不能跨存储引擎。

查看表结构/索引

1、describe/desc

2、show create table employee\G;

3、show index from employee;

4、show keys from employee;

修改数据库表

1、alter table employee rename t_employee;

2、alter table employee modify name varchar(25);

3、alter table employee change name cName varchar(20);

4、alter table employee add dCreateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

5、alter table employee add/modify id int(11) first;alter table employee add/modify name varchar(20) after id;

6、alter table employee drop name;

7、alter table employee engine=myisam;

8、alter table employee drop foreign key fk_employee_dept;

删除数据库表

1、drop table if exists employee;

查看锁

1、select * from information_schema.innodb_lock_waits G;

2、select * from information_schema.innodb_locks G;

3、select * from information_schema.innodb_trx G;