CREATE TABLE t_employee(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(20),
t_gender VARCHAR(10),
t_birthday DATE,
t_entry_date DATE,
t_job VARCHAR(20),
t_salary DOUBLE,
t_resume TEXT
);
– 约束
– * 自增 auto_increment 只针对INT型和主键约束
– * 主键约束 primary key 一条记录需要被一个字段唯一确定.将这个字段设置为主键了,默认是唯一非空的.
– * 唯一约束 unique 字段中的值不能重复的.
– * 非空约束 not null 字段中的值不能为null.
– 重制表的结构
– * 修改表添加列 alter table 表名 add 字段 类型(长度) 约束;
ALTER TABLE t_employee ADD image VARCHAR(30);
– * 修改表删除列 alter table 表名 drop 字段;
ALTER TABLE t_employee DROP t_resume;
– * 修改表修改列(类型,长度及约束) alter table 表名 modify 列名 类型(长度) 约束;
ALTER TABLE t_employee MODIFY t_name VARCHAR(30) NOT NULL;
– * 修改表修改列名 alter table 表名 change 旧列名 新列名 类型(长度) 约束;
ALTER TABLE t_employee CHANGE t_name t_username VARCHAR(20);
– * 修改表字符集 alter table 表名 character set 字符集;
ALTER TABLE t_employee CHARACTER SET GBK;
– * 修改表名称 rename table 表名 to 新表名;
ALTER TABLE t_employee TO employee,
– 增 INSERT
– * 插入某些字段: insert into 表名 [字段1,字段2,字段3…] values [值1,值2,值3…];
– * 插入所有字段: insert into 表名 values [值1,值2,值3…];
INSERT INTO t_employee(t_name,t_gender,t_birthday) VALUES('Jack','男','1991-01-03');
INSERT INTO t_employee(t_name,t_gender,t_birthday) VALUES('Lucy','女','1995-08-13');
INSERT INTO t_employee(t_name,t_gender,t_salary) VALUES('张三','男','4000');
INSERT INTO t_employee(t_name,t_gender,t_salary) VALUES('李四','男','9000');
INSERT INTO t_employee(t_name,t_gender,t_salary) VALUES('王五','男','10000');
INSERT INTO t_employee(t_name,t_gender,t_salary) VALUES('凤九','女','8000');
INSERT INTO t_employee(t_name,t_gender,t_salary) VALUES('白浅','女','4000');
INSERT INTO t_employee(t_name,t_gender,t_salary) VALUES('琴九','女','10000');
– 删 DELETE/TRUNCATE
– * 删除所有记录: delete from 表名;
– * 删除某些条件记录: delete from 表名 where 条件;
DELETE FROM t_employee WHERE t_id=2;
– * truncate语句: truncate (table) employee;
TRUNCATE t_employee;
– 改 UPDATE
– * 修改表中的所有记录: update 表名 set [字段名=值,字段名=值];
– * 按照条件修改记录: update 表名 set [字段名=值,字段名=值…] where 条件;
UPDATE t_employee SET t_job='Java软件工程师',t_salary='5000' WHERE t_id=1;
UPDATE t_employee SET t_job='前台',t_salary='3500' WHERE t_id=2;
– 查 SELECT (distinct–>过滤重复数据)
– * 语句顺序: SELECT FROM 表 WHERE 条件 GROUP BY 字段 HAVING … ORDER BY 字段*
– * 基本查询语句: select (distinct) /[列名,列名] from 表名;*
SELECT * FROM t_employee;
SELECT t_name,t_salary FROM t_employee;
SELECT DISTINCT t_salary FROM t_employee;
– * 给列起个别名: select 旧列名 (as) 新列名 from 表名;
SELECT t_name,t_salary+1 AS money FROM t_employee;
– * 条件查询: select from 表名 where 条件;*
-- ** > < <= >= = <>/!= :运算符
SELECT t_name,t_salary FROM t_employee WHERE t_salary<>4000;
-- ** like :模糊查询. _或者%作为占位符. _:代表一个字符 %:代表0或多个字符
SELECT t_name,t_salary FROM t_employee WHERE t_name LIKE '%九';
-- ** is null 或者 is not null
SELECT t_name,t_salary FROM t_employee WHERE t_job IS NULL;
SELECT t_name,t_salary FROM t_employee WHERE t_job IS NOT NULL;
-- ** in :在某个范围内.
SELECT t_name,t_salary FROM t_employee WHERE t_salary IN (4000,8000);
-- ** not :不在某个范围内.
SELECT t_name,t_salary FROM t_employee WHERE t_salary NOT IN (4000,10000);
-- ** and :与
SELECT t_name,t_salary FROM t_employee WHERE t_salary=4000 AND t_name='白浅';
-- ** or :或
SELECT t_name,t_salary FROM t_employee WHERE t_salary=4000 OR t_salary=10000;
– * 排序查询: select from 表 order by 字段名 asc(升序)/desc(降序);*
SELECT t_name,t_salary FROM t_employee ORDER BY t_salary ASC;
SELECT t_name,t_salary FROM t_employee ORDER BY t_salary DESC;
– * 聚合函数:
-- ** group by :分组.聚合函数一般与分组一起使用
SELECT t_gender,SUM(t_salary) FROM t_employee GROUP BY t_gender;
-- ** count :统计个数. select count(*/列名) from t_employee where 条件;
SELECT COUNT(t_job) FROM t_employee;
-- ** sum :求和. select sum(列名) from t_employee where 条件;
SELECT SUM(t_salary) FROM t_employee WHERE t_gender='女';
-- ** avg :求平均值 select avg(列名) from t_employee where 条件;
SELECT AVG(t_salary) FROM t_employee WHERE t_gender='女';
-- ** max :求最大值. select max(列名) from t_employee where 条件;
SELECT MAX(t_salary) FROM t_employee WHERE t_gender='女';
-- ** min :求最小值. select min(列名) from t_employee where 条件;
SELECT MIN(t_salary) FROM t_employee WHERE t_gender='女';
-- ** concat :连接两个字符串组成新字符串 select concat(列名1,列名2) from t_employee where 条件;
SELECT t_id,CONCAT(t_name,t_gender) remix FROM t_employee;
-- ** having :where后面不能使用聚合函数.having进行条件过滤可以在having后面写聚合函数
SELECT t_gender,SUM(t_salary) FROM t_employee GROUP BY t_gender HAVING SUM(t_salary)>26000;
– 显示数据库所有编码信息
SHOW VARIABLES LIKE 'character%';
– 更改编码信息
SET character_set_results=gbk;
– 删除表格
DROP TABLE t_employee;
– 显示表格属性信息
DESC t_employee;