MySql


MySQL

SQL语句分类

  • DDL(Data Definition Language) -- 数据库定义语言,增删改查库或表
  • DML(Data Control Language) -- 数据库操作语言 操作数据 对表中数据的增删改查
  • DCL(Data Manipulation Language) -- 数据库控制语言 创建用户,管理用户权限,事务控制

非标准分类:DQL(Data Query Language) -- 数据库查询,将数据库的增删改查中的“查”单独提出来,因为最重要也最难。

与服务器连接

先把MySQL配置到系统环境变量中,以为5.7为例,Path里增加C:\Program Files\MySQL\MySQL Server 5.7\bin。cmd里输入mysql -u root -p,其中u代表user,这里用root,p代表密码,不输入密码,回车后也会提示你输入。填入自己的密码就连接成功了。

数据库的CURD

1、添加新数据库

create database 库名称 [character set 码表名称 collate 字符校对集名称];,括号里面是可选项,一般不加也可,举个例子create database book;,注意每行语句结束,需要加;show databases;指令可以显示全部数据库。

2、删除数据库

drop database 库名称;

3、修改数据库(可修改码表和字符校对集)

alter database 库名称 character set utf8 collate utf8_bin;

默认utf-8,该语句不常用。

4、使用一个库

use 库名称;成功会出现Database changed。

select database();可以显示当前正在使用的数据库。

5、显示创建数据库语句

show create database 数据库名称;

数据库中的数据类型

1、数字型

整型

整型类型 大小 对比Java
TINYINT 1字节 byte
SMALLINT 2字节 short
MEDIUMINT 3字节 ----
(常用)INT 4字节 int
BIGINT 8字节 long

浮点型

浮点类型 大小 对比Java
FLOAT 单精度4字节 float
(常用)DOUBLE 8字节 double
DECIMAL -- String保存的数字,没有精度损失

DOUBLE 和 DECIMAL 区别?

  • DOUBLE类型在运算时会有精度的缺失。
  • DECIMAL 就是解决精度缺失问题的。(底层使用字符串来保存数字)

单纯想表示小数属性时,使用double。

需要频繁参与运算的小数,使用decimal。

2、字符串

注意: 字符串类型要使用单引号包裹.

短字符串类型

CHAR/VARCHAR (最大长度255字节)

char和varchar有什么区别?

  • char:定长字符串。
  • varchar:变长字符串。

同时指定长度为10。当存储 abc时候

char =》 'abc ' 后面有7个空格
varchar => 'abc'

结论: 开发中varchar用的最多。 char只在表示固定长度的枚举中使用。例如 :性别(用01,02表示)

长字符串类型(流类型)

TEXT

保存文本(字符流) --> 当要保存的内容超过255字节时使用。java中的writer 字符。TEXT只能存储字符数据.

BLOB

保存字节(字节流) --> 开发中用不到。 java中的stream 字节。

BLOB可以存储字符和多媒体信息(图片 声音 图像)

3、日期类型

类型 记录的数据 例子
date 只记录日期 2017-03-11
time 只记录时间 10:36:25
year 只记录年 2017
(常用)datatime 记录日期、时间 2017-03-11 10:36:25
(常用)timestamp 记录日期、时间 2017-03-11 11:36:25

datatime 和 timestamp 区别?

  • 这两种类型记录的数据是一模一样。

  • 区别在于插入的时候,如果插入datatime类型时,没有传值,那么该类型默认值就是

null;如果插入timestamp类型时,没有传值,那么该类型默认值就是当前时间。

表的CURD

1、创建表

create table t_user (
    id int,
    name varchar(20),
    sal double(4,3),
    birthday datetime,
    hiredate timestamp
);

double(4, 3)中间是逗号,表示小数点前4位,小数点后面3位。

2、显示所有表、查看某个表的结构

显示所有表:show tables;

查看某个表的结构:desc 表名;

3、删除表

drop table 表名

4、添加一列

alter table 表名 add 列名 类型;

alter table t_user add age int;

5、修改列的类型

alter table 表名 modify 列名 类型;

alter table t_user modify age varchar(20);

6、修改列的名称

alter table 表名 change 旧列名 新列名 数据类型;

alter table t_user change age location varchar(20);

7、删除某一列

alter table 表名 drop 列名;

alter table t_user drop location;

8、修改表名

rename table 旧表名 to 新名;

rename table t_user to t_root;

列的约束

1.非空约束(not null) 指定非空约束的列, 在插入记录时 必须包含值.

2.唯一约束(unique) 该列的内容在表中. 值是唯一的.

3.主键约束(primary key) 当想要把某一列的值,作为该列的唯一标示符时,可以指定主键约束

(包含 非空约束和唯一约束). 一个表中只能指定一个主键约束列。主键约束 , 可以理解为非空

+唯一.

注意: 并且一张表中只能有一个主键约束.

