mysql基础知识笔记

• Mysql基础笔记
  • 环境配置
  • 基本概念
  • mysql命令行
    • 进入
    • use
    • show
  • 查询
    • select
    • order by
    • where
    • like
    • 数据过滤regexp
    • concat
    • 文本函数
    • 日期函数
    • 数值函数
    • 聚集函数
    • group
    • 子查询
    • 联结表
    • 高级联结表
    • 组合查询
    • 全文本搜索
  • 插入数据
  • 更新
  • 删除
  • 表操作
  • 视图操作
  • 存储过程
  • 游标
  • 触发器
  • 事务
  • 导入导出
  • 性能研究
  • 实时监控

sudo apt-get install mysql-server mysql-client
netstat -nltp | grep mysql
配置文件 /etc/mysql/my.conf

InnoDB是一个可靠地事务处理引擎，不支持全文本搜索
MyISAM是一个性能极高的引擎，支持全文本搜索，不支持事务处理

保存有组织的数据的容器（通常是一个文件或一组文件）

某种特定类型数据的结构化清单

关于数据库和表的布局及特性的信息

表中的一个字段，所有表都是由一个或多个列组成的

所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据

表中的一个记录

一列或一组列，其值能够唯一区分表中的每个行

输入： mysql
或者   mysql -u ken
       mysql -u ken -p -h myserver -P 9999 【给出用户名，主机名，端口】

获取帮助: mysql --help

1.命令必须；或\g结束，仅Enter不执行明林
2.help 或\h获得帮助
3.quit或exit退出

MySQL Administrator
MySQL Query Browser

>CREATE DATABASE MYSQLDATA

use db_name

show databases;

use db_name;
show tables;

show columns from table_name;
or
desc table_name;

show create database db_name;
show create table table_name;

show status  服务器状态信息
show grants  显示授权用户
show errors/show warnings 显示服务器错误或警告信息

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

>SELECT col FROM tb_name;

>SELECT col1, col2
 FROM tb_name

>SELECT *
 FROM tb_name;
#除非确认要用到所有列

>SELECT DISTINCT col
 FROM tb_name

>SELECT col1
 FROM tb_name
 LIMIT 5;
返回不多于五行

>SELECT col1
 FROM tb_name
 LIMIT 5, 5
 第一个为开始位置，初始为0.第二个为显示个数
等价于LIMIT 5 OFFSET 5

>SELECT col1
 FROM tb_name
 ORDER BY col1

>SELECT col1, col2, col3
 FROM tb_name
 ORDER BY col1, col2

>SELECT col1, col2
 FROM tb_name
 ORDER BY col1 DESC;【默认ASC】
注意：如果想在多个列上排序，必须对每个列使用DESC
注意：ORDER BY必须放在LIMIT之前

>SELECT col1, col2
 FROM tb_name
 WHERE col1 = 2.5;

>SELECT col1, col2
 FROM tb_name
 WHERE col1 <> 1000

>SELECT col1, col2
 FROM tb_name
 WHERE col1 BETWEEN 5 AND 10

>SELECT col1
 FROM tb_name
 WHERE col2 IS NULL
NULL, 无值，它与字段包含0，空字符串或仅仅包含空格不同

>SELECT col1
 FROM tb_name
 WHERE col1=100 AND col2 <= 10

>SELECT col1
 FROM tb_name
 WHERE col1=100 OR col2 <= 10

select prod_id from products where (prod_price < 2.5 or vend_id = 1000) and prod_price > 1;

>SELECT col1
 FROM tb_name
 WHERE col1 IN (1001,1002)

>SELECT col1
 FROM tb_name
 WHERE col1 NOT IN (1001,1002)

=
<>
!=
<
<=
>
>=
between A and B

>SELECT col1
 FROM tb_name
 WHERE col1 LIKE ‘jet%’

%匹配0个或多个字符

>SELECT col1
 FROM tb_name
 WHERE col1 LIKE ‘_ ton anvil’

>SELECT col1
FROM tb_name
WHERE col1 REGEXP ‘1000’

REGEXP ‘.000’
REGEXP对列值匹配

>SELECT col1
 FROM tb_name
 WHERE col1 REGEXP ‘1000|2000’

select prod_id from products where prod_name regexp '[1|2]000';

select prod_id from products where prod_name regexp '[1-5]000';

必须使用\\为前导。 \\-
>SELECT col1
 FROM tb_name
 WHERE col1 REGEXP ‘\\.’

