mysql笔记
数据库:DataBase 简称:DB 按照一定格式存储数据的一些文件的组合
数据库管理系统: DataBaseManagement,简称:DBMS 专门用来管理数据库中的数据,可以对数据库中的数据进行增删改查 常见的数据库管理系统:MySQL、Oracle、MS SQLServer、DB2、sybase
SQL:结构化查询语言 通过编写SQL语句,由DBMS负责执行SQL语句,最终来完成数据库中数据的增删改查操作
SQL是一套标准,SQL在其他的数据库管理系统中也可以使用
三者之间的关系: DBMS--->通过执行SQL语句--->DB
登录MySQL:mysql -uroot -p***
常用命令:不区分大小写,不见分号不执行导入表格:source D:\MySQL\bjpowernode.sql退出:exit 查看数据库:show databases;
使用数据库:use 数据库名;查看数据库下的表:show tables;查看表中数据:select*from 表名;查看表的结构:desc 表名查看mysql数据库的版本号:select version();结束语句:\c查看当前使用的数据库: select database();
查看数据库:mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.05 sec)
查看表中数据:select*from 表名;
mysql> select*from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC |+--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
mysql> select*from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | 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 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.01 sec)
查看表的结构:desc 表名
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.01 sec)
查看mysql数据库的版本号:select version();
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.24 | +-----------+
查看当前使用的数据库:
mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
数据库中最基本的单元是表:table
数据库中是以表格的形式表示数据的,比较直观任何一张表都有行和列:行(row):被称为数据/记录列(column):被称为字段
每个字段都有:字段名、数据类型、约束等属性
SQL语句的分类:DQL:数据查询语言:select
DML:数据操作语言:insert增、delete删、update改(针对数据)
DDL:数据定义语言: 对表结构的增删改 create:新建,等同于增 drop:删除 alter:修改
TCL:事务控制语言:事务提交commit、事务回滚rollbackDCL:数据控制语言:授权grant、撤销权限revoke
简单查询语句:查询一个字段:select 字段名 from 表名;select、from都是关键字,字段名和表名都是标识符查询多个字段:select 字段名,字段名 from 表名; select* from 表名(查询表内的所有字段)
给查询的列起别名: select 字段原名 as 字段新名 from 表名 别名中有空格时,用引号进行标识 字段中可以使用表达式
mysql> select empno,ename as newename from emp where sal>3000; +-------+----------+ | empno | newename | +-------+----------+ | 7839 | KING | +-------+----------+ 1 row in set (0.00 sec)
同时给多个起别名
mysql> select empno as newempno,ename as newename from emp where sal>3000; +----------+----------+ | newempno | newename | +----------+----------+ | 7839 | KING | +----------+----------+ 1 row in set (0.00 sec)
别名中有空格
mysql> select empno,ename as "new ename" from emp where sal>3000; +-------+-----------+ | empno | new ename | +-------+-----------+ | 7839 | KING | +-------+-----------+ 1 row in set (0.00 sec)
条件查询:不是将表中的所有数据都查出来,而是查询符合条件的
语法格式:select
字段1,字段2,字段3....
from
表名
where
条件;
条件分类:=等于、<>,!=不等号、< 小于、>大于、小于等于<=、大于等于>=、between....and 两者之间(闭区间,包含两端的值)、is null为null(is not null表示不为空)、and 并且、or 或者、in 包含、not取非、like模糊查询,%任意多个字符,_任意一个字符例如:名字中含有“O”的,“%O%”
名字以“K”结尾的,“%K”
名字以“T”开头的,“T%”
第二个字符是“R”的,“_R%”...
如果找名字中有“_”的,利用“_”进行转义:“%_%”
and和or同时出现,and的语句优先级比or高,会先执行and语句 排序: order by默认升序
order by desc 指定降序
双项指定排序:
先按照工资升序排列,工资相同时按照名字升序排列:
select
ename,sal
from
emp
where
sal>2000
order by
sal asc,ename asc;
mysql> select -> ename,sal -> from -> emp -> where -> sal>2000 -> order by -> sal asc,ename asc; +-------+---------+ | ename | sal | +-------+---------+ | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +-------+---------+ 6 rows in set (0.00 sec)
相关用法:查询工资在2000到5000之间并且不住不为空的员工:
mysql> select ename,sal,deptno from emp where sal between 2000 and 5000 and comm is null; +-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | JONES | 2975.00 | 20 | | BLAKE | 2850.00 | 30 | | CLARK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | +-------+---------+--------+ 6 rows in set (0.00 sec)
查询工作岗位是manager和clerk的员工:
mysql> select ename,sal,job from emp where job="manager" or job="clerk"; +--------+---------+---------+ | ename | sal | job | +--------+---------+---------+ | SMITH | 800.00 | CLERK | | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | | ADAMS | 1100.00 | CLERK | | JAMES | 950.00 | CLERK | | MILLER | 1300.00 | CLERK | +--------+---------+---------+ 7 rows in set (0.00 sec)
查询工资大于2000并且工资编号为10或者20的员工:
mysql> select sal,ename,deptno from emp where sal>2000 and (deptno="10" or deptno="20"); +---------+-------+--------+ | sal | ename | deptno | +---------+-------+--------+ | 2975.00 | JONES | 20 | | 2450.00 | CLARK | 10 | | 3000.00 | SCOTT | 20 | | 5000.00 | KING | 10 | | 3000.00 | FORD | 20 | +---------+-------+--------+ 5 rows in set (0.00 sec) mysql> select ename,sal from emp where sal in(1100,3000); +-------+---------+ | ename | sal | +-------+---------+ | SCOTT | 3000.00 | | ADAMS | 1100.00 | | FORD | 3000.00 | +-------+---------+ 3 rows in set (0.01 sec)
模糊查询:
mysql> select ename,sal from emp where ename like "%l%"; +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | MILLER | 1300.00 | +--------+---------+ 4 rows in set (0.00 sec)
排序:
升序:
mysql> select ename,sal from emp where sal>2000 order by sal; +-------+---------+ | ename | sal | +-------+---------+ | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +-------+---------+ 6 rows in set (0.00 sec)
降序:
mysql> select ename,sal from emp where sal>2000 order by sal desc; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | +-------+---------+ 6 rows in set (0.00 sec)
按照第二列排序:
mysql> select ename,sal from emp where sal>1100 order by 2; +--------+---------+ | ename | sal | +--------+---------+ | 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 | +--------+---------+ 11 rows in set (0.00 sec)
找出薪资在1250到3000之间的员工信息并且按照薪资降序排列:
mysql> select*from emp where sal between 1250 and 3000 order by sal desc; +-------+--------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+---------+--------+ | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 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 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ 10 rows in set (0.00 sec)
数据处理函数:单行处理函数
单行处理函数特点:一个输入对应一个输出。
和单行处理函数相对的是多行处理函数。(多行处理函数特点:多行输入,对应一个输出)
常见的单行处理函数:
lower:转换成小写
upper:转换成大写
mysql> select lower(ename) from emp where sal>2000; +--------------+ | lower(ename) | +--------------+ | jones | | blake | | clark | | scott | | king | | ford | +--------------+ 6 rows in set (0.00 sec)
substr:取子串(substr:被截取的字符串,起始下标,截取的长度,起始下标从1开始 )
mysql> select substr(ename,2,3) from emp where sal>2000; +-------------------+ | substr(ename,2,3) | +-------------------+ | ONE | | LAK | | LAR | | COT | | ING | | ORD | +-------------------+ 6 rows in set (0.01 sec)
length:取长度
mysql> select length(ename) from emp where sal>2000; +---------------+ | length(ename) | +---------------+ | 5 | | 5 | | 5 | | 5 | | 4 | | 4 | +---------------+ 6 rows in set (0.00 sec)
concat:字符串拼接
mysql> select concat(ename,empno) from emp; +---------------------+ | concat(ename,empno) | +---------------------+ | SMITH7369 | | ALLEN7499 | | WARD7521 | | JONES7566 | | MARTIN7654 | | BLAKE7698 | | CLARK7782 | | SCOTT7788 | | KING7839 | | TURNER7844 | | ADAMS7876 | | JAMES7900 | | FORD7902 | | MILLER7934 | +---------------------+ 14 rows in set (0.00 sec)
trim:去除的空格
mysql> select * from emp where ename=trim(' king'); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | +-------+-------+-----------+------+------------+---------+------+--------+ 1 row in set (0.00 sec)
data_format:日期格式化
round:四舍五入:
mysql> select round(1234.567,1) from emp; +-------------------+ | round(1234.567,1) | +-------------------+ | 1234.6 | | 1234.6 | +-------------------+ 14 rows in set (0.01 sec) mysql> select round(1234.567,0) from emp; +-------------------+ | round(1234.567,0) | +-------------------+ | 1235 | | 1235 | +-------------------+ 14 rows in set (0.00 sec) mysql> select round(1234.567,-1) from emp; +--------------------+ | round(1234.567,-1) | +--------------------+ | 1230 | | 1230 | +--------------------+ 14 rows in set (0.00 sec)
select后面可以跟某个表的字段名也可以跟字面量/字面值(数据):
mysql> select 'abc' as ABC from emp; +-----+ | ABC | +-----+ | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | +-----+ 14 rows in set (0.01 sec)
rand():生成随机数:
mysql> select round(rand()*100) from emp; +-------------------+ | round(rand()*100) | +-------------------+ | 64 | | 33 | | 9 | | 45 | | 97 | +-------------------+ 14 rows in set (0.00 sec)
format:设置千分位
str_to_data:将字符串转换成日期
data_format:日期格式化
finull:可以将null转换成具体的数值
mysql> select ename,(sal+ifnull(comm,0))*12 from emp; +--------+-------------------------+ | ename | (sal+ifnull(comm,0))*12 | +--------+-------------------------+ | SMITH | 9600.00 | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | 35700.00 | | MARTIN | 31800.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) case when then when then else and::: mysql> select ename,sal,(case job when 'manager' then sal1.1 when 'clerk' then sal1.5 else sal end) as newsal from emp; +--------+---------+---------+ | ename | sal | newsal | +--------+---------+---------+ | SMITH | 800.00 | 1200.00 | | ALLEN | 1600.00 | 1600.00 | | WARD | 1250.00 | 1250.00 | | JONES | 2975.00 | 3272.50 | | MARTIN | 1250.00 | 1250.00 | | BLAKE | 2850.00 | 3135.00 | | CLARK | 2450.00 | 2695.00 | | SCOTT | 3000.00 | 3000.00 | | KING | 5000.00 | 5000.00 | | TURNER | 1500.00 | 1500.00 | | ADAMS | 1100.00 | 1650.00 | | JAMES | 950.00 | 1425.00 | | FORD | 3000.00 | 3000.00 | | MILLER | 1300.00 | 1950.00 | +--------+---------+---------+ 14 rows in set (0.00 sec)
分组函数:多行处理函数
特点:输入多行,最终输出一行(5个)
count:计数
sum:求和
avg: 平均值
max:最大值
min:最小值
注意:分组函数必须先分组才能使用
如果没有分组,则整张表默认为一组
mysql> select count(sal) from emp; +------------+ | count(sal) | +------------+ | 14 | +------------+ 1 row in set (0.01 sec) mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+ 1 row in set (0.01 sec) mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ 1 row in set (0.00 sec) mysql> select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+ 1 row in set (0.00 sec) mysql> select sum(sal) from emp; +----------+ | sum(sal) | +----------+ | 29025.00 | +----------+ 1 row in set (0.00 sec)
分组函数使用过程中注意事项:
1>分组函数自动忽略null,不需要提前对null进行处理
mysql> select sum(comm) from emp; +-----------+ | sum(comm) | +-----------+ | 2200.00 | +-----------+ 1 row in set (0.00 sec) mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
2>分组函数中count*和count+字段的区别
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec) mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
count+字段表示统计该字段下所有不为空的元素的总数
count:统计表中的总行数,只有某一行有一个数据,该行则在 count中有意义
3>分组函数不能直接使用在where子句中
4>所有的分组函数可以组合起来一起使用
mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp; +----------+----------+----------+-------------+----------+ | sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) | +----------+----------+----------+-------------+----------+ | 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 | +----------+----------+----------+-------------+----------+ 1 row in set (0.01 sec)
分组查询:
意义:当需要对数据进行分组然后查询时,就会使用到分组查询
找出每个部门的最高薪资:
mysql> select max(sal) from emp group by job; +----------+ | max(sal) | +----------+ | 3000.00 | | 1300.00 | | 2975.00 | | 5000.00 | | 1600.00 | +----------+ 5 rows in set (0.01 sec)
select
···
from
···
group by
···
order by
···
以上关键字的顺序不能颠倒,执行顺序为:
1.from
2.where
3.group
4.select
5.order by
分组函数不能直接使用在where后:
分组函数必须先分组才能使用,where执行的时候,还没有分组,所以where后面不能出现分组函数
在一条select语句中,如果有group by 语句的话,select后面只能跟参加分组的字段,以及分组函数,其他的内容一律不能跟。
按照工作岗位,求各个工作岗位的工资和:
mysql> select job,sum(sal) from emp group by job; +-----------+----------+ | job | sum(sal) | +-----------+----------+ | ANALYST | 6000.00 | | CLERK | 4150.00 | | MANAGER | 8275.00 | | PRESIDENT | 5000.00 | | SALESMAN | 5600.00 | +-----------+----------+ 5 rows in set (0.01 sec)
可以对两个字段进行同时分组:
找出“每个部门,不同岗位”的最高薪资:
mysql> select deptno,job,max(sal)from emp group by job,deptno; +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 20 | ANALYST | 3000.00 | | 10 | CLERK | 1300.00 | | 20 | CLERK | 1100.00 | | 30 | CLERK | 950.00 | | 10 | MANAGER | 2450.00 | | 20 | MANAGER | 2975.00 | | 30 | MANAGER | 2850.00 | | 10 | PRESIDENT | 5000.00 | | 30 | SALESMAN | 1600.00 | +--------+-----------+----------+ 9 rows in set (0.00 sec)
having语句不能单独使用,不能替代where语句,必须和group by语句进行联合使用,使用having语句可以对分完组之后的数据进行进一步的过滤。
mysql> select deptno,max(sal) from emp where sal>2100 group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ 3 rows in set (0.00 sec) mysql> select deptno,max(sal) -> from emp -> group by -> deptno -> having max(sal)>2100; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ 3 rows in set (0.00 sec)
在where无法满足需求时,再使用having语句:
找出每个部门的平均薪资,并显示平均薪资高于2100的
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2100; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | +--------+-------------+ 2 rows in set (0.00 sec)
单表查询的符合语句:select
from
where
group by
having
order by
执行顺序:1.from2.where3.group by4.having5.select6.order by
从某张表中查询数据,先经过where条件筛选出有价值的数据,对有价值的数据进行分组,分组之后可以使用having继续筛选,select查询出来,最后进行排序输出。
找出每个工作岗位的平均薪资,要求显示平均薪资大于1500,除manager岗位之外,降序排列:
mysql> select job,avg(sal) from emp where job !='manager' group by job having avg(sal)>1500 order by avg(sal) desc; +-----------+-------------+ | job | avg(sal) | +-----------+-------------+ | PRESIDENT | 5000.000000 | | ANALYST | 3000.000000 | +-----------+-------------+ 2 rows in set (0.00 sec)
查询结果去除重复记录,原表结构数据不会被修改:distinct
mysql> select distinct job from emp; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+
distinct出现在两个字段前,是两个字段联合去除重复记录:
mysql> select distinct job,deptno from emp; +-----------+--------+ | job | deptno | +-----------+--------+ | CLERK | 20 | | SALESMAN | 30 | | MANAGER | 20 | | MANAGER | 30 | | MANAGER | 10 | | ANALYST | 20 | | PRESIDENT | 10 | | CLERK | 30 | | CLERK | 10 | +-----------+--------+ 9 rows in set (0.00 sec)
连接查询:
定义:链接查询是指从一张表中单独查询,指的是单独查询
从多张表中联合起来查询数据,被称为连接查询
连接查询的分类:
SQL92:92年出现的语法
SQL9999年出现的语法
根据表链接的方式分类:
内连接
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
忧外连接(右连接)
连接查询:
mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.01 sec)
内连接之等值连接:
SQL92的语法:
mysql> select e.ename,d.dname from emp e,dept d (表起别名) where e.deptno=d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec) //inner 可以省略(表示内连接)
SQL99 的语法:select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno; where...(可以加后续的条件)
内连接之等值连接:条件不是一个 等值关系,称为非等值连接
找出每个员工的工资等级,显示员工的姓名、工资、工资等级:
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; +--------+---------+-------+ | ename | sal | grade | +--------+---------+-------+ | SMITH | 800.00 | 1 | | ALLEN | 1600.00 | 3 | | WARD | 1250.00 | 2 | | JONES | 2975.00 | 4 | | MARTIN | 1250.00 | 2 | | BLAKE | 2850.00 | 4 | | CLARK | 2450.00 | 4 | | SCOTT | 3000.00 | 4 | | KING | 5000.00 | 5 | | TURNER | 1500.00 | 3 | | ADAMS | 1100.00 | 1 | | JAMES | 950.00 | 1 | | FORD | 3000.00 | 4 | | MILLER | 1300.00 | 2 | +--------+---------+-------+ 14 rows in set (0.00 sec)
内连接之自连接:
自连接的技巧:一张表看做两张表。
查询员工的上级领导,显示员工的名和对应的领导名
mysql> select ename,empno,mgr from emp;\ +--------+-------+------+ | ename | empno | mgr | +--------+-------+------+ | SMITH | 7369 | 7902 | | ALLEN | 7499 | 7698 | | WARD | 7521 | 7698 | | JONES | 7566 | 7839 | | MARTIN | 7654 | 7698 | | BLAKE | 7698 | 7839 | | CLARK | 7782 | 7839 | | SCOTT | 7788 | 7566 | | KING | 7839 | NULL | | TURNER | 7844 | 7698 | | ADAMS | 7876 | 7788 | | JAMES | 7900 | 7698 | | FORD | 7902 | 7566 | | MILLER | 7934 | 7782 | +--------+-------+------+ 14 rows in set (0.00 sec) mysql> select a.ename as '员工',b.ename as '领导' from emp a join emp b on a.mgr=b.empno; +--------+-------+ | 员工 | 领导 | +--------+-------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+-------+ 13 rows in set (0.00 sec)
外连接(右外连接):又称作右连接
mysql> select e.ename,d.dname from emp e right join dept d on e.deptno= d.deptno;
right表示将join关键字右边的这张表看做主表,并显示出主表的全部内容,在外连接中产生了主次关系。
+--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | | NULL | OPERATIONS | +--------+------------+ 15 rows in set (0.01 sec)
查询每个员工的上级领导,显示所有的员工和领导的名字:
mysql> select a.ename as '员工名',b.ename as '领导名' from emp a left join emp b on a.mgr=b.empno; +--------+--------+ | 员工名 | 领导名 | +--------+--------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | KING | NULL | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+--------+ 14 rows in set (0.00 sec)
多张表的连接:语法: select ... from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d的连接条件
一条SQL中,内连接和外连接可以混合,都可以出现
例子:找出每个员工的部门以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资以及薪资等级:
子查询:定义:select语句中嵌套select语句,被嵌套的select语句称为子查询
语法: select ..(select). from ..(select) where ..(select)
from子句中的子查询:注意:from后面的子查询,可以将子查询的查询结果当做一张临时表例子:找出每个工作岗位的平均工资的薪资等级:
mysql> select t.*,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between losal and hisal; +-----------+-------------+-------+ | job | avgsal | grade | +-----------+-------------+-------+ | ANALYST | 3000.000000 | 4 | | CLERK | 1037.500000 | 1 | | MANAGER | 2758.333333 | 4 | | PRESIDENT | 5000.000000 | 5 | | SALESMAN | 1400.000000 | 2 | +-----------+-------------+-------+ 5 rows in set (0.00 sec)
union合并查询结果集:
1>select ename,job from emp where job='manager' or job='salesman';
2>select ename,job from emp where job in('manager','salesman');
union :
3>select ename,job from emp where job='manager'
union
select ename,job from emp where job='salesman';
上述三天语句查询结果相同,但union语句的效率更高,对于表连接来说,每连接一次新表,匹配的次数要满足笛卡尔积,成倍数增加。但是union可以减少匹配的次数,在减少匹配次数的情况下,还可以完成两个结果集的拼接。
union使用的注意事项:
在结果合并时,要求结果的列数相同列和列的数据类型相同
limit:作用:将查询的结果集的一部分取出来,通常使用在分页查询当中。
示例:按照薪资排序,去除排名在前五的员工:
select ename,sal from emp order by sal desc limit 0,5;
完整用法:limit startindex,length,整体的起始下标从0开始。
注意:mysql中limit在order by之后执行
eg:取出工资排名在【3-5】名的员工,按照工资降序排列。
mysql> select ename,sal from emp order by sal desc limit 2,3;(起始下标,长度) +-------+---------+ | ename | sal | +-------+---------+ | SCOTT | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 3 rows in set (0.00 sec)
通用分页:
公式:limit (pageno-1)*pagesize,pagesize;
关于DQL语句的大总结:
查询语句:
select .. from ..where.. group by.. having.. order by..limit...;
执行顺序:from-where-group by - having - select-order by - limit
表的创建(建表):DDL语句,包括create、drop、alter
建表的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);表名:建议以t-或者tbl-开始,可读性强,见名知意。
关于mysql中的数据类型:
常见的数据类型:
varchar:可变长度的字符串,根据传入的数据长度,动态分配空间。可节省空间,但是速度慢。最长255
char:定长字符串,不管实际数据长度是多少,都会分配定长的空间存储数据。不需动态分配空间,但是速度快。最长255
int:数字中的整数型,等同java中的int.最长11
bigint:数字中的长整型,等同java中的long
float:单精度浮点型数据
double:双精度浮点型数据
date :短日期类型
datetime:长日期类型
clob:字符大对象,最多可以存储4G的字符串
blob:二进制大对象,专门存储图片、声音、视频等流媒体数据。插入数据时需要使用IO流。
建表的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
创建一个电影信息表:
create table t_movie(
no bigint;
name varchar;
history clob;
playtime data;
time double;
image blob;
);
创建一个学生表:
学号、姓名、性别、年龄、邮箱地址 create table t_student( no int, name varchar(32), sex char(1), age int(3), email varchar(255) );
删除表: drop table t_student; 如果这张表存在的话删除:drop table if exists t_student;
insert语句语法格式: insert into 表名(字段名1,字段名2,字段名3) value(值1,值2,值3) );
在t_student表中插入数据: insert into t_student(no,name,sex,age,email) value(1111,jack,m,23,123@q.com);
字段名和值要一一对应!!!
mysql> insert into t_student(no,age,sex,name) value(1,12,'w','jack'); Query OK, 1 row affected (0.02 sec) mysql> select*from t_student; +------+------+------+------+-------+ | no | name | sex | age | email | +------+------+------+------+-------+ | 1 | jack | w | 12 | NULL | +------+------+------+------+-------+ 1 row in set (0.00 sec)
注意:insert语句执行成功,必定会生成一条记录,未赋值的属性会自动赋值为NULL。
default可以进行默认赋值。
数字格式化:format
format(数字,格式)
日期:str-to-data:将varchar字符串转换成date类型
把字符串varchar转换成data的日期数据类型,通常使用在插入insert方面,因为插入的时候需要一个日期类型的数据,需要通过该函数将字符串转换成data。如果提供的日期字符串格式为%Y-%m-%d,则不需要使用函数,可自行转换。
mysql的日期格式: %Y年 %m月 %d日 %h 时 %i分 %s秒
insert into t_user(id,name,birth) value(1,‘zhangsan’,str_to_date('10-01-1990','%d-%m-%Y'))
date-format:将date类型转换成varchar字符串类型
date-format函数的使用:date-format(日期类型数据,‘日期格式’)
比如查询日期时使用
java中的日期格式:YYYY-MM-dd HH:mm:ss SSS
date和datetime两个类型的区别:
date是短日期:只包括年月日的信息
datetime是长日期:包括年月日时分秒信息
mysql短日期默认格式:%Y-%m-%d
mysql长日期默认格式:%Y-%m-%d %h:%i:%s
在mysql中获取系统当前时间:
now()函数,可获取当前系统时间并带有时分秒信息
drop table if exists t_student; create table t_student(id int,name varchar(32), birth date,creat_time datetime); mysql> insert into t_student(id,birth,name,creat_time) value(21,'1990-10-01','w',now()); Query OK, 1 row affected (0.01 sec) mysql> select * from t_student; +------+------+------------+---------------------+ | id | name | birth | creat_time | +------+------+------------+---------------------+ | 1 | w | 1990-10-01 | NULL | | 21 | w | 1990-10-01 | 2022-06-20 00:25:46 | +------+------+------------+---------------------+ 2 rows in set (0.00 sec)
修改update(DML语句)语法格式: update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3...where 条件;
注意:没有条件限制会导致所有的数据全部更改。
mysql> select * from t_student; +------+------+------------+---------------------+ | id | name | birth | creat_time | +------+------+------------+---------------------+ | 1 | w | 1990-10-01 | NULL | | 21 | w | 1990-10-01 | 2022-06-20 00:25:46 | +------+------+------------+---------------------+ 2 rows in set (0.00 sec) mysql> update t_student set id=2,birth='2000=01-01',name='x' where creat_time=' 2022-06-20 00:25:46'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_student; +------+------+------------+---------------------+ | id | name | birth | creat_time | +------+------+------------+---------------------+ | 1 | w | 1990-10-01 | NULL | | 2 | x | 2000-01-01 | 2022-06-20 00:25:46 | +------+------+------------+---------------------+ 2 rows in set (0.00 sec)
注意:没有条件限制会导致所有的数据全部更改。
mysql> update t_student set name='abc'; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from t_student; +------+------+------------+---------------------+ | id | name | birth | creat_time | +------+------+------------+---------------------+ | 1 | abc | 1990-10-01 | NULL | | 2 | abc | 2000-01-01 | 2022-06-20 00:25:46 | +------+------+------------+---------------------+ 2 rows in set (0.00 sec)
删除数据delete(DML)
语法格式:
delete from 表名 where;
注意:没有条件整张表都会删除!
mysql> delete from t_student where id=2; Query OK, 1 row affected (0.01 sec) mysql> select * from t_student; +------+------+------------+------------+ | id | name | birth | creat_time | +------+------+------------+------------+ | 1 | abc | 1990-10-01 | NULL | +------+------+------------+------------+ 1 row in set (0.00 sec)
注意:没有条件整张表都会删除!
mysql> delete from t_student; Query OK, 1 row affected (0.01 sec) mysql> select * from t_student; Empty set (0.00 sec)
insert一次可以插入多条语句:
mysql> insert into t_student (id,name,birth,creat_time) value (3,'zhangsan','1990-09-09',now()), (4,'zhangsan','1990-09-09',now()); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select* from t_student; +------+----------+------------+---------------------+ | id | name | birth | creat_time | +------+----------+------------+---------------------+ | 1 | lisi | 1990-01-20 | 2022-06-20 10:27:16 | | 2 | lisa | 1990-01-10 | 2022-06-20 10:32:38 | | 3 | zhangsan | 1990-09-09 | 2022-06-21 00:17:22 | | 3 | zhangsan | 1990-09-09 | 2022-06-21 00:17:53 | | 4 | zhangsan | 1990-09-09 | 2022-06-21 00:17:53 | +------+----------+------------+---------------------+ 5 rows in set (0.00 sec)
快速创建表:mysql> create table t_user2 as select *from t_student;
原理:将一个查询结果当做一张表新建,可以完成表的快速创建
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t_user; ERROR 1146 (42S02): Table 'mysql.t_user' doesn't exist mysql> select * from t_user2; +------+----------+------------+---------------------+ | id | name | birth | creat_time | +------+----------+------------+---------------------+ | 1 | lisi | 1990-01-20 | 2022-06-20 10:27:16 | | 2 | lisa | 1990-01-10 | 2022-06-20 10:32:38 | | 3 | zhangsan | 1990-09-09 | 2022-06-21 00:17:53 | | 4 | zhangsan | 1990-09-09 | 2022-06-21 00:17:53 | +------+----------+------------+---------------------+ 4 rows in set (0.00 sec)
将查询结果插入到一张表中:
create table dept_bak as select *from dept; mysql> select * from dept_bak; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec)
mysql> insert into dept_bak select * from dept_bak; /将查询结果插入到表中/ mysql> select * from dept_bak; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 8 rows in set (0.00 sec)
快速删除表中的数据://删除表中的数据,以dept-bak为例:delete from dept_bak;//这种删除数据的方式比较慢
mysql> delete from dept_bak;Query OK, 8 rows affected (0.01 sec)
mysql> select * from dept_bak;Empty set (0.00 sec)
delete语句删除数据的原理:属于DML语句
表中的书被删除,但是这个数据在硬盘上的真实存储空间不会被释放。
缺点:删除的效率比较低
优点:支持回滚,数据可恢复(删除之前使用start transaction;语句,删除之后使用rollback语句)
truncate语句删除数据的原理:属于DDL语句
删除数据的效率比较高,表被一次截断,物理删除
缺点:不支持回滚,数据不可恢复
优点:速度快、效率高
用法:truncate table dept_bak;
//删除表中的数据,但是表还在
删除表:drop table 表名;//不是删表中的数据,删除整个表
创建表过程中的约束:(*****)
约束,constraint
在创建表的时候,给表中的字段加上一些约束,来保证表中数据的有效性、完整性。
约束的分类:
非空约束:not null
唯一性约束:unique
主键约束:primary key(简称PK)
外键约束:foreign key(简称FK)
检查约束:check(mysql不支持,Oracle支持)
重点学习:
非空约束:not null
唯一性约束:unique
主键约束:primary key(简称PK)
外键约束:foreign key(简称FK)
/*XXX.sql这种文件被称为SQL脚本文件。SQL脚本文件中编写了大量的SQL语句。在执行脚本文件的时候,改文件中的所有SQL语句都会被执行!批量的执行SQL语句可以使用SQL脚本文件。
执行方式: source 绝对路径*/
非空约束:约束的字段不能为空NULL。
drop table if exists t_vip; create table t_vip( id int, name varchar(255) not null ); insert into t_vip (id,name) values (1,'zhangsan'); insert into t_vip (id,name) values (2,'lisi'); mysql> source C:\Users\LENOVO\Desktop\vip.sql Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> insert into t_vip (id,name) value (1,'zhangsan'); mysql> insert into t_vip (id,name) value (2,'lisi'); Query OK, 1 row affected (0.01 sec) mysql> select*from t_vip; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | lisi | +------+----------+ 2 rows in set (0.00 sec) mysql> insert into t_vip (id) value (2); ERROR 1364 (HY000): Field 'name' doesn't have a default value
唯一性约束:unique唯一性约束unique约束的字段不能重复,但是可以为NULL
mysql> create table t_vip( -> id int, -> name varchar(255) unique -> ); Query OK, 0 rows affected (0.02 sec) mysql> insert into t_vip (id,name) values (1,'zhangsan'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_vip (id,name) values (2,'lisi'); Query OK, 1 row affected (0.00 sec)
不能重复:mysql> insert into t_vip (id,name) values (3,'lisi');ERROR 1062 (23000): Duplicate entry 'lisi' for key 'name'但是可以为NULL:
mysql> insert into t_vip (id) values (3); Query OK, 1 row affected (0.00 sec) mysql> insert into t_vip (id) values (4); Query OK, 1 row affected (0.00 sec) mysql> select*from t_vip; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | NULL | | 4 | NULL | +------+----------+ 4 rows in set (0.00 sec)
name和email两个字段联合起来具有唯一性:
drop table if exists t_vip;create table t_vip( id int, name varchar(255) email varchar(255), unique(name,email)//没有添加在列(字段)的后面,成为表级约束。);
insert into t_vip (id,name,email) values (1,'zhangsan','zhansan@123.com');
insert into t_vip (id,name,email) values (2,'lisi','zhansan @123.com');
给多个字段联合起来添加某一个约束的时候,需要使用表级约束。
not NULL只有列级约束,但是unique可以使用表级约束
not null和unique 可以联合使用,mysql中联合之后成为了主键约束primary key,但是Oracle中不可以:
create table t_st( id int , name varchar(255) not null unique//不能重复,不能为空NULL ); mysql> desc t_st; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | NO | PRI | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
主键约束:primary key:
相关术语:
主键约束:是一种约束
主键字段:该字段上添加了主键约束
主键值:主键字段中的每一个值都叫做主键值
主键:主键值是每一行记录的唯一标识。用以区分记录和数据
注意:任何一张表都应该有主键,否则表无效!!!!
主键的特征:not null+unique 主键值不能为null不能重复
一个字段做主键叫做单一主键,两个或者多个字段联合做主键称为复合主键
主键只能有一个!!!!
主键值建议使用int bigint char等类型,不建议使用varchar类型。
主键除了单一主键和复合主键之外还有自然主键和业务主键
在mysql中有一种机制可以自动维护主键值:
drop table if exists t_st; create table t_st( id int primary key auto_increment, name varchar (255)); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); insert into t_st(name) value ('lisi'); select *from t_st; +----+------+ | id | name | +----+------+ | 1 | lisi | | 2 | lisi | | 3 | lisi | | 4 | lisi | | 5 | lisi | | 6 | lisi | | 7 | lisi | | 8 | lisi | | 9 | lisi | | 10 | lisi | +----+------+ 10 rows in set (0.00 sec)
//自动递增机制
外键约束:foreign key,简称FK
外键约束:一种约束
外键字段:该字段添加上了外键约束
外键值:外键字段当中的每一个值
drop table if exists t_student; drop table if exists t_class; create table t_class( classno int primary key, classname varchar(255) ); create table t_student( no int primary key auto_increment, name varchar(255), cno int , foreign key (cno) references t_class(classno) ); insert into t_class(classno,classname) value(100,'yiban'); insert into t_class(classno,classname) value(101,'erban'); insert into t_student(name,cno) value('zhangsan',100); insert into t_student(name,cno) value('lisi',100); insert into t_student(name,cno) value('wangwu',100); insert into t_student(name,cno) value('zhaoliu',100); insert into t_student(name,cno) value('qm',100); insert into t_student(name,cno) value('ag',101); insert into t_student(name,cno) value('ttg',101); insert into t_student(name,cno) value('es',101); insert into t_student(name,cno) value('we',101); mysql> select *from t_student; +----+----------+------+ | no | name | cno | +----+----------+------+ | 1 | zhangsan | 100 | | 2 | lisi | 100 | | 3 | wangwu | 100 | | 4 | zhaoliu | 100 | | 5 | qm | 100 | | 12 | zhangsan | 100 | | 13 | ag | 101 | | 14 | ttg | 101 | | 15 | es | 101 | | 16 | we | 101 | +----+----------+------+ 10 rows in set (0.00 sec) mysql> select *from t_class; +-------------+-----------+ | classno(pk) | classname | +-------------+-----------+ | 100 | yiban | | 101 | erban | | 102 | erban | +-------------+-----------+ 3 rows in set (0.00 sec)
外键值可以为NULL:insert into t_student(name) value('chenxiao');
mysql> select *from t_student;+-------+----------+----------+| no(pk)| name | cno(FK) |+-------+----------+----------+| 1 | zhangsan | 100 || 2 | lisi | 100 || 3 | wangwu | 100 || 4 | zhaoliu | 100 || 5 | qm | 100 || 12 | zhangsan | 100 || 13 | ag | 101 || 14 | ttg | 101 || 15 | es | 101 || 16 | we | 101 || 17 | chenxiao | NULL |+-------+----------+----------+11 rows in set (0.00 sec)
存储引擎:存储引擎是mysql中特有的一个术语,其他数据库中没有,(oracle中有但是名字不一样)实际上存储引擎是一个表存储或者组织数据的方式,不同的存储引擎,表存储数据的方式不同。
存储引擎展示:
show create table t_student; mysql> show create table t_student; +-----------+------------------------------+ | Table | Create Table | +-----------+------------------------------+ | t_student | CREATE TABLE t_student ( no int(11) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, cno int(11) DEFAULT NULL, PRIMARY KEY (no), KEY cno (cno), CONSTRAINT t_student_ibfk_1 FOREIGN KEY (cno) REFERENCES t_class (classno) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1 | +-----------+------------------------------+ 1 row in set (0.00 sec)
给表添加/指定存储引擎:在建表的时候可以在最后的小括号的右边使用 ENGINE=InnoDB, ENGINE指定存储引擎,mysql默认为InnoDB CHARSET=latin1,CHARSET指定字符集,默认为latin1。使用:
create table t_tt( id int, name varchar(255) )engine=InnoDB default charset=gbk; mysql> insert into t_tt(id,name)value(1,'中国'); Query OK, 1 row affected (0.00 sec) mysql> select *from t_tt; +------+------+ | id | name | +------+------+ | 1 | 中国 | +------+------+ 1 row in set (0.00 sec)
改变默认字符集,可以使用中文!!!!查看版本:
select version(); +------------+ | version() | +------------+ | 5.7.24 | +------------+ 1 row in set (0.00 sec)
查看当前版本下,mysql支持的存储引擎:命令:
show engines\G ********* 1. row ********* Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES ********* 2. row ********* Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO ********* 3. row ********* Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO ********* 4. row ********* Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO ********* 5. row ********* Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO ********* 6. row ********* Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO ********* 7. row ********* Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO
InnoDB存储引擎是mysql默认的存储引擎,同时是一个重量级引擎,支持数据库崩溃后的自动恢复机制。
关于存储引擎以及之后的事务部分可以后期缓慢了解