mysql basic operation,mysql总结

## sudo apt-get install python3-pip
## sudo pip3 install pymysql
2、约束
  1、作用 :保证数据的完整性、一致性、有效性
2、约束分类
  1、默认约束(default)
  1、插入记录,不给该字段赋值,则使用默认值
2、非空约束(not NULL)
  1、不允许该字段的值有NULL记录
  sex enum(“M”,”F”,”S”) not null defalut “S”

load data local inpath ‘page_view.txt’
overwrite into table page_view
partition (dt=’2009-09-01′,country=’US’);

为了找出当前的数据库包含什么表(例如,当你不能确定一个表的名字),使用这个命令:

3、索引
  1、定义
  对数据库表的一列或多列的值进行排序的一种结构(Btree方式)
  2、优点
  加快数据检索速度
3、缺点
  1、占用物理存储空间
  2、当对表中数据更新时,索引需要动态维护,降低数据维护速度
4、索引示例
  1、开启运行时间检测 :set profiling=1;
  2、执行查询语句
  select name from t1 where name=”lucy99999″;
3、查看执行时间
  show profiles;
4、在name字段创建索引
  create index name on t1(name);
5、再执行查询语句
  select name from t1 where name=”lucy88888″;
6、查看执行时间
  show profiles;
5、索引
1、普通索引(index)
1、使用规则
1、可设置多个字段
2、字段值无约束
3、key标志 :MUL
2、创建index
1、创建表时
  create table 表名(…
  index(字段名),index(字段名));
2、已有表
  create index 索引名 on 表名(字段名);
  create index name on t3(name);
3、查看索引
  1、desc 表名; –> KEY标志为:MUL
  2、show index from 表名\G;
  4、删除索引
  drop index 索引名 on 表名;
2、唯一索引(unique)
  1、使用规则
    1、可设置多个字段
    2、约束 :字段值不允许重复,但可为 NULL
    3、KEY标志 :UNI
    2、创建
    1、创建表时创建
    unique(字段名),
    unique(字段名)
2、已有表
  create unique index 索引名 on 表名(字段名);
3、查看、删除 同普通索引
3、主键索引(primary key)
自增长属性(auto_increment,配合主键一起使用)
1、使用规则
1、只能有一个主键字段
2、约束 :不允许重复,且不能为NULL
3、KEY标志 :PRI
4、通常设置记录编号字段id,能唯一锁定一条记录
2、创建
1、创建表时
  (id int primary key auto_increment,
  )auto_increment=10000;##设置自增长起始值
已有表添加自增长属性:
  alter table 表名 modify id int auto_increment;
已有表重新指定起始值:
  alter table 表名 auto_increment=20000;
2、已有表
  alter table 表名 add primary key(id);
3、删除
1、删除自增长属性(modify)
  alter table 表名 modify id int;
2、删除主键索引
  alter table 表名 drop primary key;
4、外键索引
4、数据导入
1、作用 :把文件系统的内容导入到数据库中
2、语法
load data infile “/var/lib/mysql-files/文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”;
3、将scoretable.csv文件导入到数据库的表中
1、在数据库中创建对应的表
create table scoretab(
id int,
name varchar(15),
score float(5,2),
number bigint,
class char(7)
);
2、把文件拷贝到数据库的默认搜索路径中
1、查看默认搜索路径
  show variables like “secure_file_priv”;
  /var/lib/mysql-files/
2、拷贝文件
  sudo cp ~/scoretable.csv /var/lib/mysql-files/
3、执行数据导入语句
load data infile “/var/lib/mysql-files/scoretable.csv”
into table scoretab
fields terminated by “,”
lines terminated by “\n”;
4、文件权限
rwxrw-rw- 1 tarena tarena scoretable.csv
所有者 所属组
rwx : tarena用户
rw- : 同组其他用户
rw- : 其他组的其他用户(mysql用户)

create table cite_count (cited int, count int);

查看当前使用的数据库:
mysql>select database();
mysql>status;

      2、计算每个国家的平均攻击力
      select country,avg(gongji) from sanguo
      group by country;
    3、查找所有国家中英雄数量最多的前2名的 国家名称和英雄数量
      select country,count(id) as number from sanguo
      group by country
      order by number desc
      limit 2;
