Mysql数据类型

最新推荐文章于 2022-12-02 19:23:06 发布
最新推荐文章于 2022-12-02 19:23:06 发布
阅读量304 收藏
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_40018205/article/details/80951582
版权

Mysql数据类型

数值类型

整形

浮点型

字符类型

日期

时间

日期时间

枚举类型 enum set


Mysql内置时间函数的使用

使用时间函数获取数据给日期时间类型字段符值

Datetime与timetamp的区别

数值类型宽度数字字符类型宽度的区别

Ageint(2)

Namechar(2)

DBA-Day02

字段约束条件

修改表结构

Mysql健值

[root@host50~]# date +%y

mysql>select year(20171120);

+----------------+

|year(20171120) |

+----------------+

| 2017 |

+----------------+

1row in set (0.00 sec)


mysql>


Insertinto db1.t1 values($a,$w);

mysql>select now();

+---------------------+

|now() |

+---------------------+

|2018-06-15 09:24:56 |

+---------------------+

1row in set (0.00 sec)


mysql>


mysql>select year(now());

+-------------+

|year(now()) |

+-------------+

| 2018 |

+-------------+

1row in set (0.01 sec)


mysql>

mysql>select date(now());

+-------------+

|date(now()) |

+-------------+

|2018-06-15 |

+-------------+

1row in set (0.00 sec)


mysql>

mysql>select month(now());

+--------------+

|month(now()) |

+--------------+

| 6 |

+--------------+

1row in set (0.00 sec)


mysql>select time(now());

+-------------+

|time(now()) |

+-------------+

|09:26:34 |

+-------------+

1row in set (0.00 sec)


mysql>


mysql>select sleep(2);

+----------+

|sleep(2) |

+----------+

| 0 |

+----------+

1row in set (2.00 sec)


mysql>

mysql>select curdate();

+------------+

|curdate() |

+------------+

|2018-06-15 |

+------------+

1row in set (0.00 sec)


mysql>

mysql>desc t7;

+----------+----------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+----------+----------+------+-----+---------+-------+

|name | char(15) | YES | | NULL | |

|syear | year(4) | YES | | NULL | |

|birthday | date | YES | | NULL | |

|up_class | time | YES | | NULL | |

|party | datetime | YES | | NULL | |

+----------+----------+------+-----+---------+-------+

5rows in set (0.00 sec)


mysql>insert into t7values("jing",year(19901120),date(now()),083000,now());

QueryOK, 1 row affected (0.32 sec)


mysql>select * from t7;

+------+-------+------------+----------+---------------------+

|name | syear | birthday | up_class | party |

+------+-------+------------+----------+---------------------+

|yaya | 1990 | 2018-11-20 | 08:30:00 | 2018-06-18 21:30:00 |

|yaya | 2053 | 2018-11-20 | 08:30:00 | 2018-11-18 21:30:00 |

|yaya | 1953 | 2018-11-20 | 08:30:00 | 2018-11-18 21:30:00 |

|jing | 1990 | 2018-06-15 | 08:30:00 | 2018-06-15 09:37:31 |

+------+-------+------------+----------+---------------------+

4rows in set (0.00 sec)


mysql>


mysql>create table t8(

->name char(5),

->meetting datetime,

->party timestamp);

QueryOK, 0 rows affected (0.21 sec)


mysql>insert into t8 values("bob",now(),now());

QueryOK, 1 row affected (0.09 sec)


mysql>select * from t8;

+------+---------------------+---------------------+

|name | meetting | party |

+------+---------------------+---------------------+

|bob | 2018-06-15 09:46:58 | 2018-06-15 09:46:58 |

+------+---------------------+---------------------+

1row in set (0.00 sec)


mysql>insert into t8(name,meetting) values("lucy",20191120214858);

QueryOK, 1 row affected (0.04 sec)


mysql>insert into t8(name,party) values("lucy",20151120214858);

QueryOK, 1 row affected (0.10 sec)


mysql>desc t8;

+----------+-----------+------+-----+-------------------+-----------------------------+

|Field | Type | Null | Key | Default | Extra |

