SQL数据库操作

一、对于数据库的操作

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

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

-- 删除数据库
DROP DATABASE test;

二、数据库表的操作

1、对表的整体操作

-- 切换数据库
USE java;

-- 创建表
CREATE TABLE `student`(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` CHAR(10),
    `age` INT,
    `gender` CHAR(1)
);

-- 删除表
drop table student;

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC student;

-- 添加字段
ALTER TABLE student ADD COLUMN address CHAR(10);
DESC student;

-- 修改表的字段
ALTER TABLE student CHANGE address addr CHAR(20);
DESC student;

-- 删除字段
ALTER TABLE student DROP COLUMN address;
DESC student;

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

2、对表中内容的操作

-- * 代表查询所有的列
SELECT * FROM student;

1）插入

-- 插入数据
-- 插入部分字段值（必须把前面的字段名都写上）
INSERT INTO student(`id`,`name`,`age`,`gender`) VALUES (1,'wangwu',23,'男');
INSERT INTO student(id,NAME,age,gender) VALUES(3,'a',21,'nan');
INSERT INTO student VALUES (2,'w',21,'nan','1234');
SELECT * FROM student;

-- 一次插入多条数据
-- id定义了AUTO_INCREMENT自增，可以不用手动添加
INSERT INTO student(NAME,age,gender) VALUES('小张',23,'男'),('小张1',23,'男');
SELECT * FROM student;

2）修改

-- 修改数据
UPDATE student SET age = age + 1;
SELECT * FROM student;

-- WHERE带条件修改
UPDATE student SET age = age + 1 WHERE id = 1;
SELECT * FROM student;

3）删除

-- 删除数据
DELETE FROM student WHERE age = 24; -- 所有age是24的数据都被删除，可能多条数据age都是24
-- SELECT from student; -- 删除所有数据
SELECT * FROM student;

-- id是主键，根据id删除只删除唯一的一条数据
-- 删除后其他数据的id值不变
DELETE from student where id = 2;
SELECT * FROM student;

-- 删除所有数据 id值会继续递增，不会从初始值1开始
DELETE FROM student;
INSERT INTO student(`name`,`age`,`gender`) VALUES ('wangwu',23,'男');
SELECT * FROM student;

-- TRUNCATE删除表里所有数据，自增的id会重新初始化为初始值1
TRUNCATE TABLE student;
SELECT * FROM student;

INSERT INTO student(`name`,`age`,`gender`) VALUES ('wangwu',23,'男');

4）查询

查询数据

-- 查询
INSERT INTO student(id,NAME,age,gender) VALUES (1,'张三',24,'男'),(2,'李四',22,'女'),(3,'王五 ',23,'男'),(4,'赵六',25,'男');
-- 查询所有列
SELECT * FROM student;

-- 查询指定列
SELECT id,NAME,age,gender FROM student;

-- 查询时添加常量列，通过as可以起别名
SELECT id,NAME,age,'java2107' AS '班级' FROM student;

-- 通过as可以起别名
SELECT id,NAME,age AS '年龄','java2107' AS '班级' FROM student;

-- 查询时候合并列，字段名可以当成java里面的变量来运算
ALTER TABLE student ADD COLUMN chinese INT;
ALTER TABLE student ADD COLUMN math INT;

SELECT id,NAME,age,gender,(chinese+math) AS '总成绩' FROM student;

-- 查询时候去掉重复的记录
INSERT INTO student(NAME,age) VALUES('赵柳',25),('赵柳',24);
SELECT * FROM student;

SELECT DISTINCT NAME FROM student;

-- 逻辑查询 AND OR
SELECT * FROM student WHERE NAME = '赵柳' AND age = 24;

SELECT * FROM student WHERE NAME = '赵柳' OR age = 24;

-- 比较查询 比较运算符  between and
SELECT * FROM student WHERE age >= 24;

SELECT * FROM student WHERE age BETWEEN 23 AND 25;

SELECT * FROM student WHERE age != 25;

聚合查询

• 聚合查询函数：sum(),avg(),max(),min(),count()
• count()统计的是指定列不包含null的数据个数

SELECT SUM(age) AS '年龄和' FROM student;

SELECT AVG(age) AS '平均年龄' FROM student;

SELECT MAX(age) AS '最大年龄' FROM student;

SELECT MIN(age) AS '最小年龄' FROM student;

-- count()统计的是指定列不包含null的数据个数
SELECT COUNT(id) AS '总人数' FROM student;

SELECT COUNT(gender) AS '总人数' FROM student;

查询排序

• 语法：order by字段，asc/desc默认是asc升序，可以不写

SELECT * FROM student ORDER BY age;

SELECT * FROM student ORDER BY age DESC;

-- 多个条件排序
-- 整体按age升序，如果age相同按id降序
SELECT * FROM student ORDER BY age ASC,id DESC;

分组查询

-- 分组查询
SELECT gender AS '性别',COUNT(id) AS '人数' FROM student GROUP BY gender;
SELECT gender AS '性别',COUNT(id) AS '人数' FROM student GROUP BY gender ORDER BY COUNT(id);

-- 分组查询后筛选
-- 使用having而不是where
SELECT gender AS '性别',COUNT(id) AS '人数' FROM student GROUP BY gender HAVING COUNT(id)>=2;