MySQL笔记（一）

数据库：mysql
一.mysql常用命令：
1、登陆mysql服务器
①、mysql -uroot -p123
②、mysql -uroot -p 回车 123
③、mysql -h 127.0.0.1 -uroot -p…
2、退出exit
3、查看所有数据库 show databases;
4、进入（切换）数据库 use 数据库名;
5、查看数据库中的表 show tables;
6、设置客户端的编码set names gbk;
7、查看表中的数据
select * from stu;
8、查看表的结构：mysql> desc stu;
±------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
| pic | varchar(100) | NO | | | |
| hobby | varchar(100) | NO | | NULL | |
±------±-------------±-----±----±--------±---------------+
9、\c 优雅的处理错误
10、创建表
create table 表名(
列1 数据类型 [参数],
列2 数据类型 [参数]
)指定编码和引擎;

create table abc(
name varchar(10),
age int
)charset utf8;

11、添加数据
insert into 表名(列1,列2,列3) values (值1,值2,值3);
insert into abc(name,age)values(‘张三’,19);

12、删除表 drop table 表名
例如：drop table abc;
13、修改表名 rename table 原表名 to 新表名
例如：rename table stu to stu2;
rename table stu to stu3;
学号 姓名 年龄 地址
10001 王大拿 30 老鳖湾莲花池水沟

14、添加一列
alter table 表名 add [column] 列名 数据类型 [参数];
alter table stu add age tinyint
15、修改一列
alter table 表名 change 原列名 新列名 数据类型 [参数]
alter table stu change age age tinyint unsigned; (unsigned表示非负)
16、删除一列
alter table stu drop column age;
17、清空表（包括表的数据和自动增长重置）
truncate table 表名
二、列类型：
参数：
①、unsigned 非负数
②、M（长度） 001 002 3->003
③、zerofill
例如：alter table stu change sn sn tinyint(3) zerofill unsigned;

2、浮点型
float(5,2) 5表示一共用5位，2表示小数点后有两位
decimal(7,2)
对于太大的数，float会出现不精确的情况，使用decimal

3、字符串型
varchar(M)
char(M)
varchar(5)和char(5)的区别：
varchar的利用率不会达到100%。char的利用率会达到100%

text() – 可以存无限个字符

☞通用参数：
not null – 不允许为null
default – 该列的默认值

☞主键上的参数
primary key – 规定该列为主键
auto_increment – 规定该列为自动增长类

再看建表语句：
create table stu(
id tinyint primary key auto_increment,
name varchar(10) not null default ‘’,
sex char(1) not null default ‘’,
age tinyint unsigned not null default 0,
class char(3) not null default ‘’
)charset=utf8;

4、时间日期类型：
典型的日期时间格式：2015-09-17 11:20:40
year 典型格式：2015 简写格式：15
00-69 表示2000-2069
70-99 表示1970-1999
date 典型格式：2015-09-17 简写格式20150917
time 典型格式：11:20:40 简写格式 112040
datetime 典型格式 2015-09-17 11:20:40 简写格式 20150917112040

三、增删改查
1、修改update语句
语法：update 表名 set 列=新值,列=新值… where 条件表达式
where条件是必须要填写的，如果不填写，那么所有数据都被修改了。
例如：update stu set age=48 where id=1;
update stu set age=45,class=‘武术班’ where id=1;

2、删除delete语句
语法：delete from 表名 where 条件表达式
where条件是必须要填写的，如果不填写，那么所有数据都被修改了。
例如：delete from stu where id = 16

3、select查询语句–where条件查询
①、select id,name from stu; #指定查询那些列
②、查询年龄大于20岁的人
select name,age from stu where age > 20;
查询年龄不等于18岁的
select name,age from stu where age != 18;
select name,age from stu where age <> 18;

③、查询年龄是17,18,19的人
select age,name from stu where age in(17,18,19);
④、查询年龄在20-30之间的
select age,id,name from stu where age >=20 and age <=30;
select age,id,name from stu where age between 20 and 30;

⑤、查询姓杨的同学
select name from stu where name like ‘杨%’;
查名字中带“峰”的
select name from stu where name like ‘%峰%’;

4、select查询语句 – 统计查询
①、max – 查最大值

查班里年龄最大的人

select max(age) from stu;

查询班里年龄最大的人

select name,max(age) from stu; #这个查询是错误的
#正确的查询
select name,age from stu where age = (select max(age) from stu);
②、min – 查最小值

