MySQL基础知识
2017-02-19 本文已影响0人
FangHao
MySQL安装
sudo apt-get install mysql-server mysql-client
管理服务
sudo service mysql start/stop/restart
允许远程连接
修改配置文件
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
将bind-address=127.0.0.1注释
用户赋权
grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;
重启数据库
数据库操作
创建数据库
create database DB_Name charset=utf8;
删除数据库
drop database DB_Name;
切换数据库
use DB_Name;
显示当前使用数据库
select database();
显示存在的数据库
show databases;
表操作
创建表
create table Table_Name(
id int auto_increment primary key not null,
name varchar(10),
cls_id int,
foreign key(cls_id) references class(id),
index index_id(id(11)),
.........
);
修改表
alter table Table_Name add|change|motify|drop column
alter table students add stu_name varchar(10) not null;
alter table students motify stu_name char(10);
alter table students change stu_name students_name varchar(10);
alter table students drop stu_name;
alter table students add constraint stu_cls foreign key(cls_id) references class(id);
alter table students drop foreign key stu_cls;
表的约束
- 主键primary key
- 非空not null
- 惟一unique
- 默认default
- 外键foreign key
索引
创建索引
普通索引
create index Index_Name on Table_Name(column【(length)】【desc|asc】);
唯一索引
create unique index Index_Name on Table_Name(column【(length)】【desc|asc】);
全文索引
create fulltext index Index_Name on Table_Name(column【(length)】【desc|asc】); --MySQL5.6之后InnnoDB也可以创建全文索引，全文索引主要是对char、varchar、text做的查询优化
多列索引
create index Index_Name on Table_Name(
column1【(length)】【desc|asc】,
column2【(length)】【desc|asc】
column3【(length)】【desc|asc】
......);
删除索引
drop index Index_Name on Table_Name;
视图
创建视图
create view View_Name as 查询语句;
查看视图
select * from View_Name;
show create view View_Name;
删除视图
drop view View_Name 【,View_Name2,.....】;
修改视图
create or replace view View_Name as 查询语句;
触发器
创建触发器
create trigger trigger_Name
BEFORE|AFTER trigger_Event
on TABLE_NAME FOR EACH ROW trigger_STMT;
trigger_Event:
- INSERT
- DELETE
- UPDATE
trigger_STMT:
满足触发条件后执行的语句
删除触发器
drop trigger Trigger_Name;
数据维护
插入
insert into Table_Name(name,gender) values ('张三',1),('李四',1),('王五',1);
insert into Table_Name values (0,'张三',1),(0,'李四',1),(0,'王五',1);--不选择插入的列需要全部的列都要写，默认的列的值如果写上会被替换，如果不写可以用default代表默认
删除
delete from Table_Name where id=1;
truncate table Tabel_Name;--注意截断表的数据无法恢复，但是删除速度快
更新
update Table_Name
set field1 = value1,
field2 = value2
where CONDITION;
查询
查询语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit star,count;
执行顺序
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit star,count
聚合
count()
sum()
avg()
max()
min()
关系
join
inner join
left join
right join
select * from sutdents inner join class on students.cls_id = class.id;
自关联
create table areas(
aid int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);
事务
begin;
commit;
rollback;
数据库备份
备份
mysqldump -uroot p DatabaseName > ~/Desktop/back.sql
恢复
mysql -uroot –p DatabaseName < ~/Desktop/back.sql
用户
创建用户
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
- username：你将创建的用户名
- host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
- password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
删除用户
DROP USER 'username'@'host';
赋权
GRANT privileges ON databasename.tablename TO 'username'@'host'
- privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
- databasename：数据库名
- tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;--赋权给用户可以给他们赋权的权限
撤销赋权
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
- privilege, databasename, tablename：同授权部分
密码安全
修改密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前用户
SET PASSWORD = PASSWORD("newpassword");
