首页 > 资讯 > MySQL > 正文

MySQL的数据库操作（DDL）和数据类型讲解

18-07-20 来源：[db:作者]

收藏我要投稿

一、数据库操作（DDL）

1，创建数据库

CREATE DATABASE test;

实际上是在data目录下创建

CREATE DATABASE if not exists test;

如果同名是不会创建

2，查看

SHOW DATABASES; 查看所有数据库

SHOW CREATE DATABASE test; 查看创建方式

3，修改数据库

ALTER DATABASE test character set gbk;

4,删除数据库

DROP DATABASE test; 删库不存在时会报错

DROP DATABASE if exists test1; 不存在不会报错

5，使用数据库

USE test;

进入数据库后不会退出，只能切换。

SELECT DATABASE(); 查看当前库

二、数据类型

1，数值类型

2，日期类型

3，字符串类型

三、数据表操作

1，创建表

CREATE TABLE student(

id int primary key auto_increment,

name varchar(20),

gender bit default 1,

age tinyint

);

约束条件：

primary key (非空且唯一) :能够唯一区分出当前记录的字段称为主键！

unique 唯一

not null 非空

auto_increment 自增加主键字段必须是数字类型。

外键约束 foreign key

2，查看表信息

DESC student; 查看表结构

SHOW columns FROM student; (同上)

SHOW TABLES; 查看当前数据库所有的表。

SHOW CREATE TABLE student；查看建表语句

3，修改表结构

（1）增加列【字段】

ALTER TABLE student ADD aa int first,ADD bb tinyint after age,ADD cc varchar(20); 插入位置first/after来控制

（2）修改列类型

ALTER TABLE student MODIFY aa varchar(20) after age;

（3）修改列名

ALTER TABLE student CHANGE bb BB int after cc;

（4）删除列

ALTER TABLE student DROP aa,DROP bb,DROP cc;

（5）修改表名

RENAME TABLE student TO student11;

（6）修改字符集

ALTER TABLE student character set gbk;

4，删除表

DROP TABLE employee;

5，添加删除主键

删除主键

ALTER TABLE student MODIFY id int;

ALTER TABLE student DROP PRIMARY KEY;

创建主键

ALTER TABLE student ADD PRIMARY KEY pk_id(id); 主键名（字段）

ALTER TABLE student MODIFY id int auto_increment; 添加主键自增

四、表记录操作

1，增加记录

增加一条记录

INSERT INTO student (id,name,gender,age) VALUES (11,'BOB',1,15);

INSERT INTO student VALUES (15,'LUCY',0,16);

INSERT INTO student (name,age) VALUES ('LILY',16);

INSERT INTO student SET name='GREEN',age=19;

增加多条记录

INSERT INTO student (name,gender,age) VALUES ('ESON',0,17),('WINDY',0,18);

2,修改表记录

UPDATE student SET age = 17 where id=19;

3,删除表记录

DELETE FROM student where id=19;

TRUNCATE TABLE student; 删除表，再创建同名空表

五、单表查询

格式：

SELECT *|field1,filed2 ... FROM tab_name

WHERE 条件

GROUP BY field

HAVING 筛选

ORDER BY field

LIMIT 限制条数

所有内容

SELECT * FROM student;

查看某列

SELECT name FROM student;

查看某行某列

SELECT name FROM student where id=15 ;

去重

SELECT DISTINCT age FROM student;

别名

SELECT name as 姓名 FROM student;

过滤

SELECT * FROM student WHERE age >16 AND age<18;

SELECT * FROM student WHERE age between 16 and 18; 范围是[16,18]

SELECT * FROM student WHERE age in (17,18);

SELECT * FROM student WHERE name like'L%'; 所有名字为L开头的信息

SELECT * FROM student WHERE name is null; 名字为空

排序

SELECT * FROM student ORDER BY age DESC; 降序，默认asc升序

SELECT * FROM student ORDER BY age ASC;

group by

创建表单

CREATE TABLE order_menu(

id INT PRIMARY KEY auto_increment,

product_name VARCHAR (20),

price FLOAT(6,2),

born_date DATE,

class VARCHAR (20)

);

