當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql新增范围之外数据_mysql第二天数据的增删改查补充及外键

發布時間：2025/3/15 数据库 21 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql新增范围之外数据_mysql第二天数据的增删改查补充及外键小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

1.主鍵的一些補充：

1.1 一個表中只允許一個主鍵，但是多個列可以共同擁有一個主鍵：

上節課內容學的命名主鍵的方式：(具有一定的約束條件，不能為空，并且不能重復)

mysql>create table t2 (->id int auto_increment primary key,-> name varchar(10));

Query OK, 0 rows affected (0.02 sec)

View Code

1.2今天學到的主鍵命名方式(可以給多個列進行主鍵的設定)

mysql>create table t3(->id int auto_increment ,-> name varchar(10),->primary key(id,name));

Query OK, 0 rows affected (0.02 sec)

View Code

1.3外建的的mysql語句：(和上面創建的t3進行連接)

create table t4(->id int auto_increment primary key,->department_id int,-> name varchar(10),-> constraint fk_t3_t4 foreign key(department_id) references t3(id));

View Code

1.4外鍵一次性和和另外一個表格中的多個主鍵進行連接：

create table t5 (->id int auto_increment primary key,->department_id int,-> name varchar(10),-> constraint fk_t5_t3 foreign key (department_id, name) references t3(id,name));

View Code

1.5加上引擎 engine用于防止數據錯亂 /或者說數據進行更改的過程中如果某一方更改失敗可以回到：

mysql>create table t6(-> id int notnull,-> name char(10)) engine=innodb default charset=utf8;

Query OK, 0 rows affected,1 warning (0.02 sec)

View Code

2.外鍵里面的變種：

1.每一個表格中只允許某一個變量只出現一次：unique指令：(如果values出現第二次插入就會報錯)

create table t4 (-> id int notnull auto_increment primary key,-> name varchar(10),->unique t4_uql(name));

Query OK, 0 rows affected (0.02sec)

mysql> insert into (name) value('alex');

