java基础高级2 MySQL 高级
1.数据库简介
DDL(数据定义语言)
DML(数据操作语言)
2. 准备工作
解压缩文件目录下找到my.ini文件,文件中写入[mysql] default-character set= utf-8
[mysqld]
port 3306
character set server=utf8
环境变量:path目录下,分号bin目录
把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');
相关子查询
子查询的查询条件不依赖于父查询,成为不相关子查询
子查询的查询条件依赖于父查询,称为相关子查询
例如:查询至少有一门课程成绩大于90分的学生的姓名
select studentname
from student s
where exists (select * from grade g where s.studentid=g.studentid and score>90)
不相关子查询的查询过程:先执行子查询,得到结果后,再执行父查询
相关子查询的查询过程:先执行父查询,每次从表中取出一条数据后,紧接着就执行子查询,子查询会用到父查询取出的这条数据,如果符合条件,这条数据就会被放进最终的结果集中
判断是否依赖;子查询是否可以独立执行
集合查询
查询语句的结果实际上是集合,集合之间就可以进行并union,交intersect,差except等集合运算(很多数据库只支持并集)
语法格式
查询语句1 union查询语句2 -----会去掉重复数据行
查询语句1 union all 查询语句2 -------会保留全部数据行
集合查询要求查询结果集的列数必须相等,并且对应列的数据类型必须相同
select studentname ,age from student where age<30
union
select studentname age from student where age>33
sql查询常用优化
1. 尽量不要使用select *,因为会增加编译时间(不过很多数据库都对select * 进行了优化),有时也会查出并不需要的数据,增加网络和服务器负担
2.尽可能把多条sql语句合并成一条,因为每条sql语句的发送,编译,执行,返回结果都需要时间
3. 尽量不要在where子句中使用1=1,这会迫使数据库服务器进行全表扫描(很多数据库也对这种情况进行了优化)
4.尽可能使用between and 因为数据库已经对他进行了优化
5.数据库一般采用自后向前的顺序解析where子句,所以表的链接条件最好写在最前面,那些可以过滤大量数据的条件要写在最后
6.通过编程语言查询数据时最好使用带占位符的sql语句,数据库会预先编译好这些sql语句,并保存起来供重复使用,效率高而且可必选sql注入漏洞
MySQL账号管理
%通配符,所有的电脑
创建账号;create user 'username'@'%主机名或ip地址' ,identified by '123'
删除账号;drop user 'username'@'%'
给账号授权;grant all privileges on dbname.* to 'username'@'%'
查看授权;show grants for 'username'@'%'
取消授权: revoke all privileges on dbname.* from 'username'@'%'
修改自己的密码(没有登录)mysqladmin -u 用户名 -p password 新密码
修改别人的密码(root登录) set password for 用户名=password("新密码");
create user 'dandan' @ '%' identified by'123';只有登录权限
select * from mysql.user;不需要使用use database
use mysql
delete from user where host='localhost' and select _priv='N';
重启mysql数据库
quit 退出
net stop myslq停止数据库
net start mysql 启动数据库
找回root密码
1. 在my.ini文件中[mysqld]下找到skip -grant-tables
2.重启MySQl服务
net stop mysql
net start myslq
3.使用mysql直接登录,不需要用户名和密码
4. 执行
5.执行
6.退出quit
7.删除my.ini文件中的skip -grant-tables