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;