Mysql数据库基础操作
在mysql数据库中开启使用tab键补全功能
1）修改主配置文件/etc/mysql/my.cnf（mysql和mariadb目录有些不同）
vim /etc/mysql/my.cnf
[mysql]
#no-auto-rehash
auto-rehash
2）重启并登录测试
3）可以设置临时支持他不键补全
msyql -uroot -p --auto-rehash
使mysql支持简体中文
vim /etc/msyql/my.cnf（mysql和mariadb目录有些不同）
[client]
default-character-set=uft8
[mysql]
default-character-set=uft8
service mysqld restart
mysql数据库表和库管理操作
注意：大部分命令不区分大小写，且有些唯一的命令可以简写
1）查看数据库结构
1》查看数据库列表信息
show database；
help show
help item：item在下面所示，来进行更进一步查看帮助
show authors
show {binary | master} logs
show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count]
show character set [like_or_where]
show collation [like_or_where]
show [full] columns from tbl_name [from db_name] [like_or_where]
show contributors
show create database db_name
show create event event_name
show create function func_name
show create procedure proc_name
show create table tbl_name
show create trigger trigger_name
show create view view_name
show databases [like_or_where]
show engine engine_name {status | mutex}
show [storage] engines
show errors [limit [offset,] row_count]
show events
show function code func_name
show function status [like_or_where]
show grants for user
show index from tbl_name [from db_name]
show master status
show open tables [from db_name] [like_or_where]
show plugins
show procedure code proc_name
show procedure status [like_or_where]
show privileges
show [full] processlist
show profile [types] [for query n] [offset n] [limit n]
show profiles
show slave hosts
show slave status
show [global | session] status [like_or_where]
show table status [from db_name] [like_or_where]
show [full] tables [from db_name] [like_or_where]
show triggers [from db_name] [like_or_where]
show [global | session] variables [like_or_where]
show warnings [limit [offset,] row_count]
like_or_where:
LIKE 'pattern'| WHERE expr
2》查看数据表信息
use 数据库名称；##使用数据库
show tables；
help use
user db_name;
use db1;
select count(*) from mytable; # selects from db1.mytable
use db2;
select count(*) from mytable; # selects from db2.mytable
3》显示数据表的结构（字段）
describe [数据库.]表名
describe mysql.user;
help describe
{DESCRIBE | DESC} tb_name [col_name | wild]
tbl_name：表名
col_name：列名
2）DDL（数据定义语言）语句操作
1》create 创建新库和创建新表
create database 数据库名；
create table 表名（定义字段）；
help create
help item：items在下面所示，来进一步查看帮助
create database
create event
create function
create function udf
create index
create procedure
create server
create table
create tablespace
create trigger
create user
create view
示例：
MariaDB [mysql]> create database auther;
MariaDB [mysql]> use auther;
MariaDB [auther]> create table users (user_name char(20) not null,user_passwd char(30) default '',primary key (user_name));
MariaDB [auther]> show tables ;
MariaDB [auther]> desc users;
2》drop 删除库和删除表
drop table [数据库名.]表名；
drop database 数据库名；
drop [temporary] table [if exists] tb_name [,tb_name2...];
help drop
help item：item是下面所示，来进一步查看帮助
drop database
drop event
drop function
drop function udf
drop index
drop procedure
drop server
drop table
drop tablespace
drop trigger
drop user
drop view
示例：
MariaDB [haha]> drop table haha.users ;
MariaDB [haha]> drop database haha;
3》alter 更改表结构
alter table [数据库名.]表名
help alter
help item：item是下面所示，来进一步查看帮助
alter database
alter event
alter function
alter logfile group
alter procedure
alter server
alter table
alter tablespace
alter view
4》复制表
create table tb_name2 select * from tb_name1;
create table tb_name2 select id,user_name from tb_name1;
5》创建临时表
create temporary table tb_name;
6》表重命名
alter table tb_name_old to tb_name_new;
rename table tb_nam_old to tb_name_new;
help rename
help item：item是下面所示，来进一步查看帮助
rename table
rename user
3）DML（数据库操作语言）语句操作
1》insert 插入新数据
insert into 表名（字段1，字段2，....）values（[调用函数]'字段1的值'，[调用函数]'字段2的值'，...）;
help insert
insert [low_priority | delayed | high_priority] [ignore][into] tbl_name
[(col_name,...)] {values | value} ({expr | default},...),(...),... [ on duplicate key update col_name=expr [, col_name=expr] ... ]
insert [low_priority | delayed | high_priority] [ignore][into] tbl_name
set col_name={expr | default}, ... [ on duplicate key update col_name=expr [, col_name=expr] ... ]
insert [low_priority | high_priority] [ignore] [into] tbl_name [(col_name,...)]
select ... [ on duplicate key update col_name=expr[, col_name=expr] ... ]
示例：
MariaDB [auther]> insert into users(user_name,user_passwd) values('shen',password('1234'));
MariaDB [auther]> insert into users values('list',password('1234')); ##字段内容也可以省略
MariaDB [auther]> select * from users ;
+-----------+--------------------------------+
| user_name | user_passwd |
+-----------+--------------------------------+
| list | *A4B6157319038724E3560894F7F93 |
| shen | *A4B6157319038724E3560894F7F93 |
+-----------+--------------------------------+
2》update 更改原有数据
update 表名 set 字段名1=值1[，字段2=值2] where 条件表达式；
help update
single-table syntax:
update [low_priority] [ignore] tbl_name
set col_name1={expr1|default} [, col_name2={expr2|default}] ...
[where where_condition]
[order by ...]
[limit row_count]
multiple-table syntax:
update [low_priority] [ignore] tbl_name
set col_name1={expr1|default} [, col_name2={expr2|default}] ...
[where where_condition]
示例：
MariaDB [auther]> update auther.users set user_passwd=password('') where user_name='list';
MariaDB [auther]> select * from auther.users;
+-----------+--------------------------------+
| user_name | user_passwd |
+-----------+--------------------------------+
| list | |
| shen | *A4B6157319038724E3560894F7F93 |
+-----------+--------------------------------+
MariaDB [(none)]> update mysql.user set password=password('xm1234') where user='root'; ##修改root的密码
MariaDB [(none)]> flush privileges; ##刷新权限
3》delete 删除不需要的数据
delete from 表名 where 条件表达式；
help delete
single-table syntax:
delete [low_priority] [quick] [ignore]
from tbl_name
[where where_condition]
[order by ...]
[limit row_count]
multiple-table syntax:
delete [low_priority] [quick] [ignore] tbl_name[.*] [, tbl_name[.*]] ...
from table_references
[where where_condition]
delete [low_priority] [quick] [ignore]
from tbl_name[.*] [, tbl_name[.*]] ...
using table_references
[where where_condition]
示例：
MariaDB [auther]> delete from auther.users where user_name='list';
4）DQL（数据查询语言）语句操作
select 查询语句
select 字段名1，字段名2，.... from 表名 [where 条件表达式]；
help select
select
[all | distinct | distinctrow ][high_priority][straight_join][sql_small_result] [sql_big_result] [sql_buffer_result][sql_cache | sql_no_cache] [sql_calc_found_rows]select_expr [, select_expr ...]
[from table_references
[where where_condition]
[group by {col_name | expr | position}[asc | desc], ... [with rollup]][having where_condition]
[order by {col_name | expr | position}[asc | desc], ...]
[limit {[offset,] row_count | row_count offset offset}][procedure procedure_name(argument_list)]
[into outfile 'file_name' [character set charset_name] export_options|into dumpfile 'file_name'|into var_name [, var_name]]
[for update | lock in share mode]]
distinct：数据去重；
sql_cache：显式指定缓存查询语句的结果；
sql_no_cache：显式指定不缓存查询语句的结果；
query_cache_type服务器变量有三个值：
on：启用；
sql_no_cache：不缓存；默认符合缓存条件都缓存；
off：关闭；
demand：按需缓存；
sql_cache：缓存；默认不缓存；
示例：
MariaDB [auther]> select * from auther.users;
MariaDB [auther]> select user_name from auther.users;
MariaDB [auther]> select * from auther.users where user_name='shen';
查询执行路径：
请求-->查询缓存
请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应
select语句的执行流程：
from --> where --> group by --> having --> order by --> select --> limit
字段可以使用别名 ：
col1 as alias1, col2 as alias2, ... ：as有时可以省略
多表查询：
连接操作：
交叉连接：笛卡尔乘积；
内连接：
等值连接：让表之间的字段以等值的方式建立连接；
不等值连接：
自然连接
自连接
外连接：
左外连接：
from tb1 left join tb2 on tb1.col = tb2.col
右外连接：
from tb1 right join tb2 on tb1.col = tb2.col
子查询：在查询中嵌套查询；
用于where子句中的子查询；
(1) 用于比较表达式中的子查询：子查询仅能返回单个值；
(2) 用于in中的子查询：子查询可以返回一个列表值；
(3) 用于exists中的子查询：
用于from子句中的子查询；
select tb_alias.col1, ... from (select clause) as tb_alias where clause;
联合查询：将多个查询语句的执行结果相合并；
union
select clause union select cluase；
5）条件控制（非常重要，一般不能没有控制条件）
1》where语句
指明过滤条件以实现“选择”功能；过滤条件：布尔型表达式；
where where_condition
select * from tb_name where A=b;
2》group by 语句
根据指定的字段把查询的结果进行“分组”以用于“聚合”运算；
3》order by 语句
根据指定的字段把查询的结果进行排序，升序asc，降序desc。
4》having 语句
对分组聚合后的结果进行条件过滤
select * from tb_name group by score having count(*)>n;
5》limit 语句
对输出的结果进行数量限制
[limit {[offset,] row_count | row_count offset offset}]
limit row_count
6》相关条件控制符
算术操作符：+, -, *, /, %
比较操作符：=, <>, !=, <=>, >, >=, <, <=
区间：between min and max
列表：in()
模糊比较：like()，% 为匹配任意，_ 匹配一个字符
值：is null，is not null
逻辑操作符：and，or，not
7》query cache：缓存查询的执行结果；
key：查询语句的hash值；
value：查询语句的执行结果；
6）mysql的函数
1》concat()：字符串连接函数
2》数学函数
avg()、sum()、max()、min()、count()；
3》文本处理函数
trim()、locate()、upper()、lower()、substring();
4》时间函数
date()、curtime()、day()、year()、now()
7）示例：
建立数据库imployee_salary
show databases;
create database imployee_salary；
use imployee_salary；
create table IT_salary(岗位类别 char(20) not null,姓名 char(20) not null,年龄 int, 员工 ID int not null, 学历 char(6), 薪资 int not null,primary key (员工 ID));
insert into IT_salary(岗位类别,类别,姓名,年龄,员工 ID,学历,年限,薪资) values('网络工程师','wang',27,011,'本科',3,4800);
select * from IT_salary;
8）explain：
分析查询语句的执行路径
其他常用命令
show status ：显示广泛的服务器状态信息
status：显示当前服务器状态
show grants：显示授权用户的安全权限
show errors or warnings：显示服务器错误或警告信息
select user() or current_user：显示当前连接用户
select now() or current_timestamp：显示当前时间
select database()：显示当前数据库
mysql数据乱码的解决方法
1）mysql数据乱码的可能原因
服务器系统字符设置问题
数据表语系设置问题
客户端连接语系的问题
2）解决方法
1》在创建数据库时设定
create database 库名 character set 'utf8' collate 'utf8_general_ci';
2》在创建表时设定字符集
create table tbl_name(字段 格式) default charset=utf8;
3》使用set names 设置默认字符集
set names utf8; ##或者charset utf8
4》永久修改，修改主配置文件在[mysql]字段中加入default_character_set=utf8。
vim /etc/my.cnf
[mysql] ##注意，不是[mysqld],否则启动时会报错。
default-character-set=utf8