sql语句--黄一家

1、建库建表语法，字段数据类型，（案例）

2、增删改查语法，

3、表关联查询

4、子查询

#创建库
CREATE DATABASE yueshu DEFAULT CHARSET utf8;
#使用库
USE yueshu;
CREATE TABLE temp(
id INT,
NAME VARCHAR(20),
pwd VARCHAR(20),
PRIMARY KEY(id,NAME)
);
#删除主键(修改表结构)
ALTER TABLE temp DROP PRIMARY KEY;
#添加主键
ALTER TABLE temp ADD PRIMARY KEY(id);

#创建表
CREATE TABLE IF NOT EXISTS emp(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL COMMENT ‘员工姓名’,
salary INT NOT NULL COMMENT ‘员工工资’,
hiredate DATE NOT NULL COMMENT ‘入职日期’,
deptid INT NOT NULL
);
#插入语句
INSERT INTO emp(NAME,salary,hiredate,deptid)VALUES
(“王”,9000,“2020-10-10”,2);
#多条插入
INSERT INTO emp(NAME,salary,hiredate,deptid)VALUES
(“liu”,9000,“2020-10-10”,2),(“zhao”,7000,“2020-10-10”,3),(“li”,8000,“2020-01-10”,5);
#删除语句
#全部删除
DELETE FROM emp;
#局部删除
DELETE FROM emp WHERE NAME=“zhao”;
DELETE FROM emp WHERE hiredate < “2011-12-12”;

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

CREATE TABLE IF NOT EXISTS sudent(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL UNIQUE,
age INT NOT NULL,
sex VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
math INT NOT NULL,
english VARCHAR(20) NOT NULL
);

INSERT INTO sudent(NAME,age,sex,address,math,english)VALUES(“马云”,55,“男”,“杭州”,66,78),
(“马化腾”,45,“女”,“深圳”,98,87),
(“马锦涛”,55,“男”,“香港”,56,77),
(“柳岩”,20,“女”,“湖南”,76,765),
(“刘德华”,57,“男”,“香港”,99,99),
(“玛德”,22,“女”,“香港”,99,99),
(“德玛西亚”,18,“男”,“南京”,56,65);

SELECT * FROM sudent WHERE math > 80;
SELECT * FROM sudent WHERE english >= 80;
SELECT * FROM sudent WHERE age = 20;
SELECT * FROM sudent WHERE age != 20;
SELECT COUNT(*) FROM sudent;
SELECT * FROM sudent WHERE NAME LIKE “马%”;
SELECT * FROM sudent GROUP BY address;
SELECT sex,COUNT(sex) 总数 FROM sudent GROUP BY sex;
SELECT * FROM sudent ORDER BY math;

#建库
CREATE DATABASE IF NOT EXISTS ku DEFAULT CHARSET utf8;
USE ku;
#建表
CREATE TABLE IF NOT EXISTS websites(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
url VARCHAR(50) NOT NULL,
alexa INT NOT NULL COMMENT “网站排名”,
country VARCHAR(20) NOT NULL
);
#插入数据1
INSERT INTO websites(NAME,url,alexa,country)VALUES
(“google”,“http://t.cn/RhJHwfb”,1,“usa”),
(“淘宝”,“http://t.cn/EU6rbLj”,13,“cn”),
(“菜鸟”,“http://t.cn/RTW2MKE”,4689,“cn”),
(“微博”,“http://t.cn/SyzbqZ”,20,“cn”),
(“facebook”,“http://t.cn/hgp5y1”,3,“usa”),
(“stackoverflow”,“http://t.cn/RhJlXUd”,0,“ind”),
(“百度”,“http://t.cn/Rp7YRQG”,4,“usa”);
#键表2
CREATE TABLE IF NOT EXISTS access_log(aid INT PRIMARY KEY AUTO_INCREMENT,
site_id INT NOT NULL,
COUNT INT DEFAULT 0,
DATE DATE
);
#插入数据2
INSERT INTO access_log(site_id,COUNT,DATE)VALUES
(1,45,“2016-05-10”),
(2,100,“2016-05-10”),
(5,415,“2016-05-10”),
(5,200,“2016-05-10”),
(6,7,“2016-05-10”),
(7,145,“2016-05-10”),
(8,45,“2016-05-10”),
(9,545,“2016-05-10”),
(10,201,“2016-05-10”);

USE ku;
#内连接 ,…inner join…on
SELECT * FROM websites w INNER JOIN access_log a ON w.id=a.site_id;
SELECT * FROM websites w,access_log a WHERE w.id=a.site_id;
#左连接
SELECT * FROM websites w LEFT JOIN access_log a ON w.id=a.site_id;
#右连接
SELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;
#全连接 = 左连接 union 右链接
SELECT * FROM websites w LEFT JOIN access_log a ON w.id=a.site_id
UNION
SELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;
#union
SELECT id,NAME FROM websites
UNION ALL
SELECT aid,DATE FROM access_log;

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
CREATE TABLE IF NOT EXISTS stu(NO INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
#插入数据
INSERT INTO stu(NAME)VALUES
(“张三”),
(“李四”),
(“王五”),
(“赵柳”),
(“田七”),
(“钱八”);
#创建科目表
CREATE TABLE IF NOT EXISTS sub(subno INT PRIMARY KEY AUTO_INCREMENT,
subname VARCHAR(20)
);
#插入数据
INSERT INTO sub(subname)VALUES
(“java”),
(“python”),
(“html”),
(“hdoop”),
(“js”);
#创建分数表 （中间表）
CREATE TABLE IF NOT EXISTS score(NO INT,
score INT, subno INT);
#插入数据
INSERT INTO score(NO,score,subno)VALUES
(1,88,1),
(2,90,3),
(3,85,3),
(4,60,4);
#三表关联查询，第一个表关联中间表的结果，然后关联第二个表
SELECT st.,sc.,su.* FROM stu st
INNER JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERE st.no > 1;

CREATE TABLE IF NOT EXISTS sid(id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
gender VARCHAR(10),
class_id INT
);
INSERT INTO sid(sname,gender,class_id)VALUES
(“张三”,“男”,1),
(“李四”,“男”,1),
(“王五”,“男”,2);

CREATE TABLE IF NOT EXISTS cid(id INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
teacher_id INT
);
INSERT INTO cid(cname,teacher_id)VALUES
(“语文”,1),
(“数学”,2),
(“英语”,3);

CREATE TABLE IF NOT EXISTS sd(sid INT,
student_id INT,
corse_id INT,
NUMBER INT);
INSERT INTO sd(sid,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);[/cp]