mysql之SQL的操作

 

一.什么是数据库
用来存储数据的仓库
二.数据库的优势：
1.存储数据量大
2.方便管理
3.多用户共享
4.独立的数据集合
三. 关系型数据
mysql oracle sql server db2 sybase
四. 非关系型数据库
redis MongoDB

五.数据库操作

1.显示所有数据库

show databases;

2.创建数据库

create database 数据库名 default character set = 'utf8'

3.使用数据库

use 数据库名;

4.删除数据库

drop database 数据库名;

六.表操作

1.创建表

create table 表名(
　　　　id int not null auto_increment primary key,　　　　#id值，非空、递增——唯一性，主键。
　　　　name VARCHAR(60) NOT NULL
　　　　age int(3)   　　　　
　　　　)

 2.复制表

create table 新表名 select * from 原表名;

部分复制：

ceate table 新表名 select id,name from 原表名;

3.查看数据库中可用的表

show tables;

4.删除表

drop table  表名; 

5.清空表 

truncate table 表名;

5.表重命名

rename table 旧表名 to 新表名;

还可以使用：

alter table 旧表名 rename 新表名;

6.更改表结构

alter table 表名 add column 字段varchar(80) not null;//添加字段

alter table 表名 drop address; //删除字段

alter table 表名 change score int(4) not null;//改变字段类型

七.增删改查

1.增

插入数据：
insert into 表名(id,name,score)values(null,'张三',140),(null,'李四',178),(null,'王五',134);
这里的插入多条数据直接在后边加上逗号，直接写入插入的数据即可；主键id是自增的列，可以不用写。

插入检索出来的数据：

insert into 需要插入的表名(name,score) select name,score from 检索的表名;

2.修改

update 表名 set 字段名=新值 where 字段名=旧值;

3.删除

delete from 表名 where 条件字段=条件值;

4.查询

select 字段 from 表名 where 条件字段 (=, !=, <, >,<=,>=,<>,is,in,like) 条件值;

练习题

  

1.创建表

-- create table person(
-- 　　 p_id VARCHAR(10) not null,
-- 　　 p_name VARCHAR(100),
-- 　　 p_age int(5),
-- 　　 p_menpai VARCHAR(50) not null,
-- 　　 p_leader VARCHAR(20) not null,
-- 　　 p_sal DOUBLE(10,2) not null
-- )
-- create table location(
-- 　　 a_id VARCHAR(100) not null,
-- 　　 a_name VARCHAR(50),
-- 　　 a_address VARCHAR(50)
-- )

2.新增表数据

-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p001','张三','100','武当','p001','10000');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p002','张无忌','20','明教','0','8000');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p003','岳不群','40','华山','0','6500');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p004','东方不败','35','日月神教','0','12000');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p005','令狐冲','21','华山','p003','4000');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p006','林平之','18','华山','p003','2000');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p007','左冷禅','50','嵩山','0','10000');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p008','','46','武当','p001','10000');
-- INSERT INTO person(p_id,p_name,p_age,p_menpai,p_leader,p_sal)VALUES('p009','宋远桥','55','武当','p001','6500');
-- INSERT INTO location(a_id,a_name,a_address)VALUES('aaa001','嵩山','河南'),('aaa002','华山','陕西'),('aaa003','武当','湖北'),('aaa004','日月神教','黑木崖');

3.修改表数据

要求:将武当派 张三 修改为 张三丰

update ren set p_name ='张三丰' where p_name='张三'

4.删除你不满意的数据

delete from person where p_id = 'p008';

5.练习

1.查询所有人员

select * from ren  

默认查询表中所有字段
select :关键字,表示查询
* :通配符,表示查询当前表中所有的字段
from :表示从哪个表中进行查询
ren  :具体的表名称

2.只查询人员的姓名和年龄

select p_name,p_age from ren;

按照字段的方式进行查询
注意:要查询哪个字段就把哪个字段的名称写在select与from之间,
多个字段之间用(",")间隔

