mysql笔记1

structured query language sql 结构化查询语言

sql是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

自带四个数据表：mysql,information_schema,test,performance_schema,

mysql:各类权限相关及帮助信息

informance_schema:数据库对象相关概要

test:测试的空数据库

performance_schema:配置及性能信息

5.0新增informance_shcema

5.5新增performance_shcema 默认关闭 ,配置文件my.ini开启performance_schema=on

select * from information_schema.schemata;查看mysql数据库下所有库信息

select table_schema,table_name from information_schema.tables;查看所有库及对应表

select table_schema,table_name,column_name from information_schema.columns;查看所有库及对应表，及各表字段

show databases;

create database hellocs;

create database hellocs charset utf8;创建数据库并指定字符集

create database hellocs if not exists hellocs charset utf8;

use hellocs;

drop database hellocs;

show character set;查看mysql字符集，常用utf8,、gbk、gb2312

show global variables like 'port'; 查看本机端口

show tables;

desc g1;

create table g1(

id int(20) not null auto_increment primary key,

name varchar(20) not null,

pass varchar(20) not null

);

数据类型

数据库约束

说明：约束是一种限制，对表的行或列的数据做出限制，确保表数据的完整性、唯一性。

主键约束，primary key constaint，要求主键列的数据唯一，不允许为空，能够唯一标识。

单字段主键：一个字段，

id int(20) primary key,

或者在表定义末尾

primary key(id)

多字段主键（复合主键）：多个字段

primary(id,id2)

唯一约束，unique constaint，要求该列唯一，允许为空，确保一列或多列不出现重复值。

格式：

id int(20) unique;

或者

unique(id,id2);

默认约束，default constaint,对某列指定默认值。

格式：

sex varchar(20) default '男';

外键约束，foreign key,两个表数据建立连接，一个表可以有一个或多个外键。外键可为空，。

foreign key(列名) references 主表名(主键)

非空约束,not null constraint，指字段不能为空。添加数据时不指定的话会报错。

格式：

sex varchar(20) not null;

自动增加 auto_increment

id int primary key auto_increment;

表复制

create table [if not exists] gnew like gold;将表gold复制一份为gnew

查看表结构

desc gnew;

插入数据

注意：

多条数据添加用,号分隔，

字符和日期型应包含在单引号中，

格式：

指定查，顺序需一致

insert into g1(id,name) values(12,'gao');

默认插，顺序需一致

insert into g1 values(20,'g','y'),(21,'g','y');

复制数据

insert into gnew select * from gold;默认复制

insert into gnew id,name select id2,name2 from gold; 指定复制

测试：是否在复制表结构的同时，也复制表数据

删除delete

delete from g1;删除表中所有数据，表结构存在

delete from g1 where id=2;

删除表结构

drop table g1;

修改update

update g1 set name='gao' where id=12;

跨表修改：

update g1,g2 set g1.name=g2.name2 where g1.id=g2.id2;

修改表结构 alter

1.alter table 表名 add 字段名 字段约束条件 #增加字段

2.alter table 表名 drop 字段名 #删除字段

3.alter table 表名 change 字段名 新字段名 字段约束条件 #修改字段

4.alter table 表名 modify 字段名 字段约束条件 #修改字段约束条件

查询select

select * from table;

select id,name form g1 where id=2;

select * from stu where id <>25;其中不等于表示方式有：<>，!=

select * from stu where id [not] between 1 and 10 ;[not] between x and y 表示[不]查询某个字段的x与y间值，包括x,y。

select * from stu where id [not] in (1,2,3); [not] in(x,y,z....) 表示[不]查询某个字段的集合x,y,z...中相匹配的值。

select id,2018-age from student; 查询经过计算的值

select id idxx from student; 为id设置列别名

select distinct/all id from student; 消除/保留重复列

模糊查询

[not] like 模糊匹配 _单个字符 %任意多个字符，

查询字段本身含有通配符%或_，需要使用escape '<转码字符>',

select * from student where name like '高\_驿' escape '\';其中_不再作为通配符，因为其后紧跟转码字符。

elect * from student where name like '高\_驿__' escape '\';查询的名字是高_驿**，其中只对第一个_进行转义。

select * from student where name like '高_';

select * from student where name like '高%驿';

限制查询

select * from studnet limit 1 限制查询数量

详细说明：limit x 表示查询x条数据

select * from student limit 0,1 限制查询数量

详细说明 ：limit x,y 表示从x+1开始查询，查询y条数据，x默认从0开始，表示第一条数据。如limit 2,3表示从第3条数据开始查，查3条数据。

select * from student limit 4 offset 9 限制查询数量

详细说明：limit x offset y表示从y+1开始查询,查询x条数据，y默认从0开始，表示第一条数据。如limit 4 offset 9表示从第10条数据开始查，查4条数据。

order by 排序

说明：对结果集按照一个或多个数据列排序，asc 升序， desc降序，默认升序

select * from student order by name desc,id asc;

group by 分组

说明：对结果集按照某一列或多列的值进行分组，值相等的为一组。

