MySQL操作表和表记录

Wesley13
• 阅读 811

[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的，%表示通配符

mysql运维开发运维开发
点赞
收藏
评论区
推荐文章
Easter79 Easter79
•
3年前
sql注入
反引号是个比较特别的字符，下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用，不过使用范围只于表名、数据库名、字段名、起别名这些场景，下面具体说下1）表名payload：select\from\users\whereuser\_id1limit0,1;!(https://o
788
0
0
Kent_Sun Kent_Sun
•
3年前
工作中用到的oracle SQL
OracleSQL用于记录工作中用到的oraclesql语句表新增字段表结构变动：新增字段并指定字段类型sql新增一个字段,多个字段用小括号括起来,逗号分隔altertable表名add新增字段名(类型长度);demoTSALES_APPLY_COST表中新增一个字段(CAPITAL)类型为:NU
1524
0
0
御弟哥哥 御弟哥哥
•
3年前
mysql表和字段的操作
(3)mysql表和字段的操作(3)mysql表和字段的操作创建表createtablename(idint,studentvarchar(20));查看表结构常用describe表名;修改表名老表rename新表ALTERTABLEtb\_men
1366
0
0
Wesley13 Wesley13
•
3年前
MySQL学习——操作表
MySQL学习——操作表摘要：本文主要学习了使用DDL语句操作表的方法。创建表语法1createtable表名表定义选项表选项;表定义选项用来创建定义表的结构，由列名（col\_name）、列的定义（column\_definition）以及可能的空值说明、完
759
0
0
Wesley13 Wesley13
•
3年前
mysql数据过滤
1、AND操作符：select表的字段名from对应的表名where表的字段名AND表的字段名运算符值；例子：selectprod\_id,prod\_price,prod\_namefromproductswhereven\_id1003ANDprod\_price<10;2、OR操作符：sele
748
0
0
Wesley13 Wesley13
•
3年前
MySQL创建索引
创建索引方法一：创建表时　　CREATETABLE表名(字段名1数据类型完整性约束条件…,字段名2数据类型完整性约束条件…,UNIQUE|FULLTEXT|
712
0
0
Wesley13 Wesley13
•
3年前
MySQL 数据类型以及约束条件
一，创建表的完整语法：语法：createtable表名(字段名1类型(宽度)约束条件,字段名2类型(宽度)约束条件,字段名3类型(宽度)约束条件);注意：1.在同一张表中，字段名不能相同2.宽度和约束条件可选，字段名和类型
605
0
0
Wesley13 Wesley13
•
3年前
Oracle一张表中实现对一个字段不同值和总值的统计（多个count）
需求：统计WAIT\_ORDER表中的工单总数、未处理工单总数、已完成工单总数、未完成工单总数。表结构：为了举例子方便，WAIT\_ORDER表只有两个字段，分别是ID、STATUS，其中STATUS为工单的状态。1表示未处理，2表示已完成，3表示未完成总数。 SQL：  1.SELECT   2
1007
0
0
Wesley13 Wesley13
•
3年前
ThinkPHP 根据关联数据查询 hasWhere 的使用实例
很多时候，模型关联后需要根据关联的模型做查询。场景：广告表（ad），广告类型表(ad\_type)，现在需要筛选出广告类型表中id字段为1且广告表中status为1的列表先看关联的设置部分 publicfunctionadType(){return$thisbelongsTo('A
1171
0
0
京东云开发者 京东云开发者
•
1年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
1040
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
20
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？