数据库基础1

MySQL数据类型

整数

注：1. 在能够满足需求的情况下，尽量选择占用空间小的类型。

       2. 指定unsigned 就是没有符号，否则为有符号。

CREATE TABLE t3 (
    id TINYINT UNSIGNED);

Bit

比如 bit(8) 表示一个字节的范围在 0~255(2^8-1)

小数

字符串 

CREATE TABLE t01(
	`name` CHAR(255));
CREATE TABLE t02(
	`name` VARCHAR(32766)) CHARACTER SET gbk;

注：1. 如果表的编码是 utf8 那么size最大是 (65532 - 3) / 3 = 21844

       2. 如果表的编码是 gbk 那么size最大是 (65532 - 3) / 2 = 32766

       3. 区分字符与字节！size里永远是字符！ 比如 char(4) 和 varchar(4) 都表示4个字符，但是对应的字节不同，需要计算(根据编码方式)。

      4. char是定长（固定的大小），比如 char(4)，即使只插入了'aa'，也会分配占用4个字符的空间。

      5. varchar是变长（变化的大小），就是说，varchar(4) ，如果你插入了'aa'，实际占用的空间大小只有两个字符。 当然varchar本身还要占用1~3个字节来记录存放的内容长度。

      6. 如果数据是定长，比如身份证号，邮编，md5的密码等，使用char。如果一个字段的长度是不确定的，比如留言，文章等，使用varchar。 char的好处在于查询速度较快。

      7. 如果varchar不够用，可以使用mediumtext或longtext，想简单点可以直接使用text

日期类

CREATE TABLE t1(
	birthday DATE,
	job_time DATETIME,
	login_time TIMESTAMP   //时间戳
		NOT NULL DEFAULT CURRENT_TIMESTAMP
		ON UPDATE CURRENT_TIMESTAMP);
#如果希望timestamp自动更新，需要手动输入上面两行代码

表的各种操作 

创建表 

注：在SQLyog中创建表非常容易，这里仅展示DOS的（可以在SQLyog的代码区使用）。只有在右括号后面结束后加分号，其它用逗号隔开，即使是最后的数据也不加分号。

