mysql基础及操作

1 mysq环境搭建

1.1 Ubuntu16.04安装mysql

apt-get install mysql-server
#注意中途弹框输入：mysql的root密码
#安装完成后注释只允许本地连接
vi /etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address           = 127.0.0.1

1.2 问题：navicat连接数据库报错：1130-host … is not allowed to connect to this MySql server如何处理：

mysql -u root -p
#root密码
grant all privileges on *.* to 'root'@'%' identified by 'root密码' with grant option;
flush privileges;
quit

service mysql restart

连接成功后就可以在window界面愉快的玩耍。

报错解决：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause

show variables like "sql_model";
set sql_mode="";
set sql_mode="no_engine_substitution,strict_trans_tables";

2 mysql基础操作

mysql基本操作详见官方文档：https://dev.mysql.com/doc/refman/5.7/en/tutorial.html

2.1 命令行连接：

root@ubuntu:~# mysql -u root -p
Enter password: *****
#mysql命令行
mysql>
#退出
quit
exit

2.2 数据定义语句（DDL）

数据库创建与删除

create database MYSQL_TEST;
drop database MYSQL_TEST;

数据表创建与删除

--选择数据库
use MYSQL_TEST;
--创建表
create table if not exists `mysql_test`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `age` INT NOT NULL,
   `sex` VARCHAR(40),
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--查看所有表
show tables;
--查看表结构
DESCRIBE `mysql_test`;
--删除表
DELETE TABLE  `mysql_test`; -- 逐行删除，有自增索引的话，从之前值继续累加
TRUNCATE TABLE `table_name`; -- 截断表 以页为单位（至少有两行数据），有自增索引从初始值开始累加

## 2.3 数据操作语句（DML）
**增insert**
```cpp
INSERT INTO `mysql_test`( `id`, `name`,`age`, `sex`)
                       VALUES
                       ( 1, "xiaoming", 12, "男");

删delete

DELETE FROM `mysql_test` WHERE `id` = 1;

改update

UPDATE `mysql_test` SET `sex`="女", `name`="xiaohong" WHERE `id` = 1;

2.2 数据查询语句（DQL）

查询数据库select，也可以带条件查询。

SELECT * FROM `mysql_test`;
-- 排序：asc:升序, desc:降序
SELECT name, birth FROM pet ORDER BY birth DESC;
--模糊匹配
 SELECT * FROM pet WHERE name LIKE 'b%';
 --去重
select distinct from student；
 --计数
 SELECT COUNT(*) FROM pet;
 --别名
 SELECT `sname` AS 'name' , `class` AS 'id' FROM student;
 --条件查询
 SELECT * FROM `student` WHERE `gender`="男" AND `class`=2;
 --范围查询
 SELECT * FROM `student` WHERE `class` BETWEEN 1 AND 3;
 --判空查询
 SELECT * FROM `student` WHERE `class` IS NOT NULL;
 --分页查询：分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用
-- 使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第一条记录的参数为0。
SELECT * FROM `student` LIMIT 1,2;
--分组查询
-- 可以把查询出来的结果根据某个条件来分组显示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;

聚合查询函数：

--sum() 计算某列的总和
SELECT sum(`score`) FROM `class`;
--avg() 计算某列的平均值
SELECT avg(`score`) FROM `class`;
--max()  计算某列的最大值
SELECT max(`score`) FROM `class`;
--min() 计算某列的最小值
SELECT min(`score`) FROM `class`;
--count() 计算某列的行数
SELECT count(`score`) FROM `class`;

联表查询

--INNER JOIN取交集
SELECT cid FROM `course` INNER JOIN `teacher` ON course.teacherid = teacher.tid;
--LEFT JOIN取左边表数据
SELECT cid FROM `course` LEFT JOIN `teacher` ON course.teacherid = teacher.tid;
--RIGHT JOIN取右边表数据
SELECT cid FROM `course` RIGHT JOIN `teacher` ON course.teacherid = teacher.tid;

2.3 数据控制语句（DCL）

修改权限grant

grant select, insert, update, delete on MYSQL_TEST.* to common_user@'%'

2.3 事务控制语句（TCL）

事务提交和回滚

commit; --事务提交
rollback; --事务回滚；

3 数据库设计原则

三范式：

范式1：
确保每列原子性；数据表中的所有字段都是不可分解的原子值；

范式2：
确保表中的每列都和主键相关，而不能只和主键的某一部分相关（组合索引）；
不满足：会造成数据冗余；效率降低

范式3：
确保每列都和主键直接相关，而不是间接相关；
目的：减少数据冗余

反范式

反范式：
满足上面要求会造成表变多，会造成联表查询；会造成整个系统性能降低；出于性能要求需要进行反范式设计。