sql语句

– 连接数据库
mysql -uroot -proot
– 可以保护你的密码，不泄露*
mysql -uroot -p
– 查看mysql数据库中已有的数据库
show databases;
– 创建数据库
create database 1411b;
– 删除数据库
drop database 1411b;
– 打开数据库
use 1411b;
– 建表
create table t_student(
sid int(11) primary key auto_increment,
name varchar(20),
age int(3)
);
– 查询当前数据库中已有的表
show tables;
– 查看表结构
describe t_student;
desc t_student;
– 添加数据
insert into t_student values(null,’zhangsan’,20);
insert into t_student value(null,’lisi’,21);
insert into t_student(age,name) value(25,’zhaoyun’);
insert into t_student(name,age) value(‘zhaoliu’,23),(‘tianqi’,24);
insert into t_student(age,name) value(25,’zhaoyun’);
– 查询数据
select * from t_student;
select name from t_student;
select age,name from t_student;
select * from t_student where sid<5;
select * from t_student where age<25;
– 添加中文数据
set names gbk;
insert into t_student(age,name) value(25,’赵云’);
– 删表
drop table t_student;
– 修改
update t_student set name=”张三” where name=’zhangsan’;
update t_student set age=30 where sid=1;
update t_student set age=31,name=’李四’ where sid=2;
update t_student set age=20;
– 删除
delete from t_student where sid=7;
delete from t_student where sid<4;

– 数据类型
create table t_test(
tid int(11) primary key auto_increment,
name varchar(2)
);
insert into t_test(name) value(“年后”);
insert into t_test(name) value(“年后好”);
– 日期类型
create table t_test1(
tid int(11) primary key auto_increment,
name varchar(20),
createdate date,
tdate datetime,
tm time
);
insert into t_test1(name,createdate,tdate,tm) value(“张三”,’2016-04-19’,’2016-04-19 11:15:10’ ,’11:15:20’);

