Mysql第四天

数据库管理

新建数据库

CREATE DATABASE  [IF NOT EXISTS] 数据库名

CREATE DATABASE IF NOT EXISTS MySQLDB;

数据类型

数据类型

日期和时间类型

字符类型

特殊字符序列

约束

非空约束（NOT NULL）

强制列不能为 NULL 值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

唯一约束（UNIQUE）

• 唯一约束可以保证记录的唯一性，即就是同一个表中，相同字段的值不会出现重复。
• 唯一约束的字段可以为空值（NULL）。
• 每一张数据表可以存在多个唯一约束字段。

主键约束（PRIMARY KEY）

外键约束（FOREIGN KEY）

删除数据库

DROP DATABASE 数据库名

复制表

复制一个表结构的实现方法有两种。

• 方法一：在CREATE TABLE语句的末尾添加LIKE子句，可以将源表的表结构复制到新表中，语法格式如下。

  CREATE TABLE 新表名 LIKE 源表

• 方法二、在CREATE TABLE 语句的末尾添加一个SELECT语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。

  CREATE TABLE 新表名 SELECT - FROM 源表

删除表

使用DROP TABLE语句删除表，语法如下：

DROP TABLE 表名

数据操作语言

插入数据

插入日期值

insert into emp value(8889,'李会长','小青年',null,'1888-01-01'
,4000,12,50);
Query OK, 1 row affected

批量插入数据

使用insert语句可以一次性地向表批量插入多条记录，语法格式如下。
INSERT INTO 表名[(字段列表)] VALUES
(值列表1),
(值列表2),
…
(值列表n);

INSERT INTO EMP(EMPNO, ENAME, JOB, SAL) 
VALUES 
('8881', '张三', '部门经理', 6000),
('8882', '李四', '职员', 3000),
('8883', '王五', '职员', 3500),
('8884' ,'赵六', '部门经理', 6500),
('8885', '高七', '职员', 2500),
('8886', '马八', '职员', 3100),
('8887', '钱九', '部门经理', 5000),
('8888', '孙十',  '职员', 2800);

创建一个manager表

create table manager as select * from emp where 1 = 0;

操作MySQL时，经常要使用where语句，当where语句不存在时，经常在后边加一个where 1=1，这个条件始终为true，在不定数量查询条件时，1=1可以很方便的规范语句。

修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

UPDATE emp
SET HIREDATE = DATE_ADD(HIREDATE,INTERVAL '10' day)
WHERE
	deptno = 20
AND HIREDATE >= '1982-01-01'	

dateadd():　DATE_ADD(date,INTERVAL expr unit)
date:起始日期或者起始时间
expr:指定的是一个间隔值,在起始时间中增加或者减少,注意:expr是一个字符串.对于负值间隔,可以以"-"开头
unit:表示的是一个单位,比如,加上的是1天还是一个小时.

datesub()::日期 减法
datediff(‘日期一’,‘日期二’) as 相差时间

修改奖金为null的员工，奖金设置为0

修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

UPDATE emp e,dept d
set e.sal = e.SAL + 500
WHERE
	e.DEPTNO = d.DEPTNO and d.LOC in ('NEW YORK','CHICAGO')

删除数据语法

DELETE from empl
WHERE deptno = 10;

• TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，使用ROLLBACK不可以回滚。
• DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。

事物

存储引擎

• InnoDB存储引擎的特点:
  支持外键（Foreign Key）
  支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。
  最新版本的MySQL已经开始支持全文检索。

• MyISAM存储引擎的特点：
  MyISAM具有检查和修复表的大多数工具。
  MyISAM表可以被压缩
  MyISAM表最早支持全文索引
  但MyISAM表不支持事务
  但MyISAM表不支持外键（Foreign Key）。
  如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。

修改存储引擎的方法

• 直接修改

alter table 表名 engine = 引擎名;

• 导出，导入。这个比较容易操作，直接把导出来的sql文件给改了，然后再导回去。
• 创建，插入。这个比第一种速度快， 安全性比第二种高，

CREATE TABLE my_tmp_table LIKE my_table;
ALTER TABLE my_tmp_table ENGINE=InnoDB;

事物演示

CREATE TABLE `account` (
  `id` int(12) NOT NULL,
  `name` varchar(20) NOT NULL,
  `balance` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 手动开启事物

start transaction;

• 实行一系列操作

给张三账户转50块钱

update account 
set balance = balance + 50 
where name = '张三';

提交后就不能再进行回滚rollback

MySql的各种错误

You can’t specify target table ‘emp’ for update in FROM clause

MySQL中You can’t specify target table for update in FROM clause错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。 例如下面这个sql：

delete from tbl where id in 
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
)

改写成下面就行了：

delete from tbl where id in 
(
    select a.id from 
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
    ) a
)

也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和Oracle不会出现此问题