数据库学习(一)初步使用MySQL及sql语句(DDL DML DQL)

SQL

Structured Query Language : 结构化查询语言
定义了操作所有关系型数据库的规则，可以用SQL操作MySQL，也可以操作Oracle，每一种数据库操作的方式存在不一样的地方，成为“方言”

一些操作

配置mysql
1.管理mysql服务
管理员运行cmd
net start mysql
net stop mysql
2.连接mysql
1）进入mysql -uroot-proot
退出exit
2）连接其他人的sql
mysql -h127.0.0.1 -uroot -proot
quit
3）mysql --host=127.0.0.1 --user=root --password=root

数据库-文件夹、表-文件、数据

通用语法

SQL可以以单行或多行书写，以分号结尾

MySQL的数据库的SQL语句不区分大小写，但是建议关键则使用大写

图形化工具 SQLYog

SQLYog很好用，能更直观地看到每一步操作实现了什么。

单行注释-- （两横杠一空格）
多行注释/* */

SQL分类

DDL DML DQL DCL
操作数据库和表 DDL
操作数据记录 DML 增删改表中的数据
查询数据记录 DQL 查询表中的数据
授权 DCL

DDL：操作数据库、表

1、操作数据库：CRUD

· C(create): 创建

·create database [数据库名称];
·create database if  not exists [数据库名称]/*先确认数据库是否存在*/;
·create database db character set gbk/*设置字符集*/;

· R(retrieve): 查询

 ·show databases;
 ·show create database [数据库名称] /* 可以查看数据库的字符集和创建;语句*/

· U(update): 修改

 ·alter database [数据库名称] character set utf8;

· D(delete): 删除

·drop database [数据库名称]；
drop database if exists;/*删除之前先判断是否存在*/

· 使用数据库

·select database();/* 查询当前使用的数据库*/
·use [数据库名称]

2、操作表

数据类型

• int 整数类型 double 小数类型
• date yyyy-MM-dd datetime yyyy-MM-dd HH:mm-ss
• timestamp 时间戳 yyyy-MM-dd HH:mm-ss 如果将来不给赋值则自动设置为当前系统时间
• varchar 字符串类型
  name varchar(20) 最大20个字符 zhangsan 8个字符 张三 两个字符

CRUD

C(create): 创建

 ·create table [表名]([列名1] [数据类型1]，[列名2] [数据类型2],...,[列名n] [数据类型n]);
 · 举例：
  create table student(
  	id int,
  	name varchar(32),
  	age int,
  	score double(4,1),
  	birthday date,
  	insert_time timestamp
  );
  · create table stu like student;-- 复制一份student表

R(retrieve): 查询

· use [库名] show tables;-- 查询某个数据库中所有表的名称
· desc [表名] -- 查询表结构

U(update): 修改

·  alter table [旧表名] rename to [新表名] -- 修改表名
·  alter table [表名] character [字符集名称 ] --修改字符集
·  alter table [表名] add [列名] [数据类型 ] --添加列
·  alter table [表名] change [旧列名] [新列名] [新数据类型] -- 修改列名、类型
·  alter table [表名] drop [列名] 删除列

D(delete): 删除

· drop table if exists [表名]

DML

1、增(insert)

· insert into 表名(列名1，列名2，...，列名n) values(值1，值2，…，值n);
· insert into 表名 values(值1，值2，…，值n);
· insert into 表名(name,birthday) values('张无忌', "1888-11-11");

注意

1. 列名和值要一一对应
2. 表名后面不定义列名，默认给所有列添加值，但是不能缺少任何一个列
3. 除了数字类型，其他类型需要引号，单双都可以

2、删(delete/truncate)

· delete from [表名] where 条件;--如 id=1
· truncate table [表名];-- 删除表，然后再创建一个一模一样的空表

注意

1. 如果不加条件，则会全部删除掉
2. 如果要删除所有记录，不推荐使用delete from 表名 ，因为有多少条记录就会执行多少次删除操作，费时。推荐使用truncate table 表名，效率更高

3、改(update)

· update [表名] set [列名1] = 值1,[列名2] = 值2，,...where [条件];

注意
如果不加条件，则将表中所有记录全部修改

DQL:查询表中的记录

1、语法

1.select 
	[字段列表]
from
    [表名列表]
where
	[条件列表]
group by
	[分组字段]
having
	[分组之后的条件限定]