–DDL 数据定义语言，对数据库，表的操作
– 查看mysql数据库中已经存在的数据库
show databases;
– 创建数据库
create database mydb;
– 删除数据库
drop database mydb;
– 打开数据库
use mydb;
– 查看当前数据库中的表
show tables;
– 结束本次输入的语句：
\c
– 退出mysql提示符，返回Dos
Ctrl+c
– 表结构操作
– 建表
create table t_test2(
tid int(11) primary key auto_increment,
name varchar(20) unique
);
– unique(唯一约束）代表本列的值在表中只能出现一次
insert into t_test2(name) value(‘张三’);

create table t_test3(
tid int(11) primary key auto_increment,
name varchar(20) not null ,
age int(3) default 20
);
– not null（非空约束）代表该列不能为空，必须插入值；
– default (默认值）如果该列不插入值，则使用默认值
insert into t_test3(name) value(‘a’);
insert into t_test3(name,age) value(‘a’,3);
– 删除表
drop table t_test;
– 改表名
alter table t_test rename t_t;
alter table t_t rename t_test;
– 查看表结构
desc t_test;
– 增加列
alter table t_test add column age int(3) ;
alter table t_test add tel varchar(20) ;
– 删除列
alter table t_test drop tel;
– 修改列类型
alter table t_test modify age varchar(10);
– 修改列名
alter table t_test change age tel varchar(20) ;
– 增加约束
alter table t_test add unique (name);
– 删除约束
alter table t_test drop index tel;
alter table t_test add name varchar(20) not null;
– 把添加的列在第一个位置
alter table t_test add address varchar(20) first;
– 添加列在某一列的后边
alter table t_test add sex varchar(20) after tel;

– 查询条件
– =等于,查询学生名字为赵云的学生
select * from t_student where name =’赵云’;
– != <> 不等于
select * from t_student where name != ‘赵云’;
select * from t_student where name <> ‘赵云’;
– <= >= 用于指定范围
– 查询sid在>=2 并且<=100的学生
select * from t_student where sid >=2 and sid<=100;
select * from t_student where sid between 2 and 100;
select * from t_student where sid not between 2 and 100;
– in（） 指括号中存在的值就查询出来该值匹配的记录
select * from t_student where sid in(2,4,5,200);
select * from t_student where sid not in(2,4,5,200);
– like 像。。。,模糊查询;% –>代表0到n个字符
select * from t_student where name like ‘张%’;
select * from t_student where name like ‘%a%’;
– _代表一个字符
select * from t_student where name like ‘张_’;
select * from t_student where name like ‘张__’;
– is null为空,is not null不为空
select * from t_student where name is null;
select * from t_student where name is not null;
– and 代表连接的条件都要满足， or连接的条件满足哪个查哪个
select * from t_student where age >=11 and age <=25;
select * from t_student where age >=11 or age <=25;
– GROUP BY 分组
– t_student添加sex列
alter table t_student add sex varchar(10) default ‘男’;
update t_student set sex=’女’ where sid in(4,5,6);
– 按性别分组查询学生,查询男学生 （having）
select * from t_student group by sex having sex=’男’;
select * from t_student where sid<5;
select * from t_student having sid<5;
– Order by 排序 asc 升序（正序） desc 降序（逆序）， 默认查询就是升序
select * from t_student order by age asc;
select * from t_student order by age desc;
– 分页Limit 限制,limit m 表示查询出前每条记录;
select * from t_student limit 3;
–limit m,n m代表从m条以后的数据查询出来，n代表要查询的条数
select * from t_student limit 3,3;
– 分页 limit m,n –> m*n-n

– （聚合）函数
– DISTINCT 去重复数据
– 查学生表中有哪些性别
select distinct sex from t_student;
select distinct * from t_student;
select distinct age,sex from t_student;
– avg()取平均值
select avg(age) from t_student ;
– 别名，对列名及表名器别名
select avg(age) as av from t_student ;
select avg(age) av from t_student ;
select avg(st.age) av from t_student st ;
select avg(t_student.age) av from t_student st ;
select st.name,u.age,u.name from t_student st ,t_user u ;
– count(),统计表中记录数量
select count(*) c from t_student;
select count(sid) c from t_student;
– sum()求和
select sum(age) from t_student;
– max()最大值
select max(age) from t_student;
– min()最小值
select min(age) from t_student;
– instr(str,substr),查找子字符串第一次出现的位置
select instr(‘adsafdasfdsaf’,’sa’);
– dual 亚表
select instr(‘adsafdasfdsaf’,’sa’) from dual;
– concat(str,str,,..) 字符串连接
select concat(‘ab’,’cd’,’ef’);
– 获得字符串长度
select length(‘abc’);
select length(‘你好吗’);
– trim()，去除字符串开头和结尾所有空格
select trim(’ dsafdsaf dsfadsf dsfdsaf ‘);
– 获得当前系统时间sysdate(),now(),current_date(),current_time()
select sysdate(),now(),current_date(),current_time();
– 获得当前系统时间 now(),curdate(),curtime();
select now(),curdate(),curtime();

– 多表
– 建表
create table t_school(
scid int(11) primary key auto_increment,
scname varchar(20),
address varchar(50),
tel varchar(12)
);
insert into t_school(scname,address,tel) value(‘移动学院’,’2号楼’,’123455’),(‘游戏学院’,’6号楼’,’1877985’),(‘软学院’,’8号楼’,’1299955’);
create table t_stu(
sid int(11) primary key auto_increment,
sname varchar(20),
age int(3),
scid int(11),
foreign key(scid) references t_school(scid) on delete cascade
);
insert into t_stu(sname,age,scid) value(‘张三’,20,1),(‘李四’,21,2),(‘王五’,20,3),(‘赵六’,20,3),(‘张三’,20,3),(‘张飞’,20,3);
– 查询学院信息及学院下的所有学生
select * from t_school sc ,t_stu s where sc.scid=s.scid;
select * from t_stu s,t_school sc where sc.scid=s.scid;
select sc.,s. from t_stu s,t_school sc where sc.scid=s.scid;
– 级联删除，在删除学院的时候，会把该学院的所有学生自动从学生表删除
delete from t_school where scid=3;

– 子查询
select * from t_student where sid in(1,2,3);
select * from t_student where sid in(select sid from t_student where sid<4);
– 查出学生表中年龄最大的学生姓名和年龄
select name,age from t_student where age=(select max(age) from t_student);
– 多表关联查询
– join on用来关联两张表，内连接
select * from t_school sc ,t_stu s where sc.scid=s.scid;
select * from t_school sc join t_stu s on sc.scid=s.scid;
– 外连接（左外连接，右外连接）

insert into t_school(scname,address,tel) value(‘传媒学院’,’2号楼’,’123455’),(‘高翻学院’,’6号楼’,’1877985’),(‘软学院’,’8号楼’,’1299955’);
insert into t_stu(sname,age,scid) value(‘王五’,20,4),(‘赵六’,20,4),(‘张三’,20,4),(‘张飞’,20,4);
insert into t_stu(sname,age) value(‘赵云’,20),(‘刘备’,20);
– 查询所有学院 ,及学院的所有学生（左外连接）
select * from t_school sc left join t_stu s on sc.scid=s.scid;
select * from t_stu s right join t_school sc on sc.scid=s.scid;
– 查询学院的学生及所有学生（右外连接）
select * from t_school sc right join t_stu s on sc.scid=s.scid;
select * from t_stu s left join t_school sc on sc.scid=s.scid;
– 笛卡尔积，（很少用）
select * from t_school sc ,t_stu s；