sql 常用语句

数据仓库的操作

登录数据库

mysql -h 192.168.1.110 -P 3306 -u root –p（h-host 主机ip P-port端口号 –u-user-用户 –p输入密码）

示例

-- 关于数据仓库的操作
-- 创建库demo1
CREATE DATABASE demo1;
-- 创建demo2库 指定编码表为utf-8
CREATE DATABASE demo2 CHARACTER SET utf8;
-- 创建demo3库 指定编码表为utf8 并带校对规则
CREATE DATABASE demo3 CHARACTER SET utf8 COLLATE utf8_bin;
-- 每一个数据库创建后，在data目录下会创建一个文件夹，文件夹下有一个db.opt 里面指定了编码和校对规则


-- 删除数据库
DROP DATABASE demo1;

-- 查询所有的库
SHOW DATABASES;


-- 修改数据库字符集

-- 1.查询demo2的编码
SHOW CREATE DATABASE demo2;
-- 2.修改为gbk
ALTER DATABASE demo2 CHARACTER SET gbk;

-- 切换当前使用的数据库
USE demo2;
-- 查看当前正在使用的数据库
SELECT DATABASE();

-- 以上都属于数据定义语言

数据表的操作

数据表

在创建数据表格的时候，需要指定表格每列的列名，同时还要定义出每个列存放的数据类型。需要搞清楚java中的数据类型和数据库中的数据类型的对应关系。
一个数据表中可以存在很多列（字段） 每列具有数据类型和能存放数据的长度。
创建表时没有指定字符集，将采用数据库默认字符集。
创建表之前必须使用use db 来切换到数据库里面。
在创建数据表时一般只有字符串需要指定长度。

示例

创建表

-- 创建用户表
CREATE TABLE users(
    id INT,
    username VARCHAR(100),
    password VARCHAR(100),
    sex CHAR(2),
    address VARCHAR(200)
);

-- 查看表结构
DESC users;

在创建数据表的时候，一般要求数据表都要遵守一定的规范（三范式）
表的数据需要有约束：常见的约束有三种：
1，主键约束（唯一非空）把数据表的某列设置为这个表中每行的标识。每张表都有主键，一般主键列的名称是id。
2，唯一约束 标识的这列的值也不能重复 可以加在很多列上 但主键只能加在某一列上。经常用在手机号码 用户昵称上面。
3，非空约束 表示某一列的数据不能存null
以上的三个约束是单张表的约束
如果有多张表之间还有4，外键约束

-- 创建一张有约束的表 创建员工表
CREATE TABLE employee(
    eid     INT PRIMARY KEY,-- 主键约束
    ename VARCHAR(100) NOT NULL,-- 非空
    telephone VARCHAR(11) UNIQUE  NOT NULL, -- 唯一和非空
    sex VARCHAR(2) NOT NULL,
    address VARCHAR(100)
);

-- 查看employee表的结构
DESC employee;


/*主键自增长*/
CREATE TABLE employee2(
    eid     INT PRIMARY KEY AUTO_INCREMENT,-- 主键约束 当主键为int或者bigint时 可以设置成自增长
    ename VARCHAR(100) NOT NULL,-- 非空
    telephone VARCHAR(11) UNIQUE  NOT NULL, -- 唯一和非空
    sex VARCHAR(2) NOT NULL,
    address VARCHAR(100)
);
-- 查看employee表的结构
DESC employee2;

修改表

/*修改employee这个表*/

-- 添加生日列
ALTER TABLE employee ADD birthday DATE NOT NULL;
-- 修改现有列的类型  修改address的长度 非空等
ALTER TABLE employee MODIFY address VARCHAR(50) NOT NULL;
-- 修改列名 不为空也改为了为空
ALTER TABLE employee CHANGE ename username VARCHAR(100) NOT NULL;
-- 删除电话列
ALTER TABLE employee drop telephone;
-- 修改表名
RENAME TABLE employee TO yuangong;
DESC yuangong;
-- 修改字符集
ALTER TABLE yuangong CHARACTER SET gbk;
SHOW CREATE TABLE yuangong;

删除表

/*删除表*/
DROP TABLE employee2;

对数据表中数据的操作

创建一个学生表

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sage INT,
    sname VARCHAR(32) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    score DOUBLE NOT NULL,
    birthday DATE
);

向表中插入数据

