mysql

#-----------------------------------------------表操作--------------------------------------------------

# 创建表
CREATE TABLE if NOT exists `user` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT "姓名"
);

# 删除表
DROP Table IF EXISTS `user`;

#-----------------------------------------------字段操作--------------------------------------------------

# 添加字段
ALTER TABLE `user` ADD COLUMN `age` int COMMENT "年龄";

# 添加多个字段
ALTER TABLE `user` ADD  `xingbie` int NOT NULL ,ADD `shengfen` VARCHAR(18) COMMENT "性别";

# 添加字段，并给字段默认值
ALTER TABLE `user` MODIFY COLUMN `age` INT DEFAULT 1 COMMENT "年龄";

# 修改字段
ALTER Table `user` MODIFY COLUMN `age` INT;

# 修改字段：可以修改字段名称
ALTER TABLE `user` CHANGE COLUMN `age` `age` int COMMENT "年龄";

# 删除字段
ALTER TABLE `user` DROP COLUMN age;

#-----------------------------------------------数据操作--------------------------------------------------

# 插入数据
INSERT INTO `user` ( name) VALUES ( '张三');

# 插入多个数据
insert INTO `user` ( `name`, `age`)
VALUES
 ( "李四", 8),
 ( "cxk", 12);

# 更新
update `user` SET `name` = '鸡' WHERE id = 6;

update `user` SET age = 12;

update `user` SET age = 10,name = '鸡你太美'  WHERE id = 1;

update`user` SET age = 10 WHERE (id!=1&&age>1)||id>0 ORDER BY id DESC,age   

# 删除
DELETE FROM `user` WHERE id = 3;

DELETE FROM `user` WHERE id > 3 AND age IS NULL;

# 删除表中所有数据
DELETE FROM `user`

# 删除表中所有数据，并重置自增id
truncate table `user`

# 查询语句
SELECT * FROM `user`

SELECT name as 名字 FROM `user`

# 查询表中指定字段的数据，并且查询数据不重复
SELECT DISTINCT name FROM `user`

SELECT DISTINCT id, name FROM `user` WHERE name = '张三'

SELECT id, name FROM `user` WHERE id >= 1 and id <= 3

SELECT id, name FROM `user` WHERE id >= 1 && id <= 3

# 降序查询:不写DESC默认升序
SELECT * FROM `user` ORDER BY id DESC,age

# 升序查询
SELECT * FROM `user` ORDER BY age,

# 分页查询
SELECT * FROM `user` ORDER BY id ,age DESC LIMIT 0,8

# 条件分页查询
SELECT * FROM `user` WHERE (id!=1&&age>1)||id>0 ORDER BY id ,age DESC  LIMIT 0,8 

#-----------------------------------------------数据操作--------------------------------------------------

# 函数
UPDATE `user` SET name=CONCAT(${name}, '555')

UPDATE `user` SET name = SUBSTRING(name, 0, 3)