DB_1_mysql

1、常识科普

• 数据库（DB，DataBase）是长期存储在计算机内有组织的、大量的、共享的数据集合。是一个按数据结构存储和管理数据的计算机软件系统。
• 数据库管理系统（DBMS，DataBase Management System）是专门用于管理数据库的计算机系统软件。
  • 关系型数据库管理系统(Relationship DataBase Management System)  RDBMS
• 表具有固定的列数和任意的行数，在数学上称为“关系”
• 二维表是同类实体的各个属性的结合，每个实体对应于表中的一行，在关系中称为“元组”，相当于通常的一个记录
• 表中的列表示属性，称为域，相当于通常记录中的一个数据项，也叫列、字段

2、常见的数据库

• db2：是IBM公司的关系型数据库管理系统

　　2001年IBM以 10 亿美金收购了 Informix 的数据库业务，这次收购扩大了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、修改账号的密码

1. 登录：cmd> mysql –uroot -p
2. 使用数据： mysql>  use  mysql;
3. 查询user表： mysql>  select host,user,password from user;
                                       关键字    字段       关键字  表名
4. 修改密码： 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
• 注意：sql必须使用；结尾

9、DCL，数据控制语言

       上班时，root账号不给一般人用。

• 创建用户
  格式：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 changedmysql> 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，整形null

11.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;