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