数据库

数据库简介

  1、数据库

　　数据库（database，DB）是指长期存储在计算机内的，有组织，可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储，具有较小的冗余，较高的数据独立性和易扩展性，并可为各种用户共享。

  2、数据库管理系统软件

　　数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。

　　数据库管理系统是数据库系统的核心，是管理数据库的软件。数据库管理系统就是实现把用户意义下抽象的逻辑数据处理，转换成为计算机中具体的物理数据处理的软件。有了数据库管理系统，用户就可以在抽象意义下处理数据，而不必顾及这些数据在计算机中的布局和物理位置。

常见的数据库管理软件：甲骨文的oracle,IBM的db2，sql server, Access,Mysql(开源，免费，跨平台).

  3、数据库系统

 　　数据库系统DBS（Data Base System，简称DBS）通常由软件、数据库和数据管理员组成。其软件主要包括操作系统、各种宿主语言、实用程序以及数据库管理系统。数据库由数据库管理系统统一管理，数据的插入、修改和检索均要通过数据库管理系统进行。数据管理员负责创建、监控和维护整个数据库，使数据能被任何有权使用的人有效使用。

 

 

 

  4、存储引擎 

　　存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

　　SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询。 

一种类型表明的是一种存储方式
存储引擎：就是表的类型，不同的类型就会对应不同的处理机制去处理他
事务：就是要么同时成功，要么同时不成功

使用存储引擎

方法1：建表时指定 

存储引擎
1.create table t1(id int) engine = innodb 会出现2个文件
2.create table t2(id int) engine = myisam 会出现3个文件（速度比上面的快），但是我们还是用innodb存储 
3.create table t3(id int) engine = memory 只有表结构没有表数据,是创建到内存中的
4.create table t4(id int) engine = blackhole  黑洞，吃数据（数据存进去就没有了，存进去就没有了）

方法2：在配置文件(\s)中指定默认的存储引擎

/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1

 

 

mysql

 mysql的管理

1、安装

linux:

#二进制rpm包安装
yum -y install mysql-server mysql
二进制rpm包安装
1.解压tar包
cd /software
tar -xzvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql-5.6.21

2.添加用户与组
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql mysql-5.6.21

3.安装数据库
su mysql
cd mysql-5.6.21/scripts
./mysql_install_db --user=mysql --basedir=/software/mysql-5.6.21 --datadir=/software/mysql-5.6.21/data

4.配置文件
cd /software/mysql-5.6.21/support-files
cp my-default.cnf /etc/my.cnf
cp mysql.server /etc/init.d/mysql
vim /etc/init.d/mysql   #若mysql的安装目录是/usr/local/mysql,则可省略此步
修改文件中的两个变更值
basedir=/software/mysql-5.6.21
datadir=/software/mysql-5.6.21/data

5.配置环境变量
vim /etc/profile
export MYSQL_HOME="/software/mysql-5.6.21"
export PATH="$PATH:$MYSQL_HOME/bin"
source /etc/profile

6.添加自启动服务
chkconfig --add mysql
chkconfig mysql on

7.启动mysql
service mysql start

8.登录mysql及改密码与配置远程访问
mysqladmin -u root password 'your_password'     #修改root用户密码
mysql -u root -p     #登录mysql,需要输入密码
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;     #允许root用户远程访问
mysql>FLUSH PRIVILEGES;     #刷新权限
源码安装mysql
1. 解压
tar zxvf  mariadb-5.5.31-linux-x86_64.tar.gz   
mv mariadb-5.5.31-linux-x86_64 /usr/local/mysql //必需这样，很多脚本或可执行程序都会直接访问这个目录

2. 权限
groupadd mysql             //增加 mysql 属组 
useradd -g mysql mysql     //增加 mysql 用户 并归于mysql 属组 
chown mysql:mysql -Rf  /usr/local/mysql    // 设置 mysql 目录的用户及用户组归属。 
chmod +x -Rf /usr/local/mysql    //赐予可执行权限 

3. 拷贝配置文件
cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf     //复制默认mysql配置 文件到/etc目录 

