SQL基础语法_刘世民

100 阅读 0 评论 66 点赞

我是靠谱客的博主虚拟航空，最近开发中收集的这篇文章主要介绍SQL基础语法_刘世民，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.建库建表语法，字段数据类型, 需要案例

CREATE DATABASE IF NOT EXISTS love DEFAULT CHARSET utf8;
USE love;

CREATE TABLE IF NOT EXISTS student
(
   sid INT PRIMARY KEY AUTO_INCREMENT,
   sname VARCHAR(50) NOT NULL,
   gender VARCHAR(50) NOT NULL,
   class VARCHAR(50) NOT NULL COMMENT"班级",
   class_id INT
);

2.增删改查语法，需要案例

INSERT INTO student(sname,gender,class,class_id)
       VALUES("张三","男","大数据32001",1),
           ("李四","女","大数据32002",1),
           ("王五","男","虚拟现实32001",2),
           ("老六","男","虚拟现实32002",3);

DELETE FROM student WHERE sname="老六";
UPDATE student SET gender="男" WHERE sname="李四";
SELECT *FROM student;

3.表关联查询，需要案例

CREATE TABLE IF NOT EXISTS student2
(
   uid INT PRIMARY KEY NOT NULL,
   uname VARCHAR(50) NOT NULL,
   ugender VARCHAR(50) NOT NULL,
   uclass VARCHAR(50) NOT NULL COMMENT"班级",
   uclass_id INT
);
SELECT *FROM student2;
DROP TABLE student2;
INSERT INTO student2(uid,uname,ugender,uclass,uclass_id)
       VALUES(1,"张三","男","大数据32001",1),
           (2,"李四","女","大数据32002",1),
           (4,"王五","男","虚拟现实32001",2),
           (5,"老六","男","虚拟现实32002",3),
           (6,"老气","男","虚拟现实32004",3),
           (7,"老八","男","虚拟现实32005",3);
SELECT w.*,a.* FROM student w LEFT JOIN student2 a ON w.sid=a.uid;

4.子查询，需要案例

CREATE TABLE IF NOT EXISTS de
(
   cid INT PRIMARY KEY AUTO_INCREMENT,
   cname VARCHAR(50) NOT NULL,
   teacher_id INT
);
INSERT INTO de(cname,teacher_id)
   VALUES("语文",1),
       ("数学",2),
       ("英语",3);
SELECT *FROM de;

CREATE TABLE IF NOT EXISTS lol
(
   lid INT,
   student_id INT,
   corse_id INT,
   NUMBER INT
);
INSERT INTO lol(lid,student_id,corse_id,NUMBER)
   VALUES(1,1,1,79),
   (1,1,2,80),
   (1,1,3,70),
   (2,2,1,66),
   (2,2,2,80),
   (2,2,3,87),
   (3,3,1,97),
   (3,3,2,69),
   (3,3,3,89);
SELECT *FROM lol;
SELECT st.*,d.* FROM student st
LEFT JOIN lol d ON st.sid=d.lid
LEFT JOIN de e ON e.teacher_id=d.corse_id;