15. mysql 常用语句

点击量:21

yum install mariadb-server mariadb;
systemctl start mariadb;
systemctl stop mariadb;
systemctl restart mariadb;
set password for 'root'@'localhost' =password('passwd'); 
create user username identified by 'passwd';
grant all privileges on *.* to 'username'@'%' identified by '1234@abcd.';


逻辑备份
mysqldump -usl1 -p startlive --lock-all-tables>/root/startlive.sql;
mysqldump --no-data(只显示创建信息) --no-create-info (只显示数据)
导入,登录mysql之后,执行sql语句
source /root/startlive.sql

-- 查询用户
SELECT * FROM mysql.user;
-- 删除用户
DROP USER 'bigline'@'%';

-- 创建用户
create user bigline identified by '123456';
-- 授权(使用root用户登录后执行授权语句)  命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
-- 说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等.如果要授予所的权限则使用 ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*.
grant all on bigline.* to 'bigline'@ '%';


-- 创建数据库和
CREATE DATABASE IF NOT EXISTS bigline ;
CREATE USER 'bigline'@'%' IDENTIFIED BY '123456' ; GRANT ALL ON bigline.* TO 'bigline'@'%';


-- 解决1396问题
DROP USER 'bigline'@'%'; FLUSH PRIVILEGES ;
CREATE USER 'bigline'@'%' IDENTIFIED BY '123456' ;


-- 创建视图
create view test_create as
select * from mysql.user;

-- 删除
drop view test_create;  



CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

show databases;
show tables;
show create table t2;
show columns form t2;
desc t2;
describe t2;

alter database us_states default character set 'utf8';
alter table t2 default character set 'utf8';
alter table t2 convert to character set 'utf8';
show create table t2;
show create database us_states;

create table t2(
    id int not null auto_increment,
    state not null varchar(20) default NULL
)


select Date(createdate) from users group by Date(createdate);



alter table t1 change a b int;
    change name a -> b and set type to int
alter table t1 change b b int not null;
    alter table t1 modify b bigint not null;

alter table t1 add d timestamp,add a int(10),add b int(20);
alter table t1 drop c,drop a,drop b,drop d;

update table users set name='1',birthday='2011/1/1',age=20,desc1='what is' where id =1;

alter table t1 rename t2;

show columns from t2;

alter table t2 add index indexname(column1[,column2,column3,column4]);

alter table t2 addd primary key(id);

alter table t2 add unique name(column)

1alter talbe t2 drop index name;

发表评论

电子邮件地址不会被公开。

8 + 17 =