数据库基础命令操作

本章汇总：

增

删

改

查

表结构查询

多表查询

完整性约束

增

创建数据库

CREATE DATABASE db_name CHARACTER SET utf8;

创建表

创建表只是创建表结构，即创建出了一张空表

语法

CREATE TABLE tab_name(
            field1 type[完整性约束条件],
            field2 type,
            ...
            fieldn type
        )[character set xxx];

示例

 CREATE TABLE employee(
            id int primary key auto_increment ,
            name varchar(20),
            gender bit default 1,
            birthday date,
            department varchar(20),
            salary double(8,2) unsigned,
            resume text
          );

增加表记录

1、插入一条记录

insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);

示例

INSERT employee VALUES (8,"女神",0,"1992-02-12","教学部",7000,"");

2、插入多条记录

insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......)，
　　　　　　　　　　　　　　　　　　　　　　　　　　　　            (value1,value2,.......), 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　...                               ;

示例

INSERT new_p (name,gender,birthday,salary,department) VALUES
                　　　　　　("alex",1,"1985-12-12",8000,"保洁部"),
                　　　　　　("egon",1,"1987-08-08",5000,"保安部"),
                　　　　　　("yuan",1,"1990-06-06",20000,"教学部");

3、set插入

insert ［into］ tab_name set 字段名=值

删

删除数据库

DROP DATABASE db_name;

删除表

drop table tab_name;

删除表记录

delete from tab_name [where ....]
or
truncate table emp_new;

如果不跟where语句则删除整张表中的数据
delete只能用来删除一行记录
delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在
事务中恢复。

示例

-- 删除表中名称为’alex’的记录。
   DELETE FROM employee_new WHERE NAME='alex';
-- 删除表中所有记录。
   DELETE FROM employee_new;

-- 注意auto_increment没有被重置:
     ALTER TABLE employee auto_increment=1;

改

修改数据库

ALTER DATABASE db_name CHARACTER SET gbk;

改表

1、增加字段（增加列）

alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］;

示例：添加多个字段（添加多列）

ALTER TABLE employee 
            add addr varchar(20),
            add age  int first,
            add birth varchar(20) after name;

2、修改列的类型

alter table tab_name modify 列名 新类型 [完整性约束条件]［first｜after 字段名］;

示例

ALTER TABLE employee  MODIFY addr varchar(100);

3、修改列名

alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;

示例

ALTER TABLE employee  CHANGE COLUMN addr new_addr varchar(100);

4、删除一列的内容

 alter table tab_name drop [column] 列名;

示例

ALTER TABLE employee  DROP birth;

5、修改表名

 rename table 表名 to 新表名;

示例

RENAME TABLE employee to new_p;

6、修改该表的字符集

alter table table_name character set utf8;

修改表记录

update tab_name set field1=value1,field2=value2,......[where 语句]

示例

UPDATE employee SET  birthday="1989-10-24" WHERE id=1;

#将yuan的薪水在原有基础上增加1000元。
UPDATE employee_new SET salary=salary+4000 WHERE NAME='yuan';

查

数据库查询

查看db_name的创建方式

SHOW CREATE DATABASE db_name;

查看所有的数据库

SHOW DATABASES;

查看当前数据库

SELECT DATABASE();

表查询

查看表结构

DESC employee;
show COLUMNS FROM employee;

查看当前库中的所有表

show TABLES;

查看建表employee语句

SHOW CREATE TABLE employee;

表记录查询

查询语法

-- 查询语法：

   SELECT *|field1,filed2 ...   FROM tab_name
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数


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

准备环境

CREATE TABLE emp(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20),
    gender   ENUM("male","female","other"),
    age      TINYINT,
    dep      VARCHAR(20),
    city     VARCHAR(20),
   salary    DOUBLE(7,2)
);


INSERT INTO emp (name,gender,age,dep,city,salary) VALUES
                ("yuan","male",24,"教学部","河北省",8000),
                ("egon","male",34,"保安部","山东省",8000),
                ("alex","male",28,"保洁部","山东省",10000),
                ("景丽阳","female",22,"教学部","北京",9000),
                ("张三", "male",24,"教学部","河北省",6000),
                ("李四", "male",32,"保安部","北京",12000),
                ("王五", "male",38,"教学部","河北省",7000),
                ("赵六", "male",19,"保安部","河北省",9000),
                ("猪七", "female",24,"保洁部","北京",9000);

SELECT  * FROM emp;

View Code

查看全表信息

SELECT  * FROM emp;

where子句过滤

-- where字句中可以使用：

         -- 比较运算符：
                        > < >= <= <> !=
                        between 80 and 100 值在10到20之间
                        in(80,90,100) 值是10或20或30
                        like 'yuan%'
                        /*
                        pattern可以是%或者_，
                        如果是%则表示任意多字符，此例如唐僧,唐国强
                        如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
                        */

         -- 逻辑运算符
                        在多个条件直接可以使用逻辑运算符 and or not

