linux下安装指令
yum install mariadb-server -y //如果已安装可以省略
systemctl start mariadb.service //启动服务
systemctl enable mariadb.service //开机启动服务
mysql -u root -p //登录mysql
登录
远程登录 mysql -h192.168.9.151 -uroot -p123456
本机登录 mysql -uroot -p123456(省略了本机ip)
数据库指令
命令 | 描述 | 范例 |
---|---|---|
show databases | 显示所有数据库 | |
create database | 创建数据库 | create database test; |
use | 使用指定数据库 | use test; |
drop | 删除数据库 | drop database test; |
数据表指令
命令 | 描述 | 范例 |
---|---|---|
create | 创建表 | create table class (id integer,name varchar(25)); |
show | 显示所有表 | show tables; |
describe | 查看表结构 | describe class; desc class; |
add | 添加表字段 | alter table class add description varchar(50),add age integer; |
alter | 删除表字段 | alter table class drop age; |
insert | 插入一条记录 | insert into class (id,name,description) values (100000001,‘计算机’,‘这是一个计算机班’); |
select | 查询表中的数据 | select * from class;select * from student where name like “%碧落%”;select * from student order by count asc; |
drop | 删除表 | drop table student; |
常见指令
命令 | 描述 | 范例 |
---|---|---|
distinct | 列出不同(distinct)的值 | SELECT DISTINCT Company FROM Orders |
order by | 排序 | SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC(在以上的结果中有两个相等的公司名称 (W3School)。只有这一次,在第一列中有相同的值时,第二列是以升序排列的。如果第一列中有些值为 nulls 时,情况也是这样的) |
join | 用于根据两个或多个表中的列之间的关系,从这些表中查询数据 | SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName |
index | 用于在表中创建索引 | CREATE INDEX PersonIndex ON Person (LastName, FirstName) |
备份
备份MySQL数据库某个(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
还原数据库
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
常见错误
mysql乱码问题
MariaDB [(none)]> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
默认安装后编码如下,导致写入中文字符各种失败
mysql> SHOW VARIABLES LIKE 'character%';
mysql> SET character_set_client = utf8 ;
mysql> SET character_set_connection = utf8 ;
mysql> SET character_set_database = utf8 ;
mysql> SET character_set_results = utf8 ;
mysql> SET character_set_server = utf8 ;
输入密码后登录失败
修改 /etc/my.cnf文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock