mysql基础知识

1. 数据库入门

1.1引入

数据保存在内存：
	优点： 存取速度快
	缺点： 数据不能永远保存！！！
数据保存在文件：
	优点： 数据永远保存！！！
	缺点：
		1）速度比内存操作慢，频繁的IO操作。
		2）查询数据不方便
数据保存在软件：
		1）数据永远保存！！！
		2）速度比较快
		3）查询和管理数据方便				
数据库软件可以实现以上的功能！！！

1.2市面常见数据库软件

SQL Server： 微软的产品。与net平台兼容比较好！！！收费的。
	Oracle：甲骨文公司的产品。 与java平台兼容性最好！！！收费的。
		收购Sun公司： java
		收购mysql: mysql 数据库软件
			中大型企业（中大型应用）
	mysql： 开源产品。免费的。与java平台兼容性最好！！
		中小企业（中小应用）

		前面： 学习mysql
		后面： 学习oracle

	mysql学习大纲：
		1）学习如何安装和基本使用
		2）如何管理数据库
		3）如何管理表
		4）如何管理数据
		5）如何查询数据

2.MySQL数据库

2.1安装和基本使用

1）下载安装包。mysql-5.5.40-win32.ms
2）如果之前安装过mysql，要先安装文档步骤卸载mysql
3）安装安装文档安装mysql数据库软件。
4）打开cmd命令行：
	输入： mysql -u root -p 回车
	输入：root

2.2 mysql的结构

先有数据库 -> 再有表  ->再有数据
通过sql语句对mysql数据库进行管理。

3. 管理数据库

3.1 查看所有数据库

mysql> show databases;   --分号结束。sql语句就会发送给mysql服务器端执行
+--------------------+
| Database           |
+--------------------+
| information_schema |      --mysql元数据数据库。
| mysql              |    --mysql配置数据库。其中user表用于管理mysql用户和密码、权限信息。
| performance_schema |     --mysql性能监控信息数据库。
| test               |     --测试数据库。
+--------------------+
4 rows in set (0.00 sec)
mysql>

3.2 创建数据库

1）创建数据库，并制定默认字符集

mysql> create database day16
    -> default character set utf8;
Query OK, 1 row affected (0.00 sec)
-- 查看数据库的状态（默认字符串）：
showcreate database day15;               

2）创建数据库，同时指定默认字符集，以及字符集的默认校验规则。

mysql> create database day15
    -> default character set utf8
    -> collate utf8_general_ci;     --指定默认的校验规则
Query OK, 1 row affected (0.00 sec)

校验规则：

	一个字符集可以同时存在多种校验规则。		
	a）使用A校验规则：取字符的ascii码值，进行比较大小
		a  <  b
		97  98
	b）使用B校验规则：取字符的ascii码值的负数，进行比较大小
		a  >  b
		-97  -98			
	查看常用的字符串集的校验规则：
		show character set;

3.3 删除数据库

mysql> drop database day15;
Query OK, 0 rows affected (0.00 sec)

3.4 修改数据库

​ 修改数据库默认字符集

mysql> alter database day15 default character set gbk;
Query OK, 1 row affected (0.00 sec)

4 管理表

4.1 查询所有表

mysql> use day15;   --选择数据库
Database changed
mysql> show tables;  --查看数据库中的所有表

4.2 创建表

mysql> create table student(
    -> id int,    -字段：字段名称  字段类型
    -> name varchar(20),
    -> age int
    -> );
查看一个表结构：
	show create table student;   以sql格式返回
	desc student;  以表格格式返回 

4.3 删除表

mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

4.4 修改表

1）添加字段

mysql> alter table student add column gender varchar(2);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>alter table student add a int,add b int;  -- 多个操作

2）修改字段类型

mysql> alter table student modify column remark varchar(100);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

3）修改字段名称

mysql> alter table student change column remark remark2 varchar(100)
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

4）删除字段

mysql> alter table student drop column a,drop column b;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

5)修改表名

mysql> alter table student rename to student2;
Query OK, 0 rows affected (0.01 sec)

5 管理数据

-- *************** 一、 数据增删修改操作 ********************---------
-- 1.1 查看表的数据
SELECT * FROM student;

-- 1.2 插入数据
-- 插入所有列（所有列的值都要按顺序依次插入）
INSERT INTO student VALUES(1,'eric',20,'广州人','男');
INSERT INTO student VALUES(2,'rose',25,'佛山人','女');

-- 插入部分列(列的数据和值的顺序和数量一定保持一致！)
INSERT INTO student(id,NAME,age,remark2) VALUES(3,'jacky',27,'佛山人');

