Mysql常用语句

免费 - Sunrise - 3年前 - 浏览量： 0 - 文章来源

MYSQL语句

-- 登录数据库

mysql -h 127.0.0.1 -u root -p（密码为空可以省略-p）

-- 显示全部数据库

SHOW DATABASES;

-- 创建数据库

CREATE DATABASE IF NOT EXISTS `sunshine` CHARSET utf8 COLLATE utf8_bin;

-- 显示完整的创库语句

SHOW CREATE DATABASE `sunshine`;

-- 显示MYSQL编码

SHOW CHARSET;

-- 查看字符集

SHOW VARIABLES LIKE '%char%';

-- 设置字符集

CREATE DATABASE IF NOT EXISTS `sunshine` CHARSET SET = utf8;
可简写为:
CREATE DATABASE `sunshine` CHARSET utf8;

-- collation的主要作用是指定字符排序时使用哪种字符编码作为排序的依据

SHOW COLLATION;
SHOW COLLATION WHERE Charset = 'utf8';

-- 登录数据库时选择数据库

mysql -h localhost `sunshine` -u root -p

-- 使用数据库

USE `sunshine`;

-- 查看全部数据表

SHOW TABLES;

-- 创建数据表(单个字段)

CREATE TABLE IF NOT EXISTS `tbl_user` (
    `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT
);

-- 创建数据表(多个字段)

CREATE TABLE IF NOT EXISTS `tbl_collect` (
    `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `userid` int(10),
    `username` varchar(50) DEFAULT ''
);

-- 得到创建数据表语句

SHOW CREATE TABLE `sunshine`;

-- 打印表结构

DESC `tbl_user`;

-- 打印存储引擎(主要使用的是两种引擎：MyISAM和InnoDB)

SHOW ENGINES;

-- MySQL默认的引擎是InnoDB，如果需要使用其他的引擎，需要特别指定

