2021-08-28

show variables like 'transaction_isolation';
-- set transaction isolation level serializable;
-- set session transaction isolation level serializable;
-- set global transaction isolation level serializable;

create database if not exists sql_store2;
use sql_store2;
-- drop table if exists customers;
create table if not exists customers
(
	customer_id int primary key auto_increment,
    first_name varchar(50) not null,
    points int not null default 0,
    email varchar(255) not null unique
) ;

alter table customers
add last_name varchar(50) not null after first_name,
add city varchar(50) not null,
modify column first_name varchar(55) default '',
drop points
;

create database if not exists sql_store2;
use sql_store2;
drop table if exists ordders;
drop table if exists customers;
create table if not exists customers
(
	customer_id int primary key auto_increment,
    first_name varchar(50) not null,
    points int not null default 0,
    email varchar(255) not null unique
);

create table orders
(
	order_id    int primary key,
    customer_id int not null,
    foreign key fk_orders_customers(customer_id)
		references customers(customer_id)
        on update cascade
        on delete no action
);

alter table customers
add last_name varchar(50) not null after first_name,
add city varchar(50) not null,
modify column first_name varchar(55) default '',
drop points
;


alter table orders
	add primary key(order_id),
    drop primary key,
	drop foreign key fk_orders_customers,
    add foreign key fk_orders_customers(customer_id)
		references customers(customer_id)
        on update cascade
        on delete no action;

show charset; --  展示所有可以设置的语言
create database db_name
	character set latin1;

alter database db_name
	character set latin1;
    
create table table1
(lieming varchar(20) character set latin1 not null)
character set latin1;

alter table table1
character set latin1

alter table customers
engine = innodb

use sql_store;
explain select customer_id from customers where state = 'ca';
-- select count(customer_id) from customers

create index idx_state on customers(state);

use sql_blog;
select *
from posts
where title like '%react redux%' or
	body like '%react redux%'
-- % 代表了任意数量的字符

select *, match(title, body) against('react redux')
from posts
where match(title, body) against('react -redux +form' in boolean mode);

-- create fulltext index idx_title_body on posts(title, body);

-- select *, match(title, body) against(' react redux')
-- from posts
-- where match(title, body) against(' react redux');

-- select *, match(title, body) against(' react redux')
-- from posts
-- where match(title, body) against(' react -redux' in boolean mode);
--  不带 redux 文件

-- select *, match(title, body) against('react redux')
-- from posts
-- where match(title, body) against('react -redux +form' in boolean mode);

select *
from posts
where match(title, body) against('"handling a form"' in boolean mode);

-- 短的类似地址、名字等,可以用前缀索引;长的微博等,可以使用全文索引。

use sql_store;
show indexes in customers;
create index idx_state_points on customers(state, points);
explain select customer_id from customers
where state = 'ca' and points > '1000';

-- create user me@127.0.0.1;
-- create user john@localhost;
--  增加域范围create user john@域名.com;
--  增加子网范围 create user john@'%. 域名.com';
-- 任意地方连接 create user john
-- 设置密码 create user john identified by '1234';

-- select * from mysql.user; 查看用户
-- drop user 域名;删除用户

-- set password  用户名 = ’密码‘;修改密码

-- 1:web、desktop application
-- create user moon_app identified by '1234';
-- grant select, insert, update, delete, execute
-- on sql_store.*
-- to moon_app;

-- 2:admin
-- grant all on *.* to moon_app;

-- show grants;查看当前用户权限
-- show grants for moon_app; 查看具体用户权限

-- 撤销权限
-- revoke create 权限 on sql_store.* from moon_app;
-- grant create  权限  on sql_store.* to moon_app; 对比增加授权



上一篇:数据分析应该掌握的知识及SQL技能


下一篇:SQL如何获取时间的方法?