day_34

数据库表操作

操作背景为在数据库内操作 即: use 数据库名

列约束

auto_increment :  自增 1
primary key : 主键索引，加快查询速度, 列的值不能重复
not null :   标识该字段不能为空
default :    为该字段设置默认值

数据类型

整型

tinyint：1字节 -128~127 *
smallint：2字节 -32768 ~ 32767
mediumint：3字节
int：4字节 -2147483648~2147483647 *
bigint：8字节

区别:
 1.取值范围不同
 2.unsigned  加上代表不能取负数  只适用于整型
 3.不同类型所占字节数不一样, 决定所占空间及存放数据的大小限制

应用场景：
根据公司业务的场景，来选取合适的类型

浮点型

float:  不一定精确 
decimal: 非常的精确的数字,decimal(M, D) m是数字总个数（负号不算），d是小数点后个数。

如：decimal(3, 2)  ====》1.45

字符串

char：定长
varchar：变长

区别：
char: 定长， 无论插入的字符是多少个，永远固定占规定的长度
    应用场景：
        1. 身份证
        2. 手机号 char(11)
        3. md5加密之后的值，比如密码 等 char(32)
						
varchar: 变长， 根据插入的字符串的长度来计算所占的字节数，但是有一个字节是用来保存字符串的大小的
						
注意：如果， 不能确定插入的数据的大小， 一般建议使用 varchar(255)

时间类型

year：yyyy(1901/2155)
date：yyyy-MM-dd(1000-01-01/9999-12-31)
time：HH:mm:ss
datetime：yyyy-MM-dd HH:mm:ss(1000-01-01 00:00:00/9999-12-31 23:59:59)
timestamp：yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038-01-19 年某时)

datetime和timestamp区别:
    datetime：时间范围，不依赖当前时区，8字节，可以为null
    timestamp：时间范围，依赖当前时区，4字节，有默认值CURRENT_TIMESTAMP

枚举

列出所有的选项,单选

例子:		
create table t9 (
    id int auto_increment primary key,
    gender enum('male','female')
)charset utf8;

mysql> insert into t9 (gender) values ('male');

mysql> insert into t9 (gender) values ('female');

mysql> insert into t9 (gender) values ('dshajjdsja');   #报错

一、增

语法：

create table 表名(
    字段名  列类型 [可选的参数],  ### 记住加逗号
    字段名  列类型 [可选的参数],  ### 记住加逗号
    字段名  列类型 [可选的参数]  ### 最后一行不加逗号
    .....
)charset=utf8;  #### 后面加分号

例子：

# 例1
create table t1(
    id int,
    name char(5)
)charset=utf8;

# 例2
create table t2(
    id int auto_increment primary key,
    name char(10) 
)charset=utf8;
						
# 例3
create table t3(
    id int unsigned auto_increment primary key,
    name char(10) not null default 'xxx',
    age int not null default 0
)charset=utf8;

二、删

语法：

drop table 表名;  

# 线上慎用

例子：

 drop table t9;

三、改

语法：

# 修改表名
alter table 旧表名 rename 新表名;
 
# 增加字段1
alter table 表名 add 字段名 列类型 [可选参数],add 字段名 列类型 [可选参数];
注意: 上面添加的列永远是添加在最后一列之后

# 增加字段2
alter table 表名 add 字段名 列类型 [可选参数],add 字段名 列类型 [可选参数] first;
注意: 上面添加的列永远是添加在最前面

# 增加字段3
alter table 表名 add 字段名 列类型 [可选参数],add 字段名 列类型 [可选参数] after 字段名;
注意: 上面添加的列在 after 字段名 后面添加

# 删除字段
alter table 表名 drop 字段名;

# 修改字段1
alter table 表名 modify 字段名 数据类型 [完整性约束条件...];

# 修改字段2
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件...];

例子：

# 修改表名
mysql> alter table t8 rename t88;
				
# 增加字段1
mysql> alter table t88 add name varchar(32) not null default '';
注意: 上面添加的列永远是添加在最前面
				
# 增加字段2
mysql> alter table t88 add name3 varchar(32) not null default '' first;
注意: 上面添加的列永远是添加在最前面		

# 增加字段3
mysql> alter table t88 add name4 varchar(32) not null default '' after d;
注意: 上面添加的列在 "d"字段 后面添加

# 删除字段
mysql> alter table t88 drop name4;

# 修改字段1
mysql> alter table t88 modify name2 char(20);

# 修改字段2
mysql> alter table t88 change name2 name22 varchar(32) not null default '';

