<span>数据库基础语句</span>
1、创建数据库
-- 1、创建数据库
CREATE DATABASE school CHARACTER SET utf8;
2、创建表
-- 2、创建表
CREATE TABLE stu(
stuid INT PRIMARY KEY AUTO_INCREMENT,
stuname VARCHAR(20) NOT NULL,
stusex VARCHAR(5) DEFAULT '男' CHECK(stusex='男' OR stusex='女'),
stuage INT DEFAULT 0
) DEFAULT CHARSET=utf8;
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
-- unique: 值唯一
cname VARCHAR(20) NOT NULL UNIQUE
) DEFAULT CHARSET=utf8;
CREATE TABLE stu_score(
id INT PRIMARY KEY AUTO_INCREMENT,
stuid INT,
cid INT,
score DOUBLE,
-- 设置外键(一般不建议设置)
-- FOREIGN KEY(stuid) REFERENCES stu(stuid),
-- FOREIGN KEY(cid) REFERENCES course(cid)
) DEFAULT CHARSET=utf8;
3、插入
-- 3、插入数据
INSERT INTO stu(stuname) VALUES('规划明');
INSERT INTO course VALUES(NULL,'高等数学');
INSERT INTO stu_score VALUES(NULL,1,2,66.6);
4、查询
4.1、过滤查询
-- 4、过滤查询
SELECT * FROM stu;
SELECT * FROM stu WHERE stuname LIKE '%明';
SELECT * FROM stu WHERE stuname LIKE '小_';
4.2 、起别名
-- 对查询结果起别名，修改列名显示
SELECT stuname AS '姓名' ,stuage AS '年龄' FROM stu;
4.3、分页查询（limit）
-- 显示4条查询数据
-- 0：开始位置；4：显示4个
SELECT * FROM stu LIMIT 0,4;
4.4、子查询
-- 查询成绩为100分的学生的姓名
SELECT stuid FROM stu_score WHERE score=100;
SELECT stuname FROM stu WHERE stuid = 2;
SELECT stuname FROM stu WHERE stuid =(
SELECT stuid FROM stu_score WHERE score=100
)
4.5 、连接查询
4.5.1内连接？
-- 查询分数信息，显示学生姓名，课程名，分数。
-- 第一种方法
-- 1.查什么写什么
SELECT stuname,cname,score FROM stu,course,stu_score
-- 2.建立关系
WHERE stu.stuid=stu_score.stuid
AND course.cid=stu_score.cid
-- 第二种方法
SELECT stuname,cname,score FROM stu
INNER JOIN stu_score ON stu.stuid=stu_score.stuid
INNER JOIN course ON stu_score.cid=course.cid
4.5.2左外连接和右外连接？
左外连接：显示左表的全部信息，右表没有的部分用null表示。右外连接类似。
-- 查询学生分数，显示学生姓名和分数
SELECT stuname,score FROM stu
LEFT JOIN stu_score ON stu.stuid=stu_score.stuid
4.6 、case语句
SQL中CASE语句相当于if---else 语句
-- 显示学生性别信息
SELECT stuid,stuname,stusex,stuage FROM stu;
-- CASE有两种格式
-- 简单case函数
SELECT stuid,stuname,
CASE stusex
WHEN '1' THEN '男'
WHEN '0' THEN '女'
ELSE '保密'
END,stuage FROM stu
-- case搜索函数
SELECT stuid,stuname,
CASE WHEN stusex='1' THEN '男'
WHEN stusex='0' THEN '女'
ELSE '保密'
END,stuage FROM stu
select 与 case结合使用好处：
- 一是在显示查询结果时可以灵活的组织格式
- 二是有效避免了多次对同一个表或几个表的访问
5、删除
DELETE FROM stu WHERE stuname='哈哈';
6、修改
UPDATE stu SET stusex='女' WHERE stuname='哈哈';
7、去重复（distinct）
-- 7、去重复，作用于单列或多列。返回唯一不同的值。distinct必须放在开头。
-- 去掉相同的stuname，然后显示信息
SELECT DISTINCT stuname FROM stu;
8、统计（count）
-- 8、 统计: count
-- 学生表中stuname去重后的数目
SELECT COUNT(DISTINCT stuname) FROM stu;
-- 统计学生表中一共有多少个学生
SELECT COUNT(stuid) FROM stu;
SELECT COUNT(1) FROM stu;
SELECT COUNT(*) FROM stu;
8.1、COUNT(*)和COUNT(1)的区别
这两种方式都可以用来统计表中总数据量，但是他们的实现方式略有不同。
-
COUNT(*)是统计表中的行（该行数据同时都不为null）的数据总量，
也就是说如果有一行所有数据都为null，那么就不会被统计。
-
COUNT(1)是通过在该表的表结构中添加一列，该列的数据都为1，
最终是通过统计该列中有多少个数据为1的数量，即为数据总数。
9、排序
升序: asc 降序: desc
-- 9 、排序 默认升序
SELECT * FROM stu ORDER BY stuage;
-- 升序: asc 降序: desc
SELECT * FROM stu ORDER BY stuage ASC;
SELECT * FROM stu ORDER BY stuage DESC;
-- 根据查询结果的第二列进行降序排序
SELECT stuname,stuage FROM stu ORDER BY 2 DESC;
10、常用的聚合函数
| count | sum | avg | max | min |
|---|---|---|---|---|
| 计数 | 求和 | 平均值 | 最大值 | 最小值 |
-- count 计数;sum 求和;avg 平均值;max 最大值;min 最小值
SELECT SUM(score) FROM stu_score;
SELECT AVG(score) FROM stu_score;
11、null 的特殊性
1、在sql中，任何数据和null做运算，运行的结果还是null
４＋NULL
NULL/0
2、null不是0
3、可以通过特定的函数，将null设置为特定的值。注意有null值时，结果为null
4、将字段中null值替换为指定值的方法
-- 方法1:使用isnull替换
select keyId,isnull(info,'替换null值') as info from test
-- 方法2:使用case when 替换
select keyId,case when info is null then '替换null值' else info end as info from test
-- 方法3:使用coalesce替换相应的值
select keyId , coalesce(info,'替换null值') as info from test