查询年龄最小的年龄

select min(age) from stu;
select min(age) as minage from stu;
select min(age) minage from stu;
③、avg – 求平均值

查班里所有人的年龄的平均值

select avg(age) from stu;

④、sum – 求总和

查班里所有人的年龄的总和

select sum(age) from stu;

⑤、count – 求总行数、总记录数
select count(id) from stu;
select count(*) from stu;

5、group by – 分组查询（经常配合统计函数用）

按班级分组

select * from stu group by class;#在得到的结果中，只有班级列是有意义的

按班级分组，查每个班年龄最大的年龄

select max(age),class from stu group by class;
group by谁，谁就是有意义的。

分别查看男生和女生中年龄最小的

select min(age),sex from stu group by sex;

6、order by – 排序

查班里所有人，按年龄从小到大排序

select age,name from stu order by age asc;
select age,name from stu order by age;# 默认是从小到大排序

查班里所有人，按年龄从大到小排

select age,name from stu order by age desc;

查班里所有人，按班级、年龄从小到大排序

select class,age,name from stu order by class desc,age;

7、limit – 限制
语法： 1）limit length; 表示从0的位置开始，取出length条记录
2）limit start,length; 表示从start开始，取出length条记录

按id从小到大排序，取出前3条记录

select * from stu order by id limit 3;
select * from stu order by id limit 0,3;

8、having – 类似于where，用于表中不存在的字段

查询商品表中，每卖出一件商品，盈利超过100块的商品

select name,price2-price as a from goods having a > 100;

因为a是一个临时的字段，如果用他当做条件，那么用having而不能用where。

9、如果where、group by、having、order by、limit出现在同一条sql语句中，那么他们的顺序是where、group by、having、order by、limit
练习：
create table cj(name varchar(10),subject varchar(3),score tinyint unsigned);
insert into cj(name,subject,score) values (‘张三’,‘数学’,90);
insert into cj(name,subject,score) values (‘张三’,‘语文’,50);
insert into cj(name,subject,score) values (‘张三’,‘地理’,40);
insert into cj(name,subject,score) values (‘李四’,‘语文’,55);
insert into cj(name,subject,score) values (‘李四’,‘政治’,45);
insert into cj(name,subject,score) values (‘王五’,‘政治’,30);
姓名科目分数
张三数学90
张三语文50
张三地理40
李四语文55
李四政治45
王五政治30
用一条sql语句查询两门及两门以上不及格同学的平均分
①、select score<60 from cj;
②、select sum(score<60) as bujige from cj having bujige >= 2;
③、select sum(score<60) as bujige,name,avg(score) from cj group by name having bujige>=2;

10、连接查询left、right、inner
左连接：语法：select * from 表1 left join 表2 on 表1.field = 表2.field;
得到的结果是表1的全部和表2中和表1有关系的数据

右连接：语法：select * from 表1 right join 表2 on 表1.field = 表2.field;
得到的结果是表1中和表2有关系的数据和表2的全部数据

内连接：语法：select * from 表1 inner join 表2 on 表1.field = 表2.field;
得到的结果是表1和表2的交集。对于内连接，inner可以省略。

错误的查询：select name from boy join girl on boy.flower = girl.flower;
报错：Column ‘name’ in field list is ambiguous
正确的查询：select boy.name,girl.name from boy join girl on boy.flower = girl.flower;

11、联合查询 union
select * from boy union select * from girl;
对于union，union两侧的查询必须有相同数量的列。

12、子查询where 、from 、 exists
①、where
##查张三丰的同班同学（包括他自己）
把内层sql查询的结果作为外层sql查询的条件；
select * from stu where class = (select class from stu where name=‘张三丰’);

1. ②、from
   将一个查询结果当做一个临时表，然后从这个临时表中进行查询
   #查女同学中，年龄小于30岁的
   select * from (select * from stu where sex=‘女’) a where age < 30;
   ③、exists
   外层sql查询所查到的行代入内层sql查询,要使内层查询能够成立

根据brand表和category表。在brand表中查找存在分类的品牌

内层：select * from category where brand.id=category.brand_id;
外层：select * from brand where exists 判断类型的条件;
整合：select * from brand where exists (select * from category where brand.id=category.brand_id);