3、注意
  1、group by之后的字段名必须要为select之后的字段名
  2、如果select之后的字段名和group
by之后的字段不一致,则必须对该字段进行聚合处理(聚合函数)
  3、having语句
1、作用
对查询的结果进行进一步筛选
2、示例
  1、找出平均攻击力>105的国家的前2名,显示国家名和平均攻击力
  select country,avg(gongji) as pjgj from sanguo
  group by country
  having pjgj>105
  order by pjgj DESC
  limit 2;
3、注意
  1、having语句通常和group by语句联合使用,过滤由group
by语句返回的记录集
  2、where只能操作表中实际存在字段,having可操作由聚合函数生成的显示列
4、distinct
  1、作用 :不显示字段重复值
  2、示例
1、表中都有哪些国家
  select distinct country from sanguo;
2、计算蜀国一共有多少个英雄
  select count(distinct id) from sanguo
  where country=”蜀国”;
3、注意
  1、distinct和from之间所有字段都相同才会去重
  2、distinct不能对任何字段做聚合处理
5、查询表记录时做数学运算
1、运算符
  + – * / %
2、示例
1、查询时所有英雄攻击力翻倍
select id,name,gongji*2 as gj from sanguo;

insert overwrite table cite_count select cited , count(citing) from
cite group by cited;

mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[,字段名2
…]);
mysql> alter table tablename add index emp_name (name);

本节完!

load data (local) inpath ‘cite75_99.txt’ overwrite into table
cite;//若不加local则默认为HDFS路径

删除列[drop 列名]

①alter table 表名 drop 列名
例:alter table test drop pid;

=========================

r -> 4
w -> 2
x -> 1
chmod 644 文件名 rw-r–r–
5、Excel表格如何转化为CSV文件
1、打开Excel文件 -> 另存为 -> CSV(逗号分隔)
6、更改文件编码格式
1、用记事本/编辑器 打开,文件->另存为->选择编码
5、数据导出
1、作用
将数据库中表的记录导出到系统文件里
2、语法格式
select … from 表名
into outfile “/var/lib/mysql-files/文件名”
fields terminated by “分隔符”
lines terminated by “\n”;
3、把MOSHOU库下的sanguo表英雄的姓名、攻击值、国家导出来,sanguo.txt
select name,gongji,country from MOSHOU.sanguo
into outfile “/var/lib/mysql-files/sanguo.txt”
fields terminated by ” “
lines terminated by “\n”;
$ sudo -i
$ cd /var/lib/mysql-files/
$ ls
$ cat sanguo.txt
4、将mysql库下的user表中 user、host两个字段的值导出到 user.txt
select user,host from mysql.user
into outfile “/var/lib/mysql-files/user.txt”
fields terminated by ” “
lines terminated by “\n”;

配置hive时出现不能加载自己修改的hive-site.xml等配置文件的问题。发现它总是加载默认的配置文件。
解决:
hadoop的配置文件hadoop-env.sh中加上export
HADOOP_CLASSPATH=$HIVE_HOVE/conf:$HADOOP_CLASSPATH
还有一个问题:运行其他hadoop子项目时总会出现找不到类文件,必须把相关jar包拷贝到hadoop的lib目录下,导致其lib目录会越来越大。
至今不知道怎样将其他jar包加入到classpath中,网上说用export
HADOOP_CLASSPATH=“”,但是好像不行
hive –config
/root/etc/hive(注:/root/etc/hive是指hive-site.xml的存放目录)

ALTER TABLE person DROP PRIMARY KEY;
添加唯一索引:

1、SQL查询
  1、执行顺序
    3、select …聚合函数 from 表名
      1、where …
      2、group by …
      4、having …
      5、order by …
      6、limit …
  2、group by
    1、作用 :给查询结果进行分组
    2、示例
      1、查询表中一共有几个国家

show tables ‘page_.*’;

//增加一个新列

