mysql之基本语法

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

I. 数据库管理相关

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

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

1. 创建database

create database test

2. 切换databasae

user test

3. 删除database

drop databse test

4. 显示所有数据库

show databases

II. 表相关

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

1. 创建表

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='自定义表'

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

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进行相关操作时,非常常见的一个命令就是如何查看表的数据结构,有几个命令

desc table_name;

输出格式如下:

+-----------+---------------------+------+-----+---------+----------------+
| 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)

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

show create table tableName\G

输出如下

*************************** 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. 修改表

重命名表名

rename table `oldTableName` to `newTableName`

新加字段

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

修改列

-- 修改类型
alter table newuser modify newcol text not null;


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

删除列

alter table newuser drop newcol2;

删除表

drop table newuser;

4. 增删改查

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

a. 插入一条数据

-- 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用于查询,先给出一个最基本的,下面再详细说明

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

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

+--------+----------+----------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+
| 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 语法实现

update newuser set nickname='newNickName' where userId=3;

-- 再次查询验证

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

输出结果

+--------+----------+-------------+----------+---------+---------------+-------+------+-------+-----------+------------+------------+
| 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将记录彻底抹掉),更多的是采用逻辑删除的方案(至少还有恢复的可能,而且数据都是宝贵的,虽然大部分时候我们都没有去挖掘,但保存着总比丢掉好)

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

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 '%灰%';
    • 输出:
```
+--------+-----------+
| 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常用函数
select username,from_unixtime(created) from newuser limit 1;

--- 下面为输出

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

2. 跨表查询

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

简单的多表查询方式

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

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

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

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

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


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

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

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

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

一般多表查询可划分为:

a.交叉连接查询

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

SELECT employee.name,dept.name

FROM employee,dept;

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

多表查询的步骤:

1)确定查询哪些表

2)确定查询哪些字段

3)确定连接条件(规则:条件=表数量-1)

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

另一种语法

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

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

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

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

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

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

原文链接:https://liuyueyi.github.io/hexblog/2018/03/22/mysql%E5%9F%BA%E6%9C%AC%E8%AF%AD%E6%B3%95%E5%AD%A6%E4%B9%A0%E5%B0%8F%E7%BB%93/

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据

mysql联合索引详解

上一篇文章:mysql数据库索引优化 比较简单的是单列索引(b+tree)。遇到多条件查询时,不可避免会使用到多列索引。联合索引又叫复合索引。 b+tree结构...

1.7K9
来自专栏chenssy

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

1003
来自专栏PHP在线

MySQL索引入门简述

用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,MYSQL必须从第一条记录开始读完整个表,直到找出相关的行。表越大,花...

1513
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战五——SQL查询

Oracle SQL SQL学习是重点,请仔细阅读。 ? O Oracle 结构化查询语言(Structured Query Language)简称SQL(...

3464
来自专栏数据库

mysql数据操作语句

?imageMogr2/blur/1x0/quality/75|watermark/1/image/aHR0cDovL29zNzhmNGhueS5ia3QuY2...

1995
来自专栏杨建荣的学习笔记

MySQL关于数据字典的一个疑问

今天看着MySQL的数据字典,突然想到一个问题:为什么MySQL数据字典 information_schema中的表名是大写,而performance_sche...

3668
来自专栏抠抠空间

MySQL之表的约束

一 介绍 约束条件与数据类型的宽度一样,都是可选参数 作用:用于保证数据的完整性和一致性 主要分为: PRIMARY KEY (PK) 标识该字段为该表的...

2926
来自专栏Linux运维学习之路

MySQL索引

索引管理 索引是什么? 索引就好比一本书的目录,它会让你更快的找到内容; 让获取的数据更有目的性,从而提高数据库检索数据的性能; 索引建立在表的列上(字段)。 ...

4645
来自专栏IT技术精选文摘

MySQL的索引是什么?怎么优化?

1044
来自专栏技术博客

Oracle初级性能优化总结

  关于对Oracle数据库查询性能优化的一个简要的总结。 从来数据库优化都是一项艰巨的任务。对于大数据量,访问频繁的系统,优化工作显得尤为重要。由于Oracl...

1483

扫码关注云+社区

领取腾讯云代金券