本篇将主要集中在mysql的使用上,包括如何创建标,如何进行insert,update,select,delete,以及一些常见的sql中关键字的使用姿势

I. 数据库管理相关

首先是从结构上知晓,一般的关系型数据库,先创建database(数据库), 然后可以在database中创建多个table(表)

通常,在业务稍微大一点的公司而言,不会把所有的数据都放在一个database中,相反会根据不同的业务,创建不同的database,然后在各自的database中维护自己的表,好处就是不会相互影响,后续扩容也方便

1. 创建database

1
create database test

2. 切换databasae

1
user test

3. 删除database

1
drop databse test

4. 显示所有数据库

1
show databases

II. 表相关

主要的操作都是针对表来的,因为数据就是挂在这个下面的

1. 创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `newuser` (
`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`username` varchar(30) DEFAULT '' COMMENT '用户登录名',
`nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称',
`password` varchar(50) DEFAULT '' COMMENT '用户登录密码 & 密文根式',
`address` text COMMENT '用户地址',
`email` varchar(50) NOT NULL DEFAULT '' COMMENT '用户邮箱',
`phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户手机号',
`img` varchar(100) DEFAULT '' COMMENT '用户头像',
`extra` text,
`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`created` int(11) NOT NULL,
`updated` int(11) NOT NULL,
PRIMARY KEY (`userId`),
KEY `idx_username` (`username`),
KEY `idx_nickname` (`nickname`),
KEY `idx_email` (`email`),
KEY `idx_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='自定义表'

创建表的规则还是比较简单的,一般语法是:

1
2
3
4
5
6
create table tableName(
列名 + 列类型 + NOT NULL(可选,表示这个字段不能为空) + DEFAULT '' (可选,表示默认填充的数据) + COMMENT (后面加上这一列的注释),
...
PRIMARY KEY (`id`), // 这个指定主键
KEY `idx_firstId` (`name`) // 这个是指定索引
) ENGINE=InnoDB (指定存储引擎) AUTO_INCREMENT=1 (自增开始值) DEFAULT CHARSET=utf8 (默认编码) COMMENT='自定义表';

需要注意一点,一个表的设计时,最好不要让某一列可以为null,而且良好的习惯是加上DEFALUT默认值,加上列的注释(特别是type的取值固定为1,2,3,4时,尽量在说明中写上每个值的含义)

2. 显示表信息

如果我们是在控制台中来上mysql进行相关操作时,非常常见的一个命令就是如何查看表的数据结构,有几个命令

1
desc table_name;

输出格式如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| userId | bigint(20) | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | MUL | | |
| nickname | varchar(30) | NO | MUL | | |
| password | varchar(50) | YES | | | |
| address | text | YES | | NULL | |
| email | varchar(50) | NO | MUL | | |
| phone | bigint(20) | NO | MUL | 0 | |
| img | varchar(100) | YES | | | |
| extra | text | YES | | NULL | |
| isDeleted | tinyint(1) unsigned | NO | | 0 | |
| created | int(11) | NO | | NULL | |
| updated | int(11) | NO | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

如果我希望获取这个表的建表语句,方便直接创建表,也可以用下面的命令

1
show create table tableName\G

输出如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
*************************** 1. row ***************************
Table: newuser
Create Table: CREATE TABLE `newuser` (
`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`username` varchar(30) DEFAULT '' COMMENT '用户登录名',
`nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称',
`password` varchar(50) DEFAULT '' COMMENT '用户登录密码 & 密文根式',
`address` text COMMENT '用户地址',
`email` varchar(50) NOT NULL DEFAULT '' COMMENT '用户邮箱',
`phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户手机号',
`img` varchar(100) DEFAULT '' COMMENT '用户头像',
`extra` text,
`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`created` int(11) NOT NULL,
`updated` int(11) NOT NULL,
PRIMARY KEY (`userId`),
KEY `idx_username` (`username`),
KEY `idx_nickname` (`nickname`),
KEY `idx_email` (`email`),
KEY `idx_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3. 修改表

重命名表名

1
rename table `oldTableName` to `newTableName`

新加字段

1
2
-- 修改类型
alter table newuser add newcol varchar(10) not null default '' comment '新加的列';

修改列

1
2
3
4
5
6
-- 修改类型
alter table newuser modify newcol text not null;


-- 修改名
alter table newuser change `newcol` `newcol2` text;

删除列

1
alter table newuser drop newcol2;

删除表

1
drop table newuser;

4. 增删改查

对DB的操作,基本上就四种了,增删改查,甚至绝大多数的业务都可以用简单的db的增删改查来实现

a. 插入一条数据

1
2
3
4
-- insert into table_name(`列名`, `列名`) values('插入值', '插入值');

insert into newuser(`username`, `nickname`, `password`, `address`, `email`, `phone`, `img`, `extra`, `isDeleted`, `created`, `updated`)
values('insert', 'insert', 'insert', 'test', 'test@test.com', 123, 'img', '', 0, 1521638764, 1521638764);

b. 查询数据

select用于查询,先给出一个最基本的,下面再详细说明

1
select * from newuser where username='insert' limit 1;

上面表示查询 username为insert的记录,输出结果(也就是刚才插入的那一条数据)

1
2
3
4
5
+--------+----------+----------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+
| userId | username | nickname | password | address | email | phone | img | extra | isDeleted | created | updated |
+--------+----------+----------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+
| 3 | insert | insert | insert | test | test@test.com | 123 | img | | 0 | 1521638764 | 1521638764 |
+--------+----------+----------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+

c. 修改数据

将之前插入的记录中,nickname 修改成 ‘newNickName’, 借助 update set 语法实现

1
2
3
4
5
update newuser set nickname='newNickName' where userId=3;

-- 再次查询验证

select * from newuser where username='insert' limit 1;

输出结果

1
2
3
4
5
+--------+----------+-------------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+
| userId | username | nickname | password | address | email | phone | img | extra | isDeleted | created | updated |
+--------+----------+-------------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+
| 3 | insert | newNickName | insert | test | test@test.com | 123 | img | | 0 | 1521638764 | 1521638764 |
+--------+----------+-------------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+

d. 将刚才的数据删掉

说明,在实际的生产环境中,一般很少物理删除(即执行delete将记录彻底抹掉),更多的是采用逻辑删除的方案(至少还有恢复的可能,而且数据都是宝贵的,虽然大部分时候我们都没有去挖掘,但保存着总比丢掉好)

物理删除的语法比较简单,但是需要额外小心,一不小心删错了,说不准就得卷铺盖滚蛋了

1
2
3
4
5
6
7
delete from newuser where userId=3 limit 1;

-- 再次查询验证

select * from newuser where username='insert' limit 1;

-- 输出: Empty set (0.00 sec)

III. 玩出花的查询语句

1. 基本查询

写sql而言,最常见的,也是最复杂的就是写各种查询了,根据各种不同的条件查询检索结果,大概可以区分以下几种

  • 简单查询:知道确切的检索条件
    • where xxx=xxx 相等的判断
    • where xxx<>xxx 不等的判断
    • where id in (xxx, xxx, xxx) 满足集合的判断
    • where xxx=xxx and yyy=yyy 条件同时满足
    • where xxx=xxx or yyy=yyy 条件满足一个即可
    • where id>10 or id<5 范围判断 >, <, >=, <=
    • where name is null 判空 is null, =’’, 非空 is not null, <>’’
  • 模糊查询: like 语法,

    • %:替代任意个字符
    • _:替代一个字符
    • 如: select userId,username from newuser where username like '%灰%';
    • 输出:

      1
      2
      3
      4
      5
      6
      +--------+-----------+
      | userId | username |
      +--------+-----------+
      | 1 | 大灰狼 |
      | 2 | 小灰灰 |
      +--------+-----------+
  • 条件限制

    • where 1=1 limit 10; 限制最多查询出来的条数
    • where 1=1 limit 1, 2; 分页查询
    • group by username; 分组
    • order by userId desc; 排序: desc倒排,asc 正排
    • select distinct(nickname) from xxx; 去重
    • having count(*) > 2; 分组之后再筛选
  • 执行计算
    • update phone=phone+1 limit 1; 直接实现数值计算
    • count(*) 统计总数
    • sum() 统计和
    • max() 最大值
    • min() 最小值
    • avg() 平均值
  • 常用函数
    • abs() 返回绝对值
    • bin() 返回二进制 oct() 返回八进制, hex() 返回十六进制
    • exp() 返回e的n次方
    • greatest(x1, x2... xn) least(x1, x2, ...n) 返回最大最小
    • ln(x) 返回x的自然对数
    • log(x, y)
    • mod(x, y) 返回x%y的模(余数)
    • rand() 返回0-1内的随机值
    • floor(x) 反后小于x的最大整数
    • ceiling(x) 返回大于x的最小整数
    • round(x, y) 返回x的四舍五入的有y位小数的值
    • turncate(x,y) 截断为y位小数
    • sign(x)
    • sqrt(x) 平方根
    • concat(s1, s2...) 字符串拼接
    • left(str, x) str的左x个字符 right(str, x)
    • length(str) 返回字符串的长度
    • trim(str) 去掉空格
    • from_unixtime 将时间戳转日期
    • 更多参考:MySQL常用函数
1
2
3
4
5
6
7
8
9
select username,from_unixtime(created) from newuser limit 1;

--- 下面为输出

-- +-----------+------------------------+
-- | username | from_unixtime(created) |
-- +-----------+------------------------+
-- | 大灰狼 | 2016-09-25 00:00:00 |
-- +-----------+------------------------+

2. 跨表查询

当设计到查询多张表的结果时,往往是比较麻烦的

简单的多表查询方式

1
select col1, col2 from table1, table2 where table1.col1 = table2.col2 limit 10;

主要就是利用两个表中的关联的列进行联合查询,也就是说,当查询涉及到多表时,那么这些表肯定是有沟通的桥梁的(一般是某一张表的主键是另一张表的某一列)

举个小例子,查询商品评价数为1的商品(假设评价是一张表,商品也是一张表),那么关联的主键就是商品ID了

如果是分开查询,那么应该是

1
2
3
4
5
6
7

-- 查询出评价总数为1的10条评价
select * from Rate group by itemId having count(*) =1 limit 10;


-- 查询对应的商品信息, 上面的结果就是下面()中的内容
select * from Item where itemId in (xxx, xxx);

那么换成一条sql,可以怎么写?

简单的嵌套方案:(有点像是硬把多条语句写成一条)

1
2
select * from Item where itemId in 
(select itemId from Rate group by itemId having count(*)=1 limit 10);

一般多表查询可划分为:

a.交叉连接查询

需求:查询员工及其部门名称

1
2
3
SELECT employee.name,dept.name

FROM employee,dept;

b.内连接查询(使用最多)

多表查询的步骤:

1)确定查询哪些表
2)确定查询哪些字段
3)确定连接条件(规则:条件=表数量-1)

1
2
3
SELECT employee.name,dept.name
FROM employee,dept
WHERE employee.deptId=dept.id;

另一种语法

1
2
3
4
SELECT e.name,d.name
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;

c.左外连接查询(左表数据全部显示,如果右边不满足,则显示null)

需求:查询部门及其部门的员工

1
2
3
4
SELECT d.name,e.name
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;

d.右外连接查询(右表数据全部显示,如果左边不满足,则显示null)

1
2
3
4
SELECT d.name,e.name
FROM employee e
RIGHT OUTER JOIN dept d
ON e.deptId=d.id;

IV. 其他

个人博客: Z+|blog

基于hexo + github pages搭建的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛

声明

尽信书则不如,已上内容,纯属一家之言,因本人能力一般,见识有限,如发现bug或者有更好的建议,随时欢迎批评指正

扫描关注

QrCode