操作表
表类似于文件
1. 增
创建新表
create table 表名(
字段名 列类型 [可选的参数], ### 记住加逗号
字段名 列类型 [可选的参数], ### 记住加逗号
字段名 列类型 [可选的参数] ### 最后一行不加逗号
.....
) charset = utf-8 ;
例子:
create table t1(
id int, # int 数字类型
name char(5) # char代表字符串
) charset = utf8 ;
'''
mysql> create table t1(ID int,name char(5))charset = utf8;
Query OK, 0 rows affected (0.02 sec)'''
列约束
****************************************
auto_increment: 自增1
primary key 主键索引,加快查询速度,列的值不能重复
not null 标识该字段不能为空
default 为该字段设置默认值
例子:
create table t2(
id int auto_increment primary key,
name char(10)
)charset=utf8;
'''Query OK, 0 rows affected (0.02 sec)'''
mysql> insert into t2(name) values ('ersha');
# 添加值也可以只添加一项,id项是自增的
'''Query OK, 1 row affected (0.01 sec)'''
mysql> select * from t2 ;
/* +----+-------+
| id | name |
+----+-------+
| 1 | ersha |
| 2 | dasha |
+----+-------+
2 rows in set (0.00 sec) */
推荐使用:
create table t3(
id int unsigned auto_increment primary key,
// 设置无符号int 自增加1 主键索引
name char(10) not null default 'xxx',
// 字符串(10) 非空 设置默认值为xxx
age int not null default 0
// int 非空 默认值0
)charset = utf8 ;
// Query OK, 0 rows affected (0.02 sec)
// 只添加 age 值,其余的默认值
insert into t3 (age) values(10);
// Query OK, 1 row affected (0.00 sec)
// 查看t3表内容
select * from t3 ;
/* +----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | xxx | 10 |
+----+------+-----+
1 row in set (0.00 sec) */
列类型
****************************************
create table 表名(
字段名 列类型 unsigned [可选参数],
字段名 列类型 [可选参数],
字段名 列类型 [可选参数]
.....
)charset = utf8 ;
1. 数字
整型
分类:
tinyint //(微小int)
smallint //(小的int)
int // 常用的 ********************推荐使用
mediumint //(中等int)
bigint //(大int)
区别:
1. 整数类型
2. 取值范围
3. unsigned 加上代表不能取负数,只适用于整型.
应用场景:
根据公司业务场景,来选取合适的类型
浮点型
*********
'''类型
float：4字节，3.4E–38～3.4E+38 *
double：8字节，1.7E–308～1.7E+308
decimal：M，D大值基础上+2
'''
'''宽度：
限制存储宽度
(M, D) => M为位数，D为小数位
float(255, 30)：精度最低，最常用
double(255, 30)：精度高，占位多
decimal(65, 30)：字符串存，全精度
'''
float 不一定精准
decimal 非常精确的数字
decimal(m,d) m是数字总个数(负号不算),d是小数点后个数
// 新建t5表
create table t5(
id int auto_increment primary key,
salary decimal(16,10),
num float
)charset = utf8 ;
1. // 添加数据 (decimal 正好10位)
insert into t5 (salary,num)
values(500023.0123456789,5000.0123456789);
// 查询数据
select * from t5;
/*
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.0123456789 | 5000.01 |
+----+-------------------+---------+
1 row in set (0.00 sec)
*/
2. // 添加数据 (decimal 少于10位)
insert into t5 (salary,num)
values(500023.01234567,5000.0123456789);
// 查询数据
select * from t5;
/*
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.0123456789 | 5000.01 |
| 2 | 500023.0123456700 | 5000.01 |
+----+-------------------+---------+
2 rows in set (0.00 sec)
*/ 会自动补全10位
3. // 添加数据 (decimal 多于10位)
insert into t5 (salary,num)
values(500023.012345678989,5000.0123456789);
// 查询数据
select * from t5;
/*
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.0123456789 | 5000.01 |
| 2 | 500023.0123456700 | 5000.01 |
| 3 | 500023.0123456790 | 5000.01 |
+----+-------------------+---------+
*/ 会四舍五入进1
2. 字符串
char(长度):定长
create table t6 (
id int unsigned auto_increment primary key,
name char(10) not null default 'xxx'
)charset = utf8 ;
// 插入数据(少于10位)
insert into t6(name) values('hellow');
// 查看数据行
select * from t6;
/*
+----+--------+
| id | name |
+----+--------+
| 1 | hellow |
+----+--------+
1 row in set (0.00 sec)
*/
// 插入数据(多于10)
insert into t6(name) values('dadadadbeia');
/*
ERROR 1406 (22001): Data too long for column 'name' at row 1 超出长度报错
varchar(长度):变长
create table t6(
id int auto_increment primary key,
name varchar(10) not null default 'xxx'
) charset = utf8;
// 插入数据(小于10)
insert into t6(name) values('hellow');
// 查看数据行
select * from t6;
/*
+----+--------+
| id | name |
+----+--------+
| 1 | hellow |
+----+--------+
1 row in set (0.00 sec) */
// 插入数据(大于10)
insert into t6(name) values('dadadadbeia');
/*
ERROR 1406 (22001): Data too long for column 'name' at row 1 超出长度报错
区别
char定长
无论插入的字符是多少个,永远固定占用规定的长度
应用场景:
身份证
手机号
md5加密之后的值,比如密码等 char(32)
varchar变长
根据插入的长度来计算所占的字节数,但是有一个字节是用来存储字符串长度的
注意:如果不能确定插入的数据的大小,一般建议使用varchar(255)
3. 时间日期类型
year
YYYY（1901/2155）
date
YYYY-MM-DD（1000-01-01/9999-12-31）
time
HH:MM:SS（'-838:59:59'/'838:59:59'）
datatime
************************
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 ）
timestamp
时间戳 YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
例子
create table t8(
d data,
t time,
dt datetime
);
// 添加数据行
insert into t8 values(now(),now(),now());
// 查询数据
select * from t8;
/*
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2019-10-29 | 17:17:05 | 2019-10-29 17:17:05 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
4. 枚举
列出所有的选项(enum) 只接受正确输入的数值
create table t9(
id int auto_increment primary key,
gender enum('male','female')
)charset utf8;
// 插入数据行
insert into t9 (gender) values ('male');
//Query OK, 1 row affected (0.01 sec)
insert into t9 (gender) values ('female');
//Query OK, 1 row affected (0.01 sec)
insert into t9 (gender) values ('snsdd');
// ERROR 1265 (01000): Data truncated for column 'gender' at row 1
2.改
1.修改表名
alter table 旧表名 rename 新表名;
//修改表名
alter table t8 rename t88;
2.添加字段
1.alter table 表名
add 字段名 列类型[可选参数],
add 字段名 列类型[可选参数];
//增加字段 (永远在最后一行添加)
alter table t88 add name varchar(32) not null default'';
/* Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0 */
2.alter table 表名
add 字段名 列类型 [可选参数] first;
// 添加字段 (永远在第一行)
alter table t88 add name3 varchar(32) not null default'' first;
/* Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0 */
3.alter table 表名
add 字段名 列类型[可选参数] after 字段名;
// 添加字段 (可添加在指定数据后)
alter table t88 add name4 varchar(32) not null default '' after d ;
/* Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0 */
3.删除字段
alter table 表名 drop 字段名;
// 删除字段
alter table t88 drop name4 ;
/* Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0 */
4.修改字段
1. // 指定字段修改类型
alter table t88 modify 字段名 数据类型[完整性约束条件...];
// 修改字段
alter table t88 modify name3 char(20);
/* Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0 */
2. // 修改字段至新值
alter table 表名 change 旧字段名 新字段名 新数据类型[完整性约束条件...];
// 修改
alter table t88 change name3 name33 varchar(32) not null default '' ;
/* Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0 */
//修改2
alter table t88 change name33 name3 ;
报错 不能
3. 删
线上禁用
drop table 表名;
drop table t9;
//Query OK, 0 rows affected (0.01 sec)
4. 查
查看所有表
show tables;
查询表中列的信息
desc 表名;
5. 复制表结构
查看表的创建语句
show create table 表名;
show create table t88;
/*
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t88 | CREATE TABLE `t88` (
`name33` varchar(32) NOT NULL DEFAULT '',
`d` date DEFAULT NULL,
`t` time DEFAULT NULL,
`dt` datetime DEFAULT NULL,
`name` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
like 复制
create table t89 like t88;
// Query OK, 0 rows affected (0.02 sec)
操作数据行
数据行像是在文件中的数据
1.增
添加数据行
insert into 表名(列1,列2) values(值1,'值2');
例子:
insert into t1 (id,name) values (1,'dasha');
insert into t1(id,name) values (2,'ersha'),(3,'sansha'),(4,'sisha');
# Query OK, 1 row affected (0.00 sec)
2.查
查询数据行
select 列1,列2 from 表名; // 查询指定的列
select * from 表名; // * 查询所有的列
按条件查询
1. select * from 表名 where 条件
2. select * from 表名 where 条件 and 条件
select * from t3 where id > 4 ;
// '>' '<' '=' '>=' '<=' '!=' '<>' 都可做判断//
/*
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 5 | sisha | 0 |
| 6 | wusha | 0 |
+----+-------+-----+
2 rows in set (0.01 sec) */
between...and...:取值范围是闭区间
select * from t3 where id between 2 and 4;
/*
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 2 | dasha | 0 |
| 3 | ersha | 0 |
| 4 | sansha | 0 |
+----+--------+-----+
3 rows in set (0.00 sec)
distinct 去除重复元素
select distinct name from t3;
/*
+--------+
| name |
+--------+
| xxx |
| dasha |
| ersha |
| sansha |
| sisha |
| wusha |
+--------+
6 rows in set (0.00 sec)
通过四则运算查询
尽量不要使用,占用查询内存
select name ,age*10 from t3;
select name, age*10 as age from t3;
in() 查询输出指定列
select * from t3 where id in (2,4,1);
/*
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | xxx | 10 |
| 2 | dasha | 0 |
| 4 | sansha | 0 |
+----+--------+-----+
3 rows in set (0.00 sec)
like : 模糊查询
不让用,与主键查询比占用内存
1.以'x'开头
select * from t3 where name like 'x%';
/*
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | xxx | 10 |
+----+------+-----+
1 row in set (0.00 sec) */
2.以'a'结尾
select * from t3 where name like '%a';
/*
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 2 | dasha | 0 |
| 3 | ersha | 0 |
| 4 | sansha | 0 |
| 5 | sisha | 0 |
| 6 | wusha | 0 |
| 7 | dasha | 0 |
+----+--------+-----+
6 rows in set (0.00 sec) */
3.包含'n'的
select * from t3 where name like '%n%';
/*
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 4 | sansha | 0 |
+----+--------+-----+
1 row in set (0.00 sec)
3. 改
指定列修改值
update 表名 set 列名1=新值1 , 列名2=新值2 where 条件;
// 修改列名
update t3 set name='yyy' where id=3;
// "<" ">" "=" "<=" ">=" "and" "or"
/* Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 */
//查询列内容
select * from t3;
/*
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | xxx | 10 |
| 2 | dasha | 0 |
| 3 | yyy | 0 |
4. 删
根据条件删除列
delete from 表名 where 条件;
delete from t5 where id=1 ;
delete from t5 where id>1 and id <3
// '<' '>' "=" "<=" ">=" 都可判断
删除表中所有数据
delete from 表名;
// 删除表中所有的数据 Query OK, 1 row affected (0.00 sec)
truncate 全选删除
没有where条件的
truncate t5;
区别
- delete 之后,插入数据从上一次主键自增加1开始,truncate则是从1开始.
- delete删除是一行一行删除. truncate:是全选删除,删除速度是高于delete的