MYSQL-SQL操作-小总结

一、编码设置

1.检查当前数据库编码

　　使用语句

show variables like '%character%';
show variables like '%collation%';

 

2.MySQL设置编码

SET character_set_client = utf8;

SET character_set_connection = utf8;

SET character_set_database = utf8;

SET character_set_results = utf8;

SET character_set_server = utf8;

 此种方式只在当前状态下有效，当重启数据库服务后失效。所以如果想要不出现乱码只有修改my.ini文件。

3.修改my.ini文件设置编码

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8

4.重启MySQL服务

C:\mysql-5.6.24-win32\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。


C:\mysql-5.6.24-win32\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

 

5.问题

mysql> use test
Database changed
mysql> show variables like '%character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | utf8                                  |
| character_set_connection | utf8                                  |
| character_set_database   | latin1                                |
| character_set_filesystem | binary                                |
| character_set_results    | utf8                                  |
| character_set_server     | utf8                                  |
| character_set_system     | utf8                                  |
| character_sets_dir       | C:\mysql-5.6.24-win32\share\charsets\ |
+--------------------------+---------------------------------------+
8 rows in set (0.00 sec)

因为在建数据库的时候 编码设置为了 latin1

 

6.解决

mysql> alter database test default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show variables like '%character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | utf8                                  |
| character_set_connection | utf8                                  |
| character_set_database   | utf8                                  |
| character_set_filesystem | binary                                |
| character_set_results    | utf8                                  |
| character_set_server     | utf8                                  |
| character_set_system     | utf8                                  |
| character_sets_dir       | C:\mysql-5.6.24-win32\share\charsets\ |
+--------------------------+---------------------------------------+
8 rows in set (0.00 sec)

 

一、数据完整性
1、实体完整性
2、域完整性（列完整性约束）：
指数据库表的列（即字段）必须符合某种特定的数据类型或约束。

约束：
类型约束： id int
长度约束： id int(3) 插入数据 520438
非空约束： username varchar(10) NOT NULL 必须有值
唯一约束： idcardnum varchar(18) UNIQUE 可以为null，有的话必须唯一

用户名：一般网站用户名必须有，且唯一（不让他作为主键）
username varchar(100) NOT NULL UNIQUE


示例：
CREATE TABLE user(
id int PRIMARY KEY,
username varchar(20) NOT NULL UNIQUE,
idcardnum varchar(18) UNIQUE,
gender varchar(10) NOT NULL
);

专题：整数类型的主键自动增长。由数据库自己自动插入主键的值
CREATE TABLE t1(
id int PRIMARY KEY auto_increment,#主键自动增长
name varchar(100)
);
INSERT INTO t1 (id,name) VALUES(1,'A');
INSERT INTO t1 (name) VALUES('B'); (推荐)
INSERT INTO t1 VALUES (null,'C');

建议：尽量不要让数据库维护主键。（不是所有的数据库都有自动增长这个功能）
由应用来维护主键

3、参照完整性（重点：多表.外键）
3.1多表的设计
a、一对多（出现频率最高）
CREATE TABLE department(
id int PRIMARY KEY,
name varchar(100),
addr varchar(100)
);
CREATE TABLE employee(
id int PRIMARY KEY,
name varchar(100),
gender varchar(10),
salary float(8,2),
depart_id int,
CONSTRAINT depart_id FOREIGN KEY(depart_id) REFERENCES department(id)
);

外键定义语法：
CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名称(主键字段);
外键名称：随便定义，当前库中必须唯一
外键字段：当前表中那个字段是外键

b、多对多（出现频率其次）
CREATE TABLE teacher(
id int PRIMARY KEY,
name varchar(100),
salary float(8,2)
);
CREATE TABLE student(
id int PRIMARY KEY,
name varchar(100),
grade varchar(10)
);
#定义关系表
CREATE TABLE teacher_student(
t_id int,
s_id int,
PRIMARY KEY(t_id,s_id),
CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id)
);

INSERT INTO teacher VALUES(1,'WYJ',10000);
INSERT INTO teacher VALUES(2,'DH',10001);
INSERT INTO student VALUES(1,'WF','A');
INSERT INTO student VALUES(2,'QHS','A');
INSERT INTO teacher_student VALUES(1,1);
INSERT INTO teacher_student VALUES(1,2);
INSERT INTO teacher_student VALUES(2,1);
INSERT INTO teacher_student VALUES(2,2);
c、一对一（出现频率几乎没有）
CREATE TABLE person(
id int PRIMARY KEY,
name varchar(100)
);
CREATE TABLE id_card(
id int PRIMARY KEY,
num varchar(18),
CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES person(id)
);
二、多表的查询

