SQL语言包含四个部分:
DDL(数据库定义语言):用于定义和管理数据对象,包括数据库、数据表等
如:create、drop、alter
DML(数据库操作语言):用于操作数据库对象中所包含的数据
如:insert、update、delete
DQL(数据库查询语言):用于查询数据库对象中所包含的数据
如:select
DCL(数据库控制语言):管理数据库的语言,包括管理权限及数据更改
如:grant、revoke、commit、rollback
【对数据库的操作】
创建数据库:create database/schema [if not exists] db_name default character set '字符集';
删除数据库:drop database if exists db_name;
查看已有的数据库:show databases/schemas;
打开指定数据库:use db_name;
查看当前打开的数据库:select database();
查看已创建的指定数据库的编码方式:show create database db_name;
修改已有数据库的编码方式:alter database db_name default character set '字符集';
临时转换客户端的编码方式:set names gbk;(临时的用来输入中文,退出后就失效)
将已有的数据库备份到外部文件中:
mysqldump -u用户名 -p密码 db_name >> 文件路径/text.sql
将外部文件中的数据导入到已有数据库中:
mysql -u用户名 -p密码 db_name < 文件路径/text.sql
方法二:
use db_name;
source 文件路径/text.sql
为数据库创建新用户授权:
GRANT 权限 ON 数据库.数据表 TO 用户名@'登录的主机名' IDENTIFIED BY '密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;(立即生效)
例:
grant all/select,insert,update,delete on *.* to root@'%' identified by '密码' with grant option;
flush privileges;
(*.*表示所有数据库的所有数据表;'%'表示通过任意主机进行授权,可为'localhost'或已有用户的'主机IP')
【对数据表的操作】
创建数据表:
create table [if not exists] `table_name`(
`字段名称` 字段类型 [字段属性],
... ...
)[engine=myisam default charset=utf8];
例:
create table [if not exists] `users`(
`id` int unsigned auto_increment primary key, #添加索引的三种方式之一
`username` varchar(30) not null unique comment "字段加注释",
`password` char(32) not null,
`age` tinyint unsigned,
`sex` enum("男","女","保密") default "男",
`addr` varchar(255),
`face` varchar(40) not null default "01.jpg",
`email` varchar(50) not null default "819508293@qq.com",
`vip` tinyint(1) not null default 0 comment "会员,0代表不是会员,其余值为会员",
`startTime` int unsigned comment "会员起始时间",
`endTime` int unsigned comment "会员到期时间",
`ip` int not null, #可利用 ip2long(str) 将IPv4转为int数值写入数据库,long2ip() 可将该int数值转为标准点格式的字符串输出;
key users_email(`email`), #添加索引的三种方式之二
key users_ip(`ip`)
)engine=myisam default charset=utf8 [collate utf8_general_ci];
给字段添加注释:在字段最后加 comment "注释内容"
删除数据表:
drop table table_name1,table_name2...;
修改数据表结构项:
alter table table_name add `字段名称` 字段类型;
例:alter table table_name add `email` varchar(50);
alter table table_name drop `字段名称`;
例:alter table table_name drop `email`;
修改:
alter table table_name modify `字段名称` 字段类型 字段属性;
注:只能修改对应字段名称的字段类型和属性
例:alter table table_name modify `name` varchar(30) not null;
alter table table_name change `字段名称` `新字段名称` 字段类型 字段属性;
注:不仅能修改字段类型和属性,也能修改字段名称
例:alter table table_name change `name` `username` varchar(30) not null;
用修改表结构方法添加索引:
alter table table_name add primary key(id);
alter table table_name add unique table_name_字段名(字段名);
alter table table_name add index table_name_字段名_index(字段名);
用修改表结构方法删除索引:
alter table table_name drop primary key;
alter table table_name drop index table_name_字段名;
1.删除主键索引前,必须先将自增长修改掉;
drop index 而不是drop unique;
查看数据表中所有字段的索引:
show indexes from table_name \G
查看当前数据库已有数据表:show tables;
查看已创建的指定数据表:show create table table_name;
查看数据表的结构:
desc table_name;
describe table_name;
show columns from table_name;
【对数据表中数据的操作】
向数据表中添加数据:
insert [into] table_name(字段名称1,字段名称2...) values("值1","值2"...);
例:
①指定字段名称和字段值一一对应,是字符串类型的须使用引号(" "/ ' ')包含,不是字符串类型的可加可不加:
insert table_name(id,username,password) values(1,"admin1",'password1');
②可为空的自增列在插入字段值时可使用NULL,会自动转为自增的值:
insert table_name(id,username,password) values(null,"admin1",'password1');
③自增列、非空且有默认值的字段可无需插入,仍会自动添加自增值或默认值:
insert table_name(username,password) values("admin1",'password1');
④字段名称的顺序可自由排列,但后面字段值的顺序也应对应排列:
insert table_name(password,username) values('password1',"admin1");
⑤可以省略字段名称,但在插入字段值的时候必须按照数据表中的字段顺序插入:
insert table_name values(null,"admin1",'password1');
insert [into] table_name(字段名称1,字段名称2...) values("值1","值2"...),("值1","值2"...),...;
例:insert table_name(username,password) values("admin1","pass1"),("admin2","pass2"),("admin3","pass3");
删除数据表中的数据:
delete from table_name [条件语句];
例:
delete from table_name where 条件;
delete from table_name order by 字段名称 asc limit 3;
清空数据表中的数据:truncate [table] table_name;
注:delete后再添加数据时,auto_increment(自增长)的值不会重置而是向后递加;truncate后auto_increment的值重置为1,重新开始递增;
修改数据表中的数据:update table_name set 字段名称="值",字段名称="值",...;
有条件的定向修改部分数据:
update table_name set 字段名称="值" 条件语句;
例:
update table_name set 字段名称=字段名称+1 where 条件;
uodate table_name set 字段名称="值" order by 字段名称 desc limit 3;
select查询语句:
select [all/distinct] */字段名称 from table_name [where 条件] [group by 分组] [having 条件过滤(二次过滤)] [order by 排序] [limit 显示条数];
select * from table_name;
select * from table_name \G (注意此时结尾是不加分号“;”的)
select count(*) from table_name;
select count(*) as 别名 from table_name;
select 字段 [as] 别名,字段 别名 from table_name;
select */字段名称 from table_name 条件语句;
select distinct 字段名称 from table_name;(distinct为去重复项)
order by 字段 asc(升序)/desc(降序)
select * from table_name [where 条件] order by 字段 asc/desc;
select * from table_name order by 字段1 asc/desc,字段2 asc/desc;
查询限制显示条数的记录:
select * from table_name limit 数值;(默认从头开始)
select * from table_name limit 0,2;(0-偏移量,跳过前多少条;2-显示多少条)
可用来做分页
例:
1.查询各个年龄段的人数总数
select age,count(*) from table_name group by age;
2.查询出年龄大于20的各个年龄的人数总数
select age,count(*) from table_name where age>20 group by age;
3.查询出年龄大于20的各个年龄的人数总数多于1个人的
select age,count(*) as c from table_name where age>20 group by age having c>1;
4.以年龄进行升序排序,如果年龄相同的,按id降序排列
select * from table_name order by age asc,id desc;
5.随机排序
select * from table_name order by rand();
多表关联查询:
1.普通关联查询:
select [table1.]字段1,[table1.]字段2,[table1.]字段3,[table2.]字段1,[table2.]字段2 from table1,table2 where table1.id=table2.uid;
table1.id=table2.uid为关联项,table1中的数据值在table2中无关联项,则该数据不显示;
select [别名1.]字段1,[别名1.]字段2,[别名1.]字段3,[别名2.]字段1,[别名2.]字段2 from table1 as 别名1,table2 as 别名2 where 别名1.id=别名2.uid;
可以给数据表名称添加别名,方便简写;
2.连接关联查询:
左连接:以左表(table1)为主,先输出左表中的查询数据,右表中无关联项的则显示NULL
select table1.字段1,table1.字段2,table1.字段3,table2.字段1,table2.字段2 from table1 left join table2 on
select [别名1.]字段1,[别名1.]字段2,[别名1.]字段3,[别名2.]字段1,[别名2.]字段2 from table1 as 别名1 left join table2 as 别名2 on
右连接:以右表(table2)为主,先输出右表中的查询数据,左表中无关联项的则显示NULL
select table1.字段1,table1.字段2,table1.字段3,table2.字段1,table2.字段2 from table1 right join table2 on
select [别名1.]字段1,[别名1.]字段2,[别名1.]字段3,[别名2.]字段1,[别名2.]字段2 from table1 as 别名1 righttable2 as 别名2 on 别名1.id=别名2.uid;
内连接:不以任何表为主,直接查询on后面的关联条件项,无对应数据的均不显示
select table1.字段1,table1.字段2,table1.字段3,table2.字段1,table2.字段2 from table1 inner join table2 on
select [别名1.]字段1,[别名1.]字段2,[别名1.]字段3,[别名2.]字段1,[别名2.]字段2 from table1 as 别名1 innertable2 as 别名2 on 别名1.id=别名2.uid;
3.嵌套关联查询(不推荐使用,效率极低)
select * from table1 where id in(select uid from table2);
当使用条件语句时可用条件:
和:&& / and 或:|| / or 非:!=
值为null/不为null:is null / is not null
在什么范围之内:between...and...
不在什么范围之内:not between...and...
包含在其内:in(1,2,3,4...)
不包含在其内的:not in(1,2,3,4...) 除了括号中的匹配外其他都显示
模糊查询:like / not like
通配符:%(代表任意多个字符) _(代表任意一个字符)
聚合函数:
count(*):统计总数
sum(字段名)
avg(字段名)
max(字段名)
min(字段名)