mysql如何插入datetime mysql添加时间

转载

mob64ca140a8e67 2023-09-21 08:51:50

文章标签 mysql如何插入datetime mysql 增加日期时分秒 mysql 外键 ide 文章分类 MySQL 数据库

mysql基础happy everyday

mysql如何插入datetime mysql添加时间_mysql如何插入datetime

1#远程连接数据库
 2mysql -u root -h 114.116.253.54 -p;
 3#创建数据库
 4use xysc
 5#查询数据
 6select * from user;
 7#创建表格，并指定主键
 8create table persion(Id int(10) primary key not null auto_increment,LastName varchar(25),FirstName varchar(40),Address varchar(100),City varchar(25));
 9#添加表格数据
10insert into persion values(1,"三","张","青海省","西宁市");
11insert into persion values(2,"tony","lucy","china","上海");
12#查看添加数据是否成功，避免重复值
13select distinct * from persion;
14#增加表格列名
15alter table persion add year datetime not null default 0;
16#删除表格列名
17alter tabke persion drop column year;
18#按条件查询
19select Id,FirstName from persion where City ="上海";
20#and和or结合
21select * from persion where FirstName="马" and (Address="西宁" or City="上海");
22#升序和降序
23select * from persion order by City ASC；
24select * from persion order by City DESC；
25#更新数据
26update persion set City="北京" where LastName="tony"; 
27#删除表中数据
28delete from persion where city="York";
 1#远程连接数据库
 2mysql -u root -h 114.116.253.54 -p;
 3#创建数据库
 4use xysc
 5#查询数据
 6select * from user;
 7#创建表格，并指定主键
 8create table persion(Id int(10) primary key not null auto_increment,LastName varchar(25),FirstName varchar(40),Address varchar(100),City varchar(25));
 9#添加表格数据
10insert into persion values(1,"三","张","青海省","西宁市");
11insert into persion values(2,"tony","lucy","china","上海");
12#查看添加数据是否成功，避免重复值
13select distinct * from persion;
14#增加表格列名
15alter table persion add year datetime not null default 0;
16#删除表格列名
17alter tabke persion drop column year;
18#按条件查询
19select Id,FirstName from persion where City ="上海";
20#and和or结合
21select * from persion where FirstName="马" and (Address="西宁" or City="上海");
22#升序和降序
23select * from persion order by City ASC；
24select * from persion order by City DESC；
25#更新数据
26update persion set City="北京" where LastName="tony"; 
27#删除表中数据
28delete from persion where city="York";

mysql如何插入datetime mysql添加时间_mysql 增加日期时分秒_02

mysql进阶

1#top子句(limit中第一个2是第n+1条数据，第二个2是返回条数)
 2select * from persion limit 2,2;
 3#like进行模糊查询
 4select * from persion where City like "%宁%";
 5#通配符_
 6select * from persion where City like "_宁%";
 7#in子句
 8select distinct Id,FirstName from persion where City IN ("西宁市","兰州市","郑州市","北京市","上海市");
 9#betweent and 子句
10select * from persion where Id between 2 and 5;
11#别名
12select a.LastName,a.FirstName from persion a ;
13#join子句
14select a.name,a.job,a.addr,b.text from ptuser a inner join idea b where a.id=b.ptid;
15# left join子句中即使右表没有匹配项也会输出，right join子句中即使左表中没有匹配项也会返回行，full join子句中即使左右表中没有匹配项也会输出所有行
16select a.name,a.job,a.addr,b.text from ptuser a right(left) join idea b on a.id=b.ptid;
17#union并操作，允许出现重复union all,不允许出现重复用union
18select a.name,a.job,a.addr,b.text from ptuser a left JOIN idea b ON a.id=b.ptid union select a.name,a.job,a.addr,b.text from ptuser a right JOIN idea b ON a.id=b.ptid;
19#select into子句，用子查询的方式去使用
20create table person(select * from persion);
21#创建数据库
22create database shyy;
23#UNIQUE约束,PRIMARY KEY 拥有自动定义的 UNIQUE 约束。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。(唯一标识数据库的每条记录)
24#一个表设置多个unique时用 constraint uc_PersonID UNIQUE (Id,LastName))；若表已创建时，增加unique
25alter table persion add unique(LastName);
26#撤销unique
27alter table persion drop index LastName;
28alter table persion drop constraint uc_PersonID;
29#增加和撤销主键
30alter table persion add primary key(Id);
31alter table persion drop primary key;
32#外键foreign key:指向另一个表的键
33create table orders(id int(4) not null primary key auto_increment,grate varchar(50),personID int not null,foreign key(personID) references person(Id));
34#删除外键
35alter table orders drop foreign key orders_ibfk_1;
36#增加外键
37alter table orders add foreign key(personID) references person(Id);
38#增加和撤销check约束，撤销时通过show create table person查看约束名
39alter table person add check(age>0);
40alter table person drop constraint person_chk_1;
41alter table person add constraint person check(age>0 and City="北京");
42#增加和撤销default约束，后面alter不能加括号
43alter table person alter age set default "12";
44alter table person alter age drop default;
45#增加删除索引，目的是高效快速的查找数据
46create index personIndex on person(Id);
47select Id from person;(速度比不加索引快);
48drop index personIndex on person;
49#自增
50alter table person auto_increment=100;
51#视图view(对结果集可视化的虚拟表，对于复杂的查询语句，可以直接查询视图)
52create view qw_03 as select * from user where City like "__市";
53select * from qw_03;
54DROP VIEW qw_01;
55#更新视图
56alter view qw_03 as select LastName,FirstName,year from user;
57#date型
58DATE - 格式 YYYY-MM-DD
59DATETIME - 格式: YYYY-MM-DD HH:MM:SS
60SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
61TIMESTAMP - 格式: 唯一的数字(时间戳)

