终端运转mysql命令
终端运行MYSQL命令
进入数据库 mysql -uroot -p123456(数据库的密码);
查看所有库名 show databases;
进入库 use news(库的名字);
查看表 show databases;(表的名字); 查看表的内容 select * from 表名; 以数值的方式显示 explain 表名;(如果数据多在表名后加\G与表名之间没有空格)|
统计数据显示 select count from 表名; 创建库 create database 新的库名; 创建表 create table 表名;
写表的内容 (id int not null auto-increment,
user chor(20) not null,
pass chor(32) not null, 设置主键 primary key (id););
查看与服务器连接状态 \S
以数值显示 \G
去除乱码 set names gbk;
退出\Q
-----------------------------------------------------------
-----------------------------------------------------------
---
普通链接:select * from 表名 join 表名;
注释:一个一个查找;
左链接:select * from 表名 left join 表名;
注释:以左表为主;
例子:
yuangong 主表 gongzi
-------------------------##
##-------------------------
| id | xm | xb |## ##| id | userid |
money |
|------------------------##
##|------------------------
| 1 | a | 0 |## ##| 1 | 1 | 1000
|
|------------------------##
##|------------------------
| 2 | b | 1 |## ##| 2 | 2 | 1500
|
|------------------------##
##|------------------------
| 3 | c | 1 |## ##| 3 | 2 | 800
|
########################################################
#########
条件:select * from yuangong left join gongzi on
yuangong.id=gongzi.userid;
结果有4条
记录
混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载
:注释:空的也有一个链接，大于0的有几条显示就有几条结果。
右链接和左链接反过来就好～～～～～～
内链接:在左链接的基础上吧空记录去点就好～～～～～～～《简单吧》
语句的使用:以性别为1的显示出来:
标准语句:select yuangong.xm，gongzi.money，yuangong.id
from yuangong left join gongzi onyuangong.id=gongzi.userid
where yuangong.xb=1;
||
||
再次筛选
-----------------------------实例
---------------------------------------------
school库
学生表 student ( SID,Sname,Ssex,Sage); 综合积分 Score( CID,SID,Score);
1，在dos进入mysql的命令;
2，显示当前mysql当中已经具有那些数据库的命令;
3，在mysql当中创建数据库Score数据库; 4，打开数据库Score;
5，在Score中创建表，要求CID主键，SID整形，Score。
6，在Score插入记录，学生编号5，综合积分100分;
7，将学号为5的学生的综合积分更改为120分; 8，显示所有学生的综合积分，如该学生没有综合积分，显示以空，
只显示姓名和综合积分;
9，显示所有具有综合积分的列表，要求只显示姓名和综合积分，并按照年龄进行排序;
10，要求显示所有姓许的年龄在15-18所有学生的积分;
11，删除student中所有姓许的学生记录; 12，删除Score表;
13，删除数据库school库;
Microsoft Windows XP [版本 5.1.2600] (C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>e:
E:\>cd appserv\mysql\bin
E:\AppServ\MySQL\bin>dir/w
驱动器 E 中的卷是 工具
卷的序列号是 7C07-A9F1
E:\AppServ\MySQL\bin 的目录
[.] [..] mysql.exe
mysql.ico
mysqladmin.exe mysqld-nt.exe mysqldump.exe
5 个文件 11,856,638 字节
2 个目录 8,983,019,520 可用字节
E:\AppServ\MySQL\bin>mysql -uroot -p5211314 Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 Server version: 5.0.51b-community-nt-log MySQL Community
Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bawei |
| db |
| huiyuan2 |
| kaoshishiyan |
| mysql |
| phpmyadmin |
| shiyan |
| shopping |
| test |
| tongji |
+--------------------+
11 rows in set (0.09 sec)
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bawei | | db | | huiyuan2 | | kaoshishiyan | | mysql | | phpmyadmin | | school | | shiyan | | shopping | | test | | tongji | +--------------------+ 12 rows in set (0.00 sec)
mysql> use school; Database changed
mysql> create table score(
-> cid int auto_increment primary key,
-> sid int,
-> score int
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> insert into score (sid,score) values (5,100);
Query OK, 1 row affected (0.00 sec)
mysql> select * from score; +-----+------+-------+
| cid | sid | score |
+-----+------+-------+
| 1 | 5 | 100 |
+-----+------+-------+
1 row in set (0.00 sec)
mysql> update score set score=120 where sid=5;
Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from score; +-----+------+-------+
| cid | sid | score |
+-----+------+-------+
| 1 | 5 | 120 |
+-----+------+-------+
1 row in set (0.00 sec)
mysql> create table student(
-> sid int auto_increment primary key,
-> sname char(10),
-> ssex char(6),
-> sage int
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> inset into student (sname,ssex,sage) values ('a','nan',12);
ERROR 1064 (42000): You have an error in your SQL syntax; check
the manual that
corresponds to your MySQL server version for the right syntax to use near 'inset
into student (sname,ssex,sage) values ('a','nan',12)' at line
1
mysql> insert into student (sname,ssex,sage) values ('a','nan',12);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (sname,ssex,sage) values
('a1','nan',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (sname,ssex,sage) values ('a2','nan',21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (sname,ssex,sage) values ('b2','nv',17);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+-----+-------+------+------+
| sid | sname | ssex | sage |
+-----+-------+------+------+
| 1 | a | nan | 12 |
| 2 | a1 | nan | 18 |
| 3 | a2 | nan | 21 |
| 4 | b2 | nv | 17 |
+-----+-------+------+------+
4 rows in set (0.00 sec)
mysql> select student.sname,score.score from student left join score on student.
sid=score.sid;
+-------+-------+
| sname | score |
+-------+-------+
| a | NULL |
| a1 | NULL |
| a2 | NULL |
| b2 | NULL |
+-------+-------+
4 rows in set (0.06 sec)
mysql> select student.sname,score.score from student inner join score on student
.sid=score.sid order by sname.sage desc;
ERROR 1054 (42S22): Unknown column 'sname.sage' in 'order clause'
mysql> select student.sname,score.score from student inner join score on student
.sid=score.sid order by student.sage desc;
Empty set (0.00 sec)
mysql> select student.sname,score.score from student left join score on
-> student.sid=score.sid where student.sname like 'a%' and
student.sage>15
-> and student.sage<18; Empty set (0.00 sec)
mysql> select student.sname,score.score from student left join
score on
-> student.sid=score.sid where student.sname like 'a%' and
student.sage>10
-> and student.sage<21; +-------+-------+
| sname | score |
+-------+-------+
| a | NULL |
| a1 | NULL |
+-------+-------+
2 rows in set (0.00 sec)
mysql> delete from student where sname like 'a%';
Query OK, 3 rows affected (0.00 sec)
mysql> select * from student; +-----+-------+------+------+ | sid | sname | ssex | sage | +-----+-------+------+------+ | 4 | b2 | nv | 17 | +-----+-------+------+------+ 1 row in set (0.00 sec)
mysql> drop table score;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database school; Query OK, 1 row affected (0.06 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bawei | | db | | huiyuan2 | | kaoshishiyan | | mysql | | phpmyadmin | | shiyan | | shopping | | test | | tongji | +--------------------+ 11 rows in set (0.00 sec)
mysql>
浙公网安备 33021202002483