mysql学习笔记2

mysql -uroot -h127.0.0.1 -p
show databases;
use test;
create database testman;
select database();
show tables;
drop table test;
create table songlh(id int,name varchar(20),age int,birthday datetime);
show create table songlh;

这两个sql语句是一样的

show create database slh;
show create database slh\G

desc songlh;#查看表所有字段的类型

insert into songlh values(5,"zhao",10,"1981-11-10 10:10:10"),(6,"qian",8,"2010-1-1 1:1:1");

insert into songlh(id,name,age,birthday) values(6,"zhao",10,"1981-11-10 10:10:10"),(7,"qian",8,"2010-1

-1 1:1:1");

insert into songlh(id,name,age,birthday) values(8,"zhao",10,"1981-11-10 10:10:10");

delete from songlh where id>=7;

select * from songlh;

update songlh set name="jason" where name="zhao";

update songlh set name="Jordan",age=5 where name="jason";

select id,name from songlh;
select id 序号,name 名字 from songlh;
select * from songlh where id<>1;
select * from songlh where id between 2 and 5;
select * from songlh where id in(1,2,3);
select * from songlh where id in(select id from songlh where id>=5);
select id from songlh where id>=5 and id<=6;
select id from songlh where id>=5 and name="qian";
select id from songlh where id>=5 or name="qian";
select * from songlh order by id desc;
select * from songlh order by id asc;
select * from songlh order by id desc,age desc;
select * from songlh order by id desc,age desc limit 0,1;
select distinct name from songlh;
select count(name) 名字 from songlh;
select avg(age) 年龄 from songlh;
select max(age) 年龄 from songlh;
select min(age) 年龄 from songlh;
select sum(age) 年龄 from songlh;
select now();
select curdate();
select curtime();

查看表结构：

create table student(
id int not null auto_increment,
name varchar(20) not null,
sex char(1),
submission_date date,
primary key(id)
)engine=innodb character set utf8;

desc student;

create table grade(
id int not null auto_increment,
stuid int not null,
class varchar(20) not null,
grade int(3),
primary key(id)
)engine=innodb character set utf8;

insert into student(name,sex,submission_date) values("张三","男","2010-10-10");
insert into student(name,sex,submission_date) values("李四","男","2010-10-10");
insert into student(name,sex,submission_date) values("王五","男","2010-10-10");
insert into student(name,sex,submission_date) values("赵六","男","2010-10-10");
insert into student(name,sex,submission_date) values("孙七","男","2010-10-10");


insert into grade(stuid,class,grade) values(1,"计算机","100");
insert into grade(stuid,class,grade) values(1,"guitar","90");
insert into grade(stuid,class,grade) values(1,"美术","80");
insert into grade(stuid,class,grade) values(2,"计算机","70");
insert into grade(stuid,class,grade) values(2,"guitar","60");
insert into grade(stuid,class,grade) values(2,"美术","80");
insert into grade(stuid,class,grade) values(3,"guitar","50");

select * from student where name like '张%';
select sum(grade) from grade group by stuid having stuid=1;
select stuid,sum(grade) from grade group by stuid;
select stuid,avg(grade) from grade group by stuid;

select a.name,sum(grade) from student a,grade b where a.id=b.stuid group by b.stuid;#内连接
select a.name,sum(grade) from student a inner join grade b on a.id=b.stuid group by b.stuid;#内连接
select a.name,sum(grade) from student a left join grade b on a.id=b.stuid group by b.stuid;#左连接（group by丢数据）
select a.name,sum(grade) from student a left join grade b on a.id=b.stuid group by a.id;#左连接
select a.name,sum(grade) from student a right join grade b on a.id=b.stuid group by b.stuid;#右连接

内连接和外连接的区别

内连接（inner join）：取出两张表中匹配到的数据，匹配不到的不保留（取交集）
外连接（outer join）：取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL
内连接：
a表的id在b表的stuid出现，则数据会显示在结果中。
左外连接（left outer join）：以左边的表为主表
右外连接（right outer join）：以右边的表为主表
以某一个表为主表，进行关联查询，不管能不能关联的上，主表的数据都会保留，关联不上的以NULL显示

取出学生总成绩第二高的：

select a.name,sum(grade) from student a right join grade b on a.id=b.stuid group by b.stuid order by sum(b.grade) desc limit 1,1;

select id from student union select stuid from grade;#排重
select id from student union all select stuid from grade;

外键：表B的某一个列关联表A某一列，且B的这列数据的所有值，必须在表A的关联列中出现。
例如：表A关联列的数据是1-5，表B的关联列的所有数据只能是1-5的范围，能写6吗？不行。

truncate table b;#删除表的所有数据和索引，表还在