4. 初始化
/usr/local/mysql/scripts/mysql_install_db --user=mysql          //初始化数据库 
cp  /usr/local/mysql/support-files/mysql.server    /etc/init.d/mysql    //复制mysql服务程序 到系统目录 
chkconfig  mysql on     //添加mysql 至系统服务并设置为开机启动 
service  mysql  start  //启动mysql

5. 环境变量配置
vim /etc/profile   //编辑profile,将mysql的可执行路径加入系统PATH
export PATH=/usr/local/mysql/bin:$PATH 
source /etc/profile  //使PATH生效。

6. 账号密码
mysqladmin -u root password 'yourpassword' //设定root账号及密码
mysql -u root -p  //使用root用户登录mysql
use mysql  //切换至mysql数据库。
select user,host,password from user; //查看系统权限
drop user ''@'localhost'; //删除不安全的账户
drop user root@'::1';
drop user root@127.0.0.1;
select user,host,password from user; //再次查看系统权限，确保不安全的账户均被删除。
flush privileges;  //刷新权限

7. 一些必要的初始配置
1）修改字符集为UTF8
vi /etc/my.cnf
在[client]下面添加 default-character-set = utf8
在[mysqld]下面添加 character_set_server = utf8
2）增加错误日志
vi /etc/my.cnf
在[mysqld]下面添加：
log-error = /usr/local/mysql/log/error.log
general-log-file = /usr/local/mysql/log/mysql.log
3) 设置为不区分大小写，linux下默认会区分大小写。
vi /etc/my.cnf
在[mysqld]下面添加：
lower_case_table_name=1

修改完重启：#service  mysql  restart
源码安装mariadb
一、MySQL5.6.36安装前准备

（1）克隆一个模板机器（使用centos6），克隆完做快照
（2）IP 10.0.0.52 主机名db02
（3）iptables   selinux
（4）下载好5.6.36
（5）安装依赖包
     yum  install  -y  ncurses-devel libaio-devel
（6）安装cmake
        yum install cmake –y
（7）创建用户
        useradd -s /sbin/nologin -M mysql
        id mysql

二、MySQL下载安装

（0）创建软件下载目录：
mkdir -p /server/tools
cd /server/tools/
（1）下载并上传到/server/tools
https://www.mysql.com/downloads/

社区版MySQL Community Server

5.6    5.6.3x   5.6.34  5.6.36  5.6.38  

发布超过6-12的版本

5.7    5.7.17以后

（2）解压：
cd /server/tools
tar xf mysql-5.6.36.tar.gz
（3）安装：
cd mysql-5.6.36

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.36 \
-DMYSQL_DATADIR=/application/mysql-5.6.36/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.36/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0

make && make install

三、配置并启动

（1）制作软连接：
ln -s /application/mysql-5.6.36/ /application/mysql


（2）拷贝配置文件到/etc:
cp support-files/my*.cnf /etc/my.cnf

（3）初始化数据库：
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql

（4）创建关键目录并设置权限:
mkdir -p /application/mysql/tmp
chown -R mysql.mysql /application/mysql/

（5）复制启动脚本到/etc/init.d/mysqld
cp support-files/mysql.server /etc/init.d/mysqld

（6）启动数据库
/etc/init.d/mysqld start
netstat -lntup|grep 330

（7）配置环境变量
echo 'PATH=/application/mysql/bin/:$PATH' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
echo $PATH
mysql

select user,host,password from mysql.user;



mysqladmin -uroot -p 原密码（初始为空）  新密码                        # 默认是修改的本地root用户登录的密码  不能-h
----------------------------------------------------------
排错：
1、输出
2、错误日志
tail -100 /application/mysql/data/db02.err
设置密码:
mysqladmin -u root password 'oldboy123'
mysql -uroot -poldboy123
清理用户及无用数据库(基本优化)
select user,host from mysql.user;
drop user ''@'db02';
drop user ''@'localhost';
drop user 'root'@'db02';
drop user 'root'@'::1';
select user,host from mysql.user;
drop database test;
show databases;
5.6.36
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
port=3306
server_id=10
log-error=/var/log/mysql.log
log-bin=/application/mysql/data/mysql-bin
binlog_format=row
skip_name_resolve

