-- DDL库操作

-- 创建字符集数据

CREATE DATABASE db3;

-- 创建指定字符集的数据库

CREATE DATABASE db1 CHARSET utf8;

-- 切换数据库

ues db3;

-- 查看数据库的创建信息

SHOW CREATE DATABASE db1;

-- 查看所有数据库

SHOW DATABASES;

-- 删除数据库

DROP DATABASE db1;

--  DDL表操作 --

USE db3;

-- 创建新表

CREATE TABLE person(

uid INT,

sname VARCHAR(20),

sex VARCHAR(20),

age INT,

hobby VARCHAR(20)

);

-- 修改表名

ALTER TABLE person RENAME per;

-- 查看表结构

DESC per;

-- 添加一列  add 

ALTER TABLE per ADD price DOUBLE;

-- 在指定字段后面进行添加    after 

ALTER TABLE per ADD price1 DOUBLE AFTER price;

-- change 修改一列

ALTER TABLE per CHANGE price1 price2 DOUBLE;

-- 删除表

DROP TABLE per;

--   DML 数据操作语言  --

CREATE DATABASE db2;

USE db2;

CREATE TABLE person(

uid INT,

sname VARCHAR(20),

sex VARCHAR(20),

age INT,

hobby VARCHAR(20)

);

-- 添加数据 insert into

INSERT INTO person(sname) VALUES('路飞');

INSERT INTO person(uid,sex,age,hobby)

VALUES (1,'男',18,'当海贼王');

INSERT INTO person(uid,sname,sex,age,hobby)

VALUES(2,'娜美','女',19,'赚钱'),

(3,'山治','男',25,'美女'),

(4,'索隆','男',23,'练功'),

(5,'艾斯','男',20,'白胡子');

-- 修改

UPDATE person SET sname = '蒙奇D路飞' WHERE uid = 1;

UPDATE person SET hobby = '海贼王' WHERE sname = '蒙奇D路飞';

-- 删除

DELETE FROM person WHERE sname = '艾斯';

-- 清空数据

TRUNCATE TABLE person;

--  DQL 数据查询语言  --

CREATE DATABASE db1;

USE db1;

CREATE TABLE person(

sid INT,

sname VARCHAR(20),

sex VARCHAR(20),

age INT,

hobby VARCHAR(20)

);

INSERT INTO person(sid,sname,sex,age,hobby)

VALUES(2,'娜美','女',19,'赚钱'),

(3,'山治','男',25,'美女'),

(4,'索隆','男',23,'练功'),

(5,'艾斯','男',20,'白胡子'),

(1,'路飞','男',18,'当海贼王');

-- 查询表中所有数据

SELECT * FROM person;

-- 单列查询

SELECT sname FROM person;

-- 多列查询

SELECT sname , sex FROM person;

-- 给列名设置别名

SELECT sname s , age a FROM person;

-- 条件查询

SELECT * FROM person WHERE sid = 3;

SELECT * FROM person WHERE sex = '女';

-- 多条件查询

SELECT * FROM person WHERE sname = '索隆' AND sex = '男';

-- > < >= <= != <>

SELECT * FROM person WHERE age >= 17 AND age <= 21;

-- 范围查询 between... and ...

SELECT * FROM person WHERE age BETWEEN 17 AND 21 OR sex = '女';

-- 性别不为男的信息

SELECT * FROM person WHERE sex <> '男';

--  约束  --

USE db1;

CREATE TABLE t1(

tid INT PRIMARY KEY AUTO_INCREMENT,-- 主键自增

tname VARCHAR(20)

)

INSERT INTO t1 VALUES(1,'鸣人');

INSERT INTO t1 VALUES(1,'雏田');

INSERT INTO t1 VALUES(2,'博人');

CREATE TABLE t2 (

  tid INT PRIMARY KEY AUTO_INCREMENT,

  tname VARCHAR (20) UNIQUE,

  sex VARCHAR (20) NOT NULL,

  hobby VARCHAR (20) DEFAULT '吃喝嫖赌'

) ;

INSERT INTO t2 VALUES(NULL,'鹿丸','男','影子');

INSERT INTO t2 VALUES(NULL,'鹿丸','女','111');-- 错误  

INSERT INTO t2 VALUES(NULL,'李四',NULL,'唱歌'); -- 错误  

INSERT INTO t2 VALUES(NULL,'二柱子','男',NULL);

INSERT INTO t2 VALUES(NULL,'宇智波带土','男',DEFAULT);