3.查询年龄为20岁的人有哪些

select * from ren where p_age ='20';

按条件进行查询
where:关键字,表示条件,在where语句之后的都称之为条件.
p_age :表示一个具体的条件字段
= 20 :表示查询当前字段下为20的所有数据.
注意1:所有字段字符串类型的数据都要使用单引号('')进行包裹
注意2:对于数字类型来说,可以加上单引号(''),也可以不使用.

4.查询60岁以下的人员有哪些

select * from ren where p_age < '60'
-- 常见的逻辑运算符 <,>,= <=,>=,<>,!=

5.查询50岁以上并且工资大于8000的人员有哪些?

select * from ren where p_age >50 and p_sal <8000

多条件查询
and :表示逻辑与运算,两个或两个以上的条件同时成立.
or :表示逻辑或运算,表示多个条件时,其中一个条件满足时就成立.
():表示一组条件

6.查询姓[张]的人员有哪些

select * from ren  where p_name like '%张%'
模糊查询
like :表示模糊查询
1.以张开始: like '张%'
2.以张结尾: like '%张'
3.包含张 :  like '%张%'

7.查询哪些人员属于 武当/华山/嵩山

select * from ren where p_menpai in('武当','华山','嵩山')
等同于:select * from ren  where p_menpai ='武当'  or p_menpai ='华山' or p_menpai = '嵩山'
以集合的方式进行查询1
in:关键字,表示集合
注意:在in关键字之后,用()表示集合条件,必须按照当前条件进行查询.

select * from ren where p_menpai NOT in('武当','华山','嵩山')
以集合的方式查询2
not :关键字,表示 非集合的内容查询

8.查询工资在 5000-8900的人员有哪些

select * from ren where p_sal >=5000 and p_sal<= 8900;
select * from ren where p_sal between 5000 and 8900;

按照区间进行查询
注意:between value1 and value2 :表示查询的是value1到value2之间的数据，包括value1和value2

9.查询所有人员,要求按工资倒序排列

select * from ren  ORDER BY p_sal  ASC

排序方式
order by :表示 排序
ASC :表示正序排列
DESC :表示倒序排列

10.查询年龄为20岁人员的领导人是谁

select * from ren where p_leader in (select p_id from ren where p_age in(select p_age from ren where p_age = 20 ))
子条件查询
注意1:where关键字后的具体条件应为一个具体的查询结果
注意2:子条件查询的结果为单一数据时,可以使用(=)进行查询,如果不为单一数据则需要使用(in关键字)
注意3:遇到"="为单一,遇到"in"为集合

----------------聚合函数-------------------
11.查询当前人员中谁的工资最高?
#select max(p_sal) as p_sal from ren ;
select p_name from ren where p_sal =(select max(p_sal) as p_sal from ren)
注意： max() 表示最大值
as 表示 起别名

12.查询当前人员中谁的工资最低?
select p_name from ren where p_sal = (select min(p_sal) from ren)
注意：min()表示最小值

13.查询所有人员的平均工资是多少?
select AVG(p_sal) from ren
注意：avg():表示平均值

14.查询所有人员的工资总和是多少?
select sum(p_sal) from ren
注意 sum() 求和

15.查询目前有多少个人员?
select count(p_id) from ren
注意 count(主键) 表示查询表中数据的总条数

16.查询各门派的平均工资是多少?
select avg(p_sal),p_menpai,p_name from ren GROUP BY p_menpai order by avg(p_sal) desc
注意 group by 表示分组

17.查询武当派最高工资是谁?
select p_name from ren where p_sal = (select max(p_sal) from ren where p_menpai ='武当') and p_menpai ='武当'

18.查询当前武林中有哪些门派?
select p_menpai from ren GROUP BY p_menpai;
select DISTINCT p_menpai,p_name from ren
注意：DISTINCT 表示去重复查询，要求查询的所有字段必须一样，才认为是重复数据