主键自增

注意:

  1. 前提某个表的主键是数字. 我们可以将该主键设置为自增.
  2. 使用主键自增可能会造成主键的断层。(删除后数值不减,比如删除4、5行,下次新增行从6开始)
  3. mysql,sqlserver,sqllite这三个数据库具有该功能.
  4. 主键自增只能给主键约束的列加。

自增就是 每次插入记录时不需要指定值,该字段自己维护自己的值。维护方式就是每次加1。

语法如id int primary key auto_increment,

表中数据的增删改(DML)

1、为表添加记录

insert into 表名[(列名1,列名2...)] values (值1,值2...);

  • 指定要插入哪些列

insert into t_user(name, email) values('tom','tom@163.com');

注意: 数据类型为字符串类型的,需要使用单引号包裹.

不指定插入哪些列,默认全部列,需要指定每一列的值。

insert into t_user values(null,'jerry','jerry@itcast.cn');id自增所以填写null。

`insert into t_user(name,email) values('汤姆','tom2@163.com');

2、修改一条记录

update 表名 set 列名1 = 值 , 列名2 = 值 ....[where 条件1,条件2...]

UPDATE t_user SET NAME='rose' WHERE id=7;表示将id为7的数据name改为rose。

如果不指定id则是将所有列name都更新为rose。

3、删除某一条记录

DELETE FROM 表名 [WHERE 条件];

DELETE FROM t_user WHERE NAME='God';把name是God的记录删除。

DELETE FROM t_user;删除整张表中所有记录。

使用truncate删除表中记录。

TRUNCATE TABLE employee;

DELETE 删除 和 TRUNCATE删除(了解) 两者有什么区别?

首先,这两种都是删除表中的记录。

不同的是:

  1. delete 是逐行标记删除. TRUNCATE 是将整张表包括表结构都移除,然后将表重新创建.
  2. delete DML语句。 TRUNCATE DDL语句。
  3. delete 删除的记录可以被恢复,TRUNCATE 不能恢复。
  4. delete 不释放空间,TRUNCATE 释放空间。
  5. TRUNCATE 会提交事务。

拿自增的id来说,delete后id不会归为1,而是延续被删除的id继续自增。truncate后id又像新表一样从1开始。

4、查询

语法:

 SELECT selection_list -- 要查询的列名称 FROM table_list -- 要查询的表名称 WHERE condition -- 行条件 GROUP BY grouping_columns -- 对结果分组 HAVING condition  -- 分组后的行条件 ORDER BY sorting_columns -- 对结果排序 LIMIT offset_start, row_count -- 结果限定
  • 查询所有行所有列
    select * from stu;

*号 是通配符.通配所有列. 上面语句与下面是一模一样的

select sid,sname,age,gender from stu;

谁的效率更高?下面的效率更高。*需要运算。可以忽略不计,用*简便。

  • 查询某一列

select sname from stu;只会查询sname这一行。

2.1 条件查询介绍

条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:

=、!=、<>、<、<=、>、>=;
BETWEEN…ANDIN(SET)/NOT IN(SET)
IS NULL/IS NOT NULL
---条件连接符
AND; &&
OR;  ||
NOT; !

查询例子

-- 查询性别为女,并且年龄小于50的记录 select * from stu where gender='female' and age<50;
-- 查询学号为S_1001,或者姓名为liSi的记录 select * from stu where sid='S_1001' or sname='liSi';
-- 数据库中,sql语句不区分大小写 ,但是数据区分大小写. -- 查询学号为S_1001,S_1002,S_1003的记录 select * from stu where sid='S_1001' or sid='S_1002' or sid='S_1003';
select * from stu where sid in('S_1001','S_1002','S_1003');
-- 查询学号不是S_1001,S_1002,S_1003的记录 select * from stu where not (sid='S_1001' or sid='S_1002' or sid='S_1003');
select * from stu where sid not in('S_1001','S_1002','S_1003');
-- 查询年龄为null的记录  select * from stu where age=null;
-- null的特性: null不等于null 所以判断时应如下写法: select * from stu where age is null;
-- 查询年龄在20到40之间的学生记录 select * from stu where age >= 20 and age <= 40;
select * from stu where age between 20 and 40;
-- 查询性别非男的学生记录 select * from stu where gender!= 'male';
select * from stu where not gender='male';
select * from stu where gender not in ('male');
-- 查询姓名不为null的学生记录 select * from stu where sname is not null;
select * from stu where not sname is null;

模糊查询

where 字段 like '表达式';

% 通配任意个字符.

_通配单个字符。

*通配任意多个字符。

-- 查询姓名由5个字母构成的学生记录 select * from stu where sname like '_____';
-- 查询姓名由5个字母构成,并且第5个字母为“i”的学生记录 select * from stu where sname like '____i';
-- 查询姓名以“z”开头的学生记录 -- 说明: "%"该通配符匹配任意长度的字符. select * from stu where sname like 'z%';
-- 查询姓名中第2个字母为“i”的学生记录 select * from stu where sname like '_i%';
-- 查询姓名中包含“a”字母的学生记录 select * from stu where sname like '%a%';

去除重复的记录

-- 关键词: distinct 去除重复查询结果记录. select gender from stu; -- 出现大量重复的记录 select distinct gender from stu; -- 去除重复的记录

查看雇员的月薪与佣金之和

select sal*12+comm from emp;

null与任何数字计算结果都是null.上面的写法是错误的.

使用IFNULL(参数1,参数2) 函数解决. 判断参数1的值是否为null,如果为null返回参数2的值.

select sal*12 + IFNULL(comm,0) from emp;

这个函数在所有数据库通用吗?不通用。

给列名添加别名

SELECT ename '姓名' FROM emp; -- 把ename重命名为姓名 select sal*12 + IFNULL(comm,0) as '年收入' from emp;
select sal*12 + IFNULL(comm,0)  '年收入' from emp;
select sal*12 + IFNULL(comm,0)  年收入 from emp;

按顺序查询

-- 查询所有学生记录,按年龄升序排序 -- asc: 升序 -- desc:降序 select * from stu order by age asc;
-- 默认就是升序 select * from stu order by age;
-- 查询所有学生记录,按年龄降序排序 select * from stu order by age desc;
-- 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序。前面一个优先,后面一个仅仅是前面的相同时才会比较 select * from emp order by sal desc , empno asc;

聚合函数
聚合函数是用来做纵向运算的函数:

COUNT():统计指定列不为NULL的记录行数;
MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0
-- COUNT -- 当需要纵向统计时可以使用COUNT()。 -- 查询emp表中记录数: select count(*) from emp;
-- 查询emp表中有佣金的人数: select count(*) from emp where comm is not null and comm >0;
-- 查询emp表中月薪大于2500的人数: select count(*) from emp where sal > 2500;
-- 统计月薪与佣金之和大于2500元的人数: select count(*) from emp where sal+IFNULL(comm,0) > 2500;
-- 查询有佣金的人数并且有领导的人数: select count(*) from emp where comm > 0 and  mgr is not null;
-- SUM(计算总和)和AVG(计算平均值) -- 当需要纵向求和时使用sum()函数。 -- 查询所有雇员月薪和: select sum(sal) from emp;
-- 查询所有雇员月薪和,以及所有雇员佣金和: select sum(sal),sum(comm) from emp;
-- 查询所有雇员月薪+佣金和: select sum(sal+IFNULL(comm,0)) from emp;
-- 统计所有员工平均工资: select avg(sal) from emp;
-- MAX和MIN -- 查询最高工资和最低工资: select max(sal),min(sal) from emp;

分组查询

当需要分组查询时需要使用GROUP BY子句,例如查询每个部门的工资和,这说明要使用部分来分组。

-- 查询每个部门的部门编号和每个部门的工资和: select deptno,sum(sal) from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门的人数: select deptno,count(ename) from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数: select deptno,count(ename) from emp where sal>1500 group by deptno ;   
-- HAVING子句 -- 查询工资总和大于9000的部门编号以及工资和: select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
-- 使用having在分组之后加条件. -- where和having都可以加条件? -- where在分组之前加条件. -- having在分组之后加条件. -- where的效率要远远高于having. 分组本身消耗资源非常大.

LIMIT
LIMIT用来限定查询结果的起始行,以及总行数。

第一个是起始行,第二个是查几条。

-- 查询5行记录,起始行从0开始 select * from emp limit 0,5;
-- 查询10行记录,起始行从3开始 select * from emp limit 3,10;

如果一页记录为5条,希望查看第3页记录应该怎么查呢?

-- 第一页记录起始行为0,一共查询5行; select * from emp limit 0,5;
-- 第二页记录起始行为5,一共查询5行; select * from emp limit 5,5;
-- 第三页记录起始行为10,一共查询5行; select * from emp limit 10,5;

by @sunhaiyu

2017.3.11

注:本文转载自http://www.cnblogs.com/sun-haiyu/p/7026984.html,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责。如有侵权行为,请联系我们,我们会及时删除。
上一篇 下一篇

评论



最新评论

daliangzao_qq_com: 1112 查看原文 12月03日 10:57
PHP笔记: ypengchao@126.com 查看原文 06月26日 11:38
PHP笔记: 用数据库 查看原文 06月26日 11:37
网站/shl设计: 如果关闭浏览器了,session就没有了呀。再打开浏览器不就可以重新登陆了。 查看原文 05月28日 15:26
网站/shl设计: 站长怎么联系你有给问题请教。 查看原文 05月28日 15:25

分享

扫一扫,快速分享到微信

赞助商