认识mysql第三篇,发出的内容适合初学者,如果能持续关注我的博客,可以全面的掌握mysql的常用知识,后续我也会陆续发出python相关的知识,关注我,和我一共进步吧!

可以在本地命令行运行hive的shell:
$ hive -e ‘select * from userinfo’ (执行hiveQL语句)
$ hive –config /hive-0.9.0/conf (重新载入新的配置文件)
$ hive –service hiveserver 50000(启动服务)

ALTER TABLE person DISABLE KEYS;
ALTER TABLE…DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。

HiveQL以分号结束。可以跨行。
在hive的shell上可以使用dfs命令执行HDFS文件操作。
dfs -ls /user/hive/warehouse;

加索引

//执行查询:
insert overwrite table query-result; //eg: insert overwrite table
query_result select * from page_view where country=’US’;
insert overwrite (local) directory ‘/hdfs-dir(local-dir)/query_result’
query;
select country , count(distinct userid) from page_view group by
countey;

ALTER TABLE person DROP INDEX birthday_index;
ALTER TABLE person DROP INDEX name_unique_index;

hive语句必须以分号“;”结束。
不支持更新,索引和事务。
表名,列名不区分大小写。
在hive的shell上可以使用dfs命令执行HDFS文件的操作。>>dfs -ls
/user/hive/warehouse/;
查看和设置临时变量:>>set
fs.default.name[=hdfs://zhaoxiang:9000];
导入jar包: >>add jar hivejar.jar;
创建函数: >>create temporary function udfTest as
‘com.cstore.udfExample’;
【在pig中使用UDF,先用register语句注册jar文件,之后可以通过完全的java类名调用,或者用define语句为UDFding
指定一个名称:
register pigjar.jar;
define UPPER org.pigjar.string.UPPER();
B = foreach a generate UPPER($0); 】

在创建数据库时报错
mysql> create database haha;
ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘haha’ mysql>  
解决方案:这是因为在mysql的usr表中,存在用户名为空的账户,虽然登陆时用的是root,但是匿名登陆。删除user.user中值为NULL的,或更新NULL为test 1)delete from user where user is NULL
2)update user set user=’test’ where user is NULL

create table page_view(viewTime int, userid bigint,
page_url string, referrer_url string,
ip string comment ‘ip address of user’)
comment ‘this id the page view table’
partitioned by (dt string, country
string)//注意table中的列不能和partition中的列重合
clustered by (userid) into 32 buckets //桶
row format delimited
fields terminated by ‘,’
collection items terminated by ‘\002’
map keys terminated by ‘\003’
lines terminated by ‘\n’
stored as textfile;

更改列类型
ALTER TABLE employee MODIFY COLUMN truename VARCHAR(10) NOT NULL DEFAULT

//创建视图
create view teacher_classnum as select teacher, count(classname) from
classinfo group by teacher;

 

select * from cite_count where count > 10 limit 10;

mysql> ALTER TABLE table_name CHANGE old_field_name
new_field_name field_type;

//子查询,只能在from子句中出现子查询
select teacher, max(class-num) from
(select teacher, count(classname) as class-num from classinfo group by
teacher)subquery
group by teacher;

增加列[add 列名]

①alter table 表名 add 列名 列类型 列参数【加的列在表的最后面】
例:alter table test add username char(20) not null default ”;
alter table test add birth date not null default ‘0000-00-00’;

②alter table 表名 add 列名 列类型 列参数 after
某列【把新列加在某列后面】
例:alter table test add gender char(1) not null default ” after
username;

③alter table 表名 add 列名 列类型 列参数 first【把新列加在最前面】
例:alter table test add pid int not null default 0 first;

=========================

show tables;
describe cite;

增加字段:

select * from cite limit 10;

复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO tab1 SELECT * FROM tab2

select count(1)/count() from cite; //count(1)相当于SQL中的count()

ALTER TABLE person ADD INDEX birthday_index (`birthday`);

在hive的shell上执行unix命令:命令前加感叹号(!),命令尾加分号(;).
hive> ! ls ;
hive> ! head hive_result;

 

