[大数据] 常用SQL语句

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 常用SQL语句 -> 正文阅读

[大数据]常用SQL语句

一、SQL介绍

在这里插入图片描述

二、SQL分类之DQL、DDL、DML、DCL介绍

在这里插入图片描述

三、DDL(Data Definition Language)数据定义语言

开发、运维都得会！

1、操作库

 #创建库
 
MariaDB [(none)]> create database db1;    
Query OK, 1 row affected (0.00 sec)

#创建库是否存在，不存在则创建

MariaDB [(none)]> create database if not exists db1;   
Query OK, 1 row affected, 1 warning (0.00 sec)

 #查看所有数据库
 
MariaDB [(none)]> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| kings              |
| mysql              |
| performance_schema |
| ultrax             |
+--------------------+
6 rows in set (0.00 sec)

#根据关键字筛选数据库，%是通配符，匹配以 kings 开头，符合的数据库

MariaDB [mysql]> show databases like 'kings%';
+-------------------+
| Database (kings%) |
+-------------------+
| kings             |
+-------------------+
1 row in set (0.00 sec)

 
 #查看某个数据库的定义信息
 
MariaDB [(none)]> show create database db1;   
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)


#修改数据库字符信息

MariaDB [(none)]> alter database db1 character set utf8;    
Query OK, 1 row affected (0.03 sec)

MariaDB [(none)]> drop database db1;     #删除数据库
Query OK, 0 rows affected (0.03 sec)

2、操作表

#创建一个名为 learn 的数据库

MariaDB [(none)]> create database learn;
 
Query OK, 1 row affected (0.00 sec)

#进入数据库
use 数据库名;

MariaDB [(none)]> use learn      
Database changed

 #创建一个名为 student 的表并填入以下信息
 
MariaDB [learn]> create table student(
    id int,
    name varchar(32),
    age int ,
    score double(4,1),
    birthday date,
    insert_time timestamp
);                             
Query OK, 0 rows affected (0.01 sec)


#查看当前数据库下的所有表
show tables;

MariaDB [learn]> show tables;
+-----------------+
| Tables_in_learn |
+-----------------+
| students        |
+-----------------+
1 row in set (0.00 sec)


 #查看表结构
desc 表名

MariaDB [learn]> desc student;    
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | YES  |     | NULL              |                             |
| name        | varchar(32) | YES  |     | NULL              |                             |
| age         | int(11)     | YES  |     | NULL              |                             |
| score       | double(4,1) | YES  |     | NULL              |                             |
| birthday    | date        | YES  |     | NULL              |                             |
| insert_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)


 #查看创建表的SQL语句
 show create table 表名;

MariaDB [learn]> show create table student;     
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                               |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `score` double(4,1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


#修改表名
alter table 表名 rename to 新的表名;

MariaDB [learn]> alter table student rename to students; 
Query OK, 0 rows affected (0.01 sec)

#添加一列
alter table 表名 add 列名 数据类型;

MariaDB [learn]> alter table students add grade int;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [learn]> desc students;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | YES  |     | NULL              |                             |
| name        | varchar(32) | YES  |     | NULL              |                             |
| age         | int(11)     | YES  |     | NULL              |                             |
| score       | double(4,1) | YES  |     | NULL              |                             |
| birthday    | date        | YES  |     | NULL              |                             |
| insert_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| grade       | int(11)     | YES  |     | NULL              |                             |
+-------------+-------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)


#删除列
alter table 表名 drop 列名;

MariaDB [learn]> alter table students drop grade;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [learn]> desc students;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | YES  |     | NULL              |                             |
| name        | varchar(32) | YES  |     | NULL              |                             |
| age         | int(11)     | YES  |     | NULL              |                             |
| score       | double(4,1) | YES  |     | NULL              |                             |
| birthday    | date        | YES  |     | NULL              |                             |
| insert_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.01 sec)


#删除表
drop table 表名;
drop table  if exists 表名 ;

MariaDB [learn]> show tables;   #删除前
+-----------------+
| Tables_in_learn |
+-----------------+
| students        |
+-----------------+
1 row in set (0.00 sec)

MariaDB [learn]> drop table students;
Query OK, 0 rows affected (0.00 sec)

MariaDB [learn]> show tables;     #删除后
Empty set (0.00 sec)

四、DCL(Data Control Language)数据控制语言

运维重点掌握，开发了解！

语法大小写不敏感，注意加空格

管理用户

1、添加用户

语法：create user '用户名'@'主机名' identified by '密码';     

MariaDB [learn]> create user 'xuyuhan'@'%' identified 'xuyuhan';
Query OK, 0 rows affected (0.00 sec)

2、删除用户

语法：drop user '用户名'@'主机名';

MariaDB [mysql]> DROP USER 'xuyuhan'@'%';
Query OK, 0 rows affected (0.00 sec)

权限管理

1、查询权限

show grants for '用户名'@'主机名';

MariaDB [mysql]> show grants for 'xuyuhan'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for xuyuhan@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xuyuhan'@'%' IDENTIFIED BY PASSWORD '*F29AA23ACEA90407097D68FFEEEF8C2D5274BA94' |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2、授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
 
-- 给xuyuhan用户授予所有权限，在任意数据库任意表上
grant all on *.* to 'xuyuhan'@'localhost';;

MariaDB [mysql]> grant all on *.* to 'xuyuhan'@'localhost';
Query OK, 0 rows affected (0.00 sec)

3、撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

五、DML(Data Manipulation Language)数据操作语言

开发重点掌握，运维了解！

1、增加 insert into

#还是先准备好一个表来进行实验
MariaDB [learn]> create table student(
    id int,
    name varchar(32),
    age int ,
    score double(4,1),
    birthday date,
    insert_time timestamp
);                             
Query OK, 0 rows affected (0.01 sec)

MariaDB [learn]> desc student;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | YES  |     | NULL              |                             |
| name        | varchar(32) | YES  |     | NULL              |                             |
| age         | int(11)     | YES  |     | NULL              |                             |
| score       | double(4,1) | YES  |     | NULL              |                             |
| birthday    | date        | YES  |     | NULL              |                             |
| insert_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)


#写全所有列名
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

MariaDB [learn]> insert into student (id,name,age) values (1,2,3);
Query OK, 1 row affected (0.00 sec)



#不写列名（所有列全部添加）
insert into 表名 values(值1,值2,...值n);



#插入部分数据
insert into 表名(列名1,列名2) values(值1,值2);

2、删除 delete

#删除表中数据
delete from 表名 where 列名  = 值;

MariaDB [learn]> delete from student where id=1;
Query OK, 2 rows affected (0.00 sec)


#删除表中所有数据
delete from 表名;

MariaDB [learn]> delete from student;
Query OK, 0 rows affected (0.00 sec)



#删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table 表名;

MariaDB [learn]> truncate table student;
Query OK, 0 rows affected (0.00 sec)

修改 update

#不带条件的修改(会修改所有行)
update 表名 set 列名 = 值;


#带条件的修改
update 表名 set 列名 = 值 where 列名=值;

六、DQL(Data Query Language)数据查询语言

开发重点掌握，运维了解！

#查看当前在哪一层数据库，相当于pwd命令
select database();

MariaDB [mysql]> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

#从mysql库中的user表筛选出user，host两个信息

MariaDB [mysql]> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| xuyuhan | %         |
| root    | 127.0.0.1 |
| root    | ::1       |
|         | docker    |
| root    | docker    |
|         | localhost |
| root    | localhost |
| xuyuhan | localhost |
+---------+-----------+
8 rows in set (0.00 sec)