MySQL入门

• mysql的数据类型

  • 可以将mysql与java中类比，但是mysql中的数据类型有一些区别

  • 日期时间类型：year, date, datetime, timestamp 类似于Java里的java.sql.Date类

  • 浮点型系列：float,double

  double(M,D)：表示最长为M位，其中小数点后D位

  例如：double(5,2)表示的数据范围[-999.99,999.99]，如果超过这个范围会报错。

  • 定点型系列：decimal

  decimal(M,D)：表示最长为M位，其中小数点后D位

  • 字符串类型：char,varchar(M),text

  char如果没有指定宽度，默认为1个字符

  varchar(M)，必须指定宽度

MySQL	JAVA	长度	例子
TINYINT	byte	1byte有符号整数	2
SMALINT	short	2byte有符号整数	20
INT	int	4byte有符号整数	20
BIGINT	long	8byte有符号整数	20
无	boolean	布尔类型，true或者false	TRUE FALSE
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
VARCHAR	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP		时间类型
BINARY		字节数组

• 库的DDL

  • 创建库(C)

    创建数据库：

    create database 数据库名称;

    创建数据库，判断不存在，再创建：

    create database if not exists 数据库名称;(增强代码的健壮性)

    创建数据库，并指定字符集

    create database 数据库名称 character set 字符集名;

    练习： 创建db4数据库，判断是否存在，并制定字符集为gbk

    create database if not exists db4 character set gbk;

  • 查询库(R)

    • 查询所有数据库的名称:

      show databases;

    • 查询某个数据库的字符集:

      show create database 数据库名称;

  • 修改库(U) 不能修改数据库名

    • 修改数据库的字符集:

      alter database 数据库名称 character set 字符集名称;

  • 删除库(D)

    • 删除数据库

      drop database 数据库名称;

    • 判断数据库存在,存在再删除

      drop database if exists 数据库名称;

  • 使用库

    • 查询当前正在使用的数据库名称

    select database();

    • 使用数据库

    use 数据库名称;

• 表的DDL

  • 创建表(C)

    create table 表名(

    列名1 数据类型1,

    列名2 数据类型2,

    ....

    列名n 数据类型n

    );

    除了timestamp以外的数据类型默认值都为null,字符串varchar要指定长度

    复制表： create table 表名 like 被复制的表名;

  • 查询表(R)

    • 查询某个数据库中所有的表名称 show tables;

    • 查询表结构 desc 表名;

    • 查看表的字符集 show create table 表名;

  • 修改表(U)

    • 修改表名 alter table 表名 rename to 新的表名;

    • 修改表的字符集 alter table 表名 character set 字符集名称;

    • 添加一列 alter table 表名 add 列名 数据类型;

    • 修改列名称 类型

    alter table 表名 change 列名 新列名 新数据类型; -- 修改列名和数据类型

    alter table 表名 modify 列名 新数据类型; -- 只修改数据类型

    • 删除列 alter table 表名 drop 列名;

  • 删除表(D)

    • drop table 表名;

    • drop table if exists 表名 ;

• DML增删改表中数据

  • 添加数据 insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

    注意:添加的数据类型需要一一对应

    如果表名后，不定义列名，则默认给所有列添加值

  • 删除数据 delete from 表名 [where 条件]

    若要删除所有数据,建议使用 truncate table 表名;(效率更高)

  • 修改数据 update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件]; (记得加条件)

• DQL查询表中的数据

  • 基础查询: 在where前

    • 查询常量会自动创建一列常量

    • 去除重复： distinct

      select distinct 字段名1，字段名2... from 表名；

    • +号的作用

      • select 100+90; 两个操作数都为数值型,则做加法运算

      • select '123'+90; 213 其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功则做加法运算,转换失败则该字符型的值为0

      • null与任何数据类型相作用都为null

  • 条件查询:(在where后)

    • 条件运算符: > < = != <> <= >=

    • 逻辑运算符: && || ！ and or not

    • 模糊查询:

      • like 找出所有与条件匹配的值. 一般和通配符一起使用

        %:表示任意匹配字符（个数不限）

        _:代表一个任意字符(只占一个位置)

      • between and 在...与...之间的值

      • in 判断某字段的值是否属于in列表中的某一项(为了简洁,但不支持通配符)

      • is (not) null 判断是否为(非)空

  • 排序查询:(在条件查询后)

    • order by 排序列表 [asc|desc]

      • desc 降序,asc 升序,默认升序

  • 分组查询

    • group by 分组列表

      • 分组之后查询的字段：分组字段、聚合函数

    • where 和 having 的区别？

      • where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来

      • where 后不可以跟聚合函数，having可以进行聚合函数的判断。

  • 聚合函数

    • count：计算个(行数)数

      • 一般选择非空的列：主键

      • count(*)

        select count(math) from student;

    • max：计算最大值

      select max(math) from student;

    • min：计算最小值

      select min(math) from student;

    • sum：计算和

      select max(math) from student;

    • avg：计算平均值

      select avg(math) from student;

    • 注意:

      1. 可以配合distinct使用 select count(distinct math),count(math) from student;

      2. 和分组函数一同查询的字段有限制,一般来说是group by之后的字段

  • 分页查询

    • limit 分页限定

      • 显示数学成绩前3的学生信息

        select * from student order by math limit 0,3;

  • 常见函数:(只能用在where之前,或者子查询中)

    • 单行函数

      • 字符函数

        • length(参数) 获取参数值字节数(随字符集而定)

        • concat(参数1,参数2,...) 字符串拼接

        • upper（转大写） ,lower（转小写）

        • substr('字符串',start, length) 截取字符串 在sql中索引从1开始

        • instr(字符串,子字符串) (子串在大字符串里的第一次的出现起始索引，如果没有返回0)

        • trim(要去掉的字符串,作用对象字符串) 前后去字符

        • lpad，rpad('nihao',10,'a')（向左或者向右补全）在'nihao'右边补5个a

        • replace (作用对象,被替换的对象,替换后的对象)

      • 数学函数

        • round () 四舍五入

        • ceil () 向上取整 floor() 向下取整

  • 多表连接

    • 内连接

      • 显式内连接

        语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件

      • 隐式内连接: 使用where条件消除无用数据

        SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

      • 注意:

        1. 表的顺序是按照中间匹配的字段顺序进行排序

    • 外连接

      • 左外连接 select 字段列表 from 表1 left [outer] join 表2 on 条件

        查询的是左表所有数据以及其交集部分。

      • 右外连接 select 字段列表 from 表1 right [outer] join 表2 on 条件

        查询的是右表所有数据以及其交集部分。

      • 注意:

        1. 连接表的顺序join谁就按照谁的顺序排列

  • 执行顺序

    • from > where > group by > 分组函数> having >select>order by>limit

  • 语法:

    • select 字段列表 from 表名列表 [where 条件列表] [group by 分组字段]

      [having 分组之后的条件] [order by 排序] [limit 分页限定]