CREATE TABLE `user`(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

删除表

 操作很简单：DROP TABLE 表名

DROP TABLE tableName

修改表(Alter语句等)

插入列

ALTER TABLE acc 
    ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME
#插入一个image列，varchar类型，不为空，初始值为''，在resume后面

显示表结构 

DESC acc #显示表结构，可以查看表的所有列

修改列的属性：注意有两种方法，MODIFY不能修改名字，但CHANGE可以

ALTER TABLE acc
	MODIFY image VARCHAR(60) NOT NULL DEFAULT '' #修改image列，使其长度为60

ALTER TABLE acc
	CHANGE `resume` `rrr` VARCHAR(64) NOT NULL DEFAULT '' 
	#修改列的属性：名字 长度 是否为空 初始值

删除列 

ALTER TABLE acc
	DROP image #删除image列

修改表名 

RENAME TABLE acc TO qqq #修改表名为class
DESC qqq #表名修改后，对应的操作也需要修改

修改表的字符集 

ALTER TABLE acc CHARACTER SET utf8 #修改表的字符集为utf8

Insert语句

INSERT INTO `goods` (id, goods_name, price)
	VALUES(1,'华为手机',2000); 

注意！一定不要带括号。

细节说明：

1. 插入的数据应与字段的数据类型相同。 但是如果能相互转化那就是可以的。

比如： 'abc' 放到int里是不行的（'a'也不行），但是'30'放到int里就是可以的。

2. 数据的长度应在列的规定范围内。比如：不能把一个长度为80的字符串加入到长度为40的列中

3. 字符和日期型数据应包含在单引号中。

4. 列可以插入null，前提是该字段允许为空(不写 NOT NULL，参见插入列)

5. insert into 列名 values (),(),()...  这样的形式可以添加多条记录。

INSERT INTO goods (id,goods_name,price)
	VALUES(60,'三星手机',2300),(60,'小米手机',3000)

6. 如果是给表中的所有字段添加数据，可以不写()里的字段名称。

INSERT INTO goods
	VALUES(60,'三星手机',2300),(60,'小米手机',3000)

7. 当不给某个字段值时，如果有默认值就会添加(default ...)，否则会报错。

Update语句

#把所有员工薪水修改为5000
UPDATE employee SET salary = 5000

#将姓名为 小天才 的员工薪水修改为3000元
UPDATE employee 
	SET salary = 3000
	WHERE user_name = '小天才'
	
#在原有基础上增加薪水
UPDATE employee 
	SET salary = salary + 1000
	WHERE user_name = '小天才'
	
#修改多个列
UPDATE employee
	SET salary = salary + 1000 , job = '策划'
	WHERE user_name = '小天才'

delete语句

#删除表中所有名称为 '一方通行' 的记录
DELETE FROM employee
	WHERE user_name = '一方通行'
	
#删除表中所有记录	
DELETE FROM employee

Select语句

#查询表中学生的所有信息
SELECT * FROM student;
#查询表中所有学生的姓名和对应的英语成绩
SELECT `name`,english FROM student;
#过滤表中重复数据 distinct 注：要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT english FROM student;

SELECT DISTINCT `name`,english FROM student;
#比如 english相同，但name不同，这样的就不会去重

#统计每个学生的总分
SELECT `name`,(chinese + english + math) FROM student;

#假设要显示总分+10的内容
SELECT `name`,(chinese + english + math + 10) FROM student;

#使用别名表示学生分数
SELECT `name`,(chinese + english + math) AS total_score FROM student;

where语句 

#查询姓赵的同学的成绩
SELECT * FROM student
	WHERE `name` LIKE '赵%'; #这里 %表示名字以赵开头的都行('赵'也行)

#查询总分大于200的所有同学
SELECT * FROM student
	WHERE (chinese + english + math) > 200;
	
#查询math大于60，并且id大于4的学生成绩
SELECT * FROM student
	WHERE math>60 AND id>4;
	
#查询英语分数在 80-90 之间的同学
SELECT * FROM student
	WHERE english BETWEEN 80 AND 90;  #注意都是闭区间
	
#查询数学分数为89，90，91的同学
SELECT * FROM student
	WHERE math IN (89,90,91);

注：where不能和计量函数一起使用，如果一起使用的话要用 having（having和where可以替换）

order by语句 

#对数学成绩排序后输出(升序) 啥也不用写，默认的
SELECT * FROM student
	ORDER BY math;
#对总分按从高到低的顺序输出 (降序)  
SELECT `name`,(chinese + math + english) AS total_score FROM student
	ORDER BY total_score DESC;
	
#对姓赵的学生成绩(总分)排序输出
SELECT `name`,(chinese + english + math) AS total_score FROM student
	WHERE `name` LIKE '赵%'
	ORDER BY total_score;
	
#当然也可以这样写，但是这样写不显示总分
SELECT * FROM student
	WHERE `name` LIKE '赵%'
	ORDER BY (chinese + english + math);

select增强

#使用where语句 ：查找 1992.1.1后入职的员工

SELECT * FROM emp
	WHERE hiredate>'1992-01-01';

#使用 like操作符
#	%: 表示0到多个任意字符
#	_: 表示单个任意字符

#显示首字母为S的员工姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE 'S%';
	
#显示第三个字符为大写H的员工的姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE '__H%'; #前面两个_ 代表两个任意字符

#显示没有上级的雇员
SELECT * FROM emp
	WHERE mgr IS NULL;
	

#使用order by 子句
#按照工资从低到高的顺序，显示雇员信息
SELECT * FROM emp
	ORDER BY sal;
	
#按照部门号升序，但雇员的工资降序排列，显示雇员信息
SELECT * FROM emp
	ORDER BY deptno ASC,sal DESC;

计量函数

COUNT

#统计一个班有多少学生有多少个
SELECT COUNT(id) FROM student

#统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
	WHERE math>90

注：count(*) 返回满足条件(where)的记录的行数

       count(列) 会排除为列为null的情况

SUM

#统计一个班数学总成绩
SELECT SUM(math) FROM student;

#统计一个班级语文，数学，英语各科的总成绩
SELECT SUM(chinese),SUM(math),SUM(english) FROM student;

#统计一个班语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student;

AVG

#统计一个班数据平均分
SELECT AVG(math) FROM student;

MAX/MIN 

#求班级数学最高分和最低分
SELECT MAX(math),MIN(math)
	FROM student

分组与过滤

#deptno为部门号
#显示每个部门的平均工资和最高工资
SELECT AVG(sal),MAX(sal), deptno
	FROM emp GROUP BY deptno;

#显示每个部门的每种岗位的平均工资和最低工资 (多种分组方式)
SELECT AVG(sal),MAX(sal),deptno,job
	FROM emp 
    GROUP BY deptno,job;

#显示平均工资低于2000的部门号和它的平均工资 (过滤)
#分析：1. 显示各个部门的平均工资和部门号
#      2. 在1结果的基础上进行过滤

SELECT AVG(sal),deptno
	FROM emp GROUP BY depto
		HAVING AVG(sal) < 2000; #用having过滤
#也可以使用别名		
SELECT AVG(sal) AS sal_avg,deptno
	FROM emp GROUP BY depto
		HAVING sal_avg < 2000;

group by增强 

# 1. 显示每种岗位的雇员总数，平均工资
SELECT COUNT(*),AVG(sal),job
	FROM emp
	GROUP BY job;
	
# 2.显示没有获得补助的雇员数
SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))
	FROM emp;
	
