数据库基础语句

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