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)
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)
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));
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));
1.5加上引擎 engine用于防止数据错乱 /或者说数据进行更改的过程中如果某一方更改失败可以回到：
mysql> create table t6( -> id int not null, -> name char(10)) engine=innodb default charset=utf8; Query OK, 0 rows affected, 1 warning (0.02 sec)
2.外键里面的变种：
1.每一个表格中只允许某一个变量只出现一次：unique指令：（如果values出现第二次插入就会报错）
create table t4 ( -> id int not null auto_increment primary key, -> name varchar(10), -> unique t4_uql(name)); Query OK, 0 rows affected (0.02 sec) mysql> insert into (name) value('alex'); 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 '(name) value('alex')' at line 1 mysql> insert into (name) values ('alex'); 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 '(name) values ('alex')' at line 1 mysql> insert into t4 (name) values ('alex'); Query OK, 1 row affected (0.01 sec) mysql> insert into t4 (name) values ('alex'); ERROR 1062 (23000): Duplicate entry 'alex' for key 't4.t4_uql'
如果不适用unique的结果：
insert into t2(name) values('alex'); Query OK, 1 row affected (0.01 sec) mysql> insert into t2(name) values('alex'); Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +----+------+ | id | name | +----+------+ | 1 | alex | | 2 | alex | +----+------+ 2 rows in set (0.00 sec)
2.1 外键里面的一对一：
mysql> create table t5( -> id int auto_increment primary key, -> department_id int not null unique, -> name char(10), -> constraint fk_t5_t4 foreign key (department_id) references t4(id)); Query OK, 0 rows affected (0.02 sec)
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.02 sec) 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)
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.02 sec) 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) mysql> create table teacherinfo ( -> id int auto_increment primary key, -> name varchar(10), -> teacher_name varchar(10)); Query OK, 0 rows affected (0.02 sec) 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)
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)
在看的过程中会有点乱但是也能找到关系量，为了更加美观的进行看可以在指令后面加一个\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_ci 1 row in set (0.00 sec) ERROR: No query specified
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 |
从上面我们可以看到AUTO_INCREMENT=3，但是我们下次id的值我们想要从5开始：可以使用下面方法：
mysql> alter t2 auto_increment_increment=5; 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 't2 auto_increment_increment=5' at line 1 mysql> alter table t2 auto_increment_increment=5; 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 'auto_increment_increment=5' at line 1 mysql> alter table t2 auto_increment=5; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t2(name) values('wusir'); Query OK, 1 row affected (0.00 sec) mysql> select *from t2; +----+-------+ | id | name | +----+-------+ | 1 | alex | | 2 | alex | | 5 | wusir | +----+-------+ 3 rows in set (0.00 sec)
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.00 sec) mysql> set session auto_increment_increment =2; 设置步长 Query OK, 0 rows affected (0.00 sec)
mysql> select *from t2; +----+-------+ | id | name | +----+-------+ | 1 | alex | | 2 | alex | | 5 | wusir | +----+-------+ 3 rows in set (0.00 sec) mysql> insert into t2(name) values('wusi1r'); Query OK, 1 row affected (0.01 sec) mysql> select *from t2; +----+--------+ | id | name | +----+--------+ | 1 | alex | | 2 | alex | | 5 | wusir | | 7 | wusi1r | +----+--------+ 4 rows in set (0.00 sec)
2.如果我们想要每次启动sql都想使用此步长，则需要使用global（全局变量）
mysql> set global auto_increment_increment=2; Query OK, 0 rows affected (0.00 sec)
6.昨天增添改删的补充：
1.增加数据：
1.每次增加一个数据：
insert into t2 (name) values('alex'); Query OK, 1 row affected (0.05 sec) mysql> select *from t2; +----+------+ | id | name | +----+------+ | 1 | alex | +----+------+ 1 row in set (0.00 sec)
2.每次增加多个数据：每个values之后面用逗号进行隔开：
insert into t2(name) values('1'),('2'),('3'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select *from t2; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | | 5 | 2 | | 7 | 3 | +----+------+ 4 rows in set (0.00 sec)
3。如果新创建一个新表想要把另一个表格中的所有数据全部复制过来·：
insert into t6 (id,name) from table t2(id,name); 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 table t2(id,name)' at line 1 mysql> insert into t6(id,name) select id ,name from t2; Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t6; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | | 5 | 2 | | 7 | 3 | +----+------+
2.删除数据：可以选择条件：
mysql> select * from t6; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | | 5 | 2 | | 7 | 3 | +----+------+ 4 rows in set (0.00 sec) mysql> delete from t6 where id>3; Query OK, 2 rows affected (0.00 sec) mysql> select * from t6; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | +----+------+ 2 rows in set (0.00 sec)
3.数据更新：也可以进行条件更新：
mysql> select * from t6; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | +----+------+ 2 rows in set (0.00 sec) mysql> update t6 set name='wusir'where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t6; +----+-------+ | id | name | +----+-------+ | 1 | alex | | 3 | wusir | +----+-------+ 2 rows in set (0.00 sec)
4.数据的查看：
1.查看所有数据：
mysql> select *from t2; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | | 5 | 2 | | 7 | 3 | +----+------+ 4 rows in set (0
2.查看带条件的数据：
mysql> select *from t2 where id >3 ; +----+------+ | id | name | +----+------+ | 5 | 2 | | 7 | 3 | +----+------+ 2 rows in set (0.00 sec)
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)
4not in 除了他以外的数据都显示出来：
mysql> select * from t2 where id not in (1,3,5); +----+------+ | id | name | +----+------+ | 7 | 3 | +----+------+ 1 row in set (0.00 sec)
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)
6.查看两个表中共同拥有的数据：
mysql> select * from t2 where id in (select id from t6) ; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | +----+------+ 2 rows in set (0.01 sec)
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)
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)
8.限制字符：
1每次显示：n个表格的数据：
mysql> select * from t2 limit 2; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | +----+------+
2.设置每次开始的位置 limit （开始的位置，显示数据的个数）行数从0开始
mysql> select * from t2 limit 2, 2; +----+------+ | id | name | +----+------+ | 5 | 2 | | 7 | 3 | +----+------+ 2 rows in set (0.00 sec)
mysql> select *from t2 limit 2,offset 2; 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 '2' at line 1 mysql> select * from t2 limit 2 offset 2; +----+------+ | id | name | +----+------+ | 5 | 2 | | 7 | 3 | +----+------+ 2 rows in set (0.00 sec)
3如果想要倒着取数据想要获取最后n个数据：
1.把数据进行反转：desc
mysql> select * from t2 where id order by id desc; +----+------+ | id | name | +----+------+ | 7 | 3 | | 5 | 2 | | 3 | 1 | | 1 | alex | +----+------+ 4 rows in set (0.00 sec) mysql>
2.把数据进行·正传：
mysql> select * from t2 where id order by id asc; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | 1 | | 5 | 2 | | 7 | 3 | +----+------+ 4 rows in set (0.00 sec)
3.使用多重判断结构进行判断·
mysql> select * from t10 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)
2.进行两个通过外键数据在同一行进行显示：
1.第一种方法：
mysql> select * from t1,t2 where t1.id =t2.department_id; +----+-----------------+----+------+---------------+ | id | department_name | id | name | 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)
2.第二种方法：使用left方法：（左边的全部显示，右边没有的显示null）
mysql> select * from t1 left join t2 on t1.id =t2.department_id; +----+-----------------+------+------+---------------+ | id | department_name | id | name | department_id | +----+-----------------+------+------+---------------+ | 1 | 12 | 9 | jin | 1 | | 3 | 133 | 13 | iuf | 3 | | 5 | 11 | 11 | jinf | 5 | | 7 | 22 | 15 | jinn | 7 | | 9 | 11 | NULL | NULL | NULL | +----+-----------------+------+------+---------------+ 5 rows in set (0.00 sec)
3.第三种方法：使用right方法：（右边的全部显示，左边没有的显示null，多余的不显示）
| id | department_name | id | name | 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)
7分组：
1.想要查看某个部门共有多少个人：
select count(department_name),department_name from t1 group by department_name; +------------------------+-----------------+ | count(department_name) | department_name | +------------------------+-----------------+ | 1 | 12 | | 1 | 133 | | 2 | 11 | | 1 | 22 | +------------------------+-----------------+ 4 rows in set (0.00 sec)
2。想要要使用某个id的最大值作为标号：
mysql> select max(id),department_name from t1 group by department_name; +---------+-----------------+ | max(id) | department_name | +---------+-----------------+ | 1 | 12 | | 3 | 133 | | 9 | 11 | | 7 | 22 | +---------+-----------------+ 4 rows in set (0.00 sec)
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)
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.01 sec) 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)); 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 '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), -> ; 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 '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.02 sec) mysql> create table teacher ( -> tid int auto_increment primary key, -> tname varchar(10)); Query OK, 0 rows affected (0.02 sec) 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.02 sec) 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.03 sec) mysql>
