MariaDB数据库命令与SQL语句

----------------------------------------------------------------------------------------------------------------------------------------------


一、数据库客户端命令

1、mysql，连接数据库

    -A，--no-auto-rehash 禁止补全

    -u，--user= 用户名,默认为root

    -U，以安全模式登录

    -h，--host= 服务器主机,默认为localhost

    -p，--passowrd= 用户密码,建议使用-p,默认为空密码

    -P，--port= 服务器端口

    -S，--socket= 指定连接socket文件路径

    -D，--database= 指定默认数据库

    -C，--compress 启用压缩

    -e SQL，“ 执行SQL命令

    -V，--version 显示版本

    -v，--verbose 显示详细信息

    --print-defaults 获取程序默认使用的配置

2、服务器监听的两种socket地址

    ip socket：监听在tcp的3306端口，支持远程通信

    unix sock：监听在sock文件上，仅支持本机通信

3、执行命令

    (1)mysql -uUSER -pPASSWORD，连接数据库，此处也可以输入mysql -uUSER -p，回车后弹出输入密码也可以。指令中-u后的用户和-p后的密码与各自的选项没有空格image.png

    (2)mysql -uroot -pPASSWORD < /路径/DBFILE，导入数据库

4、其它工具

    mysql_secure_installation 一个脚本，辅助及设置工具

    mysqldump    备份工具

    mysqladmin    基于mysql协议管理mysql的工具

    mysqlimport    数据导入工具

5、如果写脚本需要插入SQL数据，可以将命令按照SQL语句写入一个文件，此时用命令

    mysql -uUSER -pPASSWORD < SQLFILE

6、配置文件

    执行顺序为后面文件覆盖前面的文件，最下方文件优先级最高

    /etc/my.cnf                              Global选项 

    注：/etc/my.cnf的[mysqld]下添加skip_networking=1，表示关闭远程端口，只能本机连接本机数据库  

    /etc/mysql/my.cnf                   Global选项

    SYSCONFDIR/my.cnf            Global选项

    $MYSQL_HOME/my.cnf        Server-specific 选项

    --defaults-extra-file=path

    ~/.my.cnf                                User-specific 选项

注：工作中可能需要更改数据库提示符以区分环境，建议在/etc/profile.d/下新建个.sh文件，里面写上export MYSQL_PS1="(\u@\h \v)[\d]>"，之后 . 启动一次该脚本image.png

或者在/etc/my.cnf.d/mysql-clients.cnf的[mysql]下添加image.png

二、服务器端命令

1、SQL语言规范

    数据库中SQL语句不区分大小写

    SQL语句可以单行或多行书写，以分号结尾

    关键词不能跨多行或者简写

2、数据库对象

(1)数据库对象：数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

(2)命名规则：

必须以字母开头

可包括数字和三个特殊字符（# _ $）

不要使用MySQL的保留字

同一database(Schema)下的对象不能同名

3、SQL语句分类

    (1)DDL: Data DefinationLanguage 数据定义语言

    CREATE，DROP，ALTER

    (2)DML: Data Manipulation Language数据操纵语言

    INSERT，DELETE，UPDATE

    (3)DCL：Data Control Language 数据控制语言

    GRANT，REVOKE，COMMIT，ROLLBACK

    (4)DQL：Data Query Language 数据查询语言

    SELECT

4、数据类型

image.png

    show databases;                 显示所有数据库

    select user();                       显示所有用户

    use DBNAME                      切换数据库

    quit                                      退出数据库

    create database DBNAME  创建数据库

    drop database name           直接删除数据库，不提醒

    show tables;                        显示表

    describe tablename;            显示具体的表结构

    select 中加上distinct           去除重复字段

    show table status from DBNAME\G    查看某个数据库的存储引擎

    show create DBNAME/TBLNAME\G    查看数据库或者表的存储引擎

注：像对于数据库增删改操作都是需要慎重的操作，因此连接数据库时，最好需要mysql -U连接，或者/etc/my.cnf.d/my-clients.cnf文件中[mysqld]下添加safe_upgrade，这样系统对危险操作会提示或者拒绝