-- 1.3 修改数据
-- 修改所有（用的很少）
UPDATE student SET gender='男';
-- 修改某个某些条件的数据（用的较多）
UPDATE student SET gender='女' WHERE id=2;

-- 修改多个列(SET 列名=值,列名=值...)
UPDATE student SET age=28,remark2='韶关人' WHERE id=2;

-- 1.4 删除数据
-- 删除表的所有数据（用的较少）
-- 注意： 
   -- 1）只能删除表中的数据，不能影响表中的自增长约束
   -- 2）使用delete删除的数据，通过事务可以回滚。
   -- 3）后面带条件
DELETE FROM student;

-- 删除部分
DELETE FROM student WHERE id=3;

-- 删除所有数据
-- 注意：
   -- 1）既能删除表的数据，也能够把表的子增长约束置为0.
   -- 2）使用truncate删除的数据，不能回滚！
   -- 3）后面不能带条件
TRUNCATE TABLE student;

6 查询数据（重点的重点）

6.1 查询所有列

-- 2.1 查询所有列
SELECT * FROM student;

6.2 查询指定列

-- 2.2 查询指定列(多个列之间以逗号分隔)
SELECT NAME,gender FROM student;

6.3 查询时指定别名

-- 2.3 查询时给列指定别名(AS)
SELECT NAME AS '姓名',gender AS '性别' FROM student;
-- AS可以省略
SELECT NAME '姓名',gender '性别' FROM student;

6.4 合并列查询

-- 2.5 合并列查询
-- 添加servet,jsp成绩列
ALTER TABLE student ADD servlet INT,ADD jsp INT;
SELECT * FROM student;
UPDATE student SET servlet=86,jsp=75 WHERE id=1;
UPDATE student SET servlet=90,jsp=65 WHERE id=2;
UPDATE student SET servlet=78,jsp=50 WHERE id=3;
UPDATE student SET servlet=85,jsp=80 WHERE id=4;

-- 需求： 查询每个学生的servlet和jsp的总成绩
-- 注意： 必须是数值类型
SELECT NAME '姓名',(servlet+jsp) '总成绩'  FROM student;

-- 注意，和数值以外合并没有意义
SELECT NAME '姓名',(gender+jsp)  FROM student;

6.5 查询时添加常量列

-- 2.4 查询时添加常量列
-- 需要： 在查询学生表时都带上一个班级列，内容为"java就业班"
SELECT NAME '姓名',gender '性别',age '年龄','java就业班' AS '班级' FROM student;

6.6 查询去除重复数据

-- 2.6 查询去除重复数据
-- 需求： 统计学生都来自于哪里
SELECT DISTINCT remark2 FROM student;
-- 这种语法也可以
SELECT DISTINCT(remark2) FROM student;

6.7 条件查询

-- 2.7 条件查询(where)
SELECT * FROM student;
-- 1) 逻辑条件： and   or
-- 需求: 查询id值为3，且性别为男的学生(交集：两个条件同时满足)
SELECT * FROM student WHERE id=3 AND gender='男';
-- 需求：查询id为3，或性别为男的学生（并集： 两个条件其中一个满足）
SELECT * FROM student WHERE id=3 OR gender='男';

-- 2）比较条件： >  <  >=  <=  = between and
-- 需求： 查询servlet分数大于80分的学生
SELECT * FROM student WHERE servlet>80;
-- 需求：查询jsp分数小于或等于80分的学生
SELECT * FROM student WHERE jsp<=80;
SELECT * FROM student WHERE jsp<80 OR jsp=80;
-- 需求： 查询servlet成绩大于等于80，且小于等于88的学生
SELECT * FROM student WHERE servlet>=80 AND servlet<=88;
-- 等价于上面
SELECT * FROM student WHERE servlet BETWEEN 80 AND 88; -- (包前包后)

-- 3）判空条件:  =''  is null  <>''  is not null
UPDATE student SET remark2="" WHERE id=1;
UPDATE student SET remark2=NULL WHERE id=4;
-- 需求： 查询remark字段为空的学生（包含null，也包含字符串）
-- null 和 空字符串的区别：
-- null： 没有数据。 判断null： is null,判断不为null： is not null
-- 空字符： 有数据，数据就是空字符串。判断空字符： =''； 判断不为空字符串： <>''
SELECT * FROM student WHERE remark2 IS NULL;
SELECT * FROM student WHERE remark2='';
SELECT * FROM student WHERE remark2 IS NULL OR remark2='';
-- 需求：查询remark字段不为空的学生
SELECT * FROM student WHERE remark2 IS NOT NULL AND remark2<>'';

