sql 笔记(mysql)

主键：一列或一组列，其值能够唯一区分表中的每一行。

外键：外键为某个表中的一列，包含另一个表的主键值。

create table department (id int not null primary key,name char(20));

create table employeee (id int not null primary key,name char(20), department_id int, foreign key(department_id) references department(id));

mysql的dbms基于客户端-服务器模式，服务器部分负责所有数据访问和和处理，客户端与用户打交道。

show语句总结

show databases;

show tables;

show columns from 表名;

show status;

show grants;

show errors;

show warnings;

 创建表

create table worker (id int not null primary key,name char(20) default 'worker');

插入insert

insert into worker (id,name) values (1,'jack');

distinct关键字

检索有不同值的列表

select distinct name from table_name;

limit关键字

限制返回的行的范围：

select name from tabe_name limit 5; (返回前5行)
select name from table_name limit 5,5; (行5开始的5行)

排序

order by desc(降序) asc（升序，默认就是升序）

不匹配检查

select name from person where age <> 20;
select name from person where age != 20;

空值检查、between、in

IS NULL

between xx and xx

in (xx,xx)

模糊查询

%通配符：..like 'ab%'  ..like '%ab' ..like 'a%b'

_通配符。与%的区别是_，_只能代表一个字符。

UNION(组合查询)

UNION的使用非常简单。所需要做的只是给出每条SELECT语句，在各语句之间放上关键字UNION。

正则表达式

sql查询也可以用正则表达式，例如

SELECT NAME FROM PRODUCTS WHERE NAME REGEXP '[[:DIGIT:]]{4}';

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可以将其视为批文件，虽然它们的作用不仅限于批处理。

创建存储过程

CREATE PRODUCEDURE productpricing()

BEGIN

  SELECT Avg(prod_price) AS priceaverage

  FROM products;

END; 

使用参数创建
CREATE PROCEDURE productpricing(
     OUT p1 DECIMAL(8,2),
     OUT ph DECIMAL(8,2),
     OUT pa DECIMAL(8,2)
)
BEGIN
     SELECT Min(prod_price)
     INTO p1
     FROM products;
     SELECT Max(prod_price)
     INTO ph
     FROM products;
     SELECT Avg(prod_price)
     INTO pa  
     FROM products;
END;

执行存储过程
CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

删除存储过程
DROP PROCEDURE productpricing;

Windows 安装mysql（zip包）

1，zip包解压到要安装目录

2，配置环境变量，Path后加mysql路径in

3，修改配置文件，mysql目录下my-default.ini

basedir=D:xx（mysql所在目录） 

datadir=D:xxdata （mysql所在目录data）

4，以管理员身份运行cmd，运行cd命令到mysql的bin目录下，运行命令：mysqld -install

5，安装成功后net start mysql启动mysql服务

6，mysql -u root -p登录

2017.02.14补充：mysql 5.7版本后有一些改变，默认没有data文件夹，按照上述步骤安装之后net start mysql显示：

需要CMD在bin目录输入命令mysqld --initialize-insecure --user=mysql，随后net start mysql启动服务成功。

Centos 安装mysql

（转载http://www.mamicode.com/info-detail-503994.html）

CentOS7的yum源中默认好像是没有mysql的。为了解决这个问题，我们要先下载mysql的repo源。

1. 下载mysql的repo源

$ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

2. 安装mysql-community-release-el7-5.noarch.rpm包

$ sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

安装这个包后，会获得两个mysql的yum repo源：/etc/yum.repos.d/mysql-community.repo，/etc/yum.repos.d/mysql-community-source.repo。

3. 安装mysql

$ sudo yum install mysql-server

根据步骤安装就可以了，不过安装完成后，没有密码，需要重置密码。

2021-04-27更新：

因为mysql被甲骨文收购，centos不再支持mysql，所以在mysql上安装mysql后启动服务失败

所以只能安装MariaDB，MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护。

MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

yum install -y mariadb-server

systemctl start mariadb.service

systemctl enable mariadb.service

mysql_secure_installation

（进行一些安全设置，以及修改数据库管理员密码）

install mysql on ubuntu:

u just need to run three commands:

　　sudo apt-get install mysql-server

　　sudo apt-get install mysql-client

       sudo apt-get install libmysqlclient-dev

mysql修改root(其他用户原理类似)密码

首先登录（新装mysql root是不需要密码的）

$ mysql -u root

登录时有可能报这样的错：ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock‘ (2)，原因是/var/lib/mysql的访问权限问题。下面的命令把/var/lib/mysql的拥有者改为当前用户：

$ sudo chown -R root:root /var/lib/mysql

然后，重启服务：

$ service mysqld restart

接下来登录重置密码：

$ mysql -u root
$ mysql > use mysql; 
$ mysql > update user set password=password('123') where user='root'; 
$ mysql > exit;

重启之后再登录mysql -u root -p

之后输入密码

2017.02.14补充：mysql 5.7以后修改密码需要update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';  因为user表里，password这个字段已经不在。

或者：

格式：mysqladmin -u用户名 -p旧密码 password 新密码 
例子：mysqladmin -uroot -p123456 password 123 

mysql export and import:

windows：go to mysql's directoryin 

导出：

linux：

本以为linux下也是需要到mysql的bin目录下输入导出命令才行，实际操作发现直接输入命令即可。

导入：

linux：

mysql -u 用户名 -p  数据库名 < 数据库名.sql
mysql -u abc -p abc < abc.sql

windows:

mysql 关联查询：

http://www.cnblogs.com/rixiang/p/5492435.html

mysql全局设置中文字符：

set character_set_database=utf8;
set character_set_server=utf8;
set character_set_client=gbk;
set character_set_connection=gbk; 

mysql设置表字符集：

ALTER TABLE User CONVERT TO CHARACTER SET utf8;

sql删除表中内容

delete from table_name where xx='xx'

sql清空一个表

delete from 表名;
truncate table 表名;

判断数据库或表是否存在

(1) 判断数据库存在, 则删除: 

      drop database if exists db_name;
(2) 判断数据表存在, 则删除:

      drop table if exists table_name;

注: db_name, table_name可用``(1键旁边那个键)号引起来, 也可不引起来.

 

(1) 如果单纯显示是否存在数据库, 则可用

     show databases like 'db_name';

(2) 单纯显示是否存在数据表, 则可用 

     show tables like 'table_name'; 

 

注: 这里db_name, table_name必须用''(单引号)引起来.

 

RTRIM()函数：

许多数据库保存填充为列宽的文本值，RTRIM函数可以去掉这些空格

 

聚集函数：

 

 

HAVING和WHERE区别：

WHERE在数据分组前过滤，HAVING在数据分组后过滤

 

 

使用sqlite通过日期字段，以月份统计查询：