1,创建数据库
创建数据库语法结构
CREATA DATABASE db_name;
语法解析:
CREATA DATABASE 表示创建数据库,是SQL的关键字。
db_name是要创建的数据库名。
示例:
#创建公司信息数据库
CREATE DATABASE company_info;
代码解析:
- 创建了名称为company_info的数据库
- #是MySQL数据库中的注释
- 在sqlyog中,打开询问出窗口,输入代码,选中执行的sql语句,选择执行就可。
2、使用数据库
使用数据库语法结构
use db_name;
语法解析
- 使用use关键词,来指定我们要使用的数据库。
- db_name是被使用 的数据库名称
示例
#使用company_info数据库
USE company_info;
代码解析
- 使用USE命令指定要使用的数据库
- campany_info是被使用的数据库名称
3、删除数据库
删除数据库的语法结构
DROP DATABASE db_name;
语法解析
- 使用DROP关键词删除数据库
- db_name是被删除的数据库名称。
示例
DROP DATABASE company_info;
代码解析
- 使用DROP DATABASE命令删除数据库
- company_info是被删除的数据库名称
4、创建数据库
创建数据表的语法结构
CREATE TABLE tab_name(
col_name1 datetype,
col_name2 datetype,
......
);
语法解析
- 使用CREATE TABLE关键词创建数据表
- tab_name是数据表的名称
- col_name是列名称
- datetype是列的数据类型
- 创建每一列以逗号结尾,但最后一列不允许写逗号
- 常见的数据类型有:SMALLINT、MEDIUMINT、INT/INTEGER、BIGINT、FLOAT、DOUBLE、DATE、DATETIME、TIMESTAMP、TIME、YEAR、CHAR(M)、VARCHAR(M)、BLOB[(M)]、TEXT[(M)]等
示例:
#创建部门表
CREATE TABLE dept(
deptno int comment'部门编号',
dname VARCHAR(14) COMMENT'部门名称',
loc VARCHAR(13) COMMENT'部门地址'
);
#创建员工表
CREATE TABLE emp(
empNO INT COMMENT'员工编号',
ename VARCHAR(10) COMMENT'员工姓名',
job VARCHAR(10) COMMENT'职位',
mgr INT COMMENT'领导编号',
hirdate DATETIME COMMENT'入职时间',
sal DOUBLE COMMENT'工资',
comm DOUBLE COMMENT'奖金',
deptno INT COMMENT'所属编号'
);
代码解析:
- 创建了部门表dept
- 创建了员工表emp
5、删除数据表
删除数据表的语法结构
DROP TABLE TABLE_name;
语法解析
- 使用DROP关键词删除数据库
- table_name是被删除的表的名称
示例
DROP TABLE dept;
代码解析
- 使用DROP TABLE命令删除表
- dept是被删除表的名称
CRUD操作
6、添加数据
添加数据语法结构
INSET INTO tab_name(col1,col2...)values(value1,value2...);
语法解析:
- INSERT INTO关键字为表中添加数据
- tab_name是表名称
- col1,col2,col3是表中的列名称
- value1,value2,value3是列中的值
- value的数量、顺序、类型必须与col完全一致
- 如果为所有列添加数据可以省略(col1, col2, col3, …)部分
示例:
#给dept表中添加数据
INSERT INTO dept VALUES(10, '人事部', '北京');
INSERT INTO dept(deptno,dname, loc) VALUES(20,'软件部', '深圳');
INSERT INTO dept VALUES(30, '销售部', '杭州');
#给emp表中添加数据
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(1001, '宋江', '董事长', null ,'2001-1-1', 6000, 10000, 10);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(10011, '卢俊义', '董事助理', 1001,'2003-1-1', 3000, 1000, 10);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(10012, '吴用', '董事助理', 1001,'2001-1-1', 4000, 4000, 10);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(2001, '林冲', '项目经理', 1001,'2003-3-1', 6000, 5000, 20);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(20011, '李逵', '项目组长', 2001,'2001-1-1', 3000, 1000, 20);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(20012, '扈三娘', '项目助理', 2001,'2003-1-1', 5000, 1000, 20);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(200111, '时迁', '程序员', 20011,'2004-1-1', 2000, 2000, 20);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(3001, '母夜叉', '销售经理', 1001,'205-1-1', 2000, 10000, 30);
INSERT INTO emp(empNo, ename, job, mgr, hirdate, sal, comm, deptno)
VALUES(30011, '张青', '销售助理', 3001,'2005-3-1', 2000, 2000, 30);
7、查询数据
查询数据语法结构
SELECT col1,col2,...FROM tab_name;
语法解析
- SELECT FROM关键字用于查询表中数据
- col1,col2是表中的列名称,如果要查询所有列可以使用*
- tab_name是表名称
示例
select * from dept;
运行结果
8、修改数据
修改数据语法结构
UPDATE tab_name SET col_name='被修改的新数据' WHERE col_name='旧数据';
语法解析
- UPDATE SET关键字用于修改表中的数据
- tab_name是表名称
- col1,col2是表中的列名称
- value1,value2是列中的值
- WHERE是修改数据的条件
9、删除数据
删除数据语法结构
DELETE FROM tab_name WHERE condition;
语法解析
- DELETE FROM关键字用于删除表中的数据
- tab_name是表名称
- WHERE是修改数据的条件
示例
DELETE FROM emp WHERE ename='李逵';
代码解析
使用DELETE 语句删除员工表emp中ename为李逵的记录
10、数据备份
备份语法
mysqldump -u root -proot db_name >c:/back.sql
语法解析
使用mysqldump命令备份数据库
-u是指登录数据库的用户名
-p是指登录数据库的密码
db_name是备份的数据库的名称
使用输出目标操作符>,指定输出文件的具体路径c:/back.sql
示例
mysqldump -uroot -proot compan_info >c:/back.sql
11、还原数据库
还原语法结构
mysql -u root -proot db_name <c:/back.sql
语法解析
使用mysql命令还原数据库
-u是指登录数据库的用户名
-p是指登录数据库的密码
db_name是还原的数据库的名称
使用目标操作符<,指定输出文件的具体路径c:/back.sql
示例
MySQL -uroot -proot company_info<c:/back.sql
代码解析
- 使用mysql命令还原数据
- 还原数据之前应该先清空数据库中的原数据
知识点总结:
SQL语句的五种分类
SQL语句的五种分类分别是DQL、DML、DDL、TCL和TCL,
各个包括的语句
- 数据定义语言(DDL),包括CREATE(创建)命令、ALTER(修改)命令、DROP(删除)命令等。
- 数据操纵语言(DML),包括INSERT(插入)命令、UPDATE(更新)命令、DELETE(删除)命令、SELECT … FOR UPDATE(查询)等。
- 数据查询语言(DQL),包括基本查询语句、Order By子句、Group By子句等。
- 事务控制语言(TCL),包括COMMIT(提交)命令、SAVEPOINT(保存点)命令、ROLLBACK(回滚)命令。
- 数据控制语言(DCL),GRANT(授权)命令、REVOKE(撤销)命令。
约束和高级查询
1,约束
1.1主键约束
主键约束的语法结构
ALTER TABLE tab_name add CONSTRAINT pk_name PRIMARY KEY(col_name);
示例
给dept表中的部门编号设置为主键
ALTER TABLE dept add CONSTRAINT pk_dept_deptno PRIMARY KEY(deptno);
主键约束的规则
- 主键约束的列不允许有重复的值
- 主键约束的列不允许有控制
1.2自增长列
CREATE TABLE emp (
empNo INT PRIMARY key AUTO_INCREMENT,
ename VARCHAR (10),
job VARCHAR (10),
mgr INT,
hirdate DATETIME,
sal DOUBLE,
comm DOUBLE,
deptno INT
);
代码解析:
- PRIMARY KEY 将empno列设置为主键列
- AUTO_INCREMENT将empno列设置为自增长列
- AUTO_INCREMENT 列必须是主键列
1.3唯一约束
唯一约束语法结构
ALTER TABLE dept add CONSTRAINT UNIQUE(col_name);
示例
将部门名称设置为唯一约束
ALTER TABLE emp ADD CONSTRAINT UNIQUE(ename);
在创建表的时候也可以设置唯一约束
CREATE TABLE dept(
deptno INT COMMENT'部门编号',
dname VARCHAR(14) unique COMMENT'部门名称',
loc VARCHAR(13) COMMENT'部门地址'
);
1.4默认约束
默认约束的语法条件
ALTER TABLE tab_name CHANGE COLUMN old_col_name new_col_name datetype default value;
示例:
ALTER TABLE emp CHANGE COLUMN comm comm DOUBLE DEFAULT 500;
1.5外键约束
外键约束的语法规则
ALTER TABLE tab_name add CONSTRAINT FOREIGN key fk_name(col_name) REFERENCES re_name(re_col_name);
示例
ALTER TABLE emp add CONSTRAINT FOREIGN key fk_dept_emp_deptno(deptno) REFERENCES dept(deptno);
在创建表的时候添加外键约束
FOREIGN KEY (order_id) REFERENCES t_orders(id)
或者
CONSTRAINT fk_t_orders_id FOREIGN KEY (order_id) REFERENCES t_orders (id)
2、高级查询
2.1基本查询
查询所有列
select * from tab_name;
示例:
SELECT * from emp;
查询指定列
SELECT col_name1,col_name2....from tab_name;
示例
#查询公司员工姓名,工资,奖金
SELECT ename,sal,comm FROM emp;
去掉重复值
SELECT DISINCT col_name FROM tab_name;
示例:
#查询公司所有员工的工资等级
SELECT DISINCT sal FROM emp;
查询语句中使用算数表达式
#计算员工奖金增加500后的奖金结果
SELECT ename,comm+500 FROM emp;
查询语句中使用列的别名
SELECT col_name AS 别名 FROM emp;
2.2条件查询where子句
比较运算符
where子句中可以使用的比较运算符包括> >= < <= <>。
示例
#查询公司中小于3000的所有员工
SELECT * FROM emp WHERE sal<3000;
逻辑运算符
逻辑运算符包含and or not三种运算
示例
#查询薪水大于4000,奖金大于4000的所有员工
SELECT * FROM emp WHERE sal>4000 and comm>4000;
BETWEEN…AND
表示两者之间,例如between 60 and 100 相当于>=60 and <=100
示例
#查询公司中奖金在1000~5000之间的所有员工
SELECT * FROM emp WHERE comm BETWEEN 1000 AND 5000;
in查询
in用于没有规律的范围查询,相当于多个or的组合,;例如 id in(1,4,7)相当于id=1 or id=4 or id=7。
示例
#查询公司中职位为项目经理,项目组长的员工
SELECT * FROM emp WHERE job IN('项目经理','项目组长');
模糊查询like
使用通配符%和_ 实现,%表示任一个任意字符,_表示任意一个字符。
示例1
#查询姓宋的员工
SELECT * FROM emp WHERE ename LIKE '宋%';
示例2
#查询姓名由三个字组成,并且以义结尾的员工
SELECT * FROM emp WHERE ename LIKE '__义';
NULL值查询
NULL在数据库中表示没有数据,应使用is来判断列中是否存在NULL
示例1
#查询mgr的值为NULL的员工
SELECT * FROM emp WHERE mgr IS NULL;
示例2
#查询mgr的值不为NULL的员工
SELECT * FROM emp WHERE mgr IS NOT NULL;
2.3排序order by子句
order by 子句用于排序,升序需制定ASC,降序需指定DESC,默认是ASC。
示例
#按照工资从高到低的查询员工信息,工资相同的再按照奖金升序排序
SELECT * FROM emp ORDER BY sal DESC,comm ASC;
2.4聚合函数查询
聚合就是将多个数据聚合成一个数据,聚合是通过聚合函数实现的,聚合函数有sum、max、min、avg、count共五个
示例
SELECT
MAX(sal) 最高工资,
MIN(sal) 最低工资,
AVG(sal) 平均工资,
SUM(sal) 总工资,
COUNT(*) 公司总人数
FROM
emp;
2.5分组查询group by 子句
分组是按照指定的列,将列中相同的值分为一组,一组用一条记录来表示。分组后,可以对每组中的数据进行聚合查询
示例
SELECT
deptno 部门编号,
COUNT(*) 人数
FROM
emp
GROUP BY
deptno;
2.6分组后筛选having子句
Having子句是对分组后,每组中的数据进行筛选。相对于where子句是在分组前进行的筛选,having子句是在分组后进行的筛选。
示例
SELECT
deptno 部门编号,
COUNT(*) 人数
FROM
emp
GROUP BY
deptno
HAVING
人数 > 3;
2.7多表查询
笛卡尔乘积现象
- 表查询中的笛卡尔乘积现象:多行表在查询时,如果定义了无效连接或者漏写了连接条件,就会产生笛卡尔乘积现象,
- 所谓笛卡尔乘积就是每个表的每一行都和其他表的每一行组合,
- 假设两张表的总行数分别是X行和Y行,笛卡尔乘积就会返回X*Y行记录。
SELECT
*
FROM
emp,
dept;
2.8子查询
子查询
存在于另外一个SQL语句中、被小括号包起来的查询语句就是子查询
相对于子查询来说,在外部直接执行的查询语句被称作主查询
单行子查询:
返回单行单列数据的子查询
示例:
查看 emp 表中与 SMITH 岗位相同的员工信息
- 先尝试查看 SMITH 所从事的岗位
SELECT job FROM emp WHERE ename = 'SMITH' ;
2.查询从事 clerk 工作的员工
SELECT * FROM emp WHERE lower(job) = 'clerk' ;
综合
SELECT * FROM emp WHERE lower(job) = ( SELECT lower(job) FROM emp WHERE lower(ename) = 'smith' );
多列子查询:
返回单行多列数据的子查询
示例:
查询 与 SMITH 在同一个部门且岗位相同的员工的信息
分步1: 查询 SMITH 所在的部门和从事的岗位
SELECT deptno , job FROM emp WHERE upper(ename) = 'SMITH' ;
分步2: 查询在 20 部门从事 clerk 岗位的员工信息
SELECT *
FROM emp
WHERE deptno = 20 AND job = 'CLERK' ;
成对比较
SELECT *
FROM emp
WHERE (deptno,job) =
( SELECT deptno , job FROM emp WHERE upper(ename) = 'SMITH' );
多行子查询
示例
查询emp表中与20部门员工岗位相同的员工信息
分步1: 查询20部门的所有岗位
SELECT DISTINCT job FROM emp WHERE deptno = 20 ;
分步2: 根据20部门的岗位来查询emp表中的员工
SELECT * FROM emp WHERE job IN ( 'clerk' , 'manager' , 'analyst');
关联子查询(相关子查询) )
示例:
子查询中如果使用了外部主SQL中的表或列,就说这个子查询跟外部SQL是相关的
查询哪些员工与SMITH不在同一个部门
SELECT * FROM emp e
-> WHERE EXISTS ( SELECT * FROM emp p WHERE p.ename = 'SMITH' AND p.deptno != e.deptno
事务
事务:
构成单一逻辑工作单元的操作集合称作事务( transaction ),
它是作为不可分割的逻辑单元执行的一组SQL语句。
0、开启事务
begin ;
1、杨某 账户上减少 5000
UPDATE t_accounts SET balance = balance - 5000 WHERE card_no = ‘123666321’ ;
2、龙某 账户上增加 5000
UPDATE t_accounts SET balance = balance + 5000 WHERE card_no = ‘456888654’ ;
3、提交事务
commit ;
事务特性
Atomicity(原子性): 事务中的所有操作要么都发生、要么都不发生
UPDATE t_accounts SET balance = balance - 5000 WHERE card_no = ‘123666321’ ;
— 停电了 — 没网了 — 地震了 — 台风来了 — 发大水
UPDATE t_accounts SET balance = balance + 5000 WHERE card_no = ‘456888654’ ;
Consistency(一致性): 事务将数据库从一种一致状态转变为下一种一致状态
转账之前,1 和 2 两个账户之和是 30000
id card_no name balance
1 123666321 杨某 20000
2 456888654 龙某 10000
成功转账之后,1 和 2 两个账户之和是 30000
id card_no name balance
1 123666321 杨某 15000
2 456888654 龙某 15000
Isolation(隔离性): 一个事务的执行,不应该受到其它事务的干扰。
—郭靖存钱1000—黄蓉存钱1000—郭芙取钱6000—
Durability(持久性): 事务一旦提交,其结果就是持久的。
查询当前会话中事务的提交模式
SELECT @@autocommit ;
– 系统变量 @@autocommit 取值为 1 表示自动提交,取值为 0 表示手动提交
– 在当前会话中设置事务的提交模式 ( 关闭自动提交,改用手动提交 )
SET @@autocommit = 0 ;
回滚事务( 期望撤销当前事务中的所有操作 )
mysql> rollback ;
事务控制语句
- 开始事务 BEGIN
- 提交事务 COMMIT
‘456888654’ ;
Consistency(一致性): 事务将数据库从一种一致状态转变为下一种一致状态
转账之前,1 和 2 两个账户之和是 30000
id card_no name balance
1 123666321 杨某 20000
2 456888654 龙某 10000
成功转账之后,1 和 2 两个账户之和是 30000
id card_no name balance
1 123666321 杨某 15000
2 456888654 龙某 15000
Isolation(隔离性): 一个事务的执行,不应该受到其它事务的干扰。
—郭靖存钱1000—黄蓉存钱1000—郭芙取钱6000—
Durability(持久性): 事务一旦提交,其结果就是持久的。
查询当前会话中事务的提交模式
SELECT @@autocommit ;
– 系统变量 @@autocommit 取值为 1 表示自动提交,取值为 0 表示手动提交
– 在当前会话中设置事务的提交模式 ( 关闭自动提交,改用手动提交 )
SET @@autocommit = 0 ;
回滚事务( 期望撤销当前事务中的所有操作 )
mysql> rollback ;
事务控制语句
- 开始事务 BEGIN
- 提交事务 COMMIT
- 回滚事务 ROLLBACK