四、查

语法：

# 查看所有表
show tables;

# 复制表结构
create table 新表名 like 已有表名;

# 复制所有数据
insert into 需要信息的表名 select * from 拥有信息的表名;

例子：

# 复制表t88结构
create table t89 like t88;

# 把t8信息复制到t9
insert into t9 select * from t8;

数据库记录操作

一、增

语法：

insert into 表名 (列1, 列2...) values (值1,值2...);

例子：

insert into t1 (id, name) values (1, 'zekai2'),(2, 'zekai3'),(3,'zekai4');

二、删

语法：

# 删除符合条件的记录
delete from 表名 where 条件;

# 删除表中所有的数据
delete from 表名; 

#### 没有where条件的
truncate 表名; 

delete和truncate区别：	
1. delete之后，插入数据从上一次主键自增加1开始， truncate则是从1开始
2. delete删除， 是一行一行的删除， truncate：全选删除 truncate删除的速度是高于delete

例子：

mysql> delete from t5 where id=1;
mysql> delete from t5 where id>1;
mysql> delete from t5 where id>=1;
mysql> delete from t5 where id<1;
mysql> delete from t5 where id<=1;
mysql> delete from t5 where id>=1 and id<10;

三、改

语法：

update 表名 set 列名1=新值1,列名2=新值2 where 条件;

例子：

mysql> update t66 set name='xxxx' where id<30;
mysql> update t66 set name='xxxx' where id<=30;
mysql> update t66 set name='xxxx' where id>=30;
mysql> update t66 set name='xxxx' where id>30;
mysql> update t66 set name='xxxx' where id>20 and id<32;
mysql> update t66 set name='xxxx' where id>20 or name='zekai';

四、查

语法：

# 查询指定的列
select 列1, 列2... from 表名;  （*代表查询所有的列）

# 查询该表所有字段
select * from 表名;  （*代表查询所有的列）

例子：

select * from t66 where id>30 and id<40;
select * from t66 where id>30;
select * from t66 where id<30;
select * from t66 where id<=30;
select * from t66 where id>=30;
select * from t66 where id!=30;
select * from t66 where id<>30;

select * from t1;

补充:

# between..and...: 取值范围是闭区间
mysql> select * from t66 where id between 31 and 33;
					+----+--------+
					| id | name   |
					+----+--------+
					| 31 | dsadsa |
					| 32 | dsadsa |
					| 33 | dsadsa |
					+----+--------+
		
        
# 避免重复DISTINCT
mysql> select distinct name from t66;
					+--------+
					| name   |
					+--------+
					| xxxx   |
					| hds    |
					| dsadsa |
					+--------+
	
    
# 通过四则运算查询 （不要用）
mysql> select name, age*10 from t3;
					+------+--------+
					| name | age*10 |
					+------+--------+
					| xxx  |    100 |
					+------+--------+
					

mysql> select name, age*10 as age from t3;
					+------+-----+
					| name | age |
					+------+-----+
					| xxx  | 100 |
					+------+-----+
					1 row in set (0.02 sec)
					
			
					
mysql> select * from t66 where id in (23,34,11);
					+----+------+
					| id | name |
					+----+------+
					| 11 | xxxx |
					| 23 | hds  |
					+----+------+
					2 rows in set (0.04 sec)
					
# like : 模糊查询
# 以x开头：
mysql> select * from t66 where name like 'x%';
						+----+------+
						| id | name |
						+----+------+
						|  1 | xxxx |
						|  2 | xxxx |
						|  3 | xxxx |
						|  4 | xxxx |
						|  8 | xxxx |
						|  9 | xxxx |
						| 10 | xxxx |
						| 11 | xxxx |
						| 15 | xxxx |
						| 16 | xxxx |
						| 17 | xxxx |
						| 18 | xxxx |
						| 30 | xxxx |
						+----+------+
						
						
# 以x结尾：
mysql> select * from t66 where name like '%x';
						+----+------+
						| id | name |
						+----+------+
						|  1 | xxxx |
						|  2 | xxxx |
						|  3 | xxxx |
						|  4 | xxxx |
						|  8 | xxxx |
						|  9 | xxxx |
						| 10 | xxxx |
						| 11 | xxxx |
						| 15 | xxxx |
						| 16 | xxxx |
						| 17 | xxxx |
						| 18 | xxxx |
						| 30 | xxxx |
						+----+------+
						13 rows in set (0.00 sec)
					
# 包含x的：
mysql> select * from t66 where name like '%x%';