MySQL之基本操作

2018-06-23 13:27:50来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

 

二. MySQL基本管理

#设置密码
mysqladmin -uroot -p password ""
#破解密码1
mysql --skip-grant-tables
#修改密码1
update mysql.user set authentication_string=password('123') where user='root';
flush privileges;
#破解密码(配置文件)
grant all on 库.表 to '用户名‘@'192.168.1.%' identified by '123';
grant all on 库.表 to '用户名‘@'localhost' identified by '123';
grant all on 库.表 to '用户名‘@'%' identified by '123';

三. MySQL库操作

3.1 系统数据库

information_schema: 虚拟库,不占用磁盘空间,存储的是数据库启动后的一些参数,如用户表信息、列信息、权限信息、字符信息等
performance_schema: MySQL 5.5开始新增一个数据库:主要用于收集数据库服务器性能参数,记录处理查询请求时发生的各种事件、锁等现象 
mysql: 授权库,主要存储系统用户的权限信息
test: MySQL数据库系统自动创建的测试数据库

3.2 库操作语法

create database db1 charset utf8;

show databases;
show create database db1;
select database();

alter database db1 charset latin1;

drop database db1;

三. MySQL表操作

3.1 搜索引擎介绍

引擎链接

3.2 数据类型

3.2.1 数字类型

a.整形类型

注意:为该类型指定宽度时,仅仅只是指定查询结果的显示宽度,与存储范围无关,存储范围如下

其实我们完全没必要为整数类型指定显示宽度,使用默认的就可以了

默认的显示宽度,都是在最大值的基础上加1

b.浮点类型

定点数类型  DEC等同于DECIMAL  

浮点类型:FLOAT DOUBLE

3.2.2 字符串类型

#char类型:定长,简单粗暴,浪费空间,存取速度快
    字符长度范围:0-255(一个中文是一个字符,是utf8编码的3个字节)
    存储:
        存储char类型的值时,会往右填充空格来满足长度
        例如:指定长度为10,存>10个字符则报错,存<10个字符则用空格填充直到凑够10个字符存储

    检索:
        在检索或者说查询时,查出的结果会自动删除尾部的空格,除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';)

#varchar类型:变长,精准,节省空间,存取速度慢
    字符长度范围:0-65535(如果大于21845会提示用其他类型 。mysql行最大限制为65535字节,字符编码为utf-8:https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)
    存储:
        varchar类型存储数据的真实内容,不会用空格填充,如果'ab  ',尾部的空格也会被存起来
        强调:varchar类型会在真实数据前加1-2Bytes的前缀,该前缀用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字,正好符合mysql对row的最大字节限制,即已经足够使用)
        如果真实的数据<255bytes则需要1Bytes的前缀(1Bytes=8bit 2**8最大表示的数字为255)
        如果真实的数据>255bytes则需要2Bytes的前缀(2Bytes=16bit 2**16最大表示的数字为65535)
    
    检索:
        尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容

其他字符串类型

TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
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.
BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB 
BINARY系列 BINARY VARBINARY

日期类型

YEAR
            YYYY(1901/2155)

        DATE
            YYYY-MM-DD(1000-01-01/9999-12-31)

        TIME
            HH:MM:SS('-838:59:59'/'838:59:59')

        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 年某时)
在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒,但在某些情况下,会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。

1.DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。

2.DATETIME存储时间与时区无关,TIMESTAMP存储时间与时区有关,显示的值也依赖于时区。在mysql服务器,操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间,TIMESTAMP的存储空间为4字节。因此,TIMESTAMP比DATETIME的空间利用率更高。

4.DATETIME的默认值为null;TIMESTAMP的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP),如果不做特殊处理,并且update语句中没有指定该列的更新值,则默认更新为当前时间。
datetime与timestamp的区别

枚举与集合类型

enum 单选 只能在给定的范围内选一个值,如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3...)

3.3 约束

3.3.1 not null 与default

ot null - 不可空
null - 可空

default-默认值

create table t10(name char(16) not null default 'hahaha');

3.3.2 unique

设置唯一约束

#方法一
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);
#方法二:
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);
#不设置primary时,not null+unque自动成为primary key
create table t1(id int not null unique);

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
not null+unique
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

3.3.3 primary key

rimary key字段的值不为空且唯一

一个表中可以:

单列做主键
多列做主键(复合主键)

但一个表内只能有一个主键primary key

3.3.4 auto_increment

 约束字段为自动增长,被约束的字段必须同时被key约束

3.3.5 foreign key

 通过外键把一个表关联到另一个表

#表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键,关联父表(department主键id),同步更新,同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade 
)engine=innodb;

#先往父表department中插入记录
insert into department values
(1,'欧德博爱技术有限事业部'),
(2,'艾利克斯人力资源部'),
(3,'销售部');

#再往子表employee中插入记录
insert into employee values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)
;
#删父表department,子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
+----+-------+--------+
| id | name  | dpt_id |
+----+-------+--------+
|  1 | egon  |      1 |
|  2 | alex1 |      2 |
|  3 | alex2 |      2 |
|  4 | alex3 |      2 |
+----+-------+--------+


#更新父表department,子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
+----+-------+--------+
| id | name  | dpt_id |
+----+-------+--------+
|  1 | egon  |      1 |
|  3 | alex2 |  22222 |
|  4 | alex3 |  22222 |
|  5 | alex1 |  22222 |
+----+-------+--------+
#多对多
create table book(
    id int primary key auto_increment,
    name char(32),
    type char(64)
);
create table author(
    id int primary key auto_increment,
    name char(32)
);

create table author2book(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on delete cascade
    on update cascade,
    foreign key(author_id) references author(id)
    on delete cascade
    on update cascade
);
多对多
#foreign key 加unique
一对一

3.4表操作

use db1;
select database();

create table t1(
  id int,
  name char
)engine=innodb

show tables;
show create table t1;
desc t1;

alter table t1 add age int;
alter table t1 drop age;
alter table t1 modify name char(4);
alter table t1 change name HAHAHA char(4);

drop table t1;

复制表

复制表结构+记录 (key不会复制: 主键、外键和索引)
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假,查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees

四. 数据操作

4.1 insert

. 插入完整数据(顺序插入)
    语法一:
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二:
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法:
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法:
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法:
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

4.2 update

语法:
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例:
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

4.3 delect

语法:
    DELETE FROM 表名 
        WHERE CONITION;

示例:
    DELETE FROM mysql.user 
        WHERE password=’’;

4.4 select

4.4.1 单表查询

a. 基本语法

select 字段1,字段2,... from 表

  where 约束条件

  group by 分组字段

  having 过滤条件

  order by 排序字段

  limit 显示条数;

b. 简单查询

company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);


#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入记录
#三个部门:教学,销售,运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
准备表和数据

 

4.4.2多表查询

#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
准备表

a. 多表连接查询

 

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

b.子查询

#带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名
select name from department
    where id in 
        (select dep_id from employee group by dep_id having count(id) <=1);
#带比较运算符的子查询
#比较运算符:=!=>>=<<=<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 
#带EXISTS关键字的子查询

 

ELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
select语句关键字语法
(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>
select语句关键字执行顺序

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL Schema与数据类型的优化

下一篇:MYSQL问题解决方案:Access denied for user &#39;root&#39;@&a