先看子句，如果子句查询结果为真，则查外层的sql语句；如果字句查询结果为假，外层语句就不执行。
分析：
当brand.id为1的时候，对于外层查询语句来说，条件就是id=1.
此时看内层sql语句，内层如果查到结果，则执行外层的sql语句。所以查到“手机”;
当brand.id为2的时候，对于外层查询语句来说，条件就是id=2.
此时看内层sql语句，内层如果查到结果，则执行外层的sql语句。所以查到“服装”;
当brand.id为3的时候，对于外层查询语句来说，条件就是id=3.
此时看内层sql语句，内层如果查到结果，则执行外层的sql语句。当brand.id=3的时候，内层sql没有查到结果，所以不执行外层的sql。所以查不到“汽车”
13、多表查询
语法：select * from 表1,表2,… where 表1.field = 表2.field and…
注意：条件部分，一定要有所查表的关系
例如：查张三丰所有成绩
name subject score
张三丰 数学 87
张三丰 语文 55
…
select stu.name,course.subject,score.score from stu,course,score where stu.id = score.stu_id and course.co_id = score.co_id and stu.name=‘张三丰’;

多表查询练习：
#查张三丰同班同学的数学成绩
select score from stu,score,course where score.co_id = course.co_id and stu.id = score.stu_id and subject = ‘数学’ and stu.id in(select id from stu where class = (select class from stu where name=‘张三丰’));

#查和张三丰数学成绩相同的其他同学的语文成绩
select score from score,course where course.co_id = score.co_id and subject = ‘语文’ and score.stu_id in(select id from stu,course,score where stu.id=score.stu_id and course.co_id = score.co_id and subject = ‘数 学’ and score = (select score from stu,course,score where stu.id=score.stu_id and course.co_id = score.co_id and subject = ‘数学’ and name = ‘张三丰’) and name != ‘张三丰’);

四、字符集和存储引擎：
MySQL的存储引擎主要有：Myisam、InnoDB.
Myisam：批量存取速度快，不支持事务安全，表锁。
InnoDB:批量存取速递慢，支持事务安全，行锁。
用法：在建表时指定。
create table test(列 数据类型,…)charset=utf8 engine=myisam;

五、字符集（编码）校对集
set names gbk代表了三句话：
set character_set_client=gbk;
set character_set_results=gbk;
set character_set_connection=gbk;

以上三句话的意思是：
set character_set_client=gbk;意思是在插入数据的时候，所采用的编码
set character_set_results=gbk;意思是取出的数据用什么编码来翻译。
set character_set_connection=gbk;意思是转换器要以什么编码来编译（一般来说，不影响结果）
编码utf-8；但是在mysql中utf-8码要写成utf8；
为了解决乱码：统一编码，数据库和网页都用相同的编码。
我们在以后的学习中，除了dos窗口必须用gbk的编码外，其他地方，全部采用utf-8的编码。
查看所有的字符集：show character set;
查看所有的校对集：show collation;
一个字符集对应着一个或多个校对集。校对集是字符集的座次表。

应用：create table abc(aa int,….)engine myisam charset utf8;
如果创建表的时候没有指定字符集，将使用数据库的字符集，如果数据库也没有指定字符集，将使用数据库服务器的字符集。

查看创建表的语句：show crea te table 表名;
表示新插入的数据的id AUTO_INCREMENT=11

六、备份和恢复。
备份：
mysqldump.exe
不登录到mysql命令行：

1. 备份一个表
   mysqldump –uroot –p1 test stu > D:/test_stu.sql
2. 备份多个表
   mysqldump -uroot -p1 test2 boy girl > d:/test_boyandgirl.sql
3. 备份一个库
   mysqldump -uroot -p1 -B test2 > d:/test2.sql
4. 备份多个库
   mysqldump -uroot -p1 -B test test2 > d:/test_test2.sql
5. 备份所有库
   mysqldump -uroot -p1 -A > d:/alldatabase.sql

恢复：

1. 登陆到mysql命令行
2. 恢复表级数据（先选择库）
   source d:/test_stu.sql
   source d:/test_boyandgirl.sql
3. 恢复库级数据
   source D:/test2.sql
4. 不登陆到mysql命令行
5. 恢复表级文件
   mysql -uroot -p1 test2 < d:/test_stu.sql
6. 恢复库级文件
7. mysql -uroot -p1 < d:/test2.sql

工具备份：（多数都收费）
可视化界面软件也可以备份：比如phpMyAdmin(借助与Apache)。Navicat