MySQL的基本操作
MySQL数据库
关系型数据库和非关系型数据库
关系型数据库的特点：
1.数据以表格的形式出现；
2.每行是各种记录名称；
3.每列是记录名称所对应的数据域；
4.许多的行和列组成一张表单；
5若干的表单组成数据库。
MySQL数据库基本操作
连接数据库：
mysql：采用匿名账号和密码登陆本机服务。
mysql -h localhost -u root -proot：采用root账号和root密码登陆本机服务。
注：localhost指本地主机，即MySQL数据库所在的那台主机。
mysql -u root -p：推荐方式默认登陆本机
Enter password：***
mysql -u root -p mydb：直接进入mydb数据库的方式登陆
SQL语句中的快捷键：
\G：格式化输出（文本式）
\s：查看服务器端信息
\c：结束命令输入操作
\q：退出当前sql命令行模式
\h：查看帮助
数据库操作：
show databases; 查看当前用户下的所有数据库
create database [if not exists] 数据库名; 创建数据库
use 数据库名; 选择进入数据库
show create database 数据库名\G; 查看建库语句
select database(); 查看当前所在的数据库位置
drop database [if exists] 数据库名; 删除一个数据库
数据表操作：
show tables; 查看当前库下的所有数据表
desc(describe) 数据表; 查看数据表的结构
show columns from 数据表; 查看数据表的结构
show create table 表名\G 查看建表语句
create table 表名(
name varchar(16) not null,
age int); 创建数据表
drop table if exists 数据表名; 删除数据表
数据操作（增、删、改）：
增加数据
insert into stu(name, age, sex) values(‘zhangsan’, 22, ‘male’);
不指定字段名添加一条数据
insert into stu values(‘zhangsan’, 22, ‘male’);
指定部分字段名添加一条数据
insert into stu(name, age) values(‘zhangsan’, 22);
批量添加数据
insert into stu(name, age, sex) values(‘zhangsan’, 22, ‘male’), (‘lisi, 21, ‘female’);
删除数据
delete from stu; 清空表中所有数据
delete from stu where name = ‘zhangsan’; 有条件的删除表中部分数据
修改数据
格式：update 表名 set 字段1=值1, 字段2=值2,… where 条件
update stu set age=26; 无条件的修改所有数据
update stu set age=26 where name = ‘zhangsan’; 有条件的修改部分数据
update stu set age=26, sex=’female’ where name = ‘zhangsan’; 同时修改两个字段信息
MySQL数据类型
MySQL的数据类型分为三个类：数值类型、字串类型、日期类型 。 还有一个特殊的值：NULL。
数值类型：
*tinyint(1字节) 0~255 或 -128~127
smallint(2字节)
mediumint(3字节)
*int(4字节)
bigint(8字节)
*float(4字节) float(6,2)
*double(8字节)
decimal(自定义)字串形数值
字串类型：
普通字串类型
*char 定长字串 char(8)
*varchar 可变字串 varchar(8)
二进制类型
tinyblob
blob
mediumblob
longblob
文本类型
tinytext
*text 常用于<textarea></textarea>
mediumtext
longtext
*enum枚举
set集合
时间和日期类型：
date 年月日
time 时分秒
*datetime 年月日时分秒
timestamp 时间戳
year 年
NULL值：
NULL意味着“没有值”或“未知值”
可以测试某个值是否为NULL
不能对NULL值进行算术计算
0或NULL都意味着假，其余值都意味着真
表的字段约束
unsigned 无符号(正数)
zerofill 前导零填充
auto_increment 自增
default 默认值
not null 非空
primary key 主键（非null并不重复）
unique 唯一性（可以为null但不重复）
index 常规索引
建表语句格式
create table 表名(
字段名 类型 [字段约束],
字段名 类型 [字段约束],
字段名 类型 [字段约束],
...
);
create table stu(
-> id int unsigned not null auto_increment primary key,
-> name varchar(8) not null unique,
-> age tinyint unsigned,
-> sex enum('m','w') not null default 'm',
-> classid char(6)
-> );
MySQL的运算符
算术运算符：+ - * / %
比较运算符：= > < >= <= <> !=
数据库特有的比较：in，not in, is null, is not null, like, between ... and
逻辑运算符：and or not
修改表结构:
格式： alter table 表名 action（更改选项）;
添加字段：alter table 表名 add 字段名信息
alter table stu add class char not null; 在stu表的最后增加一个class字段。
alter table stu add hobby varchar(255) default null after name; 在stu表的name字段后增加一个hobby字段。
alter table stu add school varchar(255) not null first; 在stu表的最前面增加一个school字段。
删除字段：alter table 表名 drop 被删除的字段名
alter table stu drop score; 删除stu表的score字段。
修改字段：alter table 表名 change[modify] 被修改后的字段信息
注意：change可以修改字段名， modify 不能修改字段名。
alter table stu modify age tinyint not null default 0; 修改stu表中的age字段信息（使用modify只修改字段信息，不修改字段名）。
alter table stu change hobby gender enum('m', 'w') not null default 'm'; 将stu表中的hobby字段改为gender字段（使用change可修改字段名和信息）。
添加和删除索引：
alter table stu add index index_school(school); 为stu表中的school字段添加普通索引，索引名为index_school。
alter table stu add unique unique_uid(uid); 为stu表中的uid字段添加唯一性索引，索引名为unique_uid。
alter table stu drop index unique_uid; 将stu表中的unique_uid索引删除。
更改表名称：alter table stu rename as students; 将旧表明stu改为新表明students。
更改auto_increment的初始值：alter table students auto_increment=1;
更改表类型：alter table students engine='InnoDB';
MySQL数据库中的表类型一般常用两种：MyISAM和InnoDB。
MySQL单表查询
格式：
select [字段列表]|* from 表名
[where 搜索条件]
[group by 分组字段 [having 子条件]]
[order by 排序 asc|desc]
[limit 分页参数]
无条件查询：
# 查询所有字段信息 > select * from stu; +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 2 | lisi | w | python4 | 20 | | 3 | wangwu | w | python3 | 21 | | 4 | zhaoliu | m | python5 | 22 | | 5 | yang | w | python6 | 26 | | 6 | wagbon | m | python5 | 25 | | 7 | aaa | w | python4 | 22 | | 8 | bbb | m | python5 | 23 | | 9 | ccc | w | python6 | 21 | | 10 | dd02 | m | python3 | 20 | +----+----------+--------+---------+-----+ # 查询部分字段 > select id, name from stu; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | zhaoliu | | 5 | yang | | 6 | wagbon | | 7 | aaa | | 8 | bbb | | 9 | ccc | | 10 | dd02 | +----+----------+ # 查询部分字段，并增加字段查询 > select id, name, age, age+5 from stu; # 得到5年后的年龄 +----+----------+-----+-------+ | id | name | age | age+5 | +----+----------+-----+-------+ | 1 | zhangsan | 20 | 25 | | 2 | lisi | 20 | 25 | | 3 | wangwu | 21 | 26 | | 4 | zhaoliu | 22 | 27 | | 5 | yang | 26 | 31 | | 6 | wagbon | 25 | 30 | | 7 | aaa | 22 | 27 | | 8 | bbb | 23 | 28 | | 9 | ccc | 21 | 26 | | 10 | dd02 | 20 | 25 | +----+----------+-----+-------+ # 也可以在查询的时候重命名字段名 > select id, name as stu_name, age, age+5 age5 from stu; # 字段name重命名为stu_name, +----+----------+-----+------+ # 字段age+5重命名为age5，关键字as可省略 | id | stu_name | age | age5 | +----+----------+-----+------+ | 1 | zhangsan | 20 | 25 | | 2 | lisi | 20 | 25 | | 3 | wangwu | 21 | 26 | | 4 | zhaoliu | 22 | 27 | | 5 | yang | 26 | 31 | | 6 | wagbon | 25 | 30 | | 7 | aaa | 22 | 27 | | 8 | bbb | 23 | 28 | | 9 | ccc | 21 | 26 | | 10 | dd02 | 20 | 25 | +----+----------+-----+------+
where条件查询（基于MySQL 的运算符）
> select * from stu where id=5; # 查询id=5的学生信息 +----+------+--------+---------+-----+ | id | name | gender | class | age | +----+------+--------+---------+-----+ | 5 | yang | w | python6 | 26 | +----+------+--------+---------+-----+ > select * from stu where id in (2, 4, 7); # 查询id分别为2、4、7的学生信息 +----+---------+--------+---------+-----+ | id | name | gender | class | age | +----+---------+--------+---------+-----+ | 2 | lisi | w | python4 | 20 | | 4 | zhaoliu | m | python5 | 22 | | 7 | aaa | w | python4 | 22 | +----+---------+--------+---------+-----+ > select * from stu where id between 3 and 6; # 查询id在3-6之间的学生信息 +----+---------+--------+---------+-----+ | id | name | gender | class | age | +----+---------+--------+---------+-----+ | 3 | wangwu | w | python3 | 21 | | 4 | zhaoliu | m | python5 | 22 | | 5 | yang | w | python6 | 26 | | 6 | wagbon | m | python5 | 25 | +----+---------+--------+---------+-----+ > select * from stu where id>3 and id < 9; # 查询id大于3小于9的学生信息 +----+---------+--------+---------+-----+ | id | name | gender | class | age | +----+---------+--------+---------+-----+ | 4 | zhaoliu | m | python5 | 22 | | 5 | yang | w | python6 | 26 | | 6 | wagbon | m | python5 | 25 | | 7 | aaa | w | python4 | 22 | | 8 | bbb | m | python5 | 23 | +----+---------+--------+---------+-----+ > select * from stu where class='python5' and gender='m'; # 查询班级为python5性别为m的学生信息 +----+---------+--------+---------+-----+ | id | name | gender | class | age | +----+---------+--------+---------+-----+ | 4 | zhaoliu | m | python5 | 22 | | 6 | wagbon | m | python5 | 25 | | 8 | bbb | m | python5 | 23 | +----+---------+--------+---------+-----+
like运算符模糊查询
> select * from stu where name like "%a%"; # 模糊查询名字中间有字母a的学生 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 3 | wangwu | w | python3 | 21 | | 4 | zhaoliu | m | python5 | 22 | | 5 | yang | w | python6 | 26 | | 6 | wagbon | m | python5 | 25 | | 7 | aaa | w | python4 | 22 | +----+----------+--------+---------+-----+ > select * from stu where name like "%ang%"; # 模糊查询名字中间有字母ang的学生 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 3 | wangwu | w | python3 | 21 | | 5 | yang | w | python6 | 26 | +----+----------+--------+---------+-----+ > select * from stu where name like "____"; # 模糊查询名字为四个字符的学生，一个_表示一个字符 +----+------+--------+---------+-----+ | id | name | gender | class | age | +----+------+--------+---------+-----+ | 2 | lisi | w | python4 | 20 | | 5 | yang | w | python6 | 26 | | 10 | dd02 | m | python3 | 20 | +----+------+--------+---------+-----+ > select * from stu where name like "z%"; # 模糊查询名字以字母z开头的学生 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 4 | zhaoliu | m | python5 | 22 | +----+----------+--------+---------+-----+ > select * from stu where name like "%g"; # 模糊查询名字以字母g结尾的学生 +----+------+--------+---------+-----+ | id | name | gender | class | age | +----+------+--------+---------+-----+ | 5 | yang | w | python6 | 26 | +----+------+--------+---------+-----+
聚合函数：count()、max()、min()、sum()、avg()
# count()函数统计非空数据条数 > select count(*) from stu; # 统计学生人数 +----------+ | count(*) | +----------+ | 10 | +----------+ > select count(id) from stu; # 通过非空字段统计 +-----------+ | count(id) | +-----------+ | 10 | +-----------+ # max()、min()、sum()、avg()分别统计某字段的最大值、最小值、总和、平均值 > select count(id), max(age), min(age), sum(age), avg(age) from stu; +-----------+----------+----------+----------+----------+ # 统计了学生的最大年龄、最小年龄、年龄总和、平均年龄 | count(id) | max(age) | min(age) | sum(age) | avg(age) | +-----------+----------+----------+----------+----------+ | 10 | 26 | 20 | 220 | 22.0000 | +-----------+----------+----------+----------+----------+
group by分组查询（配合聚合函数使用）
> select class from stu group by class; # 按班级分组进行查询 +---------+ | class | +---------+ | python3 | | python4 | | python5 | | python6 | +---------+ 配合着聚合函数进行分组查询 > select class, count(*) from stu group by class; # 按班级分组查询并统计每个班级的人数 +---------+----------+ | class | count(*) | +---------+----------+ | python3 | 3 | | python4 | 2 | | python5 | 3 | | python6 | 2 | +---------+----------+ > select class, count(*), avg(age) from stu group by class; +---------+----------+----------+ # 按班级分组查询并统计每个班级的人数和每个班级的平均年龄 | class | count(*) | avg(age) | +---------+----------+----------+ | python3 | 3 | 20.3333 | | python4 | 2 | 21.0000 | | python5 | 3 | 23.3333 | | python6 | 2 | 23.5000 | +---------+----------+----------+ 分组查询并加having子条件 > select class, count(*) as num from stu group by class having num>2; +---------+-----+ # 按班级分组查询并统计每个班级的人数，且只统计人数大于2的班级 | class | num | +---------+-----+ | python3 | 3 | | python5 | 3 | +---------+-----+
order by排序查询
> select * from stu order by age; # 按年龄排序查询，默认为升序排序 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 10 | dd02 | m | python3 | 20 | | 2 | lisi | w | python4 | 20 | | 3 | wangwu | w | python3 | 21 | | 9 | ccc | w | python6 | 21 | | 4 | zhaoliu | m | python5 | 22 | | 7 | aaa | w | python4 | 22 | | 8 | bbb | m | python5 | 23 | | 6 | wagbon | m | python5 | 25 | | 5 | yang | w | python6 | 26 | +----+----------+--------+---------+-----+ > select * from stu order by age desc; # 按年龄排序查询，desc指定降序排序 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 5 | yang | w | python6 | 26 | | 6 | wagbon | m | python5 | 25 | | 8 | bbb | m | python5 | 23 | | 4 | zhaoliu | m | python5 | 22 | | 7 | aaa | w | python4 | 22 | | 9 | ccc | w | python6 | 21 | | 3 | wangwu | w | python3 | 21 | | 1 | zhangsan | m | python3 | 20 | | 2 | lisi | w | python4 | 20 | | 10 | dd02 | m | python3 | 20 | +----+----------+--------+---------+-----+ > select * from stu order by age asc; # 按年龄排序查询，asc指定升序排序 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 10 | dd02 | m | python3 | 20 | | 2 | lisi | w | python4 | 20 | | 3 | wangwu | w | python3 | 21 | | 9 | ccc | w | python6 | 21 | | 4 | zhaoliu | m | python5 | 22 | | 7 | aaa | w | python4 | 22 | | 8 | bbb | m | python5 | 23 | | 6 | wagbon | m | python5 | 25 | | 5 | yang | w | python6 | 26 | +----+----------+--------+---------+-----+ > select * from stu order by class asc, age desc; # 按班级升序、年龄降序排序 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 3 | wangwu | w | python3 | 21 | | 1 | zhangsan | m | python3 | 20 | | 10 | dd02 | m | python3 | 20 | | 7 | aaa | w | python4 | 22 | | 2 | lisi | w | python4 | 20 | | 6 | wagbon | m | python5 | 25 | | 8 | bbb | m | python5 | 23 | | 4 | zhaoliu | m | python5 | 22 | | 5 | yang | w | python6 | 26 | | 9 | ccc | w | python6 | 21 | +----+----------+--------+---------+-----+
limit分页查询
> select * from stu limit 3; # 查询前三条数据 +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 2 | lisi | w | python4 | 20 | | 3 | wangwu | w | python3 | 21 | +----+----------+--------+---------+-----+ > select * from stu order by age desc limit 3; # 查询年龄最大的前三条数据 +----+--------+--------+---------+-----+ | id | name | gender | class | age | +----+--------+--------+---------+-----+ | 5 | yang | w | python6 | 26 | | 6 | wagbon | m | python5 | 25 | | 8 | bbb | m | python5 | 23 | +----+--------+--------+---------+-----+ > select * from stu limit 2, 3; # 前两条不要，查询后面的三条数据，即从第三条开始查 +----+---------+--------+---------+-----+ | id | name | gender | class | age | +----+---------+--------+---------+-----+ | 3 | wangwu | w | python3 | 21 | | 4 | zhaoliu | m | python5 | 22 | | 5 | yang | w | python6 | 26 | +----+---------+--------+---------+-----+ # 总的查询，必须按照where、group by、order by、limit的顺序进行查询，条件的顺序不能颠倒。 > select * from stu where class='python3' order by age desc limit 2; +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 3 | wangwu | w | python3 | 21 | | 1 | zhangsan | m | python3 | 20 | +----+----------+--------+---------+-----+
数据库授权、备份和恢复
MySQL数据库的核心库是mysql，里面存放着和用户及授权相关信息的数据表。
> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydemo | | mysql | # MySQL数据库的核心库 | performance_schema | | phpmyadmin | | test | +--------------------+ > select host, user, password from user; # user表中存放着主机、用户、密码等信息 +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | localhost | pma | | +-----------+------+----------+
数据库授权
格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';
> grant all on *.* to zhangsan@'%' identified by '123';
# 授权所有操作，所有数据库的所有数据表给张三，可以用任何主机登陆，密码为123 > select host, user, password from mysql.user; +-----------+----------+-------------------------------------------+ | host | user | password | +-----------+----------+-------------------------------------------+ | localhost | root | | | % | zhangsan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # 新授权的用户 | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | localhost | pma | | +-----------+----------+-------------------------------------------+ # 刷新生效，否则就要重启MySQL服务才可以。 > flush privileges; # 移除一些权限 revoke:删除用户的权限，但不删除用户。 # 删除了整个用户 drop user:删除了整个用户及其权限（包括数据字典中的数据） 格式：drop user '用户名'@'来源' > drop user 'zhangsan'@'%'; # 删除用户zhangsan及其权限 > select host, user, password from mysql.user; +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | localhost | pma | | +-----------+------+----------+
备份（导出）
root@debian:~# mysqldump -u root -p mydemo>mydemo.sql # 数据库导出 Enter password: root@debian:~# ls bitnami-first-boot-123456 mydemo.sql # mydemo数据库被导出 root@debian:~# mysqldump -u root -p mydemo stu>mydemo_stu.sql # 导出数据表 Enter password: root@debian:~# ls bitnami-first-boot-123456 mydemo.sql mydemo_stu.sql # mydemo数据库的stu表被导出
恢复（导入）
root@debian:~# mysql -u root -p mydemo<mydemo.sql # 数据库导入同名库中 Enter password: root@debian:~# mysql -u root -p mydb<mydemo.sql # 数据库导入非同名库中 Enter password: root@debian:~# mysql -u root -p mydb<mydemo_stu.sql # 数据表导入 Enter password: 注意：导入数据库或数据表时，mysql里必须有可以接收的数据库，或者导入文件中有建库语句。
MySQL的多表联查
表之间的关系有：1对1、1对多、多对多。
多表联查的方式：
嵌套查询
where关联查询
join连接查询
左连：left join
右连：right join
内连：inner join
嵌套查询：一个查询的结果是另外sql查询的条件。
> select max(age) from stu; # 查询stu表中最大的年龄 +----------+ | max(age) | +----------+ | 26 | +----------+ > select * from stu where age=(select max(age) from stu); # 嵌套查询 +----+------+--------+---------+-----+ # stu表中最大的年龄为条件，查询stu表中年龄最大的学生信息 | id | name | gender | class | age | +----+------+--------+---------+-----+ | 5 | yang | w | python6 | 26 | +----+------+--------+---------+-----+ > select * from grade where sid=(select id from stu where name='zhangsan'); +----+-----+---------+-------+ # 以zhangsan的id作为grade的sid的查询条件 | id | sid | subject | score | +----+-----+---------+-------+ | 9 | 1 | python | 69 | +----+-----+---------+-------+
where关联查询：关联两个表中的条件。
下面stu表中存放学生信息；grade表中存放学生成绩信息。stu表中的学号id与grade表中的学号sid对应，即stu表中的学号id与grade表中的学号sid相同。
> select * from grade; +----+-----+---------+-------+ | id | sid | subject | score | # 学号sid与stu表中的id对应 +----+-----+---------+-------+ | 1 | 3 | python | 87 | | 2 | 4 | python | 90 | | 3 | 2 | python | 58 | | 4 | 7 | python | 98 | | 5 | 5 | python | 78 | | 6 | 9 | python | 86 | | 7 | 11 | python | 88 | | 8 | 10 | python | 76 | | 9 | 1 | python | 69 | +----+-----+---------+-------+ > select * from stu; +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 2 | lisi | w | python4 | 20 | | 3 | wangwu | w | python3 | 21 | | 4 | zhaoliu | m | python5 | 22 | | 5 | yang | w | python6 | 26 | | 6 | wagbon | m | python5 | 25 | | 7 | aaa | w | python4 | 22 | | 8 | bbb | m | python5 | 23 | | 9 | ccc | w | python6 | 21 | | 10 | dd02 | m | python3 | 20 | | 11 | ee03 | w | python3 | 22 | | 12 | ww04 | w | python3 | 20 | +----+----------+--------+---------+-----+ > select s.id, s.name, g.subject, g.score from stu s, grade as g where s.id=g.sid; +----+----------+---------+-------+ # stu表的id和grade表的sid相同，都表示学生的学号，可关联查询 | id | name | subject | score | +----+----------+---------+-------+ | 3 | wangwu | python | 87 | | 4 | zhaoliu | python | 90 | | 2 | lisi | python | 58 | | 7 | aaa | python | 98 | | 5 | yang | python | 78 | | 9 | ccc | python | 86 | | 11 | ee03 | python | 88 | | 10 | dd02 | python | 76 | | 1 | zhangsan | python | 69 | +----+----------+---------+-------+ > select s.id, s.name, g.subject, g.score from stu s, grade as g where s.id=g.sid order by g.score limit 5; # 加上排序、分页功能 +----+----------+---------+-------+ | id | name | subject | score | +----+----------+---------+-------+ | 2 | lisi | python | 58 | | 1 | zhangsan | python | 69 | | 10 | dd02 | python | 76 | | 5 | yang | python | 78 | | 9 | ccc | python | 86 | +----+----------+---------+-------+ > select s.id, s.name, s.class, g.subject, g.score from stu s, grade as g where s.id=g.sid and s.class='python3'; # 继续加上条件，只查班级为python3的学生信息 +----+----------+---------+---------+-------+ | id | name | class | subject | score | +----+----------+---------+---------+-------+ | 3 | wangwu | python3 | python | 87 | | 11 | ee03 | python3 | python | 88 | | 10 | dd02 | python3 | python | 76 | | 1 | zhangsan | python3 | python | 69 | +----+----------+---------+---------+-------+ > select * from stu where class='python3'; # stu表中python3班级有5条，上面只查出4条 +----+----------+--------+---------+-----+ # 因为where关联查询是查两个表的交集 | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | m | python3 | 20 | | 3 | wangwu | w | python3 | 21 | | 10 | dd02 | m | python3 | 20 | | 11 | ee03 | w | python3 | 22 | | 12 | ww04 | w | python3 | 20 | +----+----------+--------+---------+-----+
join连接查询：join的on后面是连接条件。
> select s.id, s.name, s.class, g.subject, g.score from stu s left join grade g on s.id=g.sid where s.class='python3'; # stu表以学号左联grade表进行查询，左联则以stu表为主+----+----------+---------+---------+-------+ # 显示stu表中所有符合条件的信息，包括空数据 | id | name | class | subject | score | +----+----------+---------+---------+-------+ | 3 | wangwu | python3 | python | 87 | | 11 | ee03 | python3 | python | 88 | | 10 | dd02 | python3 | python | 76 | | 1 | zhangsan | python3 | python | 69 | | 12 | ww04 | python3 | NULL | NULL | # 空数据也显示 +----+----------+---------+---------+-------+ > select s.id, s.name, s.class, g.subject, g.score from stu s right join grade g on s.id=g.sid where s.class='python3'; # 与上面相同的查询，右联则以grade表为主 +----+----------+---------+---------+-------+ # 以grade表为主，stu表中的空数据就不再显示 | id | name | class | subject | score | +----+----------+---------+---------+-------+ | 3 | wangwu | python3 | python | 87 | | 11 | ee03 | python3 | python | 88 | | 10 | dd02 | python3 | python | 76 | | 1 | zhangsan | python3 | python | 69 | +----+----------+---------+---------+-------+ 左联或右联是以from后的表为参照，from后的表为左。 > select s.id, s.name, s.class, g.subject, g.score from stu s inner join grade g on s.id=g.sid where s.class='python3'; # 内联相当于where关联查询，得到两个表的交集 +----+----------+---------+---------+-------+ | id | name | class | subject | score | +----+----------+---------+---------+-------+ | 3 | wangwu | python3 | python | 87 | | 11 | ee03 | python3 | python | 88 | | 10 | dd02 | python3 | python | 76 | | 1 | zhangsan | python3 | python | 69 | +----+----------+---------+---------+-------+
MySQL的其他操作
MySQL的表复制
复制表结构
> create table 目标表名 like 原表名;
复制表数据
> insert into 目标表名 select * from 原表名;
> create table stu2 like stu; # 赋值stu的表结构为stu2 > desc stu2; # 查看stu2和stu相同的结构 +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(16) | NO | | NULL | | | gender | enum('m','w') | NO | | m | | | class | char(8) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 20 | | +--------+---------------------+------+-----+---------+----------------+ > insert into stu2 select * from stu; # 复制全部数据 > insert into stu2 select * from stu where class='python3'; # 有条件的复制部分数据
数据表的索引
目前常用的索引有三种：主键索引（PRI）、唯一性索引（UNI）、普通索引（MUL）。主键索引由primary key创建，唯一性索引由unique创建，普通索引由index创建。
创建索引：create index 索引名 on 表名(字段名)。给数据表的某个字段创建索引。
> create index index_age on stu(age); # 给stu表的age字段创建一个名为index_age的普通索引 > desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(16) | NO | | NULL | | | gender | enum('m','w') | NO | | m | | | class | char(8) | NO | | NULL | | | age | tinyint(3) unsigned | NO | MUL | 20 | | # age字段创建了一个普通索引(MUL) +--------+---------------------+------+-----+---------+----------------+
删除索引：drop index 索引名 on 表名。删除数据表的某个索引。
> drop index index_age on stu; # 删除stu表的index_age索引
MySQL的内置函数
字符串处理函数
*concat(s1,s2,…Sn) 连接s1,s2..Sn为一个字符串
insert(str,x,y,instr)将字符串str从第x位置开始，y字符串的子字符串替换为str
lower(str)将所有的字符串变为小写
upper(str)将所有的字符串变为大写
left(str,x)返回字符串中最左边的x个字符
rigth(str,y)返回字符串中最右边的x个字符
lpad(str,n,pad)用字符串pad对str最左边进行填充，直到长度为n个字符串长度
rpad(str,n,pad)用字符串pad对str最右边进行填充，直到长度为n个字符串长度
trim(str) 去掉左右两边的空格
ltrim(str) 去掉字符串str左侧的空格
rtrim(str) 去掉字符串str右侧的空格
repeat(str,x) 返回字符串str重复x次
replace(str,a,b)将字符串的的a替换成b
strcmp(s1,s2) 比较字符串s1和s2
substring(s,x,y)返回字符串指定的长度
*length(str) 返回值为字符串str 的长度
数值函数
*abs(x) 返回x的绝对值
ceil(x) 返回大于x的最小整数值
floor(x) 返回小于x的最大整数值
mod(x,y) 返回x/y的取余结果
rand() 返回0~1之间的随机数
*round(x,y)返回参数x的四舍五入的有y位小数的值
truncate(x,y) 返回x截断为y位小数的结果
日期和时间函数
curdate() 返回当前日期,按照’YYYY-MM-DD’格式
curtime() 返回当前时间,当前时间以'HH:MM:SS'
*now() 返回当前日期和时间,
*unix_timestamp(date) 返回date时间的unix时间戳
from_unixtime(unix_timestamp[,format]) 返回unix时间的时间
week(date) 返回日期是一年中的第几周
year(date) 返回日期的年份
hour(time) 返回time的小时值
minute(time) 返回日time的分钟值
monthname(date) 返回date的月份
*date_fomat(date,fmt) 返回按字符串fmt格式化日期date值
date_add(date,INTERVAL,expr type) 返回一个日期或者时间值加上一个时间间隔的时间值
*datediff(expr,expr2) 返回起始时间和结束时间的间隔天数
其他常用函数
*database() 返回当前数据库名
version() 返回当前服务器版本
user() 返回当前登陆用户名
inet_aton 返回当前IP地址的数字表示 inet_aton("192.168.80.250");
inet_ntoa(num) 返回当前数字表示的ip inet_ntoa(3232256250);
*password(str) 返回当前str的加密版本
*md5(str) 返回字符串str的md5值
字符串连接(concat函数)查询，将stu表中的class字段和name字段连接在一起进行查询。
> select concat(class, ":", name) from stu limit 5; +--------------------------+ | concat(class, ":", name) | +--------------------------+ | python3:zhangsan | | python4:lisi | | python3:wangwu | | python5:zhaoliu | | python6:yang | +--------------------------+
长度(length函数)查询，查询名字长度为六个字母的数据。
> select * from stu where length(name)=6; # 查询名字长度为六个字母 +----+--------+--------+---------+-----+ | id | name | gender | class | age | +----+--------+--------+---------+-----+ | 3 | wangwu | w | python3 | 21 | | 6 | wagbon | m | python5 | 25 | +----+--------+--------+---------+-----+
随机数(rand函数)查询，产生0-1之间的任意随机数。
> select rand(); # 每次执行，产生一个0-1之间的随机小数 +---------------------+ | rand() | +---------------------+ | 0.24321279324890374 | +---------------------+ > select * from stu order by rand() limit 3; # 随机取三条数据 +----+---------+--------+---------+-----+ | id | name | gender | class | age | +----+---------+--------+---------+-----+ | 9 | ccc | w | python6 | 21 | | 8 | bbb | m | python5 | 23 | | 4 | zhaoliu | m | python5 | 22 | +----+---------+--------+---------+-----+ # 其它函数应用举例。 > select now(); # 获取时间 +---------------------+ | now() | +---------------------+ | 2018-11-17 00:38:05 | +---------------------+ 1 row in set (0.00 sec) > select version(); # 获取数据库版本 +-----------------+ | version() | +-----------------+ | 10.1.36-MariaDB | +-----------------+ > select database(); # 当前所在数据库 +------------+ | database() | +------------+ | mydemo | +------------+
MySQL的事务
1. 关闭自动提交功能（开启手动事务）
> set autocommit=0;
2. 操作数据库（增、删、改、查）
> delete from stu where id>10; # 删 > update stu set gender='w'; # 改 > select * from stu; +----+----------+--------+---------+-----+ | id | name | gender | class | age | +----+----------+--------+---------+-----+ | 1 | zhangsan | w | python3 | 20 | | 2 | lisi | w | python4 | 20 | | 3 | wangwu | w | python3 | 21 | | 4 | zhaoliu | w | python5 | 22 | | 5 | yang | w | python6 | 26 | | 6 | wagbon | w | python5 | 25 | | 7 | aaa | w | python4 | 22 | | 8 | bbb | w | python5 | 23 | | 9 | ccc | w | python6 | 21 | | 10 | dd02 | w | python3 | 20 | +----+----------+--------+---------+-----+
3. 事务回滚(rollback)，当操作数据库发生错误，不进行保存而需要重新操作
> rollback; # 事务回滚，不保存数据。回滚到最开始的位置
事务回滚，还可以设置还原点。
> savepoint p1; # 设置还原点
> rollback to p1; # 回滚到还原点
4. 事务提交(commit)，当操作数据库完成，进行数据库保存
> commit; # 提交并保存数据
5. 开启自动事务提交（关闭手动事务）
> set autocommit=1;
