mysql

一、数据库的发展史

(1)手工管理:藏书阁，图书馆。
优点:分类管理，直观性强
缺点:信息流动慢，不方便
(2)文件管理:计算机文件系统，图书管理系统
优点：分类管理，层次分明
缺点: 查找不方便
(3)数据库管理:
优点:存取数据非常方便.
缺点:有数据的安全隐患。

二、数据库概念

? 数据库(DataBase),简称DB，按照某一特定数据结构来组织，存储
? 数据的仓库。
? 数据库管理系统(DataBase Management System),简称DBMS.
? 是为了建立，操作和维护数据库
? 而设计的一个程序。介于用户和数据库之间。
? PS: 使用SQL语言，通过DBMS来管理DB。

三、关系型数据库

? (1)所谓的关系，指的就是表，或者表与表之间。关系模型，就是表的
? 行与列。比如 教师(教师编号，姓名，年龄，性别，科目)。
? (2)常用的关系型数据库：
? 大型:Oracle–甲骨文(Oracle)公司的产品
? SqlServer–Microsoft旗下的产品
? DB2–IBM旗下的产品
? 小型:Access–MicroSoft旗下的产品
? Mysql–目前是甲骨文旗下的产品

四、Mysql

1)Mysql是一个关系型数据库，最开始是瑞典的MysqlAB公司的产品，后
来被Sun公司收购，在2009年4月20号Sun公司被Oracle收购.
2)Mysql特点
–mysql是开源的，不会产生额外的费用。
–mysql支持大型数据库，比如可以管理上千万条记录的数据库。
–mysql支持多操作系统，支持多语言连接：C，C++,java,PHP…
–mysql成本比较小，相比较Oracle和DB2。
3)Mysql的安装(略)
服务端的安装：DB和DBMS的安装
客户端的安装：自带的客户端
第三方客户端
4)Mysql的连接和使用
打开mysql图形界面，输入密码即可打开mysql

1)查询数据库
	show databases;
2)先选择数据库
	use mysql;
3)书写SQL
4)查询数据库中的表
	show tables;	

五、表(Table)

表是关系型数据库的基本存储结构。
1)表是二维数据结构，有行和列
2)行(Row)是横排数据，也叫记录(Recond)
3)列(Column)是竖排数据，也叫字段(Field)
4)行与列的交叉点是 字段值
5)表与表之间也存在关系

六、数据库支持的数据类型

? MySQL支持多种类型，大致可以分为三类：
? 数值、日期/时间和字符串(字符)类型。
?

数值类型：

类型 大小 范围（有符号） 用途
TINYINT 1 字节 (-128，127) 小整数值
SMALLINT 2 字节 (-32 768，32 767) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) 极大整数值
FLOAT 4 字节 单精度
浮点数值
DOUBLE 8 字节 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型：
类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型：
类型 大小 用途
CHAR 0-255字节 定长字符串 char(10)
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

七、结构化查询语言(SQL)

? 结构化查询语言(Structured Query Language),简称SQL，是数据库
? 的标准查询语言。可以通过DBMS对数据库进行定义数据，操纵数据
? 查询数据，数据控制等
? SQL可以分为:

#### 1)数据定义语言(DDL):Data dafinitaion Language

       如创建表create
		create table tableName(
			字段1 类型,
			字段2 类型,
			字段3 类型
		)
    删除表drop
	drop table tableName

	修改表alter

	a)向表最后追加新字段
--格式：alter table tableName add (colName Type);
	b)删除表字段语句
--格式：alter table tableName drop colName;
	c)--修改表字段类型
--格式：alter table tableName modify colName newType;
	d)--修改表字段名称
--格式：alter table tableName change oldColName newColName newType;
	e)修改表名
--格式：alter table oldName rename newName;

	查看表结构
--格式：desc 表名;

清空表truncate，彻底清空，无法找回。
	只删除表中的记录，保留表的结构
--格式：truncate tableName;

?

2)数据操纵语言(DML):Data Multipart Language

	  插入数据insert
	--格式1：insert into tableName(字段1,字段2...,字段n) values (值1,值2...,值n)
	--格式2：insert into tableName values(值1,值2,..值n),(值1,值2,..值n);
注：格式2要求插入的值必须和建表时字段的顺序相对应，否则容易出现错误

  删除数据delete
	--格式：delete from tableName [where eid=2 and(or) ename='小赵'];

  修改数据update
	--格式：update tableName set 字段1=值1,字段2=值2 
	[where eid > 2];

3)事物控制语言(TCL):Transation Control Language

begin,savepoint,rollback,commit

4)数据查询语言(DQL):Data Query Language

 select:
select * from tableName;

5)数据控制语言(DCL):Data Control Language

--创建数据库并指定字符集
	create database jsd1707 default character set utf8

小知识：
修改命令提示符界面显示编码集：set names GBK;

1.复习

1.1 DB(DataBase) 和 DBMS(DataBase Management System)
1.2 表(行–记录和列–字
1.3 SQL
1)DDL：CREATE,DROP,ALTER,TRUNCATE
创建表
create table tableName(
字段1 类型,
字段2 类型，
。。
字段n 类型
)
创建数据库
create database jsd1707 default character set utf8
删除表
drop table tableName
删除数据库
drop database jsd1707
修改表–为表添加字段
alter table tableName add (字段 类型)
修改表–删除表中的某个字段
alter table tableName drop 字段名
修改表–修改字段的类型
alter table tableName modify 新字段名 类型
修改表–修改字段名
alter table tableName change 字段名 新字段名 类型
修改表–修改表名
alter table tableName rename 新表名
截取表(清空表)
truncate 表名
2)DML：INSERT,DELETE,UPDATE
插入语句
语法1：insert into tableName(字段1，字段2，…，字段n) values (值1，值2，…,值n)
语法2：insert into tableName values(值1，值2，…,值n)
删除语句
delete from tableName [where 字段=值 or 字段=值]
修改语句
update tableName set 字段=值,字段2=值2 [where 字段=值 or 字段=值]
3)TCL：事务控制语言savepoint,commit,rollback
4)DQL：select

