一、数据库

一、数据库操作: 

 1.1 创建数据库:   

 create database student; 

 1.2  删除数据库:   

 drop database student;

二、数据库表操作:

  2.1 创建数据库表   

 create table 表名;  

  create table class2 (name char(4),sex char(2),age int(3));  

  create table class3(id char(10) primary key,name char(3) not null,sex char(2),age int(4) default '20'); 

 primary key 主键 

 not null  不能为空

  Default  默认值  

 创建表时多列设为主键:

  create table test(adress char(20),id int(3),name char(10),primary key(id,name));   

   创建表时设置ID自增长:

  create table users ( id int(5) not null auto_increment, name varchar(20)not null, primary key (`id`) );

自增长插入数据:

mysql> insert into users values('','liming'),('','zhangsan');

Query OK, 2 rows affectedRecords: 2  Duplicates: 0  Warnings: 0  

2.2 查看表结构: 

 desc 表名;

  desc student;  

 2.3 查看有哪些表  

  show tables;  

 2.4 删除表:  

 drop table 表名; 

 2.5 修改表:   

 2.5.1 rename   

   语法结构:alter table 旧表名  rename 新表名;  

    alter table test rename class;   

 2.5.2对表结构修改   

   增加一列 add  

    alter table class add 地址 char(20);  

  2.5.3 删除一列 drop  

    alter table class drop 表结构;

    2.5.4 修改列 change   

   alter table 表名 change 姓名 name char(3); 

 三、表中数据的操作:

  3.1插入数据:  

  3.1.1 插入一条数据: 

 insert into 表名(id,name,sex,age) values(001,赵四,男,20);      insert into 表名 values(值1,值2...);-----values中的值必须和表的默认结构对应/    

  insert into class values('wuhuishan','女',20,'006');   

 3.1.2 一次性写入多条数据  

    insert into 表名(属性1,属性2..) values(值1.1,值2.1...),(值1.2,值2.2,....);     

 insert into class(id,name,sex,age) values('001','zhangsan','男',20),('002','lisi','男',21); 

   3.2 删除数据:  

  3.2.1 delete 语句delete from 表名;--一次性删除所有数据了  

  delete from 表名 where语句:  

  delete from class where name='lisi';   

 3.2.2 truncate语句  

  truncate 表名;---清空数据库表   

 truncate class;

 delete truncate 区别  清空数据库表时truncate 效率更高,delete可以跟条件语句而truncate不能  

  3.3 修改表中数据:   

   update  set    

  update 表名  set 需要更新的数据 where: 

     update class set id='002' where id='004';  

  3.4  查询语句:  

    3.4.1 简单查询语句    

  select * from 表名;---*代表查询所有列   

   select 需要查询的属性 from 表名 (where);     

     select address,name,birth from student;---查询指定的列address,name,birth   

   3.4.2 where语句查询:

  select * from student where birth=1990 or birth=1991;  ---查看生日是1990和1991的所有学生信息 

 select * from  student where birth!=1990;  ------查看生日不是1990的所有学生信息    

  select * from student where not birth=1990;  ------查看生日不是1990的所有学生信息    

  select goods_name from ecs_goods where is_delete=0 and is_new=1; ----查询goods表中没有被is_delete=0删除并且为is_new=1新品      

查询 goods表中的shop_price价格 大于4000,小于8000的产品信息      select goods_name,shop_price from ecs_goods where 40004000 and shop_price<8000;  ----正确写法     

 查询没有删除的产品中,要么是新品,要么是热销,要么精品      select goods_name from ecs_goods where is_delete=0 and (is_new=1 or is_hot=1 or is_best=1);     

 查询出点击量click_count超过10次,并且没有库存的产品信息      select click_count,goods_name,goods_number from ecs_goods where click_count>10 and goods_number=0;     

 查询出并且热销is_hot=1产品库存小于5   

   select goods_number,goods_name from ecs_goods where is_hot=1 and goods_number<5;      

 找出删除的产品哪些是热销,精品,新品   

   select goods_name from ecs_goods where is_delete=0 and (is_new=1 and  is_hot=1 and is_best=1);      


