MySQL数据库1-mysql数据库基础

常见的数据库软件

数据库软件，存储和管理数据的仓库。
1、Oracle: 是甲骨文公司的产品。满足中大型应用。和java语言兼容性非常好的。
2、SQL Server： 是微软公司的产品。和net平台（c#语言）兼容最好。
3、DB2: 是IBM的产品。
4、mysql：开源的数据库产品。和java语言兼容非常好。满足中小型应用。
5、mangoDB： noSQL数据库

注：mysql：满足中小型应用
　　oracle：满足中大小应用

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释：
　　单行注释: -- 注释内容 或 #注释内容(MySQL 特有)
　　多行注释: /* 注释 */

sql语句分类

1、数据库定义语句(DDL)：（操作数据库，表）create、alter、drop、truncate
2、数据库操作语句(DML)：（对表中数据进行增删改）insert、update、delete
3、数据库查询语句(DQL)：（对表中数据进行查）select、show
4、数据库控制语句(DCL)：（对数据库进行权限控制）用来定义数据库的访问权限和安全级别，及创建用户

MySQL数据模型（关系型数据库）

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的“二维表”组成的数据库
优点：
　　都是使用表结构，格式一致，易于维护。
　　使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
　　数据存储在磁盘中，安全。

 

MySQL数据库基本使用

连接MySQL：mysql -u用户名 -p  　　例：mysql -uroot -p

　　　　　　 mysql -u用户名 -p密码  例：mysql -uroot -proot

管理数据库(DDL)

1、查看所有数据库：show databases;
2、创建新的数据库：create database day14;
　　指定字符集：create database day15 default character set gbk;
　　创建数据库(判断，如果不存在则创建):CREATE DATABASE IF NOT EXISTS day14;
3、修改数据库（修复数据库的字符集）：alter database day15 default character set utf8;
4、删除数据库：drop database day15;
　　删除数据库(判断，如果存在则删除):drop database if exists day15;
5、使用数据库
　　查看当前使用的数据库：select database();
　　使用数据库：use day14;

管理表（DDL）

操作表之前，需要切换数据库： use 数据库名称 例如 use day14;
1、创建新的表(create table)：create table 表名(字段名称 字段类型,字段名称 字段类型.....);
　　create table student(
　　　　id int,
　　　　name varchar(20),
　　　　age int
　　);
2、修改表（alter table）
　　添加字段：alter table student add gender varchar(1);
　　修改字段类型：alter table student modify gender varchar(2);
　　修改字段名称和字段类型：alter table student change gender sex varchar(2);
　　删除字段：alter table student drop age;
　　修改表名称：alter table student rename to/as teacher; (to或as都可以)
3、查看表结构：desc student;
　 查看当前数据库的所有表：show tables;
　 查看创建表语句：show create table 表名;
4、删除表：drop table teacher;
　　删除表时判断表是否存在：drop table if exists teacher;

管理数据（DML）

1、插入数据（insert into）
　　给全部列添加数据：insert into student values(1,'张三',20);
　　给指定列添加数据：insert into student(id,name) values(2,'李四');
　　批量添加数据：insert into student values(1,'张三',20),(2,'李四',21),(3,'王五',19);
　　　　　　　　　insert into student(id,name,age) values(1,'张三',20),(2,'李四',21),(3,'王五',19);
2、修改数据(update)
　　批量操作（不建议经常使用这种）：update student set name='张三';
　　按条件修改,修改一个字段：修改id为2的学生姓名：update student set name='李四' where id=2;
　　修改多个字段：修改id为1的学生姓名和年龄：update student set name='王五',age=40 where id=1;
3、删除数据（delete from）
　　全表数据删除：delete from student;
　　按条件删除数据：delete from student where id=2;
　　truncate table也可以删除全表数据：truncate table student;
　　注：delete from 和 truncate table 的区别？
　　　　1）delete from删除全部，也可以按条件删除，但是truncate table只能全表删除，不能按条件删除
　　　　2）delete from删除的数据可以回滚，truncate table删除的数据不能回滚。
　　　　3）delete from不可以把自增长约束(auto_increment)重置，truncate table可以把自增长约束(auto_increment)重置

查询数据（DQL）

基础查询（select）

1、查询所有字段：select * from student;
2、查询指定的字段：select id,name from student;
3、查询时指定别名：select id as '编号',name as '姓名' from student;
　　as可以省略：select id '编号',name '姓名' from student;
4、查询时添加常量列：临时使用的列可以通过查询时动态添加进去
　　查询学生数据时添加一个"班级"列，值为“java就业班”：select id as '编号',name as '姓名','java就业班' as '班级' from student;
5、查询时合并列：查询每个学生的总分：select name as '姓名',(servlet+mysql) as '总成绩' from student;
　　注：合并列的字段必须是数值类型的字段。非数值类型合并没有效果：select name as '姓名',(servlet+name) from student;
6、查询使用连接符(concat)：select concat(name,"姓名") ,address from student;
7、查询去除重复记录(distinct)：查询有哪些的地区学生：select distinct address from student;

条件查询（where）

