SQL基础语法_刘雯丽

#1.建库建表语法，字段数据类型, 需要案例
#2.增删改查语法，需要案例
#3.表关联查询，需要案例

#创建库
CREATE DATABASE IF NOT EXISTS zuoye DEFAULT CHARSET utf8;
#使用库
USE zuoye;
#插入数据
CREATE TABLE IF NOT EXISTS shuju(id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
PASSWORD VARCHAR(20) NOT NULL,
gender VARCHAR(20) NOT NULL,
age INT NOT NULL
);
#增
INSERT INTO shuju(username,PASSWORD,gender,age)VALUES("xiao","200000","男",20),
("xi","200001","女",19),
("xue","200002","男",23),
("sheng","200003","女",18),
("ya","200004","男",22);

#删
DELETE FROM shuju WHERE id=1;

#改
UPDATE shuju SET gender="男" WHERE id=4;

#查
SELECT * FROM shuju GROUP BY age;

#表关联（就不建表了）

#插入表数据1
INSERT INTO websites (NAME,url,alexa,country) VALUES
("google","http://www.google.com",1,"use"),
("淘宝","http://www.taobao.com",13,"cn")；

 

INSERT INTO access_log(site_id,COUNT,DATE)VALUES
(1,45,"2016-05-10"),
(2,100,"2016-05-10"),

USE web;
#内连接 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;
#左连接 left join...on
SELECT * FROM websites w LEFT JOIN access_log a ON w.id+a.site_id;
#右连接  right jion...on
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 FROM websites
UNION
SELECT aid FROM access_log;