MySQL简单示例

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8

USE test

-- 字段名和关键字保留字相同时，在字段名两边加 ` 号（注：不是单引号）
CREATE TABLE IF NOT EXISTS testtable(
id INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
age INT,
birth DATE,
PRIMARY KEY(id)
)

DROP TABLE testtable

-- 同时插入多条数据的时候在每条插入语句的最后加入分号;
INSERT INTO testtable(`name`,age,birth)VALUES('张三',10,'2010-3-1');
INSERT INTO testtable(`name`,age,birth)VALUES('李四',11,'2009-3-1');
INSERT INTO testtable(`name`,age,birth)VALUES('王五',12,'2008-3-1');
INSERT INTO testtable(`name`,age,birth)VALUES('赵六',13,'2007-3-1');
INSERT INTO testtable(`name`,age,birth)VALUES('zhangsan',13,NULL);


-- 注释的三种方法： 1.删除表内的数据 （-- 之后要加一个空格）
# 2.删除表内的数据
/* 3.删除表内的数据 */

-- 清除表内数据，保存表结构，用truncate。   重新插入的时候id重 1开始     
TRUNCATE table testtable

-- LIMIT属性设定返回的记录数，OFFSET 指定开始查询数据的偏移量，默认为0
SELECT * FROM testtable ORDER BY id LIMIT 2 OFFSET 1

-- WHERE
SELECT * FROM testtable WHERE age = 10
SELECT * FROM testtable WHERE age != 11
SELECT * FROM testtable WHERE age <> 11
SELECT * FROM testtable WHERE age <= 12

-- UPDATE
UPDATE testtable SET `name`='zhangsan', age = 20, birth = '1998-2-3' WHERE id = 1

-- 将字段中特定的字符串批量修改为其他字符串     (将张三改为  zhangsan)
UPDATE testtable set `name`=REPLACE(`name`,'张三','zhangsan')

-- DELETE
DELETE FROM testtable WHERE id = 5

-- LIKE
SELECT * FROM testtable WHERE `name` LIKE '%san'


CREATE TABLE IF NOT EXISTS student(
id INT UNSIGNED AUTO_INCREMENT,
score INT,
`name` VARCHAR(20),
PRIMARY KEY(id)
)

DROP TABLE student

INSERT INTO student(score,`name`) VALUES(92,'张三');
INSERT INTO student(score,`name`) VALUES(94,'张三');
INSERT INTO student(score,`name`) VALUES(93,'李四');
INSERT INTO student(score,`name`) VALUES(91,'王五');
INSERT INTO student(score,`name`) VALUES(90,'钱');

-- UNION     要求：1.SELECT子句中的列数相同，2.列数据类型必须在每个位置匹配
SELECT `name` FROM student UNION SELECT `name` FROM testtable
SELECT `name` FROM student UNION ALL SELECT `name` FROM testtable
SELECT `name`,age FROM testtable WHERE `name` = '李四' UNION ALL SELECT `name`, score FROM student WHERE `name` = '李四'

-- ORDER BY 排序
SELECT * FROM testtable ORDER BY age
SELECT * FROM testtable ORDER BY age DESC

-- GROUP BY 分组
SELECT `name`,COUNT(*) FROM testtable GROUP BY `name`

-- WITH ROLLUP 可以使用分组统计再进行相同的统计   分组统计
SELECT `name`,COUNT(*) as `count` FROM testtable GROUP BY `name` WITH ROLLUP

-- 上面语句最后一项为总计，但是name 为空，可使用coleace设置一个可以取代null的名称
SELECT COALESCE(`name`,'总数') as `name`,count(*) as `count` FROM testtable GROUP BY `name` WITH ROLLUP

-- INNER JOIN
SELECT a.`name` , a.age, a.birth, b.score FROM testtable a INNER JOIN student b ON a.`name` = b.`name`

-- 省略 JOIN
SELECT a.`name` , a.age, a.birth, b.score FROM testtable a JOIN student b ON a.`name` = b.`name`

-- where 与上等价
SELECT a.`name` , a.age, a.birth, b.score FROM testtable a , student b WHERE a.`name` = b.`name`

-- left join  左连接   左边没有null
SELECT a.`name` , a.age, a.birth, b.score FROM testtable a LEFT JOIN student b ON a.`name` = b.`name`

-- right join 右连接   右边没有null
SELECT a.`name` , a.age, a.birth, b.score FROM testtable a RIGHT JOIN student b ON a.`name` = b.`name`

-- NULL 值 (NULL的比较方式特殊，用= , != 不起作用,  用 <=>, IS NULL ,IS NOT NULL)
SELECT * FROM testtable WHERE birth  = NULL
SELECT * FROM testtable WHERE birth  != NULL

-- 用 <=>, IS NULL ,IS NOT NULL
SELECT * FROM testtable WHERE birth  <=> NULL
SELECT * FROM testtable WHERE birth IS NULL
SELECT * FROM testtable WHERE birth IS NOT NULL

-- 正则
-- birth 以 3 结尾的所有数据
SELECT * FROM testtable WHERE birth REGEXP '3$'
-- age 以2 开头的所有数据
SELECT * FROM testtable WHERE age REGEXP '^2'
-- name 中包含 三  的所有数据
SELECT * FROM testtable WHERE `name` REGEXP '四'
-- name 中以z开头  以 n结尾的所有数据
SELECT * FROM testtable WHERE `name` REGEXP '^z|n$'
-- * 等价于 {0，}   zo* 匹配 zoo        + 等价于 {1，}
-- {n} n是一个非负整数， o{2}  匹配food
-- {n,m} m,n为非负整数，n<=m


-- 事务处理

/*  
两种方法：
1. 用 BEGIN，ROLLBACK ,COMMIT
2. 用 SET 改变MySQL的自动提交模式
SET AUTOCOMMIT = 0 禁止自动提交
SET AUTOCOMMIT = 1 开启自动提交
*/

BEGIN;
INSERT INTO testtable(`name`,age,birth) VALUES('wenqian',25,'1994-12-1')
COMMIT;

BEGIN;
INSERT INTO testtable(`name`,age,birth) VALUES('wenqian',25,'1994-12-1')
ROLLBACK;

SELECT * FROM testtable

-- 查看字段
SHOW COLUMNS FROM testtable

-- ALTER
-- 增加删除字段
ALTER TABLE testtable ADD newcolumn VARCHAR(20)
ALTER TABLE testtable DROP newcolumn
ALTER TABLE testtable ADD newcolumn VARCHAR(20) AFTER `name`
ALTER TABLE testtable ADD newcolumn VARCHAR(20) FIRST
-- 修改字段类型及名称   在alter 命令中使用MODIFY 或者CHANGE子句
-- 将name字段的类型从 VARCHAR(20) 改为 VARCHAR(50)
ALTER TABLE testtable MODIFY `name` VARCHAR(50)
-- CHANGE 将name字段从  VARCHAR(50) 改为 VARCHAR(20)
ALTER TABLE testtable CHANGE `name` `name` VARCHAR(20)

-- 修改表名
ALTER TABLE testtable RENAME TO test
ALTER TABLE test RENAME TO testtable

-- 索引   （可以提高查询速度，同时会降低更新表的速度，如对表进行INSERT，UPDATE，DELETE，因为更新表的时候还要保存一下索引文件）
CREATE INDEX indexname ON testtable(username(length))
ALTER TABLE testtable ADD INDEX indexname(id)

-- 删除索引
DROP INDEX indexname on testtable

-- 唯一索引  索引列的值必须唯一，但是允许有空值。  组合索引中，列值的组合必须唯一
ALTER TABLE testtable ADD UNIQUE indexname (id)
-- 添加主键索引时，要确保主键默认不为空（NOT NULL）
ALTER TABLE testtable MODIFY id INT NOT NULL
ALTER TABLE testtable ADD PRIMARY KEY (id)
-- 显示索引信息，\G格式化输出信息
SHOW INDEX FROM testtable; \G

-- 临时表 TEMPORARY
CREATE TEMPORARY TABLE `temp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO temp (`name`,age, birth) VALUES('栋妖',30,'1988-3-1')

