mysqlベースコマンド2

FDML:データ操作語
Insert(データ挿入)
特殊文字は引用符で囲まなければなりません.そうしないと、データはすべて0になります.時間:000-00-00 00 00:00:00:00

#     
desc student;
#    (  )（          ）
insert into student values(1,'ss',33,'f','3333-4-5');
###    (  )（   ）
insert student(name,age) values('zeng','84');

#      （   ）(        null)
insert student(name,age) values('zeng','84'),('a','4'),('b','8');

#  
select * from student;

#into    
#      ，      
#           
#sql_mode         
[root@db03 ~]# vim /etc/my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

updateコマンド
update文を使用するには、必ずwhereを追加します.

#    
mysql> select * from student;
#    (             )
update student set age=18 where name='ss' and cometime='';
#      （        ）
update student set age=18 where id=2;
#  
mysql> select count(*) from student1;
mysql> select * from student;

deleteはデータを削除し、テーブルは削除しません

#    
select count(*) from student;
select * from student;
#    （  delete       where  ）,         ，       
delete from student where id=1;

#   （  bin-log     ）(    where     )（      ）
delete from student (where 1=1);
#   （    ）（         ）（     ）
truncate (table) student;
#   （    ）
drop table student;

deleteの代わりにupdateを使用する
オールドプレイヤー回帰

1.      status
alter table student add status enum('1','0') default 1;
2.  update  delete
#    
delete from student where id=1;
#    
update student set status='0' where id=1;
#   
select * from student;
desc student;

#status      
status='0'

DQL:データ照会言語
select

1.         
#       
select count(*) from student;
#            ，             
select * from student;
2.        
     
desc student;
#     
select user,host from mysql.user;
3.     #where
select name,gender from student where id='1';

#select                  ，      
#id='1'，               ，   where
#       ，     
#SQL  ，                       ，          
#select user,host select           

クエリーの練習

#  SQL  
[root@db03 ~]# rz -E
rz waiting to receive.
[root@db03 ~]# ll
total 396
-rw-r--r-- 1 root root 397334 Jul 15 10:21 world.sql
#  slq     drop
[root@db03 ~]# grep -ri drop world.sql 
DROP SCHEMA IF EXISTS world;
DROP TABLE IF EXISTS `city`;
DROP TABLE IF EXISTS `country`;
DROP TABLE IF EXISTS `countrylanguage`;
#  drop  

#  
  1
[root@db03 ~]# mysql -uroot -p123 < world.sql
  2(   )（SQL  ）
mysql> source /root/world.sql
  3
mysql> \. /root/word.sql

#  
[root@db03 ~]# mysql -uroot -p123 
mysql> show databases;
mysql> use world
mysql> show tables;
#     （  ）
mysql> desc city;

#     
select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
#   
select * from city;
| ID   | Name| CountryCode | District| Population |
select Name,Population from city;

#      （order by）
  
mysql> select Name,Population from city order by Population;
  
mysql> select Name,Population from city order by Population desc;

#       （limit N）
mysql> select population from city order by population limit 10;
mysql> select Name,Population from city order by Population desc limit 10;
#        ，  ，  （n+1--N）(#  )
select Name,Population from city order by xx desc limit 10,50;
select id,Name,Population from city order by xx desc limit 10,50;

#     （    ）
mysql -uroot -p123 -e 'use world;select id,name from city limit 0,100'
mysql -uroot -p123 -e 'select * from world.city'

条件クエリー

#        where  
where       ：（          ）
	#    
	=
	
	#    （int tinyint）
	!=
	>
	<
	>=
	<=
	
	#    
	or
	and
	
	#    ，            
	like 		

#2.         
mysql> select name,population from city where CountryCode='CHN';

#3.         
mysql> select name,population from city where countrycode='CHN' and District='heilongjiang';

#4.          100000   
mysql> select name,population from city where countrycode='CHN' and population < 100000;

#5.    （ ：like %）（like   ）
#   N     
mysql> select name,countrycode from city where countrycode like '%N';
#   N     
mysql> select name,countrycode from city where countrycode like 'N%';
#    N   
mysql> select name,countrycode from city where countrycode like '%N%';

