MySQL基础知识
net start mysql
net stop mysql
# 登陆和退出
mysql -hlocalhost -uroot -p123;
quit;
# 注释
#
/* */
脚本中可以使用 --
# 查看版本
select version();
# 查看日期
select current_date();
# 查看当前时间
select now();
# 查看用户
select user();
# 取消未完成的输入
\c
#显示有哪些数据库
show databases;
#显示当前数据库
select database();
#创建数据库。如：创建menagerie
create database menagerie;
#切换到menagerie
use menagerie;
#通过登陆直接进入menagerie数据库
mysql -hlocalhost -uroot -p menagerie
***
#删除数据库
drop database menagerie
#显示当前数据库中的table
show tables;
#新建一个table
create table pet(name varchar(20),
owner varchar(20),
species varchar(20),
sex char(1),
birth date,
death date);
#describe显示table的结构
describe pet;
#显示表是如何创建的
show create table pet;
#删除table
drop table pet;
#insert 插入记录
insert into pet values
('Puffball','Diane','hamster','f','1999-03-30',NULL);
#select 语句
select * from pet;
#delete 删除记录
delete from pet where name=’Puffball’;
#update 将Bowse的birth 改成1989-08-31
update pet set birth='1989-08-31' where name='Bowser';
#清空table
delet from pet;
#3.5批处理
mysql -hlocalhost -uroot -p123<"d:/site/s.txt"
#或
mysql -hlocalhost -uroot -p <"d:/site/s.txt"
Enter password: ***
#或
\. d:/site/s.txt
\. d:\\site\\s.txt
#where 语句
select * from pet where name='Bowser';
#找出1998年1月1日以后出生的PET
select * from pet where birth > '1998-1-1';
#找出母狗
select * from pet where species = 'dog' and sex='f';
#找出蛇以及鸟
select * from pet where species = 'bird' or species='snake';
#找出公猫以及母狗
select * from pet where (species="cat" and sex="m") or (species="dog" and sex="f");
#显示pet的Name 和 birth
select name, birth from pet;
# UNION
select * from pet where binary pet.name regexp '^b'
union
select * from pet where binary pet.name regexp 'py$';
#找出宠物主人
select owner from pet;
#DISTINCT 只出现一次
select distinct owner from pet;
#选出猫狗的name,species,birth
select name, species, birth from pet where
species='dog' or species ='cat';
#选出name,birth 按 birth 排序
select name, birth from pet order by birth;
#选出name,birth 按 birth 倒序排序
select name, birth from pet
order by birth desc;
#强制区分大小写功能
select name, birth from pet
order by binary name desc;
#多项排序
select * from pet order by species,birth desc;
#计算日期
select name, birth, curdate(),
(year(curdate())-year(birth))
-(right(curdate(),5)<right(birth,5))
As age
from pet;
#计算年龄并按年龄排序
select name, birth, curdate(),
(year(curdate())-year(birth))-
(right(curdate(),5)<right(birth,5))
as age
from pet
order by age;
#选出已死庞物的寿命，并按寿命排序
select name,birth,death,
(year(death)-year(birth))-
(right(death,5)<right(birth,5))
as DAGE
from pet where death is not null
order by DAGE;
#选出庞物的生日
select name, birth, month(birth) from pet;
#选出下月过生日的庞物
#方法一：
select name, birth from pet
where month(birth)=
month(date_add(curdate(),interval 1 month));
#方法二，利用求余法：
select name,birth from pet
where month(birth)=
mod(month(curdate())+1,12);
#模式匹配
#_匹配单个字符，%匹配任意数目字符
#SQL默认不区分大小写
#以b开头
select * from pet where name like 'b%';
#以fy结尾
select * from pet where name like"%fy";
#找出包含w的名字
select * from pet where name like "%w%";
#找出正好五个字母的名字
select * from pet where name like "_____";
#SQL支持扩展的正则表达式
#找出以b开头的名字，使用^匹配名字的开始
#加上 binary 区分大小写
select * from pet where name regexp "^b";
#找出以fy结尾的名字
select * from pet where name regexp binary "fy$";
#找出包含w的名字
select * from pet where name regexp "w";
#找出正好五个字母的名字
select * from pet where name regexp "^.{5}$";
#计算行
select count(*) from pet;
#计算出每组的行数
select owner, count(*) from pet group by owner;
#每种动物的数量
select species, count(*) from pet group by species;
#每种性别动物的数量，性别不为空
select sex, count(*) as amount from pet where sex is not null group by sex;
#按种类和性别组合动物
select species, sex, count(*) from pet group by species, sex;
#选出猫狗。种类、性别及对应的行数
select species, sex, count(*) from pet
where species='dog' or species='cat'
group by species,sex;
#种类、性别及对应的行数
select species, sex, count(*) from pet
where sex is not null
group by species, sex;
#列的最大值
#选出最大物品号
select max(birth) as article from pet;
#拥有某个最大值的行
select * from pet where birth =
(select max(birth)from pet);
#排序后的第一行
select * from pet
order by birth desc
limit 0,1;
#按组取出列的最大值 （非一一对应）
select name, max(birth) as bd
from pet
group by species;
#拥有某个字段的组间最大值的行
#上例一一对应
select * from pet p1
where p1.birth=
(select max(p2.birth)
from pet p2
where p2.species=p1.species);
#使用auto_increment
#
create table animals(
id mediumint not null auto_increment,
name char(30) not null,
primary key(id)
);
#
insert into animals (name) values
('dog'),
('cat'),
('penguin'),
('lax'),
('whale'),
('ostrich');
#
create table animals(
grp enum('fish','mammal','bird') not null,
id mediumint not null auto_increment,
name char(30) not null,
primary key(id,grp)
);
#auto_increment 的初值设为100
alter table animals auto_increment = 100;
