数据库操作

一、单表

-- 列出所有的数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE study DEFAULT CHARACTER SET utf8;

-- 删除数据库
DROP DATABASE study;

-- 数据库表的操作
-- 切换数据库
USE study;

-- 创建表
CREATE TABLE student1(
id INT,
`name` CHAR(10),
age INT,
gender CHAR(1)
);

-- 查看所有表
SHOW TABLES;
-- 查看表的结构
-- description
DESC student;
-- 删除表
DROP TABLE student1;



-- 修改表的结构
-- 添加地段
ALTER TABLE student ADD COLUMN address CHAR(10);
-- 删除地段
ALTER TABLE student DROP COLUMN address;
-- 修改表的字段
ALTER TABLE student CHANGE address addr CHAR(20);



-- 修改表的名字为stu
ALTER TABLE student RENAME TO stu;



-- 创建表
-- AUTO_INCREMENT实现自动增长
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` CHAR(10),
age INT,
gender CHAR(1)
);
-- PRIMARY KEY 主键，不能重复，唯一确定一条记录 （unique+not null）
-- 表中的任何列都可以作为主键，只要它满足一下条件：
-- 1、任意两行都不具有相同的主键值
-- 2、每一行都必须具有一个主键值（主键列不允许空置NULL）
-- 3、主键列中的值不允许修改或更新
-- 4、主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

-- 插入数据
INSERT INTO student(id,`name`,age,gender) VALUES (1,'wangwu',23,'女');
-- duplicate entry 1 for key student.PRIMARYid为1的数据已经存在
INSERT INTO student(id,`name`,age,gender) VALUES (1,'wangwu',23,'女');
INSERT INTO student(`name`,age,gender) VALUES ('wang',25,'男');
-- 一次插入多条数据
INSERT INTO student(`name`,age,gender) VALUES ('wangwu2',23,'女'),('wangwu3',3,'男');

-- 删除数据
-- 全部删除在加入数据是按照原来的id值顺序继续向下加
DELETE FROM student;
-- 删除id为4的一行数据
DELETE FROM student WHERE id=4;
-- 全部删除再加入数据是从1开始顺序继续向下加
TRUNCATE TABLE student;

-- 修改全部数据
UPDATE student SET age=age+1;
-- 只修改一行的数据
UPDATE student SET age=age+1,`name`='ww' WHERE id=3;
 
-- 查询数据
-- 显示所有列(字段)数据
-- *代表所有的字段（列）
SELECT * FROM student;
SELECT id,`name` FROM student;
-- 查询指定列
SELECT `name`,age FROM student;
SELECT id,`name`,age,gender FROM student;
-- 查询的时候添加常量列，通过as可以起别名
SELECT id ,`name`,age AS '年龄','Java2403' AS '班级名' FROM student; 
-- 查询时候合并列，字段可以当成java里面的变量来运算
SELECT id,`name`,(php+java) AS '总成绩' FROM student;
-- 查询的是全部的记录
SELECT address FROM student;
-- 查询时候去掉重复的记录
SELECT DISTINCT address FROM student;

-- 条件查询 where,数据库表一般包含大量的数据，很少需要检索表中所有行
SELECT * FROM student WHERE gender='男';
-- 逻辑条件: and（同时成立） or(只要有一个成立)
SELECT * FROM student WHERE `name`='wang' AND address='济南';
SELECT * FROM student WHERE `name`='wang' OR address='青岛';

-- 比较运算: >  <  >=  <=  != 不等于也可以写成：<>
SELECT * FROM student WHERE java>=30 AND java<=60;

-- between and (等价于>= and <=)
SELECT * FROM 
tudent WHERE java BETWEEN 40 AND 50;

-- 查询地址不是青岛的学生信息 <>
SELECT * FROM student WHERE address != '青岛';
SELECT * FROM student WHERE address <> '青岛';

-- 空值NULL：无值(no value)，它与字段包含0、空字符串或紧紧包含空格不同
SELECT * FROM student WHERE address IS NULL; -- IS NOT NULL


-- 聚合查询
-- 聚合查询函数：sum(),avg(),max(),min(),count()
-- 统计学生php的总成绩（sum求和）
SELECT SUM(php) AS 'php总成绩' FROM student;
-- 计算学生的 PHP 和 Java 成绩的总和
SELECT (php + java) AS 'php和java总成绩' FROM student;
-- 计算所有学生的 PHP 和 Java 成绩总和
SELECT SUM(php + java) AS '所有人的总和' FROM student;
-- 统计学生php的平均值
SELECT AVG(php) AS 'php平均值' FROM student;
-- 统计学生php的最大值
SELECT MAX(php) AS 'php最大值' FROM student;
-- 统计学生表里面一共有多少学生
SELECT COUNT(*) AS '总人数' FROM student;
-- id一共有多少个
SELECT COUNT(id) AS '总人数' FROM student;
SELECT COUNT(address) AS '总人数' FROM student;
SELECT COUNT(name) AS '总人数' FROM student;
-- *代表所有的字段（列）
SELECT * FROM student;
-- 注意：如果指定列明，则COUNT会忽略指定列的值为NULL的行，用*则不忽略。


-- 查询排序 desc:descending asc:ascending
-- 语法：order by 字段  asc/desc  默认是asc升序，可以不写
SELECT * FROM student;
-- ASC升序排序,可以不写
SELECT * FROM student ORDER BY php ASC;
-- DESC降序排序
SELECT * FROM student ORDER BY php DESC;


-- 多个条件排序
-- 需求：先按照php降序，java升序(整体是按照php降序，如果php相同的数据再按照java标准排序)
SELECT * FROM student ORDER BY php DESC,java ASC;
-- order by要放在sql语句的最后
  
    
-- 分组查询(group by)
-- 需求：查询男女分别有多少人
-- 性别  人数
-- 男     2
-- 女     3
SELECT gender AS '性别',COUNT(*) AS '人数'
FROM student GROUP BY gender;
-- 分组之后的条件筛选用HAVING
-- 需求：address大于1
-- group by之后的条件查询使用having
-- GROUP BY字句必须出现在WHERE字句之后，ORDER BY字句之前
-- WHERE过滤行（行级过滤），而HAVING过滤分组（组级过滤），WHERE所有技术和选项都适用于HAVING，语法使用一样
SELECT gender AS '性别',COUNT(*) AS '人数'
FROM student GROUP BY gender HAVING COUNT(*)>1;



 
USE study;
CREATE TABLE student1(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` CHAR(10) NOT NULL, 
age INT COMMENT '年龄',-- COMMENT注释
address CHAR(10) DEFAULT '中国',
UNIQUE KEY(`name`)
);
-- not null： 不为空，表示该字段不能放“null”这个值。不写，则默认是可以为空

