mysql-数据表操作

{}：必须有的选项，|：选择，[]：可选项

1.    数据类型：

 整型：tinyint（1个字节）smallint（2个字节）mediumint（3个字节）int（4个字节），bigint（8个字节）

 浮点型：float，double

 日期：year、time、date、datetime、timestamp

 字符型：char（定长），varchar（变长），tinytext，text，mediumtext，longtext，emum，set

2.    AUTO_INCREMENT

 自动编号，必须与主键一起使用，默认起始值为1，增量为1

3.创建数据表

 CREATE TABLE [ IF NOT EXISTS]table_name (

       column_namedata_type,

 )

 例子：

 create table tb1(

      id smallint unsigned auto_increment primary key,

  username varchar(20) not null unique key,

  age tinyint unsigned,

  salary float(8,2),

  sex enum(‘1’,’2’,’3’) default ‘3’,

 )

        create table goods_brand(

       id smallint unsigned auto_increment primarykey,

                     brand_name varchar(20) not null

 ) select brand_name from tdb_goods group by brand_name; 

4.      查看数据表

 SHOE TABLES [FROM db_name]

 例子：

 show tables;

 删除数据表

 DROP TABLEname[, ...] [ CASCADE | RESTRICT ]

CASCADE 自动删除依赖于表的对象。（比如视图）。  

  RESTRICT 如果存在依赖对象，则拒绝删除该表。这个是缺省。

5.      修改命令结束符为//

 delimiter //

6.      查看数据表结构

 SHOW COLUMNS FROM tb_name;

 DESC tb_name；

7. 插入记录

 INSERT[INTO] tb_name [(col_na,e,…)] VALUES (val,…)

 例子：

        insert tb1values ('tom',12,4500);

       insert tb1 (username,age) values ('lily',12);

8.      更新列

 添加单列

        ALTER TABLE tbl_name ADD [COLUMN] col_name colum_definition [FIRST|AFTER col_name]

        添加多列

  ALTER TABLE tbl_name ADD [COLUMN] (col_name colum_definition,…)

 删除列

  ALTER TABLE tbl_name DROP [COLUMN] col_name

 例子：

        alter table user add age tinyint unsigned not null default 10 ；

        alter table user drop age；

        alter tableuser drop age，drop password;

9.      修改列定义

 修改数据类型或位置

  ALTER TABLEtbl_name MODIFY [COLUMN] col_name column_definition [FIRST| AFTER col_name]

 修改列名称

  ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST| AFTER col_name]

 例子：

        alter tableuser modify id smallint first;

10.      数据表更名

 RENAME TABLE tbl_name TO new _tbl_name [,tbl_name2 TO new_tbl_name2]…

 ALTER TABLE tbl_nameRENAME [TO|AS] new_tbl_name

 例子：

        Alter table user rename user2;

11.   插入记录：

 INSERT[INTO] tbl_name [(col_name,…)] {VALUES | VALUE} ({expr|DEFAULT}),(…)

 INSERT[INTO] tbl_name SET col_name={expr|DEFAULT},…

 INSERT[INTO] tbl_name [(col_name,…)] SELECT…

 例子：

     给递增字段复制可以是null，或者default

        insert users values (null.’tom’,’123’);

 insert users values (default.’tom’,’123’);

 insert tb1 (username,age) values ('lily',12);

        insert users set username=’tom’,password=’123’;

 insert users (username) select username from test where age>30;

 12. 更新(单表)：

  UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={ expr| DEFAULT },[col_name2={ expr|DEFAULT }]…  [WHERE where_condition]

  例子：

   update users set age = age + 5;

   update users set age = age – id,sex=0;

   update users set age = age + 10 where id % 2 = 0;

         多表更新

                例子：

                     Update tdb_goods inner join tdb_good_cates on goods_cate = cate_name set goods_cate =cate_id;

 13.  删除记录（单表）：

              DELETE FROM tbl_name [WHERE where_condition]

              例子：

                     delete from users where is =6;

              多表：

              deletet1 from tdb_goods t1 left join(select goods_id,goods_name from tdb_goods groupby goods_name hiving count(goods_name)>=2) t2 on t1.goods_name =t2.goods_name where t1.goods_id > t2.goods_id;

   14. 查找：

  SELECT select_expr[.select_expr…] [

                    FROM table_references

                    [WHERE where_reference]

                    [GROUP BY {col_name|position} [ASC|DESC],…]

                    [HAVING where_condition]

                    [ORDER BY {col_name| expr|position} [ASC|DESC],…]

                    [LIMIT {[offset] row_count | row_count OFFSET offset}]

     ]

  条件表达式可以支持mysql的函数和表达式

              例子：

                     select now();

                     select * from users;

                     select id,username from users;

                     为字段起别名

                     select username as name from users;

                     select sex from users group by sex;

                     使用having时，条件要么为聚合函数，要么字段必须在select中

                     select sex,age from users group by sex having age>35;

   select sex,age from users group by sex having count(id)>2;

  select * from users order by id desc;

  select * from users limit 2;返回2条记录

  select * from users limit 2,3

15.   子查询          

 使用比较运算符的子查询：

  select avg(goods_price) from tdb_goods

  保留2位小数

  select round(avg(goods_price),2) from tdb_goods;

  select goods_id,goods_name,goods_price from tdb_goods where goods_price >=( select round(avg(goods_price),2) from  tdb_goods);

  子查询返回多个结果时可以配合any，some，all使用，any与some等价

  select goods_id,goods_name,goods_price from tdb_goods where goods_price >any (select good_price from tdb_goods  where goods_cate=’笔记本’);

         使用[not] in的子查询       

16.   连接

 join 、left join、right join

 在mysql中，join、inner join、cross join是等价的

 内连接：仅显示符合连接条件的记录

       select goods_id,goods_name,cate_name from tdb_goods inner jointdb_goods_cates on tdb_goods.cate_id =tdb_goods_cates.cate_id

 左外连接：显示左表的全部和右表中符合条件的记录

 右外连接：显示右表中的全部和左表中符合条件的记录

select goods_id,goods_name,cate_name,brand_name from tdb_goods inner jointdb_goods_cates on tdb_goods.cate_id =tdb_goods_cates.cate_id inner jointdb_goods_brand on tdb_goods.brand_id = tdb_goods_brand.id