mysql建表

1.建立一张表：表里面有多个字段， 需要包含我们今天讲过每一个数据类型

mysql> create table article( art_id int, art_title varchar(255), art_content text, art_binary_content blob, art_intro tinytext, art_label set('network','java','python'), art_content2 mediumtext, art_content3 longtext, art_author_gender enum('M','F') );
Query OK, 0 rows affected (0.02 sec)
mysql> desc article;
+--------------------+--------------------------------+------+-----+---------+-------+
| Field              | Type                           | Null | Key | Default | Extra |
+--------------------+--------------------------------+------+-----+---------+-------+
| art_id             | int                            | YES  |     | NULL    |       |
| art_title          | varchar(255)                   | YES  |     | NULL    |       |
| art_content        | text                           | YES  |     | NULL    |       |
| art_binary_content | blob                           | YES  |     | NULL    |       |
| art_intro          | tinytext                       | YES  |     | NULL    |       |
| art_label          | set('network','java','python') | YES  |     | NULL    |       |
| art_content2       | mediumtext                     | YES  |     | NULL    |       |
| art_content3       | longtext                       | YES  |     | NULL    |       |
| art_author_gender  | enum('M','F')                  | YES  |     | NULL    |       |
+--------------------+--------------------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

2.使用6种约束，必须要有样例: ​

未涉及的知识： select *from tb_name; ​

insert into tb_name values(列1的值,列2的值,列3的值....)

6种：
not null: 非空约束
unique key: 唯一约束， 和主键约束的区别：
1.主键只能有一个
2.unique key: 可以接受空值
primary key: 主键约束，唯一标识一行数据
foreign key: 表示一个表的两个字段之间的联系，两张表的两个字段之间的关系
                   要求子表外键关联到主表的primary key 或者 unique key
check(condition)
default: 在插入省略字段的时候生效

创建主表

mysql> create table student_info( stu_no int primary key auto_increment comment '学号', stu_name varchar(30) not null, stu_age tinyint check(stu_age between 0 and 100), stu_gender char(1) check(stu_gender in ('M', 'F')) default 'M', stu_class int comment '关联到班级表', stu_id char(18)  ) auto_increment=100001;
Query OK, 0 rows affected (0.09 sec)

创建子表

mysql> CREATE TABLE `class` (
    ->   `class_id` int NOT NULL AUTO_INCREMENT,
    ->   `class_name` varchar(30) NOT NULL,
    ->   PRIMARY KEY (`class_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    -> ;
Query OK, 0 rows affected (0.02 sec)

外键约束

mysql> alter table student_info add constraint student_class_fk foreign key(stu_class) references class(class_id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

唯一约束

mysql> alter table student_info add constraint unique index(stu_id);

去掉非空约束（重新定义字段）
mysql> alter table student_info modify stu_name varchar(30);
删除唯一约束（unique key）
mysql> alter table student_info drop key stu_id;
删除外键约束
mysql> alter table student_info drop foreign key student_class_fk;
删除check约束
mysql> alter table student_info drop check student_info_chk_1;

select *from tb_name; ​——查询表中数据

mysql> select *from student_info;
+--------+----------+---------+------------+-----------+--------------------+
| stu_no | stu_name | stu_age | stu_gender | stu_class | stu_id             |
+--------+----------+---------+------------+-----------+--------------------+
| 100001 | zhangsan |      20 | M          |      1001 | 123456789012345678 |
| 100002 | zhangsan |      20 | M          |      1001 | 123456789012345678 |
+--------+----------+---------+------------+-----------+--------------------+
2 rows in set (0.01 sec)

insert into tb_name values(列1的值,列2的值,列3的值....)——将数据插入表中

mysql> insert into student_info values(null,'张梦鑫',23,'M',2003,1357924680);
Query OK, 1 row affected (0.00 sec)

mysql> select *from student_info;
+--------+-----------+---------+------------+-----------+--------------------+
| stu_no | stu_name  | stu_age | stu_gender | stu_class | stu_id             |
+--------+-----------+---------+------------+-----------+--------------------+
| 100001 | zhangsan  |      20 | M          |      1001 | 123456789012345678 |
| 100002 | zhangsan  |      20 | M          |      1001 | 123456789012345678 |
| 100003 | 张梦鑫    |      23 | M          |      2003 | 1357924680         |
+--------+-----------+---------+------------+-----------+--------------------+
3 rows in set (0.00 sec)

mysql>