SQL基础语法_梁鑫

#建库

CREATE DATABASE  IF NOT EXISTS  test DEFAULT CHARSET utf8;

使用库
USE test;

#建表

CREATE TABLE IF NOT EXISTS student(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
gender VARCHAR(20) NOT NULL,
bj_number INT,
score INT 
);

INSERT INTO student(NAME,gender,bj_number,score)VALUES("张三","男",1,"88"),
("李四","男",1,"99"),
("王五","女",2,"67"),
("老八","女",2,"89"),
("赵六","女",1,"70");

CREATE TABLE IF NOT EXISTS banji(
teacher VARCHAR(20),
gender VARCHAR(20) NOT NULL,
bj_id INT 
);

INSERT INTO banji(teacher,gender,bj_id)VALUES("杨超","男","1"),
("孙检","男","2");

#右关联
SELECT * FROM student s RIGHT JOIN banji b ON s.bj_number=b.bj_id;

#修改语句
UPDATE student SET gender="男" WHERE NAME="老八"; 

#删除语句
DELETE FROM student WHERE NAME="赵六";

#查询
SELECT * FROM student;

#子查询
SELECT * FROM student WHERE score>(SELECT score FROM student WHERE NAME="张三");