order by
	[排序]
limit
	[分页限定]

2、基础查询

1. 多个字段查询

select 字段1,字段2...from [表名]；

如果查询所有字段，可以用*来代替字段列名

2. 去除重复

· distinct

3. 计算列
   一般可以用四则运算

4. 起别名
   as，as也可以省略

举例说明

CREATE TABLE student1(
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(5),
address VARCHAR(100),
math INT,
english INT);
INSERT INTO student1(id,NAME,age,sex,address,math,english) VALUES(1,'张三',55,'男','深圳',66,78), 
(2,'李四',40,'女','北京',80,90),(3,'王五',30,'男','杭州',40,30),
(3,'老大',20,'男','深圳',77,88),(4,'老二',30,'男','湖南',66,88),
(4,'老三',25,'女','杭州',66,96),(5,'老四',23,'男','北京',75,85);

查看全部表数据的语法为

· select * from [表名] --查询表中的记录，全部数据

但是一些公司不许使用* 号，而是要把列名一个个全写出来，这是因为* 号不方便阅读

只查询姓名和年龄

· select 
	name,	--姓名
	age 	--年龄
  from 
	student1; --学生表

不重复显示地址（如果重复的话就有重复的多个深圳等）去除重复的结果集

· select distinct address from student;

计算math和English之和

· SELECT NAME,math,english,math+english FROM student1；

如果有null参与计算，计算结果都为null
那么如何避免这种情况呢？
使用ifnull表达式，有两个参数，第二个是为null时设的值

· SELECT NAME,math,english,math+ifnull(english,0) FROM student1

math + english看着难看，可以起别名

SELECT NAME,math,english,math+english AS 总分 FROM student1;--注意到这里总分可以不加引号
SELECT NAME,math 数学,english 英语,math+english AS 总分 FROM student1;--也可以不加as

3、条件查询

1.where子句后跟条件
2.运算符

	\>、 <、 <=、 >=、 =、<>
	between and
	IN(集合)
	LIKE:模糊查询
		占位符：
			_：单个字符
			%：多个字符
	与或非

· select * from student1 where age>20;
· select * from student1 where age=40; -- 等于，用一个=
· select * from student1 where age<>20; -- 不等于，也可以用!=
· select * from student1 where age>=20 and age<=30; --不推荐使用&&
· select * from student1 between 20 and 30;-- 也可以用between and
· select * from student1 where age=22 or age=19;
· select * from student1 where age in (22,19);
· select * from student1 where english=null;--这条执行结果空白，null不能使用=判断
· select * from student1 where english is null;
· select * from student1 where name like '老%';--模糊查询名字中第一个字是老的
· select * from student1 where name like'___'; --模糊查询三个字的名字
· select * from student1 where name like'%三%';--模糊查询名字中带三的

4、排序查询

order by [排序字段1] [排序方式1]，[排序字段2] [排序方式2]...;
· SELECT * FROM student1 ORDER BY math; --默认升序asc
· select * from student1 order by math desc; --降序
· select * from student1 order by math asc,english asc--先按数学排，如果数学一样，则按照英语排名

如果有多个排序条件，则第一条件一样时才会考虑第二条件

5、聚合函数

将一列数据作为一个整体，进行纵向计算
注意：聚合函数的计算排除null值

• count
• max
• min
• sum
• avg

· select count(name) from student1;

如果有null怎么办？可以用ifnull，也可以选择不包含非空的列进行计算（主键），也可以count(*)（但是并不推荐，费时间）

6、分组查询

注意：
1.分组之后查询的字段：分组字段、聚合函数，其他的没有意义
2.where和having的区别：
①where在分组之前进行限定，不满足条件不参与分组
having在分组之后进行限定，不满足不会被查询出来；
②where后面不能跟聚合函数，having可以
例如按男女分成两组

group by [分组字段];
· select sex , avg from student1 group by sex;

· select sex,avg(math) from student where math>70 group by sex;--要求分数低于70分的人不参与分组
· select sex,avg(math) from student where math>70 group by sex having count(id)>2;--分组之后人数要大于2个人

7、分页查询

1. 语法：limit

· select * from student1 limit 0,3; --每页显示三条记录，0表示从第一个记录开始

2. 公式
   开始的索引 = (当前的页码 - 1) * 每页显示的条数
3. 分页操作是一个“方言”，limit只能在MySQL中使用，在oracle中就不能用了