MySQL笔记

<?php
数据库：
在查询语句末尾加 \G 可调整为竖向查看
show create table table_name 可查看建表结构
查看当前所在数据库：
select databases();
创建三步曲：
create database 数据库名;
use 数据库名
set names gbk;

数据库的删除：drop database 数据库名;

查看数据库：show databases;

创建数据表：
create table 表名(
字段1 字段类型,
如：a int
);
查看数据表：show tables;

查看表的创建语句：show create table 表名\G

查看表的结构：desc 表名;

删除数据表： drop table if exists 表名;

修改表名：rename table 旧表 to 新表;

表添加字段：
alter table 表名 add 字段 字段类型;
表删除字段：
alter table 表名 drop 字段 字段类型;
修改字段类型：
alter table 表名 modify 字段 字段类型;
修改字段名：
alter table 表名 change 旧字段名 新的字段名 字段类型;


键属性：
null --not null --not null default（默认值）

主键：primary key

唯一键：unique key 或 unique

自动增长：auto_increment 用法：primary key auto_increment

外键：foreign key (要设置的外键字段) references 父表（要设置的外键字段）
（简单来说，外键就是外面的主键，外面是其他表！外键应该是在子表定义，插入数据时先插父再插子）

删除外键的语法：
alter table 表名 drop foreign key 系统自动创建的外键名;

增加外键的语法：
alter table 子表 add foreign key (要设置的外键)
references 父表（要设置的外键） on update cascade;


数据操作：

插入数据：
insert into 表名 (字段) values (值);

修改数据:
update 表 set email='123@qq.com' where user_id=1;

查询数据：
select */字段列表 from 表名[查询条件];

删除数据：
delete from 表名 where 删除条件;

 

使用unsigned控制是否有正负！如果不写，默认的是有符号的！

使用使用zerofill来进行前导填充
如：alter table 表名 add num int(4) zerofill;

浮点数：-单精度 float
有效位数是6到7位

-双精度 double
有效位数16到17位
都支持 type（M,D）形式

定点数：decimal
定点数一样支持 decimal(M,D) 语法

字符串类型：
-char 某一个数据是固定长度的，就用char

-varchar

枚举类型：enum (类似单选项)
create table en(
gender enum('fenale','male') -- 可用于数据区分男女
);

集合类型：set (类似多选项)

数据操作：

蠕虫复制：
insert into 表名 select */字段列表 from 表名(被复制);
短期内为表产生大量的数据，为了测试数据库的压力以及效率

主键重复：
replace into 表名[字段] values（值）;
直接删除原纪录再插入


truncate 表名; 直接删除整表内容，但结构保留

查询数据：
select */字段列表 from 表名 where 查询条件;

去重：distinct 去掉重复的记录
#往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是 distinct只能返回它的目标字段，而无法返回其它字段
select distinct a,b,c, from 表名;

创建别名：
select 字段名 as 字段别名 from 表名

where子句：
select * from 表名 where 字段 between A and B;(A必须小于B)
select * from 表名 where 字段 in (1,3,5);
select * from 表名 where 字段 a>2 and b<60;

group by子句：
select*from 表名 group by 字段; --(分组统计查询)

sum()：求和，就是将某个组内的某个字段的值全部相加

max()：求某组内的某个字段的最大值

min()：求某组内的某个字段的最小值

avg()：求某组内某个字段的平均值

count()：统计某组非null记录的个数，通过用count(*)来表示！

单字段：
select 字段名,count(*),avg(score) from 表名 group by 字段名;

多字段：
select 字段名,gender,count(*),avg(score) from 表名 group by 字段名,gender;

回溯统计：其实就是在查询语句的后面加上with rollup即可

having 子句：
select name,avg(score) from 表 having avg(score)>80;

常的是对group by之后的统计结果再次进行以某种条件判断进行筛选

order by子句:
select * from 表 order by score; --(默认为asc，也就是升序！)

多字段排序
select * from 表 order by score,age desc;
--（先按score进行升序排序，如果遇到相同的分数再按年龄进行降序排序）

limit 子句：
select*from 表 limit 查询记录数;

select*from 表 limit 偏移量,查询记录数;

如果用 $Num 代表第多少页，用 $Per 代表每页的数量：
limit ($Num-1)* $row,$Per

联合查询：
要查询一个php_student表中字段值中score最高的，以及一个字段值2中score最低的
(select*from 表 where home = '字段值1' order by score desc limit1)
union
(select*from 表 where home = '字段值2' order by score limit1);

1，如果有order by就必须把该语句加上一对括号
2，如果有order by，就必须使用limit才能够是排序的效果生效！


连接查询（交叉查询）
select*from 表1 cross join 表2;

内连接：
首先，内连接要区分左表和右表，出现在join关键字的左边就是左表，反之就是右表
select*from 子表 inner join 父表 on 子表.class_id = 父表.class_id;

外连接：
左外连接：
select*from 子表 left join 父表 on 子表.条件 = 父表.条件;

右外连接：
select*from 子表 right join 父表 on 子表.class_id = 父表.class_id;

自然内连接：
select*from 左表 natural join 右表；

自然外连接：
左外连接：
select*from 左表 natural left join 右表
右外连接：
select*from 左表 natural right join 右表

子查询：
如何得到php_student表中成绩最高的学生的记录：
select max(score) from 表;
select*from php_student where score =(select max(score) from 表);

列子查询：
找出php_student表中所有已经开班了的学生的信息
select class_id from php_class;
select*from php_student where class_id in (select class_id from php_class);

行子查询：
要查询表中年龄最大而且分数最高的记录
select*from php_student where(age,score)=(select max(age),max(score) form php_student);

表子查询：
多行多列的子查询
找出表中每一个家乡（home）分数最低的那个学生
select*from php_student order by score;
select*from (select*from php_student order by score) as s group by home;

去重复：DISTINCT

修改数据库密码：
以root用户登录，命令：mysql -uroot -p 回车 输入密码；
mysql>use mysql；
mysql>UPDATE user SET password=PASSWORD('输入新密码') WHERE user='root';
mysql>FLUSH PRIVILEGES;

导出某个库的表到本地的目录(例如mysql库的user表)( 只导出表结构：在库前面加 -d )
# 该例子中 mysql -ujxqy -ptengdingtech -h1.4.41.50 -P1025 jxqy_central库 st_level_system表
mysqldump -ujxqy -ptengdingtech -h1.4.41.50 -P1025 --routines --default-character-set=utf8 --tables jxqy_central st_level_system > /st_level_system.sql
mysqldump -ujxqy -ptengdingtech -h1.4.41.50 -P1025 jxqy_central st_payment_top --where="pay_date='2016-11-24'" > /st_payment_top.sql

导入sql的命令行如下：
第一种方法:
mysql -ujxqy -ptengdingtech -h1.4.41.50 -P1025 --default-character-set=utf8 jxqy_central < db.sql
第二种方法：
mysql> use mysql;
mysql> source /tmp/db.table.sql;
source "路径名"+/mytest_emp_dept.sql

WEEKDAY('2016-12-06') 返回星期索引 0为星期一 6为星期天

group_concat(); 默认以逗号分割（有限制长度，可以执行SQL：SET GLOBAL group_concat_max_len = 102400;）