-- 给学生表插入数据
INSERT INTO student(id, sage, sname, gender, score, birthday) VALUES(NULL, 12, '张三', '男', 99.99, '1997-08-01');
-- 如果是对每列都插入数据，可以省略列名不写
INSERT INTO student VALUES(NULL, 20, '李四', '女', 78.99, '1998-08-01');
-- 如果不是对每一列输入数据，必须写列名
INSERT INTO student(id, sname, gender, score, birthday) VALUES(NULL, '王五', '男', 60.9, '1987-03-21');

修改一条记录

/*修改某一条记录 注意一定要加条件*/
-- 将所有学生的年龄改为20岁
UPDATE student SET sage =20;
-- 将李四的学生成绩改为60分
UPDATE student SET score=60 where sname='李四';
-- 将李四的学生成绩改为70 性别改为男
UPDATE student SET score=70,gender='男' WHERE sname='李四';
-  将李四的学生成绩在原有的基础上减10分
UPDATE student SET score = score-10 WHERE sname='李四';

删除表中数据记录

/*删除表中的数据*/
-- 删除赵三的记录
DELETE FROM student WHERE sname='张三';
-- 删除表中所有数据 一般不用
-- DELETE FROM student;
-- 如果真的需要删除整张表的数据 建议使用以下数据 效率更高 上面的数据是一条一条删除
-- TRUNCATE TABLE student;直接把表全部删了 然后重新建一张新的 TRUNCATE属于ddl，delete属于dml
-- 事务管理只能对DML有效 被事务管理SQL语句可以回滚到SQL执行前的状态

查询

普通的查询

INSERT INTO student(id, sage, sname, gender, score, birthday) VALUES(NULL, 12, '张三', '男', 99.99, '1997-08-01');

-- 普通的查询
-- 1，查询表中所有学生的信息
SELECT * FROM student;-- *代表列的信息 不写的话就显示所有的列 等价于下面这一句
SELECT id,sage, sname, gender, score, birthday FROM student;
-- 2,查询某些列
SELECT id, sname FROM student;
-- 3,查询的过程中去除重复的数据
SELECT DISTINCT sage FROM student;
-- 4,查询表中所有学生的姓名和成绩
SELECT sname,score FROM student;
使用distinct需要注意：
https://www.yiibai.com/mysql/distinct.html

列名执行运算

-- 列名执行运算
-- 1，在查询的所有学生的成绩上加10分 注意只是查询结果加10分 数据库中并没有改变
SELECT sname, score +10 FROM student;
-- 2，有时需要给列或者表本身起别名 使用as关键字 as也可以不写 
SELECT score as 分数,score+10 as 加分 FROM student;
SELECT score as 分数,score+10 加分 FROM student;
-- 看下面这个语句查询几列数据
SELECT score sage FROM student;

注意：如果需要对小数进行精确运算，这时创建表时，
不能使用double或者float作为列名，因为他们都是近似值
可以使用NUMERIC 和DECIMAL 这就是精确值 不是近似值
它们的用法相同 NUMERIC(M,N)其中m表示的总的位数，n表示小数占有的位数
在java里面可以用BIGDECIMAL 来代替int或者long 它不是近似的

按照条件查询

-- 按照条件查询
-- 1，查询姓名为张三的信息
SELECT * FROM student where sname='张三';
SELECT * FROM student where score>70;
SELECT * FROM student where gender<>'男';-- 这个是不等于的符号
SELECT * FROM student where score between 70 and 100;
SELECT * FROM student where sage in(20,21);
SELECT * FROM student where sage not in(20,21); 
SELECT * FROM student where sname like '张%';-- 查询所有姓张的学生 这是模糊查询
SELECT * FROM student where sname like '%四%';
SELECT * FROM student where sname like '张_';-- 查询所有张姓的两个字名字的信息 这是模糊查询

关于条件的运算符

-- 关于条件的运算符
-- 查询成绩在80-100的学生信息
SELECT * FROM student where score>=80 && score <=100;-- 支持
SELECT * FROM student where score>=80 && score <=100;-- 标准
SELECT * FROM student where sage=20 || sage=21;
SELECT * FROM student where sage=20 OR sage=OR;
SELECT * FROM student where sage>20 AND score>80;
--  查询没有性别的同学
SELECT * FROM student where gender is NULL;
SELECT * FROM student where gender is NOT NULL;
-- SELECT * FROM student where gender = NULL;错误的 在sql中null 永远不等于null

