Mysql基本的不能再基本的操作_mysql操作2018-CSDN博客

本文链接：https://blog.csdn.net/Sunshine_Moon/article/details/79383595

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;