MySQL数据库学习I（基础）_创建一个数据库mydb1,字符集utf8的语句

MySQL数据库

数据库就是一个文件系统，访问数据的时候需要通过标准的SQL语言来完成。
关系型的数据
保存的实体与实体之间的关系。（用户、商品、订单）
常见的数据库
Oracle 公司Oracle（甲骨文）数据产品，收费的大型的数据库。
MySQL 开源的，被Oracle收购了，小型的数据库。5.x版本免费，6.x收费了。
SQLServer 微软的，收费的中型的数据库。
DB2 IBM公司收费的大型的数据库。
SyBASE PowerDigener软件（非常牛）

MySQL访问：

cmd > 输入命令 mysql -u root -p 回车
1

1.停止mysql服务:
services.msc 进入到服务界面
2.在cmd>输入一个命令:
mysqld --skip-grant-tables (开启一个mysql服务,不需要进行认证.)
3.新打开一个cmd窗口
mysql -u root -p 不需要输入密码.就可以进入.
4.输入命令 show databases;查看数据库，输入命令 use mysql;使用mysql数据库。
5.修改密码的语句:
update user set password=password(‘root’) WHERE user=‘root’;
6.将两个窗口都关闭.
7.任务管理器中结束（mysqld）进程.
8.重启mysql服务
输入net start mysql 回车即可启动，输入net stop mysql 回车即可关闭。
方法二：选择计算机（我的电脑）右击鼠标，选择管理，在选择系统和应用服务下面的服务。找到MySQL，右击选择启动或停止。
SQLyog 连不上MySQL , ERROR NO.1045 简单的解决方法

1。停止MySQL服务：运行net stop mysql

2。修改MySQL源文件夹中的my.ini,运用查找功能找到【mysqld】，在其下加上一行 skip-grant-tables，保存。

3。启动MySQL服务：运行net start mysql

4。ok了
1
2
3
4
5
6
7

MySQL数据库服务器、数据库和表关系

    所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。

    为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
1
2
3

简单总结：

一个数据库的服务器中有多个数据库，一个数据库中有多个表，
每个表有多个字段。字段和Java中类的属性是对应的。
每一条记录对应是一个Java实例对象。
1
2
3

SQL的简介

Structured Query Language, 结构化查询语言
非过程性的语言
过程性的语言：我下一条语句，需要依赖上一条或者上几条语句。 Oracle开发PL/SQL，只能在Oracle使用 * SQL Server、Sybase的T-SQL
非过程性的语言：写一条语句，就会执行一个结果。
SQL语言分类

DDL（数据定义语言）

    •数据定义语言 - Data Definition Language

    •用来定义数据库的对象，如数据表、视图、索引等
1
2
3

DML （数据操纵语言）

    •数据处理语言 - Data Manipulation Language

    •在数据库表中更新，增加和删除记录

    •如 update， insert， delete
1
2
3
4
5

DCL（数据控制语言）

    •数据控制语言 – Data Control Language

    •指用于设置用户权限和控制事务语句

    •如grant，revoke，if…else，while，begintransaction
1
2
3
4
5

DQL（数据查询语言）

    •数据查询语言 – Data Query Language

     select 、
1
2
3

数据库（CURD – 增删改查）
创建数据库

    * 语法：create database 数据名称; 创建一个数据了。
    * create database 数据库名称 character set 编码 collate 校对规则;
    * 校对规则：和编码是成对出现的。
    * 练习
            创建一个名称为mydb1的数据库。
                    create database mydb1;
            创建一个使用utf8字符集的mydb2数据库。
                    create database mydb2 character set 'utf8';
            创建一个使用utf8字符集，并带校对规则的mydb3数据库。
                    create database mydb3 character set 'utf8' collate 'utf8_bin';
1
2
3
4
5
6
7
8
9
10

查看数据库 show databases;

• 查询数据库的定义信息 show create database 数据库;
  show create database mydb2;
  删除数据库 drop database 数据库名称;

    练习
    查看当前数据库服务器中的所有数据库
            show databases;
    查看前面创建的mydb2数据库的定义信息
            show create database mydb2;
    删除前面创建的mydb1数据库
            drop database mydb1;
  1
  2
  3
  4
  5
  6
  7

修改数据库

    * 语法：alter database 数据库 character set 编码 collate 校对规则;
    * 练习：查看服务器中的数据库，并把其中某一个库的字符集修改为gbk
      alter database mydb2 character set 'gbk';
1
2
3

其他的操作

