慢谈MYSQL常用SQL语句
mysql> USE mydb1;mysql> CREATE TABLE IF NOT EXISTS students_tb (StudentID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED,Gender ENUM('M','F'),ClassID TINYINT UNSIGNED NOT NULL);
mysql> INSERT INTO students_tb (Name,Age,Gender,ClassID) VALUES ('Tom',17,'M',1),('Jack',18,'M',3),('Lucy',21,'F',6),('Jimima',15,'F',4),('Jimmy',30,'M',9),('Jim',26,'M',7);
mysql> SELECT * FROM students_tb;
+-----------+--------+------+--------+---------+
| StudentID | Name | Age| Gender | ClassID |
+-----------+--------+------+--------+---------+
| 1 | Tom | 17 | M | 1 |
| 2 | Jack | 18 | M | 3 |
| 3 | Lucy | 21 | F | 6 |
| 4 | Jimima | 15 | F | 4 |
| 5 | Jimmy| 30 | M | 9 |
| 6 | Jim | 26 | M | 7 |
+-----------+--------+------+--------+---------+
mysql> CREATE TABLE IF NOT EXISTS classes_tb (ClassID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Class CHAR(20) NOT NULL,TeacherID TINYINT UNSIGNED NOT NULL);
mysql> ALTER TABLE classes_tb ADD UNIQUE KEY (Class);#这个表中的班别应该是唯一的，所以用此sql语句来修改
Query OK, 0 rows affected (0.08 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> DESC classes_tb;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| ClassID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| Class | char(20) | NO | UNI | NULL | |
| TeacherID | tinyint(3) unsigned | NO | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO classes_tb (Class,TeacherID) VALUES ('class1',5),('class2',2),('class3',5),('class4',2),('class5',4),('class6',1);
mysql> SELECT * FROM classes_tb;
+---------+--------+-----------+
| ClassID | Class| TeacherID |
+---------+--------+-----------+
| 1 | class1 | 5 |
| 2 | class2 | 2 |
| 3 | class3 | 5 |
| 4 | class4 | 2 |
| 5 | class5 | 4 |
| 6 | class6 | 1 |
+---------+--------+-----------+
mysql> CREATE TABLE IF NOT EXISTS teacher_tb (TeacherID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED,Gender ENUM('M','F'));
mysql> INSERT INTO teacher_tb (Name,Age,Gender) VALUES ('Wang baoqiang',30,'M'),('Zhang shanfeng',32,'F'),('Zhao qiang',40,'M'),('Ying hui',50,'M'),('Feng bing',46,'M'),('Qian qiang',37,'M'),('Shun bin',68,'M');
mysql> SELECT * FROM teacher_tb;
+-----------+----------------+------+--------+
| TeacherID | Name | Age| Gender |
+-----------+----------------+------+--------+
| 1 | Wang baoqiang| 30 | M |
| 2 | Zhang shanfeng | 32 | F |
| 3 | Zhao qiang | 40 | M |
| 4 | Ying hui | 50 | M |
| 5 | Feng bing | 46 | M |
| 6 | Qian qiang | 37 | M |
| 7 | Shun bin | 68 | M |
+-----------+----------------+------+--------+
三个表创建及数据插入完成。
页:
[1]