[mysql]
socket=/application/mysql/tmp/mysql.sock
conf
1、创建多套目录

mkdir -p /data/330{7,8,9}

2、准备多套配置文件

vi /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin
socket=/data/3307/mysql.sock
log-error=/data/3307/mysql.log

vi /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308
server-id=3308
port=3308
log-bin=/data/3308/mysql-bin
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log

vi /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309
server-id=3309
port=3309
log-bin=/data/3309/mysql-bin
socket=/data/3309/mysql.sock
log-error=/data/3309/mysql.log


3、初始化多套数据

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3309 --user=mysql


4、启动多个实例

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

5、查看启动端口
netstat -lnp |grep 330


6、分别连接测试

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id';"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id';"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id';"
多实例

 

 

win:

1.下载路径：http://dev.mysql.com/downloads/mysql/

启动：

--service mysqld start  #开启    （调用的mysqld_safe） /etc/init.d/mysql start; /usr/bin/mysqld_safe &
--chkconfig mysqld on   #设置开机自启
OR
--systemctl start mariadb
--systemctl enable mariadb

# 关闭
pkill
kill
killall mysqld
mysqladmin shutdown
services mysqld stop

查看：

-- ps aux |grep mysqld    #查看进程
-- netstat -an |grep 3306 #查看端口

设置密码：

-- mysqladmin -uroot password '123'   #设置初始密码，初始密码为空因此-p选项没有用
-- mysqladmin -u root -p123 password '1234' #修改root用户密码

登录：

-- win
-- mysql               #本地登录，默认用户root，空密码，用户为root@127.0.0.1
-- mysql -uroot -p1234 #本地登录，指定用户名和密码，用户为root@127.0.0.1
-- mysql -uroot -p1234 -h 192.168.31.95 #远程登录，用户为root@192.168.31.95

  

 mysql的常用命令

 

-- 启动mysql服务与停止mysql服务命令：
-- 需要以管理员方式打开cmd
-- net start MySQL57
-- net stop  MySQL57
-- 
-- 
-- 登陆与退出命令：
--  win
-- mysql －h 服务器IP -P 端口号 -u  用户名 -p 密码 －－prompt 命令提示符  －－delimiter 指定分隔符
-- mysql －h 127.0.0.1 -P 3306 -uroot -p123
--    quit------exit----\q;
-- 
-- 
-- \s;   ------my.ini文件：[mysql] default-character-set=gbk [mysqld] character-set-server=gbk
-- 
-- prompt 命令提示符（\D:当前日期 \d:当前数据库  \u:当前用户）
-- 
-- \T(开始日志) \t(结束日志)
-- 
-- show warnings;
-- 
-- help() ? \h
-- 
-- \G；
-- 
-- select now();
-- select version();
-- select user;
-- 
-- \c 取消命令
-- 
-- delimiter 指定分隔符

-- select version()  查看数据库版本

  忘记密码怎么办？

 方法一、启动mysql时，跳过授权表

[root@controller ~]# service mysqld stop
[root@controller ~]# mysqld_safe --skip-grant-table &
[root@controller ~]# mysql
mysql> select user,host,password from mysql.user;
+----------+-----------------------+-------------------------------------------+
| user     | host                  | password                                  |
+----------+-----------------------+-------------------------------------------+
| root     | localhost             | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root     | localhost.localdomain |                                           |
| root     | 127.0.0.1             |                                           |
| root     | ::1                   |                                           |
|          | localhost             |                                           |
|          | localhost.localdomain |                                           |
| root     | %                     | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-----------------------+-------------------------------------------+
mysql> update mysql.user set password=password("123") where user="root" and host="localhost";
mysql> flush privileges;
mysql> exit
[root@controller ~]# service mysqld restart
[root@controller ~]# mysql -uroot -p123
LINUX
1.干死mysql（注意：以什么方式启动的就要以什么方式吧它干掉）
2.在cmd中启动：mysqld --skip-grant-tables （跳过所有的授权表）
3.在cmd中登录：mysql
4.输入两条命令：
　　update mysql.user set authentication_string = password('123') where user = 'root' and host = 　　'localhost'
　　flush privileges 重新刷新权限