? 5)DCL：grant(给某个用户授权),revoke(将权限从某个用户手上回收)

2.数据类型的学习：

–1：数值类型，

tinyint,smallint,mediumint,int/integer,bigint,

– float,double,decimal

1. tinyint : 1字节大小,范围-128~127
   create table temp_03 (tid tinyint);
   insert into temp_03 values(-128);
   insert into temp_03 values(127);
   –insert into temp_03 values(128);

2. smallint: 2字节大小，范围-32768~32767
   create table temp_04(tid smallint);
   insert into temp_04 values(-32768);
   insert into temp_04 values(32767);
   –insert into temp_04 values(32768):超范围

3. int/integer:常用数值类型，占 4字节，范围 -21~21左右
   drop table temp_05;
   create table temp_05 (tid int(1));
   insert into temp_05 values(-2147483648);
   insert into temp_05 values(2147483647);
   insert into temp_05 values(2147483648);

4. bigint:大整数类型， 范围与java语言的long的范围类似。

–float与double
5) float:单精度类型，占4字节。
–不规定长度时，默认进行四舍五入，保留0或1位小数，
create table salary_t (salary float);
insert into salary_t values(10000.123456);
insert into salary_t values(10000.56789);
insert into salary_t values(10000);
select * from salary_t;
–规定长度时:float(m,n):m表示数据的有效位数，n表示小数点后保留的位数。
– 整数位：最大值为m-n;
create table salary_t01 (salary float(6,2));
insert into salary_t01 values(10.5678);
insert into salary_t01 values(10000.5678);
insert into salary_t01 values(9999.5678);
insert into salary_t01 values(0.5678);
select * from salary_t01;

–float(m,0)与float(m),指的是整数位的最大位数。
create table salary_t02 (salary float(6,0));
insert into salary_t02 values(9999.5678);
insert into salary_t02 values(999999);
select * from salary_t02;

6. double:双精度类型，占8字节
   –规定长度写法： double(m,n):有效位数是m,小数点后保留n位。
   – 整数位:m-n
   create table salary_t03 (salary double(4,2));
   insert into salary_t03 values(100);
   insert into salary_t03 values(99.5678);
   select * from salary_t03;

7. decimal:与float和double的用法一样，decimal(m,n)

–2：日期类型：

date,time,datetime,timestamp,year

1. date:日期类型，默认格式:yyyy-mm-dd 范围 1000-1-1/9999-12-31
   create table date_t01 (birth date);
   insert into date_t01 values(‘2008-11-21’);
   insert into date_t01 values(‘2009/10/20’);
   insert into date_t01 values(‘89/10/20’);
   insert into date_t01 values(‘39/10/20’);
   insert into date_t01 values(‘68/10/20’);
   insert into date_t01 values(‘69/10/20’);
   insert into date_t01 values(‘70/10/20’);
   insert into date_t01 values(‘2089/10/31’);
   insert into date_t01 values(‘2000/2/29’);
   insert into date_t01 values(‘2000/2/29 12:30:29’);
   –insert into date_t01 values(‘12:30:29 2008/10/10’);
   select * from date_t01;

2. time : 默认格式：HH:mm:ss
   – 范围1：不写年月日时，-838:59:59/838:59:59
   – 范围2: 写年月日时， -23:59:59/23:59:59
   create table date_t02 (birth time);
   insert into date_t02 values(‘15:21:59’);
   insert into date_t02 values(‘838:21:59’);
   insert into date_t02 values(‘2008/12/12 15:21:59’);
   insert into date_t02 values(‘2008/12/31 23:21:59’);
   insert into date_t02 values(‘2008/12/31 -23:21:59’);
   select * from date_t02;

3. datetime:默认格式： yyyy-mm-dd hh:mm:ss
   –范围： 1000:1:1 -23:59:59/9999:12:31 23:59:59;
   create table date_t03 (birth datetime);
   insert into date_t03 values(‘1989/12/31 23:10:10’);

4. timestamp:时间戳类型，
   –范围：1970-01-01 00:00:00/2038-1-18 23:59:59
   create table date_t04 (birth timestamp);
   insert into date_t04 values(‘2037-12-31 23:59:59’);
   insert into date_t04 values(‘1969-12-31 23:59:59’);
   insert into date_t04 values(‘2037-12-31 23:59:59’);
   insert into date_t04 values(‘2038-1-18 23:59:59’);

5. year:年，默认格式:yyyy 范围 1901~2155;
   create table date_t05 (birth year);
   insert into date_t05 values(‘1901-12-20’);

–3：字符串类型：

char,varchar,tinyblob,tinytext,blob,text,
– mediumblob mediumtext longblob longtext

1. char:定长字符串类型,范围0~255个字节
   – char(10):插入数据时，最多插入10个字节，如果插入的字节数不足
   – 10字节时，也占内存10字节大小。
   – 定义数据类型长度时，最长为255
   drop table txt_01;
   create table txt_01(content char(255));

2. varchar:可变字符串类型，范围0~255。
   – 规定长度时：最大值为255个字节
   – varchar(10)：表示最多存储10个字节，如果不满足10个字节，在
   – 内存中按照实际字节数占用。

