《MySQL》个人笔记
一、MySQL上手
1.MySQL服务的启动和停止
(1)计算机管理 / 服务/ MySQL_ lxk服务 / 启动/停止;
(2)Windows命令提示符(管理员身份运行)/ net start MySQL_lxk
2.MySQL登录和退出
(1)开始菜单 / MySQL 5.5 Command Line Client —— 直接启动
(2)Windows命令提示符(管理员身份运行)/ mysql -h localhost -P 3306 -u root -p ——DOS命令启动
-h 主机名 -P 端口号 -u 用户名 -p 密码
(3)Windows命令提示符(管理员身份运行)/ exit —— 命令退出
(4)Ctrl + c —— 强制退出
3.环境变量配置
找到WIndows系统环境变量,添加变量值,其值为MySQL的bin目录路径;
4.mysql基础命令
(1)查看数据库
show databases; *注意:必须以 " ; " 号结尾;
±---------------------------------+
| Database |
±---------------------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±---------------------------------+
*MySQL默认有4个数据库;
(2)使用数据库
use [数据库名称];
(3)创建数据库
create database [数据库名称];
(4)显示当前数据库中的表
show tables;
(5)查看MySQL的版本号
select version();
(6)查看当前所用的数据库
select database();
(7)终止为完成的命令
输入"\c" 就ok;
二、MySQL基本概念
1.表 数据库中最基本的单位
例‘成绩’表:
name sex score
---------------------
张三 男 90
李四 女 99
王五 男 86
- 数据库中是以表的形式存储数据的,因为表比较直观;
- 任何一张表都有行和列,行又称为数据/内容,列又称字段;
- 例上表有姓名字段、性别字段、成绩字段,每个字段都有字段名、数据类型、约束性等属性;
2. SQL语句分类
(1)DQL(Data Query Language) —— 数据查询语言
- 凡是带select关键字的都是查询语言;
(2)DML(Data Manipulation Language) —— 数据操作语言
- insert —— 插入
- delete —— 删除
- update —— 修改
- 对数据进行增删改的都是数据操作语言DML;
(3)DDL(Data Definition Language) —— 数据定义语言
- 凡是带有create、drop、alter的都是数据定义语言;
- DDL主要操作的是表的结构,不是表中的数据;
- create —— 新建字段; drop —— 删除字段; alter —— 修改字段;
(4)TCL(Transaction Control Language) —— 事务控制语言
- commit —— 事务提交;
- rollback —— 事务回滚;
(5)DCL(Data Control Language) —— 数据控制语言
- grant —— 授权;
- revoke —— 撤销授权;
3.数据库的导入
-
导入网上下载好的数据表,导入路径不能有中文;
-
+------------------------+ | Tables_in_lxk_database | +------------------------+ | dept | | emp | | salgrade | +------------------------+
导入命令:source [文件所在路径]
实验一 查看导入的数据库
1.启动MySQL服务;
2.登录MySQL;
3.查看并使用数据库;
4.显示所使用的数据库中的表;
5.查看这三张表的内容;
1.net start MySQL_lxk;
2.mysql -h localhost -P 3306 -u root -p;
3.show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lxk_database |
| mysql |
| performance_schema |
| test |
+--------------------+
4.use lxk_database;
5.show tables;
+------------------------+
| Tables_in_lxk_database |
+------------------------+
| dept |
| emp |
| salgrade |
+------------------------+
6.select * from dept;
select * from emp;
select * from salgrade;
*select是查询语言DQL,“ * ”代表选择全部;
4.查看表的结构
- desc [表名] describe —— 描述
mysql> select * from dept;//查看表数据
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> desc dept;//查看表结构
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2) | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
三、DQL(Data Query Language) 数据查询语言
1.简单查询
(1)语法:select [字段名] from [表名]
注意:对于SQL语句来说,是通用的;
所有的SQL语句以“ ;”结尾;
SQL语句不区分大小写;
-
查询一个字段
select [字段名] from [表名];
-
查询多个字段
select [字段名1], [字段名2]… from [表名];
- 查询所有字段
select * from [表名];
*一般不推荐写在语句里,因为要把 * 号转换成字段,这样会降低查询效率;
(2)查询结果改别名
-
语法格式:select [字段名] as [别名] from [表名];
-
改别名只是把显示结果修改了,并不会影响原表内容;
注意:
-
别名中不能使用纯数字,如果一定要用纯数字需用单引号括起来;
-
别名中如果有空格也用单引号括起来;
-
单引号内还可用双引号作为别名中的字符,但单引号不能;
-
修改多个别名: select [字段1] as [别名1], [字段2] as [别名2]… from [表名];
(3)列参 与 数学运算
select sal*12 from emp;//先查看表结构,其结构为int类型可参与数学运算
+----------+
| sal*12 |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
+----------+
select sal*12 as 'year sal' from emp;//运算后为年薪,修改字段名方便查看
+----------+
| year sal |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
2.条件查询
(1)格式:select [字段名] from [表名] where [条件];
(2)常用条件
-
= > < >= <= !=
-
between…and… 两数之间…注意,不能从大到小取值;
-
is null / is not null 判断字段是否为空不能用=符号,因为空字段无法比较;
-
and / or 这里同事出现and与or时and优先级会高于or,使用时应注意加括号;
-
in 包含,相当于多个or;
-
not 通常和is null搭配;
-
like 模糊查询;
-
% / _ 百分号匹配任意个字符,下划线对应单个字符
例如:区间查询
mysql> select ename,job,sal
-> from emp
-> where sal between 800 and 2000;
+--------+----------+---------+
| ename | job | sal |
+--------+----------+---------+
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| MILLER | CLERK | 1300.00 |
+--------+----------+---------+
*注意and与or的优先级
//查找工资大于2000且部门编号为20或30的员工
mysql> select *
-> from emp
-> where sal>=2000 and (deptno=20 or deptno=30);
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
//and与or同时出现的话and的优先级高于or
in的用法和is null
//查找工资等于800和3000的员工或有补贴的员
mysql> select *
-> from emp
-> where sal in (800,3000) or comm is not null;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+--------+----------+------+------------+---------+---------+--------+
模糊查找
//查找员工名字S开头或第二字符为A的员工信息;
mysql> select *
-> from emp
-> where ename like 'S%' or ename like '_A%';//多字段模糊查找,如果查找的名称有下划线,需要用 //转移字符'/'进行转义;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3.排序
(1)升序
select
*
from
emp
order by
sal;//按工资sal进行默认(升序)排序,指定升序的话加asc,ascend —— 上升;
(2)降序
select
*
from
emp
order by
sal desc;//按工资sal进行降序排序;descend —— 下降
(3)多字段排序
select
*
from
emp
order by
sal asc,ename asc;//多字段按顺序执行,主要排序为sal的升序,其次(sal相同时)是ename的升序;
实验:找出工资在1250到3000之间的员工信息,要求按照薪资降序排列
select
*
from
emp
where
sal between 1250 and 3000
order by
sal desc;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
/*
知识点:DQL查询语言的执行顺序:
from -> where -> select -> order by(排序总会在最后执行)。
*/