数据库基本指令

最新推荐文章于 2022-05-11 21:23:13 发布

「已注销」

最新推荐文章于 2022-05-11 21:23:13 发布

阅读量219

点赞数 1

文章标签： big data 数据库 redis

本文链接：https://blog.csdn.net/m0_56238604/article/details/119920979

版权

数据库

#概述

数据库database是用来存储数据和管理数据的仓库

#分类

关系型MySQL与非关系型Redis

#使用

1.安装服务器端：存数据，设置端口号3306，设置密码root，设置字符集/编码表gbk/utf8

2.安装客户端：连接服务器，操作服务器里的数据CRUD（Create/Resourch/Update/Delete）

DOS窗口：小黑窗口

可视化工具：Sqlyog软件

3.结构：数据库--->表--->数据（字段/字段的值）

SQL语言

#概述

SQL语言是结构化查询语言，专门用来操作数据库的语言，是一种标准化语言，可以操作各种数据产品

#数据库操作

创建数据库【create database 数据库名 default character set utf8;】结果如下：

MariaDB [(none)]> create database My_SQL default character set utf8;
Query OK, 1 row affected (0.062 sec)

展示所有数据库【show databases;】结果如下：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| cgb2107            |
| information_schema |
| my_sql             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.176 sec)

删除数据库【drop database 数据库名;】结果如下：

MariaDB [(none)]> drop database my_SQl;
Query OK, 0 rows affected (0.003 sec)

#表操作

使用数据库【use 数据库名;】结果如下：

MariaDB [(none)]> use MY_sql;
Database changed