3.4.3 模糊查询:like     

   % 全匹配  

      _ 单个字符匹配     

   商品名称包含18k 的产品有哪些?    

    select goods_name from ecs_goods where goods_name like '%18K%' ;     

   select goods_name from ecs_goods where goods_name like '____18K_____' ;    

    查询产品名称包含18K的并且产品的库存为0的产品名称   

     select goods_name from ecs_goods where goods_name like '%18K%' and goods_number=0;    

    查询产品名称包含18K的并且产品促销中显示"国庆大促"     

   select goods_name,goods_brief from ecs_goods where goods_name like '%18K%' and goods_brief like '%国庆大促%'; 

 3.4.4 in 在..里面    

      查询产品编号为ECS000136,ECS000137,ECS000138的产品信息        

  select goods_sn from ecs_goods where goods_sn='ECS000136' or goods_sn='ECS000137' or goods_sn='ECS000138' ;---查询内容为字符串时必须用引号引起来   

       使用in时:      

    select goods_sn from ecs_goods where goods_sn in ('ECS000136','ECS000137','ECS000138');   

         查询出有订单的产品的所有信息(ecs_order_goods 订单表)          1、产品信息 在产品表中          

  select goods_name,goods_sn,goods_id from ecs_goods;          2、产品存在在订单表中         

   select goods_id from ecs_order_goods;         

   3、 综合查询:           

select goods_name,goods_sn,goods_id from ecs_goods where goods_id in (select goods_id from ecs_order_goods) ;   

   成绩大于80分的学生信息;    

      select * from student where grade in (select grade from student where grade>80);     

     3.4.5  between  and ----介于什么和什么之间    

      between 较小的数值 and 较大的数值;     

     select goods_name,shop_price from  ecs_goods where shop_price between 4000 and 8000;     

   3.4.6 算数运算符使用 + - * /  mod    

    查询出market_price,shop_price之和大于15000;

        select market_price,shop_price,market_price+shop_price,goods_name from ecs_goods where market_price+shop_price>15000;

查询出售价与超市价差价大于1000的商品信息:

select market_price,shop_price,market_price-shop_price,goods_name from ecs_goods where market_price-shop_price>1000;        

商品预售额大于100000的商品信息; 

       select goods_number,shop_price,goods_number*shop_price from ecs_goods where goods_number*shop_price>100000;     

   查询出产品的折扣大于8折    

    select shop_price/market_price from ecs_goods where shop_price/market_price<0.8;

mod 求余     

   select goods_number,goods_number mod 3 from ecs_goods where  (goods_number mod 3)=0;

select goods_number,goods_number mod 3 from ecs_goods where (goods_number mod 3) =0;

 select goods_number,goods_number mod 3 from ecs_goods;

3.4.7  排序  order by  排序 升序排序    

  select shop_price from ecs_goods order by shop_price;     

   order by decs  降序排序  

    select shop_price from ecs_goods order by shop_price desc;    3.4.8 查询空值   

   is null      查询出 is_check为null的数据    

  select * from ecs_goods where is_check is null;   

   3.4.9 函数     

 count 统计函数      统计ecs_goods总数   

  select count(goods_number) from ecs_goods;        

 sum 求和函数     

 select sum(goods_number) from ecs_goods;    

  avg 求平均值   

 select avg(shop_price) from ecs_goods;    

    max  最大值   

 select max(shop_price) from ecs_goods;    

  min 最小   

   select min(shop_price) from ecs_goods;       

   distinct  去重复    

  select distinct(goods_number) from ecs_goods; 

   3.4.10  group by  分组      

select goods_number,count(goods_number) from ecs_goods group by goods_number;    

    3.4.11 limit  用法  查询行数     

 查询2-4行   

   limit 1,3  ---第一个数指的是当前所在的行,第二数是让你显示的行数      limit 1,3 从当前第一行开始的下一行显示3行数据      如果要求显示前三行 limit0,3    

  显示价格最高的前三个    

  select shop_price from ecs_goods order by  shop_price desc limit0,3;   

   3.4.12 having 条件语句     

   select shop_price from ecs_goods where shop_price>10000;        select shop_price from ecs_goods having shop_price>10000;            having  进行分组以后只能用having   

     对产品的数量进行分组,显示同一数量的商品统计后大于两个的        select goods_number,count(goods_number) from ecs_goods group by goods_number  having count(goods_number)>2;     

 求出未删除的产品热销产品与非热销产品的数量大于2的   

     select is_hot,count(*) from ecs_goods where is_delete=0 group by is_hot  having count(*)>2  ;    

  3.4.13  子查询  in        any          some       

     要求查询student表中比任意一个class表中年龄大的人信息;          select 2017-birth from student where (2017-birth)(select avg(2017-birth) from student);

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)

SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)

SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog);

3.4.14 合并 union  合并

1、两个表列要一致

2、存在重复时去重;

select * from student union select * from student2;

select id from student union select id from class;

select id,name from test1 union select id,name from test2;

3.4.15 左右连接:

select ..from  表1 连接类型 join 表2  on 连接关系;

select * from student left join sc on student.id=sc.stu_id;

select * from test1 right join test2 on test1.id=test2.id;

3.4.16 多表连接:

找到表与表的对应关系

select 查询的列属性 from 表1 a1,表2 a2.. where 条件 表1.属性=表2.属性 and 表3.属性=表2.属性;

1、如果多张表中有同一个属性名时必须标注是哪个表中的属性,student.id ,s1.id;

2、表的别名表示法

mysql> select * from student;

+-----+--------+-----+-------+------------+--------------+

| id  | name  | sex | birth | department | address      |

+-----+--------+-----+-------+------------+--------------+

| 901 | 张老大 | 男  |  1985 | 计算机系  | 北京市海淀区 |

| 902 | 张老二 | 男  |  1986 | 中文系    | 北京市昌平区 |

| 903 | 张三  | 女  |  1990 | 中文系    | 湖南省永州市 |

| 904 | 李四  | 男  |  1990 | 英语系    | 辽宁省阜新市 |

| 905 | 王五  | 女  |  1991 | 英语系    | 福建省厦门市 |

| 906 | 王六  | 男  |  1988 | 计算机系  | 湖南省衡阳市 |

+-----+--------+-----+-------+------------+--------------+

mysql> select * from score;

+----+--------+--------+-------+

| id | stu_id | c_name | grade |

+----+--------+--------+-------+

|  1 |    901 | 计算机 |    98 |

|  2 |    901 | 英语  |    80 |

|  3 |    902 | 计算机 |    65 |

|  4 |    902 | 中文  |    88 |

|  5 |    903 | 中文  |    95 |

|  6 |    904 | 计算机 |    70 |

|  7 |    904 | 英语  |    92 |

|  8 |    905 | 英语  |    94 |

|  9 |    906 | 计算机 |    90 |

| 10 |    906 | 英语  |    85 |

+----+--------+--------+-------+

1、查询同时参加计算机和英语考试的学生的信息

a、select stu_id from score where stu_id in(select stu_id from score where c_name='计算机' ) and c_name='英语';

mysql>  select id,name,sex,birth,department,address from student where id in (select stu_id from score where stu_id in(select stu_id from score where c_name='计算机' ) and c_name='英语');

+-----+--------+-----+-------+------------+--------------+

| id  | name  | sex | birth | department | address      |

+-----+--------+-----+-------+------------+--------------+

| 901 | 张老大 | 男  |  1985 | 计算机系  | 北京市海淀区 |

| 904 | 李四  | 男  |  1990 | 英语系    | 辽宁省阜新市 |

| 906 | 王六  | 男  |  1988 | 计算机系  | 湖南省衡阳市 |

+-----+--------+-----+-------+------------+--------------+

mysql> select s1.id,name,sex,birth,department,address from student s1,score s2,score s3 where s1.id=s2.stu_id and s2.stu_id=s3.stu_id and s2.c_name='计算机' and s3.c_name='英语' ;

+-----+--------+-----+-------+------------+--------------+

| id  | name  | sex | birth | department | address      |

+-----+--------+-----+-------+------------+--------------+

| 901 | 张老大 | 男  |  1985 | 计算机系  | 北京市海淀区 |

| 904 | 李四  | 男  |  1990 | 英语系    | 辽宁省阜新市 |

