Mysql 基础篇

  • 目录
  • 知识扫盲
  • 基础语句
  • 主键、外键
  • 修改表
  • 分组与聚合
  • 连表查询
  • 其它查询
  • shell终端获取数据

数据库操作无非就是insert`deleteupdateselete,这篇博客对mysql`数据库语句做个总结记录便于学习

  • 知识扫盲

什么是事务: 原子性操作(不可拆分)就是要么全成功,要么全失败。

mysql语句的注释符是 --,在SQL注入或别的场景中语句形如:username=”root” and 1 =1 – and password=’123’
很明显 username="root" and 1 =1将永远成立, and password=’123’ 部份将被注释,这点需要注意。

基础语句

基础语句过于简单,这里就简单逻列下。

创建数据库

1
CREATE DATABASE db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

创建一张表
注意:auto_increment表示自增,primary key 表示id 这列为主键
default charset utf8指定默认字符集
engine=innodb指定因默认引警

1
2
3
4
create table tb1(
id int not null auto_increment primary key,
name char(20) not null,
age int) engine=innodb default charset utf8;

插入数据

1
insert into tb1(name,age) values('wxq',20),('pp',18);

更改数据

1
update tb1 set age=25  where name='wxq';

删除某条数据

1
delete from tb1 where id>1;

删除表内所有数据

1
2
delete from 表名      # 不会删除自增序列
truncate table 表名 # 会删除自增序列,再次插入数据将从0开始,删除更辙底

删除表

1
drop table tb1

主键

1
2
3
4
5
主键
create table tb1(
id int not null auto_increment primary key,
name char(20) not null,
age int) engine=innodb default charset utf8;
1
2
3
4
5
6
组合主键
create table tb1(
nid int not null,
num int not null,
primary key(nid,num)
)

外键

1
constraint fk_任意字符 foreign key (department_id自己字段) references  department外面的表(nid外面表的字段)
1
2
3
4
5
6
7
8
外键(约束):一对多
create table tb1(
id int not null primary key,
name char(32) null ,
age int not null,
department_id int not null
constraint fk_department_nid foreign key (department_id) references department(nid)
)

双向外键(约束):多对对
创建主机与部门之间的多对多关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table host (
id int not null auto_increatement primary key,
host char(20) not null );

create table department(
id int not null auto_increatement primary key,
title char(20) not null
);

create table de_2_host (
id int not null auto_increatement primary key,
hid int not null,
did int not null,
constraint fk_hid_host foreign key (hid) references host(id),
constraint fk_did_department foreign key (did) references department(id)
)

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
添加列:alter table 表名 add 列名 类型
删除列:alter table 表名 drop column 列名
修改列:
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名,类型

添加主键:
alter table 表名 add primary key(列名);
删除主键:
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;

添加外键:alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
删除外键:alter table 表名 drop foreign key 外键名称

修改默认值:ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值:ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
1
2
3
4
show variables like '%char%';       查看整个mysql字符集
show create table migrate_version; 查看表字符集
alter table migrate_version character set utf8; 设置表字符集或者如下条:
alter table migrate_version default character set utf8 collate utf8_general_ci;

分组与聚合

group by

- 对列进行聚合 sum(sid),max(sid),min(sid),count(sid)
- 聚合条件过滤,having count(sid) > 7
1
2
3
4
5
6
7
8
9
分组
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表 where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid

select num from 表 group by num having max(id) > 10

特别的:group by 必须在where之后,order by之前

连表查询

示例

1
2
3
4
select  A.sid,A.sname,B.caption
from student as A
left join class as B
on A.class_id = class.cid;

  • left join 表名 on 多出的数据将会出现NULL数据
  • rigth join 表名 on 相对left 只是表位置变化,其它没有区别
  • inner join 表名 on 不会出现NULL数据,没有关联的数据将不会显示
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
连表
无对应关系则不显示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid

无对应关系则不显示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid

A表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid

B表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid

其它查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
a、条件
select * from 表 where id > 1 and name != 'wxq' and num = 12;

select * from 表 where id between 5 and 16;

select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select distinct name from 表 where id in (select nid from 表) # distinct重复数所将只显示一条

b、通配符
select * from 表 where name like 'wxq%' - wxq开头的所有(多个字符串)
select * from 表 where name like 'wx_' - wx开头的所有(一个字符)

c、限制
select * from 表 limit 5; - 前5行
select * from 表 limit 4,5; - 从第4行开始的5行
select * from 表 limit 5 offset 4 - 从第4行开始的5行

d、排序
select * from 表 order by 列 asc - 根据 “列” 从小到大排列
select * from 表 order by 列 desc - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序

e、组合
组合,自动处理重合
select nickname
from A
union
select name
from B

组合,不处理重合
select nickname
from A
union all
select name
from B

shell终端获取数据

下面介绍在shell终端获取mysql的几种方法

用法一:

1
2
3
4
5
6
[root@mysql ~]# mysql -p123 -e "show global status where variable_name='uptime'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 8025 |
+---------------+-------+

用法二:

1
2
3
4
[root@mysql ~]# mysql -p123 -N -e "show global status where variable_name='uptime'"
+--------+------+
| Uptime | 8029 |
+--------+------+

用法三:

1
2
[root@mysql ~]# mysql -p123 -s -N -e "show global status where variable_name='uptime'"
Uptime 8037

用法四:

1
2
[root@mysql ~]# echo "show global status where variable_name='uptime';" | mysql -N -p123
Uptime 8072

1
2
3
4
5
6
7
8
9
# echo  "show databases;" | mysql -h 127.0.0.1 -uroot -A -p123 | tail -n +2  | head -n 2
-A 表示不自动rehash
tail -n +2 表示从第二行开始打印
head -n 2表示打印前2行

# echo "use nova01 ; show tables;" | mysql -uroot -padmin
# mysql -uroot -padmin -S /var/run/mysqld/mysqld.sock -e "use nova01;show tables;"
# mysql -uroot -padmin -S /var/run/mysqld/mysqld.sock \
-e "grant all on *.* to user1@'localhost' identified by \"user1\";"