练习

1、查询年级大于24的员工信息

SELECT  * FROM emp WHERE age>24;

2、查询教学部的男老师信息

SELECT  * FROM emp WHERE gender='male' AND dep='教学部';

order排序

按指定的列进行，排序的列即可是表中的列名，也可以是select语句后指定的别名。

-- 语法：

select *|field1,field2... from tab_name order by field [Asc|Desc]

         -- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。

练习

1、按年龄从高到低进行排序

SELECT *  FROM emp ORDER BY age DESC;

2、按工资从低到高进行排序

SELECT *  FROM emp ORDER BY salary;

group by 分组查询

根据某个列对结果进行分组，在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。

-- 语法:
  SELECT column_name, function(column_name)
  　　　　　　FROM table_name
  　　　　　　WHERE column_name operator value
  　　　　　　GROUP BY column_name;

练习

1、查询男女员工各有多少人

SELECT gender 性别,count(*) 人数 FROM emp GROUP BY gender;

2、查询各个部门的人数

SELECT dep 部门,COUNT(*) 人数 FROM emp GROUP BY dep;

3、查询各个部门的员工最大的年龄

SELECT dep 部门,MAX(age) 最大年龄 FROM emp GROUP BY dep;

4、查询各个部门年龄最大的员工姓名

SELECT name FROM emp WHERE age in (SELECT max(age) FROM emp GROUP BY dep);

5、查询每个部门的平均工资

SELECT dep 部门,AVG(salary) 平均工资 FROM emp GROUP BY dep;

6、查询教学部的员工最高工资

SELECT dep 部门,MAX(salary) 最高工资 FROM emp WHERE dep='教学部' GROUP BY dep;

SELECT dep 部门,MAX(salary) 最高工资 FROM emp  GROUP BY dep  HAVING dep='教学部';

7、查询平均薪水超过8000的部门

SELECT dep,AVG(salary) FROM emp GROUP BY dep HAVING AVG(salary)>8000;

8、查询每个组的员工姓名

SELECT dep,GROUP_CONCAT(`name`) FROM emp GROUP BY dep;

9、查询公司员工总人数

SELECT COUNT(`name`) FROM emp;

limit 记录，记录条数限制

1、显示前两行内容

SELECT * FROM emp LIMIT 2;

2、从第三行开始往后显示五条内容

SELECT * FROM emp LIMIT 2,5;

regexp 正则表达式

1、显示name以yu开头的行的信息

SELECT * FROM emp WHERE `name` REGEXP '^yu';

2、显示所有姓王的人的信息

SELECT * FROM emp WHERE `name` REGEXP '^王';

3、显示姓名以n结尾的行的信息

SELECT * FROM emp WHERE `name` REGEXP 'n$';

4、显示姓名中n出现了两次的行的信息

SELECT * FROM emp WHERE `name` REGEXP 'n{2}';

多表查询

准备环境

CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    salary DOUBLE(7,2),
    dep_id INT
);

INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2),
                                            ("李四",12000,1),
                                            ("王五",5000,2),
                                            ("赵六",8000,3),
                                            ("猪七",9000,1),
                                            ("周八",7000,4),
                                            ("蔡九",7000,2);

CREATE TABLE dep(
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(20)
);


INSERT INTO dep (name) VALUES ("教学部"),
                              ("销售部"),
                              ("人事部");

View Code

笛卡尔积查询

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

SELECT * FROM emp,dep;

内连接

SELECT * FROM emp,dep WHERE emp.dep_id=dep.id;

SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id;

练习

1、查询李四所在的部门名称

SELECT emp.name 姓名,dep.name 部门 FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name='李四';

2、查询销售部所有员工姓名以及部门名称

SELECT emp.name 姓名,dep.name 部门 FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name='销售部';

外链接

左外连接

在内连接的基础上增加左边有右边没有的结果

SELECT emp.name 姓名,dep.name 部门 FROM emp LEFT JOIN dep ON emp.dep_id=dep.id;

右外链接

在内连接的基础上增加右边有左边没有的结果

SELECT emp.name 姓名,dep.name 部门 FROM emp RIGHT JOIN dep ON emp.dep_id=dep.id;

完整性约束

完整性约束是对字段进行限制，从而符合该字段达到我们期望的效果比如字段含有默认值，不能是NULL等。直观点说：如果插入的数据不满足限制要求，数据库管理系统就拒绝执行操作

唯一约束

唯一约束可以有多个但索引列的值必须唯一，索引列的值允许有空值。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE。

示例：

CREATE TABLE t5(
  id   INT AUTO_INCREMENT,
  name VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY UK_t5_name (name)
);
-- 建表后添加约束:

alter table t5 add constraint UK_t5_name unique (name);

