SQL基础语法_陈胜

#创建库
CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;

#创建表
CREATE TABLE IF NOT EXISTS stu(
 sid INT PRIMARY KEY AUTO_INCREMENT,
 sname VARCHAR(20),
 gender VARCHAR(10),
 class_id INT
);

#插入数据
INSERT INTO stu(sname,gender,class_id)VALUES("张三","男",1),
("李四","男",1),
("王五","男",2);

#创建表
CREATE TABLE IF NOT EXISTS sub(
 cid INT PRIMARY KEY AUTO_INCREMENT,
 cname VARCHAR(20),
 teacher_id INT
);

#插入数据
INSERT INTO sub(cname,teacher_id)VALUES("语文",1),
("数学",2),
("英语",3);

#查询表
SELECT * FROM stu;
SELECT * FROM sub;

#修改数据
update stu set gender="女" where id=2;

#删除数据
DELECT FROM stu WHERE  sname="王五";

#内链接查询
SELECT * FROM stu s INNER JOIN sub b ON s.sid=b.cid;

#子查询
SELECT *FROM stu WHERE class_id IN(SELECT class_id FROM sname LIKE '李%');'李%');