MySQL入门知识总结

1. SQL—数据库操作

1.1 创建数据库

• 语法: create database 数据库名称 character set 字符集 collate 字符集校对规则

• 例子一:默认创建数据库

  create database db1;
  

• 例子二:给创建的数据库选择字符集,校对规则使用指定字符集默认的规则

  create database db1 character set utf8;
  

• 例子三:给创建的数据库选择字符集,并指定字符集的校对规则

  create database db1 character set utf8 collate utf8_bin;
  

1.2 查看数据库

• 语法:查看数据库服务器中所有的数据库

  show databases;
  

• 语法:查看服务器中指定的数据库

  show create databases 数据库名称;
  

1.3 修改数据库

• 语法:alter database 数据库名称 character set 字符集 collate 字符集校对规则;

  • 例子一:修改数据库的字符集,校对规则使用数据库默认的规则

  alter database db1 character set utf8;
  

  • 例子二:修改数据库的字符集和字符集校对规则

  alter database db2 character set utf8 collate utf8_bin;
  

• 使用方法和创建数据库有些类似

1.4 删除数据库

• 语法:drop database 数据库名称;

  drop database db1;
  

• 到工作单位千万不要随便使用这个SQL语句!

1.5 其他常用的数据库操作

• 切换数据库

  • 语法:use 数据库名称;

    use db1;
    

• 查看当前正在使用的数据库

  • 语法select database();

    select database();
    

2. SQL—数据库表操作

2.1 创建表

2.1.1 语法

create table 表名称(字段名称 字段类型(长度) 约束 , 字段名称 字段类型(长度 约束...));

• 列:字段
• 行:记录

2.1.2 字段类型

• 一个实体对应一个表,一个实体属性对应表的一个字段

• Java与MySQL中的类型对应关系

  • byte = tinyint

  • short = smallint

  • int = int

  • long = bigint

  • float = float

  • double = double

  • boolean = bit

  • char/String = char和varchar

  • Date = date/time/datetime/timestamp

  • File = BLOB/TEXT(BLOB一般为图片/MP3等 , TEXT为文本文件)

• MySQL中char和varchar的区别:

  • char代表是固定长度的字符或者字符串

    • 例子:定义char(8),向这个字段存入字符串,如果少于8个字符,空缺位置数据库会使用空格将其补全

  • varchar代表的是可变长度的字符串

    • 例子:定义varchar(8),向这个字段传入字符串,就按实际的字符个数存入数据库

• MySQL中datetime和timestamp区别

  • datetime:既有日期又有时间的日期类型,如果没有向这个字段中存入相应的值,数据库会使用null存入到数据库的该字段中(例子:生日)
  • timestamp:也是基友日期又有时间的日期类型,如果没有向这个字段中存入相应的值,数据库会使用当前系统时间存入到数据库的该字段中(例子:注册账号等)

2.1.3 约束

• 约束的作用: 保证数据的完整性
• 单表约束分类
  • 主键约束: primary key (主键约束默认就是唯一的,非空的)
  • 唯一约束: unique
  • 非空约束: not null

2.1.4 实例样板

//首先得先创建一个数据库
create database web_test1;
//在使用想要创建表的数据库
use web_test1;
//创建表
create table user(
	id int primary key auto increment,
	username varchar(20) unique,
	password varchar(20) not null,
	age int,
	birthday datetime
);

2.2 查看表

• 查看某个数据库下的所有的表

  • 语法: show tables;

    show tables;
    

• 查看某个表的结构信息

  • 语法: desc 表名称;

    desc user;
    

2.3 删除表

• 语法: drop table 表名称;

  drop table user;
  

• 删除表与删除数据库类似

2.4 修改表

• 添加列: alter table 表名称 add 列名称 类型(长度) 约束;

  alter table user add image varchar(100);
  

• 修改列类型,长度和约束: alter table 表名称 modify 列名称 类型(长度) 约束;

  alter table user modify image varchar(150);
  