1、连接查询
基本语法：select XXX from t1 连接类型 t2 [on 连接条件][where 筛选条件]
约定：t1在连接类型的左边，称之为左表 t2就是右表
连接类型：
cross join:交叉连接
inner join:显式内连接

left outer join:左外连接
right outer join:右外连接

1.1交叉连接(了解)：cross join
SELECT * FROM customer CROSS JOIN orders;
返回的结果是：返回的是两张表结果的笛卡尔积。即表1有5条，表2有7条，返回的5*7=35条
1.2内连接：inner join（自然连接）
a、隐式内连接：不使用on关键字（即不明确指定连接条件），使用的是where
查询有订单的客户的信息和订单信息
SELECT * FROM customer c,orders AS o WHERE c.id=o.customer_id;
b、显式内连接：使用on关键字
查询有订单的客户的信息和订单信息
SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id;
查询订单金额在200元以上的客户的信息和订单信息
SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id WHERE o.price>=200;
1.3外连接：outer join
a、左外连接:返回满足连接条件的结果，同时返回左表中剩余的其他记录
查询客户信息，同时显示他的订单
SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;
查询所有的员工，打印所在的部门名称
SELECT * FROM employee e LEFT OUTER JOIN department d ON e.depart_id=d.id;
b、右外连接:返回满足连接条件的结果，同时返回右表中剩余的其他记录
查询客户信息，同时显示他的订单
SELECT * FROM orders o RIGHT OUTER JOIN customer c ON c.id=o.customer_id;
查询所有订单，同时显示他的客户信息
SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id;
2、子查询（简单）：
子查询：嵌套查询（内部语句）。一个查询语句是另外一个查询语句的条件。子查询的语句必须放在小括号之内
select * from t1 where id=(select id from t2);

查询id为2的老师教过的学生姓名
方式一：多条语句
select s_id from teacher_student where t_id=2;
select * from student where id in (1,2);
方式二：连接查询
select s.* from teacher_student ts,student s where ts.s_id=s.id and ts.t_id=2;
方式三：子查询（一个单列结果）
select * from student where id in (select s_id from teacher_student where t_id=2);

查询名字为'陈冠希'的所有订单信息（子查询）
select * from orders where customer_id=(select id from customer where name='陈冠希');

3、联合查询：UNION
联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。

取多条语句的并集（没有重复记录）
查询客户id=1并且订单金额>=200的订单信息

SELECT * from orders where price>=200 and customer_id=1;

查询客户id=1或者订单金额>=200的订单信息
SELECT * from orders where price>=200 or customer_id=1;
使用联合查询：查询客户id=1或者订单金额>=200的订单信息
SELECT * FROM orders WHERE price>=200 UNION SELECT * FROM orders WHERE customer_id=1;

4、报表查询（数据库的内置函数）
报表查询对数据行进行分组统计
[select …] from … [where…] [ group by … [having… ]] [ order by … ]
group by:按照那些字段进行分组
having:对分组后的内容进行过滤（不能使用where）


统计一个班级共有多少学生？
mysql>SELECT count(*) FROM student;
统计数学成绩大于90的学生有多少个？
mysql>SELECT COUNT(*) FROM student where math>90;
统计总分大于250的人数有多少？
mysql>SELECT COUNT(*) FROM student where (math+chinese+english)>250;
统计一个班级数学总成绩？
mysql>SELECT SUM(math) FROM student;
统计一个班级语文、英语、数学各科的总成绩
mysql>SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
统计一个班级语文、英语、数学的成绩总和
mysql>SELECT SUM(math+chinese+english) FROM student;
统计一个班级语文成绩平均分
mysql>SELECT SUM(chinese)/COUNT(*) FROM student;

求一个班级数学平均分？
mysql>SELECT AVG(math) FROM student;
求一个班级总分平均分
msyql>SELECT AVG(math+chinese+english) FROM student;

求班级语文最高分和最低分
mysql>SELECT MAX(chinese) FROM student;
mysql>SELECT MIN(chinese) FROM student;

注意：不能使用关键字作为表名或列名，如果必须使用，请使用反引号`（ESC按键下面）引起来

对订单表中商品归类后，显示每一类商品的总价
mysql>SELECT product,sum(price) FROM orders GROUP BY product;
查询购买了几类商品，并且每类总价大于100的商品
mysql>SELECT product,sum(price) FROM orders GROUP BY product HAVING sum(price)>100;


三、数据库的备份与恢复（MySQL）
1、备份数据库（表结构和表中数据）
c:/>mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql

2、恢复数据库：数据库名必须手工创建，并选择
方式一：在MySQL内部导入数据
mysql>create database mydb1;
mysql>use mydb1;
mysql>source d:/mydb1.sql;
方式二：不进入mysql进行数据的恢复
c:/>mysql -u root -p mydb1<d:/mydb1.sql