Python全栈开发S7 Day31

mysql 表操作

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

#1. 数字：
    整型：tinyinit  int  bigint
    小数：
        float ：在位数比较短的情况下不精准
        double ：在位数比较长的情况下不精准
            0.000001230123123123
            存成：0.000001230000

        decimal：（如果用小数，则用推荐使用decimal）
            精准
            内部原理是以字符串形式去存

#2. 字符串：
    char（10）：简单粗暴，浪费空间，存取速度快
        root存成root000000
    varchar：精准，节省空间，存取速度慢

    sql优化：创建表时，定长的类型往前放，变长的往后放
                    比如性别           比如地址或描述信息

    >255个字符，超了就把文件路径存放到数据库中。
            比如图片，视频等找一个文件服务器，数据库中只存路径或url。



#3. 时间类型：
    最常用：datetime


#4. 枚举类型与集合类型

mysql数据类型概览

数据类型

数值类型

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

　　作用：存储年龄，等级，id，各种号码等

　　ps:默认都是有符号的

========================================
        tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127
            无符号：
～ 255

            PS： MySQL中无布尔值，使用tinyint(1)构造。



========================================
        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -2147483648 ～ 2147483647
            无符号：
～ 4294967295



========================================
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -9223372036854775808 ～ 9223372036854775807
            无符号：
 ～  18446744073709551615

View Code

浮点类型：FLOAT DOUBLE

　　作用：存储薪资、身高、体重、体质参数等

#整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT
#作用：存储年龄，等级，id，各种号码等
#ps:默认都是有符号的

#强调：整型的宽度指的是显示宽度，并不是存储宽度
create table t1(id int(1));
insert into t1 values(256111);
select * from t1;

create table t2(id int(20));
insert into t2 values(256111);
select * from t2;

create table t3(id int(20) zerofill);
insert into t3 values(256111);
select * from t3;

mysql> create table t4(id int);
Query OK, 0 rows affected (0.46 sec)

mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into t4 values(1111111111111111111111111111111111111111111);
Query OK, 1 row affected, 2 warnings (0.17 sec)

mysql> select * from t4;
+------------+
| id         |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)


mysql> create table t5(id int unsigned);
Query OK, 0 rows affected (0.45 sec)

mysql> desc t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into  t5 values(11111111111111111111111111111111111111111);
Query OK, 1 row affected, 2 warnings (0.05 sec)

mysql> select * from t5;
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)


#浮点类型：FLOAT,DOUBLE,DECIMAL
#作用：体重，薪资，价格


mysql> create table t6(weight float(256,56) unsigned);
ERROR 1425 (42000): Too big scale 56 specified for column 'weight'. Maximum is 30.
mysql> create table t6(weight float(256,30) unsigned);
ERROR 1439 (42000): Display width out of range for column 'weight' (max = 255)
mysql> create table t6(weight float(255,30) unsigned);
Query OK, 0 rows affected (0.37 sec)

mysql> desc t6;
+--------+------------------------+------+-----+---------+-------+
| Field  | Type                   | Null | Key | Default | Extra |
+--------+------------------------+------+-----+---------+-------+
| weight | float(255,30) unsigned | YES  |     | NULL    |       |
+--------+------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)



mysql> create table t7(weight double(256,33) unsigned);
ERROR 1425 (42000): Too big scale 33 specified for column 'weight'. Maximum is 30
mysql> create table t7(weight double(256,30) unsigned);
ERROR 1439 (42000): Display width out of range for column 'weight' (max = 255)
mysql> create table t7(weight double(255,30) unsigned);
Query OK, 0 rows affected (0.36 sec)


mysql> create table t8(weight decimal(66,33) unsigned);
ERROR 1425 (42000): Too big scale 33 specified for column 'weight'. Maximum is 30.
mysql> create table t8(weight decimal(66,30) unsigned);
ERROR 1426 (42000): Too big precision 66 specified for column 'weight'. Maximum is 65.
mysql> create table t8(weight decimal(65,30) unsigned);
Query OK, 0 rows affected (0.39 sec)


#对比三种类型的精度

insert into t6 values(1.1111111111111111111111111111111111111111111111111111111111111111);
insert into t7 values(1.1111111111111111111111111111111111111111111111111111111111111111);
insert into t8 values(1.1111111111111111111111111111111111111111111111111111111111111111);