#6.            
#   ...or...
mysql> select name,population from city where countrycode = 'CHN' or countrycode = 'USA';
#  in(...)
mysql> select name,population from city where countrycode in ('CHN','USA');
#  (select ...union all ...select ...)（      ）
mysql> select name,population from city where countrycode = 'CHN' union all select name,population from city where countrycode = 'USA';

selectの高度な使い方

#    ，    
	                      ，    ，    ' '（where），
	

1.    ，#  ，      ，        

2.   ,#        ，                    

3.   

4.   

従来の接続

#  
mysql> create table students(id int,name varchar(10));
mysql> create table score(id int,mark int);
#    
mysql> insert into students values(1,'qiudao'),(2,'qiandao'),(3,'zengdao');
mysql> insert into score values(1,80),(2,90),(3,100);
#    
mysql> select * from students;
mysql> select * from score;
------------------------------------ 2   
#       
mysql> select students.name,score.mark from students,score where students.id=score.id and name='qiudao';
#        
mysql> select students.name,score.mark from students,score where students.id=score.id

練習問題1
世界で100人未満の都市はどこの国ですか.都市の名前、国の名前、人口をリストしてください.

#1.         
                       100 

#2.      
country.name   city.name   city.population   

#3.           
city.countrycode   country.code

#4.    
desc xx;
mysql> select country.name,city.name,city.population from country,city where city.countrycode=country.code and city.population < 100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+

練習問題2
世界で100人未満の都市はどの国で、どんな言語を使っていますか.都市の名前、国の名前、人口、国の言語をリストしてください.

#1.         
                                 100 

#2.      
country.name   city.name   city.population   countrylanguage.language

#3.           
country.code   city.countrycode   countrylanguage.countrycode

#4. sql  （and     ）
desc xx;
mysql> select country.name,city.name,city.population,countrylanguage.language from country,city,countrylanguage where country.code=city.countrycode and city.countrycode=countrylanguage.countrycode and city.population < 100;
+----------+-----------+------------+-------------+
| name     | name      | population | language    |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown |         42 | Pitcairnese |
+----------+-----------+------------+-------------+

#     
1.     ，   
2.      
select  1. , 2. , 3. ... from  1, 2, 3... where  1. a= 2. b and  2. b= 3. c... and  xx. xx < 100;

#city.population   population      ，      （      ）

セルフジョイン
自己接続は同じフィールドを自分で検索します
2表の調査に適用

#                  
mysql> select city.name,city.countrycode,countrylanguage.language,city.population from city natural join countrylanguage where population < 100 order by population;

#                    （    ）
mysql> select country.name,city.name,city.population from city natural join country where population < 100;
         Name ，       ，  #SQL    ，     
#   
mysql> desc city;
| Name
mysql> desc country;
| Name
#   
select * from city limit 10;
select * from city limit 10;


#  ：
1.              （     ）
2.      （selec count(*)）
3.NATURAL JOIN    
4.            

mysql> select  1. 1， 2. 2... from  1 natural join  2 where  x < 100;

ないぶせつぞく
関連データが同じであればよい
2表連査、3表連査に適用

#  ：   （     ）,   join 
	 1   
	 2   

#2   
#  100         ，       ？
select city.name,city.population,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population < 100;

#3   
#     100         ？      ？
select country.name,city.name,city.population,countrylanguage.language
from city join country on city.countrycode=country.code 
join countrylanguage on country.code=countrylanguage.countrycode
where city.population < 100;

select  1. 1， 2. 2... from  1 join  2 on  1.a= 2.b... where  x. x < 100;

がいぶせつぞく
外部接続は、内部接続に基づいて使用されます.left (right) join ...... on...
外部接続は脱敏できます
and whereに置き換えることはできません

#    
select city.name,city.countrycode,country.name
from city left join country on city.countrycode=country.code and city.population < 100;
+------------------------------------+-------------+----------+
| name                               | countrycode | name     |
+------------------------------------+-------------+----------+
| Kabul                              | AFG         | NULL     |

#    
select city.name,city.countrycode,country.name,city.population
from city right join country on city.countrycode=country.code and city.population < 100;
+-----------+-------------+----------------------------------------------+
| name      | countrycode | name                                         |
+-----------+-------------+----------------------------------------------+
| NULL      | NULL        | Aruba                                        |