分组后，聚集函数将作用于结果集中的每一个组。

select name,count(*) from city group by name;表示按名进行分组，并统计名字相同的人数。

select name,count(*) from city group by name having count(*)>2;在上一个查询结果中将名字重复数大于2的统计出来。

数据处理

函数

database() 查询当前所使用的数据库名

user() 或 current_user() 查询当前用户

version() 或 @@version 查看当前数据的版本

使用函数

如 select database();

聚集函数

聚合函数-字符串连接函数

concat（str1,str2....）函数

直接连接

group_concat（str1,str2.....）函数

使用逗号做为分隔符

concat_ws（sep,str1,str2....）函数

使用第一个参数做为分隔符

concat的使用限制

说明：当concat后面连接的字符有一个是空时，整个显示为空。

select concat('a','b','c'); abc

select concat('a','b',null); null

字符处理函数

文本处理函数	含义
left(str,len)	返回字符串左边的长度为len的子字符串
length(str)	返回字符串的长度
cate(substr,str,pos)	找出str中substr的起始索引
lower(str)	捋字符串转换为小写
Itrim(str)	去掉字符串左边的空格
right(str,len)	返回字符串右边的长度为len的子字符串
Round()	把数值字段舍入为指定的小数位
upper(str)	捋字符串转换为大写
rtrim(str)	去掉字符串右边的空格
substr(str,pos,[len])	返回字符串从pos位置开始长为len的子字符串
hex(str),hex(n)
unhex(str)
ascii(str)
char(n)
reverse(str)
lpad(str,len,padstr)
rpad(str,len,padstr)

注意：在数据处理过程中，字符串的第一个就是代表1；substr()是substring()的别名'

帮助信息查看：如 help show;

select left('gao666',3); 'gao'

select right('gao666',3); '666'

select locate('gao','gao666gao',0); 0

select locate('gao','gao666gao',1); 1

select locate('gao','gao666gao',2); 7

select lower('gao666GAO'); 'gao666gao'

select upper('gao666GAO'); 'GAO666GAO'

select round('66.666',1); 66.7

select round('66.666',0); 67

select ltrim(' gao 666 '); 'gao 666 '

select ltrim(' gao 666 '); ' gao 666'

select substr('gao666GAO',1,6); 'gao666'

select substr('gao666GAO',7,3); 'GAO'

select substr('gao666GAO',1); 'gao666GAO'

select substr('gao666GAO',-3,2); 'GA'

select substring('gao666GAO' from -6 for 3); 666

select mid('gao666GAO',4,3); 666

select lpad('gao666GAO',3,'*'); 'gao'

select lpad('gao666GAO',10,'*'); '*gao666GAO'

select lpad('gao666GAO',3,'*'); 'gao'

select lpad('gao666GAO',10,'*'); ' gao666GAO*'

select reverse('gao666GAO'); 'OAG666oag'

select ascii('a'); 97

一些重要字符ascii码 0:48, A:65, a:97 常见ASCII码的大小规则：0~9<A~Z<a~z。

select char(97); 'a'

select hex('a') 61

select unhex('61') 'a'

select 0x61; 'a'

日期处理

select curdate(); 2019-07-24

select curtime(); 15:44:13

select now(); 2019-07-24 15:44:24

select curdate()+0; 20190724

select datediff(now(),'2008-12-12'); 3876

select datediff('2009-12-12','2008-12-12'); 365

select date_format(now(),'%b'); Jul

select date_format(now(),'%Y'); 2019

组合测试

select reverse(left('gao666GAO',3)); 'oag'

查询条件

注释

单行注释： #, 如 #这是一个单行注释

多行注释：/**/, 如/*这是一个多行注释*/

内联注释：/*!...*/

说明：为了保持与其他数据库兼容。进一步说明，从mysql导出的sql语句为了能在其他数据库使用，一些mysql特有的语句放在了/*!...*/中，这些语句在不兼容数据库不执行，而在mysql中可以识别、执行。

如/*!50001 语句*/表示数据库版本为50.0.01时中间的语句才执行

一些补充

asc 升，默认，desc 降,order by

[not] like 模糊匹配 _单个字符 %任意多个字符

or || 或者 and && 并且 not 反

联合查询

子查询

一个select-from-where语句称为一个查询块，讲一个查询块嵌套在另一个查询块的where子句或者having短语的条件中的查询叫做子查询。又称嵌套查询。

1.带有in谓词的子查询

select name

from student

where dept in(select dept from depts where name='zhangsan');

2.带有比较运算符的子查询

select name

from student

where dept=(select dept from depts where name='zhangsan');

3.带有any或all谓词子查询

>any	大于子查询结果集中的某个值
>all	大于子查询结果集中的所有值
<any	小于子查询结果集中的某个值
<all	小于子查询结果集中的所有值

select name from ban 1 where age>ALL(select age from ban_2);

内外连接

内连接：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