mysql如何插入datetime mysql添加时间_mysql 增加日期时分秒_03

mysql函数

1#top子句(limit中第一个2是第n+1条数据，第二个2是返回条数)
 2select * from persion limit 2,2;
 3#like进行模糊查询
 4select * from persion where City like "%宁%";
 5#通配符_
 6select * from persion where City like "_宁%";
 7#in子句
 8select distinct Id,FirstName from persion where City IN ("西宁市","兰州市","郑州市","北京市","上海市");
 9#betweent and 子句
10select * from persion where Id between 2 and 5;
11#别名
12select a.LastName,a.FirstName from persion a ;
13#join子句
14select a.name,a.job,a.addr,b.text from ptuser a inner join idea b where a.id=b.ptid;
15# left join子句中即使右表没有匹配项也会输出，right join子句中即使左表中没有匹配项也会返回行，full join子句中即使左右表中没有匹配项也会输出所有行
16select a.name,a.job,a.addr,b.text from ptuser a right(left) join idea b on a.id=b.ptid;
17#union并操作，允许出现重复union all,不允许出现重复用union
18select a.name,a.job,a.addr,b.text from ptuser a left JOIN idea b ON a.id=b.ptid union select a.name,a.job,a.addr,b.text from ptuser a right JOIN idea b ON a.id=b.ptid;
19#select into子句，用子查询的方式去使用
20create table person(select * from persion);
21#创建数据库
22create database shyy;
23#UNIQUE约束,PRIMARY KEY 拥有自动定义的 UNIQUE 约束。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。(唯一标识数据库的每条记录)
24#一个表设置多个unique时用 constraint uc_PersonID UNIQUE (Id,LastName))；若表已创建时，增加unique
25alter table persion add unique(LastName);
26#撤销unique
27alter table persion drop index LastName;
28alter table persion drop constraint uc_PersonID;
29#增加和撤销主键
30alter table persion add primary key(Id);
31alter table persion drop primary key;
32#外键foreign key:指向另一个表的键
33create table orders(id int(4) not null primary key auto_increment,grate varchar(50),personID int not null,foreign key(personID) references person(Id));
34#删除外键
35alter table orders drop foreign key orders_ibfk_1;
36#增加外键
37alter table orders add foreign key(personID) references person(Id);
38#增加和撤销check约束，撤销时通过show create table person查看约束名
39alter table person add check(age>0);
40alter table person drop constraint person_chk_1;
41alter table person add constraint person check(age>0 and City="北京");
42#增加和撤销default约束，后面alter不能加括号
43alter table person alter age set default "12";
44alter table person alter age drop default;
45#增加删除索引，目的是高效快速的查找数据
46create index personIndex on person(Id);
47select Id from person;(速度比不加索引快);
48drop index personIndex on person;
49#自增
50alter table person auto_increment=100;
51#视图view(对结果集可视化的虚拟表，对于复杂的查询语句，可以直接查询视图)
52create view qw_03 as select * from user where City like "__市";
53select * from qw_03;
54DROP VIEW qw_01;
55#更新视图
56alter view qw_03 as select LastName,FirstName,year from user;
57#date型
58DATE - 格式 YYYY-MM-DD
59DATETIME - 格式: YYYY-MM-DD HH:MM:SS
60SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
61TIMESTAMP - 格式: 唯一的数字(时间戳)

mysql如何插入datetime mysql添加时间_外键_04

笔记

mysql不支持first函数
mysql不支持last函数
mysql不支持len函数，使用length
mysql中日期格式转换用date_format
mysql不支持top子句。
join和inner join是相同的
mysql不支持full join，用union去实现
mysql不支持select into子句，报错Undeclared variable: person
报错Constraint 'personID' does not exist.原因可能：外键名！=字段名，查看创建表的语句，show create table orders.外键名为 CONSTRAINT后加引号的部分
删除自增时有外键会报错，应删除所有外键
sql语句对大小写不敏感

加油少年，未来可期！！！

mysql如何插入datetime mysql添加时间_ide_05