MySQL

1.MySQL 基本操作

1.1 数据库的基本操作

1.1.1. 创建数据库

create database 数据库名 character set utf-8;

例如：
create database mydb character set utf-8;

1.1.2. 删除数据库

drop database 数据库名;

例如：
drop database mydb;

1.1.3. 修改数据库

//修改数据库字符集
alter database 数据库名 character set utf-8;

例如：
alter database mydb character set utf-8;

1.2 数据表的基本操作

1.2.1. 创建数据表

create table 表名(
       字段名1 数据类型,
       字段名2 数据类型,
       字段名3 数据类型,
       ......
);

例如：
//创建学生表
create table Student(
       id int(11),//学号
       stuname varchar(255),//姓名
       sex char(20),//性别
       age int(11),//年龄
       address varchar(255),//住址
);

1.2.2. 查看表结构

1.用describe语句查看表定义

describe 表名;
或
desc 表名;

例如：
//查看Student表
describe Student;
或
desc Student;

2.用"show create table"语句查看表详细定义

show create table 表名;

例如：
//查看Student表
show create table Student;

1.2.3. 修改数据表

1.修改表名

alter table 旧表名 rename 新表名;

例如：
//把Student表修改为Stu表
alter table Student rename Stu;

2.修改字段

//修改字段的数据类型
alter table 表名 modify 字段名 数据类型;

例如：
//把Student表中的sex字段的数据类型修改成varchar(255)
alter table Student modify sex varchar(255);

//修改字段名
alter table 表名 change 旧字段名 新字段名 旧数据类型/新数据类型;

例如：
//把Student表中的stuname修改成name
alter table Student change stuname name varchar(255);

3.添加字段

//在表的最后一个位置添加字段
alter table 表名 add 字段名 数据类型;

例如：
在Student表的最后一个位置添加字段为birthday，数据类型为datetime
alter table Student add birthday datetime;//出生日期

//在表的第一个位置添加字段
alter table 表名 add 字段名 数据类型 first;

例如：
在Student表的第一个位置添加字段为birthday，数据类型为datetime
alter table Student add birthday datetime;//出生日期

//在表的指定字段之后添加字段
alter table 表名 add 字段名 数据类型 after 字段名;

例如：
在Student表的age字段后添加字段为birthday，数据类型为datetime
alter table Student add birthday datetime after age;//出生日期

4.删除字段

alter table 表名 drop 字段名;

例如：
在Student表中删除sex字段
alter table Student drop sex;

5.删除数据表

drop table 表名;

例如：
删除Student表
drop table Student;

2.SQL语句与MySQL数据操作

2.1 MySQL数据类型

整数类型(int)，浮点类型(floaat和 double)，字符串类型(char和varchar)

2.1.1.日期时间类型

日期时间类型	日期时间格式
datetime	yyyy-mm-dd hh-mm-ss
timestamp	yyyy-mm-dd hh-mm-ss
date	yyyy-mm-dd
time	hh-mm-ss
year	yyyy

2.2 MySQL运算符

2.2.1.算术运算符

运算符	作用	实例
+	加法运算	5+3=8
-	减法运算	5-3=2
*	乘法运算	5*3=15
/(div)	除法运算，返回商	5/3=1.667
%(mod)	求余运算，返回余数	5/3=2

select 5 + 3'加法运算',
       5 - 3'减法运算',
       5 * 3'乘法操作',
       5 / 3'除法操作(/)',
       5 div 3'除法操作(div)',
       5 % 3'求余操作(%)',
       5 mod 3'求余操作(mod)';

2.2.2.比较运算符

运算符	作用	实例
=	等于	a=5
< = >	安全等于	null< = >null
<>(! =)	不等于	a <> 5 或 a ! = 5
< =	小于等于	a < =5
> =	大于等于	a > = 5
>	大于	a > 5
is null	判断一个值是否为null	a is null
is not null	判断一个值是否不为null	a is not null
least	在有两个或多个参数时，返回最小值	least (3,5,9,2,6) 结果为：2
greatest	在有两个或多个参数时，返回最大值	greatest (3,5,9,2,6) 结果为：9
between and	判断一个值是否落在两个值之间	a between 4 and 7
isnull	与is null作用相同	isnull (a)
in	判断一个值是否落在两个值之间	a in (3,5,9,2,6)
not in	判断一个值不是in列表中的任何一个值	a not in (3,5,9,2,6)
like	通配符匹配	a like ’ %数据库% ’
regexp	正则表达式匹配	regexp ’ ^m * d $ ’ 通配 mad,mood,mind等

2.2.3.逻辑运算符

符号	作用
&& 或 and	与
or	或
! 或 not	非
xor	异或

2.3 MySQL常用函数

2.3.1数值函数

1.abs(x)函数：返回x的绝对值。
2.ceil(x)函数：返回大于x的最大整数。
3.floor(x)函数：返回小于x的最大整数。
4.rand( )函数：返回0到1内的随机数。
5.rand(x)函数：指定整数x，则用作种子值，产生一个可重复的数字序列。
6.round(x,y)函数：返回参数x的四舍五入的有y位小数的值。
7.truncate(x,y)函数：返回数字x截断为y位小数的结果。

