-- 查询数据库
show DATABASES;
-- 创建数据库
CREATE DATABASE if not EXISTS Study CHARACTER SET utf8;
-- 切换到数据库:以后的操作都是针对该数据库的,如建表
USE Study;
-- 查看当前数据库有哪些数据表:
SHOW TABLES;
-- 删除数据库:
drop DATABASE if EXISTS Study;
-- 建表,关键字primary key 用于定义此列作为主键
USE Study;
CREATE TABLE user(
id int(4) PRIMARY KEY auto_increment,
username VARCHAR(64) not null,
pwd VARCHAR(30),
sex char(1)
)
-- 查看表结构
desc user;
-- 显示检表语句
show create table user
-- 表中插入数据
INSERT INTO user(id,username,pwd,age) VALUES(1,'zhangsan','123456',18),(2,'lisi','123456789',30);
-- 表中修改数据
UPDATE user set username='wangwu',pwd='1234' WHERE id=1;
UPDATE user set birthday=NULL where age BETWEEN 18 and 30;
UPDATE user set age=50 where id=1 or id=2;
UPDATE user set age=80 where id in(1,2);
UPDATE user set age=88 where id is not null;
-- 删除表中一列数据
DELETE from user where id = 2;
-- 查询表数据
SELECT * FROM USER;
-- 修改表结构
-- ①添加字段
ALTER TABLE user add(
age int(4),
birthday date
)
-- ②修改已有字段的数据类型:modify
alter table user MODIFY age FLOAT(4,1);
desc user;
-- 删除某列:drop
alter TABLE user drop birthday;
-- 删除表
drop TABLE user;