mysql课程报告总结_基础篇 - SQL 的约束

shiyanlou:~/ $ cd Desktop; [20:02:46]

shiyanlou:Desktop/ $ git clone https://github.com/shiyanlou/SQL3 [20:03:00]

\u6b63\u514b\u9686\u5230 'SQL3'...

remote: Counting objects: 12, done.

remote: Compressing objects: 100% (5/5), done.

remote: Total 12 (delta 3), reused 12 (delta 3), pack-reused 0

Unpacking objects: 100% (12/12), done.

\u68c0\u67e5\u8fde\u63a5... \u5b8c\u6210\u3002

shiyanlou:Desktop/ $ sudo service mysql start [20:03:53]

* Starting MySQL database server mysqld [ OK ]

* Checking for tables which need an upgrade, are corrupt or were

not closed cleanly.

shiyanlou:Desktop/ $ mysql -u root [20:04:16]

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 43

Server version: 5.5.50-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /home/shiyanlou/Desktop/SQL3/MySQL-03-01.sql

Query OK, 1 row affected (0.00 sec)

Database changed

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> show tables;

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

| Tables_in_mysql_shiyan |

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

| department |

| employee |

| project |

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

3 rows in set (0.00 sec)

mysql> SELECT*FROM department

-> dpt_name

-> ;

Empty set (0.01 sec)

mysql> CREATE TABLE employee

-> (

-> id INT(10)PRIMARY KEY,

-> name CHAR(20)

-> );

ERROR 1050 (42S01): Table 'employee' already exists

mysql> CREATE TABLE student

-> (

-> id INT(10)PRIMARY KEY,

-> name CHAR(20)NOT NULL,

-> phone INT(10)UNIQUE,

-> age INT(2)DEFAULT'0'

-> );

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO student(id,name,phone,age)VALUES(01,'Drew',110110110,20);

Query OK, 1 row affected (2.59 sec)

mysql> INSERT INTO student VALUES(02,110110110,21);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> INSERT INTO student(id,phone,age)VALUES(02,110110110,21);

ERROR 1062 (23000): Duplicate entry '110110110' for key 'phone'

mysql> INSERT INTO student(id,phone,age)VALUES(02,119119119,21);

Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> INSERT INTO student(id,name,phone)VALUES(03,Sherlock,120120120);

ERROR 1054 (42S22): Unknown column 'Sherlock' in 'field list'

mysql> INSERT INTO student(id,name,phone)VALUES(03,'Sherlock',120120120);

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO student VALUES(04,'LeBron',1234567890,33);

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO student VALUES(05,'Tom',123220220,110);

Query OK, 1 row affected (0.02 sec)

mysql> SELECT *FROM student;

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

| id | name | phone | age |

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

| 1 | Drew | 110110110 | 20 |

| 2 | | 119119119 | 21 |

| 3 | Sherlock | 120120120 | 0 |

| 4 | LeBron | 1234567890 | 33 |

| 5 | Tom | 123220220 | 110 |

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

5 rows in set (0.00 sec)

mysql> CREATE TABLE grade

-> (

-> name CHAR(20);

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 grade

-> (

-> name CHAR(20),

-> CONSTRAINT grade FOREIGN KEY(gra)REFFERENCES student(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 'REFFERENCES student(name)

)' at line 4

mysql> CREATE TABLE grade

-> -> (

-> -> name CHAR(20),

-> -> CONSTRAINT grade FOREIGN KEY(gra)REFFERENCES student(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 '-> (

-> name CHAR(20),

-> CONSTRAINT grade FOREIGN KEY(gra)REFFERENCES s' at line 2

mysql> CREATE TABLE grade

-> (

-> name CHAR(20),

-> CONSTRAINT grade FOREIGN KEY(gra)REFERENCES student(name)

-> );

ERROR 1072 (42000): Key column 'gra' doesn't exist in table

mysql> CREATE TABLE grade

-> (

-> name CHAR(20),

-> grade INT(3)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> CONSTRAINT gra FOREIGN KEY(grade)REFERENCES student(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 'CONSTRAINT gra FOREIGN KEY(grade)REFERENCES student(name)' at line 1

mysql> CREATE TABLE grade2

-> (

-> name CHAR(20),

-> CONSTRAINT mz FOREIGN KEY(name)REFERENCES student(name)

-> ,

-> grade INT(3)

-> );

ERROR 1005 (HY000): Can't create table 'mysql_shiyan.grade2' (errno: 150)

mysql>