5.tskill mysqld   干掉任务
6.正常启动mysql
7.mysql -uroot -p123
WIN

方法二、删库

1.在mysql的解压目录下新建一个my.ini配置文件
那么在my.ini配置文件里写上 
在ini配置文件中注释有两种#或者;
[mysql]
skip-grant-tables (开头的--就不用要了)如果启动了就先关了，然后重新启动一下，登录后，my.ini就生效了
basedir = E:\mysql-5.7.19-winx64
datadir = D:\6_data #如果data指定要保存数据的目录，一定要记得初始化 mysqld --initialize-insecure 
#完了吧原先的那个数据data干掉。
WIN
删除与权限相关的库mysql，所有的授权信息都丢失，主要用于测试数据库或者刚刚
建库不久没有授权数据的情况（从删库到跑路）
[root@controller ~]# rm -rf /var/lib/mysql/mysql
[root@controller ~]# service mysqld restart
[root@controller ~]# mysql
LINUX
#在mysql的解压目录下，新建my.ini,然后配置
#1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效
[mysqld]
;skip-grant-tables
port=3306
character_set_server=utf8
#解压的目录
basedir=E:\mysql-5.7.19-winx64
#data目录
datadir=E:\my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会去这个目录里找数据

#2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效
[client]
port=3306
default-character-set=utf8
user=root
password=123456

#3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置
[mysql]
;port=3306
;default-character-set=utf8
user=hiayan
password=123456
win指定配置文件

 

 sql及其规范

  •  SQL语句不区分大小写 。
    • 建议：
    • 命令：大写
    • 表库名：小写
  • SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。
  • 用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。
  • 注释
    • 单行注释：--
    • 多行注释：/*...................*/
  • -- --SQL中 DML、DDL、DCL区别 .
-- 
-- 
-- -- DML（data manipulation language）：
--    它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的
--    数据进行操作的语言
-- 
-- -- DDL（data definition language）：
--    DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）
--    的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
-- 
-- -- DCL（Data Control Language）：
--    是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）
--    语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权
--    力执行DCL
    DDL,DML和DCL 

  mysql数据类型

 数值、日期、字符

float(5,2) #5代表salary总共多宽，2代表小数点后保留2位，那么整数部分有3位

 

 

在实际应用的很多场景中，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语句中没有指定该列的更新值，则默认更新为当前时间。

datdatime 和 timestamp的区别
data类型
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部
空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
char:简单粗暴，不够就用空格凑够固定长度存放起来，浪费空间，但是存储速度快
    (牺牲空间，提高速度)
varchar(你有几个就存几个):精准，计算出待存放数据的长度，节省空间，存取速度慢
        （牺牲速度，提高效率）
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不
要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、
BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
这些对应4种BLOB类型，有相同的最大长度和存储需求。
字符串类型

 

  数据库操作-----DDL

 1、创建数据库（相当于在sql安装目录下多了一个文件夹，不能通过复制文件夹增加库）

create database [if not exists] db_name [character set xxx]
命名规则：
　　区分大小写，
　　唯一，
　　不能使用关键字，
　　不能够单独使用数字，
　　最长128位

2、查看数据库

show databases;查看所有数据库

show create database db_name; 查看数据库的创建信息

3、修改数据库

alter database db_name [character set xxx];
rename database 表名 to 新表名
#!/bin/bash  
mysql -uroot -p123456 -e 'create database if not exists new_sakila'
list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='sakila'")

for table in $list_table
do
    mysql -uroot -p123456 -e "rename table sakila.$table to new_sakila.$table"
done


 






4、删除数据库




drop database [if exists] db_name;




5、使用数据库




