读书笔记--SQL必知必会--常用MySQL(MariaDB)命令

DBMS信息

显示DBMS的版本
select version();
显示DBMS状态
status;
显示DBMS资源状态
show status;
显示DBMS支持的权限
show privileges;
查看DBMS用户的权限
show grants for user_name;

DBMS变量

显示DBMS的变量名称及值
show variables;
显示DBMS的使用端口
show variables like 'port';
显示DBMS的编码
show variables like 'character%';
显示DBMS的数据文件存放路径
show variables like '%datadir%';
显示DBMS的的最大连接数
show variables like '%max_connections%';

数据库

显示DBMS的所有数据库;
show databases;
创建数据库
create database db_name;
删除数据库
drop database db_name;
选择数据库
use db_name; 显示当前使用的数据库
select database();
显示当前登录的用户名称
select user();
显示当前数据库支持及默认的存储引擎
show engines;
显示当前数据库的触发器信息
show triggers;

数据库表

显示当前数据库的表信息
show tables;
创建数据库表
create table table_name;
删除数据库表
drop table table_name;
显示当前数据库的表状态
show table status;
显示表结构信息
describe table_name; 或 desc table_name; 或 show columns from able_name;
显示表中的所有记录
select * from table_name;

查看状态

show table status;
show procedure status;
show function status;

查看创建属性

show create procedure procedure_name;
show create function function_name;
show create view view_name;
show create table table_name;

异常信息反馈

查看上一条执行语句的异常信息反馈(错误、提醒和警告)
show errors;
show warnings;

示例-1

1-1 创建数据库

[root@CentOS-7 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create database sample;
Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sample |
| test |
+--------------------+
5 rows in set (0.00 sec) MariaDB [(none)]> use sample;
Database changed
MariaDB [sample]> show tables;
Empty set (0.00 sec) MariaDB [sample]>

1-2 创建表

  • Students表包含学号、姓名、年龄: Students(StudentID,StudentName,StudentAage)
  • Classes表包含课程编号、课程名称:Classes(ClassID,ClassName)
  • Grade表包含学号、所选的课程编号、成绩:StuentsClasses(StudentID,ClassID,Score)
CREATE TABLE Students
(
StudentID char(10) NOT NULL ,
StudentName char(50) NOT NULL ,
StudentAge int NULL
);
CREATE TABLE Classes
(
ClassID char(10) NOT NULL ,
ClassName char(50) NOT NULL
);
CREATE TABLE Grades
(
StudentID char(10) NOT NULL ,
ClassName char(10) NOT NULL ,
Score char(50) NOT NULL
);

1-3 插入数据

INSERT INTO Students(StudentID, StudentName, StudentAge) VALUES('st1', 'aaa', 11);
INSERT INTO Students(StudentID, StudentName, StudentAge) VALUES('st2', 'bbb', 22);
INSERT INTO Students(StudentID, StudentName, StudentAge) VALUES('st3', 'ccc', 33); INSERT INTO Classes(ClassID, ClassName)
SELECT '001', 'Java' UNION ALL
SELECT '002', 'Python' UNION ALL
SELECT '003', 'Linux' ; INSERT INTO Grades(StudentID, ClassName, Score)
SELECT 'st1', 'Java', '85' UNION ALL
SELECT 'st2', 'Java', '92' UNION ALL
SELECT 'st3', 'Java', '96' ;
INSERT INTO Grades(StudentID, ClassName, Score)
SELECT 'st1', 'Python', '88' UNION ALL
SELECT 'st2', 'Python', '81' UNION ALL
SELECT 'st3', 'Python', '97' ;
INSERT INTO Grades(StudentID, ClassName, Score)
SELECT 'st1', 'Linux', 'Unkown' UNION ALL
SELECT 'st3', 'Linux', 'Unkown' ;

1-4 查询数据

查询学生表格的结构和所有数据
desc Students;
select * from Students; 查询选修课程的学生人数
select count(distinct StudentID) from Grades; 查询年龄大于20的学生ID和姓名
select StudentName,StudentAge from Students where StudentAge > 20; 查询选修Linux课程的学生ID和姓名
select StudentID, StudentName from Students where StudentID in (select StudentID from Grades where ClassName='Linux'); 查询不选修Linux课程的学生ID和姓名
select StudentID, StudentName from Students where StudentID not in (select StudentID from Grades where ClassName='Linux'); 查询选修2门课程的学生ID和姓名
select StudentID, StudentName from Students where StudentID in (select StudentID from Grades group by StudentID having count(distinct ClassName)=2);

1-5 更改数据

将学生ID为st1的Python课程成绩修改为99
Update Grades set Score='99' where StudentID='st1'; 在Classes表格增加Effort列
alter table Classes add Effort Char(12); 在Classes表格删除Effort列
alter table Classes drop column Effort;

1-6 删除数据

删除表中的数据
delete from Grades where StudentID='st2' and ClassName='Python'; 删除表
drop tables Grades; 删除数据库
drop database sample;

不登陆MySQL界面,删除数据库

[root@CentOS-7 ~]# mysqladmin -u root -p drop sample
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed. Do you really want to drop the 'sample' database [y/N] y
Database "sample" dropped
[root@CentOS-7 ~]#

示例-2

表Story中包含货物种类(list)A和B的库存总量(StoryCount)分别为997和1234;

表Sale中货物种类(list)A有2次出库数量(SaleNumber)记录分别为105和213;

表Sale中货物种类(list)B有3次出库数量(SaleNumber)记录分别为116、211和303;

建立数据表并用一条SQL语句求出货物A,B各剩下多少?

2-1 创建数据表

创建数据表Story结构并添加数据
CREATE TABLE Story(list VARCHAR(10), StoryCount INT);
INSERT INTO Story(list, StoryCount)
SELECT 'A','997' UNION ALL
SELECT 'B','1234' ; 创建数据表Sale结构并添加数据
CREATE TABLE Sale(list VARCHAR(10), SaleNumber INT);
INSERT INTO Sale(list, SaleNumber)
SELECT 'A','105' UNION ALL
SELECT 'A','213' UNION ALL
SELECT 'B','116' UNION ALL
SELECT 'B','221' UNION ALL
SELECT 'B','303' ; 查询表结构和所有数据
desc Story;
select * from Story;
desc Sale;
select * from Sale;

2-2 计算货物的剩余数量

MariaDB [demo]> select list, StoryCount from Story;
+------+------------+
| list | StoryCount |
+------+------------+
| A | 997 |
| B | 1234 |
+------+------------+
2 rows in set (0.00 sec) MariaDB [demo]> select list, sum(SaleNumber)SaleCount from Sale group by list;
+------+-----------+
| list | SaleCount |
+------+-----------+
| A | 318 |
| B | 640 |
+------+-----------+
2 rows in set (0.00 sec) MariaDB [demo]> select Story.list, Story.StoryCount - sum(Sale.SaleNumber) from Story, Sale where Story.list=Sale.list group by list;
+------+-----------------------------------------+
| list | Story.StoryCount - sum(Sale.SaleNumber) |
+------+-----------------------------------------+
| A | 679 |
| B | 594 |
+------+-----------------------------------------+
2 rows in set (0.00 sec) MariaDB [demo]>

2-3 删除数据表

DROP TABLE Story;
DROP TABLE Sale;

参考信息

MySQL命令大全

上一篇:Lua协程学习


下一篇:Tomcat与Spring中的事件机制详解