1.基础建库建表
#建库
CREATE DATABASE IF NOT EXISTS yuesu DEFAULT CHARSET utf8;
#IF NOT EXISTS:做判断,没有就创建数据库
#yuesu 库名 DEFAULT CHARSET utf8;:设置编码格式为utf8
#使用库/调用此库
USE yuesu;
#建表
#表一
CREATE TABLE IF NOT EXISTS student(
id INT PRIMARY KEY, #字段名:id int类型 主键(唯一性)
NAME VARCHAR(20) #字段名:name varchar类型 长度为20
);
#IF NOT EXISTS:做判断,没有就创建 student:表名
#表二
CREATE TABLE IF NOT EXISTS student2(
id INT, #id int类型
NAME VARCHAR(20), #name varchar类型 长度为20
age INT, #age int类型
);
#基础格式:字段名+数据类型(长度)/数据类型;
#进阶格式:字段名+数据类型(长度)/数据类型+是否主键+是否为空+是否自增+字段说明....
# 红色部分自行根据需求添加
#PRIMARY KEY:主键 not null:不为空 AUTO_INCREMENT:自增 COMMENT:字段说明
#修改表的主键(PRIMARY KEY,)
ALTER TABLE student DROP PRIMARY KEY;#删除主键
ALTER TABLE student ADD PRIMARY KEY(id);#添加主键
#格式
#ALTER TABLE 表名 DROP PRIMARY KEY;#删除主键
#ALTER TABLE 表名 ADD PRIMARY KEY(字段名);#添加主键
2.插入,删除表数据
CREATE DATABASE IF NOT EXISTS teat3 DEFAULT CHARSET utf8;
USE teat3;
CREATE TABLE IF NOT EXISTS emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)NOT NULL COMMENT'姓名',
salary INT NOT NULL COMMENT'工资',
hireday DATE NOT NULL COMMENT '入职日期'
)
#插入语句
INSERT INTO emp(NAME,salary,hireday)
VALUES("李四",3000,"2010-10-10");
INSERT INTO emp(NAME,salary,hireday)
VALUES("张三",5000,"2010-10-10"),
("里斯",6000,"2010-10-10"),
("章节",10000,"2010-10-10");
#查询语句
SELECT *FROM emp; # 查询 *来自 emp表 :(“*”代表所有,这里指表里所有的字段)
#删除语句
DELETE FROM emp;
#判断删除语句
DELETE FROM emp WHERE NAME="李四"; #删除name字段叫李四的数据
DELETE FROM emp WHERE hireday<"2010-01-01"; #删除hireday字段小于2010-01-01的数据
·
3.查询,排序,去重
#创建库CREATE DATABASE IF NOT EXISTS xxl_cs DEFAULT CHARSET utf8;
USE xxl_cs;
#创建表结构
CREATE TABLE IF NOT EXISTS cs_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
gender VARCHAR(5) NOT NULL,
age INT NOT NULL
)
#插入表数据
INSERT INTO cs_user(username,PASSWORD,gender,age)
VALUES("张三","123456","男",18),
("丽儿","123123","女",17),
("阿里","wodemima","男",22),
("案例","123asd","男",17)
UPDATE cs_user SET age=50,PASSWORD="22222222" WHERE username="阿里";
#查询语句
SELECT * FROM cs_user;
SELECT * FROM cs_user WHERE age>=16 GROUP BY gender HAVING PASSWORD="123456";
SELECT * FROM cs_user WHERE age>=16;
SELECT * FROM cs_user WHERE age BETWEEN 16 AND 26;
SELECT * FROM cs_user WHERE username IN("张三","阿里","案例");
#查询语句,group by分组 +having过滤
SELECT COUNT(*) c FROM cs_user GROUP BY gender HAVING c>4;
#distinct去重
SELECT DISTINCT username FROM cs_user;
#排序
SELECT * FROM cs_user ORDER BY age DESC;
#limit 从第二条开始查5条
SELECT * FROM cs_user LIMIT 1,3;
4.联合查询,左关联,右关联
CREATE DATABASE IF NOT EXISTS web DEFAULT CHARSET utf8;
USE web;
#表一
CREATE TABLE IF NOT EXISTS webaites(
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
)
INSERT INTO webaites(NAME,url,alexa,country)
VALUES("google","https://www.google.com",1,"usa"),
("淘宝","https://www.taobao.com",13,"cn"),
("菜鸟","https://www.cainiao.com",4689,"cn"),
("微博","https://www.weibo.com",20,"cn"),
("facebook","https://www.facebook.com",3,"usa"),
("staokoverflow","https://www.staokoverflow.com",0,"ind"),
("百度","https://www.baidu.com",4,"cn")
#表二
CREATE TABLE IF NOT EXISTS access_log(
aid INT PRIMARY KEY AUTO_INCREMENT,
site_id INT NOT NULL,
COUNT INT DEFAULT 0,
DATE DATE NOT NULL
)
INSERT INTO access_log(site_id,COUNT,DATE)
VALUES(1,345,"2016-05-10"),
(2,425,"2016-05-10"),
(3,450,"2016-05-10"),
(4,234,"2016-05-10"),
(5,101,"2016-05-10"),
(6,93,"2016-05-10"),
(7,56,"2016-05-10"),
(8,789,"2016-05-10"),
(9,223,"2016-05-10"),
(10,666,"2016-05-10")
#表关联
#内连接 inner join....on
#将两表相同部分查询在一起显示
#webaites w意思是给表webaites设置了个别名w,可以通过w来调用表webaites
SELECT w.*,a.*FROM webaites w INNER JOIN access_log a ON w.id=a.site_id;
#左外连接 left join.....on 相同部分+左表所有数据
#左关联就是代码中 前一张表的全部部分和相同的部分
SELECT w.*,a.*FROM webaites w LEFT JOIN access_log a ON w.id=a.site_id;
#右外连接 right join.....on 相同部分+右表所有数据
#右关联就是代码中 后一张表的全部部分和相同的部分
SELECT w.*,a.*FROM webaites w RIGHT JOIN access_log a ON w.id=a.site_id;
#全连接相同部分+右表所有数据+左表所有数据
SELECT w.*,a.*FROM webaites w LEFT JOIN access_log a ON w.id=a.site_id
UNION
SELECT w.*,a.*FROM webaites w RIGHT JOIN access_log a ON w.id=a.site_id;
#联合查询 union,就是两表放一起展示
SELECT * FROM access_log
UNION ALL
SELECT * FROM webaites;
三表关联
#这是上边两表关联的进阶
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)
)
#科目表
CREATE TABLE IF NOT EXISTS sub(
subno INT PRIMARY KEY AUTO_INCREMENT,
subname VARCHAR(25)
)
INSERT INTO sub(subname)
VALUES("java"),("c"),("python"),("mysql");
#成绩表
CREATE TABLE IF NOT EXISTS score(
NO INT,
score INT,
subno INT
)
INSERT INTO score(NO,score,subno)
VALUES(1,89,1),
(2,78,2),
(3,96,3),
(4,85,4)
#三表关联
#基于stu表,先左关联score表,再将关联结果左关联sub表
SELECT st.*,sc.* FROM stu st
LEFT JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno;