mysql-linux-2-白红宇的个人博客

mysql-linux-2

发布日期：2021-06-29 06:55:48 浏览次数：2 分类：技术文章

本文共 27256 字，大约阅读时间需要 90 分钟。

上午：

关于日期时间字段

-当未给TIMESTAMP字段赋值时，自动以当前系统时间赋值，而DATATIME字段默认赋值为NULL

YEAR年份的处理

-默认用4位数字表示

-当只用2位数字赋值时，01～69视为2000～2069，而70～99视为1970～1999

mysql> insert into

mysql> create table t21(

-> meetting datetime,

-> party timestamp

-> );

Query OK, 0 rows affected (0.47 sec)

mysql> select * from t21;

Empty set (0.00 sec)

mysql> insert into t21 values(20180524091358,20170624091358);

Query OK, 1 row affected (0.05 sec)

mysql> insert into t21 values();

Query OK, 1 row affected (0.04 sec)

mysql> select * from t21;

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

| meetting | party |

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

| 2018-05-24 09:13:58 | 2017-06-24 09:13:58 |

| NULL | 2018-06-26 14:32:20 |

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

2 rows in set (0.00 sec)

mysql> insert into t21 values(now(),now());

Query OK, 1 row affected (0.08 sec)

mysql> insert into t21(meetting) values(20191224091358);

Query OK, 1 row affected (0.03 sec)

约束条件的作用：限制如何给字段复制

NULL 容许为空，默认设置

NOT NULL 不容许为空

KEY 索引

Default 设置默认值，缺省为NULL

mysql> create table t4(name char(15) not null default "",age tinyint(2) unsigned default 16,class char(7) default "nsd1802",sex enum("b","g"));

Query OK, 0 rows affected (0.30 sec)

mysql> desc t4

-> ;

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

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

| age | tinyint(2) unsigned | YES | | 16 | |

| class | char(7) | YES | | nsd1802 | |

| sex | enum('b','g') | YES | | NULL | |

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

4 rows in set (0.00 sec)

mysql> insert into t23(name ,sex)values("bob","b");

ERROR 1146 (42S02): Table 'dog.t23' doesn't exist

mysql> insert into t4(name ,sex)values("bob","b");

Query OK, 1 row affected (0.09 sec)

mysql> select * from t4;

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

| name | age | class | sex |

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

| bob | 16 | nsd1802 | b |

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

1 row in set (0.00 sec)

mysql> insert into t4(age)values(13);

Query OK, 1 row affected (0.09 sec)

mysql> select * from t4;

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

| name | age | class | sex |

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

| bob | 16 | nsd1802 | b |

| | 13 | nsd1802 | NULL |

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

2 rows in set (0.00 sec)

修改表结构

alter table 表名执行动作;

mysql> use dog

Database changed

mysql> show tables;

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

| Tables_in_dog |

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

| dogperson |

| t1 |

| t2 |

| t21 |

| t3 |

| t4 |

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

6 rows in set (0.00 sec)

mysql> alter table t1 rename dogt1;

Query OK, 0 rows affected (0.36 sec)

mysql> alter table dogperson rename qrinfo;

Query OK, 0 rows affected (0.10 sec)

mysql> show tables;

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

| Tables_in_dog |

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

| dogt1 |

| qrinfo |

| t2 |

| t21 |

| t3 |

| t4 |

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

6 rows in set (0.00 sec)

[root@localhost mysql]# ls

@5b66@751f@5e93 db1 ib_logfile1 private_key.pem

auto.cnf db2 ibtmp1 public_key.pem

ca-key.pem dog mysql server-cert.pem

ca.pem ib_buffer_pool mysql.sock server-key.pem

client-cert.pem ibdata1 mysql.sock.lock studb

client-key.pem ib_logfile0 performance_schema sys

[root@localhost mysql]# cd dog

[root@localhost dog]# ls

