06-14 SQL语言建表实操二

1、建表员工与部门

2、添加数据和查询

    -> ;	

mysql> use hello

Database changed
mysql> select * from tb_emp ;
+------+-------+-------+--------+------+-----+
| eno  | ename | sal   | job    | mar  | dno |
+------+-------+-------+--------+------+-----+
| 1001 | 李杰  | 20000 | 董事长 | 1001 |  50 |
+------+-------+-------+--------+------+-----+
1 row in set

mysql> insert into tb_emp values 
    -> ( 1002, '张无忌', 15000, '总经理',1001,50),
    -> (1003, '杨逍', 12000,'光明左使',1002,50),
    -> (1004, '李混',8000,'研发部长',1003,20);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into tb_emp values 
    -> (1005,'李红',6000, '研究员',1004,20),
    -> (1006,'徐凡' , 7500, '销售主管',30),
    -> (1007, ' 徐一户', 4800,'销售员' 30);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '30)' at line 7
mysql> insert into tb_emp values 
    -> (1005,'李红',6000, '研究员',1004,20),
    -> (1006,'徐凡' , 7500, '销售主管',30);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> (1006,'徐凡' , 7500, '销售主管',30)' at line 4
mysql> insert into tb_emp values 
    -> (1005,'李红',6000, '研究员',1004,20);

2003 - Can't connect to MySQL server on '127.0.0.1' (10038)
mysql> insert into tb_emp values (1005,'李红',6000, '研究员',1004,20);
Query OK, 1 row affected

mysql> insert into tb_emp values (1006,'徐凡' , 7500, '销售主管',30);
1136 - Column count doesn't match value count at row 1
mysql> insert into tb_emp values (1006,'徐凡' , 7500, '销售主管',1003,30);
Query OK, 1 row affected

mysql> insert into tb_emp values (1007, ' 徐一户', 4800,'销售员',
1006, 30);
Query OK, 1 row affected

mysql> insert into tb_emp values (1008, ' 胡三', 7800,'技术部长',1003, 40)
;
Query OK, 1 row affected

mysql> insert into tb_emp values (1009, ' 胡三清', 4800,'技术员',1008, 
40);
Query OK, 1 row affected

mysql> select 'X' from tb_emp ;
+---+
| X |
+---+
| X |
| X |
| X |
| X |
| X |
| X |
| X |
| X |
| X |
+---+
9 rows in set

mysql> select *from tb_emp ;
+------+---------+-------+----------+------+-----+
| eno  | ename   | sal   | job      | mar  | dno |
+------+---------+-------+----------+------+-----+
| 1001 | 李杰    | 20000 | 董事长   | 1001 |  50 |
| 1002 | 张无忌  | 15000 | 总经理   | 1001 |  50 |
| 1003 | 杨逍    | 12000 | 光明左使 | 1002 |  50 |
| 1004 | 李混    |  8000 | 研发部长 | 1003 |  20 |
| 1005 | 李红    |  6000 | 研究员   | 1004 |  20 |
| 1006 | 徐凡    |  7500 | 销售主管 | 1003 |  30 |
| 1007 |  徐一户 |  4800 | 销售员   | 1006 |  30 |
| 1008 |  胡三   |  7800 | 技术部长 | 1003 |  40 |
| 1009 |  胡三清 |  4800 | 技术员   | 1008 |  40 |
+------+---------+-------+----------+------+-----+
9 rows in set

mysql> select * rom tb_dept ;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rom tb_dept' at line 1
mysql> select * f
rom tb_dept ;
+-----+--------+------+
| dno | dname  | dloc |
+-----+--------+------+
|  10 | 技术部 | 成都 |
|  20 | 销售部 | 武汉 |
|  30 | 研发部 | 北京 |
|  40 | 品质部 | 上海 |
|  50 | 主管部 | 广州 |
+-----+--------+------+
5 rows in set

mysql> alter table tb_emp add constrait fk_emp_mar foreign key(mar) referexces tb_emp(eno);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'fk_emp_mar foreign key(mar) referexces tb_emp(eno)' at line 1
mysql> alter table tb_emp add constraint fk_emp_mar foreign key(mar) referen
ces tb_emp(eno);
Query OK, 9 rows affected
Records: 9  Duplicates: 0  Warnings: 0

mysql> alter table tb_emp add constraint fk_emp_dno foreign key(dno) references tb_dept(dno) ;
1005 - Can't create table `hello`.`tb_emp` (errno: 150 "Foreign key constraint is incorrectly formed")
mysql> alter table tb_emp add constraint fk_emp_dno foreign key(dno) references tb_dept(dno) ;
1005 - Can't create table `hello`.`tb_emp` (errno: 150 "Foreign key constraint is incorrectly formed")

mysql>
问题一:外键约束建在表钟添加数据之前还是？

问题二： 添加数据后建外键约束还有用吗？

2、单词补充：

console 控制台
constraint 约束
configure 安装
account 账户
binary 二进制
octonary 八进制
hex十六进制

indent 缩进，缩排；
recursive 循环的，递归的 ；
setter 制定者，安排者；
subscribe 签署，签订，捐献；
terminate 终端，终结，终止；
update 更新数据；
upgrade 更新版本