mysql学习笔记
1.数据库的作用：存储数据.
数据库存储数据和其他方式存储的不同之处：
1.可以存储大量数据
2.数据不会丢失
3.安全
4.共享
5.查询数据方便
2.数据库的发展历史：
1.文件系统.
2.数据库系统：
网状结构
层状结构
关系型结构:sqlserver,mysql
关系-对象型数据库: oracle(神喻 Scott)
access,db2,sysbase
3.名词：
DBMS: Database managerment system 数据库管理系统
DB: 数据库
4.安装
5.sql: structer query language :结构化查询语言
分类：
1.DML: 数据操作语言 (insert,update,delete)
2.DCL: data control language 数据控制语言 (grant ,revoke)
3.DDL: data define language 数据定义语言 (create,drop)
4.DQL: data query language 数据查询语言 (select)
5.TPL: 事务处理语言
6.sql中的注释：#注释.
数据类型：
int,smallint,long
float,double
char :固定长度的字符串
varchar :可变长度的字符串.指定的长度是最大长度.
date,datetime,timestamp(时间戳,自动插入(1970.1.1-2037.12.31))
blob: binary large object
clob: character large objct
#创建数据库
create database mydb ;
#删除数据库
drop database mydb ;
#查看所有的数据库
show databases ;
#更改当前的数据库
use mydb ;
#查看创建数据库的sql语句
show create database mydb ;
#创建表
create table t1
(
id int ,
name varchar(20) ,
age int
);
#查询表的所有数据
select * from t1 ;
#删除表
drop table t1 ;
#查看创建表的sql语句
show create table t1 ;
#查看当前数据库中所有表
show tables ;
#设定前端工具的字符编码
#设定插入字符的编码
set character_set_client=dbk;
#设定结果集的编码
set character_set_results=gbk;
#插入语句
insert into t1(id,name,age) values(1,'张三',20) ;
insert t1(id,name,age) values(2,'张无忌',21) ;
insert t1 values(3,'张三丰',25) ;
#更新语句
#修改一列的数据
update t1 set name = '张翠山' ;
#修改某一行的数据
update t1 set name = '张三丰' where id = 2 ;
#删除
delete from t1 where id = 3 ;
#删除所有的数据
delete from t1 ;
#查询
#显示所有的记录
select * from t1 ; # *表示所有的字段
#显示所有记录的姓名
select name from t1 ;
#显示id=3的记录的姓名
select name from t1 where id = 3 ;
#显示多个字段，字段之间用逗号隔开
select name,age from t1 where id = 3 ;
#显示年龄大于等于23的人
select * from t1 where age >=23 ;
#显示姓名是郭靖的人
select * from t1 where name = '郭靖' ;
#显示年龄在23到35之间的人
select * from t1 where age >=23 and age <=25 ; # and or not
select * from t1 where age between 23 and 25 ;
#显示年龄是21或者姓名叫令狐冲的人
select * from t1 where age = 21 or name = '令狐冲';
#显示姓名不叫郭靖的人
select * from t1 where name !='郭靖' ;
select * from t1 where name <>'郭靖' ;
#模糊查询
#通配符
% :代表任意多个字符(0~多个)
_ :代表的是任意一个字符(必须的有)
[]: 代表的是在某个个区间(mysql不支持)
[^] :代表的是不在某个个区间(mysql不支持)
#查询所有姓张的人
select * from t1 where name like '张%' ;
#查询姓张，名字长度是2个字符的人
select * from t1 where name like '张_' ;
#查询名字中包含一个张字的人
select * from t1 where name like '%张%' ;
#完整性 : 正确性+ 准确性=完整性.
对数据施加约束.
自定义完整性：自己定义的约束
域完整性： 由语言本身定义的约束
实体完整性: 要求每一张表必须有一个主键.
主键：唯一的去区别记录的某一列或者几列的值。
引用完整性:针对两张表。一张表中的某个字段引用另一张表的主键.此字段称为外键。
外键所在的表叫字表或从表。被引用的表叫主表.
1.添加记录时必须先添加主表中的记录，再添加子表记录.
2.当存在引用关系的时候，不能修改主表中记录的主键.
3.当删除记录的时候，必须先删除字表中的记录，再删除主表中的记录.
#创建表，设定字段operation
#timestamp类型可以主动插入也可以自动由系统插入值
create table t3(
id int ,
name varchar(20),
operation timestamp
);
#插入一条记录
insert into t3(id,name) values(1,'张三') ;
#创建表student,并添加各种约束
create table student
(
id int primary key , #主键约束
name varchar(20) , #唯一约束
age int NOT NULL, #非空约束
sex varchar(2) ,
address varchar(20) default '重庆' #默认约束
) ;
insert into student(id,name,age,sex,address) values(1,'张无忌',20,'男','北京') ;
insert into student(id,name,age,sex,address) values(2,'张三丰',20,'男','北京') ;
insert into student(id,name,age,sex,address) values(3,'小龙女',16,'女','古墓') ;
insert into student(id,name,age,sex) values(4,'黄蓉',18,'女') ;
insert into student(id,name,age,sex,address) values(5,'令狐冲',25,'男',default) ;
insert into student(id,name,age,sex,address) values(6,'郭靖',25,'男','桃花岛') ;
#演示别名的问题
#给字段或者表可以起别名(用as关键字指定，as可以省略).
select id as 编号,name as 姓名, sex 性别,age 年龄,address 地址 from student ;
#添加修改字段的问题
#添加一个字段birthday date
alter table student add birthday date ;
#查看表的结构
desc student ;
#删除字段 birthday
alter table student drop column birthday ;
#演示distinct(不同的)关键字
#distinct必须写在所有字段的前面，distinct指的是后面所有字段都不同。
#查看student表中有几个年龄
select distinct age from student ;
#查看姓名和不同的年龄
select distinct name,age from student ;
#查询null的值
#查询没有名字的人
select * from student where name = null ; #差不到
select *from student where name is null ;
select *from student where name is null or name = '';
#演示关键字in(表示在....里面)
#查询地址在桃花岛，北京的人
select * from student where address = '桃花岛' or address = '北京' ;
select * from student where address in('桃花岛','北京') ;
#创建分数表
create table score
(
id int primary key ,
sid int ,
china int ,
history int,
english int,
constraint FK_sid foreign key(sid) references student(id)
) ;
insert into score values(1,1,54,85,69) ;
insert into score values(2,3,71,60,89) ;
insert into score values(3,4,71,34,80) ;
insert into score values(4,6,87,79,81) ;
#查询给每个人语文成绩提高5后的成绩
#字段支持各种表达式
select china + 5 from score ;
#查询一下每个人考试总分
select china + history + english 总分 from score ;
#演示in语句
#在一个 select语句中嵌套一个select语句，称为子查询.
#子查询必须写在where 条件中.
#查询一下参加考试的人的姓名
select name from student where id in(select sid from score) ;
#查询一下参加考试的人的姓名和成绩
select name,china,history,english from student s,score c where s.id = c.sid ;
#查询一下参加考试的人的姓名和成绩,总分
select name,china,history,english,china + history+english 总分 from student s,score c where s.id = c.sid ;
#查询一下没有参加考试的人的姓名
select name from student where id not in(select sid from score) ;
#排序(order by)
#如果值一样，则默认再按主键升序排。
#显示语文成绩降序排序
select * from score order by china desc ;
#按多个字段排序
select * from score order by china desc,id desc;
select * from score order by china asc,id desc;
#按照表达式排序
#按照总分降序排序
select *,china + history + english 总分 from score order by china + history + english desc ;
#多表查询
#分类：内联，外联(左外链接，右外链接),交叉连接
#查询的原理：
表是由页组成，记录存放在页中。每页会有一个索引。每个表有一个索引页。页的大小是8k.
#交叉查询(cross join)
select * from student cross join score ;
#内联查询(用主外键的关系)(inner join)
#查询参加考试人的姓名
select name from student s inner join score c on s.id = c.sid ;
#查询没有参加考试人的姓名
select name from student s inner join score c on s.id != c.sid ; #交叉查询
#内联查询和子查询的关系：
所有的内联都可以用子查询替换.子查询不一定能用内联查询替换.
#左外链接是以左边的表为基准（左边的表有多少条记录，结果一定有多少条记录）
#(left outer join ,outer可以省略)
#显示所有人的成绩
select s.*,c.* from student s left outer join score c on s.id = c.sid ;
select s.*,c.* from student s left join score c on s.id = c.sid ;
select s.*,c.* from student s right join score c on s.id = c.sid ;
#演示关键字limit n,m , n是下标, m是截取几条记录
#limit关键字只适用于mysql
#显示总成绩前三名
select s.name,c.china,c.history,c.english, china + history + english 总分 from student s,score c
where s.id = c.sid
order by china + history + english desc limit 0,3 ;
#显示年龄最大的3个人
select * from student order by age desc limit 0,3 ;
#演示auto_increment
#auto_increment的字段类型必须是int或相关的类型
create table t4
(
id int primary key auto_increment,
name varchar(20)
) ;
#聚合函数
#max,min,sum,avg,count
#查询语文最高分
select max(china) from score ;
#查询年龄的总和
select sum(age) from student ;
#查询总共有多少学生
select count(*) from student;
#分组
group by
#根据性别分组
select count(*) from student group by sex ;
#希望显示男，女和分组的情况
select sex,count(*) 个数 from student group by sex;
注意：select关键字后的字段除过聚合函数外只能写根据分组的字段
select name,sex,count(*) 个数 from student group by sex,name;
#根据条件进行分组
#having 分组条件
#条件是分组条件
#根据性别进行分组，要求组中成员的年龄大于17
select sex,count(*) from student where age > 17 group by sex ;
#根据性别进行分组，要求组中成员大于3个人
select sex,count(*) from student group by sex having count(*) >=3 ;
#where,on,having三个代表条件的区别：
不能互换使用。where代表的是普通条件。on用在多表连接查询中。having只能
用于分组查询中。
where关键字一般跟字段的表达式。
having关键字后一般都是局和函数表达式。
总结select语句的完整的写法.
select [字段...] from [表名,表名] where ...
group by 字段表达式... having ....
order by 字段1，字段2...
limit .....
执行过程: 先执行where条件过滤. 再执行group by,在执行order by ..最后执行limit
#函数
#日期函数
#查询当前的时间
select now() ; #年月日，时分秒
select current_date() ; #年月日
select current_time() ; #时分秒
year(日期) ; #获取年 SELECT YEAR(NOW());
#字符串函数
left(字符串，长度) 从字符串的左边截取多长
right(字符串，长度
#数学函数
#数据库的备份与恢复
#备份(语句后不要加分号)
在dos窗口中敲入mysqldump -u root -proot mydb>d:/mydb.sql
#恢复(语句后不要加分号,先创建好数据库)
1.在dbms(数据库管理系统)的客户端敲入：source d:/mydb.sql
2.在dos环境下敲入：mysql -u root -proot mydb<d:/mydb.sql
#注：备份的文件的后缀名可以使任意的。
#创建用户zhangwuji,密码123 (必须用root创建，root是超级用户)
create user zhangwuji identified by '123' ;
#给用户zhangwuji赋看到数据库mydb的权限
grant all on mydb to zhangwuji ;
#给用户zhangwuji赋操作表t1的权限
grant all on t1 to zhangwuji ; #给zhuangwuji赋予了增删改查四种权限
grant select on t1 to zhuangwuji ; #给zhuangwuji赋予查询四种权限
grant select,insert on t1 to zhuanguji ; #给zhuangwuji赋予查询和添加四种权限
#回收zhangwuji对表t1的增删改查权限
revoke all on t1 from zhangwuji ;
