sql语法与数据库
sql对大小写不敏感
数据库类型
关系型数据库（RDBMS）：
MySQL、Oracle、SQLServer
NoSQL：
MongoDB、Redis（开源的、占内存的数据存储，可以理解为数据库）、HBase
图数据库：
Neo4j
可以对数据关系进行解析，搭建测试平台可以使用
NewSQL：
rethinkdb
stf:智能设备管理平台
phpmyadmin可视化数据库服务
mysql workbench
Workbench mysql官方图形界面数据库管理工具
mysql shell
从本地连接远程服务器
参数
-D --database=name指定数据库
-h --host=name 指定主机
-p --password[=password]密码
-P --port=#端口号
-u --username=#指定用户名
语法
mysql -h docker.testing-studio.com -P 3306 -u kongweisheng -p xxxxxx
mysql client是一个官方的客户端连接管理的工具套件
sql语法
DDL(create、alter、drop、rename、truncate、comment)
create database seveniruby;
use seveniruby;
create table person(
personid int(11),
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
);
DML(select、insert、update、delete、meger、call、explain plan、lock table)，核心是增删改查
show语法
help show;查看show语法使用
连接数据库后show databases;查看有哪些数据库
use databasename;
进入数据库后，show tables;查看有哪些数据表
offset偏移
select * from tablename order by id desc limit 3 offset 3;表示按查询条件展示第二页
distinct语法
select distinct gender from tablename;
DCL(grant、revoke)
TCL(comitte、rollback、sevepoint、set transaction)
sql高级语法
MYSQL的管理
show log
数据库性能分析
-代码层埋点
-查看每个sql执行时间
-show log
- dtrace systrace
-执行计划（excute plan辅助分析瓶颈）
bin log
系统备份机制（数据恢复）
dump :mysqldump
数据备份（格式自定义，按系统架构）
代码：
backup database testdb
to disk = '路径'
load:source
MYSQL的应用
多表联查
inner join 取公共部分
left join 取左表
right join取右表
full outer join 取全部
cross join
select e.emp_no,birth_date,d.dept_no,n.dept_name
from employees e
inner jion (dept_emp d,departments n)
on e.emp_no=d.emp_no and n.dept_no=d.dept_no
order by emp_no
limit 1000
inset employee(按顺序输入各字段的值) 10000
使用python与pymysql连接数据库
import pymysql.cursers
connection = pymysql.connect(host = ''
user = ''
password =''
db = ''
charset = ''
cursoreclass = pymysql.cursors.DictCursor
)
Linux环境下操作数据库
use RUNOOB; 命令用于选择数据库。
set names utf8; 命令用于设置使用的字符集。
SELECT * FROM Websites; 读取数据表的信息。
数据库基础知识
数据库连接
mysql -u root -p;
创建数据库
create database name;
create database if not exists name default charset utf8 collate utf8_general_ci;
删除数据库
drop database databasename;
选择数据库
[root@host]# mysql -u root -p Enter password:****** mysql> use RUNOOB; Database changed mysql>
数据类型
-数值类型
-日期和时间类型
-字符串类型
创建数据表
CREATE TABLE table_name (column_name column_type);
CREATE TABLE IF NOT EXISTS `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
命令行创建表
root@host# mysql -u root -p Enter password:******* mysql> use RUNOOB; Database changed mysql> CREATE TABLE runoob_tbl( -> runoob_id INT NOT NULL AUTO_INCREMENT, -> runoob_title VARCHAR(100) NOT NULL, -> runoob_author VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( runoob_id ) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.16 sec) mysql>
删除数据表
DROP TABLE table_name ;
按条件删除表内数据
delete from 表名 where 删除条件;
删除表内所有数据，保留表结构
truncate table
插入数据
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
不按行插入数据（需要输入所有行的数据）
mysql> INSERT INTO runoob_tbl -> VALUES -> (0, "JAVA 教程", "RUNOOB.COM", '2016-05-06');
查询数据
SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]
limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录
常用简单查询语句
SELECT * FROM websites; /* 查询表所有数据 */ SELECT NAME FROM websites; /* 查询表字段数据 */ SELECT * FROM websites where name = "广西"; /* 查询表字段下条件数据 */
SELECT * from websites where name like "_o%"; /* 模糊查询表下数据 */
SELECT * FROM websites where id BETWEEN "1" AND "5"; /* 查询表下字段范围数据 */
SELECT * FROM websites WHERE name in ("广西","百度"); /* 查询表字段下固定条件数据 */
SELECT DISTINCT country FROM Websites; /* 查询去重值 */
SELECT * FROM Websites WHERE country = "CN" AND alexa > 50; /*查询表下范围条件数据*/
SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* 查询表下条件不同值 */
SELECT * FROM Websites ORDER BY alexa; /* 查询表下值排序结果 */
SELECT * FROM Websites ORDER BY alexa DESC; /* 查询表下排序结果降序 */
SELECT * FROM Websites LIMIT 2; /* 查询表下范围数据 */
SELECT name as zzz from websites; /*别名查询表下数据*/
where子句
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....
update更新
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
delete语句
DELETE FROM table_name [WHERE Clause]
delete，drop，truncate 都有删除表的作用，区别在于：
- 1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
- 2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
- 3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。
like语句
SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
like 匹配/模糊匹配，会与 % 和 _ 结合使用。
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
在 where like 的条件查询中，SQL 提供了四种匹配方式。
（1）%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
（2）_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
（3）[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
（4）[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起 便可正常查询。
union操作符
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
SELECT country FROM Websites
UNION （ALL）
SELECT country FROM apps
ORDER BY country;
有all，读取重复数据；没有all，不读取重复数据
排序
select * from table_name where 条件 order by asc(desc);
select * from tabe_name where 条件order by id;
group by分组
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
mysql连接的使用
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
null值处理
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true
正则表达式
mysql事务
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
-特点
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
alter命令
修改数据表名或数据表字段
删除表字段
ALTER TABLE testalter_tbl DROP i;
增加表字段
ALTER TABLE testalter_tbl ADD i INT;
修改字段c的数据类型
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
修改字段c为b
ALTER TABLE testalter_tbl MODIFY c b；
设置字段默认值
ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
修改数据表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
修改存储引擎：修改为myisam
alter table tableName engine=myisam;
删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;
修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;
索引
一般索引
创建索引
CREATE INDEX indexName ON table_name (column_name)
修改表结构（创建索引）
ALTER table tableName ADD INDEX indexName(columnName)
删除索引
DROP INDEX [indexName] ON mytable;
创建表的时候直接指定
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,INDEX [indexName] (username(length)));
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length)));
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
使用 ALTER 命令添加和删除主键
主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键：
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:
SHOW INDEX FROM table_name; \G
临时表
复制表
元数据
序列使用
处理重复数据
SQL注入
导出数据
导入数据
浙公网安备 33010602011771号