MySQL基本操作（1）

一、创建数据库

-- 创建数据库
CREATE DATABASE student DEFAULT CHARACTER
SET utf8 COLLATE utf8_general_ci;

-- 使用库
USE student;

二、创建表格

-- 创建表格
CREATE TABLE t_student_dwx (
    student_id INT (11) PRIMARY KEY, -- 主键
    class_id INT (11) UNIQUE, -- 唯一
    student_name CHAR (20) NOT NULL, 不能为空
);

-- 查看库
SHOW DATABASES;

-- 查看库信息
SHOW CREATE DATABASE student;

-- 删除库
DROP DATABASE student;

-- 修改表 新增列
ALTER TABLE t_student_dwx ADD (student_dept VARCHAR(255));

-- 修改表 修改列
ALTER TABLE t_student_dwx MODIFY student_dept CHAR (20);

-- 修改表 删除列
ALTER TABLE t_student_dwx DROP student_dept;

三、简单的增删改查

-- 插入语句
-- 第一种不指定准确的字段表示插入所有的列
-- 第二种指定了个别列 表示插入指定的列
-- 注意：后面插入具体数据的顺序 必须跟前面指定字段顺序一致
INSERT INTO student_info VALUES (1,'张三','男',15,'计算机系',0);
INSERT INTO student_info (s_name,s_sex,s_age,s_dept)VALUES ('李四','女',15,'计算机系');
-- varchar类型可以存int值，但是int类型只能存是数字的varchar值
INSERT INTO student_info (s_name,s_sex,s_age,s_dept)VALUES ('男','王五','15',15);
-- 错误插入方法
INSERT INTO student_info (s_name,s_sex,s_age,s_dept)VALUES ('男','王五','计算机系',15);

INSERT INTO student_info (s_sex,s_name,s_age,s_dept)VALUES ((SELECT student_sex FROM t_student_dwx WHERE student_id = 1),'王五',15,'二人转');

-- 修改语句
-- WHERE条件表示确定要改哪一行 如果没有where条件则会修改所有行
-- update语句是先查后改
UPDATE student_info SET s_name = '刘能' WHERE id =3;

UPDATE student_info SET s_name = '刘能';

UPDATE student_info SET s_name = '赵四'WHERE s_sex = '男';

UPDATE student_info SET s_name = '赵四',s_age = 17,s_dept = '搬砖的'WHERE s_sex = '男';

UPDATE student_info SET s_sex = '男' WHERE s_sex = '女';

-- 物理删除语句
-- where确定删除某一行 如果没写where则表示删除所有数据 但是他的id序列不会重置
DELETE FROM t_student_dwx WHERE student_id = 4;

DELETE FROM t_student_dwx;

-- 查询语句
SELECT * FROM student_info; -- 简单查询
SELECT s_name FROM student_info; -- 查询某一列
SELECT COUNT(s_name) FROM student_info; -- 查询条数
SELECT MAX(s_age) FROM student_info; -- 查询最大值
SELECT SUM(s_age) FROM student_info;
SELECT * FROM student_info WHERE flag = 0; -- 条件查询
SELECT s_name AS 姓名 FROM student_info; -- AS起别名

SELECT id AS 学号,
            s_name AS 姓名,
                s_sex AS 性别,
                s_age AS 年龄,
                s_dept AS 专业 FROM student_info;

-- 运算符查询
SELECT * FROM student_info WHERE flag = 0;
SELECT * FROM student_info WHERE flag != 0;
SELECT * FROM student_info WHERE flag <> 0;
SELECT * FROM student_info WHERE s_age < 17;
SELECT * FROM student_info WHERE s_age <= 17;
SELECT * FROM student_info WHERE s_age > 15;
SELECT * FROM student_info WHERE s_age >= 15;

-- in的运用 包含和不包含
SELECT * from student_info WHERE s_age in(15,16,17);
SELECT * FROM student_info WHERE s_sex NOT in('男','女');

-- 模糊查询
SELECT * FROM student_info WHERE s_name LIKE '%刘%';

-- 空与非空运用
SELECT * FROM student_info WHERE s_name is NULL;
SELECT IFNULL(s_sex,'未知') FROM student_info;

-- 多条件查询 and的使用
SELECT * FROM student_info WHERE s_name = '刘能' AND s_sex = '女' AND s_age = 15;
SELECT * FROM student_info WHERE s_name = '刘能' OR s_sex = '女' OR s_age = 15;
SELECT * FROM student_info WHERE s_name = '刘能' AND s_age = 15 OR s_sex = '女';
SELECT * FROM student_info WHERE s_name = '刘能' AND (s_age = 15 OR s_sex = '女');

-- 排序 如果是两个字段去排序 需要以逗号分割 排在语句的最后位
SELECT * FROM student_info ORDER BY s_age ASC;
SELECT * FROM student_info ORDER BY s_age DESC,id ASC;
SELECT * FROM student_info WHERE s_name = '刘能' OR s_sex = '女' OR s_age = 15 ORDER BY s_age;