java基础高级2 MySQL 高级

1.数据库简介

DDL（数据定义语言）

DML(数据操作语言)

2. 准备工作

解压缩文件目录下找到my.ini文件，文件中写入[mysql] default-character set= utf-8

[mysqld]

port 3306

character set server=utf8

把mysql安装到系统服务中，管理员权限运行，mysql -install

4.使用命令行客户端执行MySQL 命令

使用命令行登陆MySQL:mysql -u -p

MySQL 不区分大小写，即除了表中的数据外，MySQL的命令，关键字，数据库名，表名，列名等大写时和小写时时一样的

5. My SQL DDl 数据定义语言

Data Definition Language 数据定义语言，用来操作数据库对象

数据定义语言包含如下命令：create，drop,alter

操作数据库：

一个My SQL数据库服务器中可以创建多个数据库

创建数据库：create database dbname;

使用数据库：use database dbname;

查看现有数据库：show databasees;

查看数据库创建语句：show create database daname;

删除数据库:drop database dbname;

操作表：

一个MySQL数据库中可以创建多个表

创建表：create table tableName(列定义列表)

列定义格式：columnName datatype [null][key][default][extra]

create table student(id int primary key auto_increament,

name varchar(20) not null,

gender varchar(10) not null default '男',

age int );

查看表结构；desc tableName;

查看表的创建语句： show create table tableName;

删除表：drop table tableName;

F5刷新

create database testdb;

use testdb;

每个单词之间有空格，小括号用英文的

create table student (

id int primary key auto_increment,

name varchar(20) not null ,

gender varchar(10) not null default '男',

age int

);

varchar可变的字符

命令行查看表show tables;

修改表结构

添加新列：alter table tableName add 列定义

删除列： alter table tableName drop columName

修改列定义：alter table tableName change oldColunmName 新列定义

操作索引

索引基于特定的数据结构，大部分是树结构，加快查询速度，另一个方面占用存储空间，拖慢更新速度，造成存储碎片

创建索引：create index indexName on tableName(columName);在表的该列上创建名为**的索引

查看索引：show index from tableName;

删除索引：drop index indexName on tableName;

在创建表时，会默认为主键列创建一个聚集索引，并且不可修改，最终只有一个

后来手动创建的都是非聚集索引

show index from testtable;

查找所有id为15的数据

select * from testtable where id=15;遍历数据库中的每一条数据

没有索引情况452ms，有索引31ms

create index index0 on testtable(id);

#注释的意思

运行SQL文件，sql

1. 插入，更新，删除

Data Manipulation Laguage数据操纵语言，用来操作表中数据（这种操作都需要事务控制，都会记录日志，都可以回滚）

常用命令：insert，select ,update,delete

插入数据

语法格式：

insert into tableName(列名列表) values（值列表）

insert int tableName(列名列表) select 查询语句

use testdb;

alter table student change age age int;修改列的数据类型

insert into student (name,gender,age) values//指定数据('dan','nan',20);

insert into student values('dandan','nv',25)不写列名列表，就把所有列的值写上

给自增列提供数据，直接写提供的

以最大的自增

insert into student (name,gender) select name,gender from student;把查到的结果集当做value值

insert into student values(null,'dandan2''nan'55),(null,'小红'，99);

更新数据；

语法格式：

update tableName set 列名= where 列名=

删除数据

语法格式

delete from tableName where 列名=

命令行工具开启事务，自己提交事务

还有另一种更彻底的删除方式

truncate table tableName;删除表中数据

这种方式会把表结构删掉，然后重新创建表，属于DDL语句，删除操作效率高，但不可回滚，慎用；

2.select ...from .., where ....子句

语法格式

select 子句查询那些列

from子句从哪个表里查询

[where 子句]

[group by子句][having 子句]

[order by子句]

[limit子句]

select 子句

指定需要从表中取出那些列的数据

select * 结果集中包含所有的列

select 列名1，列名2 可查询多个列

select 列名 as '别名' 可以为列指定别名

select 表名1.列名1，表名2.列名2