• 切换数据库 ： use db_name;
• 查看当前使用的数据库 select database();
  表（table）（CURD – 增删改查）

语法：
create table 表名(
字段1 类型(长度) 约束,
字段2 类型(长度) 约束,
字段3 类型(长度) 约束,
字段4 类型(长度) 约束
);
注意：

        * 表名小括号，后面要有分号。
        * 每一行字段后面要有逗号，但是最后一行没有逗号。
        * 数据的类型后面有长度，如果是字符串类型，长度必须加。如果其他类型可以不加。默认长度。int 默认长度11
        public class User{
                int id;
                String name;
                String pass;
                String eamil;
                String nikename;
        }
1
2
3
4
5
6
7
8
9
10

数据的类型

字符串型
VARCHAR、CHAR
* varchar和char区别：
* varchar（经常使用） 长度是可变的。 name varchar(8) 存入数据hello，但是如果存入helloworld报错了。
* char 长度不可变的。 name char(8) 存入的数据hello，如果不够用空格补全。 * 效率高：char效果。
大数据类型（一般不用）
BLOB、TEXT
BLOB：二进制文件
TEXT：字符
数值型
TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE
逻辑性 对应boolean
BIT
日期型
DATE、TIME、DATETIME、TIMESTAMP
* date 只包含日期
* time 只包含时分秒
* datetime和timestamp包含日期和时分秒区别：
* datetime需要手动录入时间。
* timestamp不传入数据，默认选择当前系统时间。

• 练习，创建表的练习

create table employee(
    id int,
    name varchar(20),
    gender varchar(10),
    birthday date,
    entry_date date,
    job varchar(100),
    salary double,
    resume text
);
1
2
3
4
5
6
7
8
9
10

创建数据表时，只有字符串类型必须写长度，而其他类型都有默认长度。
MySQL常用数据类型

java中 String char ----- mysql 中字符串型 char varchar

    *char是定长 varchar是变长
1

例如：char(8)保存 lisi，因为lisi 只有四个字符，所有会补充四个空格，成为8个字符存入 char(8)中 ，如果有 varchar(8) 自动根据存放内容改变长度

java中 byte short int long float double ----- mysql 中数值类型 TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE

java中 boolean ---- mysql 逻辑性 bit 存放一位数值 0 或者1

java中 Date ----- mysql 日期类型 date (只有日期) time(只有时间) datetime(日期时间都有)timestamp(日期时间都有 )
1
2
3
4
5

*datetime和timestamp 表现形式上完全相同，区别就在于timestamp 在数据库可以自定更新（当前时间）

java中大数据类型 inputStream 二进制文件 Reader 文本文件 ------- mysql 大数据类型 blob(存放大二进制数据)text(存放大的文本文件)

*tinyblob tinytext 255字节 blob text 64KB mediumblob mediumtext 16MB longblob longtext 4GB

约束（单表）

作用：约束用来保证数据有效性和完整性。

• 主键约束
  * 标识标记该条记录。 通过pramary key声明主键。（默认唯一、非空）

• auto_increment 数据库维护主键。自动增长。

• 唯一约束
  * 值是唯一的。使用unique声明

• 非空约束
  * 值不能为空 not null

• 创建新的标签employee2，把约束加上。

  create table employee2(
  id int primary key auto_increment,
  name varchar(20) unique not null,
  gender varchar(10) not null,
  birthday date not null,
  entry_date date not null,
  job varchar(100) not null,
  salary double not null,
  resume text not null
  );

• 使用desc 表名; 查看表的信息

• show tables ; 查看当前库内所有表名

• show create table 表名; 查看建表语句和字符集

  主键约束primary key : 信息记录某个字段可以唯一区分其他信息记录，这个字段就可以是主键（唯一非空）

  唯一约束unique : 该字段的值不允许重复

  一张表中可以有很多个唯一约束，只能有一个(两个)作为主键约束

  非空约束 not null ：该字段的值不能为空

• 删除表
  drop table employee2;

• 修改表
  alter table 表名 add 字段 类型(长度) 约束; – 添加字段
  alter table 表名 drop 字段; – 删除字段
  alter table 表名 modify 字段 类型(长度) 约束; – 修改类型或者约束
  alter table 表名 change 旧字段 新字段 类型(长度) 约束 – 修改字段的名称
  rename table 表名 to 新表名; – 修改表名
  alter table 表名 character set utf8; – 修改字符集
  练习
  在上面员工表的基本上增加一个image列。
  alter table employee add image varchar(20);
  修改job列，使其长度为60。
  alter table employee modify job varchar(60);
  删除gender列。
  alter table employee drop gender;
  表名改为user。
  rename table employee to user;
  修改表的字符集为utf8
  alter table user character set utf8;
  列名name修改为username
  alter table user change name username varchar(30);
  数据（CURD – 增删改查）(******)

