常用数据类型:
int: 整型
double: 浮点型, 例如 double(5,2) 表示最多 5 位, 其中必须有 2 位小数, 即最大值为 999.99;
char: 固定长度字符串类型; char(10) 'aaa' 占 10 位
varchar: 可变长度字符串类型; varchar(10) 'aaa' 占 3 位
text: 字符串类型;
blob: 字节类型;
date: 日期类型, 格式为: yyyy-MM-dd;
time: 时间类型, 格式为: hh:mm:ss
timestamp: 时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime: 日期时间类型 yyyy-MM-dd hh:mm:ss
常用约束:
主键约束: primary key
唯一约束: unique [key]
非空约束: not null
默认约束: default
外键约束: foreign key
自动增长: auto_increment
DROP TABLE 语句用于删除现有表
DDL 使用:
1. 查看数据库的个数和某个数据库, 以及查看当前使用的数据库
- show databases;
- show database dbname;
- select database;
2. 使用指定或者切换数据库
use dbname;
3. 删除数据库
drop database dbname;
4. 创建表和删除表
- create table employee
- (
- id int not null,
- age int not null,
- name String varchar(255),
- primary key(id)
- );
- drop table table_name;
5. 查看当前使用的数据库的所有表 和 查看表的字段内容
- show tables;
- decs table_name;
数据库表操作:
1. 增加表列
alter table employee add reward int(20);
2. 修改表
alter table employee modify 列名 列属性;// 修改表列的长度
- alter table employee drop reward;// 删除表列
- alter table employee character set gbk;// 修改表的字符集为 gbk
- alter table employee change name username varchar(100);
3. 修改表名
rename table employee to user;
4. 查看表格的创建细节
show create table user;
DML 操作:
DML 是对表中的数据进行增, 删, 改, 查的操作, 不要与 DDL 混淆.
主要包括: insert update delete
?
小知识:
在 mysql 中, 字符串类型和日期类型都要用单引号括起来.
空值: null
增加操作 INSERT:
1. 根据表数据插入数据
- INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
- VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','goodgirl');
小知识:
查看数据库编码的具体信息
Show variables like 'character%';
临时更改客户端和服务器结果集的编码
- Set character_set_client=gbk;
- Set character_set_results=gbk;
或者
SET NAMES 'gbk';
修改操作 UPDATE:
1. 修改所有员工的薪水
update emp set salary = 5000 ;
2. 修改姓名为'xxx'的员工的 薪水为 3000;
update emp set salary = 3000 where name = 'xxx';
3. 修改姓名为'xxx' 的员工薪水为 4000,gender 改为'ccc'
updatte emp set salary = 4000,gender = 'ccc' where name = 'xxx';
4. 将 wu 的薪水在原有基础上增加 10000 元
update emp set salary =salary+1000 where gender = 'male';
删除操作 : DELETE :
1. 删除表中的名称为'zs' 的记录
delete from emp where name='zs';
2. 删除表中所有的记录
delete from emp;
3. 使用 truncate 删除表中的记录
TRUNCATE TABLE emp;
1.delete 删除表中的数据, 表结构还在, 删除后的数据还可以找回
2.truncate 删除是把表直接 drop 掉, 然后再创建一个同样的新表
3. 删除的数据不能找回, 执行速度比 delete 块
DQL 数据查询:
数据库执行 DQL 语句不会对数据进行改变, 而是让数据库发送结果集给客户端.
查询返回的结果集是一张虚拟表
SELECT:
语法: SELECT 列名 FROM 表名 (where-->broup by -->having --> order by)
SELECT selection_list 要查询的列名
FROM table_list 要查询的表名称
WHERE condition 行条件
GROUP By grouping_columns 对结果分组
HAVING condition 分组后的行条件
ORDER BY sorting_columns 对结果分组
LIMIT offset_start,row_count 结果限定
简单查询:
1. 查询所有列
select * from stu;
2. 查询指定列
select sid,sname,age from stu;
条件查询:
条件查询就是在 查询时给出 where 子句, 在 where 子句中可以使用如下运损及关键字:
- =,!=,<>,<,<=,>,>=;
- BETWEEN...AND;
- IN(set);
- IS NULL;
- AND;
- OR;
- NOT;
1. 查询学号为 S_1001,S_1002,S_1003 的记录 或者不为 not in
- SELECT * FROM stu
- WHERE sid IN ('S_1001','S_1002','S_1003');
2. 查询年龄为 null 的记录
select * from stu where age is NULL;
3. 查询年龄在 20 到 40 的学生记录
- WHERE age>=20 AND age<=40;
- WHERE age BETWEEN 20 AND 40;
4. 查询性别非男的学生记录
- WHERE gender!='male';
- WHERE gender<>'male';
- WHERE NOT gender='male';
5. 查询姓名不为 null 的学生记录
- WHERE NOT sname IS NULL;
- WHERE sname IS NOT NULL;
模糊查询:
关键字: LIKE
通配符:
_任意一个字母
% 任意 0-n 个字母
'张 %'
?
句式
select * from stu where 姓名等 like '-';
包含 a '%a%'
第二个字母为 i '_i%'
以 z 开头'z%'
字段控制查询:
1. 去重复:
select distinct sal from emp;
2. 查看员工的月薪和佣金之和
- select *,sal+comm from emp;
- select *,sal+IFNULL(comm,0) from emp;//comm 列有很多记录的值为 NULL, 因为任何东西与 NULL 相加结果还是 NULL, 所以结算结果可能会出现 NULL. 使用了把 NULL 转换成数值 0 的函数 IFNULL
3. 给查询的列名添加别名 as 可以省略
select *,sal+IFNULL(comm,0) as 'total' from emp;
排序:
1. 所有学生的年龄升序
- select * from stu order by sage ASC;
- select * from stu order by sage;
2. 所有学生的年龄降序
select * from stu order by sage DEsc;
3. 查询所有员工, 按月薪降序, 月薪相同, 按编号升序
select * from stu order by sal DESC,empno ASC;
聚合
聚合函数:
聚合函数是用来做纵向运算的函数:
l COUNT(): 统计指定列不为 NULL 的记录行数;
2 MAX(): 计算指定列的最大值, 如果指定列是字符串类型, 那么使用字符串排序运算;
3 MIN(): 计算指定列的最小值, 如果指定列是字符串类型, 那么使用字符串排序运算;
4 SUM(): 计算指定列的数值和, 如果指定列类型不是数值类型, 那么计算结果为 0;
5 AVG(): 计算指定列的平均值, 如果指定列类型不是数值类型, 那么计算结果为 0;
l COUNT(): 统计指定列不为 NULL 的记录行数;
?
统计月薪与佣金之和大于 2500 元的人数:
select count(*) as cnt from stu where sal+IFNULL(comm,0)>2500;
查询有佣金的人数, 以及有领导的人数:
select count(comm),count(mgr) from emp;
2 MAX(): 计算指定列的最大值, 如果指定列是字符串类型, 那么使用字符串排序运算;
3 MIN(): 计算指定列的最小值, 如果指定列是字符串类型, 那么使用字符串排序运算;
?
查询最高工资和最低工资
select max(sal),min(sal) from emp;
4 SUM(): 计算指定列的数值和, 如果指定列类型不是数值类型, 那么计算结果为 0;
5 AVG(): 计算指定列的平均值, 如果指定列类型不是数值类型, 那么计算结果为 0;
?
查看所有员工月薪和, 以及所有雇员佣金和
select sum(sal),sum(comm) from emp;
查询所有员工月薪加佣金和:
select sum(sal+ifnull(comm,0)) from emp;
统计所有员工平均工资:
SELECT AVG(sal) FROM emp;
分组查询:
按部门查询工资和, 按男女查询工资和
?
注意:
凡和聚合函数 coun sum avg max min 同时出现的列名, 则一定要写在 group by 之后
查询每个部门编号和每个部门工资和
- select deptno,sum(sal)
- from emp
- group by deptno;
查询每个部门的部门编号以及每个部门的人数
- select deptno,count(*)
- from emp
- group by deptno;
查询每个部门的部门编号以及每个部门工资大于 1500 的人数
- select deptno,count(*)
- from emp
- where sal>1500
- group by deptno;
HAVING 子句
查询工资总和大于 9000 的部门编号以及工资和:
- SELECT deptno, SUM(sal)
- FROM emp
- GROUP BY deptno
- HAVING SUM(sal)> 9000;
- ?
注: having 与 where 的区别:
1.having 是在分组后对数据进行过滤, where 是在分组前对数据进行过滤
2.having 后面可以使用分组函数 (统计函数)
where 后面不可以使用分组函数.
WHERE 是对分组前记录的条件, 如果某行记录没有满足 WHERE 子句的条件, 那么这行记录不会参加分组; 而
HAVING 是对分组后数据的约束.
LIMIT:
查询 5 行记录, 起始从 0 行开始
select * from emp limit 0,5;
查询 10 行记录, 起始从 3 开始
- select * from emp limit 3,19;
- ?
分页查询:
如果一页记录为 10 条, 希望查看第 3 页记录应该怎么查呢?
l 第一页记录起始行为 0, 一共查询 10 行;
2 第二页记录起始行为 10, 一共查询 10 行;
3 第三页记录起始行为 20, 一共查询 10 行;
查询语句书写顺序: select - from- where- groupby- having- order by-limit
查询语句执行顺序: from - where -group by -having - select - order by-limit
实体完整性:
主键约束 (primary key)
唯一约束 (unique)
自动增长列 (auto_increment)
主键的添加方式:
创建表时 id int primary key
创建表时 primary key(id) 优势, 创建联合主键 (两个或两个以上同时进行约束)
建表之后 ALTER TABLE student ADD PRIMARY KEY (id);
唯一约束 (unique)
数据不能重复: CREATE TABLE student(
- Id int primary key,
- Name varchar(50) unique
- );
自动增长列 (auto_increment)
给主键添加自动增长, 列只能是整数类型
- CREATE TABLE student(
- Id int primary key auto_increment,
- Name varchar(50)
- );
- INSERT INTO student(name) values('tom');
域完整性:
域完整性约束: 数据类型 非空约束 (not null) 默认值约束 (default)
- ?
- Name varchar(50) not null,
- Sex varchar(10) default '男'
- ?
引用完整性:
外键约束: FORRIGN KEY
?
添加方式一, 建表时, 一个表的主键是另一表的成员
添加方式二, ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);
多表查询:
1, 合并结果集
union(去除重复) union all
2, 连接查询
内连接 inner join on
外连接 outer join on
左外连接 left join 以左边的表为基准
左内连接 right join 以右边的表为基准
全外连接 (mysql 不支持) full join
自然连接 natural join
连接查询心得:
连接不限与两张表, 连接查询也可以是三张, 四张, 甚至 N 张表的连接查询. 通常连接查询不可能需要整个笛卡尔
积, 而只是需要其中一部分, 那么这时就需要使用条件来去除不需要的记录. 这个条件大多数情况下都是使用主外
键关系去除.
两张表的连接查询一定有一个主外键关系, 三张表的连接查询就一定有两个主外键关系, 所以在大家不是很熟悉连
接查询时, 首先要学会去除无用笛卡尔积, 那么就是用主外键关系作为条件来处理. 如果两张表的查询, 那么至少
有一个主外键条件, 三张表连接至少有两个主外键条件.
子查询:
l 子查询出现的位置:
a. where 后, 作为条为被查询的一条件的一部分;
b. from 后, 作表;
l 当子查询出现在 where 后作为条件时, 还可以使用如下关键字:
a. any
b. all
课堂笔记:
1,mysql int(10)
int 类型长度 4 个字节, 大约表示 2^32 数字, 10 代表的是显示长度, 一般和 FILLZERO 约束一起使用, 如果没有达到该长度, 填充 0 2-->0000000002
mysql varchar 最大长度可以设置到 65535, 但是一般设置为 255
mysql double,float (5,2) 代表总长度是 5, 小数点是占两个长度
2, 数据导入导出
sql 格式, xls 格式
将查询出来的数据制作成表格:
- select name,salary from worker into outfile 'C:/abc.xls';
- ?
3, 如何配置你的 mysql 服务器允许远程连接.
方法一: 该表法
找到 MySQL 数据库, 找到 user 表, 找到 User=root,Host=localhsot 的那条记录
修改 Host 为 %, 重启 MySQL 服务
方法二: 通过 SQL 语句
- grant all privileges on *.* to root@'%' identified by "root"; // 赋权限
- flush privileges; // 刷新权限
- ?
4, 临时表中间表 dual
select 7*8 ; == select 7*8 from dual;
作用只是为了语法合理, Oracle 中该表不能省略.
?
5, 数据库设计三大范式
1) 第一范式 表中的每个字段都是独立的, 不可分割的单元
- user
- id name
1 贾明灿 | 贾宝
修改为 id name ex_name[曾用名]
2) 第二范式 在满足第一范式的前体现
一张表只能表示一个意思, 每一个字段都要和主键有依赖关系
employee
id name age sex salary { deptName deptNo } 违反了第二范式
id name age sex salary
dept 部门表
id deptNo deptName
技术部 现在叫 研发部
3) 第三范式 在满足第二范式的前体现
一张表只能表示一个意思, 除主键外每个字段都只能和主键有依赖关系
employee
id name age sex salary { deptName deptNo } 违反了第二范式
id name age sex salary deptId deptName 第三范式 设计和第二范式可以联合理解
dept 部门表
id deptNo deptName
技术部 现在叫 研发部
数据库设计的三大范式: 只是设计的规范, 参考
?
6, 联合查询
UNION : 合并结果集, 去除重复的数据
UNION ALL : 不去重
前面查询出来的列数和后面的结果集查询出来的列数要相等, 建议类型也相同
?
来源: http://www.bubuko.com/infodetail-2756588.html