db.opt dogperson.ibd dogt1.ibd t21.ibd t2.ibd t3.ibd t4.ibd

dogperson.frm dogt1.frm t21.frm t2.frm t3.frm t4.frm

[root@localhost dog]# ls

db.opt dogt1.ibd qrinfo.ibd t21.ibd t2.ibd t3.ibd t4.ibd

dogt1.frm qrinfo.frm t21.frm t2.frm t3.frm t4.frm

mysql> alter table qrinfo add ipone char(11);

Query OK, 0 rows affected (0.35 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc qrinfo;

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

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

| age | tinyint(3) | YES | | NULL | |

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

5 rows in set (0.00 sec)

mysql> alter table qrinfo add qq char(11) first;

Query OK, 0 rows affected (0.33 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc qrinfo;

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

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

| qq | char(11) | YES | | NULL | |

| age | tinyint(3) | YES | | NULL | |

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

6 rows in set (0.00 sec)

mysql> alter table qrinfo add llike set("eat","buy","game") default "eat", add sex2 enum("boy","girl","no") default "no" after name;

Query OK, 0 rows affected (0.38 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc qrinfo'

'> ;

'> desc qrinfo;

'> ^C

mysql> desc qrinfo

-> '

'> ^C

mysql> desc qrinfo;

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

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

| qq | char(11) | YES | | NULL | |

| sex2 | enum('boy','girl','no') | YES | | no | |

| age | tinyint(3) | YES | | NULL | |

| llike | set('eat','buy','game') | YES | | eat | |

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

8 rows in set (0.00 sec)

mysql> desc

-> qrinfo;

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

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

| qq | char(11) | YES | | NULL | |

| sex2 | enum('boy','girl','no') | YES | | no | |

| age | tinyint(3) | YES | | NULL | |

| llike | set('eat','buy','game') | YES | | eat | |

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

8 rows in set (0.00 sec)

mysql> alter table qrinfo

-> drop class,

-> drop sex;

ERROR 1091 (42000): Can't DROP 'class'; check that column/key exists

mysql> alter table qrinfo

-> drop likes,

-> drop email;

Query OK, 0 rows affected (0.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc qrinfo;

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

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

| qq | char(11) | YES | | NULL | |

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

| age | tinyint(3) | YES | | NULL | |

| llike | set('eat','buy','game') | YES | | eat | |

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

6 rows in set (0.00 sec)

mysql 键值

普通索引 * index （使用规则，查看创建删除）

主键 *

外键（难点）*

唯一索引

全文索引

键值：如何给字段值赋值，给字段数值排序

索引是什么？

索引是对记录集的多个字段进行排序的方法类是于目录

缺点

占存储空间

内容修改时候索引需要重新的更改

优点

加快查看的速度

因为查大于写

Btree 二叉树

index 普通索引

使用说明

-一个表中可以有多个index字段

字段的值容许有重复，且可以赋NULL值

经常把做查询条件的字段设置为index字段

index字段的KEY标志是MUL

修改表的时候对相应的字段赋予相应的索引

mysql> desc qrinfo

-> ;

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

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

| qq | char(11) | YES | | NULL | |

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

| age | tinyint(3) | YES | | NULL | |

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

6 rows in set (0.00 sec)

mysql> create index namea on qrinfo(name);

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc qrinfo;

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

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

| qq | char(11) | YES | | NULL | |

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

| age | tinyint(3) | YES | | NULL | |

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

6 rows in set (0.00 sec)

mysql> show Z index from qrinfo\G;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Z index from qrinfo' at line 1

ERROR:

No query specified

mysql> show index from qrinfo\G;

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

Table: qrinfo

Non_unique: 1

Key_name: namea

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

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

Table: qrinfo

Non_unique: 1

Key_name: bbb

Seq_in_index: 1

Column_name: sex

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql> drop index bbb on qrinfo;

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc qrinfo;

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

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

| qq | char(11) | YES | | NULL | |

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

| age | tinyint(3) | YES | | NULL | |

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

6 rows in set (0.00 sec)

mysql> show index from qrinfo;

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

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

| qrinfo | 1 | namea | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |

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

1 row in set (0.00 sec)

mysql> show index from qrinfo\G;

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

Table: qrinfo

Non_unique: 1

Key_name: namea

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

ERROR:

No query specified

创建表的时候对相应的值赋予相应的索引

mysql> create table t26(

-> name char(10),

-> age int(2),

-> address varchar(30),

-> index(address)

-> );

Query OK, 0 rows affected (0.22 sec)

mysql> desc t26;

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

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

| age | int(2) | YES | | NULL | |

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

3 rows in set (0.01 sec)

mysql> drop index from t26;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from t26' at line 1

mysql> show index from t26;

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

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

| t26 | 1 | address | 1 | address | A | 0 | NULL | NULL | YES | BTREE | | |

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

1 row in set (0.00 sec)

mysql> drop index address from t26;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from t26' at line 1

mysql> drop index address on t26;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from t26\G;

Empty set (0.00 sec)

ERROR:

No query specified

下午的课程

主键

一个表只能有一个primary key字段

对应的字段不容许有重复，且不容许赋NULL值

如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一起创建。

主键字段的KEY

标志是PRI

通常与AUTO——INCREMENT连用

经常把表能够唯一标示记录i的字段设置为主键字段【记录编号字段】

建表的时候创建主键

mysql> create table t27(

-> person_id char(18) primary key,

-> name char(15),

-> age int(2)

-> );

Query OK, 0 rows affected (0.30 sec)

mysql> desc t27;

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

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

| age | int(2) | YES | | NULL | |

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

3 rows in set (0.00 sec)

mysql> insert into t27 values("123","bob",19);

Query OK, 1 row affected (0.04 sec)

mysql> insert into t27 values("123","tom",29);

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

mysql> select * from t27;

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

| person_id | name | age |

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

| 123 | bob | 19 |

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

1 row in set (0.00 sec)

复合主键：多个字段复合，必须一起创建或一起删除，多个字段的值不容许同时相同

mysql> create table t29(

-> clientip char(15),

-> ser_port smallint,

-> status enum("deny","allow"),

-> primary key(clientip,ser_port)

-> );

Query OK, 0 rows affected (0.19 sec)

mysql> desc t29;

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

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

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

3 rows in set (0.01 sec)

mysql> alter table t29 drop primary key;

Query OK, 0 rows affected (0.42 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t29;

Empty set (0.00 sec)

mysql> desc t29;

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

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

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

3 rows in set (0.00 sec)

在创建表之后添加主键

mysql> insert into t29 values("2.1.1.1",22 ,"deny");

Query OK, 1 row affected (0.02 sec)

mysql> alter table t29 add primary key(clientip,ser_port);

Query OK, 0 rows affected (0.42 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t29;

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

| clientip | ser_port | status |

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

| 1.1.1.1 | 22 | deny |

| 2.1.1.1 | 22 | deny |

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

2 rows in set (0.00 sec)

AUTO_INCREMENT 作用是让字段的值自增长

数值和主键才可以设置

mysql> create table t221(

-> id int(2) zerofill primary key auto_increment,

-> name char(15),

-> sex enum("boy","girl"),

-> age tinyint(2) unsigned

-> );

Query OK, 0 rows affected (0.19 sec)

mysql> desc t221

-> ;

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

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

| id | int(2) unsigned zerofill | NO | PRI | NULL | auto_increment |

| sex | enum('boy','girl') | YES | | NULL | |

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

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

4 rows in set (0.00 sec)

mysql> insert t221(name,sex,age)values("bob","boy",19);

Query OK, 1 row affected (0.07 sec)

mysql> select * from t221;

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

| id | name | sex | age |

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

| 01 | bob | boy | 19 |

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

1 row in set (0.00 sec)

mysql> insert into t221(name,sex,age)values("lucy","girl",19);

Query OK, 1 row affected (0.04 sec)

mysql> select * from t221;

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

| id | name | sex | age |

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

| 01 | bob | boy | 19 |

| 02 | lucy | girl | 19 |

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

2 rows in set (0.00 sec)

mysql> insert t221(id,name,sex,age)values(null,"bbb","boy",29);

Query OK, 1 row affected (0.05 sec)

mysql> select * from t221;

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

| id | name | sex | age |

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

| 01 | bob | boy | 19 |

| 02 | lucy | girl | 19 |

| 03 | bbb | boy | 29 |

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

3 rows in set (0.00 sec)

mysql> select * from qr;

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

| name | sex | age |

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

| zhu | girl | 12 |

| hai | boy | 22 |

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

2 rows in set (0.00 sec)

mysql> alter table qr add id int(2) primary key auto_increment;

Query OK, 0 rows affected (0.52 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from qr;

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

| name | sex | age | id |

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

| zhu | girl | 12 | 1 |

| hai | boy | 22 | 2 |

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

2 rows in set (0.00 sec)

mysql> alter table qr modify id int(2) not null first;

Query OK, 2 rows affected (0.51 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from qr;

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

| id | name | sex | age |

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

| 1 | zhu | girl | 12 |

| 2 | hai | boy | 22 |

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

2 rows in set (0.00 sec)

外键：

约束限制字段赋值，字段赋值时，值必要要在指定表中的指定字段值范围内选择。

什么是外键？

让当前表字段的值在另一个表中字段值的范围内选择。

使用外键的条件

-表的存储引擎必须是innodb

-字段类型要一致

-被参照字段必须要是索引类型的一种（primary key）

mysql> select * from qr;

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

| name | sex | age |

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

| zhu | girl | 12 |

| hai | boy | 22 |

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

2 rows in set (0.00 sec)

mysql> alter table qr add id int(2) primary key auto_increment;

Query OK, 0 rows affected (0.52 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from qr;

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

| name | sex | age | id |

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

| zhu | girl | 12 | 1 |

| hai | boy | 22 | 2 |

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

2 rows in set (0.00 sec)

mysql> alter table qr modify id int(2) not null first;

Query OK, 2 rows affected (0.51 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from qr;

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

| id | name | sex | age |

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

| 1 | zhu | girl | 12 |

| 2 | hai | boy | 22 |

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

2 rows in set (0.00 sec)

mysql>

mysql> create table jfb(

-> jfb_id int(2) primary key auto_increment,

-> name char(15);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

mysql> create table jfb(

-> jfb_id int(2) primary key auto_increment,

-> name char(15),

-> class char(7),

-> pay enum("yes","no") default "no"

-> engine=innodb;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'engine=innodb' at line 6

mysql> create table jfb( jfb_id int(2) primary key auto_increment, name char(15), class char(7), pay enum("yes","no") default "no") engine=innodb;

Query OK, 0 rows affected (0.52 sec)

mysql> desc jfb;

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

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

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

| pay | enum('yes','no') | YES | | no | |

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

4 rows in set (0.00 sec)

mysql> insert into jfb(name,class,pay)values("bob","nsd1802","yes");

Query OK, 1 row affected (0.04 sec)

mysql> insert into jfb(name,class,pay)values("lucy","nsd1802","yes");

Query OK, 1 row affected (0.05 sec)

mysql> insert into jfb(name,class,pay)values("tom","nsd1802","yes");

Query OK, 1 row affected (0.03 sec)

mysql> select * from jfb;

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

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

| 1 | bob | nsd1802 | yes |

| 2 | lucy | nsd1802 | yes |

| 3 | tom | nsd1802 | yes |

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

3 rows in set (0.00 sec)

mysql> create table bjb(

-> bjb_id int(2),

-> name char(15),

-> sex enum("boy","girl"),

-> foreign key(bjb_id) references jfb(jfb_id)

-> on updata cascade on delete cascade

-> ) engine=innodb;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'updata cascade on delete cascade

) engine=innodb' at line 6

mysql> create table bjb( bjb_id int(2), name char(15), sex enum("boy","girl"), foreign key(bjb_id) references jfb(jfb_id) on updata cascade on delete cascade ) engine=innodb;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'updata cascade on delete cascade ) engine=innodb' at line 1

mysql>

mysql> create table bjb(

-> bjb_id int(2),

-> name char(15),

-> sex enum("boy","girl"),

-> foreign key(bjb_id) references jfb(jfb_id)

-> on update cascade on delete cascade

-> ) engine=innodb;

Query OK, 0 rows affected (0.32 sec)

mysql> desc bjb

-> ;

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

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

| bjb_id | int(2) | YES | MUL | NULL | |

| sex | enum('boy','girl') | YES | | NULL | |

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

3 rows in set (0.01 sec)

mysql> show create bjb;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bjb' at line 1

mysql> show create `bjb`;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`bjb`' at line 1

mysql> show create table bjb;

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

| Table | Create Table |

| bjb | CREATE TABLE `bjb` (

`bjb_id` int(2) DEFAULT NULL,

`name` char(15) DEFAULT NULL,

`sex` enum('boy','girl') DEFAULT NULL,

KEY `bjb_id` (`bjb_id`),

CONSTRAINT `bjb_ibfk_1` FOREIGN KEY (`bjb_id`) REFERENCES `jfb` (`jfb_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

外键使用实例：

mysql> select * from bjb;

Empty set (0.00 sec)

mysql>

mysql> select * from jfb;

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

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

| 1 | bob | nsd1802 | yes |

| 2 | lucy | nsd1802 | yes |

| 3 | tom | nsd1802 | yes |

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

3 rows in set (0.00 sec)

mysql> desc * from bjb;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from bjb' at line 1

mysql> desc bjb;

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

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

| bjb_id | int(2) | YES | MUL | NULL | |

| sex | enum('boy','girl') | YES | | NULL | |

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

3 rows in set (0.00 sec)

mysql> insert into bjb values (3,"tom","boy");

Query OK, 1 row affected (0.04 sec)

mysql> select * from bjb;

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

| bjb_id | name | sex |

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

| 3 | tom | boy |

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

1 row in set (0.00 sec)

mysql> alter table jfb

-> ^C

mysql> update jfb set jfb_id=6 where name="lucy";

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from jfb;

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

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

| 1 | bob | nsd1802 | yes |

| 3 | tom | nsd1802 | yes |

| 6 | lucy | nsd1802 | yes |

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

3 rows in set (0.00 sec)

mysql> select * from bjb;

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

| bjb_id | name | sex |

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

| 3 | tom | boy |

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

1 row in set (0.00 sec)

mysql> update jfb set jfb_id=3 where name="tom";

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

mysql> update jfb set jfb_id=7 where name="tom";

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from jfb;

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

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

| 1 | bob | nsd1802 | yes |

| 6 | lucy | nsd1802 | yes |

| 7 | tom | nsd1802 | yes |

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

3 rows in set (0.00 sec)

mysql> select * from bjb;

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

| bjb_id | name | sex |

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

| 7 | tom | boy |

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

1 row in set (0.00 sec)

mysql> delete from jfb where name="tom";

Query OK, 1 row affected (0.05 sec)

mysql> select * from jfb;

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

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

| 1 | bob | nsd1802 | yes |

| 6 | lucy | nsd1802 | yes |

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

2 rows in set (0.00 sec)

mysql> select * from bjb;

Empty set (0.00 sec)

转载地址：https://blog.csdn.net/zhydream77/article/details/80855551 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：mysql-linux-3

下一篇：mysql的安装配置与密码更改

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章