MySQL创建和操作数据库表demo

mac2022-06-30 64

【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');

转载于:https://www.cnblogs.com/xiaowenwu/p/5063654.html

最新回复(0)