常用sql语句

DROP DATABASE IF EXISTS school;     //删除数据库 
CREATE DATABASE school;             //创建数据库
USE school;
delete from tablename               //删除表中的所有记录
drop table tablename;               //删除表

CREATE TABLE teacher(                  //创建表
id INT PRIMARY KEY AUTO_INCREMENT,     //自动增长
NAME VARCHAR(30) NOT NULL,
age INT UNSIGNED CHECK(age BETWEEN 30 AND 60)   //在30到60之间才可以,非负数
);

INSERT INTO teacher(NAME,age)VALUES    //添加记录 //一次创建多个记录
('孔子',30),
('孟子',31),
('庄子',32),
('居里夫人',33),
('乔丹',34),
('刘欢',35);

NAME VARCHAR(50) NOT NULL UNIQUE   //unique唯一

date默认格式yyyy-mm-dd

sex INT CHECK(sex=1 OR sex=2)  //性别男或女,用1和2代替

CONSTRAINT student_fk1 FOREIGN KEY (classId) REFERENCES classInfo(id)  //添加外键
也可以
classId VARCHAR(20) NOT NULL references classInfo(id)

cardbg INT DEFAULT 1 CHECK(cardbg>=1)    //默认值1,检查是否>=1

alter table books add column b_hit int(5) defult(0) not null     //添加字段
alter table sells add constraint   fk_member_no foreign key(m_no) references members(m_no)   //添加外键

PRIMARY KEY (stId,subId)   //添加多个主键

 多个表之间同时查询(前提是表和表之间有外键关联)

SELECT st.name , sub.name FROM student st JOIN studentandsubject ss ON st.id=ss.stId JOIN subjectinfo sub ON sub.id=ss.subId;

 

添加外键:

alter table ss_accesscode add foreign key (vccId) references ss_vcc(vccId) ON DELETE CASCADE;

alter table ss_accesscode add constraint FK_SS_ASC_VCC foreign key (vccId) references ss_vcc(vccId) ON DELETE CASCADE;

删除外键:

alter table student drop foreign key student_fk1;