社区编辑申请
注册/登录
常用数据库 SQL 命令详解(上)
数据库 MySQL
本文主要围绕 Mysql 中常用的语法进行一次梳理和介绍,这些语法大部分也同样适用于其他的数据库,例如 oracle、sqlserver、postgres 等等。

一、简介

本文主要以 Mysql 数据库为基础,对常用 SQL 语句进行一次深度总结!

整体内容,主要有以下几个部分:

  • 库操作
  • 表操作
  • 数据操作
  • 运算符
  • 视图
  • 函数
  • 存储过程
  • 触发器
  • 序列
  • 用户权限

由于整部内容过长,在本篇文章中,主要介绍上半部分内容!

二、库操作

2.1 新增库

创建数据库比较简单,在创建的时候直接指定字符集、排序规则即可!

CREATE DATABASE IF NOT EXISTS `库名` default charset utf8mb4 COLLATE utf8mb4_unicode_ci;

例子:

CREATE DATABASE IF NOT EXISTS test_db default charset utf8mb4 COLLATE utf8mb4_unicode_ci;

2.2 修改库名

数据库修改库名的有三种方法,如果是MyISAM存储引擎,那么可以直接去数据库目录mv就可以了,如果是Innodb完全不行,会提示相关表不存在。

方法一:

RENAME database olddbname TO newdbname

这个语法在 mysql-5.1.7 中被添加进来,到了mysql-5.1.23又去掉了,官方不推荐,会有丢失数据的危险!

方法二:

思路是先创建一个新库,之后将旧库的数据导入到新库,即可完成修改库名!

  • 创建需要改成新名的数据库
  • mysqldum 导出要改名的数据库
  • 删除原来的旧库(确定是否真的需要)

当然这种方法虽然安全,但是如果数据量大,会比较耗时,同时还需要考虑到磁盘空间等硬件成本。

例子:

# 将db1库备份到db1.sql文件
mysqldump -u root -p db1 > /usr/db1.sql;

# 导入备份文件到新库db2
mysql -u root -p db2 < /root/db1.sql;

# 删除旧库(如果真的需要)
DROP DATABASE db1;

方法三:

直接跑一个 shell 脚本!

#!/bin/bash
# 假设将db1数据库名改为db2
# MyISAM直接更改数据库目录下的文件即可

mysql -uroot -p123456 -e 'create database if not exists db2'
list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='db1'")

for table in $list_table
do
mysql -uroot -p123456 -e "rename table db1.$table to db2.$table"
done

其中p123456,p是password的简称,123456表示数据库密码值!

2.3 删除库名

删除库,比较简单,直接删除即可!

DROP DATABASE db1;

2.4 使用库

USE db2;

三、表操作

3.1 创建表

