MySQL操作表和表记录

Wesley13
• 阅读 629

[TOC]

操作表

创建表语法

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

列约束

说明:列约束用来保证数据的完整性和一致性,约束条件都是创建表语法的时候可选参数

  • auto_increment:表示自增加1

  • not null:标识该字段不能为空

  • ``default:为该字段设置默认值`

  • foreign key:外键索引

  • unique key:标识字段的值是唯一的,字段值不能重复,可以有多个

  • primary key:主键索引,可以加快查询速度,字段值不能重复,只能有一个

  • unsigned:无符号,默认是有符号的

  • zerofill:使用0填充

例子

# 创建表
mysql> 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;
Query OK, 0 rows affected (0.03 sec)

# 向表中插入数据
mysql> insert into t3 (age) values (18);
Query OK, 1 row affected (0.00 sec)

# 查询表中数据
mysql> select * from t3;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | xxx  |  18 |
+----+------+-----+
1 row in set (0.00 sec)

# 查看表结构 desc 表名
mysql> desc t3;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     | xxx     |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

列类型

列类型

说明

数字(整数)

存储年龄、等级、id等

数字(浮点数)

存储薪资、身高、体重等

字符串

存储姓名、性别等

时间日期类型

存储注册时间、入职时间等

枚举

字段的值只能在给定范围中选择

数字(整形)

  • tinyint:小整数,有符号:[-128 ~ 127],无符号:[0 ~ 255]
  • smallint:大整数,有符号:[-32768 ~ 32767],无符号:[0 ~ 65535]
  • mediumint:大整数
  • bigint:极大整数
  • int (推荐使用):大整数,有符号:[-2147483648 ~ 2147483647],无符号:[0 ~ 4294967295]

unsigned 加在列类型后面,代表无符号,不能取负数,默认是有符号,可以是负数

应用场景:根据公司业务场景选择合适类型

MySQL操作表和表记录

例子:

# 我们创建一个t4表,限制使用无符号的
mysql> create table t4(x int unsigned);
Query OK, 0 rows affected (0.02 sec)

# 查看表结构,默认int数值长度已经设置为10位
mysql> desc t4;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| x     | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

# 只能插入无符号:[0 ~ 4294967295]范围值内的数字
# 超过数字的长度也报错
mysql> insert into t4 values (42949672955);
ERROR 1264 (22003): Out of range value for column 'x' at row 1

# 只能插入无符号:[0 ~ 4294967295]范围值内的数字
# 超过范围即报错
mysql> insert into t4 values (4294967296);
ERROR 1264 (22003): Out of range value for column 'x' at row 1
    
# 在这个范围内则成功
mysql> insert into t4 values (4294967295);
Query OK, 1 row affected (0.00 sec)

# 查询插进去的数据,如果数据库配置是非安全模式的话,插进去的数据是这个数据类型限制的最大数值
mysql> select * from t4;
+------------+
| x          |
+------------+
| 4294967295 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

注意:那么有的同学可能不会报错,能插进去,那是因为你的mysql5.6 没有开启安全模式,mysql5.7 以后的版本默认都是安全模式

# 查看当前数据库模式:
mysql> show variables like "%sql_mode%";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)


sql_model=no_engine_substitution  # 非安全性,默认
sql_model=strict_trans_tables  # 安全性

# 临时设置为安全模式,服务重启后会被重置
mysql>: set global sql_mode="strict_trans_tables";  # 在root用户登录状态下
# 在设置后,quit断开数据库连接后(服务器不重启)就会进入安全模式,
# 那么现在在插入超过范围内的数据就会报错

数字(浮点型)

  • float:不一定精确
  • decimal(m,d):存精确的数字,m是数字总个数(负号不算),d是小数点后的数字个数

例子:

# 创建表t5 限制salary字段为decimal数据类型,num为float数据类型
mysql> create table t5(
    -> id int auto_increment primary key,
    -> salary decimal(16,10),
    -> num float
    -> )charset=utf8;
Query OK, 0 rows affected (0.02 sec)

# 查看表结构
mysql> desc t5;
+--------+----------------+------+-----+---------+----------------+
| Field  | Type           | Null | Key | Default | Extra          |
+--------+----------------+------+-----+---------+----------------+
| id     | int(11)        | NO   | PRI | NULL    | auto_increment |
| salary | decimal(16,10) | YES  |     | NULL    |                |
| num    | float          | YES  |     | NULL    |                |
+--------+----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 插入数据,salary总数字长度为16,小数点后面正好为10位,插入数据
mysql> insert into t5 (salary,num) values (500000.2312345678,5000.232423523534634);
Query OK, 1 row affected (0.01 sec)

# 查询没有问题,精确存,但是num  float类型的不精确,存两位小数点,还四舍五入了
mysql> select * from t5;
+----+-------------------+---------+
| id | salary            | num     |
+----+-------------------+---------+
|  1 | 500000.2312345678 | 5000.23 |
+----+-------------------+---------+
1 row in set (0.00 sec)

# 插入数据,salary总数字长度为15,小数点后面小于10位,插入数据mysql> insert into t5 (salary,num) values (500000.231234567,5000.232423523534634);
Query OK, 1 row affected (0.01 sec)

# 查询,不精确,缺省的一位用0补齐,但是num  float类型的不精确,存两位小数点,还四舍五入了
mysql> select * from t5;
+----+-------------------+---------+
| id | salary            | num     |
+----+-------------------+---------+
|  1 | 500000.2312345678 | 5000.23 |
|  2 | 500000.2312345670 | 5000.23 |
+----+-------------------+---------+
2 rows in set (0.00 sec)

# 插入数据,salary总数字长度为17,小数点后面大于10位,插入数据
mysql> insert into t5 (salary,num) values (500000.23123456789,5000.232423523534634);
Query OK, 1 row affected, 1 warning (0.01 sec)

# 查询,不精确,只能存指定的长度,多出来的四舍五入了,但是num  float类型的不精确,存两位小数点,还四舍五入了
mysql> select * from t5;
+----+-------------------+---------+
| id | salary            | num     |
+----+-------------------+---------+
|  1 | 500000.2312345678 | 5000.23 |
|  2 | 500000.2312345670 | 5000.23 |
|  3 | 500000.2312345679 | 5000.23 |
+----+-------------------+---------+
3 rows in set (0.00 sec)

字符串

  • char:定长
  • varchar:变长

两者区别:

char 定长,无论插入字符是多少个,永远固定占规定的长度,使用场景:身份证、手机号、md5加密过后的密码char(32)

varchar 变长,根据插入的字符串长度计算所占的字节数,但是总有一个字节是用来保存字符串大小的,

如果不能确定插入的数据的大小,一般建议使用varchar(255)。

Value

CHAR(4)

Storage Required

VARCHAR(4)

Storage Required

''

' '

4 bytes

''

1 byte

'ab'

'ab '

4 bytes

'ab'

3 bytes

'abcd'

'abcd'

4 bytes

'abcd'

5 bytes

'abcdefgh'

'abcd'

4 bytes

'abcd'

5 bytes

例子:

# 创建t6表
mysql> create table t6(
    -> id int unsigned auto_increment primary key,
    -> name char(10) not null default 'xxx'
    -> )charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc t6;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     | xxx     |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

# 创建t7表
mysql> create table t7(
    -> id int unsigned auto_increment primary key,
    -> name varchar(10) not null default 'xxx'
    -> )charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc t7;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | xxx     |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into t6 (name) values ("hello");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t7 (name) values ("hello"); 
Query OK, 1 row affected (0.01 sec)

mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | hello |
+----+-------+
1 row in set (0.00 sec)

mysql> select * from t7;
+----+-------+
| id | name  |
+----+-------+
|  1 | hello |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into t6 (name) values ("hello32dwdsaffgfrthtrhtr");
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t7 (name) values ("hello32dwdsaffgfrthtrhtr"); 
ERROR 1406 (22001): Data too long for column 'name' at row 1

时间日期类型

  • year:保存年份
  • date:保存日期
  • time:保存时间
  • datetime:保存格式化后的时间
  • timestamp:保存时间戳

例子:

mysql> create table t8(
    -> d date,
    -> t time,
    -> dt datetime
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t8;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# now()表示当前时间
mysql> insert into t8 values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)

# 按照指定时间类型存时间
mysql> select * from t8;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-10-29 | 01:23:08 | 2019-10-29 01:23:08 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

枚举

列出所有选项

mysql> create table t9(
    -> id int auto_increment primary key,
    -> gender enum("male","female")
    -> )charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t9 (gender) values ("male");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9 (gender) values ("female");
Query OK, 1 row affected (0.00 sec)

# 只能插入枚举类型的数据
mysql> insert into t9 (gender) values ("femal");
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> 
mysql> select * from t9;
+----+--------+
| id | gender |
+----+--------+
|  1 | male   |
|  2 | female |
+----+--------+
2 rows in set (0.00 sec)

drop table 表名

修改表名

语法:

alter table 旧表名 rename 新表名

增加字段

语法:

alter table 表名 add 字段名 列类型 [可选的参数];

上面添加的列永远是添加在最后一列之后,如果需要在指定位置添加字段的话,使用下面的语法

alter table 表名 add 字段名 列类型 [可选的参数] first;

alter table 表名 add 字段名 列类型 [可选的参数] after 字段名

修改字段

alter table 表名 modify 字段名 列类型 [可选的参数]

alter table 表名 change 旧字段名 新字段名 新列类型 [可选的参数]

删除字段

alter table 表名 drop 字段名

show tables:查看表名

复制表结构

1、 查看被复制表的创建语句:show create table 表名,然后拷贝sql语句更换表名执行

2、 create table 旧表名 like 新表名

操作表数据

增加数据,语法:

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

delete from 表名 where 条件 ;如果不加条件删除表中所有数据

truncate 表名;没有where条件,删除表中全部数据,速度比delete快

两者区别:

1、 delete删除之后再插入数据,自增id从上一次主键自增,truncate是从1开始

2、 delete删除是一行一行删除,truncate是全选删除,速度比delete快

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

语法:

select 列1,列2 from 表名 [where 条件]

select * from 表名 where id between ... and ...;between...and...取值范围是闭区间

查询去重

select distinct 列名 from 表名

四则运算

select 列表*10 from 表名

in

select * from 表名 where id in (取值范围)

like模糊查询

select * from 表名 where 列名 like 'x%'; 以x开头,%表示通配符

select * from 表名 where 列名 like '%x'; 以x结尾,%表示通配符

select * from 表名 where 列名 like '%x%' 包含x的,%表示通配符

点赞
收藏
评论区
推荐文章
Kent_Sun Kent_Sun
3年前
工作中用到的oracle SQL
OracleSQL用于记录工作中用到的oraclesql语句表新增字段表结构变动:新增字段并指定字段类型sql新增一个字段,多个字段用小括号括起来,逗号分隔altertable表名add新增字段名(类型长度);demoTSALES_APPLY_COST表中新增一个字段(CAPITAL)类型为:NU
Easter79 Easter79
2年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
御弟哥哥 御弟哥哥
3年前
mysql表和字段的操作
(3)mysql表和字段的操作(3)mysql表和字段的操作创建表createtablename(idint,studentvarchar(20));查看表结构常用describe表名;修改表名老表rename新表ALTERTABLEtb\_men
Wesley13 Wesley13
2年前
MySQL学习——操作表
MySQL学习——操作表摘要:本文主要学习了使用DDL语句操作表的方法。创建表语法1createtable表名表定义选项表选项;表定义选项用来创建定义表的结构,由列名(col\_name)、列的定义(column\_definition)以及可能的空值说明、完
Wesley13 Wesley13
2年前
mysql数据过滤
1、AND操作符:select表的字段名from对应的表名where表的字段名AND表的字段名运算符值;例子:selectprod\_id,prod\_price,prod\_namefromproductswhereven\_id1003ANDprod\_price<10;2、OR操作符:sele
Wesley13 Wesley13
2年前
MySQL创建索引
创建索引方法一:创建表时  CREATETABLE表名(字段名1数据类型完整性约束条件…,字段名2数据类型完整性约束条件…,UNIQUE|FULLTEXT|
Wesley13 Wesley13
2年前
MySQL 数据类型以及约束条件
一,创建表的完整语法:语法:createtable表名(字段名1类型(宽度)约束条件,字段名2类型(宽度)约束条件,字段名3类型(宽度)约束条件);注意:1.在同一张表中,字段名不能相同2.宽度和约束条件可选,字段名和类型
Wesley13 Wesley13
2年前
Oracle一张表中实现对一个字段不同值和总值的统计(多个count)
需求:统计WAIT\_ORDER表中的工单总数、未处理工单总数、已完成工单总数、未完成工单总数。表结构:为了举例子方便,WAIT\_ORDER表只有两个字段,分别是ID、STATUS,其中STATUS为工单的状态。1表示未处理,2表示已完成,3表示未完成总数。 SQL:  1.SELECT   2
Wesley13 Wesley13
2年前
ThinkPHP 根据关联数据查询 hasWhere 的使用实例
很多时候,模型关联后需要根据关联的模型做查询。场景:广告表(ad),广告类型表(ad\_type),现在需要筛选出广告类型表中id字段为1且广告表中status为1的列表先看关联的设置部分 publicfunctionadType(){return$thisbelongsTo('A
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究