创建数据表【create table 表名(字段名字段类型(字段最大长度),(字段名字段类型(字段最大长度),(字段名字段类型(字段最大长度),(字段名字段类型(字段最大长度),......);】结果如下：

MariaDB [MY_sql]> create table my_table(
    -> name varchar(10),
    -> age int(2),
    -> sex varchar(5),
    -> nation varchar(20)
    -> );
Query OK, 0 rows affected (0.479 sec)

展示所有表【show tables;】结果如下：

MariaDB [MY_sql]> show tables;
+------------------+
| Tables_in_my_sql |
+------------------+
| my_table         |
+------------------+
1 row in set (0.002 sec)

删除数据表【drop table 表名】结果如下：

MariaDB [my_sql]> drop table my_table;
Query OK, 0 rows affected (0.137 sec)

修改表【alter table 表名 add column 字段名字段类型(字段长度);】结果如下：

MariaDB [my_sql]> alter table my_table add column salary numeric(7,2);
Query OK, 0 rows affected (0.214 sec)
Records: 0  Duplicates: 0  Warnings: 0

描述表的结构【desc 表名;】结果如下：

MariaDB [my_sql]> desc my_table;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name   | varchar(10)  | YES  |     | NULL    |       |
| age    | int(10)      | YES  |     | NULL    |       |
| sex    | varchar(5)   | YES  |     | NULL    |       |
| nation | varchar(20)  | YES  |     | NULL    |       |
| salary | decimal(7,2) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.015 sec)

#数据操作

查看数据表数据【select * from 表名】结果如下：

MariaDB [my_sql]> select * from my_table;
Empty set (0.067 sec)

添加数据表数据【insert into 表名 value(值1,值2,值3,......);】查看数据表数据结果如下：

MariaDB [my_sql]> insert into my_table value('曹操',30,'男','魏',80000.00);
Query OK, 1 row affected (0.395 sec)

MariaDB [my_sql]> select * from my_table;
+------+------+------+--------+----------+
| name | age  | sex  | nation | salary   |
+------+------+------+--------+----------+
| 曹操 |   30 | 男   | 魏     | 80000.00 |
+------+------+------+--------+----------+
1 row in set (0.103 sec)

改变数据表数据【update 表名 set 字段名=新赋值;】查看数据表数据结果如下：

MariaDB [my_sql]> update my_table set salary=90000.00;
Query OK, 4 rows affected (0.088 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [my_sql]> select * from my_table;
+------+------+------+--------+----------+
| name | age  | sex  | nation | salary   |
+------+------+------+--------+----------+
| 曹操 |   30 | 男   | 魏     | 90000.00 |
+------+------+------+--------+----------+
4 rows in set (0.001 sec)

###删除数据表某列的数据【delete from 表名 where id=列名】查看数据表数据结果如下：

排序【select * from 表名 order by 字段名 desc】查看数据表数据结果如下：

MariaDB [my_sql]> select * from my_table;
+--------+------+------+--------+----------+
| name   | age  | sex  | nation | salary   |
+--------+------+------+--------+----------+
| 诸葛亮 |   20 | 男   | 蜀     | 50000.00 |
| 刘备   |   26 | 男   | 蜀     | 60000.00 |
| 关羽   |   25 | 男   | 蜀     | 50000.00 |
+--------+------+------+--------+----------+
3 rows in set (0.001 sec)

MariaDB [my_sql]> select * from my_table order by age desc;
+--------+------+------+--------+----------+
| name   | age  | sex  | nation | salary   |
+--------+------+------+--------+----------+
| 刘备   |   26 | 男   | 蜀     | 60000.00 |
| 关羽   |   25 | 男   | 蜀     | 50000.00 |
| 诸葛亮 |   20 | 男   | 蜀     | 50000.00 |
+--------+------+------+--------+----------+
3 rows in set (0.001 sec)

记录总数【select count(*) from 表名】查看数据表数据结果如下：

MariaDB [my_sql]> select count(*) from my_table;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.001 sec)

删除数据表数据【delete from 表名】查看数据表数据结果如下：

MariaDB [my_sql]> delete from my_table;
Query OK, 4 rows affected (0.105 sec)

MariaDB [my_sql]> select * from my_table;
Empty set (0.000 sec)

CREATE TABLE a(id INT)
CREATE TABLE b(id INT PRIMARY KEY)
CREATE TABLE c(id INT PRIMARY KEY AUTO_INCREMENT)
CREATE TABLE d(NAME VARCHAR(10))
CREATE TABLE e(NAME VARCHAR(10) NOT NULL)
CREATE TABLE f(tel VARCHAR(11))
CREATE TABLE g(tel VARCHAR(11) UNIQUE)

#总结

show databases; #查看所有库
create database 库名 default character set utf8 ; #创建库
drop database 库名 ; #删库

use 库名; #使用指定的数据库
create table 表名(字段名 字段类型(字段长度),字段名 字段类型(字段长度)); #新建表
show tables ; #查看表
drop table 表名; #删表
alter table 表名 add column 字段名 字段类型(字段长度) ; #修改表
desc 表名; #描述表的结构

select * from 表名; #查数据
insert into 表名 values(字段1的值,字段2的值,字段3的值,字段4的值) ;#添加数据
update 表名 set 字段名=新值 #改数据
delete from 表名 ; #删数据

CREATE TABLE a(id INT);
CREATE TABLE b(id INT PRIMARY KEY);
CREATE TABLE c(id INT PRIMARY KEY AUTO_INCREMENT);

CREATE TABLE d(NAME VARCHAR(10));#values can be null
CREATE TABLE e(NAME VARCHAR(10)NOT NULL);#values can't be null
INSERT INTO e VALUES();#error
INSERT INTO e VALUES(NULL);#error

CREATE TABLE f(tele VARCHAR(11));
CREATE TABLE g(tele VARCHAR(11)UNIQUE);

SELECT * FROM emp;
SELECT ename FROM emp;
SELECT ename,ename,ename FROM emp;
SELECT ename,LOWER(ename)FROM emp;
SELECT ename,UPPER(ename)FROM emp;

SELECT * FROM dept;
SELECT dname,LENGTH(dname)FROM dept;
SELECT dname,LENGTH(loc)FROM dept;
SELECT dname,CONCAT(dname,'hello')FROM dept;
SELECT dname,CONCAT(dname,'hello','SQL')FROM dept;
SELECT dname,SUBSTR(dname,2)FROM dept;#substr startwith 2nd element
SELECT dname,SUBSTR(dname,1,2)FROM dept;
SELECT dname,REPLACE(dname,'o','0')FROM dept;
SELECT comm,IFNULL(comm,200)FROM emp;
SELECT sal,comm,sal+IFNULL(comm,0)FROM emp;
SELECT sal,comm,sal+IFNULL(comm,0)*16 FROM emp;
SELECT comm,ROUND(comm)FROM emp;
SELECT comm,CEIL(comm)FROM emp;
SELECT comm,FLOOR(comm)FROM emp;
SELECT NOW();#2021-08-29 22:38:28
SELECT YEAR(NOW());#2021
SELECT YEAR('2018-08-29'),MONTH(NOW()),DAY(NOW());
SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
SELECT 'Xi\'an';#Xi'an
SELECT "Xi'an";#Xi'an

SELECT DISTINCT loc FROM dept;
SELECT * FROM dept;
SELECT * FROM dept WHERE deptno=1;
SELECT * FROM dept WHERE loc='二区';
SELECT * FROM dept WHERE deptno=1 OR loc='二区';
SELECT * FROM dept WHERE deptno=2 OR deptno=3;
SELECT * FROM dept deptno IN(2,3);
SELECT * FROM dept WHERE dname LIKE '%o%';#低效
SELECT * FROM dept WHERE dname LIKE 'o%';#高效
SELECT * FROM dept WHERE dname LIKE '%o';
#select * from dept where ename like 'l__'#了解
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;
SELECT * FROM emp WHERE sal>3000 AND sal<10000;
SELECT * FROM emp WHERE BETWEEN 3000 AND 10000;

SELECT * FROM emp;
SELECT * FROM emp LIMIT 2;#展示前两条数据
SELECT * FROM emp LIMIT 1,2;#展示前两条数据
SELECT * FROM emp LIMIT 1,5;#从第N+1行开始展示，要展示的行数
SELECT * FROM emp LIMIT 3,3;
SELECT * FROM emp ORDER BY sal;#升序
SELECT * FROM emp ORDER BY sal ASC;#升序（首字母）
SELECT * FROM emp ORDER BY sal DESC;#降序（首字母）
SELECT * FROM emp ORDER BY ename;#升序（首字母）
SELECT * FROM emp ORDER BY hiredate;#升序（首数字）
SELECT * FROM emp ORDER BY job;#升序（字典UTF-8）
SELECT * FROM emp WHERE hiredate<'2018-1-1';
SELECT * FROM emp WHERE YEAR(hiredate)<2017;
SELECT *,YEAR(NOW())-YEAR(hiredate)AS WorkTime FROM emp;
SELECT *,sal*13+IFNULL(comm,0)*13 AS YearSalary FROM emp;
SELECT MAX(sal) FROM emp;
SELECT MIN(SAL) FROM emp;
SELECT AVG(sal) FROM emp;
SELECT SUM(sal) FROM emp;
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;#高效
SELECT COUNT(empno) FROM emp;#了解
SELECT COUNT(comm) FROM emp;#了解，不统计null
SELECT empno FROM emp;
SELECT SUM(sal) FROM emp;
SELECT empno,SUM(sal) FROM emp;#报错，出现聚合列和非聚合列

SELECT MAX(sal),deptno FROM emp GROUP BY deptno;
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;

SELECT AVG(sal),job FROM emp GROUP BY job;
SELECT job,AVG(sal) FROM emp GROUP BY job;

SELECT YEAR(hiredate) AS YEAR,COUNT(1) FROM emp GROUP BY YEAR(hiredate);
SELECT deptno,COUNT(1) FROM emp GROUP BY deptno;
SELECT deptno,COUNT(1) FROM emp GROUP BY deptno HAVING COUNT(1)>1;
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)>8000;#低效
SELECT job,AVG(sal) FROM emp WHERE AVG(sal)>8000 GROUP BY job;#高效

SELECT YEAR(hiredate) AS WorkTime,COUNT(1) FROM emp
GROUP BY YEAR(hiredate) HAVING COUNT(1);

START TRANSACTION;#开启事务
INSERT INTO dept VALUES(NULL,'test','大钟寺');
INSERT INTO dept VALUES(NULL,'test2','大钟寺2');
COMMIT;#关闭事务

CREATE TABLE h(
id INT PRIMARY KEY AUTO_INCREMENT,#主键自增
sex VARCHAR(10) DEFAULT '男'#默认约束
);

CREATE TABLE i(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT,
CHECK(age>0 AND age<18)#检查约束，不合法是会报错
);

知识来源于【cgblpx】的授课