插入数据

INSERT INTO order_menu (product_name,price,born_date,class) VALUES
                                             ("苹果",20,20170612,"水果"),
                                             ("香蕉",80,20170602,"水果"),
                                             ("水壶",120,20170612,"电器"),
                                             ("被罩",70,20170612,"床上用品"),
                                             ("音响",420,20170612,"电器"),
                                             ("床单",55,20170612,"床上用品"),
                                             ("草莓",34,20170612,"水果");

SELECT * FROM order_menu group by 5; 按字段位置查询

SELECT class,SUM(price) FROM order_menu group by class; 组内运算

SELECT class,SUM(price) FROM order_menu group by class HAVING SUM(price); 筛选

PS:having 和 where两者都可以对查询结果进行进一步的过滤，差别有：

<1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；

<2>使用where语句的地方都可以用having进行替换

<3>having中可以用聚合函数，where中就不行。

聚合函数

（1）count：统计行数

SELECT COUNT(*) FROM student; 统计所有行数

SELECT COUNT(age) FROM student WHERE age>16; 统计大于16岁的行数

studen增加字段：

ALTER TABLE student ADD math tinyint ,ADD eng tinyint ,ADD cns tinyint; 

    UPDATE student SET math = 99,eng = 89,cns=90 where id=18;


    UPDATE student SET math = 79,eng = 89,cns=92 where id=17;

    UPDATE student SET math = 92,eng = 99,cns=95 where id=16;

    UPDATE student SET math = 97,eng = 88,cns=91 where id=15;

    UPDATE student SET math = 99,eng = 99,cns=99 where id=11;

SELECT COUNT(name) FROM student where (ifnull(math,0) + ifnull(eng,0) + ifnull(cns,0)) > 270; 统计总成绩大于270

SELECT SUM(math) AS math总成绩,

SUM(eng) AS eng总成绩,

SUM(cns) AS cns总成绩 FROM student; 各科总成绩总成绩

SELECT SUM(math)/COUNT(*) AS math平均分 FROM student; 平均分

AVG求平均:

SELECT AVG(IFNULL(math,0) + IFNULL(eng,0) + IFNULL(cns,0)) AS 总分平均 FROM student;

MAX、MIN:

SELECT MAX(math) AS math最高分,MIN(IFNULL(math,0)) AS math最低分 FROM student; math最高分、最低分

-- Mysql在执行sql语句时的执行顺序：from where select group by having order by

LIMIT:

SELECT * FROM student LIMIT 1; 显示一条

SELECT * FROM student LIMIT 2,2; 跳过2条显示两条

REGEXP:正则表达

SELECT * FROM student where name REGEXP '^L';

六、外键约束

主表：

创建班主任表

 CREATE TABLE classteacher(

                        id TINYINT PRIMARY KEY AUTO_INCREMENT,

                        name varchar(20),

                        age TINYINT,

                        gender bit);

INSERT INTO classteacher (name,age,gender) VALUES  ('李老师',33,0),

                                                   ('王老师',24,1),

                                                   ('宋老师',55,0);

子表：

CREATE TABLE student1(

       id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       charger_id TINYINT,     
       FOREIGN KEY (charger_id) REFERENCES classteacher(id)

) ENGINE=INNODB;

--切记:作为外键一定要和关联主键的数据类型保持一致

--

ALTER TABLE student1ADD CONSTRAINT charger_fk_stu   增加约束条件，关联外键，双方数据类型必须一致

ALTER TABLE student1 DROP FOREIGN KEY charger_fk_stu;

因为外键约束的原因而导致插入失败也会影响到主键自增

INSERT INTO student1(name,charger_id) VALUES ("aaa",2),

('bbb',1),

('ccc',3),

('ddd',1),

('eee',3),

('fff',3);

级联删除

创建表时在外键后加上ON DELETE CASCADE;

ON DELETE SET NULL:主表删除后，关联字段设为空值；

Restrict方式 :拒绝对父表进行删除更新操作(了解)

No action方式在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作（了解）

七、多表查询

创建两张表

create table employee(
      emp_id int auto_increment primary key not null,
      emp_name varchar(50),
      age int,
      dept_id int
      );