2.3.2.日期时间函数

1.获取日期的函数：curdate()
2.获取时间的函数：curtime()
3.获取日期和时间的函数：now()
4.操作指定日期：

与日期有关的操作函数

日期函数	说明
month(d)	返回日期d中的月份值，范围为1~12
monthname(d）	返回日期d中的月份名称，如January等
dayname(d)	返回日期d是星期几，如Monday等
dayofeeek(d)	返回日期d是星期几。1为星期日，2为星期一等
weedday(d)	返回日期d是星期几。0为星期一，1为星期二等
weed(d)	返回日期d是本年的第几个星期，范围为0~53
dayofyear(d)	返回日期d是本年的第几天
dayofmonth(d)	返回日期d是本月的第几天
year(d)	返回日期d中的年份值
quarter(d)	返回日期d是第几季度
to_days(d)	计算日期d与0000年1月1日之间间隔的天数
date_format(d,f)	按照表达式 f 的要求显示日期d

日期和时间的常用格式

格式	说明
%w	星期几(Monday …)
%w	以数字形式表示星期几(0：Sunday，6：Saturday)
%D	以英文后缀表示月中的几号
%d	以2位数字表示月中的几号(01，… ，31)
%j	以3位数字表示年中的天数(001，…，366)
%Y	年，数字，4位
%y	年，数字，2位
%a	星期几的英文缩写(Sun)
%e	月份中的天数，数字，(0，…，31)
%m	以2位数字表示月份(01，…，12)
%c	以数字表示月份(1，…，12)
%b	月份的英文缩写（Jan)
%U	星期数，Sunday是星期的第一天(0，…，52)
%u	星期数，Monday是星期的第一天(0，…，52)
%%	标识符%

5.日期间隔、时间间隔函数

参数type值

type	说明	expr表达式
year	年	YY
month	月	MM
day	日	DD
hour	时	hh
minute	分	mm
second	秒	ss
year_month	年和月	YY和MM之间用任意符号隔开
day_hour	日和小时	DD和hh之间用任意符号隔开
day_minute	日和分钟	DD和mm之间用任意符号隔开
day_second	日和秒	DD和ss之间用任意符号隔开
hour_minute	时和分	hh和mm之间用任意符号隔开
hour_second	时和秒	hh和ss之间用任意符号隔开
minute_second	分和秒	mm和ss之间用任意符号隔开

2.3.3.其他常用函数

1.系统信息函数

系统信息函数	说明
version()	返回数据库版本号
connection_id()	返回服务器的连接数
database()	返回当前数据库名
uesr()	返回当前用户
last_insert_id()	返回最近生成的auto_increment值

2.加密函数

(1)password(str)函数：可以对字符串str进行加密，一般情况下用来给用户的密码加密。

(2)md5(str)函数：可以对字符串str进行加密，主要是对普通的数据进行加密。

(3)encode(str,pswd_str)函数：可以使用字符串pswd_str来加密字符串str；加密结果是一个二进制数，必须使用blob类型的字段来保存它。

3.其他常用函数

函数	说明
format(x,n)	将数字x进行格式化，保留n位小数
inet_aton(ip)	将ip地址转换为数字表示
get_lock(name,time)	创建一个持续时间为time的名为name的锁
if_free_lock(name)	判断是否使用名name的锁
benchmark(count,expr)	将表达式重复执行count次
convert(s using cs)	将字符串s的字符集变成cs
convert(x,type)	将x变成type类型

2.4 常用SQL语句

2.4.1.插入数据

1.插入单行记录

insert into 表名(字段名1,字段名2,···) 
values(值1,值2,···);

例如：
insert into Student(id,stuname,sex,age,address) 
values(1,'张三','男',18,'咸阳');
insert into Student(id,stuname,sex,age,address) 
values(2,'李花','女',16,'西安');

2.插入多行记录

insert into 表名(字段名1,字段名2,···) 
values(值11,值21,···),
      (值12,值22,···),
      ······
      (值1n,值2n,···);

例如：
insert into Student(id,stuname,sex,age,address) 
values(3,'王五','男',18,'咸阳'),
      (4,'刘花','女',16,'西安');

3.插入查询结果

insert into 表名1(字段名11,字段名12,······) 
   select 字段名21,字段名22,······
      from 表名2 where 条件表达式

2.4.2.更新数据

update 表名
   set 字段名1 = 值1,字段名2 = 值2,···,字段名n = 值n
       where 条件表达式;

例如：
//将Student表中王五的address修改为长沙
update Student set address = 长沙 where name = 王五;

2.4.3.删除数据

delete from 表名 where 条件表达式;


例如：
//将Student表中的所有女生删除
delete from Student where sex = '女';

2.5 数据表记录查询

2.5.1.简单数据查询

1.选择所有列

select * from 表名;

例如：
//查询Student表中的所有数据
select * from Student;

2.查询指定列数据

select 字段1(,字段2,···,字段n) from 表名;

例如：
//查询Student表中的stuname列的数据
select stuname from Student;

3.定义列别名

select 字段1 = 值1(,字段2 = 值2,···,字段n = 值n) from 表名;

