创建用户 create user zabbix@'10.0.0.%' identified by '123';
查询用户 select user,host,password from mysql.user;
删除用户 drop user root@'db02';
创建用户并授权 grant all on . to root@'10.0.0.%' identified by '123';
查询用户权限 show grants for zabbix@'10.0.0.%';
收回权限 revoke create,drop on testdb.* from zabbix@'10.0.0.%';
创建数据库 create database lufei; create database llf CHARACTER SET utf8 ;
查看数据库 show databases; show create database llf; help create database;
删除数据库 drop database llf;
改库的字符集: alter database lf charset utf8mb4;
表: 切换数据库 use lufei; 创建: create table t1 (id int ,name varchar(20)); 查询: show tables; show create table t1; desc 删除 drop table t1; 修改: (1)在表中添加一列 alter table t1 add age int; (2)添加多列 alter table t1 add bridate datetime, add gender enum('M','F'); (3)在指定列后添加一列 alter table t1 add stu_id int after id; (4)在表中最前添加一列 alter table t1 add sid int first; (5)删除列 alter table t1 drop sid; (6)修改列名 alter table t1 change name stu_name varchar(20); (7)修改列属性 alter table t1 modify stu_id varchar(20); (8)修改表名 rename table t1 to student; alter table student rename to stu;
DML语句:数据操作语言
数据插入 use lufei create table t1 (id int ,name varchar(20)); insert into t1 values(1,'zhang3'); select * from t1; insert into t1 values (2,'li4'),(3,'wang5'),(4,'ma6'); //多行数据插入,用逗号隔开 insert into t1(name) values ('xyz'); //指定插入列值的名字
数据修改,更新 update update t1 set name='zhang33' ; ----会更新表中所有行的name字段,比较危险。!!!! update t1 set name='zhang55' where id=1; ----update在使用时一般都会有where条件去限制,不要忽略了set哦!
数据删除 delete delete from t1 ; --删除表中所有行,比较危险。!!! 一行一行删除表中数据。 delete from t1 where id=2;
DDL truncate table t1; ---在物理上删除表数据,速度比较快。清空表数据
DQL
select 基本查询
DESC world.city
SELECT id ,NAME FROM world.city;
SELECT * FROM world.city
;
select 条件查询 where
①查询中国(CHN)所有的城市信息
SELECT * FROM world.city
WHERE countrycode='CHN';
②查询中国(CHN)安徽省所有的城市信息。
SELECT * FROM world.city
WHERE countrycode='CHN'
AND
district='anhui';
③查询世界上人口数量在10w-20w城市信息
SELECT * FROM world.city
WHERE
population BETWEEN 100000 AND 200000 ;
④中国或者日本的所有城市信息
where字句中的IN
SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');
⑤模糊查询
SELECT * FROM world.city
WHERE countrycode LIKE 'ch%';
select 排序并限制
①按照人口数量排序输出中国的城市信息(ASC\DESC)
SELECT * FROM world.city
WHERE countrycode='CHN' ORDER BY population ASC;
SELECT * FROM world.city
WHERE countrycode='CHN' ORDER BY population DESC;
②按照多列排序人口+省排序
SELECT * FROM world.city
WHERE countrycode='CHN'
ORDER BY id DESC ;
SELECT * FROM city ORDER BY 5 DESC ;
1-10
SELECT * FROM world.city
WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 20;
11-20
SELECT * FROM world.city
WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 10,10 ; //跳过前10,接着选10个出来
**** 牢牢记住下面join on这种语句即可!!! SELECT ci.name ,ci.countrycode,ci.population ,co.name FROM city AS ci JOIN country AS co ON ci.countrycode=co.code AND ci.name='qingdao';
group by +聚合函数(avg()、max()、min()、sum())
SELECT countrycode ,SUM(population) FROM city WHERE countrycode = 'chn' GROUP BY countrycode; //注:不加group by显示出来的结果也是一样的,目前没看出来区别呢?
字符集和列属性:
alter database oldboy CHARACTER SET utf8 collate utf8_general_ci; alter table t1 CHARACTER SET latin1;
注意:更改字符集时,一定要保证由小往大改,后者必须是前者的严格超集。生产中别随便改!!!
列属性 primary key 主键:非空、唯一 unique:唯一 create table student(id int not null primary key AUTO_INCREMENT); create table student1(id int not null primary key AUTO_INCREMENT,name varchar(20))charset utf8; create table teacher(id int not null ,name varchar(20) not null); create table teacher1(id int not null ,name varchar(20) not null,beizhu varchar(20) not null default "ok");
mysqldump -uroot -poldboy123 world country >> /bakcup/world_country.bak.sql
####sql语句拼接方法:!!! 注意:变量前后都用逗号分隔开 select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"_",table_name,".bak.sql") from information_schema.tables where table_schema='world';
常用命令: show databases show create database oldboy show tables show create table t1 SOHW databases:列出所有数据库 SHOW TABLES:列出默认数据库中的表 SHOW TABLES FROM <database_name>:列出指定数据库中的表 SHOW COLUMNS FROM <table_name>:显示表的列结构 SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息 SHOW CHARACTER SET:显示可用的字符集及其默认整理 SHOW COLLATION:显示每个字符集的整理 SHOW STATUS:列出当前数据库状态 SHOW VARIABLES:列出数据库中的参数定义值