+----------+-----------+------+-----+-------------------+-----------------------------+

|name | char(5) | YES | | NULL | |

|meetting | datetime | YES | | NULL | |

|party | timestamp | NO | | CURRENT_TIMESTAMP | on updateCURRENT_TIMESTAMP |

+----------+-----------+------+-----+-------------------+-----------------------------+

3rows in set (0.00 sec)


mysql>



mysql>select * from t8;

+------+---------------------+---------------------+

|name | meetting | party |

+------+---------------------+---------------------+

|bob | 2018-06-15 09:46:58 | 2018-06-15 09:46:58 |

|lucy | 2019-11-20 21:48:58 | 2018-06-15 09:51:55 |

|lucy | NULL | 2015-11-20 21:48:58 |

+------+---------------------+---------------------+

3rows in set (0.00 sec)


mysql>

数值类型宽度数字字符类型宽度的区别


mysql>create table t12(

->name char(5),

->level int(3) zerofill,

->money int(5) zerofill

->);

QueryOK, 0 rows affected (0.22 sec)

mysql>insert into t12 values("zbj",8,29);

QueryOK, 1 row affected (0.05 sec)


mysql>insert into t12 values("ts",888,2222229);

QueryOK, 1 row affected (0.04 sec)


mysql>insert into t12 values("fz",88888888,2222222229);

QueryOK, 1 row affected (0.04 sec)

mysql>select * from t12;

+------+----------+------------+

|name | level | money |

+------+----------+------------+

|swk | 018 | 00029 |

|zbj | 008 | 00029 |

|ts | 888 | 2222229 |

|fz | 88888888 | 2222222229 |

+------+----------+------------+

4rows in set (0.00 sec)


DBA-Day02

字段约束条件

修改表结构

MySQL键值


Field Type Null Key Default Extra

YES



Null:nullnot null

Default:0默认为null

mysql>insert into t12(name) values("bjm");

QueryOK, 1 row affected (0.05 sec)


mysql>select * from t12;

+------+----------+------------+

|name | level | money |

+------+----------+------------+

|swk | 018 | 00029 |

|zbj | 008 | 00029 |

|ts | 888 | 2222229 |

|fz | 88888888 | 2222222229 |

|bjm | NULL | NULL |

+------+----------+------------+

5rows in set (0.01 sec)


mysql>

mysql>create table t13( name char(5) not null, level int(3) zerofilldefault 0, money tinyint(2) zerofill default 0 );

额外设置

Extra i++

mysql>desc t13;

+-------+------------------------------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+-------+------------------------------+------+-----+---------+-------+

|name | char(5) | NO | | NULL | |

|level | int(3) unsigned zerofill | YES | | 000 | |

|money | tinyint(2) unsigned zerofill | YES | | 00 | |

+-------+------------------------------+------+-----+---------+-------+

3rows in set (0.00 sec)

mysql>insert into t14(name) values("bob");

mysql>insert into t14 values(null,null,null);

ERROR1048 (23000): Column 'name' cannot be null

mysql>insert into t14 values("null",null,null);

QueryOK, 1 row affected (0.06 sec)


mysql>insert into t14 values ("",null,null);

QueryOK, 1 row affected (0.04 sec)


mysql>

mysql>create table t15( name char(5) not null default "", levelint(3) zerofill default 0, money tinyint(2) zerofill default 0 );

QueryOK, 0 rows affected (0.20 sec)


mysql>insert into t15 values();

QueryOK, 1 row affected (0.05 sec)


mysql>select * from t15;

+------+-------+-------+

|name | level | money |

+------+-------+-------+

| | 000 | 00 |

+------+-------+-------+

1row in set (0.00 sec)


mysql>


修改表结构

Altertable 表名 执行动作；

Add字段

mysql>Alter table t15 Add Email varchar(30) default "stu@tedu.cn",Add tel char(11);

mysql>select * from t15;

+------+-------+-------+-------------+------+

|name | level | money | Email | tel |

+------+-------+-------+-------------+------+

| | 000 | 00 | stu@tedu.cn | NULL |

