【数据库系统原理】常用sql 操作

     最近查看了数据相关内容,基础知识,简单记录下。

一,数据库模式定义

1. create database  if not exists 或 create schedule 创建数据库

选择数据库:Use db_name

create database db_name if not exists

2. 选择DB    use db_name 从一个数据库跳转到另一个数据库

3.修改DB.     Alter  database/schema  db_ name

4.修改已有数据库默认字符集和校对规则

 alter database db_name  或者 alter schema  db_name 语句 ,来修改已经被创建的数据库的相关参数

5.删除DB

Drop database/schema  db_name

Drop database if exists db_name(如果不存在,就删除)

例子:drop database if exists mytest_db

二,表定义

1.创建表

 create table   tab_name  ( 字段名,数据类型[列级完整性约束条件][默认值])

create table customers
( cust_id  int  not null  auto_increment,
cust_name char(30) not null,
 cust_sex char(1) not null default 0,
cust_addres char(50) null,
primary key(cust_id))

     (1)临时表和持久表

                    create table,若添加可选项  tempopary 表示创建的为临时表

      (2) 数据类型,可以帮助正确的排序数据,并在优化磁盘使用方面起着重要作用。 因此在创建表时候,必须为每个表指定正确的数据类型和长度。

              数值类型( 整型int,浮点型 double,布尔型bool)

              日期和时间类型(日期 date,时间戳 timestamp)

              字符串类型(定长字符类型 char,变长字符类型 varchar)

              空间数据类型

     ( 3)关键字

     auto_increment 可为表中数据类型为整型的列设置为自增属性,顺序从1开始

      指定默认值, Null 值 没有值或者缺值, null  不能将null值和空串 混淆;null不是空串

      (4)主键 primary key 关键字指定主键

2.更新表   alter table 语句更改原有表结构

   (1) add column   向表中增加新列

              alter table db_name customers

              add column cust_city  char(10)  not null default '武汉' after cust_sex;

             通过关键字afer 之后,在原表 cust_sex 之后添加了字段 sust_city ,也可通过关键字 “first”添加到原表第一列。

             还可以通过add primary key子句,add Foreign key ,add index子句为原表添加一个主键,外键和索引。

     (2) change column 子句

        修改表中的名称和数据类型,可以在alter table中添加 change column 子句, 这个子句可同时修改表中指定的名称和数据类型。

        alter table db_name customers

         change column cust_sex  sex char(1) null default 'M';  将cu;st_sex 列重命名为sex,并且修改数据类型。

         更改数据类型,会丢失原有数据。

 (3) alter column

         修改删除指定列默认值

          alter table db_name customers

         alter column cust_city set default 'Beijing'

  (4)  modify column 子句

         只会修改列数据类型,不会干涉其列名

 (5)drop  column  子句

        删除数据库mysql_test 中表customers 的 cust_contact 列

        alter mysql_test customers

        drop column cust_contact

 (6)rename table  更改表的名字

        rename table  tabl_name to new_tabl_name 

         alter table mysql_test customers rename to mysql_test.backup_customers 将 customer 更改为 backup.customers

 3.  删除表

        若删除数据中已经存在的表,drop table  if exists;

 4.  查看表

       (1) 显示表的名称和显示表的结构两种情形

              show tables  显示指定数据库中存放的所有表名

              show  tables  [like pattern] 按照模糊条件查找某个表名      

        (2) 显示表的结构

         show columns  显示数据库表的结构

三,索引定义

   1.索引创建  

       (1) create index   index_name

                 create index  index_cust on mysql_test.customers (cust_name,cust_id) 根据cust_name,cust_id 创建组合索引

   2.查看已经创建的索引

           show index from mysql_test.customers

     3.索引删除

                drop index index_name on tabl_name

                index_name  索引名称 tabl_name 指定该索引在的表

                alter table  mysql_test customers.

                 Drop primarky key

                  drop index index_customers

四,数据更新

   1.插入数据 insert

    insert …..values 插入单行或多行数据

          insert into mysql_test customers values (901,'章三',“北京市”)

           insert into mysql_test customers (cust_id,cust_name,cust_sex,cust_address)

            values (0,'李四',default,'武汉市',Null)

          执行时候,最好给定列名,这样即使表结构发生了改变,insert语句仍能执行

  2.insert …….set

        直接给表中的某些列指定对应的列值,即要插入数据的列名在set子句中指定

       insert  into  mysql_test  customers  set cust.name ='栗四', cust.city='邯郸';

  3.insert…select 语句 插入子查询数据

            insert  into mysql_test customers (select * from **)

            select 从多个结果集中取出来数据,并且将这些数据作为行插入到另一个表中

