启动和关闭mysql服务:
# 启动
net start mysql
# 关闭
net stop mysql
连接数据库
mysql -uroot -p密码
SQL
SQL:Structure Query Language 结构化查询语言,用于操作关系型数据库。
-
SQL的分类:
DDL:Data Defintion Language 数据定义语言,用于定义数据库。例如:创建数据库,删除数据库,创建表、修改表、删除表等等。
DML:Data Manipulation Language 数据操作语言,用于操作数据库表的数据。例如:添加数据、修改数据、删除数据等等。
DQL:Data Query Language 数据查询语言,用于查询。例如:单表查询、多表查询、分组查询、统计查询等等。
DCL:Data Control Language 数据控制语言,主要用于对数据库进行控制。例如:创建用户、修改密码、分配权限等等。
DDL命令
创建数据库,删除数据库,创建表、修改表、删除表
数据库的DDL
-- 创建数据库
create database 数据库名;
-- 查看所有的数据库
show databases;
-- 删除数据库
drop database 数据库名;
-- 选定数据库
use 数据库名;
表的DDL操作
-- 创建表
create table 表名(
列名 数据类型,
列名 数据类型,
列名 数据类型,
...
);
-- 查看表的结构
desc 表名;
-- 查看当前数据库的所有表
show tables;
-- 修改表
-- 添加一列
alter table 表名 add 列名 数据类型;
-- 修改某一列的数据类型
alter table 表名 modify 列名 数据类型;
-- 修改列名
alter table 表名 change column 旧列名 新列名 数据类型;
-- 删除列
alter table 表名 drop 列名;
-- 删除表
drop table 表名;
DML命令
用于操作数据库表的数据。例如:添加数据、修改数据、删除数据
-- 添加数据
insert into 表名(列, 列, ...)
values(值, 值, ...);
-- 修改数据
update 表名 set 列 = 值, 列 = 值, ... [where 条件];
-- 删除数据
delete from 表名 [where 条件];
delete 和 truncate命令的区别?
1)delete属于DML命令,truncate属于DDL命令;
2)delete先查询再删除数据,truncate是先删表,再重新创建表;
3)delete命令效率比truncate的效率要慢;
DQL命令
用于查询数据。例如:单表查询、多表查询、分组查询、统计查询、子查询、分页查询等等
基本查询
-- 查看所有列
select * from 数据库.表名 [where 条件];
-- 查看某些列
select 列[ as 别名], 列[ as 别名], ... from 数据库.表名 [where 条件];
多表查询
select * from emp, dept where emp.deptno = dept.deptno;
统计查询
统计查询也叫投影查询或聚合查询,统计查询就是对某一列的查询。
统计查询需要用到的函数:
sum():对某列进行求和;
avg():对某列求平均值;
count():求出某列数据的数量;
max():求出某列最大值;
min():求出某列的最小值;
-- 统计工资的总和、平均工资、最高工资、最低工资
SELECT SUM(sal), AVG(sal), MAX(sal), MIN(sal) FROM emp;
-- 统计员工的人数
SELECT COUNT(*) FROM emp;
SELECT COUNT(empno) FROM emp;
模糊查询
使用like关键字。一般还会使用一些通配符:
%
匹配0个或多个字符
_
匹配一个字符
-- 查询姓名以“李”开头所有学生
SELECT * FROM students WHERE NAME LIKE '李%';
-- 查询姓名以“李”开头所有学生,但是姓名只有两个字的学生
SELECT * FROM students WHERE NAME LIKE '李_';
-- 查询姓名包含“李”字的学生
SELECT * FROM students WHERE NAME LIKE '%李%';
结果查询
select 语句 [order by 字段 [asc|desc], 字段 [asc|desc], ...]
-- 按照学生的总分的降序排列
-- ifnull(expr1, expr2):如果expr1为null,就返回expr2.否则返回expr1.
SELECT NAME, (IFNULL(chinese, 0) + IFNULL(english, 0) + IFNULL(math, 0)) AS 总分 FROM students ORDER BY 总分 DESC;
分组查询
select 分组信息 from 表名 group by 分组字段 [having 分组条件];
分组信息:
1)分组字段;2)聚合(统计)函数;
-- 分组统计各个职位的平均工资是多少
SELECT job, AVG(sal) FROM emp GROUP BY job;
-- 分组统计各个职位的平均工资,而且平均工资必须大于20000
SELECT job, AVG(sal) AS avgSal FROM emp GROUP BY job HAVING avgSal > 20000;
-- 分组统计工资大于20000的各个职位的平均工资
SELECT job, AVG(sal) AS avgSal FROM emp WHERE sal > 20000 GROUP BY job;
having和where的区别?
having是分组后的条件,where是分组前的条件。
子查询
子查询:在一个查询中包含另外一个查询。
子查询可以在from后面使用,也可以在select后面或where后面使用。
如果在from后面使用子查询,那么子查询的结果作为一个临时表使用。
如果在select后面使用子查询,那么子查询的结果作为一个临时列。
如果在where后面使用子查询,那么子查询的结果作为where条件的一个值。
-- 子查询放在where后面作为一个条件的值
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc = '北京');
-- 子查询放在from后面作为临时表
SELECT * FROM emp, (SELECT * FROM dept WHERE loc = '北京') AS temp WHERE emp.deptno = temp.deptno;
-- 子查询放在select后面作为一个临时列
-- 例如:查询所有员工以及员工所在部门的名称
SELECT emp.*, dept.dname
FROM emp, dept WHERE emp.deptno = dept.deptno;
SELECT *, (SELECT dname FROM dept WHERE deptno = emp.deptno) AS 部门名称 FROM emp;
分页查询
select语句 limit start, size;
/* start:开始查询的位置,从0开始(从第几条记录开始查询);
下一页的start = 上一页的start + size
size:每页查询的记录数;*/
注意:limit关键是mysql的方言,只能够在mysql中使用。
约束
非空约束
-- 限制某一列不能出现null值
name varchar(255) not null default ''
唯一约束
id int unique;
主键约束
主键可以用来唯一标识表的某一行记录。一般使用一些没有意义的列作为主键列。
主键列的特点:1)唯一的;2)非空的;3)被引用;
主键一般都会设置为自增长。自动从最后一条记录的主键值自动加1。不需要我们手动指定主键的值。
mysql 设置自增长在主键列后面使用auto_increment关键字。
id int primary key auto_increment,
注意:非空加唯一不等于主键。
约束例子:
create table user (
int id PRIMARY KEY AUTO_INCREMENT; -- 主键,自增长
name varchar(255) not null unique default '', --非空,唯一,默认为空
gender char(1) not null default '男' --非空,默认为男
);
多列主键(联合主键):可以对多列同时设置为主键。
alter table 表名
add primary key(列1, 列2, ...);
例如:
CREATE TABLE student_course (
stu_id INT,
course_id INT
);
ALTER TABLE student_course
ADD PRIMARY KEY(stu_id, course_id);
外键约束
外键约束主要用于设置两个表的关联关系,限制外键列的值必须要来自于关联表的主键。
方式一:在创建表的时候定义外键。
create table 表名(
列的定义...
foreign key(外键列) references 关联表(主键列)
);
方式二:通过alter table语句添加外键。
alter table 表名
add constraint 外键名 foreign key(外键列) references 关联表(主键列);
例如:
-- 给emp表的deptno列添加外键,引用dept表的deptno
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno);
添加外键约束的表称为从表,被引用的表称为主表。
添加数据的时候,如果从表中外键列所关联的记录不存在,那么就需要先再主表中添加相应的记录。
删除数据的数据,如果删除主表记录的时候,从表中有关联该记录的数据,那么就需要先把从表中相应的数据删除后,才能够删除主表的记录。
可以添加外键约束的时候设置级联:
可以在添加外键约束的时候指定on update|delete cascade。
删除外键
-- 删除外键
ALTER TABLE emp
DROP FOREIGN KEY fk_emp_dept;
-- 给emp表的deptno列添加外键,引用dept表的deptno
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;
多表查询
等值查询(自然连接):使用等号作为连接条件。
左外连接:左边的表作为主表,右边的表作为从表。无论条件是否满足,主表的数据一定会被查询出来。
select ... from 表1 left outer join 表2 on 连接条件;
右外连接:右边的表作为主表,左边的表作为从表。无论条件是否满足,主表的数据一定会被查询出来。
select ... from 表1 right outer join 表2 on 连接条件;
数据类型
tinyint/smallint/mediumint/int/bigint:代表整数类型;
float/double:代表小数类型;
char:固定长度的字符类型;
varchar:变长的字符类型;
tinytext/text/mediumtext/longtext:代表文本类型;
date:日期类型
time:时间类型
datetime:日期时间类型
blob:二进制数据类型
mysql注释:
# 注释内容
-- 注释内容
/*
多行注释
*/
条件表达式
比较运算符:<小于 >大于 >=大于等于 <=小于等于 =等于
范围运算符:in、between...and...
逻辑运算符:or and not
注意:删除数据一般都要加上where条件,否则清空表数据。
delete 和 truncate命令的区别?
1)delete属于DML命令,truncate属于DDL命令;
2)delete先查询再删除数据,truncate是先删表,再重新创建表;
3)delete命令效率比truncate的效率要慢;
笛卡尔积现象:
所谓笛卡尔积,就是一个表的每一条数据与另外一个表的每一条数据进行连接,产生许多新的数据,这种现象称为笛卡尔积现象。笛卡尔积现象是一个不正常的现象,它会产生许多垃圾数据。
为什么会出现笛卡尔积现象?
在执行多表查询的时候,如果没有指定连接条件的时候,就会出现笛卡尔积现象。
解决笛卡尔积:
在执行多表查询的时候,必须要指定连接条件。
怎么指定连接条件?
使用它们的公共的字段作为连接条件。
记住:执行多表查询必须要添加连接条件,否则就会出现笛卡尔积现象。
select语句的执行顺序:
from > where > select > having > order by
1)先查询表的记录;2)根据where条件筛选记录;3)筛选列;4)对结果排序;
数据库设计的三大范式
第一范式:一个表每一个字段都是不可分割的单元。所有范式中最低的要求。
第二范式:在满足第一范式的基础上,保证一个表每个字段都与该表有联系。
第三范式:在满足第三范式要求的基础上,保证一个表的每个字段必须要与该表有直接关系,不能是间接关系。
数据库设计最好能够满足第三范式的要求。