-- 4) 模糊条件： like
-- like后面跟上符合
-- %: 表示任意多个字符（0-多个）
-- _: 表示一个字符
-- 需求： 查询姓张的学生
SELECT * FROM student WHERE NAME LIKE '张%';
-- 需求： 查询姓名中包含'张'字的学生
SELECT * FROM student WHERE NAME LIKE '%张%';
-- 需求： 查询姓张，且姓名只有两个字的学生
SELECT * FROM student WHERE NAME LIKE '张__';

6.8 聚合查询

-- 2.8 聚合查询
SELECT * FROM student;
DELETE FROM student WHERE id=5;
-- 需求： 查询所有学生servlet的总分 (SUM: 求和函数)
SELECT SUM(servlet) FROM student;

-- 需求： 查询所有学生servlet的平均分（AVG; 平均函数）
SELECT AVG(servlet) FROM student;

-- 需求：查询最高的servlet分数（MAX：最大值函数）
SELECT MAX(servlet) FROM student;

-- 需求：查询最低的servlet分数（MIN：最小值函数）
SELECT MIN(servlet) FROM student;

-- 需求： 一共几个学生（COUNT： 统计数量函数）
SELECT COUNT(*) FROM student;
SELECT COUNT(id) FROM student; -- 效率会比count(*)效率稍高

-- 注意： 聚合函数，会排除null值的数据
SELECT COUNT(remark2) FROM student;

6.9 分组查询

-- 2.11 分组查询
-- 需求： 查询男女的数量各自多少
-- 预期结果： 
-- 男   3
-- 女   2
-- 1) 对性别进行分组（group by） 2）在分组基础上进行聚合函数总计

SELECT gender,COUNT(*) '人数' FROM student GROUP BY gender;

6.12 分组筛选

-- 2.12 分组筛选(group by + having(条件))
-- 需求： 查询人数大于2的性别
-- 1)分组   2）统计 3)条件
SELECT gender,COUNT(*) '人数' FROM student GROUP BY gender HAVING COUNT(*)>2;

7 总结

1）管理数据库：
	增： create database 数据库名 default character set 字符集;
	修： alter database 数据库名 default character set 新的字符集;
	删： drop database 数据库名;
	查： show databases;
2）管理表：
	增： create table 表名(字段名1 字段类型,字段名2 字段类型......);
	修： 
		增加字段： alter table 表名 add column 字段名 字段类型;
		修改字段类型： alter table 表名 modify column 字段名 新的字段类型;
		修改字段名： alter table 表名 change column 旧字段名 新的字段名 字段类型;
		修改表名： alter table 表名 rename to 新表名;
	删： drop table 表名;
	查： show tables;         desc 表名;
3)管理数据：
	增： insert into 表名(字段名1,字段名2....) values(值1，值2.....);
	修： update 表名 set 字段名1=值1,字段名2=值2...... where 条件;
	删： delete from 表名 where 条件;
		truncate table 表名;
	查： (12中查询)
		a）所有字段：select * from 表名;
		b）指定字段： select 字段名1,字段名2 from 表名;
		c）指定别名： select 字段名1 as 别名1,字段名2 as 别名2  from 表名;
		d）添加常量列： select 常量值 as 别名 from 表名;
		e）合并列： select (字段名1+字段名2+....) as 别名 from 表名;
		f）去除重复： select distinct 字段名 from 表名;
		g）条件查询：
			逻辑条件： where 条件1 and/or 条件2;
			比较条件： where 字段名  >/>=/</<=/=/<> 值
				where 字段名 between 值1 and 值2;
			判空条件： 
				null;   where 字段名 is null/is not null;
				空字符串：  where 字段名=''/<>''
			模糊条件： where 字段名　like 值
				%： 代表任意个字符
				_: 代表一个字符
		h）聚合查询： 
			max(): 最大值
			min（）：最小值
			avg（）:平均值
			count(): 统计数量
		i）分页查询：
			limit 起始行，查询行数
		j）排序：
			order by 字段名 asc/desc
			asc: 升序		
			desc: 降序
		h）分组查询：
			group by 字段名 
		k）分组后筛选：
			group by 字段名 having 条件;
 		
sql语句分类：
	1）数据定义语句（DDL）：
		create/alter/drop
	2）数据操作语句（DML）
		insert/update/delete/truncate
	3）数据查询语句（DQL）
		select/show