CREATE TABLE IF NOT EXISTS `tbl_collect` (
    `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `userid` int(10),
    `username` varchar(50) DEFAULT ''
) ENGINE MyISAM;

-- 删除数据表

DROP TABLE IF EXISTS `tbl_comment`;

-- 删除数据库中全部数据表

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'subject_3.0';

-- 修改表名

ALTER TABLE `tbl_collect` RENAME `tbl_comment`;

-- 增加表字段

ALTER TABLE `tbl_user` ADD `addtime` int(10);

-- 将增加的表字段放在所有字段的最前面

ALTER TABLE `tbl_user` ADD `sorts` tinyint(1) FIRST;

-- 将增加的表字段放在任意字段后面

ALTER TABLE `tbl_user` ADD `state` tinyint(1) AFTER `id`;

-- 删除字段

ALTER TABLE `tbl_user` DROP `state;

-- 修改字段的属性

ALTER TABLE `tbl_user` MODIFY `id` int(11);

-- 修改字段名和属性

ALTER TABLE `tbl_user` CHANGE `addtime` state tinyint(1);

-- 显示警告

SHOW WARNING;

-- 插入单条数据

INSERT INTO `tbl_user` ( `id`, `sorts`, `state` ) VALUES (1,1,1);
INSERT INTO `tbl_user` VALUES (1, 2, 1);
INSERT INTO `tbl_user` SET `sorts`=1, `id`=3, `state`=1;

-- 插入多条数据

INSERT INTO `tbl_user` VALUES (1, 4, 1),(1, 5, 1),(1, 6, 1);

-- 查询用户表中数据

SELECT * FROM `tbl_user`;
SELECT `id`,`sorts`,`state` FROM `tbl_user`;

-- 查询和计算

SELECT 2*3 FROM `tbl_user`;

-- 条件查询

-- 比较运算符 = , > , < , <> , <= , >=

SELECT * FROM `tbl_user` WHERE `id` = 1;
SELECT * FROM `tbl_user` WHERE `id` > 1;
SELECT * FROM `tbl_user` WHERE `id` < 6;
SELECT * FROM `tbl_user` WHERE `id` <> 1;
SELECT * FROM `tbl_user` WHERE `id` <= 1;
SELECT * FROM `tbl_user` WHERE `id` >= 1;

-- 逻辑运算符 AND、OR

SELECT * FROM `tbl_user` WHERE `id` > 1 AND `id` < 6;
SELECT * FROM `tbl_user` WHERE `id` BETWEEN 1 AND 6;
SELECT * FROM `tbl_user` WHERE `id` = 1 OR `state` = 1;

-- 字符串模糊查询 LIKE ‘%%’ NOT LIKE ‘%%’

SELECT * FROM `tbl_user` WHERE `name` LIKE '%W%';
SELECT * FROM `tbl_user` WHERE `name` LIKE '%W';
SELECT * FROM `tbl_user` WHERE `name` LIKE 'W%';

-- 按范围查询 IN、 NOT IN

SELECT * FROM `tbl_user` WHERE `age` IN (20,25,30);
SELECT * FROM `tbl_user` WHERE `age` NOT IN (20,25,30);

-- 对查询出来的结果进行排序

SELECT * FROM `tbl_user` ORDER BY `id` DESC;
SELECT * FROM `tbl_user` ORDER BY `id` ASC;

-- GROUP BY搭配COUNT函数使用(譬如30个学生分别来自4个城市，按城市分组后，查询出来的记录数是4条)

SELECT `city`,COUNT(city) AS num FROM `tbl_user` GROUP BY `city` DESC;

-- HAVING只能使用在group by之后，用来对分组的结果进行再筛选

SELECT `city`,COUNT(city) AS num FROM `tbl_user` GROUP BY `city` DESC HAVING COUNT(city) > 4;

-- having和where的区别

SELECT `name` FROM `cv` WHERE `age` > 20 GROUP BY `city` DESC;
SELECT `city`,COUNT(city) FROM `tbl_user` GROUP BY `city` DESC HAVING COUNT(city) > 4;

-- 两者的区别是先后顺序不同，where是先筛选数据，having对分组的结果进行筛选

-- 按价格和销量排序

SELECT * FROM `product` ORDER BY `price` DESC, `sold` ASC;

-- LIMIT offset：从符合条件的所有记录的第几条开始取数据,rows：取多少条数据

SELECT `name` FROM `tbl_user` LIMIT 0,9;

-- 修改数据

UPDATE `tbl_user` SET `name` = 'wt', 'id'=1, 'state'=3 WHERE `userid` = 1;

-- 删除数据

DELETE FROM `tbl_user` WHERE `userid` = 1;

-- 清空表中全部数据

TRUNCATE TABLE `tbl_user`;

-- 数据类型

整型类：BIGINT、INT、MEDIUMINT、SMALLINT、TINYINT
浮点型：FLOAT、DOUBLE
定点型：DECIMAL
字符类：CHAR、VARCHAR、TEXT
日期时间类：DATE、DATETIME、TIMESTAMP
其他类型：ENUM、SET、BIT、BLOB
浮点型：FLOAT(3,2)   输入1，保存为1.00……  
FLOAT(4,2)时，输入12.4875，按保留2位进行四舍五入，得到12.49

-- FORMAT：保留几位小数

SELECT FORMAT(a - b, 1) FROM `float`;

-- CHAR(M) 是用来保存固定字符长度的字符串，譬如身份证号、手机号之类。

-- VARCHAR(M) 是用来保存可变长度的字符串，M是最大字符数

CREATE TABLE `enumtest` (
    test_field ENUM('one', 'two', 'three')
);

-- BIT类型（大部分是用来处理权限的）

CREATE TABLE `bittest` (
    test_field BIT(4)
);

INSERT INTO `bittest` VALUES (11);
INSERT INTO `bittest` VALUES (b'11');

SELECT bin(`test_field`) FROM `bittest`; -- (查询方式不同)

-- SET类型与ENUM非常相似，最大的区别是，ENUM是单选，而SET是多选，SET的最多可以设置64个可选值

CREATE TABLE `settest` (
    `test_field` SET('one', 'two', 'three')
);

INSERT INTO settest VALUES ('one,two');  
-- 插入数据时需要注意，逗号（,）后面不可以有空格。如果插入不存在的值，系统会自动截取掉多余的值

-- MYSQL函数

-- COUNT 计数
SELECT COUNT(*) FROM tbl_name;

-- NOW 取系统当前时间
INSERT INTO tbl_name VALUES (NOW());

-- FORMAT 指定小数位数
SELECT FORMAT(col_name, n) FROM tbl_name;

-- 求余数
SELECT MOD(31, 8);

-- TRUNCATE(小数，小数点后保留位数) 直接截取不四舍五入（注意与FORMAT的区别）
SELECT TRUNCATE(col_name, 1) FROM tbl_name;

-- 返回字节个数
SELECT CHAR_LENGTH('abcdefg');

-- 合并字符串
SELECT CONCAT('hello','world');

-- 使用分隔符合并字符串
CONCAT_WS('hello','-','world');   -- hello-world

-- 清除文字两侧的空白字符
SELECT TRIM('  HELLOWORLD  ')   -- HELLOWORLD

-- 假设有字符型字段friends保存了所有好有的姓名，其中保存的值均是以逗号（,）分隔（注意无空格）
-- 譬如：tom,jack,andy 或者 andy,susan,tommy
-- 如果想要查询好友是tom的记录，使用like查询很不方便
-- 可以使用SELECT * FROM `friends` WHERE FIND_IN_SET('tom',friends);

-- 将时间转化为时间戳
SELECT UNIX_TIMESTAMP('2013-01-01 10:10:10');  -- (date 类型数据转换成 timestamp 形式整数)

-- 将时间戳转化为时间
SELECT FROM_UNIXTIME(1479313381);               2012-12-12 08:32:40
select from_unixtime(1355272360,'%Y%m%d');      20121212

-- IF 函数(如果表达式expr是TRUE，则IF()的返回值为T；否则IF()返回F)
SELECT IF (2>1,'T','F');

-- DATABASE 用来获取当前数据库的库名
SELECT DATABASE;

-- MD5 不可逆的加密函数
SELECT MD5('root'); 

-- 将字段设置唯一

ALTER TABLE `cv` ADD UNIQUE KEY (`id`);
建表语句中添加：
CREATE TABLE `cv` (
...
  unique (`id`)
);

-- 将字段去除唯一

ALTER TABLE `cv` DROP INDEX `id`;

-- 设置主键

ALTER TABLE `cv` MODIFY `id` int(10) UNSIGNED PRIMARY KEY;

-- 删除主键

ALTER TABLE `cv` DROP PRIMARY KEY;

-- 复合主键

ALTER TABLE `cv` ADD PRIMARY KEY (`id`,`name`);

-- 建表时设置主键

CREATE TABLE `cv` (
    `id` int(10) PRIMRAY KEY,
    `name` varchar(20)
)

-- 复合主键设置

CREATE TABLE `cv2` (
    `id` int(10),
    `name` int(10),
    PRIMARY KEY (`id`,`name`)
);

-- 给字段设置自增长

CREATE TABLE `cv` (
    `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT
);

ALTER TABLE `cv` MODIFY `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT;

-- 获取前一条INSERT语句或者UPDATE语句执行后，对应记录中AUTO_INCREMENT字段的值

SELECT LAST_INSERT_ID();

-- 字段设置默认值

ALTER TABLE `cv` MODIFY `sex` tinyint(1) NOT NULL DEFAULT 'm';

-- 设置字段不为空

ALTER TABLE `cv` MODIFY `sex` tinyint(1) NOT NULL DEFAULT 'm';

-- NOT NULL 和DEFAULT 关系

注意：在MySQL中，null是空值，它不代表0，也不代表’’，当你忘记给一个字段设置数据时，MySQL默认会设置一个null，这时not null才会起作用;
当用户没有给字段赋值时，默认赋null
如果字段设置了not null时，系统会查找是否有设置default，如果设置了，则用default来代替null；
当设置了not null，又没有设置default时，系统会报错

-- 连表查询

-- LEFT JOIN

LEFT JOIN（左连接）以edu表的记录为基础，左表的记录会全部显示出来，右表如果没有能够关联上的数组，则记录中显示NULL

SELECT t1.`id`,t1.`name`,t1.`age`,t2.`school`,t2.`grade` FROM `tbl_user` t1 LEFT JOIN `tbl_school` ON t1.`id` = t2.`userId`;

-- RIGHT JOIN

RIGHT JOIN（右连接）与左连接相反，查询出的结果以右表为主，如果edu表中的school_id与school表中的id有对应不上的情况，school_id错误的简历信息将无法显示

SELECT t1.`id`,t1.`name`,t1.`age`,t2.`school`,t2.`grade` FROM `tbl_user` t1 RIGHT JOIN `tbl_school` ON t1.`id` = t2.`userId`;

-- INNER JOIN

INNER JOIN（内连接）既不以左表为准，也不以右表为准，只显示两边完全符合条件的记录

SELECT t1.`id`,t1.`name`,t1.`age`,t2.`school`,t2.`grade` FROM `tbl_user` t1 INNER JOIN `tbl_school` ON t1.`id` = t2.`userId`;

-- 外键

如果一张数据表中的某个字段的值是另外一张表的主键，那么我们可以称该字段为表的外键，因此，我们可以称：cv_id是edu表的外键；school_id也是edu表的外键。外键主要是用来保证数据的完整性，表里是否设置外键不影响其使用

-- 添加外键

如果我们给数据表添加外键，需要注意，表中的外键所保存的值，与关联表里的id是否真正的能够对应更直白的说就是，shool_id里保存的id在school表里是否存在，如果不存在，添加外键时，系统会报错，因此，在测试时，我们可以直接执行truncate来清空错误数据，但是如果是在产品线上，需要通过批处理脚本，来统一修正错误数据，然后才可以添加外键

ALTER TABLE edu ADD FOREIGN KEY(cv_id) REFERENCES cv(id) ON DELETE CASCADE;
ALTER TABLE edu ADD FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE;

-- 约束条件

当主表的数据被删除时，关联表的从数据如何处理。譬如school表里的记录被删除时，edu表里保存同样school_id的数据如何被系统自动处理
1、RESTRICT：约束     如果存在从数据，不允许删除主数据。 
2、NO ACTION     如果存在从数据，不允许删除主数据。
3、CASCADE：级联     删除主数据，顺便也删掉从数据。 
4、SET NULL     删除主数据，从数据外键的值设为NULL。 

-- 创建索引（如何快速的检索数据）

ALTER TABLE tbl_name ADD INDEX index_name (col_name);
检查select语句是否使用了正确的索引，我们一般在select之前添加explain
ALTER TABLE cv ADD INDEX idx_gender(gender);
EXPLAIN SELECT * FROM cv WHERE gender = 1;

-- 组合索引

对多个字段组合创建的索引ALTER TABLE tbl_name ADD INDEX index_name (column1, column2);当where条件里不仅按一个字段去进行检索，还使用了其他字段，如果这种检索是经常性的操作，那么我们需要创建组合索引假设有组合索引（A,B,C）三个字段，相当于创建了三个索引可以使用（以左为准）（A,B,C）（A,B）（A）所以，查询的顺序很重要