操作数据表

1. 创建 / 删除数据表

   -- 创建表
   -- create table < 表名 > ;
   create table test_table;
   
   -- 示例
   create table offices
   (
   officeCode int(10) primary key not null,
   city varchar(50) not null,
   address varchar(50),
   country varchar(50) not null,
   postalCode varchar(15)
   );
   
   
   -- 删除表
   -- drop table [ if exists ] < 表名 > <表1> <表2> <表n>;
   -- if exists 判断表是否存在，如果不存在可正常执行，但会提示warning
   drop table test_table;
   
   -- 在数据之间存在外键关联情况，直接删除父表会显示失败，原因是破坏表的参照性，但先删除子表再删除父表则删除了两个表的数据了，但是现在需要删除父表保留子表数据，则需要先删除表的外键即可
   

2. 查看数据表

   use test_db ;
   show tables ;  -- 查看该库下所有表
   desc < 表名 > ;  -- 查看表字段信息
   show create table < 表名 >\G -- 查看建表信息
   

3. 使用主键约束
   要求主键的数据唯一且不允许为空

   -- 字段名 数据类型 primary key
   create table test_table
   (
   id int(11) primary key,
   name varchar(25),
   phone int(11),
   sex varchar(2)
   );
   

4. 外键
   外键用来再两个表之间建立连接，一个表的外键可以为空值，若不为空值，则每个外键值必须等于另一个表中主键的某个值，定义外键后，不允许删除在另一个表中具有关联关系的行

   -- 创建外键
   -- constraint < 外键名 > foreign key( < 字段1 >  [< 字段2 >] ) references < 表名 >( < 主键1 >  [<主键2>] ) ;
   create table waijian_table
   (
   id int(11) primary key,
   abc int(4),
   monster_type varchar(10),
   constraint kf_name_is_kf1 foreign key (abc) references test_table(id)
   );
   
   create table employess
   (
   employeeNumber int(11) primary key not null auto_increment,
   lastName varchar(50) not null,
   firstName varchar(50) not null,
   mobile varchar(25),
   officeCode int(10) not null,
   jobTitle varchar(50) not null,
   birth datetime,
   note varchar(255),
   sex varchar(5),
   constraint office_fk foreign key (officeCode) references offices(officeCode)
   );
       
   -- 删除表的外键约束
   -- alter table < 表名 > drop foreign key < 外键名 > ;
   
   

5. 使用非空约束

   -- 字段名 数据类型 not null
   create table test_table
   (
   id int(11) primary key,
   name varchar(25) not null,
   phone int(11),
   sex varchar(2)
   );
   

6. 使用唯一约束
   允许为空，但只能出现一个空值
   unique 和 primary key 区别：一个表中可以有多个字段声明为 unique ，但只能有一个 primary key声明，声明为 primary key不允许有空值，unique允许空值存在

   -- 字段名 数据类型 unique
   create table test_table
   (
   id int(11) primary key,
   name varchar(25) unique,
   phone int(11),
   sex varchar(2)
   );
   

7. 使用默认约束

   -- 字段名 数据类型 default
   create table test_table
   (
   id int(11) primary key,
   name varchar(25) unique,
   phone int(11),
   sex varchar(2) default man
   );
   

8. 设置表的值自动增加

   -- 字段名 数据类型 auto_increment
   create table test_table
   (
   id int(11) primary key auto_increment,
   name varchar(25) unique,
   phone int(11),
   sex varchar(2) default man
   );
   

9. 修改表名

   -- alter table < 表名 > rename < 新表名 > ;
   alter table test_table1 rename test_table2;
   

10. 修改字段的数据类型

    -- alter table < 表名 > modify < 字段名 >  < 数据类型 > ;
    alter table test_table1 modify phone varchar(15);
    

11. 修改字段名

    -- alter table < 表名 > change < 旧字段名 >  < 新字段名 >  < 新数据类型 > ;
    alter table test_table1 change phone nationality varchar(10);
    

12. 添加字段

    -- alter table < 表名 > add < 新字段名 > < 数据类型 > < 约束条件 > < first | after 已存在字段名 > ;
    alter table test_table1 add phone  int(11) not null;
    -- 在表的第一列添加字段
    alter table test_table1 add phone  int(11) not null first;
    -- 在表的指定列后添加字段
    alter table test_table1 add phone  int(11) not null after name;
    

13. 删除字段

    --  alter table < 表名 > drop < 字段名 > ;
    alter table test_table1 drop phone;
    

14. 基本查询语句

select
	{ * | <字段列表> }
	[
		from <表1>,<表2>...
		[where <表达式>
		[group by <group by definition>]
		[having <expression> [{<operator> <expression>}...]]
		[order by <order by definition>]
		[limit [<offset>,] <row count>]
	]
select [字段1，字段2,...,字段n]
from [表或视图]
where [查询条件];

{ *| <字段列表> }	表示查询字段，多个字段间用逗号隔开
from <表1>,<表2>…	表示查询来源为表1表2，多个用逗号隔开
where	可选项，表示限定查询条件
group by <字段>	表示按指定的字段分组显示
order by <字段>	表示按什么顺序显示查询数据
limit [<offset>,] <row count>	显示查询出来的数据条数

such as：

mysql> create table fruits
    -> (
    -> f_id char(10) not null,
    -> s_id int not null,
    -> f_name char(255) not null,
    -> f_price decimal(8,2) not null,
    -> primary key (f_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into fruits (f_id,s_id,f_name,f_price) values
    -> ('a1',101,'apple',5.2),
    -> ('b1',101,'blackberry',10.2),
    -> ('bs1',102,'orange',11.9),
    -> ('bs2',105,'melon',8.2),
    -> ('t1',102,'banana',10.3),
    -> ('t2',102,'grape',5.3),
    -> ('o2',103,'coconut',9.8),
    -> ('c0',101,'cherry',3.2),
    -> ('a2',102,'apricot',2.2),
    -> ('l2',104,'lemon',6.4),
    -> ('b2',104,'berry',7.6),
    -> ('m1',106,'mango',15.7),
    -> ('m2',105,'xbabay',2.6),
    -> ('t4',107,'xbababa',3.6),
    -> ('m3',105,'abcd',11.8),
    -> ('b5',107,'zohar',9.1);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

-- 消除重复值 distinct
mysql> select distinct s_id from fruits;
+------+
| s_id |
+------+
|  101 |
|  102 |
|  104 |
|  107 |
|  105 |
|  106 |
|  103 |
+------+
7 rows in set (0.00 sec)

-- 对查询结果进行排序 order by
-- 先按 s_id 降序排序 再按 f_price 升序排序
mysql> select * from fruits where f_price < 10 order by s_id DESC,f_price;
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| c0   |  101 | cherry  |    3.20 |
| a1   |  101 | apple   |    5.20 |
| a2   |  102 | apricot |    2.20 |
| t2   |  102 | grape   |    5.30 |
| o2   |  103 | coconut |    9.80 |
| l2   |  104 | lemon   |    6.40 |
| b2   |  104 | berry   |    7.60 |
| m2   |  105 | xbabay  |    2.60 |
| bs2  |  105 | melon   |    8.20 |
| t4   |  107 | xbababa |    3.60 |
| b5   |  107 | zohar   |    9.10 |
+------+------+---------+---------+
11 rows in set (0.00 sec)