mysql基本语法学习小结

一灰灰blogDBMysqlMysql约 2452 字大约 8 分钟

本篇将主要集中在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常用函数open in new window
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;
Loading...
鄂ICP备18017282号
Copyright © 2024 一灰灰blog