19.查询当前武林中有哪些门派和门派的平均工资是多少?
select p_menpai,avg(p_sal) from ren GROUP BY p_menpai

20.查询当前人员表的中的第3条数据到第7条数据?
select * from ren LIMIT 2,5
注意 limit 表示分页
参数1：表示从第几条开始查询，下标从0开始
参数2：表示每次查询多少条数据

21.查询没有门派的人员有哪些?
select * from ren where p_menpai is null;
表示查询字段为 null 的数据
select * from ren where p_menpai ='';
表示查询字段为 '' 的数据

#update ren set p_menpai = null where p_id='p008'
注意：修改字段为null 时 要写 =

22.查询武当派下有哪些小弟?

select * from ren where p_leader =(select p_id from ren where p_menpai='武当' and p_leader='0')
select * from ren where p_menpai ='武当' and p_leader !='0'

23.查询各门派的工资总和按倒序/正序排列
select sum(p_sal) sal,p_menpai from ren GROUP BY p_menpai ORDER BY sal

24.查询人员并显示门派所在位置(多表联合查询)
select * from ren,wei where ren.p_menpai = wei.a_name
注意：如果多表联合查询不加条件则会出现（笛卡尔乘积）
注意：在使用多表联合查询时，一定要加条件
结果：符合两个表条件的结果

25.查询人员表,如果人员门派存在位置则显示位置信息,不存在则不显示位置
select * from ren LEFT JOIN wei on ren.p_menpai = wei.a_name

左连接查询
注意：on 表示条件 专门配置 left join 来使用
特点：左表数据全要，右表的数据与左表数据相匹配则显示，不匹配则以NULL填充


26.查询位置表,如果人员的门派有位置信息则显示人员,没有则不显示.

select * from ren RIGHT JOIN wei on ren.p_menpai = wei.a_name


27.查询登记了地理位置的门派人员信息

select * from ren INNER JOIN wei on ren.p_menpai = wei.a_name

****数据类型****

　　　　　bit[(M)]
            二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
        tinyint[(m)] [unsigned] [zerofill]
            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127.
            无符号：
　　　　　　　　　　～ 255
            特别的： MySQL中无布尔值，使用tinyint(1)构造。
        int[(m)][unsigned][zerofill]
            整数，数据类型用于保存一些范围的整数数值范围：
                有符号：
                    -2147483648 ～ 2147483647
                无符号：
　　　　　　　　　　　　～ 4294967295
            特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
                有符号：
                    -9223372036854775808 ～ 9223372036854775807
                无符号：
　　　　　　　　　　 ～  18446744073709551615
        decimal[(m[,d])] [unsigned] [zerofill]
            准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
            特别的：对于精确数值计算时需要用此类型
                   decaimal能够存储精确值的原因在于其内部按照字符串存储。
        FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
            单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
                无符号：
                    -3.402823466E+38 to -1.175494351E-38,
                    1.175494351E-38 to 3.402823466E+38
                有符号：
                    1.175494351E-38 to 3.402823466E+38
            **** 数值越大，越不准确 ****
        DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
            双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
                无符号：
                    -1.7976931348623157E+308 to -2.2250738585072014E-308
                    2.2250738585072014E-308 to 1.7976931348623157E+308
                有符号：
                    2.2250738585072014E-308 to 1.7976931348623157E+308
            **** 数值越大，越不准确 ****
        char (m)
            char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
            PS: 即使数据小于m长度，也会占用m长度
        varchar(m)
            varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
            注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
        text
            text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

        mediumtext
            A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
        longtext
            A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
        enum
            枚举类型，
            An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
            示例：
                CREATE TABLE shirts (
                    name VARCHAR(40),
                    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                );
                INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
        set
            集合类型
            A SET column can have a maximum of 64 distinct members.
            示例：
                CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
                INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
        DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）
        TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）
        YEAR
            YYYY（1901/2155）
        DATETIME
            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
        TIMESTAMP
            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）