查询结构排序

-- 查询结构排序
-- 
SELECT * FROM student ORDER BY score ASC;
SELECT * FROM student ORDER BY score DESC;
-- 先按照成绩降序，在按照年龄升序
SELECT * from student ORDER BY score ASC,sage DESC;

SQL的函数

函数

聚集（聚合）函数：也称为分组函数，主要是指sql中的内置函数。用于分组统计。
SQL语言本身定义了部分的函数（在w3c查看），这些是所有数据库通用的，但是不同的数据库在sql之外还定义了适合自己数据库的更多的函数。
SQL functions
SQL avg()
SQL count()
SQL first()
SQL last()
SQL max()
SQL min()
SQL sum()
SQL Group By
SQL Having
SQL ucase()
SQL lcase()
SQL mid()
SQL len()
SQL round()
SQL now()
SQL format()

示例

/*
    count函数：统计总数
    SELECT COUNT(列名) FROM 表名 [WHERE]
    count（*）只要某一行中的数据不全为null 该行就会被统计
    count（列名）只要这一列的数据为null 该行就不会被统计
*/
-- 统计一个班的学生
SELECT COUNT(*) FROM student;-- 统计一个班的学生
SELECT COUNT(sage) FROM student;
SELECT COUNT(id) FROM student;
SELECT COUNT(id) FROM student where score>80;

/*
    统一列的和值
    SELECT sum(列名) FROM 表名 [WHERE]
*/
-- 1、统计一个班的成绩之和
SELECT SUM(score) FROM student;
-- 2、分别统计年龄和分数之和
SELECT SUM(score),SUM(sage) FROM student;
-- 3、统计年龄和分数之和
SELECT SUM(score+sage) FROM student;-- 这样计算出来的结果可能不对
SELECT SUM(score)+SUM(sage) FROM student;
/*
    注意：
    如果在运算中遇到了null，null和其他的数据进行运算，结构还是null
    可以使用mysql中的ifnull函数来判断某个列的值是否为null，如果为null的话，给null指定一个值
    上面的语句可以改写为：
    */
SELECT SUM(IFNULL(score,0)+IFNULL(sage,0)) FROM student;

/*
    AVG函数：计算某列的平均值
    SELECT AVG(列名) FROM 表名 [WHERE]
    */
    -- 求一个班的分数的平均值
    SELECT SUM(score)/COUNT(score) FROM student;
    SELECT AVG(score) FROM student;

/*
    计算某列的最大最小值 max() min()
*/
SELECT MAX(sage),MIN(score) FROM student;

/*
    针对根据某列进行操作的函数都不能直接用在where的条件中
*/
SELECT * FROM student WHERE score>AVG(score);-- 语法错误
SELECT * FROM student HAVING score>AVG(score);-- 遗留问题 这个查询的结果是不对的

示例

/*
    GROUP BY：根据某列的特点进行分组
    SELECT 分组函数 FROM 表名     GROUP BY 列名
*/
CREATE TABLE orders(
    id INT,
    produce VARCHAR(20),
    price INT
);
INSERT INTO orders(id,produce,price) VALUES(1,"电视",900);
INSERT INTO orders(id,produce,price) VALUES(2,"洗衣机",1000);
INSERT INTO orders(id,produce,price) VALUES(3,"洗衣粉",90);
INSERT INTO orders(id,produce,price) VALUES(4,"橘子",9);
INSERT INTO orders(id,produce,price) VALUES(5,"洗衣粉",90);


-- 1、对订单中的商品归类后，按商品分类显示总价
SELECT * FROM orders GROUP BY produce;-- 这只是一个分组

SELECT produce,SUM(price) FROM orders GROUP BY produce;-- 先找到相同的内容 在进行分组

-- 2、查询购买的商品，并且购买的商品总价大于100
SELECT produce,SUM(price) FROM orders GROUP BY produce HAVING SUM(price)>100;

函数的解析顺序

/*
    考虑一个问题 上面的语句是先分组还是先过滤
    1.一般来说 在查询中 一般是先分组 后过滤 在排序
    select语句中的小结
    S-F-W-G-H-O SELECT...FROM...WHERE...GROUPBY...HAVING...ORDERBY;顺序不能改变
    解析顺序：from-where-groupby-having-orderby
*/