mysql 数据库操作

#  优秀电商开源系统地址提供学习: http://github.crmeb.net/u/fei

 

/* 创建数据库 */
CREATE DATABASE mydb001
/* 删除数据库 */
DROP DATABASE mydb001
/* 查看全部数据库 */
SHOW DATABASES
/* 使用数据库 */
USE mydb001
/* 查看数据库全部表格 */
SHOW TABLES
/* 创建表格 */
CREATE TABLE userInfo(
name VARCHAR (50) CHARACTER SET "utf8", /* 设置列表字符集防止中文乱码 */
sex VARCHAR (50) CHARACTER SET "utf8",
age INT (5),
address VARCHAR(50) CHARACTER SET "utf8",
school VARCHAR(50) CHARACTER SET "utf8",
profession VARCHAR(50) CHARACTER SET "utf8"
)

/* 重新修改表名称 */
RENAME TABLE userinfo to user_info
/* 删除表 */
DROP TABLE user_info
/* 查看表格字段 */
DESC user_info
/* 表中插入数据 */
INSERT INTO user_info(NAME,sex,age,address,school,profession) VALUES ("邓ss","男",27,"陕西省","西安学院","机械制造及自动化")
INSERT INTO user_info(NAME,sex,age,address,school,profession) VALUES ("刘亦菲","女",30,"陕西省","北京电影学院","表演艺术")
/* 查询表格全部信息 */
SELECT * FROM user_info
/* 条件查询 */
SELECT address FROM user_info WHERE name="sss"
SELECT * FROM user_info WHERE name="sss"
SELECT name FROM user_info
/* 修改字段 */
UPDATE user_info SET school="sss" WHERE name="sss"
/* 删除数据 */
DELETE FROM user_info WHERE name="刘亦菲"
/* 添加表字段 */
ALTER TABLE user_info ADD hobby VARCHAR(50) CHARACTER SET "utf8"
/* 修改表字段 */
ALTER TABLE user_info CHANGE hobby hobbies VARCHAR(50) CHARACTER SET "utf8"
/* 修改表字段属性 */
ALTER TABLE user_info MODIFY hobbies INT(10)
/* 删除表字段 */
ALTER TABLE user_info DROP hobbies

/* 两表联查：查询工作为测试工程师的人的年龄 */
SELECT age FROM user_info WHERE name IN (SELECT name FROM job WHERE work="测试工程师")
/* 并列表条件查询 */
SELECT name FROM user_info WHERE school="sss" AND age=27
/* 查询姓名不在job表中的人的住址 */
SELECT address FROM user_info WHERE name NOT IN (SELECT job.`name` FROM job)
/* ORDER BY 句子 逆顺序加 DESC*/
SELECT name FROM user_info ORDER BY age DESC

/* 查平均值 */
SELECT AVG(age) FROM user_info WHERE school="sss院"
/* 查最大 */
SELECT MAX(age) FROM user_info WHERE school="sss院"
/* 查最小 */
SELECT MIN(age) FROM user_info WHERE school="ssss院"
/* 查计算和 */
SELECT SUM(age) FROM user_info WHERE school="sss院"