insert into employee(emp_name,age,dept_id) values
        ('A',19,200),
        ('B',26,201),
        ('C',30,201),
        ('D',24,202),
        ('E',20,200),
        ('F',38,204);


create table department(
       dept_id int,
       dept_name varchar(100)
      );

insert into department values
      (200,'人事部'),
      (201,'技术部'),
      (202,'销售部'),
      (203,'财务部');

1，笛卡尔积查询

SELECT * FROM employee,department;

+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      1 | A        |   19 |     200 |     201 | 技术部    |
|      1 | A        |   19 |     200 |     202 | 销售部    |
|      1 | A        |   19 |     200 |     203 | 财务部    |
|      2 | B        |   26 |     201 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      2 | B        |   26 |     201 |     202 | 销售部    |
|      2 | B        |   26 |     201 |     203 | 财务部    |
|      3 | C        |   30 |     201 |     200 | 人事部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     202 | 销售部    |
|      3 | C        |   30 |     201 |     203 | 财务部    |
|      4 | D        |   24 |     202 |     200 | 人事部    |
|      4 | D        |   24 |     202 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      4 | D        |   24 |     202 |     203 | 财务部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|      5 | E        |   20 |     200 |     201 | 技术部    |
|      5 | E        |   20 |     200 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     203 | 财务部    |
|      6 | F        |   38 |     204 |     200 | 人事部    |
|      6 | F        |   38 |     204 |     201 | 技术部    |
|      6 | F        |   38 |     204 |     202 | 销售部    |
|      6 | F        |   38 |     204 |     203 | 财务部    |
+--------+----------+------+---------+---------+-----------+
24 rows in set (0.00 sec)

总行数为两表行数之积

2，内连接查询

SELECT * FROM employee,department whereemployee.dept_id = department.dept_id;

SELECT * FROM employee INNER JOIN department on employee.dept_id = department.dept_id;

3,外连接

左连接

SELECT * FROM employee LEFT JOIN department on employee.dept_id = department.dept_id;

SELECT * FROM departmentLEFT JOIN employee on employee.dept_id = department.dept_id;

如图，左连接会完整展示左表，剩余部分以null补全

SELECT * FROM employee RIGHT JOIN department on employee.dept_id = department.dept_id;

SELECT * FROMdepartmentRIGHT JOINemployee on employee.dept_id = department.dept_id;

全连接full join ：mysql语法不支持

SELECT * FROM employee FULL JOIN department on employee.dept_id = department.dept_id;

SELECT * FROM employee LEFT JOIN department on employee.dept_id = department.dept_id

UNIONSELECT * FROM employee RIGHT JOIN department on employee.dept_id = department.dept_id;

5，子表查询

子查询是将一个查询语句嵌套在另一个查询语句中。

内层查询语句的查询结果，可以为外层查询语句提供查询条件。

子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

还可以包含比较运算符：= 、 !=、> 、<等

（1）带IN关键字的子查询

查询employee表，但dept_id必须在department表中出现过

select * from employee

where dept_id IN

(select dept_id from department);

（2）带比较运算符的子查询

=、!=、>、>=、<、<=、<>

查询员工年龄大于等于25岁的部门

select dept_id,dept_name from department

where dept_id IN

(select DISTINCT dept_id from employee where age>=25);

（3）带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。

而是返回一个真假值。Ture或False

当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

select * from employee

WHERE EXISTS

(SELECT dept_name from department where dept_id=203);

--department表中存在dept_id=203，Ture

select * from employee

WHERE EXISTS

(SELECT dept_name from department where dept_id=205);

-- Empty set (0.00 sec)

复制表: create table t1(select * from t2);

八、索引

普通索引：

CREATE TABLE table1(

id INT,name VARCHAR(20),

index index_name (name));

或者用key代替index

唯一索引

UNIQUE INDEX index_a (a)，不命名自动以字段名命名

全文索引

FULLTEXT INDEX index_a (a) 字段a

多列索引

INDEX index_a_b (a,b) 字段a,字段b

delimiter 设置分隔符

点击复制链接与好友分享!回本站首页