mysql> select * from t6;
+----------------------------------+
| weight                           |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t7;
+----------------------------------+
| weight                           |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t8;
+----------------------------------+
| weight                           |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)


#了解：BIT
mysql> create table t9(x bit(1));
Query OK, 0 rows affected (0.34 sec)

mysql> insert into t9 values(2);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from t9;
+------+
| x    |
+------+
|     |
+------+
1 row in set (0.00 sec)

mysql> select bin(x) from t9;
+--------+
| bin(x) |
+--------+
| 1      |
+--------+
1 row in set (0.02 sec)

mysql> select hex(x) from t9;
+--------+
| hex(x) |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)

验证

日期类型

DATE TIME DATETIMETIMESTAMP YEAR

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

DATE：2017-11-11  出生年月日
TIME：10：14：11  上课时间
DATETIME：2017-11-11 10：14：11 注册时间，文章发布时间，员工入职时间
TIMESTAMP：2017-11-11 10：14：11
YEAR ：1970 出生年

create table t10(
    born_date date,
    class_time time,
    reg_time datetime,
    born_year year
);

insert into t10 values
('1999-11-11','08:30:00','2017-11-11 11:11:11',2011);

insert into t10 values
(now(),now(),now(),now());


#了解：datetime与timestamp
create table t11(
    x datetime,
    y timestamp
);

desc t11;

mysql> desc t11;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| x     | datetime  | YES  |     | NULL              |                             |
| y     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> insert into t11 values(null,null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t11;
+------+---------------------+
| x    | y                   |
+------+---------------------+
| NULL | 2017-10-23 10:25:07 |
+------+---------------------+
1 row in set (0.00 sec)



mysql> insert into t11 values('1011-11-11','1011-11-11');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from t11;
+---------------------+---------------------+
| x                   | y                   |
+---------------------+---------------------+
| NULL                | 2017-10-23 10:25:07 |
| 1011-11-11 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+

验证

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。
datetime与timestamp的区别

1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。

2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

datetime与timestamp的区别

字符串类型

字符串类型作用：名字，密码，职位，地址

注意：char和varchar括号内的参数指的都是字符的长度

char类型：
    范围：0-255
    特点：
        定长，简单粗暴，浪费空间（待存储的数据长度<宽度限制），存取速度快

varchar类型：
    范围：0-21844
    特点：
        变长，精准，节省空间（待存储的数据长度<宽度限制），存取速度慢

#1、范围
mysql> create table t12(x char(256));
ERROR 1074 (42000): Column length too big for column 'x' (max = 255); use BLOB or TEXT instead
mysql> create table t12(x varchar(21845));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 6553
ns to TEXT or BLOBs
mysql> create table t12(x varchar(21844));
Query OK, 0 rows affected (0.41 sec)

mysql> desc t12;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x     | varchar(21844) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> create table t13(x varchar(65534));
Query OK, 0 rows affected, 1 warning (0.38 sec)

mysql> desc t13;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x     | mediumtext | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)


#2、宽度的限制
mysql> create table t14(x char(3));
Query OK, 0 rows affected (0.67 sec)

mysql> create table t15(x varchar(3));
Query OK, 0 rows affected (0.40 sec)

mysql>
mysql> insert t14 values('xxxxxxxxxx');
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> insert t15 values('xxxxxxxxxx');
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from t14;
+------+
| x    |
+------+
| xxx  |
+------+
1 row in set (0.00 sec)

mysql> select * from t15;
+------+
| x    |
+------+
| xxx  |
+------+
1 row in set (0.00 sec)
mysql> insert t14 values('你好啊啊啊');
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> select * from t14;
+-----------+
| x         |
+-----------+
| xxx       |
| 你好啊    |
+-----------+
2 rows in set (0.00 sec)




create table t16(name char(5));
create table t17(name varchar(5));


alex |e   |wupei|yh   |

标记alex|标记e|标记wupei|标记yh|



#验证定长与变长
create table t16(name char(5));
create table t17(name varchar(5));


insert into t16 values('a'); #'a    '
insert into t17 values('a'); #'a'


select * from t16;
select * from t17;


SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
select char_length(name) from t16;
select char_length(name) from t17;

验证

枚举类型与集合类型

字段的值只能在给定范围中选择，如单选框，多选框
enum 单选只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

create table t18(
    id int,
    name char(10),
    sex enum('male','female','None')
);
alter table t18 modify sex enum('male','female','None') not null default 'male';
insert into t18 values(1,'egon','xxxxx');
insert into t18(id,name) values(1,'egon');



