1.创建用户表:
CREATE TABLE IF NOT EXISTS `user_info`(
`userId` INT UNSIGNED AUTO_INCREMENT,
`loginName` VARCHAR(20) NOT NULL,
`phone` VARCHAR(11) NOT NULL,
`name` VARCHAR(40) NOT NULL,
`pronvince` VARCHAR(40) NOT NULL,
`address` VARCHAR(40) NOT NULL,
`sex` INTEGER,
`registerData` DATE,
PRIMARY KEY ( `userId` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.查询表
SELECT * FROM user_info;
SELECT * FROM user_info WHERE userId=1;
3.插入表数据
INSERT INTO user_info (`loginName`,`phone`,`name`,`pronvince`,`city`,`address`,`sex`,`registerData`)
VALUES('123456','1762xxxxxxx1','张三','广东','广州','xxx',1,'2018-12-06');
INSERT `users` ( `login_name`, `phone`, `name`, `province`, `city`, `address`, `sex`, `register_date` )
SELECT '美', '181xxxxxxxx', 'author', '广东', 'xx', 'xxx', 1, '2018-12-06 07:54:56' ;
INSERT INTO user_info VALUES(1,'123456','1762xxxxxxxx','cping','广东','广州','xxx',1,'2018-12-06');
4.更新表数据
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
5.删除表数据
DELETE FROM user_info WHERE userId=2;
6.排序表数据
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
从小到大( ASC) | 从大到小(|DESC)
7.SQL AND & OR TOP运算符
SELECT * FROM table_name WHERE column_name operator value AND column_name operator value;
SELECT * FROM table_name WHERE column_name operator value OR column_name operator value;
查询:
SqlServer/s语法:
-- 50%的数据
SELECT TOP 50 PERCENT * FROM table_name;
-- 前100的数据
SELECT TOP 100 column_name * FROM table_name;
MySql语法:
SELECT *FROM table_name LIMIT 100;
8.修改表的字段
alter table user_info change pronvince province varchar(20);
其它修改表的类型:
(1)修改表的名称呢
alter table 表名 rename to 新的名字;
demo:
alter table ta1 rename to ta0;
(2)添加一个新字段
alter table 表名 add 新字段 字段的类型;
demo:
alter table ta0 add unames varchar(20);
(3)修改字段:
alter table 表名 change 旧得字段 新的字段 字段的数据类型;
alter table ta0 change unames uname varchar(20);
(4)修改字段的类型:
alter table 表名 modify 字段 字段的新类型
demo:
alter table ta0 modify uname int;
(5)修改表的字段编码:
alter table `table_name` convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
9、时间比较
-- 相差周数
select timestampdiff(week,'2021-01-2','2021-02-04');
-- 相差天数
select datediff(now(),'2021-02-05');
-- 相差天数
select timestampdiff(day,'2021-01-30','2021-02-04');
-- 相差小时
select TIMESTAMPDIFF(HOUR,'2021-02-07 14:47:23',now());
-- 相差分钟
select TIMESTAMPDIFF(MINUTE,'2021-02-07 14:47:23',now());
-- 相差秒数
select TIMESTAMPDIFF(SECOND,'2021-02-07 14:47:23',now());