切换数据库 use db_name;
查看当前使用的数据库 select database(); 

select user()#查看当前用户
select * from mysql.user; 查看所有的用户（select * from mysql.user\G）;一行一行显示

9.创建账号 identifity
create user 'ls'@'localhost' identified by '147852' # 名为ls的本机账号
create user 'alex'@'%' identified by '123' #代表只要ip地址能拼通，那么所有的用户都可以远程登录alex
create user 'susan'@'192.168.20.%' identified by '123' #创建远程账号，只要是192.168.20.？
　　　　　　　　　　开头的ip都可以登录susan
#如果你要远程登录alex的账户，那么客户端得这样登录  ：mysql -h192.168.20.97 -ualex -p123








#1. 修改配置文件
[mysqld]
default-character-set=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

#mysql5.5以上：修改方式有所改动
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    [client]
    default-character-set=utf8
    [mysql]
    default-character-set=utf8

#2. 重启服务
#3. 查看修改结果：
\s
show variables like '%char%' 查看编码



解决乱码问题


 


  数据库的权限




#insert ,select ,update,delete #有这么几个可以设置权限的操作，那么我们先以select为例吧。
分四个级别：
级别1：对所有的库，下的所有的表，下的所有的字段
'''*.*代表所有的库下的所有的表'''
同意select权限开放，开放的是*.*的select权限开放给用户
grant select on *.* to 'zhang'@'localhost' identified by '123';  #让创建用户的时候赋予权限

级别2：对db1库，下的所有的表，下的所有的字段
grant select on db1.* to 'wang'@'localhost' identified by '123';

级别3：对表db1.t1，下的多有字段
grant select on db1.t1 to 'li'@'localhost' identified by '123';

级别4：对表db1.t1，下的id,name,字段
grant select (id ,name)  on db1.t1 to 'zhao'@'localhost' identifitied by '123';
grant select (id ,name),update(name) on db1.t1 to 'zhao'@'localhost' identifitied by '123';
修改完权限后要记得刷新权限
flush privileges;

删除权限：
revoke select on *.* from 'zhang'@'localhost'
revoke select on db1.* from 'wang'@'localhost'
revoke select on db1.t1 from 'li'@'localhost'
revoke select (id ,name),update(name) on db1.t1 from 'zhao'@'localhost'

# 查看权限
show hrants for root@'10.0.0.%'




 


 


 


 


 


  表      操作


 1、创建表




 create table employee(
            id int(10) zerofill primary key auto_increment ,   
　　　　　　　　　　　　　　 -- primary key   主键         //forgin key 外键
            name varchar(20),
            gender bit default 1,  
            birthday date,
            entry_date date,
            job varchar(20),
            salary double(4,2) unsigned,
            resume text    -- 注意，这里作为最后一个字段不加逗号
          );




 2、查看表信息




desc tab_name 查看表结构
show columns from tab_name  查看表结构 //一样

show tables 查看当前数据库中的所有的表
show create table tab_name    查看当前数据库表建表语句 




3、修改表结构


    

  • 增加列

      

    • 

      

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

#添加多个字段
alter table name
      add addr varchar(20),
      add age  int first,
      add birth varchar(20) after name;
      

      

      • 

    

    • 

  • 修改列类型

      

    • 

      

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


      alter table users2 modify age tinyint default 20 after id;
      

      

      • 

    

    • 

  • 修改列名

      

    • 

      

      alter table tab_name change 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;
      
      alter table users2 change age Age int default 28 first;
      alter table test1 change id id int(4) primary key not null auto_increment;
      

      

      • 

    

    • 

  • 删除列

      

    • 

      

      alter table tab_name drop [column] 列名;
      

      

      • 

    

    • 

  • 修改表名

      

    • 

      

      rename table 表名 to 新表名;
      alter table 表名 rename to 新表名 
      

      

      • 

    

    • 

  • 修改表所用的字符集

      

    • 

      

      alter table tab_name character set utf8;
      

      

      • 

    

    • 

  • 复制表

      

    • 

      

      create table t7(id int,name char(10));
