mysql的简单使用

创建表

CREATE TABLE `bank` (
  `id` int(11)  PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `bank_end` varchar(255) DEFAULT NULL COMMENT '银行卡后四位',
  `bank_name` longtext COMMENT '银行名称',
  `bank_funds` varchar(255) DEFAULT NULL COMMENT '银行卡余额',
  `bank_use` longtext COMMENT '该笔金额用途',
  `update_resume_times` varchar(255) DEFAULT NULL COMMENT '余额变动最新时间',
  `resume_user` varchar(255) DEFAULT NULL COMMENT '消费人',
  `bank_resume_status` char(1) DEFAULT NULL
)

　　删除表中字段

1 2	`删除bank_resume_status字段` `alter table bank drop bank_resume_status`

　　表中新增字段

向bank 表中新增bank_status字段，类型为字符串
 
ALTER TABLE bank ADD bank_status  VARCHAR(10);

　　添加语句

insert into bank(bank_end,bank_name,bank_funds,bank_use,update_resume_times,resume_user,bank_status)values
('1234','阿萨','12','安师大',now(),'as','1')
 
insert into bank(bank_end,bank_name,bank_funds,bank_use,update_resume_times,resume_user,bank_status)values
('1234','阿萨','12','安师大',now(),'as','1'),
('1234','阿萨','12','安师大',now(),'as','1')

　　删除表中数据，会清掉数据和结构，操作后，内存会被释放

1	truncate table `bank`

　　修改bank表中bank_status字段的类型

1	`ALTER TABLE bank MODIFY COLUMN bank_status VARCHAR(20);`

　　删除表中数据

1	`DELETE FROM bank WHERE id=3`

　　修改表中数据

1	`UPDATE bank SET bank_funds='100'` `WHERE id=2`

　　查询表中数据

1	`SELECT * FROM bank WHERE id=1`

　　查询表的记录数

1	`SELECT COUNT(*) FROM bank WHERE id=1`

　　数据分组

1	`SELECT * FROM bank GROUP BY bank_use`

关于给字段设置默认值，分为两种形式，一种是创建表的时候设置默认值，

CREATE TABLE customer_message(
  customer_message_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  customer_message_name VARCHAR(20) COMMENT '客户姓名',
  customer_message_num INT(4) COMMENT '信息查询次数',
  is_use VARCHAR(20) DEFAULT 'Y'
)

INSERT INTO customer_message(customer_message_name,customer_message_num)VALUE
('寂寞寂寞就好',3001),
('不爱代码的程序员',3002),
('同学两亿岁',3003),
('不起眼',3004),
('未知空间',3005)

另一种是表中需要新增字段设置默认值

CREATE TABLE customer_message(
  customer_message_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  customer_message_name VARCHAR(20) COMMENT '客户姓名',
  customer_message_num INT(4) COMMENT '信息查询次数'
)
 
ALTER TABLE customer_message ADD COLUMN is_use VARCHAR(20) DEFAULT 'Y';
 
INSERT INTO customer_message(customer_message_name,customer_message_num)VALUE
('寂寞寂寞就好',3001),
('不爱代码的程序员',3002),
('同学两亿岁',3003),
('不起眼',3004),
('未知空间',3005)