+------+-------+-------+-------------+------+

1row in set (0.00 sec)


mysql>


Altertable t15

Addstu_id char(9) first;

mysql>Alter table t15

->Add stu_id char(9) first;

QueryOK, 0 rows affected (0.48 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>select * from t15;

+--------+------+-------+-------+-------------+------+

|stu_id | name | level | money | Email | tel |

+--------+------+-------+-------+-------------+------+

|NULL | | 000 | 00 | stu@tedu.cn | NULL |

+--------+------+-------+-------+-------------+------+

1row in set (0.00 sec)


mysql>


mysql>Alter table t15 Add sex enum("boy","gril","no")default "no" after name;

QueryOK, 0 rows affected (0.39 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>select * from t15;

+--------+------+------+-------+-------+-------------+------+

|stu_id | name | sex | level | money | Email | tel |

+--------+------+------+-------+-------+-------------+------+

|NULL | | no | 000 | 00 | stu@tedu.cn | NULL |

+--------+------+------+-------+-------+-------------+------+

1row in set (0.00 sec)


mysql>

mysql>alter table t15 modify stu_id varchar(10);

mysql>alter table t15 modify name char(10) not null;

mysql>alter table t15 modify tel char(11) after name;

QueryOK, 0 rows affected (0.43 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>desc t15;

+--------+------------------------------+------+-----+-------------+-------+

|Field | Type | Null | Key | Default |Extra |

+--------+------------------------------+------+-----+-------------+-------+

|stu_id | varchar(10) | YES | | NULL | |

|name | char(10) | NO | | NULL | |

|tel | char(11) | YES | | NULL | |

|sex | enum('boy','gril','no') | YES | | no | |

|level | int(3) unsigned zerofill | YES | | 000 | |

|money | tinyint(2) unsigned zerofill | YES | | 00 | |

|Email | varchar(30) | YES | | stu@tedu.cn | |

+--------+------------------------------+------+-----+-------------+-------+

7rows in set (0.00 sec)


mysql>


mysql>alter table t15 drop stu_id,drop tel;

QueryOK, 0 rows affected (0.41 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>

mysql>alter table t15

->change email mail varchar(30) default "stu@tedu.cn";

QueryOK, 0 rows affected (0.06 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>select * from t15;

+------+------+-------+-------+-------------+

|name | sex | level | money | mail |

+------+------+-------+-------+-------------+

| | no | 000 | 00 | stu@tedu.cn |

+------+------+-------+-------+-------------+

1row in set (0.00 sec)


mysql>


mysql>alter table t15 change mail Email varchar(50) default "stu@163.com";

QueryOK, 0 rows affected (0.08 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>

mysql>desc t15;

+-------+------------------------------+------+-----+-------------+-------+

|Field | Type | Null | Key | Default |Extra |

+-------+------------------------------+------+-----+-------------+-------+

|name | char(10) | NO | | NULL | |

|sex | enum('boy','gril','no') | YES | | no | |

|level | int(3) unsigned zerofill | YES | | 000 | |

|money | tinyint(2) unsigned zerofill | YES | | 00 | |

|Email | varchar(50) | YES | | stu@163.com | |

+-------+------------------------------+------+-----+-------------+-------+

5rows in set (0.00 sec)


mysql>


mysql>alter table t15 rename stuinfo;

QueryOK, 0 rows affected (0.10 sec)


mysql>show tables;

+---------------+

|Tables_in_db1 |

+---------------+

|stuinfo |

|t12 |

|t13 |

|t14 |

|t2 |

|t3 |

|t4 |

|t5 |

|t6 |

|t7 |

|t8 |

+---------------+

11rows in set (0.00 sec)


mysql>


[root@host50~]# cd /var/lib/mysql/db1

[root@host50db1]# ls *stuinfo

ls:无法访问*stuinfo:没有那个文件或目录

[root@host50db1]# ls

db.opt t12.frm t13.ibd t2.frm t3.ibd t5.frm t6.ibd t8.frm

stuinfo.frm t12.ibd t14.frm t2.ibd t4.frm t5.ibd t7.frm t8.ibd

stuinfo.ibd t13.frm t14.ibd t3.frm t4.ibd t6.frm t7.ibd

[root@host50db1]# ls stuinfo.*

stuinfo.frm stuinfo.ibd

[root@host50db1]#


Ctrl-

Ctrl+shift+


Descmysql.user;

Mysql键值

普通索引 index *

唯一索引 unique

主键 primary key *

外键 foreign key *

全文索引 fulltext

使用规则 创建 删除 查看


把常见的查询的字段作为索引

INDEX字段key的标志为MUL字段可以为空和重复

mysql>show index from t14;

Emptyset (0.00 sec)


mysql>



BTREE二叉树算法

1-10

1-5 6-10

1-2.5 2.6-5


Createtable t16(

Namechar(10),

Ageint(2),

Classchar(7),

Index(name),

Index(class)

);

Desct16;

mysql>desc t16;

+-------+----------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

|Name | char(10) | YES | MUL | NULL | |

|Age | int(2) | YES | | NULL | |

|Class | char(7) | YES | MUL | NULL | |

+-------+----------+------+-----+---------+-------+

3rows in set (0.00 sec)


mysql>


Dropindex class on t16;

Showindex from t16\G;


mysql>Show index from t16\G;

***************************1. row ***************************

Table: t16

Non_unique: 1

Key_name: Name

Seq_in_index: 1

Column_name: Name

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1row in set (0.00 sec)


ERROR:

Noquery specified


mysql>



mysql>desc stuinfo;

+-------+------------------------------+------+-----+-------------+-------+

|Field | Type | Null | Key | Default |Extra |

+-------+------------------------------+------+-----+-------------+-------+

|name | char(10) | NO | | NULL | |

|sex | enum('boy','gril','no') | YES | | no | |

|level | int(3) unsigned zerofill | YES | | 000 | |

|money | tinyint(2) unsigned zerofill | YES | | 00 | |

|Email | varchar(50) | YES | | stu@163.com | |

+-------+------------------------------+------+-----+-------------+-------+

5rows in set (0.00 sec)


mysql>show index from stuinfo;

Emptyset (0.00 sec)


mysql>select * from stuinfo;

+------+------+-------+-------+-------------+

|name | sex | level | money | Email |

+------+------+-------+-------+-------------+

| | no | 000 | 00 | stu@tedu.cn |

+------+------+-------+-------+-------------+

1row in set (0.00 sec)


mysql>

mysql>create index name on stuinfo(name);

QueryOK, 0 rows affected (0.23 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>desc stuinfo;

+-------+------------------------------+------+-----+-------------+-------+

|Field | Type | Null | Key | Default |Extra |

+-------+------------------------------+------+-----+-------------+-------+

|name | char(10) | NO | MUL | NULL | |

|sex | enum('boy','gril','no') | YES | | no | |

|level | int(3) unsigned zerofill | YES | | 000 | |

|money | tinyint(2) unsigned zerofill | YES | | 00 | |

|Email | varchar(50) | YES | | stu@163.com | |

+-------+------------------------------+------+-----+-------------+-------+

5rows in set (0.00 sec)


mysql>

mysql>create index xxxx on stuinfo(sex);

QueryOK, 0 rows affected (0.20 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>show index from stuinfo\G;

***************************1. row ***************************

Table: stuinfo

Non_unique: 1

Key_name: name

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 1

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

***************************2. row ***************************

Table: stuinfo

Non_unique: 1

Key_name: xxxx

Seq_in_index: 1

Column_name: sex

Collation: A

Cardinality: 1

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

2rows in set (0.00 sec)


ERROR:

Noquery specified


mysql>

mysql>desc t16;

+-------+----------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

|Name | char(10) | YES | MUL | NULL | |

|Age | int(2) | YES | | NULL | |

|Class | char(7) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3rows in set (0.00 sec)


mysql>create index class on t16(class);

QueryOK, 0 rows affected (0.13 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>desc;

ERROR1064 (42000): You have an error in your SQL syntax; check the manualthat corresponds to your MySQL server version for the right syntax touse near '' at line 1

mysql>desc t16;

+-------+----------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

|Name | char(10) | YES | MUL | NULL | |

|Age | int(2) | YES | | NULL | |

|Class | char(7) | YES | MUL | NULL | |

+-------+----------+------+-----+---------+-------+

3rows in set (0.00 sec)


mysql>

经常作为查询条件的字段作为索引 （默认可以为空值与重复，加快查询速度）


Primarykey主键（不能重复且不为空值）如：身份证号

一个表中只能有一个主键

如果有多个字段都作为主键称为复合主键

Createtable t17(

Stu_idchar(9) primary key,

Name char(10)

);



+--------+----------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

|Stu_id | char(9) | NO | PRI | NULL | |

|Name | char(10) | YES | | NULL | |

+--------+----------+------+-----+---------+-------+

2rows in set (0.00 sec)


mysql>

mysql>insert into t17 values("nsd1803","bob");

QueryOK, 1 row affected (0.04 sec)


mysql>insert into t17 values("nsd1803","tom");

ERROR1062 (23000): Duplicate entry 'nsd1803' for key 'PRIMARY'

mysql>

mysql>insert into t17 values(null,"tom");

ERROR1048 (23000): Column 'Stu_id' cannot be null

mysql>


Createtable t18(

Stu_idchar(9) ,

Name char(10),

Primarykey(Stu_id)

);


mysql>alter table t17 drop primary key;

QueryOK, 2 rows affected (0.61 sec)

Records:2 Duplicates: 0 Warnings: 0


mysql>desc t17;

+--------+----------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

|Stu_id | char(9) | NO | | NULL | |

|Name | char(10) | YES | | NULL | |

+--------+----------+------+-----+---------+-------+

2rows in set (0.00 sec)


mysql>


altertable t17 add primary key(stu_id);

mysql>desc mysql.db;

+-----------------------+---------------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra|

+-----------------------+---------------+------+-----+---------+-------+

|Host | char(60) | NO | PRI | | |

|Db | char(64) | NO | PRI | | |

|User | char(32) | NO | PRI | | |

|Select_priv | enum('N','Y') | NO | | N | |

|Insert_priv | enum('N','Y') | NO | | N | |

|Update_priv | enum('N','Y') | NO | | N | |

|Delete_priv | enum('N','Y') | NO | | N | |

|Create_priv | enum('N','Y') | NO | | N | |

|Drop_priv | enum('N','Y') | NO | | N | |

|Grant_priv | enum('N','Y') | NO | | N | |

|References_priv | enum('N','Y') | NO | | N | |

|Index_priv | enum('N','Y') | NO | | N | |

|Alter_priv | enum('N','Y') | NO | | N | |

|Create_tmp_table_priv | enum('N','Y') | NO | | N | |

|Lock_tables_priv | enum('N','Y') | NO | | N | |

|Create_view_priv | enum('N','Y') | NO | | N | |

|Show_view_priv | enum('N','Y') | NO | | N | |

|Create_routine_priv | enum('N','Y') | NO | | N | |

|Alter_routine_priv | enum('N','Y') | NO | | N | |

|Execute_priv | enum('N','Y') | NO | | N | |

|Event_priv | enum('N','Y') | NO | | N | |

|Trigger_priv | enum('N','Y') | NO | | N | |

+-----------------------+---------------+------+-----+---------+-------+

22rows in set (0.00 sec)


mysql>


复合主键 (不能同时相同)

PRI PRI

cip serport status

1.1.1.1 22 no

1.1.1.1 21 yes

2.1.1.1 22 yes


create table t19(

cip char(15),

serport smallint(2),

status enum(“yes”,”no”),

primarykey(cip,serport)

);


复合主键要一起创建与SQL执行顺序有关


mysql>insert into t19 values("1.1.1.1",22,"no");

ERROR1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'

mysql>insert into t19 values("1.1.1.1",22,"yes");

ERROR1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'

mysql>insert into t19 values("1.1.1.1",21,"yes");

QueryOK, 1 row affected (0.03 sec)


mysql>insert into t19 values("1.1.1.2",21,"yes");

QueryOK, 1 row affected (0.05 sec)


mysql>

mysql>select * from t19;

+---------+---------+--------+

|cip | serport | status |

+---------+---------+--------+

|1.1.1.1 | 21 | yes |

|1.1.1.1 | 22 | no |

|1.1.1.2 | 21 | yes |

+---------+---------+--------+

3rows in set (0.00 sec)


mysql>

mysql>alter table t19 drop primary key(cip);

ERROR1064 (42000): You have an error in your SQL syntax; check the manualthat corresponds to your MySQL server version for the right syntax touse near '(cip)' at line 1

mysql>alter table t19 drop primary key;

QueryOK, 3 rows affected (0.68 sec)

Records:3 Duplicates: 0 Warnings: 0


mysql>



mysql>desc t19;

+---------+------------------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+---------+------------------+------+-----+---------+-------+

|cip | char(15) | NO | | NULL | |

|serport | smallint(2) | NO | | NULL | |

|status | enum('yes','no') | YES | | NULL | |

+---------+------------------+------+-----+---------+-------+

3rows in set (0.00 sec)


mysql>



mysql>insert into t19 values("1.1.1.1",22,"no");

QueryOK, 1 row affected (0.02 sec)


mysql>insert into t19 values("1.1.1.1",22,"no");

QueryOK, 1 row affected (0.07 sec)


mysql>insert into t19 values("1.1.1.1",null,"no");

ERROR1048 (23000): Column 'serport' cannot be null

mysql>


mysql>delete from t19;

QueryOK, 6 rows affected (0.06 sec)


mysql>select * from t19;

Emptyset (0.00 sec)


mysql>


mysql>alter table t19 add primary key(cip,serport);

QueryOK, 0 rows affected (0.58 sec)

Records:0 Duplicates: 0 Warnings: 0


mysql>desc t19;

+---------+------------------+------+-----+---------+-------+

|Field | Type | Null | Key | Default | Extra |

+---------+------------------+------+-----+---------+-------+

|cip | char(15) | NO | PRI | NULL | |

|serport | smallint(2) | NO | PRI | NULL | |

|status | enum('yes','no') | YES | | NULL | |

+---------+------------------+------+-----+---------+-------+

3rows in set (0.01 sec)


mysql>


AUTO_INCREMENT 字段的值自增长 ++ （必须是主键与数值类型）

createtable t20(

stu_idint(2) primary key

auto_increment,

namechar(10),

agetinyint(2)

);


mysql>desc t20;

+--------+------------+------+-----+---------+----------------+

|Field | Type | Null | Key | Default | Extra |

+--------+------------+------+-----+---------+----------------+

|stu_id | int(2) | NO | PRI | NULL | auto_increment |

|name | char(10) | YES | | NULL | |

|age | tinyint(2) | YES | | NULL | |

+--------+------------+------+-----+---------+----------------+

3rows in set (0.00 sec)


mysql>

mysql>insert into t20(name,age) values("bob",19);

QueryOK, 1 row affected (0.30 sec)


mysql>



mysql>insert into t20(name,age) values("lucy",19);

QueryOK, 1 row affected (0.04 sec)


mysql>insert into t20(name,age) values("jerryy",19);

QueryOK, 1 row affected (0.04 sec)


mysql>select * from t20;

+--------+--------+------+

|stu_id | name | age |

+--------+--------+------+

| 1 | bob | 19 |

| 2 | lucy | 19 |

| 3 | jerryy | 19 |

+--------+--------+------+

3rows in set (0.00 sec)


mysql>


mysql>insert into t20 values(1,"jerryy",19);

ERROR1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql>insert into t20 values(null,"jerryy",19);

QueryOK, 1 row affected (0.06 sec)


mysql>

mysql>select * from t20;

+--------+--------+------+

|stu_id | name | age |

+--------+--------+------+

| 1 | bob | 19 |

| 2 | lucy | 19 |

| 3 | jerryy | 19 |

| 4 | jerryy | 19 |

+--------+--------+------+

4rows in set (0.00 sec)


mysql>


mysql>insert into t20(name,age) values("jack",19);

QueryOK, 1 row affected (0.04 sec)


mysql>


经常把行号字段设置为主键


mysql>select * from stuinfo;

+------+------+-------+-------+-------------+

|name | sex | level | money | Email |

+------+------+-------+-------+-------------+

| | no | 000 | 00 | stu@tedu.cn |

+------+------+-------+-------+-------------+

1row in set (0.00 sec)


mysql>alter table stuinfo

-> add

-> id int(2)primary key auto_increment

-> first;

QueryOK, 0 rows affected (0.52 sec)

Records:0 Duplicates: 0 Warnings: 0

乐于技术分享
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
MySQL 数据类型
Linux的成长历程
06-02 145
3.MySQL 数据类型 问题 本案例要求熟悉MySQL的字段数据类型、时间函数的使用，完成以下任务操作： 在home库里创建famliy表，表结构、字段类型自定义 练习各种时间函数的使用 步骤 实现此案例需要按照如下步骤进行。 步骤一：创建home库、family表 1）新建home库，并切换到home库 mysql> CREATE DATABASE home; Query OK, 1...
MySQL三大数据类型
weixin_34032827的博客
11-25 235
转载于:https://www.cnblogs.com/huuangrui/p/6103190.html
python xml.dom模块解析xml
liu201812的博客
12-11 2278
python xml.dom模块解析xml 阅读目录 什么是xml？有何特征？ 获得标签属性 获得子标签 获得标签属性值 获得标签对之间的数据 例子 总结 回到顶部 什么是xml？有何特征？ xml即可扩展标记语言，它可以用来标记数据、定义数据类型，是一种允许用户对自己的标记语言进行定义的源语言。 例子：del.xml 复制代码 <?xml version="1.0" encoding="utf-8"?> <catalog> <maxid>4</maxi
redis数据类型
weixin_42816196的博客
07-31 405
String 字符串 192.168.4.51:6351&gt; set sex gril [EX seconds] [PX milliseconds] [NX|XX] – 设置 key 及值,过期时间可以设置为秒或毫秒为单位 – nx 只有 key 不存在,才对 key 进行操作 – xx 只有 key 已存在,才对 key 进行操作 -EX 代表有效期，时间为秒   192.16...
Second season twelfth and thirteenth episode,what happened in the search for Marcel?
热门推荐
xiyangxia666hui的博客
09-20 2万+
[Scene: In a TV commercial that the gang is watching at Monica and Rachel's.] [A guy is sitting at his desk and his boss comes in and drops a huge pile of papers on his desk. The guy looks dejected.] COMMERCIAL VOICEOVER: Can't get the monkey off your back
redis数据类型_万金油_新浪博客
weixin_42816196的博客
07-31 189
String 字符串 192.168.4.51:6351> set sex gril [EX seconds] [PX milliseconds] [NX|XX] – 设置 key 及值,过期时间可以设置为秒或毫秒为单位 – nx 只有 key 不存在,才对 key 进行操作 – xx 只有 key 已存在,才对 key 进行操作 -EX 代表有效期，时间为秒 192.168...
Java数据类型与MySql数据类型对照表
12-16
本文讲述了Java数据类型与MySql数据类型对照表。分享给大家供大家参考，具体如下： 类型名称 显示长度 数据库类型 JAVA类型 JDBC类型索引(int) VARCHAR L+N VARCHAR java.lang.String 12 CHAR N CHAR java....
mysql数据类型
12-11
mysql中数据类型详解,各种数据类型注意事项！
mysql数据类型默认值_MySQL数据类型 - 数据类型默认值
weixin_31272749的博客
02-02 1629
数据类型默认值数据类型规范可以有显式或隐式的默认值。数据类型规范中的DEFAULT值子句显式指示列的默认值。示例：SERIAL DEFAULT VALUE是一种特殊情况。在整数列的定义中，它是NOT NULL AUTO_INCREMENT UNIQUE的别名。处理显式DEFAULT子句，某些方面依赖于特定版本，如下所述。●MySQL 8.0.13处理显式默认值●MySQL8.0.13之前处理显式默...
jasperreport5.5.0
11-05
解决jasperreport在linux系统无法正常显示宋体的问题。 java报表抛出net.sf.jasperreports.engine.util.JRFontNotFoundException: Font '宋体' is not available to the JVM. See the Javadoc for more details.
MySQL数据类型和常用字段属性总结
01-19
MySQL提供了一组可以赋给表中各个列的数据类型，每个类型都强制数据满足为该数据类型预先确定的一组规则，例如大小、类型及格式。 这里先总结数据类型。MySQL中的数据类型大的方面来分，可以分为：日期和时间、数值...
C#之$.extend详解
龙傲天的博客
09-13 1771
一、可以将所得的结果全部合并在{}中，并返回，而且还不会破坏原有的项的结构。 示例： Var item={name:”olive”,age:23}; Var item1={name:”Momo”,sex:”gril”}; Var result=$.extend({},item,item1); 结果： Result={name:”Momo”,age:23,sex:”gril”}; 二、...
$.extend()用法
weixin_30724853的博客
08-27 325
1 extend(result,item1,item2…..) 这里这个方法主要用来合并，将所有的参数项都合并result中，并返回result，但是这 样就会破坏result的结构。 2 extend({},item1,item2,……) 用这个方法，可以将所得的结果全部合并在{}中，并返回，而且还不会破坏原有的项的结构。 示例： Var item={name:”olive”,age...
Mysql数据库中使用alter对表中的字段，数据类型进行修改
长街yu巷的博客
01-06 7304
Mysql数据库中使用alter对表中的字段，数据类型进行修改 MySQL数据库中我们经常对表中的字段和字段属性进行修改。 我们在进行修改表时用到的命令语句如下： alter table 表名 [add/rename/drop/modify/change]... 1、修改表名 alter table 表名 rename [to] 新表名; 2、添加列/字段 alter table 表名 add 字段 数据类型; 3、修改字段名 alter table 表名 change 旧字段名 新字段
mysql基础(12)_列属性之NULL、Default、Comment
no15870678621的博客
01-09 9331
列属性：列属性又称之为字段属性，在mysql中一共有6个属性:null，默认值，列描述，主键，唯一键和自动增长 NULL属性 null属性：代表字段为空 如果对应的值为YES表示该字段可以为NULL mysql> desc int_data; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key |
【MySQL基础】常用指令详解
我的主页
12-02 1556
本期讲解：show查看mysql中有哪些数据库、create创建数据库、use使用数据库、show tables展示数据库中的表、select * from查看表中的信息、数据库基本单元、显示表的结构、显示表中信息。
mysql select 类型_MySQL基本select查询及数据类型
weixin_35652867的博客
01-19 1385
基本select查询知识要点设置系统变量@@sql_mode基本SELECT语句在SELECT语句中的算术表达式和null值列别名使用串联操作符和distinct关键字desc命令增删改查对数据库常规的四个操作插入：insert删除：delete修改：updata查询：selectselect查询简介查询对象有四个1.全表查询select * from PLAYERS; #全表查询PLAYE...
Linux网络服务器配置——认证网站某目录的方法
u013099529的专栏
06-14 383
用户账号保存到认证用户文件中。用户账号也可以保存到MySQL的表中。试参照/etc/httpd/conf.d/auth_mysql.conf第8-38行，做实验： 1、在文档根目录下创建lab目录，在lab下创建主页，内容自定。(5分) 2、以MySQL的root用户，创建数据库auth，在该库中创建表users。(10分) 3、把数据库auth中表users授权给用户lili，权限sele
MYSQL 数据类型
最新发布
04-22
MySQL是一种常用的关系型数据库管理系统支持多种数据类型。以下是MySQL中常见的数据类型： 1. 整数类型（INT）：用于存储数值，可以指定长度和有无符号。 2. 小数类型（DECIMAL）：用于存储精确的小数值，可以指定...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

乐于技术分享

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值