//多表插入
create table mutil1 as select id, name from userinfo;
create table mutil2 like mutil1;
from userinfo insert overwrite table mutil1 select id, name
insert overwrite table mutil2 select count(distint id),name group by
name;

禁用非唯一索引

//创建外部表,指定目录位置,删除外部表时会删除元数据,表中的数据需手动删除
create external table page_view(viewTime int, userid bigint,
page_url string, referrer_url string,
ip string comment ‘ip address of user’)
location ‘path/to/existing/table/in/HDFS’;
//修改表
alter table page_view rename to pv;
alter table pv add columns (newcol string);
alter table pv drop partition (dt=’2009-09-01′);

alter table tabelname add new_field_id int(5) unsigned default 0 not
null auto_increment ,add primary key (new_field_id);

drop table cite_count;

 

//连接
select pv., choice., f.friends from page_view pv
join user u on (pv.userid=u.id)
join friends-list f on (u.id=f.uid);

4,设置密码访问权限
mysql>grant all privileges on *.* to ‘liuhui’@’%’ IDENTIFIED BY
‘liuhui’;
说明:设置指定用户名为liuhui,密码为liuhui,可访问所有数据库*

create table cite(citing int, cited int) row format delimited fields
terminated by ‘,’ stored as textfile; //sequencefle

删除字段:

//取样
select avg(viewTime) from page_view tablesample(bucket 1 out of 3 [on
id]);

复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO tab1(字段1, 字段2, …) SELECT 字段1, 字段2, … FROM tab2

//看表里有哪些列
mysql> describe pet;
+———+————-+——+—–+———+——-+
| Field   | Type        | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| ***     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+———+————-+——+—–+———+——-+
Field显示列名字,Type是列的数据类型,Null表示列是否能包含NULL值,Key显示列是否被索引而Default指定列的默认值。

在mysql中我们对数据表字段的修改命令只要使用alter就可以了,下面我来给大家详细介绍mysql中修改表字段名/字段长度/字段类型等等一些方法介绍,有需要了解的朋友可参考。

alter table t1 change a b integer;

//选择数据库
mysql>use test;  //;号可有可无,必须单行使用.  

 

//重命名列

CREATE TABLE tab2 LIKE tab1
使用和tab1表相同的结构来创建一个新表,列名、数据类型、空指和索引也将复制,但是表的内容不会被复制。外键和专用的权限也没有被复制。

ALTER TABLE person CONVERT TO CHARACTER SET utf8;
修改表某一列的编码

mysql> show create table person;
| person | CREATE TABLE `person` (
`number` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
删除列:

ALTER TABLE person MODIFY number BIGINT NOT NULL;
或者是把number修改为id,类型为bigint:

mysql修改表
表的结构如下:

3、显示数据表的结构:
describe 表名;

此问题是没有开启mysqld服务.

//查看有哪些数据库
 mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| test               |
+——————–+

ALTER TABLE person ADD UNIQUE name_unique_index (`name`);
为name这一列创建了唯一索引,索引的名字是name_unique_index.

9、表数据中的增删改查
(全部插入)insert into 表名 valus(”,”,……,”);
(部分插入)insert into 表名(列x,列y,……,列z) valus(”,”,……,”);
insert into wifi_test values(“1234567890ab”,”201506161330″,”300″);
insert into wifi_test values(“1234567890ad”,”201506161332″,0);
 insert into wifi_test(dev_id,timestamp)
values(“1234567890ag”,”201506181405″);
 insert into wifi_test(timestamp) values(“201506181505”);
select * from wifi_test;

MySQL复制表结构及数据到新表
CREATE TABLE tab_new SELECT * FROM tab_old

mysql> select * from wifi_data where dev_id like
“0023-AABBCCCCBBAA” ;

删除某个索引

alter table `user_movement_log`
Add column GatewayId int not null default 0 AFTER `Regionid`
(在哪个字段后面添加)

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

修改表的语法

 

 

2,设置访问全部数据库权限
mysql>grant all privileges on *.* to ‘root’@’%’;
说明:设置用户名为root,密码为空,可访问所有数据库*