代码:
use data2;
create table t_1(
id int not null auto_increment,
name varchar(20) not null,
grade char(4),
age float default 13,
ruxueriqi datetime,
chinese decimal(50,2),
math decimal(50,3),
primary key (id)
);
注释:字段age使用了默认值,默认值为13
使用SQLyog查看库data2中的表t_1的表结构:
Use data2;
SELECT name AS 姓名,DATE(ruxueriqi) AS 入学日期 FROM t_1 WHERE DATE(ruxueriqi) BETWEEN '2016-01-01' AND '2017-11-05' ORDER BY DATE(ruxueriqi) DESC;
注释:
AND 而且
不等于的写法:
Use data2;
SELECT name AS Name,grade AS 班级 FROM t_1 WHERE name != '张大白';
或
SELECT name AS Name,grade AS 班级 FROM t_1 WHERE name <> '张大白';
更改记录:(可以一次性更改同一条记录中的多个字段的内容)
Use data2;
UPDATE t_1 SET age = 14,ruxueriqi = '2018-2-15 12:12:2' WHERE name = 'Zhuohua';
SELECT name,age,ruxueriqi FROM t_1 WHERE name = 'zhuohua';
注释:TIME()函数作为关键字时,它的值要写完整。
给字段的值加大:(数据实际上更改了)
Use data2;
UPDATE t_1 SET math = math + 10 WHERE grade = '三年二班';
SELECT name,grade,math FROM t_1 WHERE grade = '三年二班';
######
######
使用SQLyog,将表data2.t_1中字段math的值大于或等于77的记录复制到新表data1.t_2:
先复制表结构:
CREATE TABLE data2.t_2 SELECT * FROM data2.t_1 WHERE 1=2;
再复制表数据:(不要复制字段“id”)
INSERT INTO data2.t_2 (name,grade,age,ruxueriqi,chinese,math) SELECT name,grade,age,ruxueriqi,chinese,math FROM data2.t_1 WHERE math >= 77;
上面两条代码执行成功后,查看表data2.t_2的表数据:
SELECT * FROM data2.t_2;
最后重建表data2.t_2的字段“id”:
ALTER TABLE data2.t_2 DROP COLUMN id;
ALTER TABLE data2.t_2 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY(`id`);
上面两条代码执行成功后,再查看表data2.t_2的表数据:
SELECT * FROM data2.t_2;