联合查询基本语法	内连接	仅显示两个表中匹配行，即两表中都有才显示。 select 列名 from 表a inner join 表b on（条件）
	外连接	左外连接：左表有就显示，不论右表。 select 列名 from 表a left outer join 表b on（条件）
		右外连接：右表有就显示，不论左表。 select 列名 from 表a right outer join 表b on（条件）

内连接

1.select * from tgao1 inner join tgao2 on tgao1.id=tgao2.id;

左连接：左边的表记录全部展示，右表只会显示符合搜索条件的记录，不足的地方为null。

2.select * from tgao1 left join tgao2 on tgao1.id=tgao2.id; left join简写left outer join

右连接：右边的表记录全部展示，左表只会显示符合搜索条件的记录，不足的地方为null。

2.select * from tgao1 right join tgao2 on tgao1.id=tgao2.id; right join简写right outer join

union联合查询：把2条或者多条查询语句的结果合并成1个结果集

select * from tgao1 union [all] select * from tgao2;其中all显示两个表的所有数据，包括重复的。

注意：

union只能连接查询语句

union两侧的查询语句，结果集必须有相同的列数。

mysql维护

数据库备份 mysqldump

1.只备份数据表，不备份数据库。

说明：也就是没有create database和use database语句

格式：mysqldump-u username-p password dbName>name.sql存放路径

示例：mysqldump -u root -p root gao666>c:\gao666.sql 注：需要在cmd下，不能进入mysql中。

数据库恢复 source

说明：首先需要创建好数据库，才能使用下面的命令。因为备份时没有创建和使用数据库语句。

source c:/gao666.sql 注：此项需要先创建数据库并使用，然后在mysql中执行(在mysql命令行模式下,恢复数据库命令中的数据库表目录需要使用/来,如:c:/users/gao666/desktop/gao666.sql)

2.同时备份数据库

格式：mysqldump-u username-p password -B dbName>name.sql存放路径

示例：mysqldump -u root -p root -B gao666>c:\gao666.sql 注：需要在cmd下，不能进入mysql中。

数据库恢复

格式：mysql -uusername -ppassword [dbname]<filename.sql

参数:

username:登陆的用户名

password:用户的密码

dbname:表示要还原的数据库名称，若使用mysqldump命合备份的filename.sql文件中包含创建数据库的语句，则不需要指定数据库。

示例：mysql -uroot -proot <c:\gao666+.sql

访问控制

查看当前数据库下的用户

格式：select user.host from mysql.user;

登陆认证：用户名，密码，客户端

新建账户：

craete user 'gao666'@'192.168.198.120' identified by '666'; 注： mysql下，下同

修改用户密码：

格式：mysql>set password for 'user'@'host'=password('new_password");

set password for 'gao666'@'192.168.198.120'=password('gao666');

修改用户账户：

格式：rename user 'olduser'@' old_host' to 'newuser'@'new host'

删除用户：

格式：drop user 'user'@'host';

查看用户权限

show grants for 'user'@'host;

grant all privileges on *.* to 'yangxin'@'% identified by 'yangxin123456' with grant option;

grant select,delete,update

on information_schema.*

to user '大虎'@'192.168.34.56' identified by 'qwe' with grant option;

all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。

on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数掘库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：testuser

to：将权限授予哪个用户，格式：“用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如："yangxin"@·192.168.0.%”，表示yangxin这个用户只能在192.168.0IP段登录

identified by：指定用户的登景密码

with grant option：表示允许用户将自己的权限授权给其它用户

1.授予在mysql_test数据库中的customers表上拥有对列cust_id和列cust_name的select权限

grant select(cust_id，cust_name)

on mysql_test.customers

to'zhangsan'@localhost'；

2.新建一个用户为liming，并授予其在数据库mysql_test的表customers上拥有select和update的权限

grant select，update

on mysql_test.customers

to 'liming'@localhost identified by'123'；

3.授予可以在数据库mysql_test中执行所有操作的权限

grant all

on mysql_test.*

to'zhangsan'@/localhost'；

4.授予系统中已存在用户zhangsan拥有创建用户的权限

grant create user on *.* to 'zhangsan'@'localhost'；

5.权限的限制

每小时只能处理一条delete语句的权限

grant delete on mysql test.customers to 'zhangsan'@'localhost

with max_queries_per_hour 1；

6.权限的撤销

回收用户zhangsan在数据库mysql_test的表customers上的select权限

revoke select on mysql_test.customers from'zhangsan'@localhost；

日志

查看日志开启状态

show global varivables like '%log%';

mysql加固

1.防止空口令/弱口令

修改用户密码：set password for 'user'@'host' =password('new_password');

2.历史命令行密码设置为不可见

3.使用专用的最小权限运行mysql

linux系统下默认低权限。

windows系统继承它的运行权限。

4.数据库用户权限最低化

5.数据库备份

备份命令 ;mysqldemp -uroot -proot -Dgao666>gao666.sql

6.修改mysql端口

7.限制用户连接的数量 。配置在mysql目录下的my.ini，max_connections，最大连接量

8.限制远程连接 bind_address=127.0.0.1