mysql的基本使用

mysql基本使用：

登录MySQL
  mysql -h host -u user -p
 创建数据库和表
  CREATE DATABASEbookstore;
  USE bookstore;
  CRAETE TABLE books ( book_idINT, title VARCHAR (50), author VARCHAR(50) );
 以表格的形式查看创建的表内容
  DESCRIBE books；
 在创建好的表格当中进行数据修改
   ALTER TABLE books CHANGECOLUMN book_id book_id INT AUTO_INCREMENT PRIMARY KEY,CHANGE COLUMNautor author_id INT,ADD COLUMN genreENUM('novel','poetry','drama'),
   ADD COLUMN publisher_idINT,ADD COLUMN pub_year VARCHAR (4);
 显示数据库列表
  SHOW DATABASE;
 数据表列表
  SHOW TABLES;
 插入数据
   INSERT INTO authors(authors_last,author_first,country)VALUES('Greene','Graham','UnitedKingdom');
 查看标志码
  SELECT LAST_INSERT_ID();
  SELECT author_id FROMauthors；
 查看列表中的所有数据：
  SELECT * FROM books；
 查看指定列中的内容
  SELECT book_id， title，description FROM books where genre='novel';
 连接表和表之间的内容
  SELECT book_id, title,pub_year,CONCAT (author_first, '',author_last)AS author FROM books JOINauthors USING (author_id)WHERE author_last='Greene';
  SELECT book_id, title,pub_year,CONCAT (author_first, '',author_last)AS author FROM books JOINauthors ON (author_id = write_id)WHERE author_last='Greene';
 排序限制分组：orderby先对title进行排序，再按照pub_year进行排序，默认是升序，LIMIE限制行数（LIMIT20,10）代表从20行开始取数
  SELECT book_id, title,publisherFROM books JOIN authors USING (author_id)JOIN publishers USING(publisher_id)
  WHERE author_last='Greene'ANDgenre='play'ORDER BY title,pub_year LIMIT 20；
  SELECT book_id, title,publisherFROM books JOIN authors USING (author_id)WHERE author_last='Greene'GROUP BY title；
 分析和处理数据
  SELECT COUNT(*) FROM books JOINauthors USING (author_id) WHERE title = 'arm' AND author_last ='boyd' ;  #数目统计
  SELECT purchase_date AS'Purchase Date' FROM orders WHERE sale_id = '1250';   #打印日期
  SELECT CONCAT (MONTHNAME(purchase_date)，'',DAYOFMONTH(purchase_date),',',YEAR(purchase_date))AS 'Purchase Date' FROM orders WHERE sale_id = '1250' ;#英语月份表示日期
  SELECTDATE_FORMAT(purchase_date, "%M %d,%Y")AS 'Purchase Date' FROMorders WHERE sale_id = '1250'p;
 修改数据
  UPDATE books SET pub_year ='1938' WHERE book_id = '2';(修改的值多项时中间用逗号隔开)
  ALTER TABLE books CHANGE COLUMNisbn isbn VARCHAR(20) UNIQUE;(设置成unique列，值是唯一的)
  REPLACE INTO books(title，author_id, isbn,genre,pub_year)VALUES('BRIGHTONROCK',1,'123456','NOVEL','1938'),('THE QUIETAmECIAN',1,'123456','NOVEL','1955');
 删除数据
  DELETE FROM books WHEREauthor_id = (SELECT authors.author_id FROM authors WHEREauthor_last = 'Rowling' AND author_first = 'j.k');
  DELETE FROM authors WHEREauthor_last='Rowling'AND author_first='j.k';
 设置变量
  SET @POTTER=(SELECT author_idFROM authors WHERE author_last = 'Rowling' AND author_first ='j.k');
  DELETE FROM books WHEREauthor_id = @POTTER;
  DELETE FROM authors WHEREauthor_id = @POTTER;
 查询数据
  SELECT book_id,title FROM booksWHERE author_id = 1 AND title LIKE '�C'OR author_id = 1 AND titleLIKE '�d';(在指定列中查找含有相关单词的图书信息)
  SELECT book_id,title，CONCAT(author_first,'',author_last) AS author FROM books JOIN authorsUSING (author_id)WHERE title LIKE '�F'AND title LIKE '�D';
  (按照相关文字进行查找)
 批量导入数据
  TITLE|AUTHORLAST|AUTHOR FIRST|DATE|
  LOAD DATA INFILE'/tmp/books.txt'REPLACE INTO TABLE authors FIELDS TERMINATED BY '|'LINES TERMINATED BY '\n' TEXT_FIELDS ( col1,col2,col3,col4 ) SETauthor_last =col2,
  author_first=col3 IGNORE col1,col4，1 LINES;
 命令行界面
  mysql--user='root' --password='dongtian' -e "SELECT vendor, telephoneFROM vendors
  WHERE state='LA'"bookstore
 合并两张表的相关内容
  INSERT INTO tab3 SELECT num,name,age FROM tab1WHERE num='2';
 授予用户全局级全部权限
  GRANT ALL PRIVILEGES ON *.* to'zhu'@'%'ID BY 'DONGTIAN';
 授予用户针对某一个数据库的全部权限
 GRANT ALL PRIVILEGES ON BOOKSTORE.* to 'root'@'%' identified by 'dongtian'
 (%代表所有主机)
 查看MySQL支持的编码
 show character set；
 查看MySQL当前使用的编码：
 show variables like 'character_set%'
 show variables like 'collation%'
 通过命令修改一个数据库的编码
 ALTER DATABASE dongtian CHARACTER SET utf8COLLATE utf8_general_ci;
 flush privileges;刷新数据库 
 
 GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY'dongtian' WITH GRANT OPTION;
 
 创建与删除数据库空间
 mysqladmin -uroot -p create TUTORIALS
 mysqladmin -u root -p drop TUTORIALS
 
mysql 数据库的备份与还原例如一个数据库test
mysqldump -u root -p --database test >test.sql
mysql -u root -p ec_school< ec_school.sql

mysql -u root -pdongtian -e "show databases;"| sed '1,2d'

mysql之触发器trigger
触发器(trigger)：监视某种情况，并触发某种操作。
触发器创建语法四要素：1.监视地点(table) 2.监视事件(insert/update/delete)3.触发时间(after/before) 4.触发事件(insert/update/delete)
语法：
create trigger triggerName
after/before insert/update/delete on 表名
for each row  #这句话在mysql是固定的
begin
sql语句;
end;

after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；也就是说先插入订单记录，再更新商品的数量；
before是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作；

当新增一条订单记录时，判断订单的商品数量，如果数量大于10，就默认改为10
create trigger tg6
before insert on o
for each row
begin
　　if new.much > 10 then
　　　　set new.much = 10;
　　end if;
　　update g set num = num - new.much where id = new.gid;
end$
insert into o(gid,much) valus(1,20)$

示例：

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOREACH ROW
    ->BEGIN
   ->    IF NEW.amount < 0 THEN
   ->        SET NEW.amount = 0;
   ->    ELSEIF NEW.amount > 100 THEN
   ->        SET NEW.amount = 100;
   ->    END IF;
    ->END;//
mysql> delimiter ;