1、常识科普
- 数据库(DB,DataBase)是长期存储在计算机内有组织的、大量的、共享的数据集合。是一个按数据结构存储和管理数据的计算机软件系统。
- 数据库管理系统(DBMS,DataBase Management System)是专门用于管理数据库的计算机系统软件。
- 关系型数据库管理系统(Relationship DataBase Management System) RDBMS
- 表具有固定的列数和任意的行数,在数学上称为“关系”
- 二维表是同类实体的各个属性的结合,每个实体对应于表中的一行,在关系中称为“元组”,相当于通常的一个记录
- 表中的列表示属性,称为域,相当于通常记录中的一个数据项,也叫列、字段
2、常见的数据库
- db2:是IBM公司的关系型数据库管理系统
- oracle:是Oracle公司的关系型数据库管理系统
- ms sql server:是Microsoft公司的关系型数据库管理系统
- mysql:是瑞士MySql AB公司关系型数据管理系统
3、安装mysql
- mysql 默认端口号:3306
- mysql 将UTF-8编码修改了UTF8
- cmd命令创建打开“windows服务”: services.msc
- mysql默认账号:root
- %mysql%/my.ini ,mysql默认配置文件
- port=3306 端口号
- default-character-set=utf8 字符集(编码)
- basedir="D:/java/MySQL Server 5.5/" mysql安装的目录
- datadir="D:/java/MySQL Server 5.5/Data/" mysql创建数据库存放目录
4、启动mysql
- 已经启动
- 使用window命令启动
- 手动方式启动mysql(相当于手动启动tomcat)
5、连接mysql
- 格式1:
cmd> mysql –u账号名 –p密码 –hIP地址
例如:C:\Users\lt>mysql -uroot -p1234 -h192.168.11.94 - 格式2:
cmd> mysql –user=账号 --password=密码 --host=ip地址 - 推出
cmd> exit
6、集成开发工具:Navicat Preminu
7、常用命令
7.1、显示当前数据库服务器中的数据库列表
- mysql> show databases;
7.2、使用数据库
- mysql> use 库名;
7.3、显示数据库中的数据表
- mysql>show tables;
7.4、显示当前所使用的数据库名称
- mysql> select database();
7.5、显示当前数据库的状态
- mysql> status;
7.6、显示当前数据库中某表的表结构 (DESCRIBE )
- mysql> desc user;
7.7、显示所支持的字符集
- show character set;
7.8、修改账号的密码
- 登录:cmd> mysql –uroot -p
- 使用数据: mysql> use mysql;
- 查询user表: mysql> select host,user,password from user;
关键字 字段 关键字 表名 - 修改密码: mysql> update user set password=password('1234') where host='localhost' and user='root';
key 表 key 字段 方法(加密) key 字段 值 key 字段 值
- 注意:将cmd命令创建关闭
8、sql的介绍
- SQL被称为结构化查询语言(structured query language)
- SQL是操作和检索关系型数据库的标准语言
- 注:ANSI(American National Standards Institute,美国国家标准学会)制定的标准
- 各个数据库生产厂商,要遵循sql标准,同时又开发出自己数据特有的特性
- sql分类
- DDL,数据定义语言,对结构进行操作(数据库db,表table,列column)
- 创建 create 、移除drop、修改alter (注意:不是javascript的提示框 alert)
- DML,数据操作(操纵)语言,对表中的数据进行操作
- 录入 insert 、删除delete、修改update
- DCL,数据控制语言,对用户的权限操作
- 授权grant、收回权限revoke
- DQL,数据查询语言,表中的数据检索(查询)
- 查询 select
- DTL,事务管理
- 开启事务 start transaction ,提交事务 commit,回滚事务 rollback
- DDL,数据定义语言,对结构进行操作(数据库db,表table,列column)
- 注意:sql必须使用;结尾
9、DCL,数据控制语言
- 创建用户
格式:mysql> create user 用户名 identified by ‘密码’;
mysql> create user itheima identified by '1234';
mysql> create user 'itheima'@'localhost' identified by '1234';
创建用户默认没有权限的 - 授权:
格式:mysql> grant 权限 on *.* to 用户 with grant option;
权限:all,alter,create,drop
*.* 表示所有数据库的所有内容
day14.* 表示day14数据库的所有内容
用户:'用户名'@'主机地址'
* 例如: 'itheima'@'localhost'
* 例如: 'itheima'@'%' 表示远程主机可访问
with grant option : 表示当前用户,可以将获得权限再次授予其他人。 - 取消权限
格式:mysql> revoke 权限 on *.* from 用户;
删除用户:mysql> drop user 'itheima';
select a.* from users a where a.classes in ( select classes from users group by classes having avg(counts)>=60);
10、DDL,数据定义语言
10.1、数据库database
- 创建数据库:mysql> create database [IF NOT EXISTS] 数据库名称 [character set 字符集 ]
如果不设置编码,使用mysql安装时默认编码
例如:mysql> create database day14; - 删除数据库:mysql> drop database [IF EXISTS] 数据库名称;
例如:mysql> drop database day14; - 修改数据库:mysql> alter database 数据库 character set 字符集;
例如;mysql> alter database day14 character set gbk;
10.2、表table
- 创建表: mysql> create table 表名(字段的描述1,字段描述2,….);[注意:最后一个字段没有逗号]
- 字段描述格式: 字段名称 字段类型 约束
- 字段类型
mysql类型
mysql类型描述
java类型
字符串
char(n)
固定字符串。char(5) ,表示字段长度为5字符。
l 例如:”abc” ,右侧添加空格
String
varchar(n)
可变字符串。char(5),表示字段长度为5字符。
l 例如:”abc” ,长度为3
String
整形
bit
比特。一个位。
boolean
tinyint
byte
MEDIUMINT
short
int
int
bigint
long
时间
date
日期
java.sql.Date
datetime
日期时间
null
time
时间
java.sql.Time
timestamp
时间戳,数据库可以自动维护时间
java.sql.Timestamp
java.util.Date 父类,有3孩子:java.sql.Date、java.sql.Time、java.sql.Timestamp
使用:
l 一般使用util.Date ,如果使用sql.Date 位置放置dao层。
l util.Date提供 api,long getTime()
l new java.sql.Date(new java.util.Date().getTime() )
l new java.sql.Time(new java.util.Date().getTime() )
l new java.sql.Timestamp(new java.util.Date().getTime() )
大数据
blob
64k,字节大数据对象,Binary Large Object
Blob
longblob
4G
text
64k,字符大数据对象,Character Large Object
Clob
longtext
4G
mysql> use day14;
Database changed
mysql> create table users(
-> id varchar(20),
-> firstname varchar(50),
-> secondname varchar(50),
-> age int
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> desc users;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | varchar(20) | YES | | NULL | |
| firstname | varchar(50) | YES | | NULL | |
| secondname | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
删除表: mysql> drop table 表名;
修改表:
重命名表名:mysql> alter table 表名 rename 新的表名;
10.3、列 column
- 添加列:mysql> alter table 表名 add [column] 字段描述;
例如:mysql> alter table users add column count int; - 删除列:mysql> alter table 表名 drop [column] 列名;
- 修改列名: mysql> alter table 表名 change old_列名 new_字段描述
例如:mysql> alter table users change count counts int;
11、DML,数据操作语言
11.1、录入insert
- 格式1:mysql> insert into 表名 values(值1,[
1值如果字符串需要使用引号,常用单引号。
2值如果整形,一般直接写数据
3多个值之间使用逗号分隔
]值2);
例如:mysql> insert into users values('u001','shi','tian',18,998);
要求:4值的个数必须与表中列的个数一致- 格式2:mysql> insert into 表名(列名1,列名2,…) values(值1,值2,…);
例如:mysql> insert into users(id,firstname,age) values('u002','diao',38);
如果表中列没有约束,默认值字符串null,整形null11.2、更新update
- 格式1:mysql> update 表名 set 列名1=值1, 列名2=值2,…. ;
例如:mysql> update users set counts=87;
更新表中所有数据- 格式2:mysql> update 表名set 列名1=值1, 列名2=值2,…. where 条件;
例如:mysql> update users set secondname='baole' where id='u002';11.3、删除delete
- 格式1: mysql> delete from 表名;
删除表中所有数据,慎用- 格式2:mysql> delete from 表名 where 条件;
11.4、中文数据录入
- 前提:mysql安装时设置的编码UTF8,创建数据库时使用编码默认UTF8。
- 如果db数据库编码是UTF8,创建表时,表的编码也是UTF8
- 显示创建表的sql语句:mysql> show create table users;
| users | CREATE TABLE ``users` (
`id` varchar(20) DEFAULT NULL,
`firstname` varchar(50) DEFAULT NULL,
`secondname` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`counts` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |- `’
- 重音符:esc键下面 `
如果使用列名等关键字,必须是重音符括起来。- create table `order`();
单引号:’- 解决方案:mysql> set names gbk;
- 分析:
cmd命令窗口:录入的数据编码,GBK
查询服务器编码设置:mysql> show variables like '%char%';
12、DQL,数据查询语言
- 准备数据
insert into `users`(`id`,`firstname`,`age`,`secondname`,`counts`)
values ('u001','张',18,'飞',60),
('u002','赵',20,'云',58),
('u003','关',22,'羽',80),
('u004','刘',25,'备',98),
('u006','黄',18,'盖',NULL),
('u005','王',12,'子云',20),
('u007','诸葛',24,'亮',100);
12.1、没有条件查询
- 查询所有
mysql> select * from users;- 查询部分信息
格式:mysql> select 字段1,字段2,字段3,… from 表名;
mysql> select firstname,secondname from users;
mysql> select id,firstname,secondname,age,counts from users;- 查询用户编号、姓名,及格
mysql> select id,firstname,secondname,counts-60 from users; #表达式
mysql> select id,concat( firstname,secondname),counts-60 from users; #表达式可以是方法
- 修改上面查询显示字段名称,用"姓名"表示姓名,用"及格"表示及格
字段别名格式: select 字段1 AS 别名 , 字段 别名 from 表名;
字段 [AS] 别名
mysql> select id,concat( firstname,secondname) as 姓名,counts-60 及格 from users;
mysql> select id,concat( firstname,secondname) as `姓 名`,counts-60 及格 from users;
12.2、带有条件查询
格式:mysql> select * from 表名 where 条件条件:字段 运算符 值 ,例如:username=”jack” 或 age > 18条件1 and 条件2 or 条件3
- 查询分数等于60的学生
mysql> select * from users where counts = 60; #如果整形直接写,如果字符串使用单引号。- 查询姓"张"学生
mysql> select * from users where firstname=张; #语法错误,字符串必须使用单引号
mysql> select * from users where firstname='张';- 查询年龄大于18的学生
mysql> select * from users where age > 18;- 显示分数在60-80的学生
mysql> select * from users where counts >= 60 and counts <=80;
mysql> select * from users where counts between 60 and 80; # 字段 between 值1 and 值2- 查询编号为u001和u002的学生
mysql> select * from users where id='u001' or id='u002';
mysql> select * from users where id in ('u001','u002'); # 字段 in (值列表)- 查询年龄是18或20的学生
mysql> select * from users where age = 18 or age = 20;- 查询名中含有"云"的学生
- 模糊查询,不完全匹配查询,like语句
格式:select….. where 字段 like 值
特殊符号:%表示任意多个字符 ; _表示一个字符
值:
- ‘abc’ , 按照abc查询
- ‘%abc’ , abc结尾
- ‘abc%’ , abc开头
- ‘%abc%’ ,含有abc
mysql> select * from users where secondname like '%云%';
- 查询名中第二字还有"云"的学生
mysql> select * from users where secondname like '_云%';
- 查询分数小于60 或 大于90分的学生
mysql> select * from users where counts < 60 or counts > 90;
- 查询分数等于60 或者 分数大于90并且年龄大于23
mysql> select * from users where counts = 60 or counts > 90 and age > 23;
mysql> select * from users where counts = 60 or (counts > 90 and age > 23); #运算符优先级, and优先or- 查询没有考试的学生
mysql> select * from users where counts is null;
- 查询所有考试的学生
mysql> select * from users where counts is not null;- 分数不是100
mysql> select * from users where counts != 100;
mysql> select * from users where counts <> 100; #都不进行null计算
12.3、聚合函数的使用
聚合函数:对表中一列数据的统计,结果一个(一列,一行)
- 有多少条记录
计算总记录数,count函数
格式:select count(字段 | * | 数字) from… #注意:如果使用字段不进行null计算
mysql> select count(id) from users;
mysql> select count(*) from users;
mysql> select count(1) from users;- 平均成绩
函数avg
mysql> select avg(counts) from users;
mysql> select sum(counts)/count(id) from users;
- 最高成绩
函数max
mysql> select max(counts) from users;- 最小年龄
函数min
mysql> select min(age) from users;- 班级总成绩
函数sum
mysql> select sum(counts) from users;- 查询所有的年龄数
mysql> select age from users;
sql排序格式:select .. where … order by 字段名称; #默认升序
select .. where … order by 字段名称 asc|desc; #asc升序,desc降序
mysql> select age from users order by age asc;
- 去重复
mysql> select distinct age from users order by age asc;12.4、组合
- 添加班级字段(classes)
mysql> alter table users add column classes varchar(3);
# 班级信息初始化
mysql> update users set classes = '1';
mysql> update users set classes = '2' where id in ('u005','u006','u007');- 查询1班和2班的平均成绩
# 两个班的平均成绩
mysql> select sum(counts)/count(id) from users;
- sql分组格式:select …. where … group by 字段 having 分组条件
mysql> select sum(counts)/count(id), classes from users group by classes;
mysql> select sum(counts)/count(id) as avg, classes from users group by classes;
- 查询班级的平均成绩不及格的班级成员信息
查询不及格的班级
mysql> select sum(counts)/count(id) as avg, classes from users group by classes having avg < 60;
查询2班的成员信息
mysql> select * from users where classes = '2';
- 多表查询 (多个表是一个表:子查询,将查询语句所谓另一个sql语句语法的一部分)
格式1: select * from A,B where A.id = B.id- 表的别名:select * from 表名 as 别名
格式2:select * from A as a,B b where a.id = b.id;- #模拟
select * from users u, B b where u.classes = b.classes;- 结果:
select u.* from
users u,
(
select sum(counts)/count(id) as avg, classes from
users
group by classes
having avg < 60
) b
where u.classes = b.classes;