5、SQL语句演示

    (1)CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);

    建立students表，字段有id（id为正整数，无符号，不能为空并且设置为主键）、姓名（可变字段长度为20，不能为空）、年龄（小整数型，无符号）

    (2)CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

    建立students2表，字段有id（id为正整数，无符号，不能为空）、姓名（可变字段长度为20，不能为空）、年龄（小整数型，无符号），主键设置为复合主键（id,name）

    (3)ALTER TABLE students RENAME s1;

    修改表格名称students为s1

    (4)ALTER TABLE s1 ADD phone varchar(11) AFTER name; 

    修改表格s1，在name字段后添加phone字段，该字段为可变长度字符型，11个字节

        注意：alter没有before字段，如果插入的字段在首列，用FIRST

    (5)ALTER TABLE s1 MODIFY phone int;

    修改表格s1，更改phone字段数据类型为正整数型

    (6)ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);

    修改表格s1，更改字段phone为mobile，该字段为固定长度字符型，11个字节长度上限

    (7)ALTER TABLE s1 DROP COLUMN mobile;

    修改表格s1，删除mobile列

    (8)Drop TABLE 删除表

    (9)ALTER TABLE students ADD gender ENUM('m','f')

    修改表格students，添加字段gender，取值只有m、f

    (10)ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;

    修改表格students，改变字段id为sid，定义sid无符号，不为空且为主键

    (11)ALTER TABLE students ADD UNIQUE KEY(name);

    修改表格students，设置name字段为唯一键

    (12)ALTER TABLE students ADD INDEX(age);

    修改表格students，设置索引字段为age

    (13)DESC students;

    显示学生表结构

    (14)SHOW INDEXES FROM students;

    显示学生表的索引字段

    (15)ALTER TABLE students DROP age;

    删除年龄字段

    (16)INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');

 （知道表结构只有id姓名性别这三个字段情况下）插入学生表信息

    (17)INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');

    根据学生表id、姓名，对应插入3 jack和4 allen

    (18)SELECT * FROM students WHERE id < 3;

    查询id号小于3的学生表所有信息

    (19)SELECT * FROM students WHERE gender='m';

    查询gender是m的学生表所有信息

    (20)SELECT * FROM students WHERE gender IS NULL;

    查询gender为空的学生表信息

    (21)SELECT * FROM students ORDER BY name DESC LIMIT 1,2;

    按name倒序排列，跳过第一个，查询学生表跳过后的前两条信息

    (22)SELECT * FROM students WHERE id >=2 and id <=4

    查询id大于等于2，小于等于4的信息

    (23)SELECT * FROM students WHERE id in ('2','4')

    查询id等于2或者4的信息

    (24)SELECT * FROM students WHERE id BETWEEN 2 AND 4

    查询id位于2到4之间的信息

    (25)SELECT * FROM students WHERE name LIKE ‘t%’

    查询字段name中以t开头的内容

    (26)SELECT * FROM students WHERE name RLIKE '.*[lo].*';

    查询字段name中包含l或者o的信息

    (27)SELECT id stuid,name as stuname FROM students

    查询字段id、stuid、name（显示结果以stuname）

注意：使用group by分组语句时，查询的数据内容通常会将group by后的字段也一并加入select语句之后作为查询结果的区分，并且查询语句中如果有判断条件语句，分组前对结果的条件判断依旧用where，分组后对结果的条件判断用having，举例

select ClassID,avg(Age),name from students where gender='f' group by ClassID having name like 'L%';

从学生表中查询ClassID，avg(Age)、name字段，条件为gender值为f的记录，并以ClassID为单位进行分组，分组后的结果筛选出name记录里以L开头的记录

image.png

6、多表查询

(1)交叉连接：笛卡尔乘积

(2)内连接：

等值连接：让表之间的字段以“等值”建立连接关系；

不等值连接

自然连接:去掉重复列的等值连接

自连接

(3)外连接：

左外连接：

FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

右外连接

FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

image.png

7、视图view

视图：view，虚表，保存有实表的查询结果，如果我们经常用到一句很长的SQL语句，为避免每次都输入那么长的语句，此时可以建立视图：create view VIEWNAME as SQLSENTENCE;等待下次引用就可以select * from VIEWNAME;直接查询。举例说明create view view_students_score as select s.stuid as stu_id,s.name as stu_name,sc.score,c.course from students as s inner join scores sc on s.stuid=sc.stuid inner join courses c on sc.courseid=c.courseid;该语句创建学生成绩表视图，等下次再用该表时候用命令select * from view_students_score;即可

image.png