MySQL学习笔记汇总
来源:互联网 发布:vb的注释符号 编辑:程序博客网 时间:2024/05/29 23:22
USE test_DB;
CREAT TABLE test_tbl;
SELECT * FROM test_tbl;
SELECT * FROM test_tbl WHERE name = ‘Anne’;
(*:返回所有的列)
ALTER TABLE tbl_user RENAME TO tbl_member;
RENAME TABLE tbl_member TO tbl_user;
ALTER TABLE tbl_user ADD email VARCHAR(255) NOT NULL;
ALTER TABLE tbl_user CHANGE id newid INT(3);
ALTER TABLE tbl_user DROP email;
DROP TABLE tbl_user;
SELECT drink_name,id FROM test_tbl WHERE main='soda';
SELECT drink_name FROM test_tbl WHERE main='juice';
SELECT location FROM test_tbl WHERE main='soda' AND rating=10;
SELECT drink_name FROM test_tbl WHERE main='soda' AND amount>1;
<>:不等于
SELECT drink_name FROM test_tbl WHERE cost>=3.5 AND col<50;
按字母排序:
SELECT drink_name FROM test_tbl WHERE drink_name>='L' AND drink_name<'M';
SELECT drink_name FROM test_tbl WHERE color IS NULL ;
LIKE:通配符
SELECT * FROM test_tbl WHERE location LIKE '%CA';
//以CA为结尾的值
'_im' :下划线代替1个字符 Tim Jim Kim
选取范围:
SELECT drink_name FROM test_tbl WHERE cal>=30 AND cal<=60;
SELECT drink_name FROM test_tbl WHERE cal BETWEEN 30 AND 60;
SELECT data_name FROM test_tbl WHERE rating IN('good','deli','cool');
//IN ,NOT IN表示值的集合
NOT:
WHERE NOT / AND NOT / OR NOT
插入:
INSERT INTO test_tbl VALUES('zippo','China');
删除:
DELETE FROM test_tbl WHERE act='dancing';
//DELETE之前可以先用SELECT确认
UPDATE test_tbl SET color='red' WHERE color='blue';
UPDATE test_tbl SET type='boy',age=16;
UPDATE test_tbl SET cost=cost+1 WHERE color='red' OR color='blue';
将事物的综合信息拆分成小块信息,便于组织表。
规范化表:让数据具有原子性。
主键中的数据不能重复,例如身份证号,学号,手机号。
主键值不可以被修改,AND 不能为NULL,一般来说考虑用ID。
CREATE TABLE my_table(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL ,
PRIMARY KEY (id)
)
为现有表添加主键:
ALTER TABLE my_table
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST ,
ADD PRIMARY KEY (id);
DESCRIBE test_tbl;
改表名:
ALTER TABLE test_tbl RENAME TO new_id;
改列名,类型:
ALTER TABLE list_tbl CHANGE COLUMN number list_id
INT NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY (list_id);
ALTER TABLE list_tbl CHANGE COLUMN name name_new VARCHAR(10),
CHANGE COLUMN age locat VARCHAR(30);
只改列的类型:
ALTER TABLE test_tbl MODIFY COLUMN name_col VARCHAR(10);
添加列:
ALTER TABLE test_tbl ADD COLUMN name_id INT;
删除列:
ALTER TABLE test_list DROP COLUMN name_id;
字符串函数(与SELECT配合使用):
SELECT最后两个字符: RIGHT() LEFT()
SELECT right(name_col,2) FROM my_tbl;
SELECT逗号前面的内容:从name_col列中寻找第1个逗号前面的所有内容:
SELECT substring_index(name_col,',',1) FROM my_tbl;
类似函数:
截取字符串GOOD TIME 从第5个起,截3个:
SELECT substring('GOOD TIME',5,3);
UPPER('delete'):全大写
LOWER('Taxi'):全小写
REVERSE('good'):反转字符串
LTRIM(' dog') RTRIM('dog ') 返回清除左右空格的字符串
LENGTH('just do it'):返回字符串长度
UPDATE my_tbl SET state=RIGHT('MUFC',2);
CASE语句:
UPDATE my_tbl SET name=
CASE WHEN location='ss' THEN 'Jim'
WHEN location='RM' THEN 'Ben'
ELSE 'KATE'
END ;
排序-ORDER BY:
SELECT title,name FROM test_tbl
WHERE title LIKE 'A%' AND type='family' ORDER BY title;
SELECT title,name FROM test_tbl ORDER BY title,name;
反转排序:
SELECT title,name FROM test_tbl ORDER BY title,name DESC ;
sum avg min max
SELECT sum(sales_col) FROM test_tbl WHERE name='Jim';
SELECT name,sum(sales) FROM test_tbl GROUP BY name ORDER BY sum(sales) DESC ;
count:返回指定列的行数
SELECT count(sale_date) FROM test_tbl;
- MySQL学习笔记汇总
- mysql 汇总数据 学习笔记
- MySQL学习笔记—基本操作汇总
- MySQL学习笔记—基本操作汇总
- MYSQL学习笔记(八)汇总数据
- MYSQL 学习笔记 之 常用函数汇总
- MySQL 学习笔记(5)MySQL常用函数汇总
- MySQL学习笔记—SQL服务器模式汇总
- MySQL学习资源汇总
- MySQL参考手册学习汇总
- mysql数据库学习汇总
- Tinyos学习笔记汇总
- NUTCH学习笔记汇总
- ***********Linux学习笔记汇总***********
- Hadoop学习笔记汇总
- Hive学习笔记汇总
- Hbase学习笔记汇总
- GObject 学习笔记汇总
- 在IntelliJ上操作GitHub
- 【LeetCode】 002. Add Two Number
- 利用触发器生成三分频
- Java 复合函数-函数作为返回值
- wxPython学习笔记
- MySQL学习笔记汇总
- Flask笔记:(Flask开发基础与入门)
- 【LeetCode】 012. Integer to Roman
- libxml2->lxml 在win10 64位下的安装
- XPATH(lxml)爬虫测试
- 来自一线开发者的Swift学习资源推荐
- Leetcode 139. Word Break (Medium) (cpp)
- 基于 Swift 创建 CocoaPods 完全指南
- e820 -- retrieve memory map from BIOS