1、逻辑条件： and或&&(与) or或||（或） or
　　查询学生的id为1，且姓名为张三的学生：
　　　　select * from student where id=1 and name='张三'; -- (交集，结果数小于或等于任何一个条件的结果数)
　　　　select * from student where id=1 && name='张三';(不常用)
　　查询学生的id为2，或姓名为张三的学生：
　　　　select * from student where id=2 or name='张三'; -- (并集，结果数一定会大于或等于任何一个条件的结果数)
　　　　select * from student where id=2 || name='张三';(不常用)
　　查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
　　　　select * from student where age = 18 or age = 20 or age = 22;
　　　　select * from student where age in (18,20,22);
2、比较条件： > < >= <= = <> != (between and)
　　查询servlet分数大于80分的学生：
　　　　select * from student where servlet>80;
　　查询mysql分数小于或等于85分的学生：
　　　　select * from student where mysql<=85;
　　　　select * from student where mysql<85 or mysql=85;
　　查询servlet分数大于或等于80分，且小于或等于85分的学生：
　　　　select * from student where servlet>=80 and servlet<=85;
　　　　上面sql代替的语法：select * from student where servlet between 80 and 85; -- 在...之间（包前包后）
　　查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息
　　　　select * from student where hire_date BETWEEN '1998-09-01' and '1999-09-01';
　　查询年龄不等于30岁的学生：
　　　　select * from student where age <> 30;
　　　　select * from student where age != 30;
3、判空条件： is null , is not null, ='' ， <>''
　　查询没有性别数据的学生（数据‘男’或‘女’）：
　　　　select * from student where gender is null or gender='';
　　查询有性别数据的学生：
　　　　select * from student where gender is not null and gender<>'';
4、模糊条件： like
　　通配符：
　　　　%：替代任意个字符
　　　　_: 替代单个任意字符
　　查询姓‘李’的学生：
　　　　select * from student where name like '李%';
　　查询姓名中包含‘四’字的学生：
　　　　select * from student where name like '%四%';
　　查询第二个字是'花'的学生信息:
　　　　select * from student where name like '_花%';
　　查询姓‘李’，全名只有两个字的学生：
　　　　select * from student where name like '李_';

排序查询(order by)

desc: 降序。数值从大到小，字母z-a
asc: 升序。数值从小到大，字母a-z
　　按照id的升序排序：
　　　　select * from student order by id asc;
　　按照servlet成绩降序排序：
　　　　select * from student order by servlet desc;
注：多个排序条件的情况：先按照前面的条件排序，当出现重复记录，再按照后面的条件排序
　　按照age升序，按照servlet成绩升序排序：
　　　　select * from student order by age asc,servlet asc;

聚合查询

聚合函数：将一列数据作为一个整体，进行纵向计算。
　　查询所有学生servlet的总分 (sum: 求和函数)：
　　　　select sum(servlet) from student;
　　查询所有学生servlet的平均分（avg; 平均函数）：
　　　　select avg(servlet) from student;
　　查询最高的servlet分数（max：最大值函数）：
　　　　select max(servlet) from student;
　　查询最低的servlet分数（min：最小值函数）：
　　　　select min(servlet) from student;
　　查询学生数量（count： 统计数量函数）：
　　　　select count(*) from student;
　　　　select count(id) from student; -- 效率会比count(*)效率稍高
注：null 值不参与所有聚合函数运算

分组查询（group by）

查询每个地区有多少人
　　(1)对地区进行分组
　　(2)在分组的基础可以进行统计，统计的是每组的数据
　　　　select address,count(*) from student group by address;
统计男女的人数：注：where条件必须放在group by分组之前
　　 select gender,count(*) from student where gender is not null and gender<>'' group by gender;
注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

分组查询后筛选(group by + having(条件))

查询哪些地区的人数大于2个的地区
　　select address,count(*) from student group by address having count(*)>2 ;
注：having使用在group by分组之后的，对分组后的条件进行筛选
执行顺序： where > 聚合函数 > having

分页查询(limit)

limit 起始索引,查询条目数(起始索引从0开始)
　　从0开始查询，查询3条数据
　　　　select * from student limit 0 , 3;
　　每页显示3条数据，查询第1页数据
　　　　select * from student limit 0 , 3;
　　每页显示3条数据，查询第2页数据
　　　　select * from student limit 3 , 3;
　　每页显示3条数据，查询第3页数据
　　　　select * from student limit 6 , 3;
结论：分页查询当前页数据的sql： select * from student limit (当前页码-1)*每页显示条数,每页显示条数;
注：
　　分页查询 limit 是MySQL数据库的方言
　　Oracle 分页查询使用 rownumber
　　SQL Server分页查询使用 top

MySQL字段类型

1、char(20): 固定长度的字符串。不管实际存储的数据的大小，一定占用20个字符空间
2、varchar(20): 可变长度的字符串。占用的空间大小就是实际存储的数据大小。
3、int: 默认最多11位，长度根据实际存储的数值的长度
4、int(4)：固定的数组长度
　　create table test(
　　　　id1 int,
　　　　id2 int(4) zerofill   -- zerofill: 零填充
　　)
　　结果：id1：1
　　           id2：0001
5、float/double：浮点数。
7、date: 日期
7、datetime： 日期+时间
8、timestamp: 时间戳,用于记录当前数据的插入或更新的时间,会自动进行时区的转换
9、time: 时间
10、year： 年份
11、blob：二进制数据
12、text：字符数据