添加数据
* insert into 表名 (字段1,字段2,字段3…) values(值1,值2,值3…); 有几列就插入多少的值。
* insert into 表名 values(值1,值2,值3…); 插入所有的列
注意：

       数据与字段的类型相同。
       字段长度需要控制。
       字符串或者日期类型需要使用''
       在values中列出的数据位置必须与被加入的列的排列位置相对应。
       字符和日期型数据应包含在单引号中。
       插入空值：不指定或insert into table values(null)
1
2
3
4
5
6

• 向user表中插入数据

  insert into user values (1,‘xiaofeng’,‘1994-10-10’,‘2011-1-1’,‘HR’,19000,‘aaa’,‘abc’);
  insert into user values (2,‘美美’,‘1994-10-10’,‘2011-1-1’,‘HR’,19000,‘aaa’,‘abc’);
  insert into user values (3,‘小风’,‘1994-10-10’,‘2011-1-1’,‘WORKER’,21000,‘aaa’,‘abc’);
  insert into user values (4,‘芙蓉’,‘1994-10-10’,‘2011-1-1’,‘HR’,1000,‘aaa’,‘abc’);
  insert into user values (5,‘班长’,‘1994-10-10’,‘2011-1-1’,‘HR’,100,‘aaa’,‘abc’);

解决中文乱码的问题

[client]
port=3306
[mysql]
default-character-set=gbk
修改完需要重新启动服务。

mysql有六处使用了字符集，分别为：client、connection、database、results、server 、system。

    client是客户端使用的字符集。

    connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。      

    database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。   

    results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。      

    server是服务器安装时指定的默认字符集设定。      

    system是数据库系统使用的字符集设定。（utf-8不可修改）
1
2
3
4
5
6
7
8
9
10
11

通过修改my.ini 修改字符集编码，重启MySQL的服务
修改语句

    * 语法： update 表名 set 字段=值,字段=值... [where ]
            * 如果没有where条件，默认更新所有的记录。
            * 有where提交，选择某一条记录。
1
2
3

将所有员工薪水修改为5000元。
update user set salary=5000;
将姓名为’班长’的员工薪水修改为3000元。
update user set salary=3000 where username=‘班长’;
将姓名为’美美’的员工薪水修改为4000元,job改为BOSS。
update user set salary=4000,job=‘BOSS’ where username=‘美美’;
将班长的薪水在原有基础上增加1000元。
update user set salary = salary+1000 where username=‘班长’;
删除数据 delete

语法：delete from 表名 [where ]; 删除数据
truncate 表名; 删除所有的数据
1
2

truncate 和 delete的区别：

truncate删除数据，先删除整个表。再创建一个新的空的表。（效率）

delete删除数据，一条一条删除的。（*****）
1
2
3

事物（insert update delete）
删除表中名称为’班长’的记录。
delete from user where username=‘班长’;
删除表中所有记录。
delete from user;
使用truncate删除表中记录。
truncate user;

Delete语句不能删除某一列的值（可使用update）
使用delete语句仅删除记录，不删除表本身。如要删除表，使用droptable语句。

同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

外键约束

如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。外键是用来实现参照完整性的。

rollback 回滚的意思。 就是数据库里做修改后 （ update ,insert , delete）未commit 之前 使用rollback 可以恢复数据到修改之前。

查询语句

    语法： select * from 表名; 查询所有（字段）
1

select 字段名1,字段名2,字段名3 from 表名; 显示查询字段名
select DISTINCT 字段名 from 表名; 去除重复的数据。
查询表中所有学生的信息。
select * from stu;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from stu;

过滤表中重复数据。（面试题）
select distinct english from stu;

select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

from指定查询哪张表。
DISTINCT，指显示结果时，是否剔除重复数据

• 查询的列可以运算
• 可以使用别名：使用as 别名 并且as可以省略。
  练习：
  在所有学生分数上加10分特长分。
  select name,math+10,english+10,chinese+10 from stu;
  统计每个学生的总分。
  select name,math+english+chinese from stu;
  使用别名表示学生分数。
  select name,(math+english+chinese) as sum from stu;