like整列匹配
regexp 列值内匹配

>SELECT Concat(name, ‘ ----‘, age)
 FROM tb_name

>SELECT Rtrim(name)
 FROM tb_name

Ltrim() Trim()

>SELECT Concat(name, ‘---‘, age) AS info
 FROM tb_name

>SELECT quantity * item_price AS total_price
 FROM tb_name

支持+ - * /

left()  串左边字符
length() 串长度
locate() 找出串的一个子串
lower() 转为小写
ltrim() 去掉左边空格
right() 返回串右边字符
rtrim() 去掉串右边空格
soundex() 返回字符串soundex值
upper() 大写

>SELECT Upper(name)
FROM tb_name

adddate() 增加一个日期-天或周
addtime() 增加一个时间
curdate() 返回当前日期
curtime() 返回当前时间
date() 返回日期时间的日期部分
datediff() 计算两个日期差
date_add() 高度灵活的日期运算函数
date_format() 返回一个格式化的日期或时间串
day() 返回一个日期的天数部分
dayofweek() 对于一个日期，返回对应的星期几
hour()
minute()
month()
now() 当前日期和时间
second()
time() 当前日期时间的时间部分
year()

>SELECT col1
 FROM tb_name
 WHERE Date(order_date) = ‘2005-09-01’

Date()返回日期时间的日期部分
Day()返回日期的天数部分

abs()
cos()
exp() 指数
mod()
pi() 返回圆周率
rand() 随机数
sin()
sqrt()
tan()

>SELECT AVG(price) AS avg_price
 FROM tb_name

select count(*) from products; #无论Null还是非空，均纳入计数
select count(prod_id) from products; #计数有值记录，忽略NULL值

>SELECT MAX(price) AS max_price
 FROM tb_name

>SELECT MIN(price) AS min_price
 FROM tb_name

>SELECT SUM(quantity) AS total
 FROM tb_name
#sum函数忽略值为NULL的行

>SELECT id, COUNT(*) AS num_prods
 FROM tb_name
 GROUP BY id

1.group by 可以包含任意数目的列
2.group by 中每个列都必须是检索列或有效的表达式（但不能使聚集函数）
3.除聚集函数外，select语句中的每个列都必须在group by子句中出现
4.如果分组列有Null值，Null将作为一个分组返回
5.group by 子句必须出现在where子句之后, order by 之前

>SELECT cust_id, COUNT(*) AS orders
 FROM orders
 GROUP BY cust_id
 HAVING COUNT(*) > 2

where在分组前过滤，having在分组后过滤

>SELECT cust_id
 FROM orders
 WHERE order_num IN (SELECT order_num
                    FROM orderitems)

>SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
 FROM customers
 ORDER BY cust_name

>SELECT vend_name, prod_name, prod_price
 FROM vendors, products
 WHERE vendors.vend_id = products.vend_id
 ORDER BY vend_name, prod_name;

>SELECT vend_name,prod_name,prod_price
 FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id

>SELECT prod_id, prod_name
 FROM products
 WHERE vend_id = (SELECT vend_id FROM products
                WHERE prod_id = ‘DTNTR’)

>SELECT p1.prod_id, p1.prod_name
 FROM products AS p1, products AS p2
 WHERE p1.vend_id = p2.vend_id
        AND p2.prod_id = ‘DTNTR’

>SELECT customers.cust_id, orders.order_num
 FROM customers LEFT OUTER JOIN orders
      ON customers.cust_id = orders.cust_id

>SELECT vend_id, prod_id, prod_price
 FROM products
 WHERE prod_price <=5
 UNION
 SELECT vend_id, prod_id, prod_price
 FROM products
 WHERE vend_id IN (1001,1002)

UNION自动去除重复行
UNION ALL 保留

对所有SELECT生效