create table t8 select * from t7;  #拷贝表结果（如果有数据就把数据一起拷贝了）
      

      

      • 

    

    • 



4、删除表


    

  • 

      

    • 

      

      drop table tab_name;
      

      

      • 

    

    • 



5、添加---删除主键


    

  • 

      

    • 

      

      alter table tab_name add primary key(字段名称,...) 
      
      alter table users drop primary key;
      

      

      • 

    • 

      

      -- 自增的只能是主键

mysql> create table test(num int primary key  auto_increment);

alter table test drop primary key;--  删除主键




-- 唯一索引
alter table tab_name add unique [index|key] [索引名称](字段名称,...) 

      create index index_name on table(field)
      alter table users add unique(name)-- 索引值默认为字段名show create table users;
alter table users add unique key user_name(name);-- 索引值为user_name

-- 添加联合索引
alter table users add unique index name_age(name,age);#show create table users;
　　　　　　　　　　　　　　 唯一， 普通　　
-- 删除唯一索引
alter table tab_name drop {index|key} index_name

      -- 前缀索引
      create index index_name on stu(stu_id(8))

      -- 查  
      desc table
      show index from table\G;
      explain  sql语句

      

      

      • 

    • 

      

      

      create table article(
            id int primary key auto_increment ,
            title varchar(20),
            publish_date INT,
            click_num INT,
            is_top TINYINT(1),
            content TEXT,
            primary key(tab_name,id)
          );
      

      
创建文章表/外键
      

      • 

    • 

      

      ---  每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任

----主表

CREATE TABLE ClassCharger(

       id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       age INT ,
       is_marriged boolean  -- show create table ClassCharger: tinyint(1)

);

INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
                                                       ("丹丹",14,0),
                                                       ("歪歪",22,0),
                                                       ("姗姗",20,0),
                                                       ("小雨",21,0);


----子表

CREATE TABLE Student(

       id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       charger_id TINYINT,     --切记:作为外键一定要和关联主键的数据类型保持一致
       -- [ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)

) ENGINE=INNODB;

INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
                                            ("alvin2",4),
                                            ("alvin3",1),
                                            ("alvin4",3),
                                            ("alvin5",1),
                                            ("alvin6",3),
                                            ("alvin7",2);


DELETE FROM ClassCharger WHERE name="冰冰";
INSERT student (name,charger_id) VALUES ("yuan",1);
-- 删除居然成功,可是 alvin3显示还是有班主任id=1的冰冰的;

-----------增加外键和删除外键---------

ALTER TABLE student  ADD CONSTRAINT abc
                     FOREIGN KEY(charger_id)
                     REFERENCES  classcharger(id);


ALTER TABLE student DROP FOREIGN KEY abc;
      

      

       
      

      • 

    

    • 



6、表记录操作




create table employee (
                 id int primary key auto_increment,
                 name varchar(20) not null unique,
                 birthday varchar(20),
                 salary float(7,2)
                             );




    

  • 增

    

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

    

      

    • 插一条
      • 

    • 

      

      insert into employee_new (name,salary) values('xialv',1000);    --插入列不全


insert into employee_new values(2,'alex','1989-08-08',3000);    --
      

      

      • 

    • 插多条
      • 

    • 

      

      insert into employee_new values
       (4,'alvin1','1993-04-20',3000),
       (5,'alvin2','1995-05-12',5000);
      

      

      • 

    • 

      set
      

      

      insert ［into］ tab_name set 字段名=值

insert employee set id=12,name="alvin3"; 
      

      

      • 

    

    • 

  • 删

      

    • 

      

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

-- 删除表中名称为’alex’的记录。
delete from employee where name='alex';
-- 删除表中所有记录。
delete from employee;-- 注意auto_increment没有被重置:
      　　　　　　　　　　　　　　　　　　alter table employee auto_increment=1;      
-- 使用truncate删除表中记录。
truncate table emp_new;
      

      

      • 

    

    • 

  • 改

      

    • 

      

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

    /*      UPDATE语法可以用新值更新原有表行中的各列。
            SET子句指示要修改哪些列和要给予哪些值。
            WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。*/