-- 如果不需要唯一约束，则可以这样删除

ALTER TABLE t5 DROP INDEX UK_t5_name;

自增约束

MySQL 每张表只能有1个自动增长字段，这个自动增长字段通常作为主键，也可以用作非主键使用，但是请注意将自动增长字段当做非主键使用时必须必须为其添加唯一索引，否则系统将会报错。

CREATE TABLE t4(
        id INT NOT NULL,
            name VARCHAR(20),
                age INT UNIQUE AUTO_INCREMENT);

主键约束

主键是用于唯一标识一条记录的约束，如同身份证。

主键有两个约束：非空且唯一！

创建主键

CREATE TABLE t1(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

or

CREATE TABLE t2(
  id INT NOT NULL,
  name VARCHAR(20)
);

注意注意：

1、一张表中最多只能有一个主键

2、表中如果没有设置主键，默认设置NOT NULL的字段为主键；此外，表中如果有多个NOT NULL的字段，则按顺序将第一个设置NOT NULL的字段设为主键。

结论：主键一定是非空且唯一，但非空且唯一的字段不一定是主键。

3、主键类型不一定必须是整型

示例：添加和删除主键

语法：

-- 添加主键<br>alter table tab_name add primary key(字段名称,...) <br>
-- 删除主键<br>alter table users drop primary key;

注意，如果主键是AUTO_INCREMENT，需要先取消AUTO_INCREMENT，因为AUTO_INCREMENT只能加在KEY上。

CREATE TABLE test(num INT PRIMARY KEY AUTO_INCREMENT);<br>
 -- 思考，如何删除主键？
    ALTER TABLE test modify id int;   -- auto_increment没了，但这样写主键依然存在，所以还要加上下面这句
    ALTER TABLE test drop primary key;-- 仅仅用这句也无法直接删除主键

复合主键

所谓的复合主键就是指你表的主键含有一个以上的字段。

如果一列不能唯一区分一个表里的记录时，可以考虑多个列组合起来达到区分表记录的唯一性，形式

1、创建

1 create table sc (
2     studentid int,
3     courseid int,
4     score int,
5 primary key (studentno,courseid)
6 );

2、修改

alter table tb_name add primary key (字段1,字段2,字段3);

外键约束

语法

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
                    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

准备环境

-- 子表
CREATE TABLE emp(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  dep_id INT
  -- CONSTRAINT emp_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id) -- 注意外键字段的数据类型必须与关联字段一致
);

-- 主表
CREATE TABLE dep(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

INSERT emp (name,dep_id) VALUES ("alex",1),
                                ("egon",2),
                                ("alvin",2),
                                ("莎莎",1),
                                ("wusir",2),
                                ("女神",2),
                                ("冰冰",3),
                                ("姗姗",3);

INSERT dep (name) VALUES    ("市场部"),
                            ("教学部"),
                            ("销售部");

View Code

添加外键，如果不添加外键，可以随便删除表中的任意数据，比如

mysql> DELETE FROM dep WHERE name="市场部";
Query OK, 1 row affected (0.01 sec)

可以看到删除市场部是成功的，那么没有市场部了，在市场部中的Alex和莎莎怎么办？

所以，为了避免类似操作，我们需要给两张表建立约束，这种约束称为外键约束。外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作

INSERT dep (id,name) VALUES (1,"市场部");  -- 将前面删除的市场部添加进去
 
ALTER TABLE emp ADD CONSTRAINT dep_fk_emp
                    FOREIGN KEY (dep_id)
                    REFERENCES  dep(id);
<br><br>mysql> DELETE FROM dep WHERE name="市场部";<br><br>ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fail

INNODB支持的ON语句

外键约束对子表的含义: 如果在主表中(比如dep)找不到候选键,则不允许在子表(比如emp)上进行insert/update

外键约束对父表的含义: 在主表上进行update/delete以更新或删除在子表中有一条或多条应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的 -- on update/on delete子句

innodb支持的四种形式

cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除

     FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE CASCADE;


set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null ; 要注意子表的外键列不能为not null

     FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE SET NULL;


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

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

posted on 2017-09-13 10:57 鸢沫是只猫阅读(258) 评论(0) 收藏举报

刷新页面返回顶部

鸢沫是只猫

导航

公告

数据库基础命令操作

本章汇总：

增

创建表

增加表记录

删

改

修改数据库

改表

修改表记录

查

数据库查询

表查询

表记录查询

查看全表信息

where子句过滤

练习

order排序

练习

group by 分组查询

练习

limit 记录，记录条数限制

regexp 正则表达式

多表查询

准备环境

笛卡尔积查询

内连接

练习

外链接

左外连接

右外链接

完整性约束

唯一约束

自增约束

主键约束

创建主键

示例：添加和删除主键

复合主键

外键约束

准备环境

INNODB支持的ON语句