create table t19(
    id int,
    name char(10),
    hobbies set('music','read','basketball','football','eat','sleep')
);
insert into t19 values(1,'egon','music,read,eat');

约束

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

主要分为：
PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

KEY:
    primay key
    unique
foreign key


mysql> create table t20(id int auto_increment,name char(10));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

create table t20(id int primary key auto_increment,name char(10));
create table t21(id int not null unique auto_increment,name char(10));


create table t22(
    id int primary key,
    name char(10)
);

create table t23(
    id int,
    name char(10),
    constraint pri_id primary key(id)
);

create table t24(
    id int,
    name char(10),
    primary key(id)
);



create table t25(
    id int,
    name char(10),
    constraint uni_id unique(id)
);

create table t26(
    id int,
    name char(10),
    unique(id)
);


#只能有一个主建，但是可以有多个not null unique
create table t27(
    id int,
    name char(10),
    primary key(id),
    primary key(name)
);


create table t28(
    id int not null unique,
    name char(10) not null unique
);

#联合唯一

create table t29(
    id int,
    ip char(15),
    port int,
    primary key(ip,port)
);

insert into t29 values
(1,'1.1.1.1',3306),
(2,'1.1.1.2',3306),
(3,'1.1.1.1',8080)
;




create table t30(
    id int primary key auto_increment,
    ip char(15) not null,
    port int not null,
    unique(ip,port)
);

insert into t30(ip,port) values
('1.1.1.1',3306),
('1.1.1.1',3307),
('1.1.1.2',3307)
;

验证

auto_increment自增

　　只能有一个自增字段，并且该字段必须被约束成key。

mysql> create table t1(id int primary key auto_increment,name char(10));
Query OK, 0 rows affected (0.38 sec)

mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------------------------
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------
1 row in set (0.00 sec)


mysql> insert into t1(name) values('alex'),('egon'),('evia');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | alex |
|  2 | egon |
|  3 | evia |
+----+------+
3 rows in set (0.00 sec)

mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> show create table t1;
+-------+--------------------------------------------------
| Table | Create Table
+-------+--------------------------------------------------
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------
1 row in set (0.00 sec)

View Code

#表自带的属性
#自增字段的初始值：AUTO_INCREMENT


mysql> create table t2(id int primary key auto_increment,name char(10))auto_increment=4;
Query OK, 0 rows affected (0.35 sec)

mysql> desc t2;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+---------------------------------------------------------------------------------------------------
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> insert into t2(name) values('aaaa');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  4 | aaaa |
+----+------+
1 row in set (0.00 sec)

#针对所有表的配置：
#设置自增的初始值：auto_increment_offset
#设置自增的步长：auto_increment_increment


#基于会话级别
    set session auto_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auto_increment_increment=2 #修改全局级别的步长（所有会话都生效）
    set global auto_increment_offset=2;

#初始值（auto_increment_offset）一定要 <= 步长（auto_increment_increment）

create table t4(id int primary key auto_increment,name char(10));

set global auto_increment_increment=3;
set global auto_increment_offset=2;


mysql> use day43;
Database changed
mysql> create table t5(id int primary key auto_increment,name char(10));
Query OK, 0 rows affected (0.35 sec)