select distinct 列名列表结果集中重复的记录会被删除

select 表达式列可以进行表达式求值 select (age+1)

select 聚集函数可以对列使用聚集函数 select count(*)

聚集函数用来对结果做进一步处理

count() 对结果集计数

sum() 对结果集中特定的列求和

avg() 对结果集中特定的列求平均值 =sum()/count();

max 返回结果集列中最大值

min() 返回结果集列中最小值

from 子句

from 表名

from 表名 as '别名'

from 表名1，表名2

where子句

select max(score) from grade;

select min(score) from grade;

select avg(score) from grade;

select count(score) from grade;

select sum(score) from grade;

where 子句用来指定筛选数据的

where 表达式（表达式的计算结果应该是true或false）

select * from grade where score=100;

0和false一样，非0整数就认为是true

表达式由运算符和操作数组成；

比较运算符；= <> 不等于> >=,<,<=

逻辑运算符； and ,or ,not 用and连接两个查询条件

like, between and

is null ,is not null 对null使用其他运算符的结果都是false

查询那些列为null ；select * from grade where score is null;

集合运算符： in ,all ,ary,some exists

order by子句

order by子句对结果集进行排序

order by 列名[asc|desc]

order by 列名1[asc|desc] ,列名2[asc|desc]

asc升序，desc降序

默认是升序；

group by 子句

group by 子句对查询结果再进行分组，并常和聚集函数一起使用

语法格式

group by 列名

查询各门课程的平均成绩

select couseid,avg(score) from grade group by courseid;

查询各门课程的总成绩

select courseid ,sum(score) from grade group by courseid;

having子句

having子句和where子句用法类似

常和group by 子句配合使用，对查询的结果集进行筛选

having 子句后面可以跟聚集函数，where子句不可以

select courserid,avg(score) from grade group by couseid having avg(score)<80

limit子句

limit从结果集中取出部分结果

语法规则

limit beginindex ,length 开始索引，长度

例如：从学生表中取出前10条数据

select * from student limit 0,10;

select * from grade limit 30,10;从第30条数据开始，取10条数据；

多表查询

若一个查询涉及到两个以上的表，我们称为多表查询或者连接查询，而且需要指定连接条件

连接条件可以使用= ><等运算符，当使用=时称为等值连接，其他称为不等值连接

指定连接条件有两种方式

在where子句中指定

#例如；查询学生的姓名和该学生课程号位005的课程的成绩

select studentName,score

from student,grade

where student.studentid=grade.studentid and courseid='005'

在from子句中指定

from 表1 left|right outer join 表2 on 连接条件外连接查询

from 表1 inner join 表2 on 连接条件内连接查询

查询条件都要放在from后面，否则效果不一样

select studentName,score

from student left outer join grade on连接条件student.studentID=grade.studentid and courseid='005'

select studentName,score

from student right outer join grade on连接条件student.studentID=grade.studentid and courseid='005'

左外连接查询的作用；保留左边表的所有数据，如果右边表连接失败，用null填充

右外连接查询的作用；

例如；查询学生的姓名和该学生课程号位005的课程的成绩

select studentName ,score

from student inner join grade on 连接条件

嵌套查询

一个查询语句可以嵌套在另一个查询语句的where子句或者having子句中，这种查询成为嵌套查询

外面的查询成为外层查询或父查询，里面的查询称为内查询或子查询

子查询的结果可能包含一条数据，也可能包含多条数据

当子查询的结果是单个时，可以使用比较运算符或集合元素符

但当子查询的结果是集合时，必须使用集合运算符（in ,all,any ,exist）和all和其他运算符结合使用

in 单独使用，多条数据

any

例如：查询出年龄大于蛋蛋1的年龄的学生姓名和年龄

select studentName,age

from student

where age>(select age from student where studentname='蛋蛋1' );

例如：查询出年龄大于蛋蛋1的年龄,而且大于蛋蛋2的学生姓名和年龄

select studentName,age

from student

where age> all (select age from student where studentname='蛋蛋1' or studentname='蛋蛋2');