1、MySQL数据库-不同表的本质仍然是文件
1、mysqld.exe 就是DBMS(数据库管理系统)里面最核心的一个程序,在3306处监听
2、创建的数据库存在data文件夹内
3、表存在创建的数据库中
2、常见命令
1、show databases;查看当前存在的数据可
2、create database xxxxxxxxxxxx; 创建数据库
3、选中某个数据库:use xxxxx;
4、导入数据库表:(路径不要带中文)
mysql> sourceE:\09sql\Better_Mysql\document\bjpowernode.sql
5、展示数据库中几张表:
show tables;
6、查看表中的数据:
show * from 表名;//统一执行这个SQL语句
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)
7、查看表的结构:(desc====>describe的缩写)
desc emp;
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
8、查看当前用的哪个数数据库: select database()
mysql> select database();
+-------------+
| database() |
+-------------+
| bjpowernode |
+-------------+
1 row in set (0.00 sec)
9、\c或者; 用来终止一条sql语句
10、退出mysql: exit;
11、查询部门名字:
mysql> select dname from dept;
+------------+
| dname |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)
12、查询两个字段 或者多个字段:
使用逗号, 隔开
ex:查询部门编号和部门名
mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
13、查询所有就是select * from dept;(最好不用!!!效率低,可读性差)
14、给查询的列起别名:(使用关键字 as 起别名 只是查询结果列名更改,原列表列名还是叫:dname
这里的deptno后面有逗号 仅仅只是查询 不参与起别名
mysql> select deptno,dname as deptname from dept;
+--------+------------+
| deptno | deptname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
15、起别名的时候 别名里有空格 那就用单引号括起来 所有的数据库中 字符串统一使用单引号括起来
mysql> select dname as 'd name' from dept;
+------------+
| d name |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)
16、起别名的作用:
mysql> select ename,sal * 12 from emp;//由此得知:字段可以使用数学表达式
+--------+----------+
| ename | sal * 12 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
17、别名想用中文需要使用单引号括起来''
mysql> select ename,sal as '年薪' from emp;
+--------+---------+
| ename | 年薪 |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)
3、条件查询:
1、select 字段1,字段2,字段3.... from 表名 where 条件;
比如说:查看薪资等于800的员工姓名和编号:
mysql> select empno,ename from emp where sal = 800;
+-------+-------+
| empno | ename |
+-------+-------+
| 7369 | SMITH |
+-------+-------+
1 row in set (0.00 sec)
mysql> select empno,ename,sal from emp where sal <= 3000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
13 rows in set (0.00 sec)
2、between and 语句
mysql> select empno,ename,sal from emp where sal between 2450 and 5000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
6 rows in set (0.00 sec)
3、查询哪些员工补助为空
mysql> select empno,ename,sal,comm from emp where comm is null;
+-------+--------+---------+------+
| empno | ename | sal | comm |
+-------+--------+---------+------+
| 7369 | SMITH | 800.00 | NULL |
| 7566 | JONES | 2975.00 | NULL |
| 7698 | BLAKE | 2850.00 | NULL |
| 7782 | CLARK | 2450.00 | NULL |
| 7788 | SCOTT | 3000.00 | NULL |
| 7839 | KING | 5000.00 | NULL |
| 7876 | ADAMS | 1100.00 | NULL |
| 7900 | JAMES | 950.00 | NULL |
| 7902 | FORD | 3000.00 | NULL |
| 7934 | MILLER | 1300.00 | NULL |
+-------+--------+---------+------+
10 rows in set (0.00 sec)
4、找出名字中含有 o 的:
mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
5、找出第二个字母是A的
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
6、找出名字中带有 "_" 的
select name from t_student where name like '%_%'; //这样的意思是只要是任意字符都满足条件,找不出来 select name from t_studenr where name like '%\_%': //需要加入转义字符: /
7、排序
mysql> select ename,sal from emp order by sal;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)
8、找出工资在1250 到3000之间的员工信息 要求按照薪资降序排列
mysql> select ename,sal from emp
-> where sal between 1250 and 3000 order by
-> sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+
10 rows in set (0.00 sec)
9、查看表的结构
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
10、数据库不见分号不执行,敲回车只是换行,分号只是表示结束
11、行和列的概念
1、数据库中以表格的形式存储数据 因为表格能直观的表现数据
2、任何一张表都有行和列
行(row) 被称为数据/记录
列(colum) 被称为字段
姓名 性别 年龄(列:字段)
=============================================================
大悦 女 20 --------------------------》行 (记录)
小悦 女 20 --------------------------》行 (记录)
狗蛋 女 20 --------------------------》行 (记录)
每一个字段名都有:字段名 数据类型 约束等属性
字段名可以理解为就是一个简单的名字
数据类型可以是 字符串 数字 日期等
sql语句不区分大小写
约束:约束有很多 其中一个叫唯一约束性约束
这种约束添加后 该字段中的数据不能重复
4、SQL语句分类:
DQL:数据查询语言 (凡是带有select关键字的都是查询语句)
最重要的!!!
DML:数据操作语言 (对表中的数据进行增删改查的)
对数据进行crud
DDL:数据定义语言 (凡是带有creat drop alter 都是DDL)主要操作的是表的结构 不是表的数据
比如说新加一个字段 或者删除一个字段 对结构进行操作
同样是增删改查 但是对表的结构进行操作
TCL: 事物控制语言
包括事物提交:commit;
事物回滚:rollback;
DCL;数据控制语言 例如:授权grant 撤销授权 revoke
5、数据处理函数
1、单行处理函数:写法就是:函数 + 小括号
lower(ename)
upper(ename)
。。。。。。
2、多行处理函数
1、转大写
select lower(ename) as ename from emp;//upper是小写
6、sql语句编写大总结
select ....
from....
where.....
group by.....
having......
order by.....============> 排序
mysql> select job,avg(sal) as avgsal from emp where job <> 'manager' group by job having avg(sal) > 1500
-> order by avgsal desc;
+-----------+-------------+
| job | avgsal |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
+-----------+-------------+
2 rows in set (0.00 sec)