常用命令
select version();//查看当前版本
select now();//查看当前日期
select user();//查看当前用户
create database t1;//创建数据库t1
show warnings;//警告信息
show create database t1;//显示数据库创建信息
show database;//查看当前数据库
alter database t1 character set = utf8;//设置t1数据库编码方式
drop database t1;
--------------------------------------------------
行 ----记录
列 ----字段
use t1;//打开数据库
select database();//查看当前已打开的数据库
//创建数据表t1
create table tb1(
username varchar(20),
age tinyint unsigned,
salary float(8,2) unsigned
);
//创建自动编号主键
create table tb3(
id smallint unsigned auto_increment primary key,
username varchar(30) not null
);
show databases;//查看数据表
show database from mysql;//查看mysql数据库中的数据表
show columns from tb1;//显示当前表的结构
insert into tb1 values("tom",30,5437.32);//插入数据
insert (into) tb1(username, salary) values("John", 4534.43);//插入数据
select * from tb1;//所有字段tb1
//创建用户名非空表
create table tb2(
username varchar(20) not null;
age tinyint unsigned
);
//创建自动增长主键
create table tb3(
id smallint unsigned auto_increment primary key,
username varchar(30) not null
);
auto_increment 必须为主键
主键不一定为auto_increment
//创建unique key数据表
create table tb5(
id smallint unsigned auto_increment primary key,
username varchar(20) not null unique key,
age tinyint unsigned
);
//设置默认值
create table tb6(
id smallint unsigned auto_increment primary key,
username varchar(20) not null unique key,
sex enum('1','2','3') default '3'
);
父表
create table province(
id smallint unsigned primary key auto_increment,
pname varchar(20) not null
);
子表
create table users(
id smallint unsigned primary key auto_increment,
username varchar(20) not null,
pid smallint unsigned,
foreign key (pid) references province (id)//外键
);
show index from province;//显示索引
show index from province\G;//显示索引按列
create table users1(
id smallint usigned primary key auto_increment,
username varchar(20) not null,
pid smallint unsigned,
foreign key (pid) references provinces (id) on delete cascade
);
cascade: 从父表删除或更新且自动删除或更新子表中匹配的行
先在父表中插入记录->再在子表中插入记录
delete from province where id = 3;//删除记录
//父表中删除，子表中也会被删除
//不存在表级约束
not null
default
alter table users1 add age tinyint unsigned not null default 10;
//插入一列
alter table users1 add passwd varchar(20) not null after username;
//在username之后添加
alter table users1 add truename varchar(20) not null first;
//在第一列添加
alter table users1 drop truename;//删除truename列
alter table users1 drop passwd, drop age;//同时删除两列，也可以进行添加
alter table users2 add id smallint unsigned;
alter table users2 add constraint PK_users2_id primary key (id);//添加主键
alter table users2 add unique (username);//添加唯一约束
alter table users2 add foreign key (pid) references province (id);//添加外键
alter table users2 alter age set default 15;//设置默认值
alter table users2 alter age drop default;//删除默认值
alter table users2 drop primary key;//删除主键约束
show indexes from users2;//查看索引，再删除
alter table user2 drop index username;//删除索引-唯一约束
show create table users2;//查看创建表的信息，查看外键约束
alter table users2 drop foreign key users2_ibfk_1;//删除外键约束
alter table users2 drop index pid;//删除索引
alter table users2 modify id smallint unsigned not null first;//将id置为第一列，修改列定义
alter table users2 change pid p_id tinyint unsigned not null;//修改列的名字
alter table users2 rename users3;//修改表的名字
rename table users3 to users2;//修改表的名字
create table users(
id smallint unsigned primary key auto_increment,
username varchar(20) not null,
password varchar(20) not null,
age tinyint unsigned not null default 10,
sex boolean
);
insert users values(null,"Tom","123",25,0);//主键自动增长设置为Null
insert users values(default,"Rose","445",32,1);//也可以设置为default
insert users values(default,"Eric","324",3*4-4,1);//可以设置为数学表达式
insert users values(default,"Jow","111",default,1);//设置默认值
//同时插入多个数据时，用英文的逗号隔开
insert users set username="Ben", password="456";//使用set进行插入
update users set age = age + 5;//设置所有年龄+5
update users set age = age - id, sex = 0;//更改多行
update users set age = age + 10 where id % 2 = 0;//id为偶数的地方age+10
delete from users where id = 7;//删除id = 7的记录
//删除记录之后，id号是原有的最大id数+1
select id, username from users;//查询id, username两列
select username, id from users;//查询顺序可以和原表顺序不一致
select users.id, users.username, from users;//使得查询列的所属更为清晰
select id as userId, username as uname from users;//设置别名
select id username from users;//将出现一个字段，username 作为id别名出现
group by -----------ASC升序（默认）DESC降序
select sex from users group by sex;//查询sex分组
select sex, age from uses group by sex having age > 22;//选择分组，age>22;
select sex from users group by sex count(id) > 2;//选择id之后大于2的分组？
select * from users order by id desc;//设置按照id降序排列
select * from users order by age, id desc;//按照age升序排列，如果出现相同值则按照id降序排列（相同的记录按照降序排列）
select * from users limit 2;//从第0个开始选择两个记录
select * from users limit 3,2;//从第3个（实际是第四行）开始选择两个记录
//id号和第几个记录是没有关系的
insert test(username) select username from users where age > 25;//将查询的结果插入到test表中
子查询和连接
set name gbk;//在客户端以gbk的编码方式显示
select avg(goods_price) from tdb_goods;//求取tdb_goods表的goods_price的平均值
select round(avg(goods_price),2) from tdb_goods;//求取tdb_goods表的goods_price的平均值，四舍五入，取小数点后两位
select goods_id, goods_name, goods_price from tdb_goods where goods_price > 5636.36;//选取大于5636.36的商品的信息
select goods_id, goods_name, goods_price from tdb_goods where goods_price > （select round(avg(goods_price),2) from tdb_goods;）//子查询
select goods_id, goods_name, goods_price from tdb_goods where goods_price > ANY (select goods_price from tdb_goods where goods_cate="超级本");//查询商品信息在其商品的价格大于任何一个子查询得出的商品的价格
= >= <= > < <>
select goods_id, goods_name, goods_price from tdb_goods where goods_price <> all (select goods_price from tdb_goods where goods_cate="超级本");//除去子查询中的价格的所有
desc tdb_goods_cates;//查看表的结构
select goods_cate from tdb_goods group by goods_cate;//查询分组
insert tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;//将子查询中的查询到的分类插入数据库tdb_goods_cates的cate_name列
update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name set goods_cate = cate_id;//使用内连接的方式连接tdb_goods 和 goods_cate两张表 同时 在tdb_goods表中的goods_cate和goods_cate相等的时候，设置goods_cate等于cate_id
create table tdb_goods_brands(
brand_id smallint unsigned primary key auto_increment,
brand_name varchar(40) not null)
select brand_name from tdb_goods group by brand_name;
//创建表同时更新数据表
update tdb_goods as g inner join tdb_goods_brands as b on g.brand_name = b.brand_name
set g.brand_name = b.brand_name;
//设置连接
alter table tdb_goods change brand_name brand_id smallint unsigned not null;
//修改表的名字和类型
内连接 = 显示符合连接条件的记录
select goods_id, goods_name, cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cate.cate_id;
//内连接，连接查询tdb_goods的cate_id=tdb_goods_cate的cate_id相同的所有记录
select goods_id, goods_name, cate_name from tdb_goods left join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cate.cate_id;
//左连接，连接显示左边表的全部内容和右边表的符合条件的部分，如果左边表中的记录在右边表中没有，则显示为空
select goods_id, goods_name, cate_name from tdb_goods right join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cate.cate_id;
//右外连接，与左外连接相反
select goods_id, goods_name, cate_name, brand_name, goods_price from tdb_goods as g
inner join tdb_goods_cates as c on g.cate_id = c.cate_id
inner join tdb_goods_brands as b on g.brand_id = b.brand_id;
//多张表连接查询
select s.type_id as s_type_id, s.type_name as s_type_name, p.type_name as p_type_name from tdb_goods_types as s left join tdb_goods_types as p on s.parent_id = p.type_id;
//自身连接，查询子表中的parent_id=父表中的type_id的记录
select p.type_id as p_type_id, p.type_name as p_type_name, s.type_name as s_type_name from tdb_goods_types as p left join tdb_goods_type as s on s.parent_id = p.type_id;
//自身连接，查询父表中type_id=子表中parent_id的记录
select p.type_id as p_type_id, p.type_name as p_type_name, s.type_name as s_type_name from tdb_goods_types as p left join tdb_goods_type as s on s.parent_id = p.type_id group by p.type_name order by p.type_id;
//自身连接，查询父表中type_id=子表中parent_id的记录，并按照父表中的type_name进行分组，按照父表中type_id进行排序
select p.type_id as p_type_id, p.type_name as p_type_name, count(s.type_name) as s_count from tdb_goods_types as p left join tdb_goods_type as s on s.parent_id = p.type_id group by p.type_name order by p.type_id;
//count子类的数目
select goods_id, goods_name from tdb_goods group by goods_name having count(goods_name) >= 2;
//按goods_name进行分组，选择count(goods_name)大于2的记录
delete t1 from tdb_goods as t1 left join (select goods_id, goods_name from tbd_goods group by goods_name having count(goods_name) >= 2) as t2 on t1.goods_name = t2.goods_name where t1.goods_id > t2.goods_id;
//从表中删除重复的内容
select concat("test","-","sql");//连接字符
select concat(id, "-", username) as full_name from test;//从数据表中选择相应的字段连接按照full_name别名进行输出
select concat_ws("|", "A", "B", "C");//以字符"|"把ABC连接在一起
select format(231223123.235445, 2);//将数字进行输出，保留两位小数
select lower("Mysql");//转换为小写
select upper("Mysql");//转换为大写
select left("Mysql",2);//选取字符串的前两位
select lower(left("Mysql",2));//选取前两位并转换为小写
select right("Mysql",2);//选取后两位
select length("Mysql");//字符串的长度
select ltrim(" Mysql ");//去除左边空格
select rtrim(" Mysql ");//去除右边空格
select trim(leading "?" from "??MySQL??");//去除前面？号
select trim(trailing "?" from "??MySQL??");//删除后续？号
select trim(both "?" from "??MySQL??");//全部删除
select replace("??My??SQL??","?","");//替换？为空
select replace("??My??SQL??","?","！*");//替换为一个叹号一个星号
select replace("??My??SQL??","??","*");//两个问号替换为一个星号
select substring("MySQL",1,2);//查找字串，从第一位开始截取两个字符，字符串从1开始计数
select substring("MySQL",3);//从第三位开始，截取到字符串最后
select substring("MySQL",-1);//位置可以试负值，长度不能为负值, -1表示倒数最后一位到最后（也就是最后一位）
select * from test where first_name like "%o%";//查找有o的记录
select * from test where first_name like "%1%%" escape "1";//告诉1表示不是查找内容，表示第二个百分号是需要查找的内容
%代表任意个字符
_代表任意一个字符
---------------------------------------------------------------
select ceil(3.01);//向上取整，为4
select floor(3.99);//向下取证
select 3 div 4;//整除
select 5 mod 2;//取余，相当于%
select power(3,3);//幂运算
select round(3.432,2);//四舍五入，保留2位
select truncate(213.32,2);//直接截取，不四舍五入
select 15 between 1 and 22;//判断15是否在1和22之间，返回布尔类型1或者0
select 15 not between 1 and 22;//判断不在
select 10 in(5,10,15,20);//判断10是否在in中的集合中
select null is null;//判断null是否null，返回布尔
sekect * from test where first_name is null;//判断first_name为空的记录
---------------------------------------------------
select now();//当前日期时间
select curdate();//当前日期
select curtime();//当前时间
select date_add("2015-12-12",interval 365 day);//在2015-12-12基础之上增加365天
select date_add("2015-12-12",interval 1 year);
select date_add("2015-12-12",interval 3 week);//增加3周
select datediff("2013-3-12","2014-3-12");//查找日期差值
select date_format("2014-3-12","%m/%d/%Y");//更改日期更改格式
-------------------------------------------------------
select connection_id();//获取连接id
select database();//当前打开的数据库
select last_insert_id();//最近写入的id，写入多条记录的时候，只返回第一条写入记录的id
select user();//当前用户
select version();//版本信息
---------------------------------------------------------
聚合函数
select avg(id) from test;//对id求平均
select * from tdb_goods limit 1;//只查找一条记录
select round(avg(goods_price),2) as avg_price from tdb_goods;//获取平均价格，并保留两位小数
select count(goods_id) as counts from tdb_goods;//求取goods_id的个数
select max(goods_price) as price from tdb_goods;//求取最大的价格
select min(goods_price) as price from tdb_goods;//求取最小的价格
select sum(goods_price) as price from tdb_goods;//求价格的总和
select md5("admin");//加密
select password("admin");//加密
select date_format(now(), "%Y年%m月%d日 %H点:%i分:%s秒");//重新输出now()函数的格式
delimiter // ----修改分隔符号
drop function f1();//删除函数