• 删除列: alter table 表名称 drop 列名

  alter table user drop age;
  

• 修改列名称: alter table 表名称 change 旧列名称 新列名称 类型(长度) 约束;

  alter table user change image pic varchar(150);
  

• 修改表名: rename table 旧表名称 to 新表名称;

  rename table user to employee;
  

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

  alter table employee character set gbk;
  

3.SQL—数据库表记录操作(重点)

3.1 添加表记录

3.1.1 语法

• 向表中插入某些列记录: insert into 表名 (列名1,列名2,列名3…) values (值1,值2,值3…);

  insert into user (id,username,password) values (null,'aaa','123');
  

• 向表中插入所有列记录: insert into 表名 values (值1,值2,值3…);

  insert into user values (null,'bbb','123',25,'1994-10-18');
  

• 注意事项:

  • 值的顺序与数据库中表列的顺序要一致

  • 值的类型与数据库中表列的类型要一致

  • 值的最大长度不能超过列设置的最大长度

  • 值的类型是字符串或者是日期类型,使用单引号引起来(如:‘aaa’)

3.1.2添加中文记录出错如何解决

• 出错原因:由于数据库的字符集都是utf8,而我们命令提示符默认是gbk格式,所以在中文解码的过程中会出来现中文乱码

  • 使用: show variables like ‘%character%’; --可以查看数据库中与字符集相关的参数

    show variables like '%character%';
    

• 解决办法:需要将MySQL数据库服务器中的客户端部分字符集改为gbk

  • 首先我们需要到MySQL的安装目录下找到my.ini文件

    • 一般默认路径为C:\Program Files (x86)\MySQL\MySQL Server 5.5
      

  • 由于现在Windows有权限管控,所以不能直接进行修改,需要将my.ini文件复制一份到桌面,使用编辑器(如:Notepad++)打开

  • 修改文件中 [client] 下的字符集

    • 修改前
      

    • 修改后

      

  • 修改完毕后覆盖到原路径下的my.ini

  • 重新启动MySQL服务器

    • “我的电脑"右键"管理”—>“服务与应用”—>“服务”—>"MySQL"右键"停止"关闭后在启动即可
      

• 修改后的效果
  

3.2 修改表记录

3.2.1 语法

update 表名 set 列名=值,列名=值... where 条件

• 修改某一列的所有值 : update 表名 set 列名=值 ;

  update user set password='abc';
  

  

• 按条件修改值: update 表名 set 列名=值 where 条件;

  update user set password='xyz' where username='bbb';
  

  

• 按条件修改多个列的值:update 表名 set 列名=值,列名=值 where 条件;

  update user set password='123',age=34 where username='aaa';
  

  

3.2.2 注意事项

• 值的类型要与列的类型保持一致
• 值的最大长度不能超过列设置的最大长度
• 字符串类型和日期类型要添加单引号

3.3 删除表

3.3.1 语法

delete from 表名 where 条件;

• 删除某一条记录

  delete from user where id=2;
  

  

• 删除表中所有记录

  delete from user;
  

  • (拓展) 删除表中所有记录有两种做法

    • delete frin 表名;

      • 删除所有的记录,属于DML语句,一条记录一条记录删除

      • 事务可以作用在DML语句

    • truncate table 表名;

      • 删除所有的记录,属于DDL语句,将表删除后在重新创建一个结构一样的表
      • 事务不能控制DDL语句

3.3.2 注意事项

• 删除表的记录,指的是删除表中的一行记录
• 删除如果没有条件,默认是删除表中的所有的记录

3.4 查看表记录 (重点)

3.4.1 基本查询

• 语法: select [distinct] *或者列名 from 表名 [条件];

  • 查询所有信息: select * from 表名;

    select * from exam;
    

    

  • 查询所有指定的信息: select 列名,列名… from 表名;

    select name,english from exam;
    

    

  • 查询指定信息并且不显示重复的值: select distinct 列名 from 表名;

    select distinct english from exam;
    

    

  • 结合运算的查询例子

    select name,english+chinese+math from exam;
    

    

  • 别名查询:select 列名 as 别名 from 表名; (as 可以省略不写)

    select name,english+chinese+math as sum from exam;
    

    

