Fulongのblog

MySQL命令和SQL

2018/03/26 Mysql 共 4364 字，约 13 分钟

linux的mysql安装及配置

安装

How To Install MySQL on Debian 9 

sudo apt update 
sudo apt upgrade

wget http://repo.mysql.com/mysql-apt-config_0.8.9-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.9-1_all.deb

sudo apt update 
sudo apt install mysql-server

sudo systemctl restart mysql

mysql -u root -p

mysql更改密码和创建用户

UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
FLUSH PRIVILEGES;
select host,user,password from user ;
# 创建admin用户可远程也可本地访问，超级用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY '1234';
CREATE USER 'admin'@'%' IDENTIFIED BY '1234';
GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

grant

查看默认端口号

查看并修改默认端口号

show global variables like 'port';

mysql远程连接

mysql -h 192.168.5.116 -P 3306 -u root -p123456

配置可远程连接

配置mysql允许远程链接

update user set host = '%' where user = 'root';
select host, user from user;

还需要修改bind-address 在/etc/mysql/mysql.conf.d目录下的mysqld.cnf文件

bind-address=0.0.0.0

mysql重启或关闭

mysql重启或关闭

service mysqld restart 
service mysqld stop

符号`的作用

区分关键字，防止表名或者列名与关键字区分

DOS启动和关闭mysql服务

net start查看都有哪些服务被开启
net start MySQL57forMFL
net stop MySQL57forMFL

DOS连接数据库

mysql -h 127.0.0.1 -u -p
需要管理员权限

登陆

mysql -u root -p

root为用户名 ，后让你输入密码

创建用户

添加用户的方法为通过SQL的 GRANT 命令，你下命令会给指定数据库TUTORIALS添加用户 zara ，密码为 zara123

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

管理数据库

USE 数据库名 :
选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
SHOW DATABASES: 
列出 MySQL 数据库管理系统的数据库列表。
SHOW TABLES:
显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。
SHOW COLUMNS FROM 数据表:
显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
SHOW INDEX FROM 数据表:
显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G: 
该命令将输出Mysql数据库管理系统的性能及统计信息。

数据库基本操作

create database dbname;
show databases;
drop database dbname;
use dbname;

存储引擎

show engines;
show engines \g;

安装版mysql默认是InnoDB,免安装版是MyISAM

常用的三种：

  1. MyISAM：不支持事物，不支持外键，速度快，适合对事务完整性没有要求并以访问为主的应用
  2. InnoDB：在事务上有优势，支持提交，回滚等，但占用更多磁盘空间，会频繁更新，适合需要实现并发控制的应用
  3. MEMORY：使用内存来存储数据，速度快，但在安全上没有保障，适合数据小、快速访问的应用

数据类型

  1. tinyint;smallint;mediumint;int/integer;bigint
  2. float; double; dec(m,d)/decimal(m,d); bit(m)
  3. date; datetime; timestamp; time; year
  4. char(m); varchar(m) 经常变化选择varchar

表的操作

create tabel t1(
    id int,
    name varchar(20)
);

查看表结构
describe tablename;

查看表详细定义
show create table tablename;
尾部加\g格式化

drop table tablename;
alter table oldtablename rename newtablename;
alter table add id2 int; //最后位置加字段
            add id2 int first; //第一个位置加字段
            add id2 int after id; //指定位置
            drop id2;//delete
            modify id2 varchar(10);//修改类型
            change id2 id3 新的数据类型// id2改为id3
            modify id int after id2;//修改顺序id2之后
            modidy id int first//第一位置

约束

not null
default
unique(uk)
primary key(pk)
auto_increment
foreign key(fk)

constraint name ...
可以不命名

auto_increment 只有一个整数类型字段可以设置
foreign key(id) references tablename(id2)

索引的操作

  • InnoDB和MyISAM支持BTREE索引，MEMORY支持hash索引

普通索引

create table t(
    id int,
    name varchar(10),
    index/key indexname(id) asc/desc
    默认是asc
)

/ 

create index/key indexname on tablename(id asc)

/

alter table tablename add index/key indexname(id asc)

检验是否创建成功用show create table tablename \g
检验是否启用用explain select....

唯一性索引（索引的值唯一）

unique index/key....

全文索引(主要关联char , varchar , text等字段，只支持myisam的)

fulltext index...

多列索引

index indexname (id asc,id2 desc)

删除索引

drop index indexname on tablename

视图的操作

create view viewname as select...

show tables;

show create view viewname;

drop view viewname;

create or replace view ....

alter view as ....

视图来自多个基本表时，不允许添加和删除数据

触发器

create trigger triggername
    before/after delete/insert/update
        on tablename
            [for each row]
    begin
    ...
    end

show triggers \g

drop trigger tirggername;

数据的操作

insert into tablename(id,id2) values(3,4);

insert into tablename(id,id2) values(1,2),
                                    (1,3),
                                    (2,4)
//以上时一次性插入多条记录

insert into tablename(id,id2) select a,b from tablename2;

update tablename
    set id=3,
        id2=4
    where....

delete from tablename where ...

单表记录查询

select distinct id from tablename;

concat()用来连接字符串

select concat(ename,'年薪',sal*12) from...
select ...as 换名
或者select a b,c from....这里是a换成b

where 有is null, in, like等
还有and or xor not或者&& || xor !
还有between . and .
in (a,b,c)

like
%匹配多个字符
_匹配一个字符

注意：如果in的集合中有NULL,则使用not in没有任何查询结果

排序查询

order by id asc,id2 desc

限制数据记录查询数量

select ...where..order by...
    limit 起始偏移量，行数
/
    limit 0， 行数
默认0开始

统计函数

count()忽略null
avg()
sum()
max()
min()

分组查询(查询每个分组随机一条记录)

group by id,id2

如要要引用分组的记录元素，用select GROUP_CONCAT(ID)..group by id2,group_concat()输出的是一个列表，逗号相隔的

having限定分组查询

group by...
having..

多表数据记录查询

  • union
  • 内连接，舍弃不匹配的记录
  • 左外连接，包括左边表中不匹配的记录
自然连接
select ...from t1 join t2 on id
等值连接
t1 join t2 on a=b

outer join
left join
right join

子查询

where (id,id2)=(select id3,id4 from...)
//单行返回结果

多行用in,any,all

exists

where exists(...)

存储过程和函数

函数必须有返回值，存储过程没有，参数类型也多于函数参数类型

create procedure procname()
begin
..
end

para: in/out/inout id type; in/out这些可以没有

create function ...

DELIMITER \(作用：把提交符号设置为\) ； 以免存储过程里有;当提交了

SQL学习

请参考: https://www.nowcoder.com/ta/sql

Search

    Table of Contents

    Popular Posts