| 906 | 王六  | 男  |  1988 | 计算机系  | 湖南省衡阳市 |

+-----+--------+-----+-------+------------+--------------+

3 rows in set

mysql> select s1.id,name,sex,birth,department,address from student s1,score s2,score s3 where s1.id=s2.stu_id and s2.stu_id=s3.stu_id and s2.c_name='计算机' and s2.c_name='英语' ;--------表s2和表s3中同时存在计算机和英语时不存在;

Empty set

4、索引:

1、分类

2、增加索引方法

3、索引优缺点

mysql>  alter table student add index cxy(name);

Query OK, 6 rows affected

Records: 6  Duplicates: 0  Warnings: 0

mysql> show index from student;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| student |          0 | PRIMARY  |            1 | id          | A        |          6 | NULL    | NULL  |      | BTREE      |        |

| student |          1 | cxy      |            1 | name        | A        | NULL        | NULL    | NULL  |      | BTREE      |        |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set

复制表:as 复制表并且复制内容  like只复制表结构

mysql> create table test3 like test2;

Query OK, 0 rows affected

mysql> show tables;

+-------------------+

| Tables_in_cxy23-2 |

+-------------------+

| class            |

| score            |

| student          |

| student2          |

| test1            |

| test2            |

| test3            |

+-------------------+

7 rows in set

mysql> select * from test3;

Empty set

mysql> desc test3;

+-------+-----------+------+-----+---------+-------+

| Field | Type      | Null | Key | Default | Extra |

+-------+-----------+------+-----+---------+-------+

| id    | int(11)  | NO  | PRI | NULL    |      |

| name  | char(255) | YES  |    | NULL    |      |

+-------+-----------+------+-----+---------+-------+

2 rows in set

mysql> create table test4 as select * from test2;

Query OK, 4 rows affected

Records: 4  Duplicates: 0  Warnings: 0

mysql> show tables;

+-------------------+

| Tables_in_cxy23-2 |

+-------------------+

| class            |

| score            |

| student          |

| student2          |

| test1            |

| test2            |

| test3            |

| test4            |

+-------------------+

8 rows in set

mysql> select * from test4;

+----+-------+

| id | name  |

+----+-------+

|  1 | user1 |

|  2 | user2 |

|  6 | user6 |

|  7 | user7 |

+----+-------+

4 rows in set

mysql>

mysql> select timeday,result,count(*) from aaaa group by timeday,result;

+------------+--------+----------+

| timeday    | result | count(*) |

+------------+--------+----------+

| 2005-05-09 | 胜    |        2 |

| 2005-05-09 | 负    |        2 |

| 2005-05-10 | 胜    |        1 |

| 2005-05-10 | 负    |        2 |

+------------+--------+----------+

4 rows in set

mysql> select * from aaaa;

+------------+--------+

| timeday    | result |

+------------+--------+

| 2005-05-09 | 胜    |

| 2005-05-09 | 负    |

| 2005-05-09 | 负    |

| 2005-05-09 | 胜    |

| 2005-05-10 | 胜    |

| 2005-05-10 | 负    |

| 2005-05-10 | 负    |

+------------+--------+

7 rows in set

推荐阅读更多精彩内容

  • 1.1、常用数据库包括:Oracle、MySQL、SQLServer、DB2、SyBase等 1.2、Navica...
    NOX_5d2b阅读 580评论 0 0
  • CREATE TABLE IF NOT EXISTS ecs_order_info (order_id mediu...
    cookie口阅读 9,767评论 0 15
  • mysql的查询、子查询及连接查询 一、mysql查询的五种子句 where(条件查询)、having(筛选)、g...
    时芥蓝阅读 300评论 0 3
  • 刚搬入现在的房子那会儿,迫不急待地跑到花市买花。也没有什么目标,就豪气对摊主说:“来几盆除味除甲醛的。”在摊主的介...
    柳二白阅读 219评论 2 4
  • 我进公司第一次见到的明星就是安妮前辈,安妮的个子不高,皮肤很白,很瘦,一看就是经常健身的结果,不像我瘦得只剩下硬邦...
    容安君阅读 65评论 0 1