2.修改数据

       update tabl_name set  cust.name ='小王', cust.city='河北' where cust.id =20

        set 子句用于指定列表中要修改的列名和列值

        update mysql_test customers set cust_address ='武汉市' where cust_name ='张三'

3.删除数据

           delete 语句删除一行或者多行数据

           delete from mysql_test customers [ where condition] [order by ] [ limit row count]

           delete form mysql_test coustomers where cust_name ='王五'

五,数据查询

      数据查询是sql语言的核心功能,也是使用最多的操作。ql查询数学理论基础关系是关系数据模型中对表对象的一组关系运算:

      数学理论: 选择,投影和连接

 having 子句必须位于group by 子句之后,并且位于order by 之前

   定义并且使用列的别名 AS子句

  

 替换查询结果集中的数据

 希望对某些列的查询分析结果,而不是由查询得到的原始具体数据。

Case 表达式
  case  
     where  条件一 then 表达式一
      where 条件二 then 表达式二
      Else 表达式
      End 
			
   select cust_name ,case where cast.sex='M' then '男' else '女' end as 性别 from   mysql_test customers;

  六,常用的聚合函数

       

(1)from子句 与多表连接查询

        连接方式,交叉连接,内连接,外连接 。交叉连接:笛卡尔积,cross join 连接两张表;

        交叉连接

             select * from. tb1 cross join tb2

              select * from tab1,tab2

          返回结果集行数=连接的两张记录行数的乘积。缺点:如果两个表中记录行数很大,那么查询结果就会非常大

         内连接

              内连接是系统默认的连接方法,所以可以省略inner,只用关键字join连接表;

              关于内连接的使用,通常有以下三种情形:

                 (1)等值连接  “===”等号连接 (2)非等值连接,使用非等号之外的其他比较运算符 (3) 自连接

          外连接,一个表与他自身连接

           外连接

                  将连接的两张表分为基表和参考表,基为基础返回满足和不满足的条件 。

         左外连接  left outer  join  或者left joni 连接,null 表示右表中没有找到左表相符的记录

         右外连接  right outer joni 或者 reght join 连接表,

(2) where子句与条件查询

      1. 判定范围

       between …. And  包含左右两个值 select * from mysql_test.customers where cust_id between 903 and 912

      2. in  范围值

     3. 判断为空

     select * from  mysql_test.customers  where  cust_contact is null ;

   4.子查询

    (1) 表子查询(2) 行子查询(3)列子查询(4) 标量子查询,子查询返回的结果集仅仅是一个值

        in 子查询,通常这里的子查询只能够返回一列数据;

        exist 判定子查询的结果集是否为空

       select studentNo,studentName form tab_student

        where studentNo in ( select studentNo from tb_score where score>80)

(3)group by 子句和分组数据

  group by 子句

       select  cust_adress,cust_sex, count(*) as '人数'  from myslq_test.customers group by cust_address,cust_sex;

 with rollup  会对group by 子句中所指定的各列再次生成汇总行。

  where 子句用于过滤数据行,having 子句过滤分组;

 having 子句条件可以包含聚合函数,where 中不可以;

  where在分组前过滤;having在分组后过滤分组

 (4)  order by 子句

(1)默认是asc,升序

 (2)对空值排序,order by 会将该空值作为最小值对待

 (3) order by 子句多一个列排序,mysql中会按照这些列从左往右所罗列的次序依次排序。

     select * from cust_name,cust_addres from mysql_test.customers group by cust.address having count(*)<3

(5)  limit语句

        SELECT * FROM area  a where a.id ORDER BY a.id LIMIT  5,3

七, 视图

  1,跟其他表的区别:

(1)不是数据库真实的表,是一张虚拟表

(2)视图的内容是由存储在数据中进行查询操作的sql语句来定义的,引用视图动态生成

  (3) 不是以数据集的形式存储在数据库中,对应的视图是由存储在视图中引用的基本表汇中

(4)用户查看存储在别处数据的一种虚拟表,自身不存储数据。

  2,优点:(1)集中分散数据 (2)简化查询语句 (3)重用sql (4) 保护数据安全,用户只授权用户使用视图权限,而不指定使用表权限,来保护数据安全性(5) 共享所需数据(6) 更改数据格式,通过重新格式化检索出的数据,并组织输出到其他应用程序中

 3, 创建

 create view view_name    as  select * from mysql_test.customers where cust_setx='M'

  drop view  if exists view_name

  alter view view_name   as select * from mysql_test.customers where cust_sex='M'

  show create view view_name 查看视图定义

4, 更新视图数据

     并不是所有的视图都能更新,只有满足条件的视图才可以进行更新,对于可更新视图,需要改视图的行和基本表之间具有

 一对一的关系。

小结:

     基本上常见的一些sql操作,数据库/表/列/视图/索引等常见的sql语句,总结下扎实下基础。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值