ERROR1064 (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 '(name) value('alex')' at line 1mysql> insert into (name) values ('alex');

ERROR1064 (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 '(name) values ('alex')' at line 1mysql> insert into t4 (name) values ('alex');

Query OK,1 row affected (0.01sec)

mysql> insert into t4 (name) values ('alex');

ERROR1062 (23000): Duplicate entry 'alex' for key 't4.t4_uql'

View Code

如果不適用unique的結果：

insert into t2(name) values('alex');

Query OK,1 row affected (0.01sec)

mysql> insert into t2(name) values('alex');

Query OK,1 row affected (0.01sec)

mysql> select * fromt2;+----+------+

| id | name |

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

| 1 | alex |

| 2 | alex |

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

2 rows in set (0.00 sec)

View Code

2.1 外鍵里面的一對一：

mysql>create table t5(->id int auto_increment primary key,-> department_id int notnull unique,-> name char(10),->constraint fk_t5_t4 foreign key (department_id) references t4(id));

Query OK, 0 rows affected (0.02 sec)

View Code

2.2外鍵里面的一對多：

mysql>create table userinfo(->id int auto_increment primary key,-> name varchar(10),-> department_name varchar(10));

Query OK, 0 rows affected (0.02sec)

mysql>create table depart1(->id int auto_increment primary key,->department_id int,->unique dep_ul(department_id),->constraint fk_user_depart1 foreign key(department_id) references userinfo(id));

Query OK, 0 rows affected (0.02 sec)

View Code

2.3外鍵里面的多對多：

ql>create table userinfo(->id int auto_increment primary key,-> name varchar(10),-> department_name varchar(10));

Query OK, 0 rows affected (0.02sec)

mysql>create table depart1(->id int auto_increment primary key,->department_id int,->unique dep_ul(department_id),->constraint fk_user_depart1 foreign key(department_id) references userinfo(id));

Query OK, 0 rows affected (0.02sec)

mysql>create table teacherinfo (->id int auto_increment primary key,-> name varchar(10),-> teacher_name varchar(10));

Query OK, 0 rows affected (0.02sec)

mysql>create table teacher_user(->id int auto_increment primary key,->user_id int,->teacher_id int,->unique teach_user(user_id,teacher_id),->constraint fk_user_t1 foreign key(user_id) references userinfo(id),->constraint fk_teach_t1 foreign key (teacher_id) references teacherinfo(id));

Query OK, 0 rows affected (0.03 sec)

View Code

3.對創建表時執行了哪些命令進行查詢：

mysql>show create table t3;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

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

| t3 |CREATE TABLE `t3` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(10) NOT NULL,

PRIMARY KEY (`id`,`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

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

1 row in set (0.00 sec)

View Code

在看的過程中會有點亂但是也能找到關系量，為了更加美觀的進行看可以在指令后面加一個\G:? ? 注：使用這個指令最后會報一個錯可以忽略

Table: t3

Create Table: CREATE TABLE `t3` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(10) NOT NULL,

PRIMARY KEY (`id`,`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00sec)

ERROR:

No query specified

View Code

4.我們在使用auto_increment 的指令時如果使用delete清空表里面的數據時，它的id下一次會從刪除最后一個id+1進行顯示：可以使用下面這種方法及進行修改：

1.先去查看表格中現在id 的值：

show create table t2;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

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

| t2 |CREATE TABLE `t2` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

View Code

從上面我們可以看到AUTO_INCREMENT=3，但是我們下次id的值我們想要從5開始：可以使用下面方法：

mysql> alter t2 auto_increment_increment=5;

ERROR1064 (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 't2 auto_increment_increment=5' at line 1mysql> alter table t2 auto_increment_increment=5;

ERROR1064 (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 'auto_increment_increment=5' at line 1mysql> alter table t2 auto_increment=5;

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into t2(name) values('wusir');

Query OK,1 row affected (0.00sec)

mysql> select *fromt2;+----+-------+

| id | name |

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

| 1 | alex |

| 2 | alex |

| 5 | wusir |

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

3 rows in set (0.00 sec)

View Code

5.我們發現我們我們id在使用自增一的時候，每一次步長都是1，是否可以進行更改：

1.會話更改：指的是：在你打開的這個命令框有效，關閉或者從新打開一個命令框就沒有作用：

mysql> show session variables like 'auto_inc%'; 查看步長+--------------------------+-------+

| Variable_name | Value |

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

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

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

2 rows in set, 1 warning (0.00sec)

mysql> set session auto_increment_increment =2; 設置步長

Query OK, 0 rows affected (0.00 sec)

View Code

mysql> select *fromt2;+----+-------+

| id | name |

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

| 1 | alex |

| 2 | alex |

| 5 | wusir |

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

3 rows in set (0.00sec)

mysql> insert into t2(name) values('wusi1r');

Query OK,1 row affected (0.01sec)

mysql> select *fromt2;+----+--------+

| id | name |

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

| 1 | alex |

| 2 | alex |

| 5 | wusir |

| 7 | wusi1r |

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

4 rows in set (0.00 sec)

View Code

2.如果我們想要每次啟動sql都想使用此步長，則需要使用global(全局變量)

mysql> set global auto_increment_increment=2;

Query OK, 0 rows affected (0.00 sec)

View Code

6.昨天增添改刪的補充：

1.增加數據：

1.每次增加一個數據：

insert into t2 (name) values('alex');

Query OK,1 row affected (0.05sec)

mysql> select *fromt2;+----+------+

| id | name |

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

| 1 | alex |

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

1 row in set (0.00 sec)

View Code

2.每次增加多個數據：每個values之后面用逗號進行隔開：

insert into t2(name) values('1'),('2'),('3');

Query OK,3 rows affected (0.01sec)

Records:3Duplicates: 0 Warnings: 0

mysql> select *fromt2;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

| 5 | 2 |

| 7 | 3 |

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

4 rows in set (0.00 sec)

View Code

3。如果新創建一個新表想要把另一個表格中的所有數據全部復制過來·：

insert into t6 (id,name) fromtable t2(id,name);

ERROR1064 (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 table t2(id,name)' at line 1mysql> insert into t6(id,name) select id ,name fromt2;

Query OK,4 rows affected (0.05sec)

Records:4Duplicates: 0 Warnings: 0

mysql> select * fromt6;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

| 5 | 2 |

| 7 | 3 |

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

View Code

2.刪除數據：可以選擇條件：

mysql> select * fromt6;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

| 5 | 2 |

| 7 | 3 |

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

4 rows in set (0.00sec)

mysql> delete from t6 where id>3;

Query OK,2 rows affected (0.00sec)

mysql> select * fromt6;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

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

2 rows in set (0.00 sec)

View Code

3.數據更新：也可以進行條件更新：

mysql> select * fromt6;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

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

2 rows in set (0.00sec)

mysql> update t6 set name='wusir'where id=3;

Query OK,1 row affected (0.01sec)

Rows matched:1 Changed: 1Warnings: 0

mysql> select * fromt6;+----+-------+

| id | name |

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

| 1 | alex |

| 3 | wusir |

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

2 rows in set (0.00 sec)

View Code

4.數據的查看：

1.查看所有數據：

mysql> select *fromt2;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

| 5 | 2 |

| 7 | 3 |

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

4 rows in set (0

View Code

2.查看帶條件的數據：

mysql> select *from t2 where id >3;+----+------+

| id | name |

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

| 5 | 2 |

| 7 | 3 |

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

2 rows in set (0.00 sec)

View Code

3.如果要看某幾個數據可以使用in語法：

mysql> select * from t2 where id in (1,3,5);+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

| 5 | 2 |

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

3 rows in set (0.00 sec)

View Code

4not in 除了他以外的數據都顯示出來：

mysql> select * from t2 where id not in (1,3,5);+----+------+

| id | name |

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

| 7 | 3 |

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

1 row in set (0.00 sec)

View Code

5.如果想要查看某個范圍內的·數據：可以使用between...and

mysql> select * from t2 where id between 1 and 5;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

| 5 | 2 |

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

3 rows in set (0.00 sec)

View Code

6.查看兩個表中共同擁有的數據：

mysql> select * from t2 where id in (select id fromt6) ;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

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

2 rows in set (0.01 sec)

View Code

7.通配字符查找：

1.a% 尋找以a開頭后面n個字符的所有符合條件的行

mysql> select * from t6 where name like '1%';+----+------+

| id | name |

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

| 1 | 11 |

| 3 | 123 |

| 5 | 13 |

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

3 rows in set (0.00 sec)

View Code

2.a_ x尋找以a開頭后面1個字符的所有符合條件的行

mysql> select * from t6 where name like '1_';+----+------+

| id | name |

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

| 1 | 11 |

| 5 | 13 |

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

2 rows in set (0.00 sec)

View Code

8.限制字符：

1每次顯示：n個表格的數據：

mysql> select * from t2 limit 2;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

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

View Code

2.設置每次開始的位置 limit (開始的位置，顯示數據的個數)行數從0開始

mysql> select * from t2 limit 2, 2;+----+------+

| id | name |

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

| 5 | 2 |

| 7 | 3 |

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

2 rows in set (0.00 sec)

View Code

mysql> select *from t2 limit 2,offset 2;

ERROR1064 (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 '2' at line 1mysql> select * from t2 limit 2 offset 2;+----+------+

| id | name |

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

| 5 | 2 |

| 7 | 3 |

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

2 rows in set (0.00 sec)

View Code

3如果想要倒著取數據想要獲取最后n個數據：

1.把數據進行反轉：desc

mysql> select * fromt2 where id order by id desc;+----+------+

| id | name |

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

| 7 | 3 |

| 5 | 2 |

| 3 | 1 |

| 1 | alex |

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

4 rows in set (0.00sec)

mysql>

View Code

2.把數據進行·正傳：

mysql> select * fromt2 where id order by id asc;+----+------+

| id | name |

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

| 1 | alex |

| 3 | 1 |

| 5 | 2 |

| 7 | 3 |

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

4 rows in set (0.00 sec)

View Code

3.使用多重判斷結構進行判斷·

mysql> select * fromt10 order by id desc, name asc;+------+------+

| id | name |

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

| 5 | 5 |

| 4 | 4 |

| 2 | 3 |

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

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

6 rows in set (0.00 sec)

View Code

2.進行兩個通過外鍵數據在同一行進行顯示：

1.第一種方法：

mysql> select * from t1,t2 where t1.id =t2.department_id;+----+-----------------+----+------+---------------+

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

| 1 | 12 | 9 | jin | 1 |

| 5 | 11 | 11 | jinf | 5 |

| 3 | 133 | 13 | iuf | 3 |

| 7 | 22 | 15 | jinn | 7 |

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

4 rows in set (0.00 sec)

View Code

2.第二種方法：使用left方法：(左邊的全部顯示，右邊沒有的顯示null)

mysql> select * from t1 left join t2 on t1.id =t2.department_id;+----+-----------------+------+------+---------------+

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

| 1 | 12 | 9 | jin | 1 |

| 3 | 133 | 13 | iuf | 3 |

| 5 | 11 | 11 | jinf | 5 |

| 7 | 22 | 15 | jinn | 7 |

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

5 rows in set (0.00 sec)

View Code

3.第三種方法：使用right方法：(右邊的全部顯示，左邊沒有的顯示null，多余的不顯示)

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

| 1 | 12 | 9 | jin | 1 |

| 5 | 11 | 11 | jinf | 5 |

| 3 | 133 | 13 | iuf | 3 |

| 7 | 22 | 15 | jinn | 7 |

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

4 rows in set (0.00 sec)

View Code

7分組：

1.想要查看某個部門共有多少個人：

select count(department_name),department_name fromt1 group by department_name;+------------------------+-----------------+

| count(department_name) | department_name |

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

| 1 | 12 |

| 1 | 133 |

| 2 | 11 |

| 1 | 22 |

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

4 rows in set (0.00 sec)

View Code

2。想要要使用某個id的最大值作為標號：

mysql> select max(id),department_name fromt1 group by department_name;+---------+-----------------+

| max(id) | department_name |

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

| 1 | 12 |

| 3 | 133 |

| 9 | 11 |

| 7 | 22 |

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

4 rows in set (0.00 sec)

View Code

3.如果想要對聚合元素進行二次篩選必須使用關鍵字having

mysql> select count(department_name),department_name from t1 group by department_name having count(department_name)>2;+------------------------+-----------------+

| count(department_name) | department_name |

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

| 3 | 11 |

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

1 row in set (0.00 sec)

View Code

8作業：

mysql> create table class(->cid int auto_increment primary key,-> name varchar(10)) engine=innodb default charset=utf8;

Query OK, 0 rows affected,1 warning (0.01sec)

mysql>create table student (->sid int auto_increment primary key,-> sname varcahr(10),-> gener varchar(10),->class_id int,-> constraint fk_class_student foreign key(class_id) references class(cid));

ERROR1064 (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 'varcahr(10),

gener varchar(10),

class_id int,

constraint fk_class_student foreig'at line 3

mysql>create table student (->sid int auto_increment primary key,-> sname varcahr(10),-> gener varchar(10),->;

ERROR1064 (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 'varcahr(10),

gener varchar(10),'at line 3

mysql>create table student (->sid int auto_increment primary key,-> sname varchar(10),-> gener varchar(10),->class_id int,-> constraint fk_class_student foreign key(class_id) references class(cid));

Query OK, 0 rows affected (0.02sec)

mysql>create table teacher (->tid int auto_increment primary key,-> tname varchar(10));

Query OK, 0 rows affected (0.02sec)

mysql>create table course(->cid int auto_increment primary key,-> cname varchar(10),->teacher_id int,->constraint fk_course_teacher foreign key(cid) references teacher(tid));

Query OK, 0 rows affected (0.02sec)

mysql>create table score (->sid int auto_increment primary key,->student_id int,->corse_id int,->number int,->constraint fk_score_student foreign key(student_id) references student(sid),->constraint fk_score_corse foreign key(corse_id) references course(cid));

Query OK, 0 rows affected (0.03sec)

mysql>

View Code

總結

以上是生活随笔為你收集整理的mysql新增范围之外数据_mysql第二天数据的增删改查补充及外键的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： [云炬创业管理笔记]第九章为创业成败而准
下一篇： [云炬创业管理笔记]第十章商业计划书答辩

数据库

mysql新增范围之外数据_mysql第二天 数据的增删改查补充及外键

總結

mysql新增范围之外数据_mysql第二天数据的增删改查补充及外键