数据库常用sql语句和操作

数据库操作

标签（空格分隔）： mysql

进入：mysql -h hostname -u user_name -p password

如果不写-h hostname默认本机（localhost） 显示所有数据库：show databases; 选定要操作的数据库：use database_name; 显示指定数据库中所有数据表：show tables; 退出数据库：\q

结束当前语句的输入:\c

对库进行操作：

新建数据库:create dtatbase database_name;

删除数据库：drop database database_name;(一次只能删除一个）

对表进行操作：

新建数据表：create table 表名(

列名1 列类型[列的完整性约束],

列名2 列类型[列的完整性约束],

……

列名n 列类型[列的完整性约束]

)default charset=utf9;(显示汉字，默认编码） 显示表的创建结构：desc table_name; 删除表：drop table table_name; 删除其他数据库的表：drop table 数据库名.表名; 往表中插入数据： 一次插入一行：insert [into] 表名([列名]) values(值列表）; 如果表名后面没有写列（段）名，默认向表中所有列赋值，另外字符串要用”“和”括起来 赋值的数据类型必须要与表中的相互匹配，具有缺省值的列可以使用default来代替插入的值 查看数据:select * from 表名;

例：select * from student; 一次插入多行:

insert [into] 表名([列名]) values(值列表）,（值列表）,......;
例：insert into student values(3,'dd','女'),(4,'vv','男'); 

从其他表中引用要插入的数据：

insert [into] 表名([列名]) select <列名> from 源表名[where ...];
create table student1(id int,name varchar(20),sex varchar(10),age int)default charset=utf8;
insert into student1 values(1,"张无忌","男",26),(2,"段誉","男",27);
insert into student select id,name,sex from student1 where id=2;
select * from student;

更改数据库中表的记录：

uodate 表名 set<列名=更新值>[where <更新条件>];
// where 子句是用来限定更新条件的，不带的话更新整列
 例：
        update student1 set sex='女';
        update student1 set sex='男' where id=1;
        update student1 set sex='男',age=22 where id=2;

删除数据表中的记录：

delete from 表名[where <删除条件>];
//不带where会删除表中所有数据，但是表不会被删除
例：
 delete from student1 where age<26;

查询表中数据:

select <列名> from 表名[where <查询条件表达式>][order by<排序的列名>]{asc或者desc]
//asc:升序(默认)
//desc:降序
例：
  select * from student;
  select id ,name from student;
  select * from student where id<=3 order by id;          
  select * from student  where id<=3 order by id desc;

更改表的结构： 为表添加一列：如果没有指定位置默认在列尾添加

alter table 表名 add 列名 建表语句[after 列名];
alter table sutdent add money int after id;

删除一列:

alter table 表名 drop 列名;
alter table student drop age;

更改指定列的默认值：

alter table 表名 alter 列名 set default 默认值;

更改列名和类型：

alter table 表名 change 旧列名 新列名<建表语句>[after 列名];
//当旧列名和新列名相同时，可以改变该列名的类型，不相同时，在改变列名的同时改变类型

为表设置一个主键：即把某一列设置为主键，主键列的每一个成员都是互不相同的，它存在的目的就是唯一的标志一行

alter table 表名 add primary key(列名);
//如果已经存在其他主键，再设置就会出错，设置为主键的一行not null;

把主键设置为自增长

alter table 表名 change 旧列名 新列名 类型 auto_increment;
//设置为自增长的主键，非空，且是主键列，不能省略，不包含（0）；
例：
  alter table student change num num int auto_increment;

从特定的位置设置自增长：否则删除一个记录之后再添加会出现断层

alter table 表名 auto_increment=初值;

删除主键：

alter table 表名 drop primary key;
//删除主键之前要删除自增长才能成功
例：alter table table_name change new_type;
例：alter table student drop primary key;

更改表名：

alter table table_name rename as new_table_name;
例：alter table student rename as student2;

例子：
创建一个数据表：

  create table student3(
    -> code int not null auto_increment,
    -> name varchar(20) not null,
    -> age int,
    -> sex varchar(10),
    -> score int,
    -> grade int,
    -> address varchar(20) default '未知',
    -> major varchar(20),
    -> primary key(code))default charset=utf8;

 insert into student3(name,age,sex,score,grade,address,major) 
    -> values('xxx',13,'m',78,1,'asdfgh','sss'),
    -> ('xcc',15,'w',88,2,'afdfdh','nnn'),
    -> ('cxx',9,'m',99,3,'sdsvfbf','vvv'),
    -> ('ccc',45,'m',56,1,'cdfsff','sss'),
    -> ('xbb',34,'w',66,1,'cdfgdv','sss');
查看全体学生的详细信息：
  select * from student3;

查询全体学生的学号和姓名：
select code,name from student3;

字段计算：
select code,age+score from student3;
select code,age+score as xxx from student3;

查询全体学生的学号和姓名，用中文显示列名：
select code as '学号',name as '姓名' from student3;

给表设置别名：
select s.code,s.name from student3 as s;

查询所有年级号(去掉重复的年级号)
select distinct grade from student3;

查询年龄在20及以下的学生的姓名
select name from student3 where age<20;

查询全体学生的所有信息并按年龄的降序排列；
select * from student3 order by age desc;

查询年龄最大的前3个学生的所有信息。
select * from student3 order by age desc limit 3;

查询年龄最大的第4个，5个学生的所有信息：
select * from student3 order by age desc limit 3,2; 

concat:
select concat(name,'is ',age,' years old') from student3 order by age desc; 

计算总数：count(列名）

select count(*) from table_name;

计算一班的平均成绩：

select avg(socre) from where grade=1;

查找专业的最高分和最低分：

select max(score) as '最高分',min(score) as ' 最低分' from student3;

查找专业的平均成绩在70以上的专业

select major,avg(score) as '平均成绩' from student3 group by major having avg(score)>70;

求总和：

select sum(score) from stduent3;

查询年龄[不]20-50之间的学生个人信息

select * from student3 where age[not] between 20 and 50;
select * from where code like 21;

查询名字以’a’开头的所有学生信息;

select * from student3 where name like 'a%';
select * from steudent3 where name like '%a';
select * from student where name like '%a%';
show tables like '%s%';

//年级升序，同一年级按照分数升序排序
select * from student3 order by grade,score desc;

多表查询：

select s.id,s.name,s.age,c.course,c.score from stu as s inner join cour as c on s.id=c.num;

拼接：

select concat(name,'is','age,'years old')from student3 order by age;

内连接（inner join)

select s.id,s.name,s.age,c.course,c.score from stu as s inner join cour as c on s.id=c.num;

左外连接（left join):以左表为主(左表有的全部都有）

select s.id,s.name,s.age,c.course,c.score form stu as s left join cour as c on s.id=c.num;

右外连接（right join):

select s.id,s.name,s.age,c.course,c.score form stu as s right join cour as c on s.id=c.num;

子查询：将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为子查询
注：一个select-from-where 语句称为一个查询块
例：
select name,age from stu where id in(select num from cour where course=’语文’);

复制表：

1.复制一张已知的数据表：

create table 新表名　select * from 已知表; 

2.在复制表时限制表中的内容：

create table 新表名select [列名...] from 已知表;

3.创建一个已存在的表的空表

create table 新表名　select * from 已知表　where 0=1;