-- [primary] key： 设定为主键。
-- 是唯一键“加强”：不能重复并且不能使用null，并且可以作为确定任意一行数据的“关键值”，
-- 最常见的类似：where id= 8; 或 where user_name = ‘zhangsan’;
-- 通常，每个表都应该有个主键，而且大多数表，喜欢使用一个id并自增长类型作为主键。
-- 但：一个表只能设定一个主键

-- comment ‘字段注释’
-- default ‘默认值’： 设定一个字段在没有插入数据的时候自动使用的值。
-- unique [key] : 设定为唯一键：表示该字段的所有行的值不可以重复（唯一性）。

二、多表

  学生表、班级表、课程表、班级课程表

-- 多对多
-- 班级表
use study;
CREATE TABLE banji(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` CHAR(10) NOT NULL
);

INSERT INTO banji (`name`)VALUES('Java1807'),('Java1812');

use study;
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` CHAR(10) NOT NULL,
     age INT,
  gender CHAR(1),
  banji_id INT,
-- 设计班级id为外键
-- 当前表中的 banji_id 列与另一个表 banji 中的 id 列进行关联
  FOREIGN KEY(banji_id) REFERENCES banji(id)
);
INSERT INTO student(name,age,gender,banji_id) VALUES ('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);
-- a foreign key constraint fails (`study`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
-- 没有id为3的班级
INSERT INTO student(name,age,gender,banji_id) VALUES ('张三',20,'男',3);

use study;
-- 课程表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` CHAR(10) NOT NULL,
credit INT COMMENT '学分'
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);
-- 班级课程表
CREATE TABLE banji_course(
    -- id int PRIMARY KEY AUTO_INCREMENT,
    banji_id INT,
    course_id INT,
    PRIMARY KEY(banji_id,course_id), 
        -- 联合主键
    FOREIGN KEY(banji_id) REFERENCES banji(id),
         -- banji_id既是联合主键又是外键
    FOREIGN KEY(course_id) REFERENCES course(id)
-- course_id既是联合主键又是外键
);

INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);

-- 子查询：嵌套查询，一个查询语句结果是另一个查询语句的条件
-- 查询班级是java1812班所有学生信息
SELECT * FROM student WHERE banji_id=2;
SELECT id FROM banji WHERE name='Java1812';
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='java1812');

-- 班级是java1807班或者java1812班所有学生信息
SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
SELECT * FROM student WHERE banji_id IN (1,2);
SELECT id FROM banji WHERE name='Java1807' OR name='Java1812'; -- 1,2
SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE name='Java1807' OR name='Java1812');

-- 子查询
-- 计算字段使用子查询：班级id 班级名字 班级人数
-- 执行这条查询遵循下面的步骤：
-- 1、从banji表检索班级列表
-- 2、对检索出的每个banji，统计其在student表中的数量
-- 班级id 班级名字 班级人数
-- SELECT id,name FROM banji;
SELECT id,name,
    (SELECT COUNT(*) FROM student WHERE banji_id=banji.id)
FROM banji;

-- 等值连接
SELECT *
    FROM student,banji
WHERE student.banji_id=banji.id;

-- 内联结
-- 内连接有两种写法，一种是inner join，另一种是join
-- 班级名称   学生数量
SELECT * 
    FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id;

-- 班级id 班级名字 班级人数
SELECT b.id,b.name,Count(*)
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
GROUP BY b.id;

-- 班级名称   学生数量
SELECT b.name AS '班级名称',COUNT(*) AS '学生数量'
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
GROUP BY b.id;

-- 学生id 学生名字 班级名称
SELECT s.id AS '学生id',s.`name` AS '学生姓名',b.`name` AS '班级名称'
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id;


-- 学生id 学生名字 班级名称 课程名称 学分
use study;
SELECT *
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
INNER JOIN banji_course AS bc
ON b.id=bc.banji_id
INNER JOIN course AS c
ON bc.course_id=c.id;

SELECT s.`name` AS '学生姓名',b.`name` AS '班级名称',c.`name` AS '课程名称',c.credit AS '学分'
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
INNER JOIN banji_course AS bc
ON b.id=bc.banji_id
INNER JOIN course AS c
ON bc.course_id=c.id;

-- inner join on 只有左右两个表有关联的才查询出来
-- left join on 左表中都显示出来，右表没有显示空
-- right join on 右表都显示，左表没有显示空

-- inner join on 只有左右两个表有关联的才查询出来
SELECT *
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id

-- left join on 左表中都显示出来，右表没有显示空
SELECT *
FROM student AS s LEFT JOIN banji AS b
ON s.banji_id=b.id;

-- right join on 右表都显示，左表没有显示空
SELECT *
FROM student AS s RIGHT JOIN banji AS b
ON s.banji_id=b.id;

-- 模糊查找
-- 字段 like '要查找字符'
SELECT * FROM student WHERE name='张三';
-- 以张开头
SELECT * FROM student WHERE `name` LIKE '张%'; 
 -- 以张开头，而且名字是两个字
SELECT * FROM student WHERE `name` LIKE '张_';
-- 名字里面只要有张就可以
SELECT * FROM student WHERE `name` LIKE '%张%'; 

UPDATE student 
SET `name`='张三',age=24,gender='女'
WHERE id=1;

 

注意：

SQL的通配符很有用，但这种功能是有代价的，即通配符搜索要消耗更长的处理时间，使用通配符的技巧：

1、不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

2、在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处 '%张'。把通配符置于开始处，搜索起来是最慢的。