Mysql 基础语法

欢迎光临我的博客[http://poetize.cn],前端使用Vue2,聊天室使用Vue3,后台使用Spring Boot

sql语句分类

DDL:数据定义语言(create alter drop)

DML:数据操作语句(insert update delete)

DQL:数据查询语句(select)

DCL:数据控制语句,进行授权和权限回收(grant revoke)

TPL:数据事务语句(commit collback savapoint)

用户权限管理

创建用户:
    create  user  用户名[@主机地址]  identified  by  '密码';

    create user 'user_one'@'localhost' identified by '1234';    --创建一个只能本机访问的用户
    create user 'user_two'@'192.168.1.204.%' identified by '1234';    --创建一个可以局域网访问的用户
    create user 'user_three' identified by '1234';    --创建一个可全网访问的用户
    select host,user from mysql.user;    --查看host,user


删除用户:
    drop  user  用户名[@主机地址];
    例如:drop user 'user_two'@'192.168.1.204.%';


分配权限给用户:
    grant  权限列表  on  *|库名.*|表名  to  用户名[@主机地址]  [identified by "用户密码"]  [with grant option];

    权限列表:
        all: 表示所有权限
        delete: 允许使用delete
        select: 允许使用select
        update: 允许使用update
        insert: 允许使用insert
        *.*: 表示所有库的所有表


刷新权限:
    flush  privileges;

查看权限:
    show  grants  for  用户名[@主机地址];  
    show  grants  for  'user_one'@'localhost';

查看当前用户权限:
show grants;


撤消权限:
    revoke  权限列表  on  *|库名.*|表名  from  用户名[@主机地址];
    例如:revoke update on *.* from 'user_one'@'localhost';

储存引擎

MyISAM表文件含义:
	.frm表定义,.MYD表数据,.MYI表索引

InnoDB表文件含义:
	.frm表定义,表数据空间文件(存放表的数据和索引),日志文件


(1) InnoDB支持事物,MyISAM不支持事物

(2) InnoDB支持表级锁,行级锁(默认),MyISAM支持表级锁

(3) InnoDB支持快照数据(MVCC),MyISAM不支持

(4) InnoDB支持外键,MyISAM不支持

(5) InnoDB不支持全文索引,MyISAM支持


innodb行级锁:
	1. 开销大,加锁慢,会出现死锁。
	2. 锁力度小,发生锁冲突的概率小,并发度最高。
	3. 行锁是针对索引加的锁,不是针对记录加的锁,只有在有索引的情况下才会使用行锁。
	4. 子范围锁,依据条件锁定部分范围(间隙锁)

innodb表级锁:
	1. 开销小,加锁快,不会出现死锁。
	2. 锁定粒度大,发生锁冲突的概率最高,并发量最低。
	3. 数据库全表查询,需要将整张表加锁,保证查询匹配的正确性


触发读锁:
	就是用select命令时触发读锁。

触发写锁:
	就是在你使用update,delete,insert时触发写锁,并且使用rollback或commit后解除本次锁定。


读写锁叫做权限锁(决定了加锁后用户有哪些操作权限),表级锁行级锁叫做对象锁(决定将锁加在某一行还是整张表)


MVCC多版本并发控制:
	也可称之为一致性非锁定读。
	它通过行的多版本控制方式来读取当前执行时间数据库中的行数据。实质上使用的是快照数据。

使用MVCC:
	消除锁的开销。
	如果要保证数据的一致性,最简单的方式就是对操作数据进行加锁,但是加锁不可避免的会有锁开销。
	所以使用MVCC能避免进行加锁的方式并提高并发。

数据库操作

创建数据库:
	create database ld default charset utf8 collate utf8_bin;

查看数据库:
	show databases;

查看当前数据库创建语句:
	show create database ld;

修改数据库参数:
	alter database ld default charset gbk;

删除数据库:
	drop database ld;

使用数据库:
	use ld;

表操作

创建表:
	create table mylord (
		id varchar(32) not null comment 'id',
		name varchar(32) not null comment '姓名',
		phone varchar(16) not null comment '手机号',
		primary key(id)
	)engine=InnoDB default charset=utf8 comment='表';


表约束:
    1.非空约束    NOT NULL
    2.默认值约束    DEFAULT '男'
    3.唯一约束    UNIQUE
    4.主键约束    PRIMARY KEY
    5.自增    AUTO_INCREMENT
    6.定义索引    INDEX


查询表:
	show tables;
	show tables like 'm_l%';	--_可以代表任意的单个字符,%可以代表任意的字符

查看创建表的语句:
	show create table mylord;

查看表结构:
	desc mylord;

删除表:
	drop table if exists mylord;

修改表名称:
	alter table mylord rename to nobility;


增加列:
	alter table nobility add age tinyint not null comment '年龄';

删除列:
	alter table nobility drop age;

修改列:
	alter table nobility modify name varchar(64) not null comment '姓名';

重命名列:
	alter table nobility change name username varchar(64) not null comment '用户名';

修改表参数:
	alter table nobility engine Myisam default charset gbk;

增删改

插入数据:
	insert into nobility value('1','ld',11);
	insert into nobility(id,username,phone) value('1','ld',12);

删除数据:
	delete from nobility where age >11;

修改数据:
	update nobility set age = 22 where id = '1';

查询全部:
	select * from nobility;

条件查询:
	select username from nobility where age > 10;

去除重复:
	select distinct * from nobility;


where(条件查询):

    <, >, <=, >=, =, !=或<>, is null    --字符串和数字比较是自动转化为数字,如果开头没有数字就转化为0

    between and , not between and    --例如:between A and B,相当于区间[A,B]。

        where 列 between 条件1  and 条件2;    --列在这个区间的值
        where 列 not between 条件1 and 条件2;    --不在这个区间
        where !( 列 between 条件1 and 条件2 );    --同样表示不在这个区间

    in , not in    --例如:in表示某个值出现,not in表示没出现在一个集合之中。

        where 列 in(值1,值2);    --列中的数据是in后的值里面的
        where 列 not in(值1,值2);    --不是in中指定值的数据

    is null , is not null    --空值查询

    like    --通配符:_代表任意的单个字符,%代表任意的字符

    &&(and),||(or),!(not),xor(异或)    --逻辑运算符


group by(分组):

	group by 字段 [排序方式]    --分组后排序:asc 升序(默认),desc 降序

	统计函数需配合group by使用:
		count(*)
		count(字段)
  		sum(字段)
  		max(字段)
  		min(字段)
  		avg(字段)


having(条件查询):

    本质区别:where子句是把磁盘上的数据筛选到内存上,而having子句是把内存中的数据再次进行筛选。

    where不可以使用统计函数。一般需用统计函数配合group by才会用到having:

        SELECT class FROM nobility WHERE id>5 GROUP BY class HAVING AVG(age)>0;


order by(排序):

    order by 字段1  [asc/desc],字段n  [asc/desc]    --排序: asc 升序(默认),desc 降序

    SELECT * FROM nobility ORDER BY id DESC;


select 聚集函数 from 表名 where 条件 group by 列 having 分组后的条件 order by 列
    语句的执行顺序:FROM—> WHERE—>group by—>Having—>ORDER BY—>SELECT


limit(限制查询结果数量):

    limit offset,length    --offset是指偏移量,默认为0。length是指需要显示的记录数。

    SELECT * FROM nobility ORDER BY id LIMIT 1,3;    --2,3,4


uniou(联合查询):
    将多个查询结果进行纵向上的拼接,会把结果排序的。

    (select * from nobility order by id desc limit 1) union (select * from nobility order by id limit 1)

要求:
	两次查询的列数必须一致(列的类型可以不一样,但推荐查询的每一列,相对应的类型要一样)

	可以来自多张表的数据:
		多次sql语句取出的列名可以不一致,此时以第一个sql语句的列名为准。
		如果不同的语句中取出的行,有完全相同(这里表示的是每个列的值都相同),那么union会将相同的行合并,最终只保留一行。
		也可以这样理解,union会去掉重复的行。
		如果不想去掉重复的行,可以使用union all。


内连接 inner join:

    select  *|字段列表  from  左表  [inner]  join  右表  on  左表.字段 = 右表.字段;

    数据在左表中存在,同时在右表中又有对应的匹配的结果才会被保存。如果没有匹配上,数据没有意义不会保存。
    SELECT * FROM nobility INNER JOIN class ON nobility.`classid`=class.`classid`;


外连接 outer join:

    如果数据不存在,也会出现在连接结果中。

    左外连接 left join:
        select  *|字段列表  from  左表  left  [outer]  join  右表  on  左表.字段 = 右表.字段;
        如果数据不存在,左表记录会出现。而右表为null填充。

    右外连接 right join:
        select  *|字段列表  from  右表  right  [outer]  join  左表  on  右表.字段 = 左表.字段;
        如果数据不存在,右表记录会出现。而左表为null填充。


EXISTS 查询:
    SELECT EXISTS (SELECT * FROM class WHERE id>10);    --判断是否存在:0不存在,1存在

数据备份

导出:

    mysqldump -u账户 -p密码 数据库名称>脚本文件存储地

    mysqldump -uroot -proot ld > D:/mysql.sql

导入:

    mysql -u账户 -p密码 数据库名称< 脚本文件存储地址

    mysql -uroot -proot ld < D:/mysql.sql

复制

表结构复制:
    CREATE TABLE 新表名 LIKE 旧表名;

数据复制:
    INSERT INTO 新表 SELECT * FROM 旧表;

表结构+数据复制:
    CREATE TABLE 新表名 SELECT * FROM 旧表名;

delete、drop、truncate区别

truncate 和 delete 只删除数据,不删除表结构 
drop 删除表结构,并且释放所占的空间。

删除数据的速度,drop > truncate > delete

delete 属于DML语言,需要事务管理,commit之后才能生效。
drop 和 truncate 属于DDL语言,操作立刻生效,不可回滚。

使用场合:
    当你不再需要该表时, 用 drop。
    当你仍要保留该表,但要删除所有记录时, 用 truncate。
    当你要删除部分记录时,用 delete。

索引详解

https://www.cnblogs.com/loveer/p/11562246.html

posted @ 2019-09-28 16:01  LittleDonkey  阅读(298)  评论(0编辑  收藏  举报