3.4.2 条件查询

• 在基本语法上使用where语句

• 常用的条件

  • > , < , >= , <= , <> ,=
  • like:模糊查询
  • in:范围查询
  • 条件关联:and , or , not

• 查询指定列记录的信息:select * from 表名 where 列名=值;

  select * from exam where name = '李四';
  

  

• 查询指定列记录的信息并包含条件:select * from 表名 where 列名=值 and 条件

  select * from exam where name='李四' and english > 90;
  

  - 模糊查询:例子 (like子句中 _只能代表一个字符,%可以代表任意个字符)

  • like ‘李_’ [名字中必须是两个字]

  • like ‘李%’ [名字中所有姓李的]

  • like ‘%四’ [名字中以四结尾的]

  • like ‘%王%’ [只要名称中包含这个字就符合条件]

    select * from exam where name like '李%';
    

    

• 查找列中指定值记录的所有信息:select * from 表名 where 列名 in (值1,值2.值3…);

  select * from exam where english in (69,75,89);
  

  

3.4.3 排序查找

• 使用 order by 列名 asc(升序)/desc(降序);

  • 查询信息,并指定列进行升序排序 (默认是asc)

    select * from exam order by chinese;
    

    

  • 查询信息,并指定列进行降序排序

    select * from exam order by chinese desc;
    

    

  • 先按某一列倒序,如果有重复值再按照另外一列进行升序

    select * from exam order by chinese desc , english asc;
    

    

  • 带条件的排序查询

    select * from exam where name like '李%' order by english desc;
    

    

3.4.4 分组统计查询

3.4.4.1 聚合函数的使用 sum() , count() , max() , min() , avg()

• sum()用法—实例

  统计一列值的总和

  select sum(english) from exam;
  

  

  分别统计两列值的总和

  select sum(english),sum(math) from exam;
  

  

  统计指定规则列值的总和

  select sum(english) from exam where name like '李%';
  

  

  统计多列总和的总和值方法一:

  select sum(english)+sum(chinese)+sum(math) from exam;
  

  

  统计多列总和的总和值方法二:

  select sum(ifnull(english,0)+ifnull(chinese,0)+ifnull(math,0)) from exam;
  

  • 注意:该语句与上面语句的不同之处

    • 上面的语句是按照列的方式统计,英语成绩总和+语文成绩总和+数学成绩总和

    • 下面的语句是先计算英语+数学+语文再求和

      • 所有下面的语句为了数据的准确一般会加上 ifnull() 防止有空值导致总和值不正确

• count() 用法—实例

  统计总个数

  select count(*) from exam;
  

  

  按照规则统计总个数

  select count(*) from exam where name like '李%';
  

  

​

• max() min()用法—实例

  select max(math) from exam;
  select min(chinese) from exam;
  

  

• avg() 用法—实例

  select avg(chinese) from exam;
  

  

3.4.4.2 分组查询

• 语法: 使用 group by 字段名称;

• 使用案例

  按商品名称统计，每类商品所购买的个数

  select product,count(*) from orderitem group by product;
  

  

  按商品名称统计，每类商品所花费的总金额

  select product,sum(price) from orderitem group by product;
  

  

  按商品名称统计，统计每类商品花费的总金额在5000元以上的商品

  select product,sum(price) from orderitem group by product having sum(price) > 5000;
  

  

  按商品名称统计，统计每类商品花费的总金额在5000元以上的商品，并且按照总金额升序排序

  select product,sum(price) from orderitem group by product having sum(price) > 5000 order by sum(price) asc;
  

  

3.4.5 查询表总结

• 查询语句 : S(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);
  • 可以没有但是顺序不能改变