SELECT * FROM temp

drop TABLE temp

-- 复制表
-- 1.获取数据表的完整结构
SHOW CREATE table testtable; \G

-- 根据上句得到该语句
CREATE TABLE `testtable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

-- 2.修改SQL的数据表名
CREATE TABLE `copytesttable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

-- 3.拷贝数据表的数据
INSERT INTO copytesttable (id,`name`,age,birth) SELECT id,`name`,age,birth FROM testtable


-- 元数据

SELECT VERSION()

SELECT DATABASE()

SELECT USER()


-- 序列  设置序列的开始
ALTER TABLE testtable auto_increment = 100

-- 处理重复数据
-- 设置指定的 PRIMARY KEY 或者是 UNIQUE 索引保证数据的唯一性
CREATE TABLE `person_tb` (
  `last_name` char(20) NOT NULL,
  `first_name` char(20) NOT NULL,
  `sex` char(20) DEFAULT NULL,
PRIMARY KEY (last_name,first_name)
)

-- INSERT IGNORE INTO 与 INSERT INTO 的区别是 当插入重复数据时 INSERT IGNORE INTO 执行不会出错

INSERT IGNORE INTO person_tb (last_name,first_name) VALUES ('Jay','Thomas')

-- 设置唯一索引
CREATE TABLE `person_tb` (
  `last_name` char(20) NOT NULL,
  `first_name` char(20) NOT NULL,
  `sex` char(20) DEFAULT NULL,
UNIQUE (last_name,first_name)
)

-- REPLACE INTO 如果存在 PRIMARY 或者是 UNIQUE 相同的记录，则先删除掉，再插入新记录
REPLACE INTO person_tb (last_name,first_name) VALUES ('Jay','Thomas')

-- 统计重复数据
SELECT count(*) as count,last_name,first_name FROM person_tb GROUP BY last_name,first_name HAVING count > 1

-- 过滤重复数据
-- 1. DISTINCT
SELECT DISTINCT last_name,first_name FROM person_tb

-- 2. GROUP BY
SELECT last_name,first_name FROM person_tb GROUP BY last_name, first_name


-- 删除重复数据
CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tb GROUP BY last_name, first_name, sex

DROP TABLE person_tb

ALTER TABLE tmp RENAME TO person_tb

SELECT * FROM person_tb

-- 2. 添加 INDEX 和 PRIMARY KEY
ALTER TABLE person_tb ADD PRIMARY KEY (last_name, first_name)

防止SQL注入，我们需要注意以下几个要点：
1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包***r>6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，
网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等