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

上一篇:C2:抽象工厂 Abstract Factory


下一篇:sQL语言分类 DML、DDL、DCL区别