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 -pEnter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 9Server version: 5.5.56-MariaDB MariaDB ServerCopyright (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 changedMariaDB [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 ALLSELECT '003', 'Linux' ;INSERT INTO Grades(StudentID, ClassName, Score) SELECT 'st1', 'Java', '85' UNION ALL SELECT 'st2', 'Java', '92' UNION ALLSELECT 'st3', 'Java', '96' ;INSERT INTO Grades(StudentID, ClassName, Score) SELECT 'st1', 'Python', '88' UNION ALL SELECT 'st2', 'Python', '81' UNION ALLSELECT '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课程成绩修改为99Update 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 sampleEnter 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] yDatabase "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 ALLSELECT 'B','116' UNION ALLSELECT 'B','221' UNION ALLSELECT '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]>