MySQL创建和操作数据库表demo

【1】建立员工档案表
要求字段：员工员工编号，员工姓名，性别，工资，email，入职时间，部门。

【2】合理选择数据类型及字段修饰符，要求有NOT NULL，auto_increment, primary key等。

DROP TABLE IF EXISTS `workers_info`;
CREATE TABLE `workers_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`workername` varchar(20) NOT NULL,
`sex` enum(F,M,S),
`salary` int(11) DEFAULT '0',
`email` varchar(30),
`EmployedDates` date,
`department` varchar(30),
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql> alter table workers_info ADD sex enum('F','M','S');

【3】查看表的结构

  mysql> desc  workers_info;

【4】新增qq和tel字段，要求tel字段位于email前，要求入职时间是最后一个字段------添加字段

mysql> ALTER TABLE workers_info ADD tel varchar(15) after salary;

mysql> ALTER TABLE workers_info ADD qq int;
ALTER TABLE workers_info MODIFY EmployedDates date after qq;  

alter table bops_bad_word add COLUMN `isDelete` enum('Y','N') DEFAULT 'N' COMMENT '是否删除(Y为已删除/N或空为正常)';

【5】把email字段修改成mailbox------修改字段名

 mysql> ALTER TABLE workers_info CHANGE email mailbox varchar(30);

【6】向表里添加10条记录------添加数据

mysql> INSERT INTO workers_info (workername,salary,tel,mailbox,department,qq,EmployedDates) values('xing',10000,'1598232123','xing@qq.com','yanfa',736019646,20121221);

【7】修改其中两条记录的tel和mailbox------修改数据

mysql> UPDATE workers_info SET mailbox = 'haha@qq.com',tel='1246543423' WHERE id = 13;

【8】查看所添加记录------查询数据

mysql> select * from workers_info;

【9】查看姓名和入职时间记录

mysql> select workername,EmployedDates from workers_info;

【10】查询入职时间在2003年以前的------日期筛选

mysql> select * from workers_info where year(EmployedDates) < 2003;

【11】查询工资最高和最低的员工姓名------排序

mysql> select * from workers_info ORDER BY salary limit 1;
mysql> select * from workers_info ORDER BY salary desc limit 1;

【12】查询平均工资------平均函数

mysql> select avg(salary) from workers_info;

【13】统计男员工人数、女员工人数------总和函数

mysql> select count(1) from workers_info where sex="M";

【14】按照入职时间先后进行排序，并显示前5位员工姓名

mysql> select * from workers_info ORDER BY EmployedDates limit 5;

版权出自：http://blog.csdn.net/kakane/article/details/7401111

----------

Demo：

1、最简单的：

CREATE TABLE t1(
   id int not null,
   name char(20)
);

2、带主键的：

CREATE TABLE t1(
   id int not null primary key,
   name char(20)
);

　 复合主键：

CREATE TABLE t1(
   id int not null,
   name char(20),
   primary key (id,name)
);

3、带默认值的：

CREATE TABLE t1(
   id int not null default 0 primary key,
   name char(20) default '1'
);

转帖：

CREATE TABLE PLAYERS
       (PLAYERNO      INTEGER NOT NULL PRIMARY KEY,
        NAME          CHAR(15) NOT NULL,
        INITIALS      CHAR(3) NOT NULL,
        BIRTH_DATE    DATE,
        SEX           CHAR(1) NOT NULL
                     CHECK(SEX IN ('M','F')),
        JOINED        SMALLINT NOT NULL
                     CHECK(JOINED > 1969) ,
        STREET        CHAR(30) NOT NULL,
        HOUSENO       CHAR(4),
        POSTCODE      CHAR(6) CHECK(POSTCODE LIKE '______'),
        TOWN          CHAR(10) NOT NULL,
        PHONENO       CHAR(13),
        LEAGUENO      CHAR(4))
;

CREATE TABLE TEAMS
       (TEAMNO        INTEGER NOT NULL PRIMARY KEY,
        PLAYERNO      INTEGER NOT NULL,
        DIVISION      CHAR(6)   NOT NULL
                     CHECK(DIVISION IN ('first','second')),
        FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;

CREATE TABLE MATCHES
       (MATCHNO       INTEGER NOT NULL PRIMARY KEY,
        TEAMNO        INTEGER NOT NULL,
        PLAYERNO      INTEGER NOT NULL,
        WON           SMALLINT NOT NULL
                     CHECK(WON BETWEEN 0 AND 3),
        LOST          SMALLINT NOT NULL
                     CHECK(LOST BETWEEN 0 AND 3),
        FOREIGN KEY (TEAMNO) REFERENCES TEAMS (TEAMNO),
        FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;

CREATE TABLE PENALTIES
       (PAYMENTNO     INTEGER NOT NULL PRIMARY KEY,
        PLAYERNO      INTEGER NOT NULL,
        PAYMENT_DATE DATE NOT NULL
                     CHECK(PAYMENT_DATE >= DATE('1969-12-31')),
        AMOUNT        DECIMAL(7,2)   NOT NULL
                     CHECK (AMOUNT > 0),
        FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;

CREATE TABLE COMMITTEE_MEMBERS
       (PLAYERNO      INTEGER NOT NULL,
        BEGIN_DATE    DATE NOT NULL,
        END_DATE      DATE,
        POSITION      CHAR(20),
        PRIMARY KEY   (PLAYERNO, BEGIN_DATE),
        FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS (PLAYERNO),
        CHECK(BEGIN_DATE < END_DATE),
        CHECK(BEGIN_DATE >= DATE('1990-01-01')))

;

mysql修改字段注释方法：

来源：http://blog.sina.com.cn/s/blog_72aace390102uwgg.html

1. 问题

     已经有很多数据的按照业务逻辑分表的一系列表修改一个字段(类型，如-1：默认值，1：表示'人员id'，2：表示‘公司id’)的注释

 

2. 解决方法

 

     1> 使用alter table方法

ALTER TABLE table_name  MODIFY COLUMN column_name TINYINT(tinyint表示column类型)COMMENT '-1：默认值，1：人员id，2：公司id';

这种方法会重建表，如果数据量大，一个表100w数据，执行1min，如果分表数30个那就总共需要执行30min

 

     2> 使用元数据表

USE information_schema;
UPDATE COLUMNS t  SET t.column_comment  = '-1：默认值，1：人员id，2：公司id'  WHEREt.TABLE_SCHEMA='database_name' AND t.table_name='table_name'  ANDt.COLUMN_NAME='column_name ';

 查询语句,更新前可以查询一下

SELECT t.column_comment FROM COLUMNS t WHERE t.TABLE_SCHEMA='database_name' ANDt.TABLE_NAME='table_name' AND t.COLUMN_NAME='column_name ';

本想这种修改元数据的方法可以修改表的字段注释，但是没有权限执行，错误如下：

错误代码： 1044

Access denied for user 'usr'@'%' to database 'information_schema'

咦？这是为毛呢？？？!!!

想法很丰满，现实很骨感。

官方解释：
INFORMATION_SCHEMA是信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。
每位MySQL用户均有权访问这些表，但仅限于表中的特定行，在这类行中含有用户具有恰当访问权限的对象。
事实上，尽管不需要生成名为INFORMATION_SCHEMA的文件，我们仍提供了名为INFORMATION_SCHEMA的新数据库。可以使用USE语句将INFORMATION_SCHEMA选择为默认数据库，但访问该数据库中所含表的唯一方式是使用SELECT语句。不能在其中插入内容，不能更新它们，也不能删除其中的内容。

更多信息请戳链接：

http://dev.mysql.com/doc/refman/5.1/zh/information-schema.html

     

网摘：

Mysql没有Oracle那么方便，修改注释的时候需要指定{type}（这是Mysql设计的不合理性！）；系统数据字典表COLUMNS中有保存所有表的字段信息，但是系统字典表是只读的，无法修改注释；
所以，Mysql修改注释必须按照上面的格式对不同类型做判断，并且带有长度的字段必须指明length与decimals，否则更新注释的时候会修改length,decimals为默认值的；
修改Mysql注释是件麻烦的事情，但是也只能这样了

 

    3> 难道就没有别的方法了?

Are you kidding？！如果没有别的办法了我会写3！！！

当对于一个大表进行ALTER TABLE的时候，性能问题就产生了。MySQL大部分改动的步骤如下：根据新的表结构创建一个空表，从旧表中把数据取出来插入到新表中，在删除旧表。这是个非常漫长的过程。许多人ALTER TABLE之后，都有等待1小时或者1天的痛苦经历。

你可以这样，新建一个表，改变那个列的comment，然后原始实例关闭，替换.frm文件，再重启。

详情请咨询dba，这可是个技术活，我等rd表示一头雾水。可以参考

http://liudaoru.iteye.com/blog/616253

 

      4> 我等RD难道会因为改不了column注释就此放弃吗？

R U kidding again？！！

妥协方案，修改你的程序DB实体注释，大部分实体注释和DB注释一致，但是如果遇到频繁增加type类型这种情况。。。还是跟DB实体加注释吧，或者如果有枚举类型，枚举里描述清楚，这样起码让后来人能够不那么迷茫

 

吐槽：mysql真傻，真的...

 

SQL定时任务：

--- 新增自动收货天数字段
alter table `csc_order`.`order_current` drop column `automatic_receipt`,
add column `automaticReceipt` int(11) DEFAULT '15' NOT NULL COMMENT '发货后自动收货天数（默认15天，可延迟收货）' after `source`;

--- 新增自动收货定时任务
INSERT INTO csc_appframework.task(`projectId`, `taskName`, `taskCron`, `taskType`, `taskAddress`, `taskTime`, `addDate`, `modifyDate`, `status`)
VALUES('19','自动确认收货','0 0 0-23 * * ?','0','http://192.168.0.134:9108/order-app/order/automaticReceipt','0',NOW(),NOW(),'0');