#获得补助的员工 comm不是null，而没有获得补助的员工为null，因此用if语句
#如果comm是null就返回1

#第二种写法
SELECT COUNT(*),COUNT(*)-COUNT(comm)
	FROM emp;
	
#显示管理者的总人数(去重)
SELECT COUNT(DISTINCT mgr)
	FROM emp;

#显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal)
	FROM emp;

字符串相关函数

# 返回字符集
SELECT CHARSET(ename) FROM emp;

#连接字串，将多个列拼接成一列 注意是逗号
SELECT CONCAT(ename , ' 工作是 ' , job) FROM emp;

#返回substring在string中出现的位置，没有返回0
#dual为亚元表，系统表，可以作为测试表使用
SELECT INSTR('zhaoyun','yun') FROM DUAL;

#转换大小写
SELECT UCASE(ename) FROM emp;
SELECT LCASE(ename) FROM emp;

#从string左边起取length个字符
SELECT LEFT(ename,2) FROM emp;
#从string右边起取length个字符
SELECT RIGHT(ename,2) FROM emp;

SELECT LENGTH(ename) FROM emp;  #长度(字节长度)
SELECT STRCMP('hhh','hht') FROM DUAL; #比较大小

SELECT SUBSTRING(ename,1,2) FROM emp; #从1开始取2个字符 2如果不写就取到最后

SELECT LTRIM(' sss'); #去除左边空格
SELECT RTRIM('sss '); #去除右边空格
SELECT TRIM(' sss '); #去除空格

例：用首字母大写的方式显示所有员工emp表的姓名

SELECT CONCAT(UCASE(LEFT(ename,1)),SUBSTRING(ename,2)) AS new_name
	FROM emp;

数学函数

日期函数 

#求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;

#假设一个人1986-11-11出生，如果能活到80岁，求出还能活多长时间
SELECT DATEDIFF(DATE_ADD('1986-11-11',INTERVAL 80 YEAR),NOW())
	FROM DUAL;
	
#查询十分钟内发布的新闻
SELECT * FROM mes
	WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();
	
#查询当前年数
SELECT YEAR(NOW()) FROM DUAL;

#unix_timestamp() :返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;

#FROM_UNIXTIME() 可以把一个 unix_timestamp 秒数转换成指定格式的日期
SELECT FROM_UNIXTIME(1618483485,'%Y-%m-%d %H:%i:%s') FROM DUAL;

加密函数

#root密码是 'shang' -> 加密md5 ->数据库存放的是加密后的密码
SELECT MD5('shang') FROM DUAL;

SELECT USER() FROM DUAL;

SELECT DATABASE();

流程控制函数 

#判断是否为NULL,要使用 is null

#查询emp表，如果comm是null则输出0.0，否则输出comm
SELECT ename,IF(comm IF NULL,0.0,comm)
	FROM emp;
	
SELECT ename,IFNULL(comm,0.0)
	FROM emp;
	
#如果emp表的job是CLERK,则显示 职员，如果是 MANAGER则显示 经理
#如果是 salesman 则显示 销售人员

SELECT ename,(SELECT CASE
		WHEN job = 'CLERK' THEN '职员'
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员'
		ELSE job END) AS 'job'
	FROM emp;

分页查询

#每页显示三行记录
#第一页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,3;
	
#第二页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3,3;

查询加强