mysql命令笔记

• 登陆

mysql -u root -p

• 查看Database

SHOW DATABASES;

• 添加Database

CREATE DATABASE database_name;

• 删除Database

DROP DATABASE database_name;

• 选择Database

USE database_name;

• 查看Database下所有的Table

SHOW TABLES;

• 查看指定Table信息

DESC table_name; or DESCRIBE table_name;

• 添加表

CREATE TABLE table_name(var1, var2, var3, ...) ;

Example:
CREATE TABLE student(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  sex VARCHAR(20) NOT NULL,
  date VARCHAR(20)
);

• 删除表

DROP TABLE table_name;

• 插入数据

INSERT INTO table_name(var1, var2, var3, ...) VALUES(val1, val2, val3, ...);

Example:
INSERT INTO student(name, sex, date) VALUES('James', 'Male', '1990-03-12');
or
INSERT INTO VALUES(NULL, 'Jack’, 'Male', '1989-10-05');

• 修改数据

UPDATE table_name SET <var>=<value> WHERE <condition>

Example(条件更新):
UPDATE student SET name='Ross' WHERE id=1;
Example(整列更新):
UPDATE student SET name='Ross';

• 删除数据

DELETE FROM table_name WHERE <condition>

Example:
DELETE FROM student WHERE id=1;

• 修改表名

ALTER TABLE old_table_name RENAME TO new_table_name;

Example:
ALTER TABLE student RENAME TO new_student;