3. blob:一种字符串类型，存储数据时存储的时字符对应的字节码。字节范围：0~65535
   drop table txt_02;
   create table txt_02 (content blob);
   insert into txt_02 values(‘你好’);
   insert into txt_02 values(‘0’);
   insert into txt_02 values(‘1’);
   insert into txt_02 values(‘9’);
   insert into txt_02 values(‘A’);
   insert into txt_02 values(‘Z’);
   insert into txt_02 values(‘a’);
   insert into txt_02 values(‘z’);
   select * from txt_02;

4. text:一种字符串类型 字节范围：0~65535
   create table txt_03 (content text);
   insert into txt_03 values (‘你好吗？我很讨厌你’);
   select * from txt_03;

5. longblob:极大字符串类型，存储的是字符对应的字节码。

6. longtext:极大字符串类型，存储的是字符。
   –上述两种类型的字节范围： 0~Java语言的int的最大值的2倍+1;

–虚表dual:数据库为程序员提供的一个没有字段的表。
–作用：一般用来当作一个查询语句的基表。
–练习:计算1+2+23.
select 1+2+23 from dual;
select 2*3+2/3 from date_t01;
–mysql数据库的特点:在运算，可以不基于表操作。
select 1+2;

3.mysql常用函数的学习。

– 一、常用的字符串函数：
–1、ascii(str):返回指定字符串的第一个字符的ascii码
– 字符串为空字符串时，返回0
– 字符串为null时，返回null
select ascii(‘你好’);–返回的是第一个字符的字节码的第一个字节
select ascii(‘helloworld’);
select ascii(null);

–2、ord(str):如果字符串的第一个字符是单字符时，与函数ascii(str)一样。
– 如果不是单字节。如 UTF8的中，是三个字节。
– 返回值为： 第一个字节256+第二个字节256+第三个字节。

–3、conv(n,from_base,to_base),将from_base进制中的数n转成
– to_base进制下的表示方式
– 16进制中的字符a转成2进制来表示
select conv(‘f’,16,2);
select conv(‘6f’,16,2);

–4、bin(n),oct(n),hex(n)
– 将数值n转成对应的二进制，八进制，十六进制
–练习：查看十进制数 13的二进制，100的八进制，128的十六进制
select bin(13),oct(100),hex(128);

–5、char(n…):返回多个ascii码组成的字符串。
select char(104,101,108,108,111);

–6、concat(str1,str2,…):将多个字符串拼接成一个字符串
– 如果有一个参数为null,返回的就是null。
–练习：将hello 和kitty进行拼接
select concat(‘hello’,‘kitty’);

–练习：将hello 和null进行拼接
select concat(‘hello’,null);

7、length(str)/octet_length(str)

– 上述两个函数：返回的是默认字符集下的字符串的所有字节数。

–char_length(str)/character_length(str)

–这两个函数都是返回字符串的字符长度。
–练习：统计一下’上海自来水来自海上’的字节长度
select length(‘上海自来水来自海上’);
select octet_length(‘上海自来水来自海上’);
select character_length(‘上海自来水来自海上’);
select char_length(‘上海自来水来自海上’);

8、locate(substr,str)/position(substr in str)/instr(str,substr)
–作用：返回substr在str第一次出现的位置。如果没有，返回0;
–练习1：计算出’zuo’在’no zuo no die’第一次出现的位置。
select locate(‘zuo’,‘no zuo no die’);
–练习2：计算’bar’在’foobarfoobarfoo’中第一次出现的位置
select position(‘bar’ in ‘foobarfoobarfoo’);
–练习3：计算’baa’在’foobarfoobarfoo’中第一次出现的位置
select instr(‘foobarfoobarfoo’,‘baa’);

9、locate(substr,str,pos)
–作用：从str的下标pos开始向后查找，substr第一次出现的位置。找不到返回0
–练习1：查找’bar’在’foobarfoobarfoo’中 第二次出现的位置。
select locate(‘bar’,‘foobarfoobarfoo’,locate(‘bar’,‘foobarfoobarfoo’)+1);