CREATE TABLE ts_user (
id bigint(20) unsigned NOT NULL COMMENT '编码',
name varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户姓名',
mobile varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '手机号',
create_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人',
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人',
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_create_time (create_time) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

3.2 修改表名

ALTER  TABLE ts_user RENAME TO ts_new_user;

3.3 删除表

DROP TABLE ts_new_user;

3.4 字段操作

(1) 查询表字段

show full columns from ts_user;

(2) 新增字段

ALTER TABLE ts_user add column gender tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别,1,男;2,女' AFTER mobile;

(3) 修改字段

ALTER TABLE ts_user modify column mobile varchar(30) NOT NULL DEFAULT '' COMMENT '用户手机号';

(4) 删除字段

ALTER TABLE ts_user drop column gender;

3.5 索引操作

(1) 查询表索引

 SHOW INDEXES FROM ts_user;

(2) 新增普通索引

alter table ts_user add index idx_id (id);

(3) 新增唯一索引

alter table ts_user add unique idx_id (id);

(4) 新增主键索引

alter table ts_user add primary key idx_id (id) ;

(5) 新增多列索引

alter table ts_user add index idx_id_name (id,name) ;

(6) 新增全文索引

alter table ts_user add fulltext idx_id (id) ;

(7) 删除索引

# 删除普通索引
alter table ts_user drop index idx_id;

# 删除主键索引
alter table ts_user drop primary key;

四、数据操作

4.1 查询操作

(1) 单表查询

select * from ts_user;

或者:

select id, name from ts_user;

(2) 关键字查询

  • and 查询
select id, name from ts_user where name = '张三'
  • or 查询
select id, name from ts_user where name = '张三'
  • in 查询(参数个数不能超过1000)
select id, name from ts_user where name in ('张三', '李四')
  • like 模糊查询(%属于通配符)
select id, name from ts_user where name like '张%'
  • 非空查询
select id, name from ts_user where name is not null
  • 区间字段查询
select id, name, age from ts_user where  age >= 18 and age <= 30
select id, name, age from ts_user where age between 18 and 30
  • 多条件判断
select 
name,
(
case
when scope >= 90 then '优'
when 80 <= scope < 90 then '良'
when 80 > scope >= 70 then '中'
else '差'
end
) as judge
from ts_user

(3) 连表查询

左连接查询:

select tu.id, tu.name,tr.role_name
from ts_user tu
left join ts_role tr on tu.id = tr.user_id

右连接查询:

select tu.id, tu.name,tr.role_name
from ts_user tu
right join ts_role tr on tu.id = tr.user_id

内连接查询:

select tu.id, tu.name,tr.role_name
from ts_user tu
inner join ts_role tr on tu.id = tr.user_id

满连接查询:

select tu.id, tu.name,tr.role_name
from ts_user tu
full join ts_role tr on tu.id = tr.user_id

(4) 分组查询

统计学生总数:

select count(id) from ts_user

查询学生最大的年纪:

select max(age) from ts_user

查询学生最大的年纪:

select min(age) from ts_user

查询各个学生各项成绩的总和:

select id, sum(score) from ts_user group by id

查询各个学生各项成绩的平均分:

select id, avg(score) from ts_user group by id

查询各个学生各项成绩的平均分大于100的学生信息:

select id, avg(score) from ts_user group by id having avg(score)  > 100

4.2 插入操作

(1) 单列插入

INSERT INTO ts_user(id, name) VALUES ('1', '张三');

(2) 多列插入

INSERT INTO ts_user(id, name)
VALUES
('1', '张三'),
('2', '李四'),
('3', '王五');

4.3 修改操作

update ts_user set name = '李四1', age = '18' where id = '1'

4.4 删除操作

# 删除表全部内容
delete from ts_user

# 根据判断条件进行删除
delete from ts_user where id = '1'

五、运算符

MySQL 主要有以下几种运算符:

  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符

5.1 算术运算符

说明:在除法运算和模运算中,如果除数为0,将是非法除数,返回结果为NULL。

5.2 比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符,可以判断表中的哪些记录是符合条件的。比较结果为真,则返回 1,为假则返回 0,比较结果不确定则返回 NULL。

说明:mysql中,IN 语句中参数个数是不限制的。不过对整段 sql 语句的长度有了限制,最大不超过 4M!

5.3 逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真,结果返回 1。如果表达式是假,结果返回 0。

5.4 位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数,进行位运算。然后再将计算结果从二进制数变回十进制数。

5.5 运算符优先级

说明:在无法确定优先级的情况下,可以使用圆括号()来改变优先级,并且这样会使计算过程更加清晰。

六、视图

视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。

6.1 创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明:

  • OR REPLACE:表示替换已有视图
  • ALGORITHM:表示视图选择算法,默认算法是UNDEFINED(未定义的):MySQL自动选择要使用的算法 ;merge合并;temptable临时表
  • select_statement:表示select语句
  • **[WITH [CASCADED | LOCAL] CHECK OPTION]**:表示视图在更新时保证在视图的权限范围之内
  • cascade:是默认值,表示更新视图的时候,要满足视图和表的相关条件
  • local:表示更新视图的时候,要满足该视图定义的一个条件即可

基本格式:

create view <视图名称>[(column_list)]
as select语句
with check option;

创建视图示例:

create view v_user(用户名,年龄)
as
select user_name,age from t_user
with check option;

6.2 查看视图

使用show create view语句查看视图信息:

show create view v_user;

视图一旦创建完毕,就可以像一个普通表那样使用,视图主要用来查询:

select * from v_user;

6.3 删除视图

删除视图是指删除数据库中已存在的视图,删除视图时,只能删除视图的定义,不会删除数据,也就是说不动基表:

DROP VIEW [IF EXISTS]   
view_name [, view_name] ...

删除示例:

drop view IF EXISTS v_user;

七、小结

本文主要围绕 Mysql 中常用的语法进行一次梳理和介绍,这些语法大部分也同样适用于其他的数据库,例如 oracle、sqlserver、postgres 等等,在下篇文字中,我们会重点介绍剩下的部分。

责任编辑:赵宁宁 来源: Java极客技术
相关推荐

2022-03-29 10:55:10

Mysql数据库

2022-06-20 22:37:25

Linux操作系统命令

2022-06-15 11:02:40

网络安全运营

2022-06-27 17:46:53

PythonFlask

2022-07-03 06:10:15

2022-06-22 09:56:19

PythonMySQL数据库

2022-06-30 18:17:00

数据集云数据建模计数据仓库

2022-05-11 09:02:27

Python数据库Excel

2022-05-30 11:10:04

数据库MySQL系统

2022-06-29 10:16:25

数据库SQL

2022-06-23 11:42:22

MySQL数据库

2022-04-19 14:41:29

Oracle数据库SQL

2022-06-30 10:56:18

字节云数据库存储

2022-06-29 09:43:14

SQL优化数据库

2022-06-10 17:37:37

数据库

2022-06-26 06:32:28

MySQL数据库维护

2022-06-17 09:47:04

Linux命令

2022-06-15 08:21:49

Linux运维工程师

2022-04-11 13:34:07

区块链比特币安全

2022-07-05 19:19:11

tcpdumpLinux命令

同话题下的热门内容

为什么MySQL的count()方法这么慢?解决MySQL幻读的终极指南MySQL 死锁了,怎么办?分库分表实战:小试牛刀—千万级数据之SQL优化面试突击:MySQL 中如何去重?Undo 日志用什么存储结构支持无锁并发写入?Group By 有哪些注意事项?你知道吗?MySQL主键自增值为什么有“空洞”?

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号