MySQL 数据库操作

创建数据库

• 创建数据库的几种方法

CREATE DATABASE 数据库名;

# 创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

# 判断数据库是否已经存在，不存在则创建数据库（ 推荐）
CREATE DATABASE IF NOT EXISTS 数据库名;

• 注意事项: DATABASE 不能改名.一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的

使用数据库,常见的几个命令

# 查看当前所有的数据库
SHOW DATABASES; #有一个S，代表多个数据库

# 查看当前正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数

# 查看指定库下所有的表
SHOW TABLES FROM 数据库名;

# 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
# 或者： "\g"等同于';','\G'在'\g'的基础上,多了'垂直展示'功能而已
SHOW CREATE DATABASE 数据库名\G

# 使用/切换数据库
USE 数据库名;

修改数据库

# 更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

# 删除指定的数据库
DROP DATABASE 数据库名;

# 删除指定的数据库（ 推荐）
DROP DATABASE IF EXISTS 数据库名;

创建表

• 语法格式

CREATE TABLE [IF NOT EXISTS] 表名(
    字段1, 数据类型 [约束条件] [默认值],
    字段2, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件]
);

-- 创建表
CREATE TABLE emp (
    -- int类型
    emp_id INT,
    -- 最多保存20个中英文字符
    emp_name VARCHAR(20),
    -- 总位数不超过15位
    salary DOUBLE,
    -- 日期类型
    birthday DATE
);

......

CREATE TABLE dept(
    -- int类型，自增
    deptno INT(2) AUTO_INCREMENT,
    dname VARCHAR(14),
    loc VARCHAR(13),
    -- 主键
    PRIMARY KEY (deptno)
);

• 使用子查询返回的数据,来创建表,语法格式

CREATE TABLE table_name [(column,column...)] as subquery;

CREATE TABLE emp1 AS SELECT * FROM employees;

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

查看数据表结构,以及查看数据表如何创建

# 查看结构
desc table_name

# 查看表如何创建
SHOW CREATE TABLE table_name

修改表

• 实质就是对’列’的,增/删/改 操作

## 追加一个列
# ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
ALTER TABLE dept80
ADD job_id varchar(15);

## 修改一个列
# ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
ALTER TABLE dept80
MODIFY last_name VARCHAR(30);
# 或者
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

## 重命名一个列
# ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

## 删除一个列
# ALTER TABLE 表名 DROP 【COLUMN】字段名

删除表

- 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除

- 数据和结构都被删除

- 所有正在运行的相关事务被提交

- 所有相关索引被删除

• 语法格式

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

• 举例

DROP TABLE dept80; # DROP TABLE 语句不能回滚

清空表

• TRUNCATE TABLE语句：

  • 删除表中所有的数据

  • 释放表的存储空间

  • TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

TRUNCATE TABLE detail_dept;


DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

往表中插入数据

• 一次只能向表中插入一条数据,语法格式

INSERT INTO 表名
VALUES (value1,value2,....);

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);

• 注意事项: 值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同

• 插入的时候,加入’列名’demo演示

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

• 同时插入多条记录,语法格式

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
# 或者
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

mysql> INSERT INTO emp(emp_id,emp_name)
-> VALUES (1001,'shkstart'),
-> (1002,'atguigu'),
-> (1003,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

• INSERT 配合 子查询使用

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

• 注意事项

  • 不必书写 VALUES 子句

  • 子查询中的值列表应与 INSERT 子句中的列名对应

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;


INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

更新数据

• 使用 WHERE 子句指定需要更新的数据

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

• 如果省略 WHERE 子句，则表中的所有数据都将被更新

UPDATE copy_emp
SET department_id = 110;

• 注意事项

  • 可以一次更新多条数据。

  • 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

删除数据

DELETE FROM table_name [WHERE <condition>];

# 使用 WHERE 子句删除指定的记录
DELETE FROM departments
WHERE department_name = 'Finance';

# 如果省略 WHERE 子句，则表中的全部数据将被删除
DELETE FROM copy_emp;

原文地址：http://www.cnblogs.com/qinganning/p/16787645.html