Mysql

数据库分类

#分两大类：
　　关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
　　非关系型：mongodb，redis，memcache

#可以简单的理解为：
关系型数据库需要有表结构
非关系型数据库是key-value存储的，没有表结构

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

#1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
#2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
#3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

create database db1 charset utf8;
 use db1;
 create table t1(  
 id int, 
 name varchar(50),
 sex enum('male','female'),
 age int(3)
 );
show tables; #查看db1库下所有表名
 desc t1;

库操作

增：create database db1 charset utf8;
查：show databases;
用：use db1
改：alter database db1 charset latin1;
删除: drop database db1;

表操作：

use db1;
create table   t1(  
    id int, 
    name varchar(50),
    sex enum('male','female'),
    age int(3)      #  表中的最后一个字段不要加逗号 ！！！
    );
show tables; #查看db1库下所有表名
desc t1;

修改表：

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

复制表：

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

删除表：

DROP TABLE 表名;

 

数据行操作

插入数据：

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

改数据：

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

删数据：

语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

练习：
    更新MySQL root用户密码为mysql123
    删除除从本地登录的root用户以外的所有用户

查数据：

单表查询：http://www.cnblogs.com/linhaifeng/articles/7267592.html

多表查询：http://www.cnblogs.com/linhaifeng/articles/7267596.html

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

#2. 操作文件
先切换到文件夹下：
use db1
增：create table t1( id int,
name varchar(50),
sex enum('male','female'),
age int(3)
)
(注意注意注意：表中的最后一个字段不要加逗号)
查：show tables;
查:describe t1; 查看表结构，可简写为desc 表名
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删：drop table t1;

复制表：
复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2; //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create table t4 like employees;

#3. 操作文件中的内容/记录
增：insert into t1 values(1,'egon1',18,'male'),
(2,'egon2',18,'male'),
(3,'egon3',18,'male');
insert into t1(id) values(1),
(2);
插入查询结果
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
查：
SELECT CONCAT('姓名:', name, '年薪:',salary*12) AS Annual_salary
FROM employee;
SELECT DISTINCT 字段1,字段2...
FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数
结合CASE语句：
SELECT
(
CASE
WHEN NAME = 'egon' THEN
NAME
WHEN NAME = 'alex' THEN
CONCAT(name,'_BIGSB')
ELSE
concat(NAME, 'SB')
END
) as new_name
FROM
emp;

SELECT name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000;
SELECT name,post_comment FROM employee
WHERE post_comment IS NULL;
SELECT name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ;
SELECT * FROM employee
WHERE name LIKE 'eg%';
GROUP BY

单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

聚合函数

SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;

查询岗位名以及各岗位的平均薪资
select post,avg(salary) from employee group by post;
查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from employee group by sex;

HAVING过滤

HAVING与WHERE不一样的地方在于!!!!!!

#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;

查询排序:ORDER BY

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee ORDER BY age asc,hire_date desc;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;

限制查询的记录数:LIMIT

SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0

SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

使用正则表达式查询

查看所有员工中名字是jin开头，n或者g结尾的员工信息
select * from employee where name regexp '^jin.*[gn]$';
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';

子查询

带IN关键字的子查询

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);

带比较运算符的子查询

select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

带EXISTS关键字的子查询

select * from employee
-> where exists
-> (select id from department where id=204);

查询每个部门最新入职的那位员工

SELECT * FROM emp AS t1 INNER JOIN (
SELECT post,max(hire_date) max_date FROM emp GROUP BY post) AS t2 ON t1.post = t2.post
WHERE t1.hire_date = t2.max_date;

改：update t1 set name='sb',age=18 where id=2;
删：delete from t1 where id=1;

清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

auto_increment 表示：自增
primary key 表示：约束（不能重复且不能为空）；加速查找

 

----------------------------------------------------------------------------------

mysql常用数据类型概览

#1. 数字：
整型：tinyinit int bigint

tinyint[(m)] [unsigned] [zerofill]

小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127
无符号：
0 ～ 255

PS： MySQL中无布尔值，使用tinyint(1)构造。

int[(m)][unsigned][zerofill]

整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
小数：
定点数类型 DEC等同于DECIMAL　　

浮点类型：FLOAT DOUBLE

作用：存储薪资、身高、体重、体质参数等

float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
0.000001230123123123
存成：0.000001230000

decimal：（如果用小数，则用推荐使用decimal）
精准
内部原理是以字符串形式去存

#2. 字符串：
char（10）：
简单粗暴，浪费空间，存取速度快,
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
例如：root存成root000000,查询时却会很不要脸地删除尾部的空格
varchar：精准，节省空间，存取速度慢,字符长度范围：0-65535
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，
有时甚至可以超出 varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的
因素，以求达到最佳的平衡
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别 比如地址或描述信息

>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。

 

#3. 时间类型：
DATE TIME DATETIME TIMESTAMP YEAR
最常用：datetime
作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

#4. 枚举类型与集合类型
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
CREATE TABLE shirts (
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')）
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

-----------------------------------------------------------------------------------------
完整性约束

一 介绍
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

二 not null与default
是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
create table tb1(
nid int not null defalut 2,
num int not null
)
三 unique
唯一约束
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);

#联合唯一
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

四 primary key

从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。

一个表中可以：

单列做主键

#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

多列做主键（复合主键）

create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);

五 auto_increment

create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

六 foreign key

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,

constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade
)engine=innodb;

 

------------------------------------------------------------------------------------

索引原理与慢查询优化

普通索引INDEX：加速查找

唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引