update employee set birthday="1989-10-24" WHERE id=1;

--- 将yuan的薪水在原有基础上增加1000元。
update employee set salary=salary+4000 where name='yuan';
      

      

      • 

    

    • 

  • 查

      

    • 

      

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

      

      

      

      CREATE TABLE ExamResult(

   id INT PRIMARY KEY  auto_increment,
   name VARCHAR (20),
   JS DOUBLE ,
   Django DOUBLE ,
   OpenStack DOUBLE
);


INSERT INTO ExamResult VALUES  (1,"yuan",98,98,98),
                               (2,"xialv",35,98,67),
                               (3,"alex",59,59,62),
                               (4,"wusir",88,89,82),
                               (5,"alvin",88,98,67),
                               (6,"yuan",86,100,55);
      

      
考试成绩表
      

      

      -- distinct 过滤表中重复数据。 字段 as 别名（as也可以省略）
      select distinct JS ,name from ExamResult;
      

      显示格式
      


      CONCAT() 函数用于连接字符串
      SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
      FROM employee;

      CONCAT_WS() 第一个参数为分隔符
      SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
      FROM employee;
      

      

      附加过滤条件查询：
      

      • 

    • where
      • 

    • 

      

      -- where字句中可以使用：
-- 比较运算符：
> < >= <= <> !=
between 80 and 100 值在10到20之间闭区间
in(80,90,100) 值是10或20或30
like 'yuan%'

pattern可以是%或者_，
如果是%则表示任意多字符，
如果是_则表示一个字符


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

      

      • 

    • group by

      

      1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from employee group by post;

2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from employee group by post;

3. 查询公司内男员工和女员工的个数
select sex,count(id) from employee group by sex;

4. 查询岗位名以及各岗位的平均薪资
select post,max(salary) from employee group by post;

5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;

6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from employee group by post;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
 select sex,avg(salary) from employee group by sex;
      

      

      • 

    •  having

      

      语法和where 一样
      

      

      • 

    • order by
      • 

    • 

      

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

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

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

      

      • 

    • 每个关键字的优先级

      

      from-->where-->group by-->having-->select-->distinct-->order by-->limit
      

      

       
      

      • 

    

    • 



 


 






#！！！执行优先级从高到低：where > group by > 聚合函数 > having >order by
1.where和having的区别                                                                                
     1. Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的                                        
     （先找到表，按照where的约束条件，从表(文件)中取出数据），Where中不能使用聚合函数                                              
     2.Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作                                                     
     （先找到表，按照where的约束条件，从表(文件)中取出数据，然后group by分组，                                                
      如果没有group by则所有记录整体为一组，然后执行聚合函数，然后使用having对聚合的结果进行过滤），                                     
      在Having中可以使用聚合函数。                                                                          
     3.where的优先级比having的优先级高                                                                     
     4.having可以放到group by之后，而where只能放到group by 之前。






1.查看员工的id>15的有多少个
select count(id) from employee where id>15;#正确，分析：where先执行，后执行聚合count(id),
                                            然后select出结果
select count(id) from employee having id>15; #报错，分析：先执行聚合count(id)，后执行having过滤，
                                            #无法对id进行id>15的过滤
#以上两条sql的顺序是
1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目
2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having 基于上一步聚合的结果（此时只有count(id)字段了）
进行id>15的过滤，很明显，根本无法获取到id字段



where////having不同


 






1.select * from employee where name regexp '^ale';  #匹配以ale开头的员工信息
2.select * from employee where name regexp 'on$'; #匹配以on结尾的员工信息
3.select * from employee where name regexp 'n{1,2}'; #匹配name里面包含1到2个n的员工信息
小结：对字符串匹配的方式
where name = 'egon';
where name like 'yua%';
where name regexp 'on$';



正则表达式匹配


 







            

            
posted @ 
2018-04-30 20:36 
慕沁 
阅读(548) 
评论(0) 
 
收藏 
举报