10、lpad(str,len,padstr)/rpad(str,len,padstr);
– 作用：在字符串str的左边/右边添加padstr,添加后的总长度为len。
– 可称之为左补全/右补全函数
– 右对齐/左对齐函数
–练习1：在’bar’ 左边添加#，总长度为10;
–练习2: 在foo右边添加#，总长度为12;
–练习3:查询emp表中的员工姓名，右对齐，总长度为20。
select lpad(ename,20,’ ') from emp;

11、left(str,len)/right(str,len)
– 作用:返回字符串左端/右端的len个字符。
–练习1: 获取’helloworld’的左端和右端的4个字符。
select left(‘helloworld’,4), right(‘helloworld’,4);

12、substring(str,pos)/substring(str from pos)
–作用:截取str的子串，从pos下标开始截取到最后。
–练习1:截取"no zuo no die"，从下标4开始截取
–练习2:截取"no zuo no die"的最后六个字符。
select substring(‘no zuo no die’,4);
select substring(‘no zuo no die’,-6);

13、substring(str,pos,len)/substring(str from pos for len)/mid(str,pos,len)
–作用:对str进行截取操作，从pos下标开始，截取len长度个。
–小贴士：数据库的字符串的下标从1开始。
–练习1：对"i like you"进行截取，从第三个位置开始,截取4个长度
select substring(‘i like you’,3,4);
select substring(‘i like you’ from 3 for 4);
select mid(‘i like you’,3,4);
–练习2: 截取like字样。
select substring(‘i like you’,locate(‘like’,‘i like you’),length(‘like’));

14、substring_index(str,delim,count)
–作用:截取str，截取到第count个分隔符delim
–练习1：截取’www.mysql.com’，截取到第二个"."
select substring_index(‘www.mysql.com’,‘.’,2);
–练习2: 截取’www.mysql.com’,从倒数第二个’.‘开始截取。
select substring_index(‘www.mysql.com’,’.',-2);

15、ltrim(str)/rtrim(str)
–作用:删除str的左端/右端的空格
–练习1:删除’ bar ‘字符串的左右空格。
select ltrim(rtrim(’ bar ‘)), length(ltrim(rtrim(’ bar ')));

16、trim([[both | leading | trailing] [remstr] from] str);
–作用: 删除str里两端的remstr。
– 左右两端由 both|leading|trailing来控制
– 默认情况:both
–练习1:删除’ hello ‘前后的空格
select trim(’ hello '),trim(both ’ ’ from ’ hello ');

17、replace(str,from_str,to_str)
– 作用：使用to_str替换掉str中的from_str;
–练习1: 使用’#’ 替换掉’hellohello’里的"ll";
select replace(‘hellohello’,‘ll’,‘#’);
–练习2: 使用’#’ 替换掉’hellohello’里的"l";
select replace(‘hellohello’,‘l’,‘#’);

18、insert(str,pos,len,newstr)
–作用:使用newstr替换str中的一部分,这部分从pos下标开始，len个。
–练习: 使用"mysql"替换掉"i like you"中的’you’;
select insert(‘i like you’,8,3,‘mysql’);

19、lcase(str)/lower(str)/ucase(str)/upper(str)
– 作用:将字符串中的英文字母全变成大/小写。
–练习:将‘I like yourName 3’变成全大写 /全小写。
select lower(‘I like yourName 3’),upper(‘I like yourName 3’);

–二、mysql函数之数学函数。
–1、abs(n)
–作用：取参数n的绝对值
–练习1: 获取 5 和-10的绝对值
select abs(5),abs(-10);

–2、sign(n)
–作用:判断参数的符号，
– 如果是正数，返回1，
– 如果是负数，返回-1，
– 如果是0，返回0;
–练习:查看 1+2+(-23)的正负情况
select sign(1+2+(-23));

–3、mod(m,n):
–作用:返回m对n的取余的结果。同(m%n);
–练习:使用不同方式，计算10对4的取余操作。
select mod(10,4),10%4;
select mod(-10,4),10%4;
select mod(10,-4),10%4;
select mod(-10,-4),10%4;

–4、floor(n)/ceiling(n)
–floor(n) 返回小于等于n的最大整数，
–ceiling(n) 返回大于等于n的最小整数。
–练习: 分别使用两个函数对 -3.14 和 0.618 操作。
select floor(-3.14),ceiling(-3.14),floor(0.618),ceiling(0.618);

–5、round(n,d)
–作用:对n进行四舍五入，保留d位小数。
–练习1:对12345.6789分别保留到百位，整数位，小数点后2位。
select round(12345.6789,-2),round(12345.6789),round(12345.6789,2);

–6、pow(x,y)/power(x,y)
–作用:计算x的y次方
–练习 计算10的3次方
select pow(10,3);

–7、sqrt(n)
– 作用:对n进行开平方。
–练习: 计算64的平方根。
select sqrt(64);

–8、rand()
–作用:返回0~1.0的一个随机小数。
–练习: 获取一个随机数
select rand();
select rand(2);

–9、truncate(n,d)
– 作用:对数值n进行截断。
– d为正数时，保留小数点后d位。
– d为0时，保留整数位。
– d为负数时，保留小书店前d位。
–练习:对12345.12进行保留1位小数，保留整数位，保留到千位。
select truncate(12345.12,1),truncate(12345.12,0),truncate(12345.12,-3);

10、least(n…)/greatest(n…);
–作用:统计多个参数中的最小值/最大值。
–注意:参数类型可以不一致，但是必须能互相自动转换
–练习:统计1,2,‘10’,'5’中最小值和最大值。
select least(1,2,‘10’,‘5’),greatest(1,2,‘10’,‘5’);
–练习:统计三个日期中的最小和最大的
select least(‘2018-09-05’,‘2017-12-12’,‘2017-09-05’);
select greatest(‘2018-09-05’,‘2017-09-05’,‘2017-12-12’);

–三、mysql函数之日期时间函数
–1、dayofweek(date)
–作用:查看指定日期date是所在周的第几天,返回的是数字
– 1–周日…7–周六
–练习1:查看2018-9-5是所在周的第几天
select dayOfWeek(‘2018-9-5’);

–2、weekday(date)
–作用:返回指定date是所在周的周几。返回的也是数字
– 0-周1…6–周日
–练习1:查看2018-9-5是周几
select weekday(“2018-9-5”);

–3、dayname(date)
–作用：返回指定日期是周几，注意:返回的是英文描述。
–练习:查看自己的出生年月日是周几
select dayname(‘1986-11-08’);

–4、dayofmonth(date)/dayofyear(date);
–作用:返回指定日期是当月/当年的第几天
–练习:查看出生日期是当年的第几天
select dayofyear(‘1986-11-08’);

–5、monthname(date)
–作用：返回指定日期所在月的英文描述。
–练习:输出出生年月所在月的英文描述。
select monthname(‘1989-11-12’);

–6、week(date,first)
– 作用:返回指定日期是当年的第几周。first可以取值0或1.
– 0表示周日是一周的第一天，1表示周1是一周的第一天。
–练习:查看当前系统时间是今年的第几周。
select week(‘2018-9-5’,1);

–7、year(date),month(date),day(date),hour(time),minute(time),second(time)
–作用:返回指定日期或时间的时间分量。
–练习:获取当前系统时间now()的时，分，秒。
select hour(now()),minute(now()),second(now()),year(now())

–8、extract(part from date);
–作用：截取指定日期的一部分，part:year,month,day,hour,minute,second;
–练习: 截取当前系统时间的时间分量
select extract(year from now()),extract(day from now());

–9、date_format(date,format)
–作用:将指定日期date,按照自定义格式，转成字符串显示。
–格式: 年: %y
– 月: %M或%m
– 日: %d
时： %i
分： %s
秒： %H
–练习： 将当前系统时间格式化 yyyy年mm月dd日 mm分ss秒HH时。
select now();
select date_format(now(),‘%Y年%m月%d日 %i分%s秒%H’);

–10、curdate()/curtime();
–作用：获取当前系统的日期/时间
select curdate(),curtime();

–11、now()/sysdate()/current_timestamp();
–获取当前系统时间:都包含年月日时分秒。
select now(),sysdate(),current_timestamp();

–12、sec_to_time(n)
–作用:将指定数值n转成时间格式
–练习:计算一下36001转成时间格式
select sec_to_time(36001);

–13、time_to_sec(time)
–作用:将指定时间转成秒的总数。
–练习：将’13:56:56’转成秒数。
select time_to_sec(‘13:56:56’);
select time_to_sec(now());–只转时间部分。

查询功能之DQL的学习

–修改表的字段名
–格式：alter table tableName change [column] oldColName newColName newType;
–修改表的名称
–格式：alter table oldTableName rename [to] newTableName;
–小贴士：DDL语言都有关键字 table
– DML语言直接写表名，没有table关键

–======================================================
–SQL之DQL的学习

–1、基本查询语句。

–格式： select子句 from子句
– select colName[,colName…] from tableName;
–练习1:查询员工表emp中的 员工姓名，员工职位，员工入职日期和员工所在部门。
select ename,job,hiredate,deptno from emp;
–练习2:查询部门表中的所有信息
select * from dept;
select deptno,dname,loc from dept;

–2、给列起别名

–练习1:查询员工姓名和员工职位。分别起别名，姓名和职位。
select ename as “姓名” ,job as “职位” from emp;

–3、where子句

–作用:在增删改查时，起到条件限制的作用。
–练习1:查询员工表中部门号为10和20的员工的编号，姓名，职位，工资
select empno,ename,job,sal from emp where deptno=10 or deptno =20;
–练习2：查询员工表中部门号不是10和20的员工的所有信息。
select * from emp where deptno<>10 and deptno<>20;

–4.in|not in

(集合元素，使用逗号分开);

– 注意:同一个字段有多个值的情况下使用。
– in 相当于 or
– not in 相当于 and
–修改上面两个练习题
select empno,ename,job,sal from emp where deptno in(10,20);
select * from emp where deptno not in (10,20);
–all|any与集合连用:
–练习1:查询员工中工资大于集合(1500,1750,2000)中所有元素的员工信息
–练习2:查询员工中工资大于集合(1500,1750,2000)中任意一个元素的员工信息
–练习3:查询员工allen,blake,clark三个人的工资

select * from emp where sal>all(select sal from emp where ename in (‘allen’,‘blake’,‘clark’));

–5.范围查询：colName between val1 and val2;

–练习1:查询工资大于等于1500并且小于等于2500的员工的所有信息。
select * from emp where sal>=1500 and sal<=2500;
–练习2:查询工资小于2000和工资大于2500的所有员工信息。
select * from emp where sal<2000 or sal>2500;
–修改上述两个练习
select * from emp where sal between 1500 and 2500;
select * from emp where sal not between 1500 and 2500;

–6.模糊查询：like

–通配符:% 表示0或0个以上字符。
– _: 表示匹配一个字符
–格式: colName like value;
–练习1：查询员工姓名第二个字母为a或A的员工信息。
insert into emp (empno,ename,deptno)
values(9000,‘james’,40);
select ename,job,sal,comm,deptno from emp where ename like ‘_A%’;
–练习2：查询员工姓名中有a和s的员工信息。
select ename,job,sal,comm,deptno from emp
where ename like ‘%a%’ and ename like ‘%s%’;
select ename,job,sal,comm,deptno from emp
where ename like ‘%a%s%’ or ename like ‘%s%a%’;

– 练习：

–1:统计一下员工表中部门号是10的所有员工的姓名的长度。
select ename,length(ename) from emp where deptno=10;
–2:找到字符串’I want to go to school’中’to’第二次出现的位置
select locate(‘to’,‘I want to go to school’,locate(‘to’,‘I want to go to school’)+1);
–3:获取字符串’I want to go to school’中的’to go to’;
select substring(‘I want to go to school’,locate(‘to go to’,‘I want to go to school’),length(‘to go to’));
–4:对数值黄金比例(0.618)进行四舍五入保留2位小数;截取黄金比例保
– 留到小数点后一位，并对该数值进行向上取整和向下取整
select round(0.618,2),truncate(0.618,1),ceiling(0.618),floor(0.618);
–5:计算一下自己已经活了多少个月。
select period_diff(‘201809’,‘199701’);
–6:查询员工表中职位中第四个字符为a的员工信息
select * from emp where job like ‘___a%’;
–7:查询工资大于1250，有奖金的员工信息。
select * from emp where sal>1250 and comm>0;
–7.1：查询工资小于2000，并且没有奖金的人的信息;
select * from emp where sal<2000 and (comm=0 or comm is null);
–8:查询部门20和30，工资在1500到3000之间的员工信息。
select * from emp where sal between 1500 and 300 and deptno=20 or deptno=30;
select * from emp where sal between 1500 and 3000 and deptno in (20,30);
–9:查询职位不是’manager’,‘clerk’的员工。
select * from emp where job!=‘manager’ and job!=‘clerk’;
select * from emp where job not in(‘manager’,‘clerk’);
–10:查看emp表中的姓名和职位，使用’###'进行连接。
select ename,job,concat(ename,“###”,job) from emp;
select concat(ename,lpad(job,length(job)+3,“#”)) from emp;
–11:获取当前系统时间的年份，小时。
select extract(year from now()),extract(hour from now());
select year(now()),hour(now());

–=====================================================

mysql排序

–使用order by子句
– 排序规则: ASC升序，DESC降序， 默认不写时是ASC
–格式:
– order by Field1 [ASC|DESC][,Field2 [ASC|DESC]]
–练习1：查询员工表中的所有信息，按照部门号升序排序
–练习2:查询员工的编号，姓名，工资，奖金，部门号，
– 按照部门号降序排序,员工编号升序排序
select * from emp order by deptno asc;
select empno,ename,sal,comm,deptno from emp order by deptno desc,empno asc;
–练习3:查询30号部门的员工的姓名，工资，部门号。按照工资降序排序。
select ename,sal,deptno from emp where deptno=30 order by sal desc;

–去重关键字:distinct
–位置；紧跟着select关键字
–练习1:查看员工表中有哪些部门号。
select distinct job from emp;
–练习2:查看员工表中有几种奖金。
select distinct comm from emp;

–分组查询与分组函数：
– count(Field)，max(field),min(Field),sum(Field),avg(Field)
–：如果没有group by子句，还使用聚合函数，即整张表为一组。
–练习1：统计员工表中的姓名个数。
select count(ename) from emp;
–练习2：统计员工表中有多少条记录。
– 通配符:,可以替待任何字段
select count() from emp;
–练习3:查询员工表中的 员工的总工资，最大工资，最小工资，平均工资。
select sum(sal),max(sal),min(sal),avg(sal) from emp;
–练习4:查询30号部门的 总人数，工资之和，平均工资;
select count(),sum(sal),avg(sal) from emp where deptno=30;
–练习5:查询30号部门的总人数，奖金之和，平均奖金；
– 注意:需要对null值处理。
select count() “人数”,sum(comm),avg(ifnull(comm,0)) from emp where deptno=30;
–练习6:查询每个部门的总人数，总工资，平均奖金，最高工资和最低工资。
select deptno, count(),sum(sal),avg(ifnull(comm,0)),max(sal),min(sal)
from emp group by deptno;
–练习7:查询每种职位的总人数，最高工资，最低工资，以及平均工资
select job,count(),max(sal),min(sal),avg(ifnull(sal,0)) from emp
group by job;
–练习8:查询每个部门的每种职位的总人数，平均工资，工资之和。按照部门升序排序。
select deptno,job,count(*),avg(ifnull(sal,0)),sum(sal)
from emp
group by deptno,job
order by deptno;

–having子句:用于分组查询的再次过滤。
–练习1:查询部门的平均工资大于2000的部门号，平均工资，最高工资，最低工资，和工资之和。
select deptno,avg(ifnull(sal,0)),max(sal),min(sal),sum(sal)
from emp
group by deptno
having avg(ifnull(sal,0))>2000
order by deptno desc;

–============================
–DCL：数据控制语言
–练习1:创建一个用户 scott，密码为 tiger
create user scott@localhost identified by ‘tiger’;
–…

—=============================================

关联查询

–查询两张表emp和dept的信息
select * from emp,dept;

–等值查询：
– 两张表中有关系的字段做条件，进行等值连接。
–练习1:查询每个员工的信息及其部门的数据。
select * from emp,dept where emp.deptno=dept.deptno;
select * from emp;

–1:查看10号部门，职位为MANAGER的员工信息
select * from emp where deptno=10 and job=‘manager’;
–2:查看工资不小于1500，不大于3000的所有员工信息
select * from emp where sal between 1500 and 3000;
–3:查看不是10号和20号部门的所有员工信息
select * from emp where deptno not in (10,20) or deptno is null;
–4:查询集合(12,10,1,5,6)中的最大值和最小值
select greatest(12,10,1,5,6),least(12,10,1,5,6);
–5:查看10,20,30号部门中姓名中有A的员工的员工编号
select deptno,ename,empno from emp where ename like ‘%a%’
and deptno in(10,20,30);
–6:查看10,20号每个部门的最高月薪，平均奖金，部门人数
select max((sal)+ifnull(comm,0)),avg(ifnull(comm,0)),count(*) from emp
where deptno in(10,20) group by deptno;
–7:查看部门平均工资大于1000的所有部门号，平均奖金，按照部门号降序排序。
select deptno,avg(ifnull(comm,0)) from emp group by deptno having
avg(ifnull(sal,0))>1000 order by deptno desc;
–8:查询领导编号
select distinct mgr from emp where mgr is not null;

–========================================

–关联查询基础：

–1、等值连接

–练习1:查询部门表的名称及其每个部门的员工的员工编号和姓名及其职位，部门号。
– 按照部门号降序排序。
select dept.deptno, dname, empno,ename,job from emp,dept
where emp.deptno=dept.deptno
order by emp.deptno desc;

–内连接

–内连接：使用join on 进行等值连接
–格式:表A join 表B on 关联条件
–练习2:使用内连接修改练习1
select dept.deptno,dname,empno,ename,job from emp join dept
on emp.deptno=dept.deptno
order by emp.deptno desc;
–练习3:查询20和30部门的部门名称，部门地址及其员工的姓名和工资。
select d.deptno ‘dno’,d.dname,d.loc,e.ename,e.sal from emp e join dept d on
e.deptno = d.deptno
where e.deptno in (20,30)
order by dno ;

-外连接

–外连接： 驱动表，从表
–左外连接： 表A left join 表B on 关联条件
–练习1:查询员工表的所有信息及其员工所在部门的信息。
select e.,d. from emp e left join dept d on e.deptno=d.deptno;
–右外连接: 表A right join 表B on 关联条件
–练习2:使用右外连接查询部门表的所有信息及其员工信息。
select d.,e. from emp e right join dept d on e.deptno=d.deptno;

–自连接:

–练习1:查询员工的 编号，姓名及其领导编号和姓名。
select e1.empno “员工编号”,e1.ename “员工姓名”,e2.empno “领导编号”,
e2.ename “领导姓名”
from emp e1 join emp e2 on e1.mgr = e2.empno;
–练习2:查询领导的姓名及其下属的姓名。
select m.ename “领导姓名”,e.ename “员工姓名” from emp e join emp m on e.mgr = m.empno;

–高级关联查询：

–子查询,父查询

–1、子查询在where子句中

–练习1:查询工资大于员工JONES工资的员工的信息
– 第一步：select sal from emp where ename=‘jones’;
– 第二步：select * from emp where sal>(xxx);
–合并：
select * from emp where sal>(select sal from emp where ename=‘jones’);
–练习2:查询员工表中工资大于10号部门的平均工资的
– 员工的姓名，职位，工资及其部门号
–第一步：select avg(ifnull(sal,0)) from emp where deptno=10;
–第二步：
select ename,job,sal,deptno from emp where sal>
(select avg(ifnull(sal,0)) from emp where deptno=10);
–练习3:查询工资等于每个部门的平均工资的人员信息
select avg(ifnull(sal,0)) from emp group by deptno;
select * from emp where sal in (select avg(ifnull(sal,0)) from emp group by deptno);
–练习4:查询工资大于所有部门的平均工资的人员信息
select * from emp where sal >all(select avg(ifnull(sal,0)) from emp group by deptno);
–练习5:查询工资大于任意部门的平均工资的人员信息
select * from emp where sal >any(select avg(ifnull(sal,0)) from emp group by deptno);

–练习6:查询工资和奖金与员工scott相同的其他员工信息
select * from emp where sal=(select sal from emp where ename=‘scott’)
and comm = (select comm from emp where ename=‘scott’) and ename<>‘scott’;

2，–子查询在from子句中

–练习1:查询每个员工的工资，姓名和其部门的平均工资。
–分步思考：emp表中有工资和姓名的字段
– 而要显示的平均工资，这个字段不存在。
– 其他表中如果有平均工资字段就好了。这两张表就可以做关联查询。关联条件
– 应该是两张表中的部门字段。
–： 第一步：查询每个部门的平均工资
– select deptno,avg(ifnull(sal,0)) ‘avg’ from emp group by deptno;
– 第二步：做关联查询(将上一步的查询语句看成一张表)
select sal,ename,avg_sal from emp join (select deptno,avg(ifnull(sal,0)) ‘avg_sal’ from emp group by deptno) t
on emp.deptno = t.deptno order by t.deptno;
–练习2:查询大于本部门平均工资的员工的信息。
select sal,ename,avg_sal from emp join (select deptno,avg(ifnull(sal,0)) ‘avg_sal’ from emp group by deptno) t
on emp.deptno = t.deptno and emp.sal>t.avg_sal order by t.deptno;

3,–子查询在having子句中

–练习1:查询部门平均工资大于30部门平均工资的部门平均工资，工资之和，
– 最大工资，最小工资，总人数。
select deptno,avg(ifnull(sal,0)),sum(sal),max(sal),min(sal),count(*)
from emp group by deptno having avg(ifnull(sal,0))>
(select avg(ifnull(sal,0)) from emp where deptno=30);

4, --子查询在select子句中。

–练习1:查询每个员工的姓名，工资，及其部门的平均工资,工资之和。
select ename,sal,
(select avg(ifnull(sal,0)) from emp a where a.deptno=b.deptno) avg_sal ,
(select sum(sal) from emp c where c.deptno=b.deptno ) sum_sal
from emp b order by b.deptno;

–约束:

–主键约束

–练习1:创建表t_01 字段tid int 设置为主键约束，tname varchar(20).
create table t_01(
tid int primary key,
tname varchar(20)
);
–插入数据： 1001，‘zhangsan’
– 1002， ‘李四’
insert into t_01 values(1001,‘zhangsan’);
insert into t_01 values(1002,‘lisi’);
insert into t_01 values(null,‘wangwu’);

–非空约束：

create table t_02(
tid int primary key,
tname varchar(20) not null
);
insert into t_02 values(1001,‘zhangsan’);
insert into t_02 values(1002,null);

–唯一性约束

create table t_03(
tid int primary key,
tname varchar(20) not null,
idcard varchar(18) unique
);
insert into t_03 values(1001,‘zhangsan’,null);
insert into t_03 values(1002,‘zhangsan’,‘123456789’);
insert into t_03 values(1003,‘zhangsan’,‘223456789’);

–检查性约束

create table t_04(
tid int primary key,
tname varchar(20) not null,
idcard varchar(18) unique,
gender enum(‘f’,‘m’)
);
insert into t_04 values (1001,‘zhangsan’,‘123456789’,‘f’);
insert into t_04 values (1002,‘zhangsan’,‘223456789’,‘m’);
insert into t_04 values (1003,‘zhangsan’,‘323456789’,‘a’);

–外键约束：

create table t_05(
empno int primary key,
ename varchar(20) not null,
idcard varchar(18) unique,
gender enum(‘f’,‘m’),
mgr int ,
foreign key(mgr) references t_05(empno)
);

insert into t_05 values (1001,‘zs’,‘123456789’,‘m’,null);
insert into t_05 values (1002,‘ls’,‘223456789’,‘m’,1001);

–序列：

create table t_06(
tid int primary key auto_increment,
tname varchar(20) not null
);
insert into t_06 values(1001,‘zs’);
insert into t_06 values(null,‘ls’);
insert into t_06 (tname) values(‘ww’);
insert into t_06 values(last_insert_id()+1,‘zl’);

分页查询：

– 需求:当每次查询的记录数比较大，通常一页显示不下，此时我们可以进行分页查询。
– 关键字limit
– 用法: limit begin,size;
– begin：记录的开始行数. 偏移量
– size：每页的最大记录数。
– 注意:limit后可以是一个参数，
– 一个参数时，是指每次最大的查询记录数，limit size;
– 默认记录数从0开始，而不是1.
–练习1:查询员工表，查询第一页的数据，每页10条。
select * from emp limit 0,10;
–练习2:查询员工表，查询第二页的数据，每页5条。
select * from emp limit 5,5;
–练习3:按照部门号升序排序，查询第二页的数据，每页5条
select * from emp order by deptno limit 5,5;
–练习4:按照员工编号降序排序，查询第二页的数据，每页5条。
select * from emp order by empno desc limit 5,5;
–练习5:使用子查询，先排序，再查询每页的数据。
– 按照员工工资降序排序，查询第二页的数据，每页6条。
select e.* from (select * from emp order by sal desc) e limit 6,6;
– 执行效率与优化
— 创建了一张表temp_001,插入了10w条的数据。
– 需求: 每页100条记录，查询第1页的数据
– 查询第11页的数据
– 查询第101页的数据
– 查询第1001页的数据
–select * from temp_001 limit 100;
–select * from temp_001 limit 1000,100;
–select * from temp_001 limit 10000,100;
–select * from temp_001 limit 143000,100;
– 当我们在做分页查询的时候，查询的效率与偏移量有关系，偏移量越大，
– 效率越低，消耗的时间就会越多。 如上述分页查询
– 如何优化分页查询
– (1)我们可以使用子查询先确定作为tid偏移量的位置。
– select tid from temp_001 limit 100000,1
– (2)再进行分页查询
– select * from where tid>=(查出id的值) limit 100;
–练习:
select * from temp_001 where tid>=
(select tid from temp_001 limit 144000,1) limit 100;

– 还有一种优化：首先确定tid必须是连续的数据。
– 使用 between and
select * from temp_001
where
tid between 145000 and 145200 limit 100;

–视图

–视图:VIEW，表的一部分投影。也是数据库里一个对象。(表的虚拟部分)
– 视图的作用: 因为视图中含有某表中的部分数据，不在视图中的字段有隐藏效果。
– 相对来说，安全。
–如何操作视图呢？视图可以像表一样，进行增删改查。
–如何创建视图呢？
–格式： create view ViewName as subQuery
–练习1: 将员工表中的10部门的数据封装到一个视图view_emp_deptno_10
create view view_emp_deptno_10 as select * from emp where deptno=10;

– 通过视图进行DML操作，一定会对表有影响。
– 通过表进行DML操作，可能会对视图有影响。
–练习2:通过视图，插入数据 empno:9001,ename:‘zs’,部门号:10
– 插入数据 empno:9002,ename:‘ls’,部门号:20
– 查询表emp,查询视图view_emp_deptno_10
insert into view_emp_deptno_10 values (9001,‘zs’,null,null,null,null,null,10);
insert into view_emp_deptno_10 (empno,ename,deptno)values (9002,‘ls’,20);
select * from view_emp_deptno_10;

–with check option:对视图的一种检查约束选项，
– 如果在创建视图有此选项时，表示，只能对视图DML操作可见数据。
– 反之，对视图不可见的数据，是不可以通过视图进行DML操作的。
–练习2:创建一个视图view_emp_deptno_20，
– 投影20号部门的 员工编号，员工姓名，职位，工资，部门号。
–练习3:通过视图view_emp_deptno_20插入数据
– empno:9003,ename:‘ww’,job:manager,sal:5000,20
– empno:9004,ename:‘zl’,job:manager,comm:500,20
– empno:9005,ename:‘qq’,job:manager,sal:500,10
create view view_emp_deptno_20 as select empno,ename,job,sal,deptno
from emp where deptno = 20 with check option;
select * from view_emp_deptno_20;
insert into view_emp_deptno_20 values(9003,‘ww’,‘manager’,5000.0,20);
insert into view_emp_deptno_20 (empno,ename,job,comm,deptno)values(9004,‘zl’,‘manager’,500.0,20);
insert into view_emp_deptno_20 values(9005,‘qq’,‘manager’,5000.0,10);
–视图的分类：
– 简单视图:subQuery是一个表中的普通查询语句
– 复杂视图:subQuery是一个可以带有分组函数的，或者关联查询的语句。
– 复杂视图不能对视图进行DML操作，只能进行DQL操作
–练习:创建一个复杂视图view_emp_100，是每个部门的平均工资，最高工资，最低工资，及其部门号。
create view view_emp_100 as select avg(ifnull(sal,0)) avg_sal,max(sal) max_sal,
min(sal) min_sal,deptno from emp group by deptno;

– 索引

– 索引(Index):目的是为了提高查询速度。相当于一本书的目录。
– 索引也是数据库的对象，占空间。每张表每个字段都可以设置相应的
– 索引
– 优点: 提高查询速度。
– 缺点: 占空间， 每次进行DML操作时，数据库都要(自动)重新维护索引，
– 降低效率.
– 总体来说:表中有索引可以提高效率，但不是索引越多越好。当表中的数据量
– 比较小时，无需索引(因为直接查询可以比使用索引更快)，当某个字段的值
– 比较少时，也不需要索引，如性别字段只有’f’,‘m’.只有当数据量比较大，
– 和字段值多时，可以使用索引。
–练习1:给emp表中的empno创建索引。
create index index_emp_empno on emp(empno);
–练习2:查询empno为9003的数据。
select * from emp where empno=9003;

–删除索引，删除视图都是用drop
–drop index indexName
–drop view viewName

–可以自学： PLSQL(数据库编程语言)–触发器，存储过程