>CREATE TABLE productnotes(
 note_id int NOT NULL AUT_INCREMENT,
 note_text text NULL,
 FULLTEXT(note_text)

>SELECT note_text
 FROM tb_name
 WHERE Match(note_text) Against(‘rabbit’)

>SELECT note_text
 FROM productontes
 WHERE Match(note_text) Against(‘heavy’ IN BOOLEAN MODE)

>INSERT INTO customers(cust_name,
                     cust_address)
 VALUES(‘Pep’, ‘100 main street’)

>INSERT INTO customers(cust_name,
                     cust_address)
 VALUES(‘Pep’, ‘100 main street’),
       (‘Tim’, ‘200 main Street’);

>INSERT INTO customers(cust_name,
                     cust_address)
 SELECT cust_name, custaddress
 FROM custnew;

>UPDATE customers
 SET cust_email = ‘a@fudd.com’
 WHERE cust_id = 10005

>UPDATE IGNORE customers

>DELETE FROM customers
 WHERE cust_id = 10006

>CREATE TABLE customers(
   cust_id int NOT NULL AUTO_INCREMENT,
   cust_name char(50) NOT NULL,
   vend_city char(50) NULL,
   quantity int NOT NULL DEFAULT 1,
   PRIMARY KEY(cust_id)
)ENGINE=InnoDB

>ALTER TABLE vendors
 ADD vend_phone CHAR(20)

>ALTER TABLE tb1 DROP COLUMN names;

>ALTER TABLE infos CHANGE list list tinyint NOT NULL DEFAULT '0'

>ALTER TABLE tb1 ADD primary key(id)

>ALTER TABLE tb1 DROP field_name

alter table customers change id id not null auto_increment primary key;

Alter TABLE tablename ADD new_field_id int(5) default 0 not null auto_increment ADD primary key(new_field_id)

ALTER TABLE example ADD ID INT NOT NULL;
ALTER TABLE example ADD UNIQUE(url)

>ALTER TABLE vendors
 DROP COLUMN vend_phone

>DROP TABLE customers2;

>DELETE FROM mytable;

>RENAME TABLE customers2 TO customers;
 ALTER TABLE 'oldname' RENAME TO 'newname'

>CREATE VIEW productcustomers AS
 SELECT cust_name, cust_contact
 FROM customers, orders, orderitems
 WHERE customers.cust_id = orders.cust_id

>SELECT cust_name, cust_contact
 FROM productcustomers
 WHERE prod_id = ‘TNT2’

>CREATE PROCEDURE productpricing()
 BEGIN
SELECT Avg(price) AS priceavg
FROM products;
 END;

>DROP PROCEDURE productpricing

>CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quality)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;

>CALL ordertotal(200005, @total;
>SELECT @total;

>SHOW CREATE PROCEDURE ordertotal;

>CREATE PROCEDURE processorders()
 BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_number FROM orders;

BEGIN ordernumbers;

FETCH ordernumbers INTO o;

CLOSE ordernumbers;
 END;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
REPEAT
    FETCH ordernumbers INTO o;
END;

>CREATE TRIGGER newproduct AFTER INSERT ON products
 FOR EACH ROW SELECT ‘Product added’

>DROP TRIGGER newproduct;

>CREATE TRIGGER neworder AFTER INSERT ON orders
 FOR EACH ROW SELECT NEW.order_num

>CREATE TRIGGER deleteorder BEFORE DELETE ON orders
 FOR EACH ROW
 BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
 END;

>CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
 FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

ACID
A,原子性，食物是一个原子操作单元，其对数据的修改，要么全执行，要么全不执行
C.一致性，事务开始和完成的时候，数据必须都保持一致状态（所有相关数据规则和内部数据结构）
I.隔离性，保证事务不受外部并发操作影响，即事务处理中间过程状态对外不可见
D.持久性，事务完成后，对数据修改时永久性的，及时出现系统故障也能够保持

>START TRANSACTION
 DELETE FROM ordertotals;
 SELECT * FROM ordertotals;

>ROLLBACK
回退

>COMMIT
提交

>SAVEPOINT delete1;

>ROLLBACK TO delete1;

>LOAD DATA LOCAL INFILE 'd:/mysql.txt' INTO TABLE mytable;

>use database;
>source d:/mysql.sql

INSERT INTO tab1(f1,f2)
SELECT a.f1, a.f2
FROM a WHERE a.f1='a'

mysqldump [OPTIONS] database [tables]

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

mysqldump –all-databases > allbackupfile.sql

mysql -hhostname -uusername -ppassword databasename < backupfile.sql
mysql -hhostname -ppassword databasename tablename < backuptablefile.sql

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

SELECT a,b,a+b 
FROM test_table
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

或者

mysql -u you -p -e "SELECT ..." >  file_name

1.什么情况下无法使用索引？

命令： show processlist;
或者 # mysqladmin -uroot -p密码 processlist

命令： show status;
或者 # mysqladmin -uroot -p密码 status






转帖自 http://ourmysql.com/archives/1461