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
)

　　删除表中字段

删除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')

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

truncate table `bank`

　　修改bank表中bank_status字段的类型

ALTER TABLE bank MODIFY COLUMN bank_status VARCHAR(20);

　　删除表中数据

DELETE FROM bank WHERE id=3

　　修改表中数据

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

　　查询表中数据

SELECT  *  FROM bank WHERE id=1

　　查询表的记录数

SELECT  COUNT(*)  FROM bank WHERE id=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)