mysql> desc t5;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> show create table t5;
+-------+------------------------------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------------------------------
| t5    | CREATE TABLE `t5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> insert into t5(name) values('aaaa');
Query OK, 1 row affected (0.08 sec)

mysql> select * from t5;
+----+------+
| id | name |
+----+------+
|  2 | aaaa |
+----+------+
1 row in set (0.00 sec)

mysql> insert into t5(name) values('bbbb');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t5;
+----+------+
| id | name |
+----+------+
|  2 | aaaa |
|  5 | bbbb |
+----+------+
2 rows in set (0.00 sec)

foreign key外键

找出两张表之间的关系

分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

#先创建被关联的表
create table dep(
    id int primary key auto_increment,
    dep_name char(20) not null unique,
    dep_comment varchar(50)
)auto_increment=200;

insert into dep(dep_name,dep_comment) values
('IT','xxxxxxxxxx'),
('Sale','yhyyyyyyy'),
('Operation','asdfadfadsf'),
('HR','asfasdfasdfasdfasdf')
;


#再创表去关联上面的表
create table emp(
    id int primary key auto_increment,
    name char(6) not null,
    sex enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    on delete cascade
    on update cascade
);

insert into emp(name,sex,dep_id) values
('egon','male',200),
('alex','male',200),
('yh','female',203),
('evia','female',200),
('wpq','male',202)
;


insert into emp(name,sex,dep_id) values
('alex1','male',250);

View Code

#解散一个部门
#未指定同步更新、同步删除的参数时，需要这么删除
delete from emp where dep_id=200;
delete from dep where id=200;

#指定后
mysql> select * from dep;
+-----+-----------+---------------------+
| id  | dep_name  | dep_comment         |
+-----+-----------+---------------------+
| 200 | IT        | xxxxxxxxxx          |
| 201 | Sale      | yhyyyyyyy           |
| 202 | Operation | asdfadfadsf         |
| 203 | HR        | asfasdfasdfasdfasdf |
+-----+-----------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex    | dep_id |
+----+------+--------+--------+
|  1 | egon | male   |    200 |
|  2 | alex | male   |    200 |
|  3 | yh   | female |    203 |
|  4 | evia | female |    200 |
|  5 | wpq  | male   |    202 |
+----+------+--------+--------+
5 rows in set (0.00 sec)

mysql> delete from dep where id=200;
Query OK, 1 row affected (0.06 sec)

mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex    | dep_id |
+----+------+--------+--------+
|  3 | yh   | female |    203 |
|  5 | wpq  | male   |    202 |
+----+------+--------+--------+
2 rows in set (0.00 sec)


mysql> select * from dep;
+-----+-----------+---------------------+
| id  | dep_name  | dep_comment         |
+-----+-----------+---------------------+
| 201 | Sale      | yhyyyyyyy           |
| 202 | Operation | asdfadfadsf         |
| 203 | HR        | asfasdfasdfasdfasdf |
+-----+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex    | dep_id |
+----+------+--------+--------+
|  3 | yh   | female |    203 |
|  5 | wpq  | male   |    202 |
+----+------+--------+--------+
2 rows in set (0.00 sec)

mysql> update dep set id=2002 where id=202;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep;
+------+-----------+---------------------+
| id   | dep_name  | dep_comment         |
+------+-----------+---------------------+
|  201 | Sale      | yhyyyyyyy           |
|  203 | HR        | asfasdfasdfasdfasdf |
| 2002 | Operation | asdfadfadsf         |
+------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex    | dep_id |
+----+------+--------+--------+
|  3 | yh   | female |    203 |
|  5 | wpq  | male   |   2002 |
+----+------+--------+--------+
2 rows in set (0.00 sec)

#表之间的关系
实现方式：foreign key

#1、多对一：单向的foreign key
左表的多条记录对应右表的一条记录
右表的多条记录不能对应左表的一条记录

#2、多对多：双向的foreign key
左表的多条记录对应右表的一条记录
右表的多条记录也能对应左表的一条记录

create table role(
    id int primary key auto_increment,
    name char(7),
    permmison char(3),
    comment varchar(30)
);

create table user(
    id int primary key auto_increment,
    name char(6),
    password varchar(20)
);

create table user2role(
    id int primary key auto_increment,
    user_id int,
    role_id int,
    foreign key(user_id) references user(id) on update cascade on delete cascade,
    foreign key(role_id) references role(id) on update cascade on delete cascade
);


#3、一对一：foreign key+unique
左表的一条记录唯一对应右表的一条记录

create table student(
    id int primary key auto_increment,
    name char(6),
    class_name char(10),
    c_id int unique,
    foreign key(s_id) references student(id)
);

create table customer(
    id int primary key auto_increment,
    name char(6),
    phone int,
    qq char(11),
    mail varchar(20),
);

修改表ALTER TABLE

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                        ADD 字段名  数据类型 [完整性约束条件…];
    ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                            ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                                    DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

优质内容筛选与推荐>>
1、CentOS 5 LVM逻辑卷管理（转）
2、ObjectQuery查询及方法 [转载]
3、部署到服务器-执行脚本-脚本传递参数-需要base on 执行传入的参数(被测环境的ip)
4、图片懒加载lazyload.js详解
5、Asp.net Menu控件点击展开子目录

0 │ 收藏 │ 举报

mysql 表操作

数据类型

数值类型

日期类型

字符串类型

枚举类型与集合类型

约束

auto_increment自增

foreign key外键

修改表ALTER TABLE

复制表

朋友将在看一看看到

分享想法到看一看