• 使用where条件过滤
  查询姓名为班长的学生成绩
  select * from stu where name=‘班长’;
  查询英语成绩大于90分的同学
  select name,english from stu where english < 15;
  查询总分大于200分的所有同学
  select name,math+english+chinese from stu where (math+english+chinese) > 200;

常用的符号

< <= >= = <>（不等于）
in(范围内取内容)
like – 模糊查询 写法：like ‘张_或者%’; _和%区别：占位符。_只一个，%可以有多个
%的写法 like ‘%张’; 结果XXX张
like ‘张%’; 结果张XXX
like ‘%张%’; 只要有张就行
is null – 判断是否为null
and – 并且
or – 或者
not – 不成立

• 练习
  查询英语分数在 80－90之间的同学。
  select * from stu where english >80 and english <90;
  select * from stu where english between 80 and 90;
  查询数学分数为18,78,46的同学。（in）
  select * from stu where math in(18,78,46);
  查询所有姓班的学生成绩。
  select * from stu where name like ‘班%’;
  查询数学分>80，语文分>80的同学。
  select * from stu where math >80 or chinese > 80;

查询语文没有成绩学员select * from exam where chinese is null;

查询语文有成绩学员select * from exam where chinese is not null;

• 排序 使用order by 升序默认的(asc)/降序(desc)
• 出现select的语句末尾。
  练习
  对数学成绩排序后输出。
  select name,math from stu order by math;
  对总分排序按从高到低的顺序输出
  select name,math+english+chinese from stu order by (math+english+chinese) desc;
  对学生成绩按照英语进行降序排序，英语相同学员按照数学降序
  select * from stu order by english desc,math desc;
  对姓美的学生成绩排序输出
  select * from stu where name like ‘美%’ order by english desc;
  聚集函数

count 获取数量
练习：
统计一个班级共有多少学生？
select count() from stu;
统计数学成绩大于90的学生有多少个？
select count() from stu where math > 90;
统计总分大于150的人数有多少？
select count(*) from stu where (math+english+chinese) > 150;

• sum 求和（忽略null值） 可以同ifnull(xxx,0)
  统计一个班级数学总成绩？
  select sum(math) from stu;
  统计一个班级语文、英语、数学各科的总成绩
  select sum(math),sum(english),sum(chinese) from stu;
  统计一个班级语文、英语、数学的成绩总和
  select sum(ifnull(math,0)+english+chinese) from stu;
  select sum(math)+sum(english)+sum(chinese) from stu;
  统计一个班级语文成绩平均分

    select sum(chinese) / count(*) from stu;
  1

  注意：sum仅对数值起作用，否则会报错。

  注意：对多列求和，“，”号不能少。

ifnull(xxx,0)如果为null，则值是0

avg 平均数
练习：
求一个班级数学平均分？
select avg(math) from stu;
求一个班级总分平均分
select avg(ifnull(math,0)+english+chinese) from stu;

• max 最大值
  select max(math) from stu;
• min 最小值
  select min(math) from stu;
• group by 分组（一起使用） 条件过滤需要是having，不能使用where
  练习：对订单表中商品归类后，显示每一类商品的总价.
  select product,count(*),sum(price) from orders group by product;
  练习：查询购买了几类商品，并且每类总价大于100的商品
  select product,sum(price) from orders group by product having sum(price) > 100;

GROUPBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

注意，使用GROUPBY 时，SELECT 子句中只能由以下部分组成：

1.汇总函数

2.GROUP BY 中出现的列名

3.1和2 通过函数和表达式的结果
1
2
3
4
5

例如：

SELECT A_ID ,COUNT(*) FROM C GROUP BY A_ID; =>合法

SELECT A_DATE,COUNT(*) FROM C GROUP BY A_ID;=>不合法，如图示中的按A_ID分组后，对于其他各列的查询只能是汇总查询，否则没有意义

• 小结 select 语句 ： S-F-W-G-H-O 组合 select … from … where … group by… having… order by … ;

要保证顺序不能改变

试题：where和 having 条件语句的区别 ？

where是在分组前进行条件过滤，having 是在分组后进行条件过滤

使用where地方都可以用having替换 ， 但是having可以使用分组函数，而where后不可以用分组函数 

create table orders(
    id int,
    product varchar(20),
    price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
 
insert into orders(id,product,price) values(6,'电视',900);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

---

本篇博客借鉴以以下博客

https://blog.csdn.net/qq_24140237/article/details/79512334?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522158678149519724846449516%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=158678149519724846449516&biz_id=0&utm_source=distribute.pc_search_result.none-task-blog-soetl_SOETLBAIDU-2