数据库常用sql语句和操作

时间：2021-11-27 00:00:10

数据库操作

标签（空格分隔）： 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;
标签：

相关文章