mysql中的基本逻辑对象
mysql有这么几种对象
mysqld--->库---->表---->记录(由行和列组成)一条记录中的一列叫做字段
什么是关系型数据库
表与表发生关系，叫关系型
部门表（部门ID、部门名称）
雇员表（员工ID、员工姓名、部门ID）
可以通过上面的例子知道在雇员表中写入了部门id这个字段在部门表中也存在，2个表就通过部门id这个字段联系起来了
操作数据库的4种方式(使用SQL语句):
- action:
- 查询数据(最多的动作) 就是查询数据
- 修改数据
- 删除数据
- 增加数据
常见的SQL标准
mysql=国际标准化＋mysql官方的自定义标准
pl/sql=国际标准化＋oracle官方的自定义标准
Tsql=国际标准化＋microsoft官方的自定义标准
总结:
所有大家在学习一些SQL语句的时候会出现 我虽然没有学过这个数据库，但是它的语法我基本也能看的懂，就是因为SQL有国际标准化的语句
常用的SQL语句
- create语句
- 创建数据库
mysql> create database ceshi1; #创建数据库ceshi1
Query OK, 1 row affected (0.00 sec)
mysql> show create database ceshi1; 查看数据库是如何建立的
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| ceshi1 | CREATE DATABASE `ceshi1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database ceshi2 default charset utf8; 设置默认的字符编码集为utf-8
Query OK, 1 row affected (0.00 sec)
mysql> create database IF NOT EXISTS ceshi1 default charset utf8; #创建一个数据库如果不存在就创建，如果存在就告警
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings; #查看最后一次的waring的信息
+-------+------+-------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------+
| Note | 1007 | Can't create database 'ceshi1'; database exists |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)
创建表 二维表(表由行和列组成，表必须存在于某一个库当中)
2个变量
varchar ----不定长 比如我设一个字段为50个字符 那么我只占用了30个字符 那么数据库给我计算的时候只给我算30个字符
char ---定长 比如我设置一个字段为50个字符，我只占用了10个字符，但是数据库还是会给我算50个字符的空间的占用
mysql> use ceshi1; #进入ceshi1库
Database changed
mysql> create table ceshi1.t1(id int,name varchar(50),salary decimal(10,2),dept varchar(50));
Query OK, 0 rows affected (0.02 sec)
解析: 在ceshi1库中创建t1表 并创建4个字段
1. 第一个字段 id 设置int类型
2. 第二个字段 name 指定字符长度为50
3. 第三个字段 salary 指定保留小数点2位
4. 第四个字段 dept 执行字符长度为50
mysql> DESCRIBE t1; #查看表结构 可以看到有4个字段
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| dept | varchar(50) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from ceshi1.t1; 查询表所有内容
Empty set (0.00 sec)
mysql> insert into db01.t1 set id=1, name='zhang3',salary=5000,dept='生产部'; 插入数据
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> select * from ceshi1.t1; #可以看到数据已经插入进去了
+------+--------+---------+-----------+
| id | name | salary | dept |
+------+--------+---------+-----------+
| 1 | zhang3 | 5000.00 | 生产部 |
+------+--------+---------+-----------+
1 row in set (0.00 sec)
mysql> show create table ceshi1.t1; #可以查看表示任何创建的
| Table | Create Table
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`dept` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 可以查出使用神没存储引擎以及语言编码
1 row in set (0.00 sec)
mysql> create table ceshi1.t2 ( 列数多的情况可以使用一下方式创建表
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(50) DEFAULT NULL,
-> `salary` decimal(10,2) DEFAULT NULL,
-> `dept` varchar(50) DEFAULT NULL
-> );
Query OK, 0 rows affected (0.11 sec)
- 创建用户
创建一个用户 user01用户名 123密码
mysql> create user user01@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> set password for user01@'localhost'=password('123'); --设置用户的密码
mysql> create user u01@'localhost' ; --创建一个用户
mysql> select user,host,password from mysql.user; －－查询用户是否创建成功
| root | localhost | *425F1EBD8227A2B1E01C475B523E27A592CFF59A |
| root | vagrant-centos65.vagrantup.com | |
| root | 127.0.0.1 | |
| | localhost | |
| | vagrant-centos65.vagrantup.com | |
| user01 | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
mysql> show grants; #查看用户权限
| Grants for root@localhost |
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*425F1EBD8227A2B1E01C475B523E27A592CFF59A' WITH GRANT OPTION |
1 row in set (0.00 sec)
drop --删除数据库对象(表/库/用户)
删除表/库
mysql> use ceshi1
mysql> show tables;
mysql> drop table t1; --删除表
mysql> drop table t2;
mysql> use ceshi1 --验证表是否删除成功
mysql> show tables
mysql> drop database ceshi1; --删除库
Query OK, 0 rows affected (0.00 sec)
mysql> drop database ceshi1 ;
mysql> show databases; --验证库是否删除成功
- 删除用户:
mysql> drop user user01@'localhost'; --删除用户
Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user where user='user01'; --验证用户是否删除成功
mysql> drop user ''@'192.168.1.1'; 删除一个匿名用户
- alter（修改已经存在数据库对象（库/表/列）的属性）
mysql> ALTER DATABASE db01 DEFAULT CHARACTER SET latin1; －－修改数据库语言编码
Query OK, 1 row affected (0.05 sec)
mysql> show create database db01; －－验证修改是否成功
修改表的属性：
mysql> create table t2(name varchar(50));
mysql> ALTER TABLE t2 ADD id int FIRST; --增加一列成为第一列
mysql> ALTER TABLE t2 add id2 int AFTER id; --在id后面增加一列叫id2
mysql> alter table t2 drop id2; --删除id2这个列
mysql> alter table t2 change id ID bigint; --修改列名和数据类型
mysql> alter table t2 modify ID int; --修改列的数据类型
mysql> alter table t2 rename t20; --重命名表
mysql>show engines; --查看数据库有哪些存储引擎
mysql> alter table t20 engine MyISAM; --修改表的存储引擎
mysql> show create table t20; --查看修改存储引擎是否成功
mysql> alter table t20 DEFAULT CHARSET=utf8; －－修改表的语言编码
- insert 插入记录
mysql> insert into ceshi1.t1 set id=1,name='zhang3'; --向指定列插入数据
mysql> insert into ceshi1.t1 values(2,'li4'),(3,'wang5'),(4,'zhao6'); --插入多条记录
mysql> insert into ceshi1.t1(NAME) values('test01'),('test02'),('test03'); --不写列名，表示按顺序往所有的列插入数据
mysql> select * from ceshi1.t1 where id<5; 查出前4张表
mysql> insert into ceshi1.t2 select * from ceshi1.t1 where id<5; 查出前4张表，插入到当前表（注意表结构要一致）
mysql> rename tables t1 to t01;
- update 更新/修改记录
mysql> update db01.t1 set id=5 where NAME='test01';
mysql> update db01.t1 set id=6 where NAME='test02';
mysql> update db01.t1 set id=7 where NAME='test03';
- delete/truncate 删除和清空表
mysql> delete from mysql.user where user=''; －－删除mysql中的匿名用户
mysql> flush privileges;
mysql> create table t3 like t1; 复制表结构不复制数据
mysql> insert into t3 select * from t1;
mysql> delete from t3; --删除整个表,一行一行删除,所有的操作都会被记录至事务日志中
mysql> insert into t3 select * from t1;
mysql> delete from t3 where id=1; --指定条件删除
mysql> truncate from t2 where id=2; --报错
mysql> truncate t2; --一次性全部删除,不会被记录至事务日志中.
- 查询语句 select--查询
通配符：
% 匹配0个或任意多个字符
_ 匹配一个字符
＝ 精确匹配
like 模糊匹配
regexp(^ . .* .....) 使用正则表达式来匹配
排序：
order by 排序
asc 升序排列结果
desc 降序排列结果
group by 聚合
distinct 去除重复的行
- 聚合函数
|函数 | 作用 |
|-----------|---------|
| COUNT | 计算表中的记录（行数） |
| SUM | 计算表中数值列的数据合计值|
| AVG | 计算表中数值列的数据平均值 |
|MAX | 求出表中任意列中数据的最大值|
|MIN |求出表中任意列中数据的最小值|
mysql> select * from t1 id;
mysql> select * from t1 id having id <= 2;
gruop by 不能用where语句 用having语句
mysql> use ceshi2
mysql> create table ceshi2.t1(id int ,name varchar(50) ,math tinyint, english tinyint);
mysql> insert into ceshi2.t1 set id=1,name='zhangsan',math=55,english=66;
mysql> insert into ceshi2.t1 set id=2,name='lisi',math=66,english=77;
mysql> insert into ceshi2.t1 set id=3,name='wangwu',math=65,english=30;
mysql> insert into ceshi2.t1 set id=2,name='li04',math=88,english=99;
mysql> insert into ceshi2.t1 set id=3,name='wang5',math=75,english=73;
mysql> insert into ceshi2.t1 set id=4,name='zhao6',math=75,english=73;
mysql> insert into ceshi2.t1 set id=5,name='liu3',math=85,english=43;
mysql> select * from ceshi2.t1; --查询表中所有的列对应的值（全表扫描）
mysql> select id,name from t1; --查询表中指定列
mysql> select host,password,user from mysql.user; --查询表中指定列
mysql> select host as '主机名',password as '密码',user as '用户名' from mysql.user; --给列取别名，增加可读性
mysql> select * from t1 where name='i';
mysql> select * from t1 where name like 'i';
mysql> select * from t1 where name like '%i%'; --模糊匹配
mysql> select * from t1 where name like '____';
mysql> select * from t1 where id=2;
mysql> select * from t1 where name='wang5'; --精确匹配
mysql> select * from t1 where english < 60;
mysql> select * from t1 where name regexp '.*[0-9]?.*' ; --支持正则表达式
mysql> select * from t1 where name regexp '.*[0-9]+.*' ; 任意数字一次或一次以上
mysql> select * from t1 where name regexp '.*[0-9]{2}.*' ;
mysql> select user,password,host from mysql.user where host regexp '^l'; --支持正则表达式
mysql> select user,password,host from mysql.user where host regexp '([0-9]{1,3}\.){3}([0-9]{1,3})';
排序
mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id asc; 升序
mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id desc; 降序
去除重复行 distinct
mysql> select distinct id from t1 ;
聚合 group by 聚合以第一个为标准
mysql> select * from t1 group by id;
mysql> select * from t1 group by id having id <= 2;
gruop by 不能用where语句 用having语句
- 分页函数(limint)指定只显示前几条数据
mysql> select * from t1 limit 10; --显示前10行
mysql> select * from t1 limit 10,10; --显示11至20行
mysql> select * from t1 limit 1; 显示第1行
mysql> select * from t1 limit 0,2; 显示第1,2行
mysql> select * from t1 limit 2,2; 显示3,4行
起始位置，偏移量 第二行的下2行
mysql> select * from t1 limit 4,2; 显示5,6行
mysql> select * from t1 limit 6,2; 显示第七行
mysql> select * from t1 order by english desc limit 3;
显示英语前三名
mysql> select name,(math+english) as sum from t1 order by sum desc;
显示总分，以降序排列
mysql> select name,(math+english) as sum from t1 order by sum desc limit 1;
显示总分第一名
mysql> select name,max((math+english)) from t1;
显示总分第一名
mysql> select name,max(math+english) from t1 order by (math+english) desc;
显示总分第一名
mysql> select name,english from t1;
mysql> select max(english) from t1;
显示英语的最高分
mysql> select min(english) from t1;
显示英语的最低分
mysql> select name,english from t1 where english in (select max(english) from t1);
子查询
显示英语的最高分
- and or not 逻辑运算
1.and
mysql> select * from t1 where math >= 60 and english >= 60;
2.or
mysql> select * from t1 where math >= 60 or english >= 60; 其中一科及格
3.not
mysql> select * from t1 where math >= 60 and not english >= 60;
+------+--------+------+---------+
| id | name | math | english |
+------+--------+------+---------+
| 3 | wangwu | 65 | 30 |
| 5 | liu3 | 85 | 43 |
+------+--------+------+---------+
2 rows in set (0.00 sec)
- mysql常用函数 sum() avg() max() min() count()
mysql> select name,sum(math),sum(english) from t1;
+----------+-----------+--------------+
| name | sum(math) | sum(english) |
+----------+-----------+--------------+
| zhangsan | 215 | 252 |
+----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select name,avg(math),avg(english) from t1;
+----------+-----------+--------------+
| name | avg(math) | avg(english) |
+----------+-----------+--------------+
| zhangsan | 53.7500 | 63.0000 |
+----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select name,max(english) from t1;
+----------+--------------+
| name | max(english) |
+----------+--------------+
| zhangsan | 90 |
+----------+--------------+
1 row in set (0.00 sec)
mysql> select name,min(english) from t1;
+----------+--------------+
| name | min(english) |
+----------+--------------+
| zhangsan | 33 |
+----------+--------------+
1 row in set (0.00 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select max(english) - min(math) from t1; --英语的最高分与数学最低的差距
+--------------------------+
| max(english) - min(math) |
+--------------------------+
| 50 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select * from t1 order by english desc limit 3;
+------+----------+------+---------+
| id | name | math | english |
+------+----------+------+---------+
| 1 | zhangsan | 70 | 90 |
| 4 | lisi02 | 55 | 69 |
| 2 | lisi | 50 | 60 |
+------+----------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from t1 order by english asc limit 1,2; 显示第二名和第三名
+------+--------+------+---------+
| id | name | math | english |
+------+--------+------+---------+
| 4 | lisi02 | 55 | 69 |
| 2 | lisi | 50 | 60 |
+------+--------+------+---------+
2 rows in set (0.00 sec)
mysql> select * from ceshi1.t1 where math >= 60 or english >=60 ;
+------+----------+------+---------+
| id | name | math | english |
+------+----------+------+---------+
| 1 | zhangsan | 70 | 90 |
| 2 | lisi | 50 | 60 |
| 4 | lisi02 | 55 | 69 |
+------+----------+------+---------+
3 rows in set (0.00 sec)
- 复制表结构 表结构的复制
mysql> create table t3 like t1; --复制表结构
mysql> create database db03;
mysql> use db03
mysql> create table t1(id int,name varchar(50));
mysql> create table t2(id int,socre int);
mysql> insert into t1 set id=1,name='lee';
mysql> insert into t1 set id=2,name='zhang';
mysql> insert into t1 set id=4,name='wang';
mysql> insert into t2 set id=1,socre='90';
mysql> insert into t2 set id=2,socre='100';
mysql> insert into t2 set id=3,socre='90';
mysql> select * from t1;
mysql> select * from t1,t2; 笛卡尔集,2张表的乘方
+------+-------+------+-------+
| id | name | id | socre |
+------+-------+------+-------+
| 1 | lee | 1 | 90 |
| 2 | zhang | 1 | 90 |
| 4 | wang | 1 | 90 |
| 1 | lee | 2 | 100 |
| 2 | zhang | 2 | 100 |
| 4 | wang | 2 | 100 |
| 1 | lee | 3 | 90 |
| 2 | zhang | 3 | 90 |
| 4 | wang | 3 | 90 |
+------+-------+------+-------+
9 rows in set (0.00 sec)
mysql>create table db03.t3 (id int,name varchar(50));
mysql> insert into t3 select * from t1; --复制记录
mysql> select * from t3;
+------+-------+
| id | name |
+------+-------+
| 1 | lee |
| 2 | zhang |
| 4 | wang |
+------+-------+
3 rows in set (0.00 sec)
多表查询
mysql> select t1.id,t1.name,t2.socre from t1,t2 where t1.id=t2.id;
+------+-------+-------+
| id | name | socre |
+------+-------+-------+
| 1 | lee | 90 |
| 2 | zhang | 100 |
+------+-------+-------+
2 rows in set (0.00 sec)
- 表的连接:纵向连接/内连接/左连接/右连接
常规查表:
mysql> select * from t1,t2;
mysql> select * from t1,t2 where t1.id=t2.id;
+------+-------+------+-------+
| id | name | id | socre |
+------+-------+------+-------+
| 1 | lee | 1 | 90 |
| 2 | zhang | 2 | 100 |
+------+-------+------+-------+
2 rows in set (0.00 sec)
左连接（以左表为标准连接右表）:
letft join=left [outer] join
mysql> select * from t1 left outer join t2 ON t1.id=t2.id;
+------+-------+------+-------+
| id | name | id | socre |
+------+-------+------+-------+
| 1 | lee | 1 | 90 |
| 2 | zhang | 2 | 100 |
| 4 | wang | NULL | NULL |
+------+-------+------+-------+
3 rows in set (0.00 sec)
右连接（以右表为标准连接左表）:
right join= right outer join
mysql> select * from t1 right outer join t2 ON t1.id=t2.id;
+------+-------+------+-------+
| id | name | id | socre |
+------+-------+------+-------+
| 1 | lee | 1 | 90 |
| 2 | zhang | 2 | 100 |
| NULL | NULL | 3 | 90 |
+------+-------+------+-------+
3 rows in set (0.00 sec)
内连接:取多表之间的交集
mysql> select * from t1 inner join t2 on t1.id=t2.id;
+------+-------+------+-------+
| id | name | id | socre |
+------+-------+------+-------+
| 1 | lee | 1 | 90 |
| 2 | zhang | 2 | 100 |
+------+-------+------+-------+
2 rows in set (0.00 sec)
纵向连接:
mysql> select * from t1 union select * from t2;
+------+-------+
| id | name |
+------+-------+
| 1 | lee |
| 2 | zhang |
| 4 | wang |
| 1 | 90 |
| 2 | 100 |
| 3 | 90 |
+------+-------+
6 rows in set (0.00 sec)
- 列类型
整数
create table t1(id tinyint(2), name varchar(50))
· TINYINT[(M)] [UNSIGNED] [ZEROFILL] [not null] [comment]
很小的整数。带符号的范围是-128到127。无符号的范围是0到255。
· MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。
· INT[(M)] [UNSIGNED] [ZEROFILL]
普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
· INTEGER[(M)] [UNSIGNED] [ZEROFILL]
这是INT的同义词。
· BIGINT[(M)] [UNSIGNED] [ZEROFILL]
大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。
mysql> use db01
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
mysql> create table t4(id int ,name char(50),salary decimal(10,2));
mysql> desc t4;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t4 set id='test01' ,name='asdasdad',salary='dadadadasd' ;
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'test01' for column 'id' at row 1 |
| Warning | 1366 | Incorrect decimal value: 'dadadadasd' for column 'salary' at row 1 |
+---------+------+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from t4;
+------+----------+--------+
| id | name | salary |
+------+----------+--------+
| 0 | asdasdad | 0.00 |
+------+----------+--------+
1 row in set (0.00 sec)
=============
mysql> create table t5(id tinyint);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t5 set id=-10;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+
| id |
+------+
| -10 |
+------+
1 row in set (0.00 sec)
mysql> insert into t5 set id=-300;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t5;
+------+
| id |
+------+
| -10 |
| -128 |
+------+
2 rows in set (0.00 sec)
mysql> alter table t5 modify id tinyint unsigned;
mysql> insert t4 set id=2500;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t5;
+------+
| id |
+------+
| 0 |
| 0 |
| 250 |
| 255 |
+------+
4 rows in set (0.00 sec)
浮点数 decimal指定小数点的位数
mysql> alter table t5 add salary decimal(7,2) unsigned after id;
mysql> update t5 set salary=1000000 where id=255;
mysql> select * from t5;
+------+-----------+
| id | salary |
+------+-----------+
| 0 | NULL |
| 0 | NULL |
| 250 | NULL |
| 255 | 999999.99 |
日期和时间类型概述
mysql> alter table t5 add date date;
mysql> alter table t5 add datetime datetime;
mysql> alter table t5 add time TIMESTAMP;
mysql> insert into t5(id,salary,date,datetime) values(1,10000,'2011-09-01','2011-09-01 11:28:01');
mysql> select * from t5;
+------+----------+---------------------+------------+---------------------+
| id | salary | time | date | datetime |
+------+----------+---------------------+------------+---------------------+
| 1 | 10000.00 | 2011-09-01 11:28:27 | 2011-09-01 | 2011-09-01 11:28:01 |
+------+----------+---------------------+------------+---------------------+
1 row in set (0.00 sec)