例如：
//查询Student表中的stuname列为张三的数据
select stuname = 张三 from Student;

2.5.2.where条件查询

where设置查询条件时，where子句可以使用算数运算符(如+、-、*、/、%)、比较运算符(如<>、>、<、between and 和 like)和逻辑运算符(如and、not、or)等运算符。

2.5.3.排序

//asc 升序；desc 降序
select * form 表名 where 判断语句 
   order by 字段名1 asc/desc(,字段名2 asc/desc,······);

2.5.4.聚合函数

1.求和函数：sum()
2.求平均值函数：avg()
3.求最大值函数：max()
4.求最小值函数：min()
5.计数函数：count()

2.5.5.分组

select * from 表名 group by 字段名 [having 条件表达式] [with rollup];

例如：
//把Student表中的数据按id分组
select * from Student group by id;

3.MySQL高级操作

3.1 索引的基本操作

3.1.1.创建普通索引

1.创建表的同时创建索引

create table 表名(
       属性名1 数据类型,
       属性名2 数据类型,
       属性名3 数据类型,
       ......
       index/key 索引名 (属性名1 [长度] [asc/desc])
);

2.在已有表上创建索引

create index 索引名 on 表名 (属性名1 [长度] [asc/desc]);

3.已修改表结构的方式来添加索引

alter index 表名 add index/key 索引名 (属性名1 [长度] [asc/desc]);

3.1.2.创建多列索引

1.创建表时创建多列索引

create table 表名(
       属性名1 数据类型,
       属性名2 数据类型,
       属性名3 数据类型,
       ......
       index/key 索引名 (属性名1 [长度] [asc/desc],
                        属性名2 [长度] [asc/desc],
                        ······
                        属性名n [长度] [asc/desc],
                        )
);

2.在已有表上创建多列索引

create index 索引名 on 表名 (属性名1 [长度] [asc/desc],
                           属性名2 [长度] [asc/desc],
                           ······
                           属性名n [长度] [asc/desc]
);

3.修改表方式创建多列索引

alter index 表名 add index/key 索引名 
                    (属性名1 [长度] [asc/desc],
                     属性名2 [长度] [asc/desc],
                     ······
                     属性名n [长度] [asc/desc]);

3.1.3.删除索引

drop index 索引名 on 表名;

3.2 视图的基本操作

3.2.1.创建视图

create view 视图名 as 查询语句;

3.2.2.查看视图

show tables

4.数据库安全及性能优化

4.1 用户与权限管理

4.1.1.创建用户账号

1.create user语句

create user 
username [identified by [password] 'password']
[,username [identified by [password] 'password']]······

例如：
//创建用户名为test1、主机名为localhost、密码为123456的用户账户
create user 'test1'@'localhost' indentified by '123456'

//创建用户名为test2、允许远程登录的用户账户、密码为123456
create user 'test2' indentified by '123456'

//创建用户名为test3、主机名为localhost、没有密码的用户账户
create user 'test3'@'localhost';

2.grant语句

grant priv_type on database table to 
username [identified by [password] 'password']
[,username [identified by [password] 'password']]······

例如：
//创建用户名为test4、主机名为localhost、密码为123456的用户账户,同时设置只对mydb数据库Student表有selsct权限
grant priv_type on mydb.Student to 'test4'@'localhost' indentified by '123456'
//查看test4的权限
show grants for 'test4'@'localhost';

3.向mysql. user表中插入记录

insert into user(host,user,authentication_string)
  values('hostname','username',password('password'));

4.1.2.删除用户账号

1.drop user语句

drop user user1,[user2]······

例如：
//删除用户名为test4、主机名为localhost的用户账户
drop user 'test4'@'localhost';

2.delete语句

delete from user
    where user = 'username' and host = 'hostname';

例如：
//删除用户名为test3、主机名为localhost的用户账户
delete from user
    where user = 'test3' and host = 'localhost';

5.MySQL权限列表

权限	权限级别	权限说明
create	数据库、表或索引	创建数据库、表或索引权限
drop	数据库或表	删除数据库或表权限
grant option	数据库、表或保存的程序	赋予权限选项
references	数据库或表
alter	表	更改表，如添加字段、索引等
delete	表	删除数据权限
index	表	索引权限
insert	表	插入权限
select	表	查询权限
update	表	更新权限
create view	视图	创建视图权限
show view	视图	查看视图权限
alter routine	存储过程	更改存储过程权限
create routine	存储过程	创建存储过程权限
execute	存储过程	执行存储过程权限
file	服务器主机上的文件访问	文件访问权限
create temporary tables	服务器管理	创建临时表权限
lock tables	服务器管理	锁表权限
create user	服务器管理	创建用户权限
process	服务器管理	查看进程权限
reload	服务器管理	执行flush-hosts、flush-logs、flush-privileges、flush-status、flush-tables、flush-threads、refresh、reload等命令的权限
replication client	服务器管理	复制权限
replication slave	服务器管理	复制权限
show databases	服务器管理	查看数据库权限
shutdown	服务器管理	关闭数据库权限
super	服务器管理	执行kill线程权限