MySQL——表管理

一、添加数据 Insert

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] { {VALUES | VALUE} (value_list) [, (value_list)] ... }

1、插入完整数据

insert into student(id,stu_name,age)

value(1001,'tom',20);

insert into student value(1002,'jerry',22); -- 省略列名的时候，必须给全部列赋值

2、插入个别列

insert into student(id,stu_name)

value(1003,'smith');

3、values使用

insert into student

values

(1004,'rick',70),

(1005,'beis',40);

二、修改数据 Update

语法：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

update student set stu_name = 'new_name' where id = 1001;

三、删除数据 delete

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

delete from student where id = 1001;

四、查询语句 select

1. SELECT

2. {* | <字段列名>}

3. [

4. FROM <表 1>, <表 2>…

5. [WHERE <表达式>

6. [GROUP BY <group by definition>

7. [HAVING <expression> [{<operator> <expression>}…]]

8. [ORDER BY <order by definition>]

9. [LIMIT[<offset>,] <row count>]

10. ]

select * from student;

五、约束

1、外键

外键 FOREIGN key [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]

-- 创建时添加

create table score(

id int primary key auto_increment,

stu_id int,

score decimal(4,1),

foreign key(stu_id) reference student id [on delete cascade] --[] 内为级联删除

);

-- 修改时添加

alter table score add [constraint score_stu_id] foreign key(stu_id) reference student (id);

-- 删除外键

alter table score drop foreign key score_stu_id;

当被引用为外键的数据需要被删除时，需要先删除引用数据，或者添加级联删除，否则报错。

2、unique 唯一约束

3、非空 not null